•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Conditional Formatting to show completion involving multiple cells.
Our take
Are you ready to transform your progress tracking with conditional formatting? By leveraging multiple conditionals across cells E22:E25 and I22:I25, you can create a dynamic tracking bar that visually represents completion status. Start with two essential rules: one for overdue tasks and another for those current or completed. To elevate your sheet, introduce a third rule that calculates progress percentage, filling the bar based on completed requirements.
I'm building a progresss sheet that is based on completion of multiple conditionals. The conditionals are e22:e25 and I22:I25, each representing a different requirement. I want to do a tracking bar at the base of the sheet using three formating rules based on the completion date of the reqs.
Rule 1 is =e22:e25,I22:25<Today() Rule 2 is =e22:e25,I22:25>=Today()
These come with specific cell formats and work. I'd like to add a 3rd rule that shows a progess percentage. For example lets say e22, e24, e25 and I23 are all done but the rest remain with no completion date enter it would show a bar 50% filled and the words in progress across the bar.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Conditional formatting based on a different cell's valueI'm fumbling my way through conditional formatting on a tracking sheet I'm creating. I have the status and required delivery formatting the way I want, but now I'm running into an issue where I want it to clear the formatting of the required delivery cell once the status cell is set to "complete" like the bottom row. The status cells are drop down menu if that makes a difference. https://preview.redd.it/0va9fhl9tlvg1.png?width=346&format=png&auto=webp&s=a470a51c639e165a0e4bb41b626d60743cf457c1 I've tried playing around with "IF" formulas, but nothing seems to be happy with what I'm trying. Any thoughts? submitted by /u/Senior_Cheesecake155 [link] [comments]
- Conditional formatting with formulasI am trying to create a spreadsheet to track ordered parts and their status, including their requested delivery date. What I'm trying to sort out, and failing at, is setting a conditional format so that when the requested delivery date is less than X days out (say 14 for now), I want it to highlight the cell red so I can check on the status of the order. This is my current rule configuration, which I set by selecting the column so it auto populates as it's filled in. https://preview.redd.it/n4g68xoihzsg1.png?width=409&format=png&auto=webp&s=6715ebba2e873e171fad3b862ff3a7d188b4b3a1 Inconveniently, it's doing the exact opposite of what I want: https://preview.redd.it/h7a7in7ohzsg1.png?width=294&format=png&auto=webp&s=1168efcd78872a5158cd045e6c3ea642c1328152 In this instance, ONLY 4/13 should be highlighted (I'm not worried about the top row at this point, I'll fix that after). I've even tried creating another column to do the math, and use that cell for the formula, and that's not working either. It seems like it should be straight forward, but I apparently lack the formatting skill. submitted by /u/Senior_Cheesecake155 [link] [comments]
- 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 based on range of values in different column?https://preview.redd.it/l3guc8q9wfog1.png?width=1324&format=png&auto=webp&s=ec9fba2147eebc33af9e108241f82cb9163cf512 I've tried several different ways to do this but clearly I'm missing something. I have spreadsheet that shows the hours scheduled on all the machines at our facility. The dates in row 2 auto populate based on today's date. I want to make a rule that changes the formatting of rows 3 - 12 if any of the dates in row 2 are company holiday. For example, if B2 matches any of the dates in R3 through R12, I want B3 through B12 to fill in black with red text, so that way it shows that date is blacked out because we're closed, but if someone accidentally scheduled an order that day the machine hours will show in red so they know to reschedule it. On the original version of this spreadsheet I made a few years ago I had the same issue and end up just using =B$2=$R$3, so for each day I had to make 10 different rules for every holiday in column R. I knew that definitely was not the best way to do it but I was tired of messing with it and just needed it to work. I am updating this spreadsheet and it now shows the next 60 days instead of the next 14 days and I know there's got to be a better solution than creating 10 different rules for each day. I know =B$2=$R$3:$R$12 does NOT work and I think I'm starting to smell burnt toast. submitted by /u/BluestockingLife [link] [comments]