Dynamically changing a link name

[apmeehan]

Dear All,

is it possible to include a variable in a formula that gives you the
option to pick out a different file name?

eg.. Weekly Reports are created with just the last 6 characters of the
file name changing each time a new report is created. ... eg. WR010106,
WR070106 etc..

Can I have a cell ("A1") contain the variable '070106' that gets picked
up from a formula in "B1" where the formula may be:

= Vlookup($A2,'[WRXXXXXX.xls]Sheet1'!$A$1:$D$50,4,false)

where the XXXXXX represents the value in "A1"?

Basically, enter a string in one cell that gets picked up by a formula
containing a link.

I suppose I am trying to bypass the 'Change Source' option in the Links
area, and making the formula more dynamic.

Kind regards...

andym

 

[andym]

I have half answered my own question with:

=VLOOKUP(B5,(INDIRECT("[Test" & E1 & ".xls]Sheet1!$B$4:$C$4")),2,FALSE)

(different references used, but idea the same!!).

Only problem with this is that the linked sheet has to be open.

Is there any way around this? There will be instances where the linked
sheet will not be open.

Regards,

andym

 

[Die_Another_Day]

= Vlookup($A2,INDIRECT("'[WR" & A1 & ".xls]Sheet1'!$A$1:$D$50"),4,false)
Does something like that work?

Die_Another_Day

 

[andym]

DAD,

yes it does, but only if the WRXXXXXX file is open.

I may have to make a comprimise here!!!

thanks..

andym

= Vlookup($A2,INDIRECT("'[WR" & A1 & ".xls]Sheet1'!$A$1:$D$50"),4,false)
Does something like that work?

Die_Another_Day

Dear All,

is it possible to include a variable in a formula that gives you the
option to pick out a different file name?

eg.. Weekly Reports are created with just the last 6 characters of the
file name changing each time a new report is created. ... eg. WR010106,
WR070106 etc..

Can I have a cell ("A1") contain the variable '070106' that gets picked
up from a formula in "B1" where the formula may be:

= Vlookup($A2,'[WRXXXXXX.xls]Sheet1'!$A$1:$D$50,4,false)

where the XXXXXX represents the value in "A1"?

Basically, enter a string in one cell that gets picked up by a formula
containing a link.

I suppose I am trying to bypass the 'Change Source' option in the Links
area, and making the formula more dynamic.

Kind regards...

andym

 

[Bob Phillips]

Have a look at Laurent Longre's MOREFUNC add-in, that provides an
INDIRECT.EXT function, http://xcell05.free.fr/

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

DAD,

yes it does, but only if the WRXXXXXX file is open.

I may have to make a comprimise here!!!

thanks..

andym

= Vlookup($A2,INDIRECT("'[WR" & A1 & ".xls]Sheet1'!$A$1:$D$50"),4,false)
Does something like that work?

Die_Another_Day

Dear All,

is it possible to include a variable in a formula that gives you the
option to pick out a different file name?

eg.. Weekly Reports are created with just the last 6 characters of the
file name changing each time a new report is created. ... eg. WR010106,
WR070106 etc..

Can I have a cell ("A1") contain the variable '070106' that gets picked
up from a formula in "B1" where the formula may be:

= Vlookup($A2,'[WRXXXXXX.xls]Sheet1'!$A$1:$D$50,4,false)

where the XXXXXX represents the value in "A1"?

Basically, enter a string in one cell that gets picked up by a formula
containing a link.

I suppose I am trying to bypass the 'Change Source' option in the Links
area, and making the formula more dynamic.

Kind regards...

andym