Sum range of cells across columns into single cell on next available row

[sfryeben]

Hello all!

I'm pecking away at this project, and learning a lot about excel and VBA as I go. But, I've come up against a brick wall.

I've managed to write a macro to take data input into a column-oriented form and transpose it into the next available rows in 3 data sheets. In the blank workbook, you will find a functional data entry form with a "submit" button that transposes data from the first column of the form to the next available row in sheet "Data_R", data from the second column of the of the form to the next available row in sheet "Data_I", and the sum of both columns to the next available row in sheet "Data_C", clearing the form for further use.

Now, I need help adding to the macro code that will sum data from the constituent columns of each multi-column category header (Marketing, Intake, etc.) in sheet "Data C", entering the sums under their respective single column headings in the next available row of sheet "Data_S".

I need to do this in order to use Defined Names (utilizing the OFFSET function) to create charts comparing the different categories that automatically update as new data is input via the form. I know how to do this, I just need the multi-column categories summed into single columns to make it work.

Any help is appreciated!

Thanks, everyone!

Blank_T&E.xlsm

[k64]

Can't you just use formulas for this part? For example, in B3 of Data_S, enter

=SUM(Data_C!B4:D4)

[sfryeben]

I could. But, I need the summations to occur automatically each time the macro is run. Multiple people will have their own copies of this workbook, tracking time spent on different tasks. It would be too much work to go in at the end of each day and manually sum all groups in each new row. Also, that would defeat the purpose of automatically updating comparative charts.

I could try a separate macro that does all the summations. But not being too experienced with VBA, I'm wary of running into the issue of making the output of the functions populate the next available row -- that was easy (read: doable) enough to figure out when copying and pasting entire columns/rows, but I'm not so sure about multiple functions per row.

Anyhow, thanks for the advice!

[k64]

Why can't the formulas be there the entire time? You could make them blank out if the rows they are summing are blank.

Sent from my SPH-D710 using Tapatalk

[sfryeben]

Why can't the formulas be there the entire time? You could make them blank out if the rows they are summing are blank.

Sent from my SPH-D710 using Tapatalk

Having the SUM formulas already in each cell is a workaround I've been playing with since your last comment. If you could show me how to blank out cells that are summing blank rows, that would solve a major issue I'm having with creating charts.

Thanks!

[k64]

=IF(Data_C!B4 = "", "", SUM(Data_C!B4:D4))

[sfryeben]

Thanks a lot! I'll see what I can work in.

[k64]

You're welcome. Let me know how it goes.

[sfryeben]

You're welcome. Let me know how it goes.

I just went ahead with embedded formulas in Data_S. Embedded formulas cause zeros to be in the cells of rows that didn't yet have any data... but also cells in rows that had been populated by data that truly summed to zero. I wanted to do away with zeros in unpopulated rows in order to create defined names utilizing the offset function that would automatically update charts as more data was entered, but wanted to keep zeros in populated rows in order to make them more chart-friendly. I chose to go ahead and scrap auto-updating charts, as it is easy enough for my purposes to quickly create charts out of all the data conveniently gathered by the embedded formulas.

The little program works and everyone is happy Thanks again for your suggestions.

[k64]

Glad it works. So you know, you can probably still do the auto-chart update. You can use OFFSET and COUNT to define the range, and use IF statements to blank out the range if it's supposed to be blank.