Using 1 cell's value as a reference

[Oddjob]

Excel 2002

In Workbook 1 the value of cell A1 can be variable but always numeric. If,
for instance, A1's current value was ## how can I make cell B1 return the
value of cell Z## in Workbook 2? It would be helpful if the solution didn't
require Workbook 2 to be open.

Thanks for any help

 

[Pete_UK]

If workbook2 was open then you could use INDIRECT like this:

=INDIRECT("'[Workbook 2.xls]Sheet1'!Z"&A1)

However, this function will only work with open workbooks. But, if you
are allowed to install add-ins, then there is a free one called
morefunc (Google will tell you where you can download it from), and
this has the function INDIRECT.EXT which will work with closed
workbooks. You will need to specify the full path to Woorkbook 2, and
you would use it like this:

=INDIRECT.EXT("'full_path\[Workbook 2.xls]Sheet1'!Z"&A1)

Hope this helps.

Pete

 

[Oddjob]

Thanks for your help Pete. I couldn't quite figure out the syntax to make
INDIRECT work properly. As I'd be unable to ensure all users had downloaded
INDIRECT.EXT I think I'll just approach the problem from a different angle.

Thanks for your reply

If workbook2 was open then you could use INDIRECT like this:

=INDIRECT("'[Workbook 2.xls]Sheet1'!Z"&A1)

However, this function will only work with open workbooks. But, if you
are allowed to install add-ins, then there is a free one called
morefunc (Google will tell you where you can download it from), and
this has the function INDIRECT.EXT which will work with closed
workbooks. You will need to specify the full path to Woorkbook 2, and
you would use it like this:

=INDIRECT.EXT("'full_path\[Workbook 2.xls]Sheet1'!Z"&A1)

Hope this helps.

Pete

Excel 2002

In Workbook 1 the value of cell A1 can be variable but always numeric. If,
for instance, A1's current value was ## how can I make cell B1 return the
value of cell Z## in Workbook 2? It would be helpful if the solution didn't
require Workbook 2 to be open.

Thanks for any help

.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Take a look at this thread:

http://groups.google.com/group/microsoft.public.excel/browse_frm/thread/d98d2f2d1af434b2#

which I responded to just before replying to yours. In it Dave
Peterson gives a macro to do this, which you might find easier to get
other users on board.

Pete

Thanks for your help Pete. I couldn't quite figure out the syntax to make
INDIRECT work properly. As I'd be unable to ensure all users had downloaded
INDIRECT.EXT I think I'll just approach the problem from a different angle.

Thanks for your reply

If workbook2 was open then you could use INDIRECT like this:

=INDIRECT("'[Workbook 2.xls]Sheet1'!Z"&A1)

However, this function will only work with open workbooks. But, if you
are allowed to install add-ins, then there is a free one called
morefunc (Google will tell you where you can download it from), and
this has the function INDIRECT.EXT which will work with closed
workbooks. You will need to specify the full path to Woorkbook 2, and
you would use it like this:

=INDIRECT.EXT("'full_path\[Workbook 2.xls]Sheet1'!Z"&A1)

Hope this helps.

.- Hide quoted text -

- Show quoted text -

 

[JLatham]

On other approach to consider then:
Have a hidden sheet with direct links from this workbook over into
Workbook2, and then you can use INDIRECT() within your workbook to references
those cells in your hidden sheet.


AS on your hidden sheet you've got direct links set up to Workbook2 in same
column for easy examination/analysis:
=[Workbook2.xls]Sheet1!Z1
.....
=[Workbook2.xls]Sheet1!Z999

and in your workbook then you could use
=INDIRECT("'My Hidden Sheet'!Z" & A1)

Thanks for your help Pete. I couldn't quite figure out the syntax to make
INDIRECT work properly. As I'd be unable to ensure all users had downloaded
INDIRECT.EXT I think I'll just approach the problem from a different angle.

Thanks for your reply

If workbook2 was open then you could use INDIRECT like this:

=INDIRECT("'[Workbook 2.xls]Sheet1'!Z"&A1)

However, this function will only work with open workbooks. But, if you
are allowed to install add-ins, then there is a free one called
morefunc (Google will tell you where you can download it from), and
this has the function INDIRECT.EXT which will work with closed
workbooks. You will need to specify the full path to Woorkbook 2, and
you would use it like this:

=INDIRECT.EXT("'full_path\[Workbook 2.xls]Sheet1'!Z"&A1)

Hope this helps.

Pete

Excel 2002

In Workbook 1 the value of cell A1 can be variable but always numeric. If,
for instance, A1's current value was ## how can I make cell B1 return the
value of cell Z## in Workbook 2? It would be helpful if the solution didn't
require Workbook 2 to be open.

Thanks for any help

.