Dynamically changing a link name

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

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


apmeehan@gmail.com wrote:
> 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

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

Die_Another_Day

<apmeehan@gmail.com> wrote in message
news:1152576107.900554.114880@35g2000cwc.googlegroups.com...
> 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
>

DAD,

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

I may have to make a comprimise here!!!

thanks..

andym


Die_Another_Day wrote:
> = Vlookup($A2,INDIRECT("'[WR" & A1 & ".xls]Sheet1'!$A$1:$D$50"),4,false)
> Does something like that work?
>
> Die_Another_Day
>
> <apmeehan@gmail.com> wrote in message
> news:1152576107.900554.114880@35g2000cwc.googlegroups.com...
> > 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
> >

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)

"andym" <apmeehan@gmail.com> wrote in message
news:1152582024.071966.263410@35g2000cwc.googlegroups.com...
> DAD,
>
> yes it does, but only if the WRXXXXXX file is open.
>
> I may have to make a comprimise here!!!
>
> thanks..
>
> andym
>
>
> Die_Another_Day wrote:
> > = Vlookup($A2,INDIRECT("'[WR" & A1 & ".xls]Sheet1'!$A$1:$D$50"),4,false)
> > Does something like that work?
> >
> > Die_Another_Day
> >
> > <apmeehan@gmail.com> wrote in message
> > news:1152576107.900554.114880@35g2000cwc.googlegroups.com...
> > > 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
> > >
>