•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Choosing between conditional format, VBA, or Power Query
Our take
When managing dynamic data in Excel, especially with variable row counts like your "index cards," choosing the right tool is essential. Conditional formatting can visually enhance your data based on column B values, but may fall short for complex scenarios like yours. Power Query offers robust data transformation capabilities, allowing you to clean and manage data efficiently. VBA can automate tasks but requires more coding expertise.
My company has a google sheet where they have a series of “index cards” that are used to fill in the blanks. The top and bottom lines are always the same. But the middle can be as few as six rows or as many as 21 (that I have seen thus far) but could possibly go higher.
What I want to do is copy all the cards and paste into excel as values.
Then I want to change the fill color of column C based on a value in column B and I want that color to fill down to the cutoff line which is also based on a specific value in column B. Theoretically the empty cards will all have a stripe down the middle that can be sorted and deleted.
The live cards change daily as jobs are completed and they are deleted or someone uses an empty card to fill in new info to create an active card.
I can’t think of a conditional format formula to do this. I’m pretty sure I could clean it out in power query. I could also take a stab at it with VBA.
Which would you recommend, and if it is conditional formatting what formula would work do you thinK?
Using desktop Excel 365
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Set conditional formatting formula to change cell colors after conditions are met in a separate tabI'm doing r/Fantasy's reading Bingo challenge this year, and thought I'd set up a spreadsheet to help me track my progress. The conditional formatting formulas have been causing me some trouble, though: In Tab 1, I have the Bingo squares arranged in the usual 5x5 grid, and in Tab 2, I have all the squares & their reading rules, hard modes, and completion statuses laid out. I'd love to set it up so that if, in Tab 2, I mark "x square" as completed in "EASY" mode, then the equivalent "x square" cell in the 5x5 grid will automatically change color in Tab 1. Ideally the 5x5 grid won't need any manual color formatting, and I'll only need to edit Tab 2. But I haven't been able to figure out how to make this work across the two separate tabs. In case I'm not describing it well, I'm including an editable Google Sheets version of the spreadsheet for reference: https://docs.google.com/spreadsheets/d/1S6EtJFLLSoysxIp-HHZT_tjVznucJuDbhqpDhbdPZX0/edit?usp=sharing Thanks so much in advance! submitted by /u/defenestratedduck [link] [comments]
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- How to make conditional formatting exclude blank cellsMaking a working hours chart but I'm not very well versed in many Excel functions so I've been consulting google results for most, but I don't understand why conditional formatting is marking blank cells green for value >0. The detailed issue is as follows: I have columns for for date (C), start time (D), end time (E), and the following (using row 5 for example): Total time F5 =IF(D5;E5-D5;"") Number format: Time Overtime: G5 =IF(OR(F40="";TIME(8;;)="");"";(F40-TIME(8;;))*24) Number format: General I have set up conditional formatting in column G to color everything that is below 0 red, everything above 0 green, but all the blank cells with above formula applied are colored green too. Cells that equal 0 are left without color, as I intended. My hope is that blank cells also remain white until they recieve a value. Is there a way to achieve that? I appreciate all the help! submitted by /u/ace_098 [link] [comments]
- Form control drop down list+ color codingokay so I'm making a drop down list that has permanent arrow in the same cell as my options. now the list has tasks assigned and I want to color code on the basis of done/not done/in progress I used the index formula but I think the information in the cell is hidden behind the form control box so is the conditional formatting Does anyone know how to edit text and formatting of the form control box - even if I can make it 'no fill' so the cell formatting is visible or keep the arrow from data validation command in the same cell? is there any other way I can achieve the same cell arrow and color coding both? submitted by /u/stupidbutwitty_832 [link] [comments]
Tagged with
#Excel compatibility#Excel alternatives for data analysis#Excel alternatives#rows.com#AI formula generation techniques#cloud-based spreadsheet applications#formula generator#google sheets#financial modeling with spreadsheets#conditional formatting#Excel#VBA#Power Query#Google Sheets#fill color#column B#column C#index cards#values#active card