# Solved: Excel - summary sheet in multiple sheeted file



## alcosaint (Feb 2, 2006)

Another one in my 'got to be an easier way' series of Excel queries...

Using Excel2000 - I have a file with 60 or so sheets. Each sheet represents a different person and is named accordingly (sheet 1 is called "summary", sheet 2 "J Bloggs", sheet 3 "A Smith" etc). 

It's important that the sheets are not "sheet1", "sheet2" etc because there are so many of them and because they are updated on a weekly basis, so the sheet names need to be meaningful.

On the summary sheet I want to pick up various pieces of info from every one of the 60 'named' sheets. There's nothing complicated about that bit (e.g. ='J Bloggs'!$C$50).

HOW THOUGH, do I copy and paste the "='J Bloggs'!$C$50" into an adjacent cell so that it picks up the data in cell C50 of the 'A Smith' sheet? I.e. I want to carry out multiple paste operations, incrementing each successive paste by one sheet...


----------



## bomb #21 (Jul 1, 2005)

What's "adjacent" -- down or across?


----------



## alcosaint (Feb 2, 2006)

below, but didn't mention it because I can transpose it if you gave me a solution for "acrosswise"...


----------



## bomb #21 (Jul 1, 2005)

So:

A1 on J Bloggs = "Jim"

A1 on A Smith = "Alco"

On Sheet1, A1 = "J Bloggs", A2 = "A Smith".

In B1:

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

In B2:

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


----------



## bomb #21 (Jul 1, 2005)

Sorry saint, make that:

On "*Summary*", A1 = "J Bloggs", A2 = "A Smith".



Still, good results last night.


----------



## alcosaint (Feb 2, 2006)

I knew it (in the sense that I knew it, but I didn't)!

Before I created this new post I searched the forums and also looked at my last post a couple of months back - in that post you introduced me to the 'indirect' function and I had a feeling that this problem might be solved along those lines, but I was looking at it slightly differently. 

Your method allows me to pick up information from each sheet, AFTER I have typed in the 60 or so names on the summary sheet and this will ultimately save me a lot of time.

But...........do you know if there's a way to avoid typing in the 60 names and simply have some kind of formulae which increments sheet names using a reference to the order in which they appear in the file? It's another one of these things that seems to crop up from time to time on a variety of spreadsheets I use.


----------



## bomb #21 (Jul 1, 2005)

Here's the sample wb.

C50 on "J Bloggs" = "abc"
C50 on "A Smith" = "xyz"

Copy Summary!B2 to Summary!B3.

Gotta run.


----------



## bomb #21 (Jul 1, 2005)

alcosaint said:


> ... Your method allows me to pick up information from each sheet, AFTER I have typed in the 60 or so names on the summary sheet and this will ultimately save me a lot of time. But...........do you know if there's a way to avoid typing in the 60 names and simply have some kind of formulae which increments sheet names using a reference to the order in which they appear in the file? It's another one of these things that seems to crop up from time to time on a variety of spreadsheets I use.


Not with formulas, AFAIK. Search David McRitchie's (MVP) site for "how to list sheets in wb" *code*.


----------



## alcosaint (Feb 2, 2006)

posts getting crossed in the ether.....I'll crack on with your method. Frustrated that I can't get a formula to recognise a increment in a sheet reference without first listing the sheet names on the summary, but you've given me a solution that solves 99% of the problem and I' just being a bit greedy now

(and yes, good results last night, for both countries - still waiting for France or Italy to slip up, but looking at remaining fixtures in Scotland's group I'm not particularly confident)

Cheers

Al


----------



## bomb #21 (Jul 1, 2005)

David's a genius, his style can be ... err. very ... "stylised" tho'. 

Try something like this on a temp inserted sheet:

Sub SheetNames()
Dim WS As Worksheet
For Each WS In ActiveWorkbook.Sheets
Range("A" & WS.Index) = WS.Name
Next WS
End Sub

Don't come home too soon.  

EDIT: add-in wise, ASAP has this.


----------



## alcosaint (Feb 2, 2006)

Didn't really know what the result of that macro was going to be just by looking at the code, so I copied it into vb and ran the macro. 

A combination of that macro and the indirect function is going to be so helpful in future. In fact I'll confidently state right now that I'll be using this combination in a whole load of unnecessary places in the very near future....

Come to think of it, I wonder if I can create some new spreadsheets solely for the purpose of using that macro/formula......... I'll bet our (house building) site labourers would love to use a spreadsheet set up to record the number of bricks they've shifted from one spot to another, analysed by house type, location (by geographical site and by street obviously), brick type - oh the possibilities are endless....

On a momentarily serious note - thanks once again Bomb, you never fail! 

PS - was my earlier comment about you solving 99% of my problem the difference between you a) moving on to something else and b) getting the next bit of the solution from david's site LOL! 

LIKE THE LOOK OF THE ASAP ADD-IN AND SOME OF THE 'COLOUR COUNTING' ETC FUNCTIONS - don't know if I can justify this for the spreadsheets I use at present, but still looks very cool..


----------



## bomb #21 (Jul 1, 2005)

alcosaint said:


> ... getting the next bit of the solution from david's site LOL!


  

I'll have you know I wrote it myself -- hence the crappy syntax.  

Enjoy ... "T"TYL.


----------



## alcosaint (Feb 2, 2006)

HA HA HA HA! Until next time........................  :up:


----------

