I have a spreadsheet which links to cells in several other worksheets. Is
there a way I can select which worksheet to link to based on information in a
different cell? For instance, I want cell c5 in worksheet A to link to cell
ab4 in the worksheet identified in cell c1 in worksheet A.

Thanks,

jnix

Hi

See the help for the indirect function
with a sheet name in A1

=INDIRECT($A$1 & "!G3")




--
Regards Ron de Bruin
http://www.rondebruin.nl


"jnix" wrote in message ...
I have a spreadsheet which links to cells in several other worksheets. Is
there a way I can select which worksheet to link to based on information in a
different cell? For instance, I want cell c5 in worksheet A to link to cell
ab4 in the worksheet identified in cell c1 in worksheet A.

Thanks,

jnix

This helped and again openup a range of needed simplifications for me..
However when using the suggested =INDIRECT($A$1 & "!G3") the file refered
to should be open or else I get the #ref curse...

If there are a way around this it would help me quite a lot!

Regards
Gerrit



"Ron de Bruin" wrote:

Hi

See the help for the indirect function
with a sheet name in A1

=INDIRECT($A$1 & "!G3")




--
Regards Ron de Bruin
http://www.rondebruin.nl


"jnix" wrote in message ...
I have a spreadsheet which links to cells in several other worksheets. Is
there a way I can select which worksheet to link to based on information in a
different cell? For instance, I want cell c5 in worksheet A to link to cell
ab4 in the worksheet identified in cell c1 in worksheet A.

Thanks,

jnix

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

You may want to use a macro that would build the formulas to retrieve the values
from a closed workbook (on a hidden worksheet), then retrieve from there?

Gerrit wrote:

This helped and again openup a range of needed simplifications for me..
However when using the suggested =INDIRECT($A$1 & "!G3") the file refered
to should be open or else I get the #ref curse...

If there are a way around this it would help me quite a lot!

Regards
Gerrit

"Ron de Bruin" wrote:

Hi

See the help for the indirect function
with a sheet name in A1

=INDIRECT($A$1 & "!G3")




--
Regards Ron de Bruin
http://www.rondebruin.nl


"jnix" wrote in message ...
I have a spreadsheet which links to cells in several other worksheets. Is
there a way I can select which worksheet to link to based on information in a
different cell? For instance, I want cell c5 in worksheet A to link to cell
ab4 in the worksheet identified in cell c1 in worksheet A.

Thanks,

jnix




--

Dave Peterson

Thanks million.. I have managed to found "pull" and it works!.. it is
however quite slow as I need to 'pull' about 35 cells from one workbook and
then I have a number of workbooks (up to 40)... so it runs for ages..

The values does not change that often and the 'update links' worked quite
well in the past.. Switching off auto calc and using F9 does not solve the
problem, because it does not apply only to 'update links'.. and f9 for a
small change takes then for ages..

I will investigate the option to solve this problem with a macro on a hidden
sheet.. Let me know if you got any additional advice, or else I will take it
it from here...

THANKS!!!



"Dave Peterson" wrote:

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

You may want to use a macro that would build the formulas to retrieve the values
from a closed workbook (on a hidden worksheet), then retrieve from there?

Gerrit wrote:

This helped and again openup a range of needed simplifications for me..
However when using the suggested =INDIRECT($A$1 & "!G3") the file refered
to should be open or else I get the #ref curse...

If there are a way around this it would help me quite a lot!

Regards
Gerrit

"Ron de Bruin" wrote:

Hi

See the help for the indirect function
with a sheet name in A1

=INDIRECT($A$1 & "!G3")




--
Regards Ron de Bruin
http://www.rondebruin.nl


"jnix" wrote in message ...
I have a spreadsheet which links to cells in several other worksheets. Is
there a way I can select which worksheet to link to based on information in a
different cell? For instance, I want cell c5 in worksheet A to link to cell
ab4 in the worksheet identified in cell c1 in worksheet A.

Thanks,

jnix




--

Dave Peterson

=INDIRECT(C1&"!AB4")

--
Regards,
Dave
<!--


"jnix" wrote:

I have a spreadsheet which links to cells in several other worksheets. Is
there a way I can select which worksheet to link to based on information in a
different cell? For instance, I want cell c5 in worksheet A to link to cell
ab4 in the worksheet identified in cell c1 in worksheet A.

Thanks,

jnix