keilah
05-14-2008, 05:35 AM
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.
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.