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 -- here's a quick and dirty that does the job I think. It looks like this:
It probably can be made more elegant, but this is a "proof of concept" if nothing else. It shows the meters that have been read during the current year (that year being entered in cell B1). There is a FILTER function in columns B, E, and H that needs to be copied down the full length of the spreadsheet, so there's one row per building.
For the time being, I chose to let the values of 0 appear (in dates that appears as 1/0/00), which simply denotes buildings that haven't been done for that particular meter. To make it quickly readable, however, I highlight (using the same tri-color scheme you have) the meters that have been read. Then, off to the right, there's a column that summarizes which buildings are totally finished, which still are not.
Fill in a few more random entries on the Work Sheet tab, and see whether or not it meets your need.
