add name sheet in formula

[DMR1712]

Hello everyone,

I've been looking in the forum to find an answer to the following, but can't seem to get it to work. Could you help me with this one?

I would like to create an excel file with multiple sheets. The first sheet will be an overview sheet which shows information from the other sheets. The information on the other sheets is always the same for every sheet, so I would like to make a formula which stays the same, but uses another sheet name in it.

For example:

on the overview sheet I have a cell which contains the "formula" (more a reference)

Please Login or Register  to view this content.

. On the cell below this I would like the same information from sheet 2, so

Please Login or Register  to view this content.

. Of course this is just one of the cells I would like referenced. I thought I could type the sheetname once on the same line as where the references go and "add" this into a formula. I've started with Concatenate, but this doesn't result in a valid formula. I then tried Indirect, but can only get REF or VALUE errors. I don't seem to be able to wrap my head around this..

I think this is a very easy issue for you guys (pls don't laugh at me ), so I hope someone can help me. In short, I need a way to add the contents of a cell (i.e. SheetnameOne which I would manually enter into a cell) into a formula so that I only have to type the name of the sheet once rather than do this for all formulas.

Thank you so much in advance if you could help me with this one.

Kind regards,
DMR

[Kaper]

Indirect shall do.
If not enough - you can use nested inside Address

[Pete_UK]

Suppose you listed your sheet names in column A, starting with A2 - the sheet names have to be exactly as they appear on the respective tabs, so be wary of trailing spaces. Then you can have this formula in B2:

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

and this can be copied down column B to get the data from cell B1 of the relevant tabs. Note the use of apostrophes - you need these in case there are any spaces or special characters in your sheet names. Notice also that as the B1 is inside quotes, it will not change as you copy this down (or across).

Hope this helps.

Pete

[DMR1712]

Works like a charm! Thank you so much, I guess I didn't get the &'s and the quotes right..

Thanks again.

Best regards,
DMR

Suppose you listed your sheet names in column A, starting with A2 - the sheet names have to be exactly as they appear on the respective tabs, so be wary of trailing spaces. Then you can have this formula in B2:

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

and this can be copied down column B to get the data from cell B1 of the relevant tabs. Note the use of apostrophes - you need these in case there are any spaces or special characters in your sheet names. Notice also that as the B1 is inside quotes, it will not change as you copy this down (or across).

Hope this helps.

Pete

[Tony Valko]

Here's an easy way to create a list of the sheet names:

List Sheet Sames Using A Formula