Forum Discussion

Carl_61's avatar
Carl_61
Iron Contributor
Dec 15, 2022
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...
Formulas and Functions
Macros and VBA
  • mtarler's avatar
    mtarler
    Jan 18, 2023
    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")
mtarler's avatar
mtarler
Silver Contributor
Feb 26, 2023

Carl_61 I updated the macro to a) use a function for that repetitive SELECT CASE for column selection and then b) created a loop for the utilities so then what I did in a) probably isn't even needed lol.
In anycase I also added 2 variables for the columns on the sheet to use for each utility this month and prior month col. I also added the percent value to the building name.
the icon you mentioned isn't there so i ran it using VIEW > MACROS > FillOverview
It ran REALLY slow. Did it always run that slow? Maybe it was just my computer.
BTW, the setting of the location of the Overview tab was using ".NEXT.NEXT" meaning basically 2 tabs over from the active tab which was NOT correct. I change it to be only ".NEXT" but I'm really not a fan of the ".NEXT" to define the location of a Tab as it is prone to problems if someone moves tabs around (as you can see it caused an issue here).

Carl_61's avatar
Carl_61
Iron Contributor
Feb 27, 2023
Matt, I have to wonder about the percentages for the overview. Would these super high percentages be due to the lack of prior month USAGE numbers or due to a formula issue? I am seeing 1300+% numbers that don't make any sense. Or maybe they do. I just was not expecting to see 200+ percentage numbers. The numbers I was expecting to see are numbers that would cause them to fall into the columns they are in based on a comparison between the previous usage and the current. To fall in line with what the column headers are showing. Its just hard to fathom there could be a 600% or otherwise difference in usage between a previous and current month.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Feb 27, 2023
    I can appreciate your concern but those are your numbers. When I do spot checks the calculations seem correct. Please do spot checks yourself and see if there is something wrong. BTW, the Water usage from the previous month looks way off. I have no idea how these values are copied into this workbook/worksheet (for Jan). I see the future months use formulas to 'pull' the prior month data.
    Finally you asked if "high percentages be due to lack of prior month USAGE numbers" and that is NO because a lack of prior month number would be a 0 and cause division by 0 error which I catch and display as LM=0 (Last Month = 0) and you can see some of those also.