Summary Sheet from Multiple Worksheets or Creating a Formula based on text

[dsunter]

Morning All.

This is my first post to the Forum, my name is Dave and I've been using Excel for 15 years now.

The one thing I've never been able to really achieve is a summary sheet based on Worksheet names.

For example I have 5 worksheets. Named "001", "002", "003" etc.

I want to create another workbook which summarises all the data from each worksheet. i.e.

A B
001 £233 £12
002 £200 £10
003 £300 £5

I think I managed this years ago with a text string converted to a formula


Has anyone ever tried what I'm trying? Also it would be good if this could recognise new worksheets.

Pulling what's left of my hair out trying to figure this out.

Many Thanks

Dave

[arlu1201]

Would you want to try it using VBA? It will be very simple and fast.

[Andrew-R]

You'll need to use the INDIRECT function, so if, say, cell A1 contained the value "001" then:

=INDIRECT(A1 & "!A1")

Would return the value in cell A1 on the sheet "001".

Or you could do something like:

=SUM(INDIRECT(A1 & "!A:A"))

Which would sum all of the values in column A on sheet "001".

Does that point you in the right direction?

[dsunter]

Hi Andrew

Thanks for that. In all these years I've never used the INDIRECT function.

What would I need to do if the sheetname had a space in it? i.e. "003 - Dave"

Thanks Again

Dave

---------- Post added at 11:48 AM ---------- Previous post was at 11:47 AM ----------

Hi Arlette

I'll give the VB option a go as well if you have some code. Will it work ok on MAC?

Thanks

Dave

[Andrew-R]

If the sheet name has a space in, e.g. "003 - Dave" (still in cell A1) you'd have to enclose it in single quotes in the INDIRECT:

=INDIRECT("'" & A1 & "'!A1")

[arlu1201]

I am not sure if it will work on a MAC as i have never used one.

[dsunter]

If the sheet name has a space in, e.g. "003 - Dave" (still in cell A1) you'd have to enclose it in single quotes in the INDIRECT:

=INDIRECT("'" & A1 & "'!A1")

Fantastic! Thanks Andrew!

Many Thanks

Dave

[Cutter]

@ dsunter

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[arlu1201]

If you need code, try this

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[dsunter]

@ dsunter

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Sorry, all done