vlookup/concatenate/match?/nightmare

[mabc]

I will do my best to explain this as I know someone out there will know how to fix this as always.

in column c (c4) I have a file name.
in column b (b2) I have a location number.
in column d (d3) I need a lookup (?) formula that will say something like:

=vlookup(b2,'R:\reports\sr\c4,13) but all i get is a value error.

did i explain that one right?


the user enters location in b2
** also noted in column c there is a list of file names that the same formula is copied to.

so again the user enters a location in b2, in column d i am looking to get the 13th cell, for the corrosponding location, in the appropriate file. ugh!

Thank you in advance for anyone that not only helps but can understand my ramblings after 6 cups of coffee and 2 hours of sleep!

 

[just_jon]

The problem here is that INDIRECT will only work if your target workbook{s} are open - will they be?

Longre's Morefunc function INDIRECT.EXT will access closed books, but will not return an array.

See the PULL function by Harlan Grove.

 

[mabc]

i guess this works if i am looking for a particualr cell --

I need to have a lookup of cell b2, 'R:\file\file\worksheet'$c:$t!',13

I cant really have a range name because the file name can be 40 different things.


Anyone? Bueller?

 

[Aladin Akyurek]

I will do my best to explain this as I know someone out there will know how to fix this as always.

in column c (c4) I have a file name.
in column b (b2) I have a location number.
in column d (d3) I need a lookup (?) formula that will say something like:

=vlookup(b2,'R:\reports\sr\c4,13) but all i get is a value error.

did i explain that one right?


the user enters location in b2
** also noted in column c there is a list of file names that the same formula is copied to.

so again the user enters a location in b2, in column d i am looking to get the 13th cell, for the corrosponding location, in the appropriate file. ugh!

Thank you in advance for anyone that not only helps but can understand my ramblings after 6 cups of coffee and 2 hours of sleep!

What do you have exactly in C4 - just the file name is not enough for the formula will need a table range?

 

[mabc]

b c d
2 167 sept05
3 329 sept05_class329
4 76 sept05_class76
5 387 sept05_class387
6 180 sept05_class180
7 284 sept05_class284
8 480 sept05_class480


ok == b2 the user enters a location number
in column b -- i have a random list of classes involved that pertain to the merchandise that i am dealing with.
column c is a concatenated formation of the file name using d2_classb3
so what i am trying to do is say in a vlookup (i am guessing)
lookup b2,r:\directory\subdirectory\c3.xls$c:$t,13

am i nuts or cant this be done?