Forum Discussion
Populating cells with a Date across 12 TABS
- 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")
Carl_61 ok in the attached sheet I have a few things:
a) on all the months I added a lookup formula to pull the rates from the rate sheet to this corresponding MONTH tab
b) on the JUN tab i added a conditional formatting formula that is very similar to the above formula to highlight those rates different than the rate tab
What I realized is:
a) do you really want to highlight all the rates that are different? What about the 0 and - values?
b) or if you pull the rate over or just highlight the rate on the rate tab?
c) the rates are not exactly the same 0.88 vs 0.888 so what resolution do you want to use? You can impose that using ROUND or MROUND to force 1 number or round both numbers. Alternatively you can take the ABS() of the Difference of the 2 values and make sure it is less than x amount.
In any case I have given you a couple formulas and a couple techniques and some key pointers to consider, let's see if you can run with it and learn how this all works 🙂
