Using text in a cell to define the named range reference

brianv

Board Regular
Joined
Dec 11, 2003
Messages
100
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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)
 

Forum statistics

Threads
1,137,300
Messages
5,680,701
Members
419,928
Latest member
dolincasting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top