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

1. ## VBA question RE: Populating a table based on Data Validation

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.

Sincerely,

2. Originally Posted by Bonzopookie
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.

3. Originally Posted by Bonzopookie
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

4. 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. 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
•