Create a Summary Sheet

**msommerf** · 12-07-2009, 09:37 AM

Plase can someone help me with my problem? I have a spreadsheet which details an employee number, name, event type and no events as shown in attachment. (SHEET1)

I would like to be able to create a summary sheet in the same workbook which will search through the list of usernames and produce a summary for each user.(SHEET2)

The number of users varies in sheet1 so I would need to handle this dynamically. Can anyone help or point me in the right direction?

Thankyou:

**DonkeyOte** · 12-07-2009, 10:22 AM

IMO you should be using a Pivot Table - pref. in conjunction with a Dynamic Named Range as source such that as you add data to Sheet1 you need only right click on the PT & click Refresh to update.

See attached as proof of concept
(named range is _PTData)

If needed the PT Refresh can be automated with VBA

**msommerf** · 12-07-2009, 10:35 AM

DonkeyOte,

That would appear to work a treat. Never played with Pivot Tables before :-)

Just one question if I may? How would I create a dynamic Named Range?

Regards

Mark.

**DonkeyOte** · 12-07-2009, 10:37 AM

I created one in the attachment which can be reviewed via Insert -> Name -> Define ... or if using 2007 via Formulas Tab -> Name Manager

For more info. on Dynamic Ranges see the link in my sig. (also a PT intro link)