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