Aug 01 2020 05:39 PM
I am creating a checklist and need to have multiple cells shaded based on the text in one cell.
Each time 'Sale' is typed into any row in column 'F', the same columns are shaded for that row.
I've attached my spreadsheet, which shows which cells should be highlighted for a 'purchase' 'sale' and 'mortgage' Basically I'm trying to create a checklist where I only have to type in the file type, and the irrelevent boxes for that file type are shaded in.
I tried to do rule, which worked well, but only applied the shading to the first cell.
Any help would be greatly appreciated, I've been working on this all day and can't figure it out!
Aug 01 2020 07:18 PM
@RozBloggs , something like this?
ps: I've used the Filter function that is new to the Excel 365 version. Let me know if you are using an earlier version of Excel.
Aug 01 2020 07:21 PM
@TheAntony No I have the most current version!
Aug 01 2020 07:22 PM
@RozBloggs , great! Let me know it this is what you are looking for then.
Aug 01 2020 07:25 PM
@TheAntony on the right track! But when I type in Sale now, all the shading disappears, and then if I delete/change them it all disappears again... :(
Aug 01 2020 07:33 PM
@RozBloggs , Here's how I set it up to work (see attached video). Is this not what you were looking for? Can you share a screenshot or video of what it looks on your computer?
Aug 01 2020 07:39 PM
@TheAntony Your video works perfect - that's exactly what I'm looking for! I'm not sure why it doesn't work on the downloaded one?
When I open it it looks great:
If I delete one item in the column it deletes all the shading:
And when I add a new item to the column it doesn't add the shading:
Aug 01 2020 07:47 PM
@RozBloggs It looks like you don't have the latest version of Excel that supports the new Dynamic Array functions. I'll see what I can do with traditional formulas.
You can check if you have any updates by going to File->Accounts->Update Options->Update Now.
In the meantime, can you let me know if the functionality in the video is what you are looking for?
Aug 01 2020 07:50 PM
@TheAntony apologies, I thought it was as I bought the latest version under six months ago. I'm updating now - but yes, what shows in your video is exactly what I am looking for!
Aug 01 2020 07:59 PM
@RozBloggs , No worries. Here's a version that should work without the latest update. Also, I deleted the empty table rows since they were just taking up space. The nice thing about Excel Tables is that they will expand as you type in new info.
Aug 01 2020 08:20 PM
@TheAntony it works perfectly! Thank you SO much, I'm so grateful that you took the time to do this! Is there any way you could give me a quick explanation of what you've done/link to a video/tutorial so if I have to make changes I know what I'm doing? I may have to add more columns in future.
Aug 01 2020 08:27 PM
@TheAntony sorry another thing - I need to be able to fill in the unshaded boxes (Y, N, R, or N/A) - but when I do that it gives me an error?
Aug 01 2020 08:36 PM
@RozBloggs , Glad it worked. Here's how I set it up.
=NOT(ISNA(MATCH([@[File Type]]&G$1,Checklist[Key],0)))
Hope this helps.
Aug 01 2020 08:44 PM
@RozBloggs , Didn't know that you wanted to use those cells for typing. I moved the formula into conditional formatting dialog. You should be able to use those cells now.
Aug 01 2020 08:59 PM
@TheAntony Amazing, works perfectly. I can't thank you enough!
Aug 01 2020 09:01 PM
@RozBloggs , Happy to help. Enjoy!