Forum Discussion
Solved
Populating cells with a Date across 12 TABS
Hello Community, If you are up to a challenge and if it possible to accomplish with formulas or VBA I've got a challenge for you. I have 12 sheets in a workbook that look exactly the same. Each wor...
- 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.
Carl_61 alternative combination:
in the attached I created a tab 'Work Sheet' which is near copy of all the other monthly Work Sheets and made it a table called worksheet.
Then in Jan I updated the formulas in columns L:P to check this ONE worksheet and if the corresponding row has data with a date = to this month then use it...
I don't think you need the other tabs/lists of which ones you need or don't need as this ONE worksheet will have it all and you can easily see what has or doesn't have data. That said I did update the "GO List" to list all BLDG# that don't have all 3 dates filled in.
now you can delete all the other '... work sheet' and do you need those '... summary' tabs? nothing is in them and the 'Yearly' tab too (but if not I could recommend a better formula to avoid the INDIRECT)
Thought I would mention, you have probably noticed in some cases there are a doubling or tripling of building numbers. The data contained within each Month Tab comes from reports I get each month. On the JAN Tab you can see building #315 for instance is showing twice. This is because of the cycle period and the fact that the building was vacant during the cycle periods shown. So in this case it occupied from 12/15/21 to 12/31/21. It was VACANT from 12/31/21 to 01/15/22. Hence the 2 line items. Sitting behind this workbook is some VBA Code which does a LineEMUp process across the ROWS so all the building numbers are on the same ROW and merges a bunch of cells. I would show you what I mean but due to some changes to the sheets to incorporate the column for EL Rate, GS Rate and WTR Rate the VBA Code has to be reset accordingly. I am trying to tackle one thing at a time as finding someone who knows VBA Code and Formulas together is turning out to be a very daunting task.
Looks pretty good and I will do some tests to see how it works. The only issue I see is that I really need to have the GO List as it's meant to provide a location to define certain buildings that are Guest Only (GO) short term. These building numbers were bold and in red to easily distinguish which buildings are defined this way. This list can change. That is, they can be removed from the list, additional ones can be added to the list as they become vacant. So seeing this at a glance is important. So, if the Go List was restored and your approach to this was a bit different, had its own TAB, this might just work. Which would mean I would remove all the month defined Work Sheet Tabs and change the formulas accordingly to point to the read data.
I restored your original GO list (i mistook GO as 'go there' as opposed to 'Guest Only', lol)
I added a column for the Bldg#s not Complete
I added a simple conditional formatting to the page for duplicates so that when you look at the bldg#s not complete you will ALSO see the highlighted numbers that are 'GO'
BTW - I noticed you use a lot of conditional formatting rules. They can slow down the worksheet performance so you might consider combining some. For example I see a whole bunch that appear to create almost a gradient color based on value that maybe you could combine into a single rule that outputs the gradient color.
This is not meant to resolve everything you have currently, but only to illustrate the "simple" approach I was talking about. There are many refinements that can be done. SO MUCH depends on what you really need. There are three sheets in the attached file.
- The first, "Readings," is there to capture the data when you do visit a building and take readings. Rather than your coding system for meter maintenance--which could be incorporated; it's not needed to demonstrate things at this stage--I just allow for a verbal description ("insect infestation" etc)
- The second, "Readings done" automatically is updated whenever a building is visited and the readings recorded in "Readings"
- The third, "Still to be done" lists buildings which have not yet had their meters read. (This can be formatted differently; as with the others, I'm just demonstrating a concept)
There is one formula in the second and third sheet; that's it. No other formulas in the entire workbook. The two formulas use the FILTER function, which means you need Excel 2021 or newer.
I've just revised the formula on the last sheet to make the array more "user friendly" by turning a single very long list into a two dimensional array of building numbers where meters have not yet been read. Still a single formula, in cell A2
- hello sir, You have a very high level
