Dynamic cell/formula updating

[rivvorz]

Hiya,

If you look at the attached image, it should help with my query:

So I have the following VLookup formula:
=VLOOKUP($A17,G105501.xlsx!A1Lookup,13,FALSE)

The file in which the lookup is to take place should be denoted by the code in F12, i.e. G105501. This is fine, but I am developing this file to be generic, therefore I need the formulae within the appropriate range to change when the value in F12 changes. I can think of a few ways to accomplish this:

a) concatenate the formula together (this didn't seem to work).
b) use a find/replace regex macro on cell update (no idea how to start with this)

I'd really appreciate any advice you could offer me! Many thanks

[Bob Phillips]

If the other file is open

=VLOOKUP($A17,INDIRECT(F12)&".xlsx!A1Lookup",13,FALSE)

should work


--
HTH

Bob Phillips

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

"rivvorz" <rivvorz.2c3zqo_1154851860.6356@excelforum-nospam.com> wrote in
message news:rivvorz.2c3zqo_1154851860.6356@excelforum-nospam.com...
>
> Hiya,
>
> If you look at the attached image, it should help with my query:
>
> So I have the following VLookup formula:
> =VLOOKUP($A17,G105501.xlsx!A1Lookup,13,FALSE)
>
> The file in which the lookup is to take place should be denoted by the
> code in F12, i.e. G105501. This is fine, but I am developing this file
> to be generic, therefore I need the formulae within the appropriate
> range to change when the value in F12 changes. I can think of a few
> ways to accomplish this:
>
> a) concatenate the formula together (this didn't seem to work).
> b) use a find/replace regex macro on cell update (no idea how to start
> with this)
>
> I'd really appreciate any advice you could offer me! Many thanks
>
>
> +-------------------------------------------------------------------+
> |Filename: Image1.jpg |
> |Download: http://www.excelforum.com/attachment.php?postid=5152 |
> +-------------------------------------------------------------------+
>
> --
> rivvorz
> ------------------------------------------------------------------------
> rivvorz's Profile:
http://www.excelforum.com/member.php...o&userid=33450
> View this thread: http://www.excelforum.com/showthread...hreadid=568721
>

[rivvorz]

Hi,

Thanks for this, I appreciate your response. I did have a go (even though practically all the files wouldn't be open at the same time: there will be 15 of them- yikes!); I didn't get any joy though. Thanks anyway.

I'm surprised actually that there isn't better support imbedded for this kind of referencing...

Looks like it's back to the Macro plan...