alternative to indirect function

See if this helps:

A VBA Function to Get a Value From a Closed File
http://j-walk.com/ss/excel/tips/tip82.htm

Hi

you could use the INDEX function to point to the data array in the closed file + INDIRECT to pass the row_num and col_num

for example if you have data in WKB1.XLS (the closed file) and in WKB2.XLS A2 the reference to the value in WKB1.XLS, then your formula will look like:

=INDEX([WKB1.XLS]sheet1!$A$1:$C$10,ROW(INDIRECT(A2)),COLUMN(INDIRECT(A2)))

if there is no value in the referenced cell in the closed workbook, you will get a #REF error. This can be prevent with and if statement for example

Hi Karraj

I have a working eaxmple of how this can be done here: http://www.ozgrid.com/download/default.htm

Under "ChangingExternal.zip"

Hi, jordun,

May I know if INDIRECT function also works with the frist parameter of INDEX function too?

skyking said:

Hi, jordun,

May I know if INDIRECT function also works with the frist parameter of INDEX function too?

Click to expand...

Yes it does. It also works within the parameter of Vlookup or Hlookup. The only problem is that INDIRECT has the side-effect of basically turning each cell into a data table that has to be calculated in a more laborious fashion. This can result in slowdown if you are using large tables with many Indirect functions, which I happen to be dealing with right now.