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]

Thank you John. My biggest issue is understanding functions and formulas I’ve never seen before and understanding what they are doing. You know, trying to wrap my brain around what they are doing. I appreciate all your help and even your desire to lend a hand to those of us that have a need/want but just don’t know how to achieve the result using formulas, macros or VBA code. It’s super nice to know there are people out there that are willing to help. You are one of those people. The same with Matt. You guys seem to make a good team even it your not. Thank you for all you do.

[#3]

I’m talking about your version that has the worksheet and tally. The one with True or false. That’s the one I really liked. Of course, it would be pretty cool if instead of the words True or False, a Red circle displayed if False and a Green circle displayed if True. So if all 3 meters have been read, Green Circle. If anything less than all 3 meters having been read, Red Circle. Again, it would be cool but True or False works just the same.

[#4]

@Carl_61 

My biggest issue is understanding functions and formulas I’ve never seen before and understanding what they are doing. You know, trying to wrap my brain around what they are doing.

That makes all the sense in the world. I'm in the same position often. There are times when others here in this forum offer solutions that I don't understand at all. Sometimes I just shake my head in amazement. Other times I start playing with the function or the method in simpler ways with applications of my own, so as to get an understanding. Or I take a look at YouTube or a website like ExcelJet in order to get an explanation of the function from a more instructional perspective. In general, though, the best way to wrap our brains around a function we don't understand--but want to understand--is to start working with it (or playing with it) on some project that we care about. 

There are hundreds of functions in Excel, covering all kinds of types of data and different types of analytical systems--engineering, finance, statistics, etc. This list is worth keeping handy when you are looking for how to accomplish something.

It’s super nice to know there are people out there that are willing to help. You are one of those people. The same with Matt. You guys seem to make a good team even it your not. Thank you for all you do.

Thanks for your kind words. Matt is far more knowledgeable than I, one thing I've realized. But he and I are only two of a number of people who are happy to help others learn the ins and outs of this marvelous tool. It's like a hobby for us, and always gratifying when somebody like you is helped.

[#5]

@Carl_61 

I’m talking about your version that has the worksheet and tally. The one with True or false. That’s the one I really liked.

I had the impression you were still working with monthly sheets somehow. Is that not the case? 

In the Tally sheet, there are only a few distinct formulas, and then conditional formatting that creates the colors for the TRUE and FALSE.

Other than sequencing the building numbers as you want them, which you could accomplish just be copying a list that IS in the sequence you desire, I think the formulas themselves--all of them--can just be copied down the rest of the sheet. It does occur to me that they may need modifications in order to take account of the entire set of raw data on the entry sheet. If you need help with those modifications,  let me know.

[#6]

@Carl_61   @mathetes  First off, thank you for the kind words and I agree in spades about the kindness and generosity of the many contributors here and on other forums that both share their knowledge and lend aid to people of all skill levels.  No we are not an official team but I have been on a number of posts with John and always appreciate his input as we so often agree on best practices and he often says what I would think.

What I wanted to add here was to comment on Carl's request for a red/green dot.  Again, with the conditional formatting I already did with the whole line being green when all 3 are done, light blue for individual meter reads and white for undone reads, I think it is pretty clear.

(hmmm, might be nice if you extended that green onto the building number too.  Simple change the 'Applied to' range of that conditional formatting to be $a:$J)

BUT you say you like the True/False column that John made which you should be able to copy over and you can then use conditional formatting to add a red/green dot.  I think this could be a great exercise for you to learn more about conditional formatting.  As shown in the picture below there are a number of options in the 'Icon Sets' for you to pick from and 'play' with to learn. Apply it to the true/false column you create and if you don't want to see the text there is an option to 'show icon only' or if you could always make the text 'white' to be not visible on the white background:

[#7]

@mtarler @mathetes 

Just to be clear, I am speaking to the workbook attached and the Tally Sheet Tab Column "L" All Meters Read? column in regards to the True or False.  I was just saying that instead of "True", True is represented by a Green Dot/Circle and instead of "False", False is represented by a Red Dot/Circle.  I will try to make that happen but I just wanted to point out the Work Sheet & Tally Sheet I thought would be the most functional for me.  Thank you to both of you and now I just have to figure out how to implement these 2 into the UMS Workbook and make them functional.

[#8]

I've been working with this for a while now and cannot seem to make it work. Choices for the ICON Set are limited when it comes to saying if False, Red Dot, If its True, Green Dot.

[#9]

I cannot figure out, based on the way Icon Sets is set up, how to apply Icon Sets to make it work. It seems to be straight forward but the options are not as such to allow me to pick = and/or Text. I guess I do not know what I am doing as I just cannot make this work.

[#10]

ok so i didn't realize it didn't let you pick T/F as values on icon sets but ... you simply need to convert the T/F formula to 1/0 output:
=N(AND(B4<>0,E4<>0,H4<>0))
alternatively you could use
=--(AND(B4<>0,E4<>0,H4<>0)) or =0+(AND(B4<>0,E4<>0,H4<>0))
then the icon set will work. You can also choose the last icon to be 'no icon' and then you just have 2 (i.e. if you pick green, yellow, red then you change yellow to red and make red 'no icon' so you just have green and red instead of making yellow be something like 0.5)

[#11]

Thank you Matt. I will give this a try. I was just about to message you regarding the the workbook with the RUN PREP DATA button on it. I was clicking on the Month Tabs, JAN, FEB, MAR and so on and noticed that cell A2 is blank in some cases. The JAN Tab shows Jan-22 with a date in B2 being 1/1/22. FEB Tab, A2 = Feb-22 with a date in B2 = 2/1/22. MAR Tab, A2 =Mar-22 with a date in B2 = 3/1/22. Now in the APR Tab A2 = Blank/Nothing but a formula is in the cell and B2 = 4/1/1900. The MAY Tab in A2 = Jul-22 and B2 = 5/1/22. JUN Tab = Blank/Nothing but a formula is in the cell and B2 = 6/1900. The JUL Tab A2 = Jul-22 and B2 = 7/1/22. AUG Tab A2 = Blank/Nothing but a formula is in the cell and B2 = 8/1/1900. SEP Tab A2 = Blank/Nothing but a formula is in the cell and B2 = 9/1/1900. OCT Tab A2 = Blank/Nothing but a formula is in the cell and B2 = 10/1/1900. NOV Tab A2 = Blank/Nothing but a formula is in the cell and B2 = 11/1/1900. DEC Tab A2 = Blank/Nothing but a formula is in the cell and B2 = the work "Blank". What is going on here?

[#12]

Oh, I also noticed that all my Monthly Overview Tabs are gone. I really need those. I'm ok with the Individual Monthly worksheet Tabs being gone but the Overview Tabs have a purpose.

[#13]

Tried the = --(AND(B4<>0,E4<>0,H4<>0)) and then set up the Conditional Formatting and it works. Just curious however to why the "0" and or "1" stays in the cell along with the ICON Set. In my case I have selected the set with the Red Circle with an X and the Green Circle with the check mark. So both the symbol and the number are displayed in the cell. Is this typical?

[#14]

a) in that workbook the formula in A2 gets the MAX date of the read dates and displays the month (note the VALUE is set to day 1 of that month but the FORMAT just shows the 3 letter month). In B2 it looks at a cell that YOU have entered/set-up in each tab that mentions the MONTH and shows day 1 of that month. In theory the data pasted on the sheet should match the sheet it is. If NO data is pasted it will show blank until data is pasted. In the case of MAY, I was using that to test and was pasting data that come from July data, hence why you see the differences.
b) I you want/need those other sheet, just copy them back into the workbook. open priod book with those tabs, highlight all the tabs (i.e. click 1 and then either ctrl-click each other or shift-click across the set) and then right click and select 'move/copy' and then using drop down select the new workbook and click on the checkbox to 'Copy'.
c) as I explained above there is an option in the conditional formatting to "Show icon only". Click on that checkbox. Alternatively you can just highlight that data and set the font to match the background.

[#15]

Stupide me. I relooked at the Conditional Formatting and saw what you are talking about. I don't know why that did not register until now. Sometimes you just can't see the forest for the trees. It that's how that saying goes. I did check that box and all looks great.

[#16]

I tried using the following formula on the Tally Sheet to bold and make Red the building numbers as per the GO List but the wrong numbers are changing.
=ISNUMBER(MATCH(A6,'GO List'!$A$3:$A$52,0))
I copied the formula from the Month Tab and thought the formula was universal but for some reason the wrong building numbers are being bolded on the Tally Sheet. Any ideas to why this is?

[#17]

In the UMS Workbook in regards to the Rate for Electric shows as 0.088000 but in the 1352 Acctdetails Report it shows as 8.8000%. Is there a way to show the Rate as it comes from the 1352 Report in the UMS Workbook. 0.088000% is a bit different than 8.80000%. I tried formatting the cells as a percentage but still cannot arrive at the correct percentage this way.

[#18]

The conditional formatting formula is always RELATIVE to the 'Applied To' range. So if your conditional formatting range on the Tally Sheet starts on row 4 then that formula MUST reference row 4:
=ISNUMBER(MATCH(A4,'GO List'!$A$3:$A$52,0))

As for the Electric %, when I open the 1352 report I see the rate as 0.088 and the same when I open the UMS Workbook. That RATE is the same as 8.8% and when I click on or highlight cells on the UMS and either click the % button or use the drop down and select percent, they change to the corresponding % value (note sometimes it will show 9% but then click the 'increase decimal' button (2 buttons to right of % button) to show more decimal places and it will show you 8.8%)

[#19]

Hello Matt, just ran some tests and so far so good. A few questions: When it comes to the Rates being inserted into the UMS Worksheet, some Rates show as 0 and some show as "-". I looked over the Jan data in the 1352 and all I see is "0.000000" when it comes to Rates without an actual Rate amount. What is causing some to have "0" and some to have "-"?

[#20]

I forgot to also ask, as the Run Data Prep Macro only appears to process the Electric stuff, how do we make the Gas Data Prep work? As both of these come from the same 1352 would it not be prudent to process both of them when that button is clicked?

[#21]

The "-" indicates it couldn't find that building # on that sheet while 0 means it found that bldg# but the rate value was 0 or blank

The gas (both) were processed in the testing I did