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.
Carl_61 I think you mean John not Matt (I'm Matt, Mathetes is John, lol).
I'm trying to understand what it is that you are looking for/like with this added sheet. Is it the conditional formatting or that extra column that says All Meters Read? And why not just add either or both to the original input table? For example in the attached I added 2 conditional formulas:a) if all 3 read dates are in then it turn whole row green
b) any reading turns that set light blue
Note in this book is also the "TO DO" tab that shows only the bldg#s that do NOT have all 3 reading and shows what reading they do have (if any)
FINALLY, I also changed the macro so that if you do NOT have the correct workbook (i.e. the Acctdetails workbook) selected/active then it will automatically search through all open workbooks to see if one appears to be a match based on it having a worksheet called "PreBill GS Read Data"
AND I added a handy button on the "WORKSHEET" tab inside the Utility Management System file attached.
Give it a try, give feedback.