Forum Discussion
Solved
Populating cells with a Date across 12 TABS
Hello Community, If you are up to a challenge and if it possible to accomplish with formulas or VBA I've got a challenge for you. I have 12 sheets in a workbook that look exactly the same. Each wor...
- look at both as decimal (or percentage) are they the same or off by 100? It looks like the rate schedule is a true decimal number (i.e. 0.00088 or 0.088%) while the other number is coming in as % units (i.e. 0.088 which if formatted as % would be 8.8%) so basically you need to convert from % UNIT to decimal value.
=(ROUND(K6,3)= ROUND(100*K$3,3))
or BETTER yet change the formula in K3 to be =100*... so
=100*XLOOKUP(LEFT($I5,3),LEFT('Rate Schedule'!$B5:$B16,3),'Rate Schedule'!C5:C16,"error")
CORRECT it will give #SPILL! because it is trying to "spill" 3 values out into K3, L3 and M3 but you have to delete the existing formulas/values in L3 and M3 so the 1 formula in K3 can REPLACE all 3 formulas previously in K3, L3 & M3
I am kind of stuck on something with the conditional formatting though. I am asking it to display in different ways based on if the rates match, don't match, contain "-" or equals "0". My issue is when the Rates do not match I've asked for conditional formatting to fill the cell with Red and make the font white. I've asked it to fill the cell in a light Green with the font Yellow if the cell equaled "0". It all works but because "0" does not match the Rate the cell is filling with Red instead of the light green. Is there a way to overcome this?
- yes you can change the order of the rules in the conditional formatting window and/or click the box to the right of the rule that says 'stop processing rules if true' (or something like that)