Concat a parameter into a vlookup?

[Stephen]

Is is possible to say

=IF(ISNA(VLOOKUP($X996,Weights.xls!w"VALUE IN CELL C5"s,3,FALSE))

?

 

[Niek Otten]

Yes.

Look in HELP for the INDIRECT() function

Post again in this same thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Is is possible to say
|
| =IF(ISNA(VLOOKUP($X996,Weights.xls!w"VALUE IN CELL C5"s,3,FALSE))
|
| ?

 

[sebastienm]

Almost. If the Weights.xls book is open, you can use the INDIRECT function:
syntax: INDIRECT(address_string)
with <address_String>: [book_name]sheet_name!range_address_or_name
So something like
ISNA(VLOOKUP($X996
, INDIRECT("[Weights.xls]worksheet_name!w" & C5 & "s")
,3,FALSE) )

 

[Stephen]

I see.

So even if I qualify the path to the Weights book, it simply won't work.
Damn.

Oh well.

Thanks.

Almost. If the Weights.xls book is open, you can use the INDIRECT function:
syntax: INDIRECT(address_string)
with <address_String>: [book_name]sheet_name!range_address_or_name
So something like
ISNA(VLOOKUP($X996
, INDIRECT("[Weights.xls]worksheet_name!w" & C5 & "s")
,3,FALSE) )

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>

Is is possible to say

=IF(ISNA(VLOOKUP($X996,Weights.xls!w"VALUE IN CELL C5"s,3,FALSE))

?

 

[Niek Otten]

<it simply won't work>

What does that mean? Error message? What message?
Wrong result? If so, supply the input values, the result you expected and what you got instead.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I see.
|
| So even if I qualify the path to the Weights book, it simply won't work.
| Damn.
|
| Oh well.
|
| Thanks.
|
| "sebastienm" wrote:
|
| > Almost. If the Weights.xls book is open, you can use the INDIRECT function:
| > syntax: INDIRECT(address_string)
| > with <address_String>: [book_name]sheet_name!range_address_or_name
| > So something like
| > ISNA(VLOOKUP($X996
| > , INDIRECT("[Weights.xls]worksheet_name!w" & C5 & "s")
| > ,3,FALSE) )
| >
| > --
| > Regards,
| > Sébastien
| > <http://www.ondemandanalysis.com>
| >
| >
| > "Stephen" wrote:
| >
| > > Is is possible to say
| > >
| > > =IF(ISNA(VLOOKUP($X996,Weights.xls!w"VALUE IN CELL C5"s,3,FALSE))
| > >
| > > ?

 

[Stephen]

It returns a #REF error because my weights.xls will not be open all the time.
the kicker for this to work is to have that weights workbook always open
which is not feasible in my situation.