Hi,

Is it possible to make Array References dynamic?

For example I have a formula which is something like this:
=VLOOKUP(A1, [excelFile.xls]*sheetTab*!A1:C10, 2, FALSE)

Is it possible for the value of *sheetTab* in above to depend on the
value of a cell?

So instead of above, I would have something like this:
=VLOOKUP(A1, [excelFile.xls]*A2*!A1:C10, 2, FALSE)

Obviously this formula doesn't work, but hopefully I got my point
accross.


Thanks in advance


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile: http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=489099

Check out the INDIRECT formula. Allows you to turn a string of text into a
cell reference (or range). You could construct your string dynamically, then
turn it into a range using the formula.

"anar_baku" wrote:


Hi,

Is it possible to make Array References dynamic?

For example I have a formula which is something like this:
=VLOOKUP(A1, [excelFile.xls]*sheetTab*!A1:C10, 2, FALSE)

Is it possible for the value of *sheetTab* in above to depend on the
value of a cell?

So instead of above, I would have something like this:
=VLOOKUP(A1, [excelFile.xls]*A2*!A1:C10, 2, FALSE)

Obviously this formula doesn't work, but hopefully I got my point
accross.


Thanks in advance


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile: http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=489099

Great stuff, didn't know of this function before! Thanks a lot!

Is there a way of using this as part of a reference though? So could I
have something like:

=VLOOKUP(A1, [excelFile.xls]INDIRECT(A2)!A1:C10, 2, FALSE)


--
anar_baku
------------------------------------------------------------------------
anar_baku's Profile: http://www.excelforum.com/member.php...o&userid=18259
View this thread: http://www.excelforum.com/showthread...hreadid=489099

Yes, you can do it with the following syntax:
=vlookup(a1, indirect("'[excelfile.xls]" & a2 & "'!a1:c10"), 2, false)

As you see, we use the concatenation operator to build the entire
reference for the 2nd argument using INDIRECT().

HTH
Kostis Vezerides