Hi,

ENV: Excel 2003 running on XP


PROBLEM DETAILS:

I have a call as:

=VLOOKUP($A4, '[NEW FNO EOD Data.xls]03-Dec'!$B$2:'[NEW FNO EOD
Data.xls]03-Dec'!$Z$500, 9, FALSE)

In above call, I wish to dynamically pick up text "03-Dec" [which
refers to sheet name on different file], from another cell - say A10
of current sheet, using INDIRECT function. However, I am somehow
faultering in my permutations of INDIRECT use above. I tried to see if
REPLACE could work,but thats returning string literal, hence probably
forming invalid 2nd argument for VLOOKUP.

Even though I got useful clue in my recent post, for a slightly easier
variation of my above attempt in below thread [URL give below],
somehow there is more work to be done here.

Any clues shall be much helpful.

Thanks for your time.

- KA

http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/7e53da21753caae0/2dd6b21e7d791be0?lnk=gst&q=Kedar+Agarkar#2dd6b21e7 d791be0
[Watch URL wrap]

Self resolved:

= IF(
ISERROR( VLOOKUP($A16,INDIRECT("'[NEW FNO EOD Data.xls]"&H
$3&"'!$B$2:$Z$500"), 8, FALSE) ),
0,
VLOOKUP($A16,INDIRECT("'[NEW FNO EOD
Data.xls]"&H$3&"'!$B$2:$Z$500"), 8, FALSE)
)

.....as easy as that.

Thanks anyway to group.

- KA