Linking multiple sheets to a summary sheet

**Braddigan** · 06-14-2011, 10:55 AM

I have MS office 2011 for Mac and need help with an excel spreadsheet. Ive attached the spreadsheet so hopefully someone can help me out. I currently have 28 sheets for individual clients and need a summary sheet to lookup all the information and put it on one sheet. I already have vlookup set up, but its messy. Hopefully someone can help. Thanks

**JBeaucaire** · 06-14-2011, 08:14 PM

Put this formula in H2 of your summary sheet, then copy across/down the whole table:

=INDEX(INDIRECT("'" & $G2 & "'!G2:K2"), COLUMN(B$1))

You will need to fix the spelling of PJ Hill and OBrien Schofield to match the exact sheetnames.

**Braddigan** · 06-15-2011, 10:24 AM

Hey thank you I appreciate the help. I have one more question, if I decide to add another endorsement to the player and add an extra row, how can i get it to automatically appear in the summary sheet?

**JBeaucaire** · 06-15-2011, 11:11 AM

Your sheet isn't setup that way.

**Braddigan** · 06-15-2011, 11:15 AM

How do you recommend I set it up if I want to make changes like that?

**JBeaucaire** · 06-15-2011, 11:33 AM

I wouldn't use a formula. I would use a macro that "collected" all the data from the player sheets each time you brought up the summary sheet onscreen. The macro would literally copy the data from the other sheets.

You could access any sheet, make a change, then go back to the Summary and just looking at the summary would recreate the data, bringing in your new changes.

I would recommend you rethink the use of the pictures, or at least shrink the summary photo down to a reasonable size... just my opinion.

Then update your workbook to show several rows of fully faked data on a many sheets, upload that. If you're open to using VBA, that's a pretty simple macro to write.