Populating cells with a Date across 12 TABS

[Carl_61]

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.

[#2]

@Carl_61 

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

1. 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)
2. 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)

[#3]

@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.

[#4]

@mathetes 

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.

[#5]

@Carl_61 

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.

[#6]

@mathetes 

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

[#7]

@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)

[#8]

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. 

[#9]

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.

[#10]

@Carl_61 

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.  

[#11]

Looks much better thank you. Being that there are 3 different meters to read and not in all cases all 3 will be able to be read according to the reason mentioned on the worksheet, how difficult would it be to to break the list down into individual Utility meters. That is, the BLDG# not Complete list does not define whether all 3 meters were read for the individual building. So what I am asking is there a way to add additional columns that could specify those building where no Electric Read Data has been entered. Another column that shows where no Gas Read Data has been entered & the same for Water? What you have now to me says all meters for each utility for each of these buildings have not been read.

[#12]

@Carl_61  ok in this version I added the corresponding date columns so IF 1 or 2 of the reading were made then they will be displayed (i.e. it will therefor show which reading are still needed)

If you really want separate lists for each that can be done but I think that would be just messy and confusing IMHO

[#13]

@Carl_61 

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.)

[#14]

Thank you, I don't need a separate list for each. I just was not sure how to handle one sheet with a bunch of dates needing to reflect in and amongst 12 different months. I really like your worksheet but need to understand how to deploy the formula(s) within the Month Tabs. I would rather have the GO List Tab separate and of its own knowing there will only be a small amount of building numbers in there. I like that the dates when entered into the worksheet actually apply to the month from which the reads took place. I must admit that I really like @mathetes approach in regards to having a Tab of Reading Done and one Still to be done. One thing I noticed is that when I entered data into the worksheet against Building #300, it is showing up against Building #310 on the month Tab. If you not mind I'd like to have you explain what is going on with formula so I can understand what is happening. Also, to avoid a miss alignment of data to Building number the following formula was created: =IFERROR(VLOOKUP(R6,'JAN WORK SHEET'!$A$6:$H$368,6,FALSE),0)*100. This is just an example for the GAS but it seemed to have done the trick. If you know of a better or different way I am good with that as well.

[#15]

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/21/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.

[#16]

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.

[#17]

@Carl_61 

I must admit that I really like @mathetes approach in regards to having a Tab of Reading Done and one Still to be done.

I'm glad, of course, that you like it. I'd like to point out that the key (almost always in almost every workbook I work on) is having a single database for all entries (as opposed to, in your original version, having a separate database for each month).

Excel has marvelous abilities to extract data from well designed databases--to create "dashboards" that summarize data for reporting purposes. In this case, although not qualifying as a single dashboard, I've created those two tabs that do nothing but extract data from the single database that shows what readings have been taken, and buildings that aren't yet finished.

But the heart of it, my crucial point, is a single worksheet for data collection. In some ways, I think I'd rather see you have a separate row for each meter in each building. I.e., each row would consist of something like this: Bldg#, Date, Meter, Comment (just using a drop-down to specify which meter). It would mean a lot more rows, but I'm pretty sure that those two Output sheets would be clearer and cleaner. Especially on the "Still to be done" sheet, you could highlight not only buildings with one, two or three still be read; you could also emphasize (if it were important) one particular type of meter. (If, for example, you're specifically concerned about Gas consumption)

Unless I'm mistaken, we might well be able easily to use a Pivot Table to summarize the data on an overall basis.

[#18]

What I have is based on the only way I knew how to approach the task I was dealt with. The bottom line in this is that I collect the information as seen and then compare the information to its prior month. Hence the Conditional formatting I have running rampant thru out this workbook. The data I collect comes from monthly reports I receive for which a macro was written to go thu the report to sort and align the data for coping and pasting into the workbook. That macro has to be change to account for the addition of the Rate which comes from the same workbook but a different tab. The bottom line is I use the macro to collect the data so I can paste it into the workbook each month. So when you mention pivot table I am not apposed to this its just that I do not know what this would like. I am open to suggestions if there is a better way of achieving the goal.

[#19]

so no I did NOT notice that the lists on each tab don't line up and if that must be the case then you will need to use a LOOKUP formula (recommend XLOOKUP as it appears they improved the efficiency of it). That said, if you can avoid that and force all tabs to have the exact same row structure (i.e. bldg 123 is always on the same row) then you can use the formula I used which was a more simple IF condition applied to the range and therefore each row would fill in the corresponding row value. Since you already have a macro inserting and lining up the data you could have it insert buffer lines on all the other tabs accordingly. BTW, I also do VBA if there is a question there too, not that I need or want more work. lol.
As for the GO list, it is convenient to have the Incomplete list there for conditional formatting rule but otherwise you can easily copy it to its own sheet and make its own rule.
gtg to a mtg right now but I'll try and look at the rest of the questions later.

[#20]

Hello today Sir, I am happy to hear that you also partake in the VBA Code world. I posted on Dec 12th a request for help and am still waiting for a reply. The name of the post is VBA Code Modifications. Please check it out! I am happy to provide additional helpful information should you need it.