Reference to a range in another workbook

[Huyeote]

Hi, there, I need to insert a VLOOKUP formula to a range of cells in a
worksheet in workbook1. The looked up area is a range in workbook2. The
formula string looks like "=VLOOKUP(RC[-1],
'[Workbook2]Sheet1!R1C1:R55C7',8,FALSE". The address of the looked up area
changes frequently. How can I use a variable to replace the long string of
the address? How many ways to do it? Thanks in advance.

Regards

Huyeote

 

[Abdul Salam]

Hi,

You can define a name for your lookup area instead of
cell reference

Abdul Salam

 

[Huyeote]

Can you tell me how to do it? Thanks.

Huyeote

Hi,

You can define a name for your lookup area instead of
cell reference

Abdul Salam

-----Original Message-----
Hi, there, I need to insert a VLOOKUP formula to a range of cells in a
worksheet in workbook1. The looked up area is a range in workbook2. The
formula string looks like "=VLOOKUP(RC[-1],
'[Workbook2]Sheet1!R1C1:R55C7',8,FALSE". The address of the looked up area
changes frequently. How can I use a variable to replace the long string of
the address? How many ways to do it? Thanks in advance.

Regards

Huyeote


.

 

[Abdul Salam]

Hi,

by using VBA:

you can put a command button in your workbook and from
there you can open the referenced workbook and define the
name:

Workbooks.Open Filename:="C:\Documents and
Settings\xxx\yyyy\FileName.xls"

Range(Range("A65536").End(xlUp), Range("C1")).Select

''selects c1: last row in A..

ActiveWorkbook.Names.Add Name:="MyRangename",
RefersToR1C1:=(Selection)
''' change cell reference as per your requirements

ActiveWorkbook.Save
ActiveWorkbook.close


so you can always resize the area by running above code

without using VBA


Just select the range and left to your formula bar type
the desired name and whever you select the name from the
drop down there you can select that range ( by default
this part show you the the cell address)

or another way is :

Insert>Name>>Define.. and enter the name in top box and
select a range from the refers to section.


Abdul Salam

-----Original Message-----
Can you tell me how to do it? Thanks.

Huyeote

Hi,

You can define a name for your lookup area instead of
cell reference

Abdul Salam

-----Original Message-----
Hi, there, I need to insert a VLOOKUP formula to a

range
of cells in a

worksheet in workbook1. The looked up area is a range

in
workbook2. The

formula string looks like "=VLOOKUP(RC[-1],
'[Workbook2]Sheet1!R1C1:R55C7',8,FALSE". The address

of
the looked up area

changes frequently. How can I use a variable to

replace
the long string of

the address? How many ways to do it? Thanks in advance.

Regards

Huyeote


.

.

 

[Aaron]

Huyeote

If your not familiar with Visual basic you can use an
indirect.ext worksheet function which allow's you to use
references to external workbooks closed or open.

You'd need to add an additional add inn for this function
so if you'd like it let me know

Rgds

Aaron