Excel Formula - Copying Sheets

[Lagoon1]

Hi
Is there anyway of copying and dragging the cell so that the formula such as Sheet2!A1 in cell A1 becomes Sheet3!$A$1 in cell A2.

Thanks very much.

Cheers,

Lagoon1

 

[KenWright]

You can use the INDIRECT function to achieve this.

Assuming you had 5 sheets named Sheet1, Sheet2, Sheet3 etc

Then with 1,2,3,4,5 in say cells B21:B25, in say A"1 you could put:-

=INDIRECT("Sheet"&B21&"!A1&quot and copy down

This will build references such as

=Sheet1!A1
=Sheet2!A1
=Sheet3!A1
=Sheet4!A1 etc

The INDIRECT function allows you to create a reference from a string of text, albeit a concatenated one.

If you were actually starting on row 1 on any sheet with your formula, you could even use the ROW() function to do this:-

=INDIRECT("Sheet"&ROW()&"!A1&quot and copy down

which again will build the same references. Doesn't have to be numbers either, as all you need is some way of creating the strings that make up the normal references.

With say Jan, Feb, Mar etc in a set of cells, you could build up references to sheets that were named after the months of the year and so on.

Regards
Ken...................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Lagoon1]

Hi KenWright
Thanks very much for the valuable information.
Works like a charm.

Cheers,

Lagoon1.

 

[KenWright]

My pleasure

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------