Using text in a cell to define the named range reference

[brianv]

We use multiple manufactures for equipment, and are creating a single workbook named "Current Pricelist". Within this worksheet each manufacture will have a dedicated tab/worksheet.

We then have a separate "Engineering" wookbook that in the past has never referenced a "Pricelist" workbook, we have always entered in all the data manually and nopw are trying to streamline the process.

So I am using the Match/Offset function to lookup part numbers entered in on the "Engineering" file, to then lookup the description, cost and cost code with the "Current Pricelist" file.

Within the "Engineering" file, column "E" is for the Manufacture, "F" is the Part Number, "G" is the Description, "H" is the Cost and "I" is the Costcode. We actually are doing the matching formulas off to the side of the engineering file in colums AB:AE as so the formula does not get erased accidently, then using a form button we copy the values from AC:AE to G:I

In column AB I have this match lookup: "=MATCH(F10,OFFSET(MFG,,,,1),0)-1"
In column AC is the offset lookup for the Description: "=OFFSET(MFG,AB10,1,1,1)"
In column AD is the offset lookup for the Cost: =OFFSET(MFG,AB10,2,1,1)
and in Column AE is the offset lookup for the Costcode: =OFFSET(MFG,AB10,3,1,1)

note: the MFG within the formula is reference to the Manufactures Name

2 questions....

1- How do I refer to the the manufactures name in column E for use within the match/offset formula

2- How do I reference a named range in a separate file.

also on a side note, can I have a named range that spans multiple wooksheets, such as column D in sheet1, sheet2 and sheet3?
Thanks
BV

 

[fairwinds]

Hi,


1.)
Try e.g.
=OFFSET(INDIRECT(A1),AB10,1,1,1)
where A1 holds the name.

2.)
=INDIRECT("Book1.xls!"&A1)
if source workbook is open, else you would need to check out INDIRECT.EXT from morefunc add-in (See recommended add-in and links thread)