Using concatenate and Indirect to reference a range from a remote workbook

[Jatclarke999]

Hi all

I am trying to compare values in two ranges, one from the active
workbook and the other from a different remote workbook. I am using the
Vlookup with the table array field being populated from a range of
concatenated cells. The first one defines the path of the remote
workbook, the second defines the name of the workbook (because the
workbook name changes each week) and the third is the range name.

So the Table array part of the Vlookup looks like ths:-

INDIRECT(CONCATENATE(File_path,"Forecast_Chk_WK_",Cur_Week-1,".xls!FCast_tot"))

Now prior to adding the File_path component, and having the secondary
workbook open, it worked OK. But now I am using it as above - No deal.

Anyone able to offer a solution would be greatfully appreciated.

TIA

John

 

[Jatclarke999]

Sorry - Found a typo - I think - but still does not work

INDIRECT(CONCATENATE("'",File_path,"\Forecast_Chk_WK_",Cur_Week-1,".xls'!FCast_tot"))

The workbook does not recognise the link

 

[Harlan Grove]

(e-mail address removed) wrote...

Sorry - Found a typo - I think - but still does not work

INDIRECT(CONCATENATE("'",File_path,"\Forecast_Chk_WK_",Cur_Week-1,
".xls'!FCast_tot"))

The workbook does not recognise the link

INDIRECT only works with references into *open* workbooks. The
technical reason is that INDIRECT only returns range references, and
ranges only exist (as far as Excel is concerned) in open workbooks.

The workarounds are described in the following archived article.

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?B34B15DCC ).

 

[Jatclarke999]

Thanks - I found this later after sweating for a while on a solution. I
eventually resorted to referencing the data by simple linking of cells
on a different part of the sheet and re-doing the maths and then
running a VBA find and replace to change the week number in each of the
cells.

Thanks again for the pointer. Perhaps one day MS will solve these basic
issues (well they seem bassic to me )

 

[Jatclarke999]

Thanks - I found this later after sweating for a while on a solution. I
eventually resorted to referencing the data by simple linking of cells
on a different part of the sheet and re-doing the maths and then
running a VBA find and replace to change the week number in each of the
cells.

Thanks again for the pointer. Perhaps one day MS will solve these basic
issues (well they seem bassic to me )

 

[Harlan Grove]

(e-mail address removed) wrote...
....

Thanks again for the pointer. Perhaps one day MS will solve these basic
issues (well they seem bassic to me )

Unlikely. They made a decision a long time ago to implement external
reference links in such a way that they had to be syntactic constants,
and they made a decision that Excel's INDIRECT function, unlike 123's
@@ function, could only return range references.

When and if MSFT ever gets around to such basics as allowing multiple
files with the same base filename to be open in the same instance at
the same time, then they may address other archaic limitations as well,
but I suspect they'll give us a dancing text theme first.