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")
Thank you Sir for this. I do have a questions. When I first open your book I am able to look at each TAB and see the data. However; when I make an entry on any of the lines I can see the entry on the Readings done Tab but when I look at the Still to be Done TAB it shows me "#NAME?" in cell A2. When I hover over Orange square to the right of cell it says "This formula contains an unsupported function." I do however; really like this approach. The only real issue aside from what I've already mentioned is that I need to be able to record a date against each reading for each utility as I may not be able to read each meter on the day I am there due to one reason or another as well as I will need to define the meter condition. Electric Meter, Gas Meter, Water Meter. As you can see on the Month Tab each Utility Section has a place to show the meter condition at the time of the read. If your approach was set up differently to allow for a date, a read & a condition for each utility this would be great.
when I look at the Still to be Done TAB it shows me "#NAME?" in cell A2. When I hover over Orange square to the right of cell it says "This formula contains an unsupported function."
It uses WRAPCOLS, which is apparently a very new function, yet to be released at large. I've left it in this revised version, but all you need to do is take it out. Change the formula from
=WRAPCOLS(FILTER(Table1[Bldg '#],Table1[Counter]<3),35,"--")
to
=FILTER(Table1[Bldg '#],Table1[Counter]<3)
Beyond that, you'll see that this revision now has a date for each meter reading. It makes the "Readings Done" sheet both more comprehensive and accurate in that level of detail, but in the process, far more formulas are needed. So it will slow things down a bit (probably not noticeable).
Play around with it. Make random entries in the "Readings" tab and see how it changes things. You'll see that I've programmed it for a "default" of reading all three at the same time, so the date, when entered under ElDate propagates to the other two dates, for Gas and Water. If one or both of those others are NOT read on that date, you'll have to erase the dates and then enter them manually when you DO get around to reading those meters.
You'll see I added a column to the readings tab that just counts whether 1, 2 or all 3 meters have been read. And the "Still to be done" tab takes that into account, continuing to show building numbers until all three have been read. The "Counter" column can be hidden--probably should be hidden--since no manual entries are expected in it. I left it visible for the time being to show its presence.
(mtarler : Matt, I've not taken time to look at your contributions yet; wouldn't be surprised if you've long left me in the dust, but I wanted to give this a try with my little toy Excel tools.)
- Hello Sir, I so much like the Wrapped version. looks so much more cleaner. Maybe someday we will get the newest version of Excel to take advantage of all the new Functions and features. Like I mentioned earlier, I like having a worksheet that feeds into 2 others like you have done. 1 for pending reads and 1 for those that have been completed. The only issue I have is that I need be able to have meters for the individual utility to show up on the Not Done and Done list. This is, if only 2 meters were read, these 2 meters would show up on the Read list and the 1 not read would show up on the still to be list. I am just trying to have a clearly defined visual of what's done and what's not done. Just because there are 3 meters does not mean they will always be able to be read when the building is visited. Even though I went out to building XYZ and was able to Read 2 of the 3 meters, I will still have to get back there another day to read the remaining meter.