Consulting

Results 1 to 20 of 20

Thread: Worksheet_Change multiple ranges and different formulas

  1. #1
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location

    Worksheet_Change multiple ranges and different formulas

    Hi everyone

    I am building a new file for my company and I find myself in need of multiple Worksheet_Change one one of my sheets.

    The file consist of multiple months, and for each month we are entering a value and we would like it to calculate a value based imputed in the previous cell. And vica versa.

    Some content: We recalculate always from $ to our cent/Kg or maybe we have a target Cent/kg we then calculates to full $-value.

    Example of my sheet.

    KG, TONNES 92,349
    $ 1,000 Cent/KG
    800,000 866
    78,005 84

    We have the above for full 12 months.

    My code now for the first column to create a formula that is $/KG*100.
    Which works when we input a value in our $-column. But we also need to opportunity to directly write the Cent/KG, and then automatically calculate back to $.

    I know how to write to two codes seperately - but my issue is to combine multiple "Worksheet_Change"..
    I tried to use "If Target.Column = xx Then" "Else" but with no luck.


    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Not Intersect(Target, Range("P24:P31", "P33:P34")) Is Nothing Then
        
        Target.Offset(0, 0).Activate
        Dim acc_mth1 As Range
        Set acc_mth1 = ActiveCell
        
        Application.ScreenUpdating = False
        
            If IsNumeric(acc_mth1.Value) Then
                acc_mth1.Offset(0, 1).Formula = "=" & acc_mth1.Address & "/$P$19*100"
            
            End If
                
            If acc_mth1.Value = "" Then
                    acc_mth1.Offset(0, 1).ClearContents
            End If
        End If
    
    
    End Sub
    Any tips for how to solve this best?

    Best regards,
    Rasmus

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    An example workbook with the 12 months (?) and 2 (?) data columns would help

    Showing what doesn't work is not that helpful

    Include the expected results

    Why do you want to use a formula, why not just put in the calculated result?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Meantime, just guessing at a format and data, you could try something like this

    It's not as elegant as it could be


    Option Explicit
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim cFactor As Double
        
        cFactor = Range("C1").Value
        
        Application.EnableEvents = False    '   <<<<<<<<<<<<<<<<<<<< Important
        
        With Target.Cells(1, 1)
            
            Select Case .Column
                Case 3, 5, 7, 9, 11, 13, 15, 17, 19, 21, 23, 25
                    If Len(.Value) = 0 Then
                        .Offset(0, 1).ClearContents
                    Else
                        .Offset(0, 1).Value = .Value / cFactor * 100#
                    End If
                
                Case 4, 6, 8, 10, 12, 14, 16, 18, 20, 22, 24, 26
                    If Len(.Value) = 0 Then
                        .Offset(0, -1).ClearContents
                    Else
                        .Offset(0, -1).Value = .Value * cFactor / 100#
                    End If
                Case Else
                
            End Select
        End With
    
    
        Application.EnableEvents = True
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location
    Example WB.xlsmI don't have the workbook yet. I am currently writing it and trying to include each aspect as I am going forward so I am sure that my ideas works, before going full scale :-)

    However, I have attached the sheet I have for now.

    A solution with the calculated result would also work! And might even be better too.

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    It's easy (I think) to understand the logic for the 1.000 USD columns, but it's REALLY not clear what you want to do if numbers are entered into each of the Cent/KG columns cells, or is it just the first or what?

    Capture.JPG

    For example, Enter 450,000 and use the 125 to replace the 4,000 with 3,600 or what?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    You're trying to overload the Event Sub. IMO, Events subs should only determine which other Procedures to run. This style code structure allows one Event Sub to handle many different event locations.

    Pseudocode. edit to suit

    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.Count > 1 Then Exit sub 'Something changed several ranges at once
       If not Intersect(... rngKGTonnes)... Then Sub 1
       If not Intersect(... rngCentKG).... Then sub 2
    End Sub
    Private sub 1
    Application.EnableEvents = False 'Don't trigger another event
       'Code to fill in Cents/Kg
    Application.EnableEvents = True
    Private sub 2
    Application.EnableEvents = False
       'Code to fill in KG/Tonnes
    Application.EnableEvents = True
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I got tired of writing all those
    With Application
       .blah blah
    End With
    So I use this instead
    Private Function SpeedyCode(GoFast As Boolean)
    'Usage:
    'SpeedyCode True|False
    
    Static Calc As Long
    If Not CBool(Calc) then Calc = xlCalculationAutomatic
    
       With Application
          .ScreenUpdating = Not GoFast
          .EnableEvents = Not GoFast
          If GoFast Then
             Calc = .Calculation
             .Calculation = xlCalculationManual
          Else
             .Calculation = Calc
             .Calculate
             Calc = xlCalculationAutomatic
          End If
       End With
       
    End Function
    I also find this very handy
    Private Sub ResetApplication
       With Application
          .ScreenUpdating = True
          .EnableEvents = True
          .Calculation = xlCalculationAutomatic
    End Sub
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location
    Quote Originally Posted by Paul_Hossler View Post
    "but it's REALLY not clear what you want to do if numbers are entered into each of the Cent/KG columns cells, or is it just the first or what?
    I apologise if my explanation wasn't that clear. But you were correct in you "guess workbook".

    Enter figures in $-column than make a calculation in Cent/Kg with the amount from Cell P19 (in my example wb). And then, if we should enter a number in Cent/KG we should do the opposite, and then calculate back to the $-column.

    Because we have different targets in the company. Some requires e.g. a target of 6 Cent/KG, and then to do the forecast/budget we calculated back using the expected production amounts.

    I hope this clarified it up.


    And thank you for the code! It works how I would like it two.. I might tweak it a bit later to better suit the final workbook.

    Best regards

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Ok, but I'l still not comfortable with the math

    1. Enter P17
    2. Enter P18
    3. If P18 <> 0 then P19 = P17 / P18

    4. Enter Q17
    5. Then what???

    If P19 > 0 then P17 = Q17 / P19 * 100

    ???

    Capture.JPG

    Part of my problem is that I think the UOM are messed up

    F17 looks like UOM = #Tons (manually entered)
    F18 looks like UOM = $/Ton (manually entered)
    F19 looks like UOM = Total cost in $1000 = #Tons * $/Ton /1000 to get cost in K$ (calculated)

    It's the col Q cells where I get confused
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location
    I am not doing any calculations in P17:P19. This is general weights for all production = We are not changing this. But using this as a key figure calculation of Cent/KG on our accounts.

    Therefor, it is in the section "Cost of Goods Sold" in the example WB I need to do the calculations.
    So enter in P24 = calculation in Q24 (Formula: P24/P19*100) and then
    enter in Q24 = calculation in P24 (Formula: Q24*P19/100)

    This will then apply to approx 250 accounts - that all use the total production volume for the specific month in P19 (for April in this example)

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I am not doing any calculations in P17:P19.
    Lookinq at Example WB in post 4, it does look like P19 is being calculated based on P17 and P18

    So I was expecting that is P17 or P18 were changed, then P19 would be updated

    Capture.JPG




    So enter in P24 = calculation in Q24 (Formula: P24/P19*100) and then
    enter in Q24 = calculation in P24 (Formula: Q24*P19/100)
    1. This seems like a circular calculation

    2. Where did the 923,490 in P24 come from? It's 10 times the P19

    Capture2.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location
    P17 is the expected quantity of animals that we slaughter, and P18 is their average weight. This is not changed when when we enter our forecast numbers into the section of "Cost of Goods Sold". But yes, P19 has a calculation, that we use to calculate our key figure Cent/KG. We are a production company and our focus is on Cent/KG when we make our accounts and look at our EBIT. Because it tells us how well we are doing compared to each KG we produce and sell. Therefore, P19 should not change and we are NOT determining P19 alone, but is found from the expected slaughter quantities and average weights. These KG and weight can be different for each month.

    Therefore, we do calculation in P/Q24 that is our accounts. And probably why you find it as a circular calculation but I assure you that it is not :-) Our Purchase of livestock figures in April is 10 times bigger the number in P19 because our forecast is 10USD per KG of meat. For May it is 10.1USD per KG. And that also equals a Cent/KG of 1000.

    Therefore, if we should change the price of meat to say 10.2 for April I would correct P24 to 941,959.8 (92349*10.2) - and in that case I need Q24 to automatically change to 1020 Cent/KG.

    The reason why I need the reverse, is that for some accounts we use a target of Cent/KG in the forecast - so in these cases I would change Cent/KG and have the workbook calculate back with P19 to the $-value.

  13. #13
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    So enter in P24 = calculation in Q24 (Formula: P24/P19*100) and then
    enter in Q24 = calculation in P24 (Formula: Q24*P19/100)
    No!

    If you manually change one, you can calculate the other. But, you can't calculate both
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  14. #14
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location
    Quote Originally Posted by SamT View Post
    No!

    If you manually change one, you can calculate the other. But, you can't calculate both
    Normally I would agree. But in this case, for our purpose I must disagree.
    As I will not input the formula into the cells but only the calculated values, so I don't have any circular calculation issues.

    If you still think it is a totally NO GO - please advise how you would suggest a solution where I can change the cells as per my need explained in the previous reply from today.

  15. #15
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Try this to see if it's closer

    The only cells it handles are the green ones in each of the monthly columns

    Capture.JPG

    Option Explicit
    
    Const rowQty As Long = 17
    Const rowAvg As Long = 18
    Const rowAmt As Long = 19
    Const rowPurch As Long = 24
    
    
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim colTarget As Long, rowTarget As Long
        Dim rQty As Range, rAvg As Range, rAmt As Range, rPurch As Range, rCENT_KG As Range
        
        
        'remember the location of the first cell changed
        With Target.Cells(1, 1)
            colTarget = .Column
            rowTarget = .Row
        End With
        
        'set some range variables to make it easier to keep straight
        Select Case colTarget
            Case 16, 24, 32, 40, 48, 56, 64, 72, 80
                Set rQty = Cells(rowQty, colTarget)
                Set rAvg = Cells(rowAvg, colTarget)
                Set rAmt = Cells(rowAmt, colTarget)
                Set rPurch = Cells(rowPurch, colTarget)
                Set rCENT_KG = Cells(rowPurch, colTarget + 1)
            
            Case 17, 25, 33, 41, 49, 57, 65, 73, 81
                Set rQty = Cells(rowQty, colTarget - 1)
                Set rAvg = Cells(rowAvg, colTarget - 1)
                Set rAmt = Cells(rowAmt, colTarget - 1)
                Set rPurch = Cells(rowPurch, colTarget - 1)
                Set rCENT_KG = Cells(rowPurch, colTarget)
            Case Else
                Exit Sub
        End Select
                
        Application.EnableEvents = False
        
        'calc amount
        If Len(rQty.Value) = 0 Then
            rAmt.ClearContents
        ElseIf Len(rAvg.Value) > 0 Then
            rAmt.Value = rQty.Value / rAvg.Value
        End If
    
    
        'do caclucation that I don't understand
        Select Case colTarget
            Case 16, 24, 32, 40, 48, 56, 64, 72, 80
                If rAmt.Value > 0 Then
                    rCENT_KG.Value = rPurch.Value / rAmt.Value * 100
                End If
            
            Case 17, 25, 33, 41, 49, 57, 65, 73, 81
                rPurch.Value = rCENT_KG * rAmt.Value / 100#
        End Select
    
    
        Application.EnableEvents = True
    
    
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #16
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Normally I would agree.
    Disagree all you want. that won't change the facts.

    As I understand your issue, you enter a new value into one of two Cells on a Row. Then you need the other Cell in that Row to have a value that is calculated from the value entered into the first Cell.

    Is that correct?

    Paul is thinking that you are concerned about Cells in 18 Columns. I am thinking that you are concerned about Cells in two Columns. Which is correct?

    I can tell by Paul's use of Target(1,1) that he expects some critical Cells to be changed via other Procedures that change many Cells below and to the right of the crucial Cells at the same time. Since there is no simple method of properly handling all situations like that, I ignore them with Target.Count > 1, and depend on the other programmer to avoid Side Effects.

    The main difference in our methods, is that Paul places all code in the Event sub, but I like to keep the Event sub available for more events and place the code that does the actual work in different Procedures. Both methods are correct and proper. IMO, until you have to add an Event.
    Last edited by SamT; 06-10-2021 at 12:03 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  17. #17
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by SamT View Post
    Paul is thinking that you are concerned about Cells in 18 Columns. I am thinking that you are concerned about Cells in two Columns. Which is correct?

    The main difference in our methods, is that Paul places all code in the Event sub, but I like to keep the Event sub available for more events and place the code that does the actual work in different Procedures. Both methods are correct and proper. IMO, until you have to add an Event.
    1. The way I understand it, there is a pair of columns for each month. Col P-Q is the pair for April 2021 with 9 months left in 2021

    2. While I agree in general about putting only event code in an event handler, each situation is different.

    I certainly wouldn't include code to (for ex) calculate and print a 5 year acounts summary in the event handler, in THIS case since there are only 3 calculations -- all of which use cells on the sheet to put values on the sheet -- I made a judgement that I wasn't overloading the event handler.

    A standard module sub could have been used, but I felt that the increase in complexity wasn't worth it

    Bottom line = matter of personal style and a judgement call
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  18. #18
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Bottom line = matter of personal style and a judgement call
    Absolutely.

    The way I understand it, there is a pair of columns for each month. Col P-Q is the pair for April 2021 with 9 months left in 2021
    That makes more sense. I have yet to look at the OP's attachment, so I have been missing a lot.

    Going with the premise that a User will not be changing Columns out of date and for more robustness
    If Not Intersect(Target, ThisMonthsColumns) is Nothing Then...
    Private Function ThisMonthsColumns() As Variant
    Set Found  =  Find Todays month
    Set ThisMonthsColumns to Range(Found.Offset(, -2)Resize(1, 4).EntireColumn.Address) 'Include previous month
    Which (different) Procedure to run should be determinable by
    If CBool(Target.Column Mod 2) Then... Else...
    Last edited by SamT; 06-10-2021 at 02:06 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  19. #19
    VBAX Regular
    Joined
    Feb 2021
    Posts
    21
    Location
    Quote Originally Posted by SamT View Post
    As I understand your issue, you enter a new value into one of two Cells on a Row. Then you need the other Cell in that Row to have a value that is calculated from the value entered into the first Cell.

    Is that correct?
    Yes correct. I need both values to depend on each other - and it varies from time to time which of the two we know.

    Quote Originally Posted by SamT View Post
    Paul is thinking that you are concerned about Cells in 18 Columns. I am thinking that you are concerned about Cells in two Columns. Which is correct?
    Two columns for each month. And with full year of accounting it is 24 columns


    Quote Originally Posted by SamT View Post
    The main difference in our methods, is that Paul places all code in the Event sub, but I like to keep the Event sub available for more events and place the code that does the actual work in different Procedures. Both methods are correct and proper. IMO, until you have to add an Event.
    Ok. Thanks for info - I am still quite new to VBA, and I see your point in keeping the Event sub clean. So for know I have chosen to use your earlier suggestion to call a procedure instead.

    Thanks to both of your for your inputs. I managed to make it work with this:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub 
    
    
    If Not Intersect(Target, Range("P24:P30")) Is Nothing Then
    Target.Offset(0, 0).Activate
    Call ForecastPeriod1_DKK
    Target.Offset(1, 0).Activate
    End If
    
    
    ... Ect
    End Sub
    And then calling:
    Sub ForecastPeriod1_DKK()
    
    Application.EnableEvents = False
    
    
    Set acc_mth1 = ActiveCell
    
    
    acc_mth1.Offset(0, 1).Formula = "=" & acc_mth1.Address & "/$P$19*100"
    acc_mth1.Offset(0, 1).Copy
    acc_mth1.Offset(0, 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    
    Application.EnableEvents = True
    
    
    End Sub
    However, for the Event sub. I am thinking of setting it up, so it only works in cells with a specific colour code - instead of specifying ranges.
    Does that sound like a good idea, or do you have a good proposition?

  20. #20
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    However, for the Event sub. I am thinking of setting it up, so it only works in cells with a specific colour code - instead of specifying ranges.
    Does that sound like a good idea, or do you have a good proposition?
    Bad idea. you can only check colors in a Range, so that would be two steps vs one. Colors change: A user might not like the colors you like, Opening a file on a different computer might change the colors. Stick with Ranges and Values.

    I finally opened your example in post #4, and now understand Paul's frustrations. You have many Hard numbers with no reference as to their meaning/association.

    From a programmers viewpoint, there are several issue with the sheet: There are Actuals, Budgets, Forecasts and Variables on the same sheet. Each Monthly table has empty columns in it. Monthly tables; Actual, Budget, and Forecast should be compacted with empty columns only between tables.

    For monthly Table labels use real Dates Formatted as desired, do not use simple Strings like "April" or "May, 2021" .For Forecasting, use 13 Monthly Tables so Jan of next year is present. You can use 14 Tables, Last Dec to Next Jan, but I don't see any advantage.

    A very common amateur mistake is trying to conserve Excel Real Estate. Worksheets are cheap, pennies on the thousand. Use a different sheet for each Class of Data (Actuals, Budgets, Forecasts).

    For Data Tables (Actual, Budget, and Forecast): Row 1 = Month/Table Names; Row 2 = Field names / Column Headers; Row 3 and below = Data. The only formatting is Table and Field Names should be a larger Font and Bold. the Field Names Row should be Underlined. Numerical Columns should be numerically formatted as desired (Accounting, Dollar/Pound/Euro signs, Number of decimals. etc. Do not use thousands separators. Column "A" should be Row Labels, Font Bold, no borders, and Column B should be empty. Data Sheets usually have no Formulas, only Code.

    For Management's pleasure, create Report sheets that pull Data from the necessary Data Sheets. Reports usually have many Formulas, but no code. As you advance in knowledge, create a Dashboard sheet. These Reports and Dashboards can be automatically emailed to Directors as desired. Reports sheets should be formatted to be informative and very eye pleasing.

    The difference between Data and Reports is Data is for Programmers and Reports are for Users.

    The Code to convert Dollars/lb to Cents/Kg can be incorporated into the Actuals Sheet to assist in Formulating Reports.

    As you learn more, you will be wanting to keep User's hands off the Data sheets by creating VBA UserForms so simple Data entry clerks (and others) can input data safely and correctly. The data layout structure above enables this.

    Code Follows Data: So you need to make the Data sheets first, the above Data Structures make the Code algorithms obvious . When you're uncertain of formulas etc.leave the cell empty but with a Comment/Note.

    Upload/attach it when done with the Data Sheets.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •