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 worksheet represents a month. So Jan, Feb, Mar and so on up thru Dec. Each sheet consists of 317 ROWS. Each ROW represents an individual building number. ie, 200, 210, 215, 218 and so on. Each month I have to physically go to 26 or 27 buildings and physically read the utility meters, Water, Gas & Electric and jot down the readings. I then enter these numbers (Readings) into the workbook and on the sheet for the month which the readings were obtained for. The task here is to obtain 100% of the meter reads over the course of 12 months by dividing the number of buildings by 12 to determine the number of reads necessary per month to obtain 100% by the end of the year. So, what I am trying to accomplish is the ability to enter a date against a building number, for example JAN, and have that date populate all the other cells for that building number across the months of FEB, MAR, APR and so on right on up into DEC. In essence showing that the meters for this building have already been read and thus locking those cells from having any additional entries being made into them. The only way to change the date would be to go back into the month the meters were originally read. Any change, if it were necessary would reflect/update all those other cells. Now lets say dates were entered into the FEB worksheet for which no dates were entered in the JAN Worksheet, the same thing would happen but the date entered in FEB would also reflect into JAN as well as MAR, APR, MAY and so on right on up into DEC. This process would continue all the way up into DEC. If a date was entered into DEC for a building that did not reflect as having its meters read, the DEC date would reflect all the way back into the JAN worksheet and populate all cells for that building number up thru Nov as DEC is the originating date entry. The idea here is to be in a current month and be able to look at the current month worksheet and be able to determine what buildings still need to have its meters read. So come DEC for instance, you should only see what buildings still remain to have its meters read. Again, the idea is to read a number of meters each month that would as evenly possible to obtain 100% reads by the end of the year. More or Less could be read each month but this method will always show which ones still need to be read.
So what do you think about this challenge??? This will probably keep you on your toes. Keep in mind, I have 12 worksheets, each having its own TAB within a workbook. And on each TAB I have a cell, within a column, for entering a date for Electric Read, one for Gas Read and one for Water Read. Anyone up to the challenge? More details can and will be provided upon request.
- 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_61 My suggestion is to add another tab, let's call it CHECK with the same table as the monthly tabs and use a formula in that table to determine if that building has been done. e.g. =COUNTA(JAN:DEC!B2)
Using COUNT you can see if the count is >0 (and see if you accidentally have 2 or more.
Alternatively you can use MAX() which will also show what that date value is so you can go find it easier
Either way, I suggest that you then use conditional formatting on each sheet to check the corresponding cell on CHECK to see if it is >0 and then fill with a color to notify you it is done.
See attached for simple example
EDIT: I just saw mathetes response after I posted, and want to agree that from the sounds of it, maybe it is overly complicated workbook. Since according to you, you visit each building 1x per year, why not have 1 tab and you are already entering the date so you know WHEN you did the inspection. If you want other statistics like how many you did in a given month then use pivot table or create a table using COUNTIFS.
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)
