Dynamic sheet reference in formulas 1

[Jamie2002]

Say I have sheets 1,2 & 3.

Then on Sheet 4 I have a formula that says;

='[c:\windows\desktop\Workbook1.xls]Sheet1'!a1 * '[c:\windows\desktop\Workbook1.xls]Sheet1'!a2

Could I have a cell where I type in Sheet1 or Sheet2 etc and this will in turn alter the formula ?

I have tried;

="'[c:\windows\desktop\Workbook1.xls]"&c1&"'!a1" but this doesn't work.

Thanks

Jamie

 

[xlbo]

Use INDIRECT
where C2 contains "Sheet2"
=Indirect("'[c:\windows\desktop\Workbook1.xls]" & C1 & "'!A1&quot

Essentially, Indirect converts a string to a range reference Rgds
~Geoff~

 

[Jamie2002]

Just to complicate things more.....

I have a sheet with thousands of formulas on it that I would now like to add this indirect to each of them.

For instance I have a formula

='[c:\windows\desktop\workbook1.xls]sheet1'!a1

if I try to use find & replace in two parts as follows;

Find: '[c:
Replace: indirect('[c:

I get an error as the other bracket is not present....?!?

Am I asking for too much here ? Thanks

Jamie

 

[xlbo]

You need 3 steps
1:
Find "="
Replace "'="

This changes a formula to a text string

Then you can re-jig the formula without it erroring in your other 2 steps

HTH Rgds
~Geoff~

 

[xlbo]

oh yeh - then you'll need a 4th step to do
Find "'="
Replace "=" Rgds
~Geoff~