Reference to a range in another workbook
 

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

Reference to a range in another workbook
 

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


.

Reference to a range in another workbook
 

Can you tell me how to do it? Thanks.

Huyeote

"Abdul Salam" wrote in message
...
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


.

Reference to a range in another workbook
 

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 :

InsertNameDefine.. 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

"Abdul Salam" wrote in message
...
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


.



.

Reference to a range in another workbook
 

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

-----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


.