Insert text in a formula
 

I have an excel-file with a lot of sheets named ADB, AS, BM etc.

In the first sheet I'm collecting data from the sheets above.
In this sheet the first collumn contains the names of the sheets in the
file.
In the next collumn I'm going to show data from the other sheets. For
instance I use the command =ADB!A5.

Is it possible to get the 'ADB' from the first collumn, so I can copy the
formula?????

I'm thinking of something like this:

=A2!A5 (where A2 contains the text 'ADB').

T

Insert text in a formula
 

Are you wanting to use the same cell from each sheet?

Try this:

A2 = ADB

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

Biff

"petbi" wrote in message
. ..
I have an excel-file with a lot of sheets named ADB, AS, BM etc.

In the first sheet I'm collecting data from the sheets above.
In this sheet the first collumn contains the names of the sheets in the
file.
In the next collumn I'm going to show data from the other sheets. For
instance I use the command =ADB!A5.

Is it possible to get the 'ADB' from the first collumn, so I can copy the
formula?????

I'm thinking of something like this:

=A2!A5 (where A2 contains the text 'ADB').

T

Insert text in a formula
 

Use the INDIRECT function:

=INDIRECT(A1&"!A5")

If any of your sheet names contain spaces, you'll need to add single quotes,
like this:

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

Shown here with additional spaces for clarification (do not include these
spaces): " ' " & A1 & " ' !

HTH,
Elkar


"petbi" wrote:

I have an excel-file with a lot of sheets named ADB, AS, BM etc.

In the first sheet I'm collecting data from the sheets above.
In this sheet the first collumn contains the names of the sheets in the
file.
In the next collumn I'm going to show data from the other sheets. For
instance I use the command =ADB!A5.

Is it possible to get the 'ADB' from the first collumn, so I can copy the
formula?????

I'm thinking of something like this:

=A2!A5 (where A2 contains the text 'ADB').

T