change the defined name range names to correspond to workbook (book1.xls) -

[keilah]

This questionis also posted to Experts Exchange no answer

here is the link: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23400998.html

see attached workbook(s)
Hi Guys

Look at workbook (book1.xls) and see the concatinated name ranges in column d worksheet "Repsol"

Here is the logic

I have selected Repsol as my supply and then Repsol Sale and Baja as my demand (customers)

If you look at column D workbook book1.xls and worksheet Repsol

How did I get the name ranges

Ok, now look at workbook EE-modified-4.xls and worksheet Deal Selection

Deal Selection worksheet we are only interested in column C and J

So back to book1.xls

Under heading Allocation column B we have

Repsol Sale ? Allo_Sup_6_Dem_13
Baja ? Allo_Sup_6_Dem_14

How did i get these name range what logic do they follow.......

If we the Allo_ part come from the heading in book1.xls column b10

The Sup_6 comes from (the name repsol) in worksheet Deal Selection columns B and C

So if we find Repsol in column B (first) then >

Move across to column C we have Sup_6

NOW

The Dem_13 comes from (the name Repsol Sale) in worksheet Deal Selection columns I and J

So if we find Repsol Sale in column I (first) then >

Move across to column J we have Dem_13

Then hence we have our new defined name range Allo_Sup_6_Dem_13

The must work for all combination of supply and demand customers in worksheet Deal Selection

However, if it helps in worksheet Tables workbook EE-modified-4.xls I have all ready worked out all the combinations..

Its getting the computer via VBA to select the right one.

[keilah]

second workbook - where the correction need to be made,,,,