I have a main worksheet where I have to collect data from cells in 30
worksheets.
How can I make my life easier without copy and paste the info cell by cell.

for example:
in the main worksheet I have a row in which I have to show the collect the
data form the other 30 worksheets.
The data is mentioned in 30 worksheet in the same cell (in two columns),
however the worksheet is named 1, 2, 3, 4, etc.

The formula to pick the data is now '2'!$B$5 how can i automatically
generate the next worksheet, nr. 3, so '3'!$B$5???????

Press a Shift key and click on the last sheet it will select all sheets, then
enter your formula.

To ungroup sheets, right click on sheet tab and select Ungroup sheets



"Maarten from Holland" wrote:

I have a main worksheet where I have to collect data from cells in 30
worksheets.
How can I make my life easier without copy and paste the info cell by cell.

for example:
in the main worksheet I have a row in which I have to show the collect the
data form the other 30 worksheets.
The data is mentioned in 30 worksheet in the same cell (in two columns),
however the worksheet is named 1, 2, 3, 4, etc.

The formula to pick the data is now '2'!$B$5 how can i automatically
generate the next worksheet, nr. 3, so '3'!$B$5???????

=INDIRECT(COLUMN()&"!B5")

Entered in A1 of summary sheet and copied across row 1

Adjust COLUMN() to suit depending upon where you start.

You may need COLUMN()+1 or somesuch.


Gord Dibben MS Excel MVP


On Tue, 21 Nov 2006 13:24:01 -0800, Maarten from Holland <Maarten from
wrote:

I have a main worksheet where I have to collect data from cells in 30
worksheets.
How can I make my life easier without copy and paste the info cell by cell.

for example:
in the main worksheet I have a row in which I have to show the collect the
data form the other 30 worksheets.
The data is mentioned in 30 worksheet in the same cell (in two columns),
however the worksheet is named 1, 2, 3, 4, etc.

The formula to pick the data is now '2'!$B$5 how can i automatically
generate the next worksheet, nr. 3, so '3'!$B$5???????