Basing a range for a linked spreadsheet on internal criteria

[dstampor]

Hello,

I am trying to modify the following formula:

=VLOOKUP(10,'W:\CARS Accounting\CARS Recs\Prod, Susp, Ext, Misc\2008 Product
Reports\08-Aug\[08-23-08 PRODRPT.XLS]CFPRODRPT082608'!$D$3:$F$19,3)

The part I would like to change is the $3 and $19 part of the formula. I
would like these pieces to reference cells within the spreadsheet. IE:
$D(P5):$F(Q5).

I have tried things like $D"P5" and $D"& P5 &" to no avail. Any help would
be greatly appreciated.

 

[bb67dart]

Take a look at the INDIRECT function, that will accomplish this.

 

[Pete_UK]

You would normally use INDIRECT to build up a reference as a string in
the way that you wish, but that function will only work with workbooks
that are open, and as you show the full path to the file in your
formula it would appear that the file 08-23-08 PRODRPT.xls is closed.

There is a function INDIRECT.EXT available in the free add-in
MoreFunc, which does work with external closed files, so you will have
to use that. Do a Google search for MoreFunc to find where you can
download it from.

Hope this helps.

Pete

 

[Teethless mama]

maybe this...

=VLOOKUP(10,INDIRECT("your_long_path!D"&P5&":F"&Q5),3)

 

[Max]

=VLOOKUP(10,INDIRECT("your_long_path!D"&P5&":F"&Q5),3)

The above requires the source book to be open simultaneously to work by
virtue of using INDIRECT. And if the source book is open, the longish path
would then be removed, only the book & sheet refs remains, viz. the
expression would be more like:

=VLOOKUP(10,
INDIRECT("'[08-23-08 PRODRPT.XLS]CFPRODRPT082608'!"&P5&":F"&Q5),3)

I'd just go with Edit>Replaces if I need to work with closed source books
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

 

[Max]

Typo, missed out the "D", earlier should be:

=VLOOKUP(10,
INDIRECT("'[08-23-08 PRODRPT.XLS]CFPRODRPT082608'!D"&P5&":F"&Q5),3)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---

 

[dstampor]

Thanks for all the help everyone. I went a different route on this, but will
certainly keep this in mind for the future.
--
David Stampor

You would normally use INDIRECT to build up a reference as a string in
the way that you wish, but that function will only work with workbooks
that are open, and as you show the full path to the file in your
formula it would appear that the file 08-23-08 PRODRPT.xls is closed.

There is a function INDIRECT.EXT available in the free add-in
MoreFunc, which does work with external closed files, so you will have
to use that. Do a Google search for MoreFunc to find where you can
download it from.

Hope this helps.

Pete

Hello,

I am trying to modify the following formula:

=VLOOKUP(10,'W:\CARS Accounting\CARS Recs\Prod, Susp, Ext, Misc\2008 Product
Reports\08-Aug\[08-23-08 PRODRPT.XLS]CFPRODRPT082608'!$D$3:$F$19,3)

The part I would like to change is the $3 and $19 part of the formula. I
would like these pieces to reference cells within the spreadsheet. IE:
$D(P5):$F(Q5).

I have tried things like $D"P5" and $D"& P5 &" to no avail. Any help would
be greatly appreciated.