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")
I suspect, Carl, that you're making this more complicated than it needs to be. Can I ask three questions? (That not being one of them)
Does each meter in each building need to be read once in each year?
Do you always read each of the three meters in any given building at the same time? (So basically only one visit to each building for this purpose in any given year)
Is there any particular sequence required, or can the buildings visited vary from year to year?
Assuming answers are Yes, Yes and No
- you could have one sheet (Sheet 1) on which you record the readings done (lists all buildings, but no dates or readings until done), a sheet into which you enter dates and readings when they're done (no more monthly sheets)
- and another sheet (Sheet 2) that draws from that Sheet 1 the buildings for which readings have not been done,--i.e., they are still needed--and you just go to ___ more in the following month, enter the readings in Sheet 1 (causing them to disappear from Sheet 2)
Rinse and repeat
It may be that a variation on that would be simpler; my basic point is that you do not need monthly sheets with all that elaborate dating stuff you talk about in your challenge (unless I'm missing something)
Thank you, sir. I am ok with one sheet if I can accomplish what is needed. There are other things on these sheets but if it can be consolidated, I agree with what you are all saying. The reason I have all the sheets is as follows but if there is another way, I am very much ok with it. I have attached the workbook for your better understanding. I certainly like your approach. Question 1. Yes. Each meter, Electric, Gas & Water needs to get read at least once per year. Question 2. Yes. Each meter will be read during the visit to the location. The only time that will not happen is 1. When the meter is broken, not legible, not accessible (confined within a fenced in area for which contact with resident would be required) or not accessible due to an insect issue like ants, bees, wasps & could not locate, etc. Maybe only 2 of the 3 meters will be read requiring a return later to get to the unread meter. Yes. The goal would be to visit and read one time, all 3 meters, once and move on to another building and do the same. 3. There is no sequence. Just reads at random. If I'm in an area I will attempt to read the required number of meters or more. 1 read per building per year as an oversight to the utility company readings. My reads are then compared to the reading taken by the utility companies. My numbers will never be the same as the utility companies, but we are looking for within reason differences. In the attached workbook if you look at the TABS across the bottom and click on the JAN Tab for instance, you will see what the worksheet is a part of. The data entered into the worksheet feeds areas in Green on ROW 4, Columns L, M & P for Electric. ROW 4, Columns AC, AD & AG for Gas & ROW 4, Columns AO, AP & AS for water. There are formulas in all the cells starting in L6, M6, N6, O6 & P6 all the way down as well as for GAS & Water in their perspective Columns & cells. If there is a better way to record and use the data I will be collecting and still accomplish the mission I am all for it. At the time I just could not figure out a better way. Also, if a record and disappear on a sheet as you mentioned could happen, this to would be super nice.