VBA question RE: Populating a table based on Data Validation

B

Bonzopookie

New member
Joined
Jul 15, 2016
Messages
29
Reaction score
0
Points
0
Location
Boston, MA, USA
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,
 

Attachments

  • Example_VBA_Fitness_Tracker.xlsm
    28.3 KB · Views: 18
Bonzopookie said:
I created a table on the "Formulas" Page that can correctly sum the totals for each day.
Click to expand...
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:
Bonzopookie said:
I would like to populate the total sums from that day, across the worksheet row.
Click to expand...
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
 

Attachments

  • bonzopookie-navic7179.xlsx
    44.3 KB · Views: 6
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.
 
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!
 
You must log in or register to reply here.
Back
Top