Referencing a cell when using VLOOKUP

[SandyUK]

Hello all

Does anybody know how i can replace WE 08 27 05 in the table_array part of the formula below with a reference to a cell?

The idea is that I don't have to manually update the links everytime i want to look at a different week just the relevant cell.

=VLOOKUP(D2,'I:\Costs\WE actuals\Date Format MMDDYY\[WE 08 27 05.xls]Customer'!$1:$65536,5,FALSE)

Regards

Adrian

[Vacation's Over]

concatenate

prety sure that if you use concatenate and replace teh week code with a cell
referenceand then change the cell reference then the Vlooup will reflect the
change.

=VLOOKUP(D2,Concatenate(" 'I:\Costs\WE actuals\Date Format MMDDYY\[ + A3 +
..xls]Customer'!$1:$65536",5,FALSE)

Air code = you may have syntax issues with teh quotes but the approach is
sound...

"SandyUK" wrote:

>
> Hello all
>
> Does anybody know how i can replace WE 08 27 05 in the table_array part
> of the formula below with a reference to a cell?
>
> The idea is that I don't have to manually update the links everytime i
> want to look at a different week just the relevant cell.
>
> =VLOOKUP(D2,'I:\Costs\WE actuals\Date Format MMDDYY\[WE 08 27
> 05.xls]Customer'!$1:$65536,5,FALSE)
>
> Regards
>
> Adrian
>
>
> --
> SandyUK
> ------------------------------------------------------------------------
> SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
> View this thread: http://www.excelforum.com/showthread...hreadid=477505
>
>

[SandyUK]

Thanks for the reply

I have tried this approach and checked the syntax etc and no joy. Its been a long term problem which i can't seem to find a solution for.

Regards

Adrian

[Dave Peterson]

The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

SandyUK wrote:
>
> Thanks for the reply
>
> I have tried this approach and checked the syntax etc and no joy. Its
> been a long term problem which i can't seem to find a solution for.
>
> Regards
>
> Adrian
>
> --
> SandyUK
> ------------------------------------------------------------------------
> SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
> View this thread: http://www.excelforum.com/showthread...hreadid=477505

--

Dave Peterson

[Vacation's Over]

Harlan's FTP has "Some Assembly required"


You might use Ron's Code to pull data from diferent files onto a master
sheet in your book then Vlookup to select the right data from the Master sheet

http://www.rondebruin.nl/summary.htm

"Dave Peterson" wrote:

> The function you want to use is =indirect().
>
> The bad news is that =indirect() will return an error if that other workbook
> isn't open.
>
> Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
> workbook.
>
> You can find the function at Harlan's FTP site:
> ftp://members.aol.com/hrlngrv/
> Look for pull.zip
>
> SandyUK wrote:
> >
> > Thanks for the reply
> >
> > I have tried this approach and checked the syntax etc and no joy. Its
> > been a long term problem which i can't seem to find a solution for.
> >
> > Regards
> >
> > Adrian
> >
> > --
> > SandyUK
> > ------------------------------------------------------------------------
> > SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
> > View this thread: http://www.excelforum.com/showthread...hreadid=477505
>
> --
>
> Dave Peterson
>

[SandyUK]

Thanks Dave

I have been ill for a while and just got back to this project (forgot i had posted the query but came across it when searching the site).

I will give it a try.

Regards

Adrian

[Dave Peterson]

You may want to verify that you have Harlan's latest and greatest version of
Pull.

SandyUK wrote:
>
> Thanks Dave
>
> I have been ill for a while and just got back to this project (forgot i
> had posted the query but came across it when searching the site).
>
> I will give it a try.
>
> Regards
>
> Adrian
>
> --
> SandyUK
> ------------------------------------------------------------------------
> SandyUK's Profile: http://www.excelforum.com/member.php...o&userid=17487
> View this thread: http://www.excelforum.com/showthread...hreadid=477505

--

Dave Peterson