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