Hi, I run a report weekly that looks at previous weeks data and using xlookup copies across the notes of previous week. I can get it to copy across the value in each cell but not the formatting. For example each week my team colour format the cells based on what they have done, ie Red = stuck, yellow = working on it and green = done. That way at a glance we can see the status. When i run the new report each week (from a Power BI report) the report is the raw data and use xlookup to copy across column F but it only copies across the data not the colour formatting. Please explain how i can copy across the formatting as well
Indeed, you can't 'look-up' color formats. Best to change the template so that column B contains the status in words. Do that with data validation to avoid spelling errors. Then use conditional formatting to color the status cells.
Come back here if you get stuck.
@riny Thanks for the quick response. Issue arises with your solution that i often have over 100 lines and my staff dont want to have to change/update the status of each line every week they want to be able to sort by colour then only want to have to update those that need changing. Column B (is looking at a different rule) and is formatted using a macro as this column comes across from the PBI as a number (a choice of only 3 numbers) so i can easily format using the conditional formatting. Its the columns that are manually changed by staff that need to carry across. Looks like i will have to find another way. Thanks anyway
Oh, I hadn't noticed that B had numbers in it and three conditional formats based on these (10, 20, 30). Not sure I though how these tie in to the 5 status codes at the top. But, that's not any of my business.
My general recommendation is to move away from manual colour coding of texts to indicate a status. Best to have a value based (text or number) system with data validation and do conditional formats on these, just like you do with column B. It would require an additional column, though. I trust you are well enough versed in Excel to figure out some solution.
