Indirect function

[Peter]

I am trying to use the indirect function. I have created a list of
the names of the worksheets in a workbook, (about 130 Sheets from
various external sources).

I want to obtain the information in, say A1 of each sheet in a column.
First I combined the sheet name with the cell reference then use
Indirect to return the contents of cell A1 in sheet1 as below. My
problem is if the sheet name has a space it does not work.

With out renaming all the sheet, how can I get around this?

Example
A B C
1 A1
2 Sheet_name name & cell ref formula and Result
3 Sheet1 A3&"!"&$a$1 =Indirect(B2) works OK
4 Sheet 2 A4&"!"&$a$1 =Indirect(B3) #REF! (Does not work)
5


Looking for help and regards

Peter

 

[Harlan Grove]

With out renaming all the sheet, how can I get around this?

Example
A B C
1 A1
2 Sheet_name name & cell ref formula and Result
3 Sheet1 A3&"!"&$a$1 =Indirect(B2) works OK
4 Sheet 2 A4&"!"&$a$1 =Indirect(B3) #REF! (Does not work)

Make your col B formulas

B3: ="'"&A3&"'!"&$A$1
B4: ="'"&A4&"'!"&$A$1

etc.

 

[Peter]

With out renaming all the sheets, how can I get around this?

Example
A B C
1 A1
2 Sheet_name name & cell ref formula and Result
3 Sheet1 A3&"!"&$a$1 =Indirect(B2) works OK
4 Sheet 2 A4&"!"&$a$1 =Indirect(B3) #REF! (Does not work)

Make your col B formulas

B3: ="'"&A3&"'!"&$A$1
B4: ="'"&A4&"'!"&$A$1

etc.

Thanks Harlan

The expression works perfectly now. I assume the extra "'" turn the
name into a string excel can recognise.

Regards

Peter