If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Basing a range for a linked spreadsheet on internal criteria
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. -- David Stampor |
#2
|
|||
|
|||
Basing a range for a linked spreadsheet on internal criteria
Take a look at the INDIRECT function, that will accomplish this.
|
#3
|
|||
|
|||
Basing a range for a linked spreadsheet on internal criteria
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 On Aug 26, 11:52*pm, dstampor wrote: 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. -- David Stampor |
#4
|
|||
|
|||
Basing a range for a linked spreadsheet on internal criteria
maybe this...
=VLOOKUP(10,INDIRECT("your_long_path!D"&P5&":F"&Q5 ),3) "dstampor" wrote: 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. -- David Stampor |
#5
|
|||
|
|||
Basing a range for a linked spreadsheet on internal criteria
=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 EditReplaces if I need to work with closed source books -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- |
#6
|
|||
|
|||
Basing a range for a linked spreadsheet on internal criteria
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 --- |
#7
|
|||
|
|||
Basing a range for a linked spreadsheet on internal criteria
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 "Pete_UK" wrote: 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 On Aug 26, 11:52 pm, dstampor wrote: 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. -- David Stampor |
Thread Tools | |
Display Modes | |
|
|