vlookup and referencing a range name in another workbook.

  • Thread starter Thread starter ebola
  • Start date Start date
E

ebola

Hello,


I want to reference a column in another workbook, where
the columns in that workbook keep changing (column #2
today is column #5 tomorrow). The answer is named ranges.


the column i want to reference has the range name:

HPMDESC = =HPmasterlist!$C:$C
....in a different workbook


-the worksheet on the other workbook is HPmasterlist
-the workbook is titled MPL.xls





here is my formula:

=IF(ISBLANK($B3),"",VLOOKUP($B3,[MPL.xls]HPmasterlist!
$A$1:$O$4500,INDIRECT("HPMDESC"),FALSE))


any ideas?? i'm stumped!


Marcel.
 
I want to reference a column in another workbook, where
the columns in that workbook keep changing (column #2
today is column #5 tomorrow). The answer is named ranges.

the column i want to reference has the range name:

HPMDESC = =HPmasterlist!$C:$C
...in a different workbook ...
=IF(ISBLANK($B3),"",VLOOKUP($B3,[MPL.xls]HPmasterlist!
$A$1:$O$4500,INDIRECT("HPMDESC"),FALSE))
Click to expand...

If you know you want column C, then it'd be easier to define HPMDESC as 3 or
=COLUMN($C:$C). Then refer to it directly.

=IF(ISBLANK($B3),"",
VLOOKUP($B3,[MPL.xls]HPmasterlist!$A$1:$O$4500,HPMDESC,0))
 
Hello,

thanks for the reply. My problem is that the master list from where I
draw my information (in this case, called "MPL.xls") may change the
order of the columns! it can be column 3 today, and column 4
tomorrow.


My 'workaround' was to go into the master list and put in range names.
(like the one i mentioned: HPMDESC in [mpl.xls]HPmasterlist)


essentially, i am looking to reference a range name in another
workbook (pull information from one workbook to another, using range
names, since the columns change)

any thoughts on the best way to approach this one?

thank you for your reply!


I want to reference a column in another workbook, where
the columns in that workbook keep changing (column #2
today is column #5 tomorrow). The answer is named ranges.

the column i want to reference has the range name:

HPMDESC = =HPmasterlist!$C:$C
...in a different workbook ..
=IF(ISBLANK($B3),"",VLOOKUP($B3,[MPL.xls]HPmasterlist!
$A$1:$O$4500,INDIRECT("HPMDESC"),FALSE))
Click to expand...

If you know you want column C, then it'd be easier to define HPMDESC as 3 or
=COLUMN($C:$C). Then refer to it directly.

=IF(ISBLANK($B3),"",
VLOOKUP($B3,[MPL.xls]HPmasterlist!$A$1:$O$4500,HPMDESC,0))
Click to expand...
 
thanks for the reply. My problem is that the master list from where I
draw my information (in this case, called "MPL.xls") may change the
order of the columns! it can be column 3 today, and column 4
tomorrow.

My 'workaround' was to go into the master list and put in range names.
(like the one i mentioned: HPMDESC in [mpl.xls]HPmasterlist)

essentially, i am looking to reference a range name in another
workbook (pull information from one workbook to another, using range
names, since the columns change) ...
I want to reference a column in another workbook, where
the columns in that workbook keep changing (column #2
today is column #5 tomorrow). The answer is named ranges.

the column i want to reference has the range name:

HPMDESC = =HPmasterlist!$C:$C
...in a different workbook ..
=IF(ISBLANK($B3),"",VLOOKUP($B3,[MPL.xls]HPmasterlist!
$A$1:$O$4500,INDIRECT("HPMDESC"),FALSE))
Click to expand...
Click to expand...
Click to expand...
...

First, without a workbook qualifier, INDIRECT assumes you've fed it a range in
the *same* workbook as the cell containing the formula calling it. But if this
is an existing named range in the other workbook, you don't need to use
INDIRECT. Also, a straight range reference won't work as 3rd argument to
VLOOKUP. You want it's column index. So, if you want to pull from the column
given the name HPMDESC, try

=IF(ISBLANK($B3),"",
VLOOKUP($B3,[MPL.xls]HPmasterlist!$A$1:$O$4500,COLUMN([MPL.xls]HPMDESC),0))
 
Hello,

Thanks, friend. You are superb! A collegue at work told me it could
not be done... sigh


I knew it wasn't out of reach...


sincerely,

Marcel.


thanks for the reply. My problem is that the master list from where I
draw my information (in this case, called "MPL.xls") may change the
order of the columns! it can be column 3 today, and column 4
tomorrow.

My 'workaround' was to go into the master list and put in range names.
(like the one i mentioned: HPMDESC in [mpl.xls]HPmasterlist)

essentially, i am looking to reference a range name in another
workbook (pull information from one workbook to another, using range
names, since the columns change) ..
...
I want to reference a column in another workbook, where
the columns in that workbook keep changing (column #2
today is column #5 tomorrow). The answer is named ranges.

the column i want to reference has the range name:

HPMDESC = =HPmasterlist!$C:$C
...in a different workbook
..
=IF(ISBLANK($B3),"",VLOOKUP($B3,[MPL.xls]HPmasterlist!
$A$1:$O$4500,INDIRECT("HPMDESC"),FALSE))
Click to expand...
Click to expand...
..

First, without a workbook qualifier, INDIRECT assumes you've fed it a range in
the *same* workbook as the cell containing the formula calling it. But if this
is an existing named range in the other workbook, you don't need to use
INDIRECT. Also, a straight range reference won't work as 3rd argument to
VLOOKUP. You want it's column index. So, if you want to pull from the column
given the name HPMDESC, try

=IF(ISBLANK($B3),"",
VLOOKUP($B3,[MPL.xls]HPmasterlist!$A$1:$O$4500,COLUMN([MPL.xls]HPMDESC),0))
Click to expand...
 
Back
Top