Need to refer a cell having range of another workbook in table array of vlook up function

**joinashish** · 08-07-2014, 02:53 PM

I am working on a assignment in which I need to vlookup the details from another worksheet (X). And this worksheet(X) updates every other week. So, I want to set a cell(Y) which would refer this sheet (X) always. Then I want to use this cell (Y) as my table array range to determine the values.

Attached is the sheet,

I want to use cell A1 for my vlookup function in D4.

Any help would be highly appreciated.

**Tony Valko** · 08-07-2014, 03:55 PM

There is no attachment to reference.

See if you can adapt this.

A1: Sheet2!A2:B100

=VLOOKUP(D4,INDIRECT(A1),2,0)

Which evaluates to:

=VLOOKUP(D4,Sheet2!$A$2:$B$100,2,0)

**joinashish** · 08-07-2014, 04:21 PM

I am sorry for not having the attachment. I am trying but for some reason I am not able to upload it. Anyhow, I tried to use the function you suggested but it is giving the error as "#ref". I am selecting A1 cell and writing-- A1:!sheetX A1:Z567 and then selecting vlookup function in another column and then under table array I am putting "indirect(A1)".

Response shall be highly appreciated.

Thanks in advance.

**Tony Valko** · 08-07-2014, 06:43 PM

Originally Posted by joinashish

I am selecting A1 cell and writing-- A1:!sheetX A1:Z567

Try it like this:

sheetX!A1:Z567

**joinashish** · 08-08-2014, 09:33 AM

Thanks for your reply. Now I am getting the error as "#Ref". While going through vlookup calculation, under table array result shows as "volatile".

I would appreciate any help.

Rgds

**Tony Valko** · 08-08-2014, 09:54 AM

What is the REAL sheet name that holds the lookup table?

**joinashish** · 08-08-2014, 10:49 AM

Thanks for your response.

Sheet name from which I am trying to lookup: [20140731 Rating File SB 001.xlsb]Ratings'!$A$7:$S$7867

**Tony Valko** · 08-08-2014, 11:01 AM

So, you're actually trying to link to another file.

This won't work unless the source file is open.

Need to refer a cell having range of another workbook in table array of vlook up function

LinkBack

Thread Tools

Rate This Thread

Display

Need to refer a cell having range of another workbook in table array of vlook up function

Re: Need to refer a cell having range of another workbook in table array of vlook up funct

Re: Need to refer a cell having range of another workbook in table array of vlook up funct

Re: Need to refer a cell having range of another workbook in table array of vlook up funct

Re: Need to refer a cell having range of another workbook in table array of vlook up funct

Re: Need to refer a cell having range of another workbook in table array of vlook up funct

Re: Need to refer a cell having range of another workbook in table array of vlook up funct

Re: Need to refer a cell having range of another workbook in table array of vlook up funct

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] how to refer to a range instead of an array

VLOOK problem with multiple values in table array

[SOLVED] Vlook up when duplicate values in the array table and lookup column

[SOLVED] Can a cell refer to range name/array of data previously set?

syntax to refer to a range as an array?

Bookmarks

Bookmarks

Posting Permissions