Forum Discussion
Formatting based on searching for identical cell in another sheet
Hi, Does anyone know if it is possible to do this? I have two spreadsheets containing data from two consecutive months. In the earlier spreadsheet, let's say I have a list of customers colour cod...
I suspect it is indeed possible, probably fairly easy. HOWEVER, it's also likely that a wholly different approach might even be better.
Is it possible for you to post a copy--just without real names and other identifiers--of your two spreadsheets? Use OneDrive or GoogleDrive if you aren't able to post them here in this forum. Then post a link to them.
- Thanks for the response! XLOOKUP works great for the comments so I'll just wait a bit to check there isn't an equally easy solution incoming for the colour coding before I look at altering the sheets and posting them. If there's something easy like the XLOOKUP for comments then that'll be a fantastic solution for me.
- The color/colour coding would be more appropriately handled by Conditional Formatting rather than manual setting. That way it's rules-based, and the rules themselves would be what enabled the ongoing application of the same colors. That's how you take advantage of Excel for what Excel itself brings to the party. Otherwise you're basically using Excel for its resemblance to paper ledger sheets, and using it manually, making minimal use of the tool itself. Even the act of breaking what could be a single data base into separate sheets for each month--that in itself is treating this amazing computer-based tool more as a set of paper ledger sheets. So I would really encourage you to consider posting copies (anonymized so as not to reveal real names) of the spreadsheets so we could give suggestions that would involve making more full use of Excel.
- I have a solution thanks to your advice. I completely agree that having one database would be much nicer but the monthly data comes from an external source and then needs to be filtered and sorted before I can do anything with it so this monthly update probably can't be helped (although I should probably at least merge them into different tabs in the same file rather than keep the files separate).
That being said, I have now set it up so that rather than change the fill colour of the cells, I have added a column which I assign a number to depending on what I want the colour to be. I then set up conditional formatting to change the fill colour of the relevant cells depending on what the number is. This means that I can use XLOOKUP to get both the number and the comment related to each entry from the previous month that appears again and I can copy the conditional formatting rules so that the colour coding is automatically copied from the previous month.
This will save me a load of time each month, thank you so much for your generosity with your time in helping out!
