I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all 30
tabs and I know there is an easier way but I can't remember how. I seem to
recall Index but I don't completely understand how it works. Any suggestions?

Or am I thinking of Indirect?

"HJ" wrote:

I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all 30
tabs and I know there is an easier way but I can't remember how. I seem to
recall Index but I don't completely understand how it works. Any suggestions?

If you sheet names are sheet1,sheet2..... sheet30

Then copy this formula in A1 and copy down

=INDIRECT("Sheet" & ROW()&"!A1")





--
Regards Ron de Bruin
http://www.rondebruin.nl


"HJ" wrote in message ...
I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all 30
tabs and I know there is an easier way but I can't remember how. I seem to
recall Index but I don't completely understand how it works. Any suggestions?

Probably INDIRECT with the sheet names in say A1:A30

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"HJ" wrote in message
...
Or am I thinking of Indirect?

"HJ" wrote:

I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in
all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all
30
tabs and I know there is an easier way but I can't remember how. I seem
to
recall Index but I don't completely understand how it works. Any
suggestions?

Yes

--
Don Guillett
SalesAid Software

"HJ" wrote in message
...
Or am I thinking of Indirect?

"HJ" wrote:

I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in
all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all
30
tabs and I know there is an easier way but I can't remember how. I seem
to
recall Index but I don't completely understand how it works. Any
suggestions?

Bear with me here as I try to understand and get this to work. I am getting
a reference error so I wanted to be a bit more specific so I can figure out
what I am missing.

I have 30 tabs all with different Project names (i.e. Buick, Toyota, Ford,
Honda, etc.). On the summary tab, I am trying to pull cell B29, B30, B31,
B8, B13, B14, etc. for each worksheet onto the summary worksheet. Any
thoughts?

"Bob Phillips" wrote:

Probably INDIRECT with the sheet names in say A1:A30

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"HJ" wrote in message
...
Or am I thinking of Indirect?

"HJ" wrote:

I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in
all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all
30
tabs and I know there is an easier way but I can't remember how. I seem
to
recall Index but I don't completely understand how it works. Any
suggestions?

Hi HJ

Use this macro if you want
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"HJ" wrote in message ...
Bear with me here as I try to understand and get this to work. I am getting
a reference error so I wanted to be a bit more specific so I can figure out
what I am missing.

I have 30 tabs all with different Project names (i.e. Buick, Toyota, Ford,
Honda, etc.). On the summary tab, I am trying to pull cell B29, B30, B31,
B8, B13, B14, etc. for each worksheet onto the summary worksheet. Any
thoughts?

"Bob Phillips" wrote:

Probably INDIRECT with the sheet names in say A1:A30

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"HJ" wrote in message
...
Or am I thinking of Indirect?

"HJ" wrote:

I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in
all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all
30
tabs and I know there is an easier way but I can't remember how. I seem
to
recall Index but I don't completely understand how it works. Any
suggestions?

Put the sheet names in A2:A31

Put the cells to pick up in B1:Z1

In B2 add =INDIRECT("'"&$A2&"'!"&B$1)

and copy down and across

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"HJ" wrote in message
...
Bear with me here as I try to understand and get this to work. I am
getting
a reference error so I wanted to be a bit more specific so I can figure
out
what I am missing.

I have 30 tabs all with different Project names (i.e. Buick, Toyota, Ford,
Honda, etc.). On the summary tab, I am trying to pull cell B29, B30, B31,
B8, B13, B14, etc. for each worksheet onto the summary worksheet. Any
thoughts?

"Bob Phillips" wrote:

Probably INDIRECT with the sheet names in say A1:A30

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"HJ" wrote in message
...
Or am I thinking of Indirect?

"HJ" wrote:

I have a workbook with 30 tabs, all of which have the same format.
I'm
trying to create a summary worksheet that references the same cells
in
all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for
all
30
tabs and I know there is an easier way but I can't remember how. I
seem
to
recall Index but I don't completely understand how it works. Any
suggestions?

Thanks Ron. That works like a charm.

Thanks to all who posted. I was trying to find a way where I wouldn't have
to define the tab names since new ones will constantly be added. I do have a
better understanding of Indirect now. Thanks!

"Ron de Bruin" wrote:

Hi HJ

Use this macro if you want
http://www.rondebruin.nl/summary.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"HJ" wrote in message ...
Bear with me here as I try to understand and get this to work. I am getting
a reference error so I wanted to be a bit more specific so I can figure out
what I am missing.

I have 30 tabs all with different Project names (i.e. Buick, Toyota, Ford,
Honda, etc.). On the summary tab, I am trying to pull cell B29, B30, B31,
B8, B13, B14, etc. for each worksheet onto the summary worksheet. Any
thoughts?

"Bob Phillips" wrote:

Probably INDIRECT with the sheet names in say A1:A30

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"HJ" wrote in message
...
Or am I thinking of Indirect?

"HJ" wrote:

I have a workbook with 30 tabs, all of which have the same format. I'm
trying to create a summary worksheet that references the same cells in
all 30
worksheets. I don't want to do the ='Sheet1' then =Sheet2 etc. for all
30
tabs and I know there is an easier way but I can't remember how. I seem
to
recall Index but I don't completely understand how it works. Any
suggestions?