Vlookup with concantenate problems

[Dibbley247]

Where have I gone wrong???

Please Login or Register  to view this content.

[martindwilson]

'C:\Users\Duane\Downloads\Packing Waste Query test.xls'!"Week6"

what is week 6 here? wher is the lookup range?
forget the concatenate for a mo' it should look like this

=VLOOKUP(A1,'C:\Documents and Settings\Martin Wilson\My Documents\[xxxxxxxx.xls]Sheet2'!$A$1:$B$10,2,FALSE)

[Dibbley247]

This is what my lookup should look like with out the concantenate

The "week6 is a named range.
But when I start adding the C: part etc it don't like it.

Please Login or Register  to view this content.

The W4 will be the concantenate part

[martindwilson]

works for me must be what you are concatenating is not quite whats in the other workbook
=VLOOKUP(CONCATENATE(C1,D1,E1),'C:\Documents and Settings\Martin Wilson\My Documents\xxxxxxxx.xls'!martin,2,FALSE)
cells c1 =pigs d1= may e1=fly
named range in xxxxxxxx.xls is "martin" and refers to sheet1 a1:b10 a1=pigsmayfly b1=dog
formula returns dog no problem

[Dibbley247]

Ah I see what Ive done wrong - In the concatenate part, I have written L1,"Packing" That would actually refer to cell L1 where i should have put it as "L1packing"

so here it is working:

Please Login or Register  to view this content.

Very stupid mistake that you wouldn't know if reading from your end!!!!!

Thanks for your time!!!!!

[Dibbley247]

Now that it's running, Ive come across another Hitch!!!!!!!

The named range which the lookup works on will vary each week. So I'm trying to use the Concatenate for the named range also! So it would look like

Please Login or Register  to view this content.

So I'm trying to have the part in red as a variable

Please Login or Register  to view this content.

So Ive written it as:

Please Login or Register  to view this content.

But no joy!

[bebo021999]

May be use INDIRECT to convert text to reference:
=VLOOKUP(.....,INDIRECT("'C:\....'!Week"&V4),....,....)

[Dibbley247]

I've never used INDIRECT before.

I done this, but comes up with too few arguments.

Please Login or Register  to view this content.

[bebo021999]

Try to replace 6 with V4 (see my sample in prv. post)

[Dibbley247]

Oh yeah!

Tried this, but comes up with: contains an error, and highlights the last CONCATENATE

Please Login or Register  to view this content.

[martindwilson]

hard to test
=VLOOKUP(CONCATENATE("Week",$V$4,"L1Packing"),INDIRECT("'C:\Users\Duane\Downloads\Packing Waste Query test.xls'!Week"&V4),2,FALSE)) note both workbooks need to be open for indirect to work

[Dibbley247]

Ah. In my case the workbook which the lookup is working on needs to be closed, as it will be off a network.

Is there a way around this?

[martindwilson]

you could try indirect.ext which is in the morefunc add in but i've had mixed results with it

http://downloads.info/windows/office.../morefunc.html