Automatically updating summary tab with details...

[Guest]

Got a complicated situation here that I think I'd be better of using a macro
to achieve. However if someone knows a slightly easier method (because I'm
not that good with Macros atm) that would be great!

I have a "data" sheet that has a list of current projects. I've used the
OFFSET formula so that when adding/removing projects the named range (used
for data validation) gets updated.

I have a "summary" tab that lists the projects and the number of people
assigned to them that day. Is there anyway of making this table (on the
summary) automatically update (with a total column) when a project is added?

Currently the only way I can think of doing this would be to add a macro so
that users can add/remove projects, and in that macro it does all the work of
updating the summary tab. Am I right about this?

Thanks

 

[Guest]

Hi George

By table do you mean pivot table? If so, I think you need a macro like:

Sub UpdateTable()
Sheets("Summary").PivotTables("Summary").PivotCache.Refresh 'assumning your
table is named Summary
End Sub

I you add this to the shhet code as a change event (Worksheet_Change) in the
shhet code, it should update automagically.

Or, you could use some combination of sumproduct() formulaes in the summary
tab. =SUMPRODUCT((Data!B1:B10)*(Data!A1:A10=C1)) will return the sum of
B1:B10 where the values in A1:A10 match the value in C1.

HTH.

/Sune