Results 1 to 5 of 5

Thread: VBA question RE: Populating a table based on Data Validation

  1. #1
    Acolyte Bonzopookie's Avatar
    Join Date
    Jul 2016
    Location
    Boston, MA, USA
    Posts
    29
    Articles
    0

    VBA question RE: Populating a table based on Data Validation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,


    I am not very good with VBA programming, however I am somewhat knowledgeable with figuring out Excel formulas. I could really use some help with solving a problem that I am faced with (I am attaching a sample workbook for reference).


    I have 2 worksheets: 1 Formulas, and 1 JanTable- which contains a bunch of records for tracking fitness and activity.


    At the end of each day, the user can choose from a pull-down Actions menu, and select the option of "Daily Totals". When the user selects this item, I would like to populate the total sums from that day, across the worksheet row.


    I created a table on the "Formulas" Page that can correctly sum the totals for each day. However I am not sure if/how I can use VBA code to then take the summed totals from the Formulas page, and place them across the worksheet row where "Daily Totals" was selected from the Actions Menu pull-down.


    Attached is a simplified workbook example, showing my problem in more detail. Hopefully, it will be easier to explain what I am trying to convey here. Please let me know if I need to provide even more details.

    Thank you in advance, for any help you can offer me.


    Sincerely,
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    965
    Articles
    0
    Quote Originally Posted by Bonzopookie View Post
    I created a table on the "Formulas" Page that can correctly sum the totals for each day.
    I'm not so sure;
    1. The formula in cell C2 is:
    =SUMIF(JanTable!$A$3:$A$31,Formulas!$A2,JanTable!G$2:G31)
    The ranges in red are not the same size, and the tops of the ranges are offset (that might be intentional, but I don't think it will do what you might think).
    2. The second red range does not contain 2 $ symbols, but only one. Consequently:
    3. Cell C13 contains:
    =SUMIF(JanTable!$A$3:$A$31,Formulas!$A13,JanTable!G$2:G42)
    which is even more incomprehensible.

    I'll be glad to be shown I'm wrong.

    A problem arises when using a SUMIF formula when the cell that will contain the copy of the result also has the same date in column A; you will see the result in Formulas for that date (currently it's actually the day after result which is wrong - but that's due I think to wrong formulae mentioned in points 1, 2 and 3 above (see next paragraph)).

    To show what I mean with a specific example in your file as it stands: We'll manually copy value of cell C3 of Formulas (1175) to cell G11 of JanTable, but first making a note of the value of cell C4 in Formulas (810). After copying, take a look at cell C4 in Formulas (was 810) which is now 1985!

    On to VBA, you could try the following: Comment-out or delete your current Private Sub Worksheet_Change(ByVal Target As Range) in the JanTable sheet's code-module and replace it with:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
      If Not Intersect(Columns(3), Target) Is Nothing Then
        If Target.Value = "DAILY TOTALS" Then
          With Target.Offset(, 4).Resize(, 8)
            .FormulaR1C1 = "=SUMIF(R3C1:R[-1]C1,RC1,R3C:R[-1]C)"
            '.Value = .Value
          End With
        End If
      End If
    End If
    End Sub
    then change (or re-affirm) a cell in column C to Daily Totals and satisfy yourself that the formulae added to cells in columns G:N of the same row are satisfactory, and if so you can remove the apostrophe at the start of the line:
    '.Value = .Value
    which will then convert those formulae to plain values.
    Last edited by p45cal; 2017-01-06 at 02:01 AM.

  3. #3
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    723
    Articles
    0
    Quote Originally Posted by Bonzopookie View Post
    I would like to populate the total sums from that day, across the worksheet row.
    If I understand well, I would be solve in a different way (without VBA)
    For example, SUM per month and per day
    See attached example
    Attached Files Attached Files
    My personal Web Excel for beginners
    Attention! In the formulas as a separator I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  4. #4
    Acolyte Bonzopookie's Avatar
    Join Date
    Jul 2016
    Location
    Boston, MA, USA
    Posts
    29
    Articles
    0
    Hi p45cal,

    My apologies for writing bad code, as you are 100% correct with your points 1 & 2. I created this example from a more complex workbook. I did not enter the formula correctly, and then I just "drag-copied" everything down, creating those incorrect results.

    I just made the changes, so there is no longer any offset to the formulas. I am going to try your VBA code now, and see if I can make it work. Thanks for your reply.

  5. #5
    Acolyte Bonzopookie's Avatar
    Join Date
    Jul 2016
    Location
    Boston, MA, USA
    Posts
    29
    Articles
    0
    Thank you both for helping me out. I actually combined both of your VBA codes, to accomplish exactly what I wanted: the ability to sum the totals of a row, based on the conditions of a user selected drop-down menu (and the ability to clear cell contents if the menu item changes). Everything works perfectly now, and I could not have accomplished this without both of your help.

    Thanks Again!

Posting Permissions

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