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")
So here is the formula I am using =(ROUND(K6,3)=ROUND(K$3,3)) with conditional formatting but nothing is happening. My cells are to fill in green with font yellow if K6 & K3 match each other. My cells are just staying with no fill or change in Font Color. I tried a simple formula on the numbers as =IF(K6=K3,"True", "False") and it returns a False even though the numbers in both cells look the same. K3 is formatted as a percentage with 5 decimal places and k6 is just a general number. If I try to format it as a percentage it changes it to 8.77300% at 5 decimal places. Its like I'm comparing apples to oranges instead of apples to apples. Do not understand why this is happening??
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")
=(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")
- 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. - 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.
- 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.
- I had no idea of the .NEXT.NEXT So I would have had no idea of what you are speaking of. As always if there is a better way I am fully up to it. And YES, it has always run slow. Have no idea why. I was hoping for better performance but I have no idea how to change that myself. Hopefully the changes you made will keep the data within their lanes. For some reason it use to create its own column just tot he right, outside the defined columns. I have not checked yet but was wondering if you were able to get the %# included with the building number? I guess I'll find out soon otherwise.
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).In case you cannot see the macro for the Overview Tab here it is:
Sub FillOverview()
Dim w1 As Worksheet ' month sheet
Dim w2 As Worksheet ' month overview sheet
Dim r As Long ' source row number
Dim m As Long ' last row number
Dim s As Long ' target row number
Dim c As Long ' target column number
Dim v1 As Double ' current usage
Dim v2 As Double ' previous usage
If Len(ActiveSheet.Name) <> 3 Then
MsgBox "Please select a month sheet, then try again!", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Set w1 = ActiveSheet
m = w1.Range("A" & w1.Rows.Count).End(xlUp).Row
Set w2 = w1.Next.Next
w2.Range("B5:ZZ355").ClearContents
For r = 6 To m
' Electric
v1 = w1.Range("H" & r).Value
v2 = w1.Range("BI" & r).Value
Select Case v1
Case 0
c = 2
Case Is > v2 * 1.5
c = 9
Case Is > v2 * 1.4
c = 8
Case Is > v2 * 1.3
c = 7
Case Is > v2 * 1.2
c = 6
Case Is > v2 * 1.1
c = 5
Case Is > v2
c = 4
Case v2
c = 3
Case Is < v2 * 0.5
c = 15
Case Is < v2 * 0.6
c = 14
Case Is < v2 * 0.7
c = 13
Case Is < v2 * 0.8
c = 12
Case Is < v2 * 0.9
c = 11
Case Is < v2
c = 10
End Select
s = w2.Cells(w2.Rows.Count, c).End(xlUp).Row + 1
If s < 5 Then s = 5
w2.Cells(s, c).Value = w1.Range("A" & r).Value
' Gas
v1 = w1.Range("X" & r).Value
v2 = w1.Range("BX" & r).Value
Select Case v1
Case 0
c = 2
Case Is > v2 * 1.5
c = 9
Case Is > v2 * 1.4
c = 8
Case Is > v2 * 1.3
c = 7
Case Is > v2 * 1.2
c = 6
Case Is > v2 * 1.1
c = 5
Case Is > v2
c = 4
Case v2
c = 3
Case Is < v2 * 0.5
c = 15
Case Is < v2 * 0.6
c = 14
Case Is < v2 * 0.7
c = 13
Case Is < v2 * 0.8
c = 12
Case Is < v2 * 0.9
c = 11
Case Is < v2
c = 10
End Select
c = c + 15
s = w2.Cells(w2.Rows.Count, c).End(xlUp).Row + 1
If s < 5 Then s = 5
w2.Cells(s, c).Value = w1.Range("A" & r).Value
' Water
v1 = w1.Range("AG" & r).Value
v2 = w1.Range("CG" & r).Value
Select Case v1
Case 0
c = 2
Case Is > v2 * 1.5
c = 9
Case Is > v2 * 1.4
c = 8
Case Is > v2 * 1.3
c = 7
Case Is > v2 * 1.2
c = 6
Case Is > v2 * 1.1
c = 5
Case Is > v2
c = 4
Case v2
c = 3
Case Is < v2 * 0.5
c = 15
Case Is < v2 * 0.6
c = 14
Case Is < v2 * 0.7
c = 13
Case Is < v2 * 0.8
c = 12
Case Is < v2 * 0.9
c = 11
Case Is < v2
c = 10
End Select
c = c + 30
s = w2.Cells(w2.Rows.Count, c).End(xlUp).Row + 1
If s < 5 Then s = 5
w2.Cells(s, c).Value = w1.Range("A" & r).Value
Next r
Application.ScreenUpdating = True
End SubBecause I have added columns and maybe even some rows since this macro was somewhat working, I'm sure References will have to be changed or added. As always, any changes or if there is a better way to accomplish what I've I got, I am wide open to it.
Carl- I will continue to test this Matt and provide you feedback. As you have already most likely figured out, there are several facets to this System. In addition to the things you have helped me with there are the Overview Tabs for each month that gives me a snap shot (if you will) of where each building falls with its usage of Utilities. As you can see the columns for each Utility shows where the usage falls based on categories. One of the issues I was having was data not falling into it's proper columns and categories. Another words, sometimes data would drop into columns just to the right of the right most column per utility grouping. When the building number was dropping into the columns according to their usage percentage groupings I had wanted to be included with the building number the actual percentage of usage that caused the building number to land in that column. Example: 520/52%, 837/23.52%, 838A/12.67% and so on. This would give visual assurance the building number was in the correct column. Do you have a workbook with the Overview Tabs in it? Do the workbooks I have sent you have the Icons at the top left of the workbook? This is where I have been placing the Macro's for a 1 click launch.
- Your the man Matt. Thank you so much.
Hello Matt, so far so good. One thing I am seeing however is that for the month of Sep 2022 we are falling short 3 buildings. The 3 buildings missing from the UMS Workbook after the copy, using the macro, from the actual Water Report (FY22 MFH SEP) are on the FY22 MFH Sep Report, but do not appear in the UMS workbook. In this case they are the last 3 buildings, after sorting (925B, 927A, 927B). There are 317 building but only 314 buildings are being posted in the UMS. So far, Sep 2022 is the only month I've tested.
- Thank you Matt. I’ll check it out in the morning. It’s 9 pm Texas time & I believe you are in New York, correct? So it’s 19 pm there if I am correct about your location.
- All is good Matt. It just happens that way sometimes. I know that when I highlight existing data and Clear Contents then run the Macro, the macro appears to be dropping in 2 columns of building numbers there by offsetting the placement of the data. I'd prefer that not to happen if at all possible. I'd like to rerun the macro as described as many times as I'd like if I needed to.
- ok sry for the issues. I have no idea why it would be doing that and not sure I understand all that you mentioned, but guessing once I dig in (especially if I can re-create your issues) I will find some dumb mistake/assumption I made.
- As I play around with testing I am noticing that data from the Workbook is not showing up or not calculating properly. I am assuming that cell referencing for Electric, Gas & Water may have to be changed in the LET() formulas. Would this be a correct statement? I ask this as entries I have made for water are not appearing on the Jan Tab. I've checked the data over and over and there is no reason I can see to why its not showing up under columns AO thru AS other than maybe the cell referencing in the LET() function/formula.
- Matt, I did some additional testing and found a couple of things. 1) I found that if I clear contents columns of cells for which data has already been there that it double pastes the building number creating an offset. 2) I tested using a completely blank month and the Usage numbers are still that of the current month read numbers. The Usage numbers are not being calculated for some reason.
- So I tried the macro using the Mar Tab and Data is being copied into the columns however the data in AJ & AK is the same. Another words, the data in AK is supposed to be the result of AJ - AI which is the Water Usage.
- Thank you Matt, Question a). My thinking on this the last time was as I knew how the operation was happening. I would open the report I wanted, click the macro I wanted to run from the quick access bar, watch the operation of the macro, see the results and then copy and paste the results into the UMS workbook into the desired location. When I closed the workbook the data was collected from I would never save it in order to preserve the original workbook/report so the macro could be run in the future if needed. I finally realized you were doing the same thing as I never really looked at the 1352 report to see what new was going on so I was not grasping what your macro was doing. I am good with your system as now I understand what is being done. The bottom line is, as long as the data is being collected and placed where it needs to go I am good. I would say add the water macro to the Prep Data button but I don't typically get the reports at the same time. b). Yes, I need the Prior Reading, The Current Reading and the current usage. The current usage can be pulled from the report or calculated. Current Reading minus Previous Reading to get current Usage. If its more efficient to just grab what is showing on the report that is fine. As you can see there are columns for that data. c). Yes, the data needs to be arranged so it gets placed properly into the columns it goes into. Like I stated above, the data just needs to be configured so it gets placed into the proper columns against the correct buildings. If this can be done without moving it around on the water report that works fine for me. This is all because the macro is doing all the sorting and moving with out me having to manually copy and paste.
- fyi, i went over the code and file this weekend and have a grasp of it. I have a few thought/questions:
a) it is set up, like the other, assuming the data sheet is 'active'. This is fine but last time you had issue understanding that the location of the macro and the active sheet are separate and that you can have the data sheet 'active' but run a macro from a different sheet. So I added a whole section in the macro on 'finding' the correct data sheet. Do I need to do that again?
b) the macro has you 'select' the month you want but now (if i understand correctly) you want both the month of usage AND the prior and present readings. do you still want/need that or should it just assume the most recent month of data available?
c) it also appears to copy and move data around on the workbook so you can copy and paste easier. if I copy and paste for you do you still want/need the data columns moved into the 'correct' sequence? - I want to thank you for all the help you are giving me to get this thing figured out and finished. I know this is taking away time from what you would otherwise do and I truly appreciate it.
