I hope I can make this as clear as possible without an example, but I will
try my best on this.

I'm not even sure what this is called in Excel so I'm not sure what to
search for in the board here. If someone could either answer this question or
direct me to the right place to find out more, it will be much appreciated.

So, here's what I'm trying to do:

I'm using Excel 2003 and want to create a sheet where I can reference data
from one section of the sheet to the other using drop down menus.

I have a "Data" sheet where I list all the products (there are about 25
total) and their data. Each product can be broken down into 7 other
subproducts (each subproduct is the same for every product). For example, the
first product is Widgets. 300 Widgets can equal 1,000 Digits and 500 Nuggets.
The next product may be Weasels. 500 Weasels can equal 500 Nuggets as well,
but not any Digits.

Each product will be able to be broken down into at LEAST 1 subproduct, and
some may be able to be broken down into all 7.

Now, on the main sheet that the user will input data on, they will be able
to put in how many subproducts they need. For example, let's say the user
wants to be able to get 10,000 Digits and 1,500 Nuggets. However, they only
want to use Weasels. Well, they can select from a drop down menu, lets say, 5
Products to choose from (Out of the original 25), and they only want Weasels.
The sheet will then try to solve the problem of figuring out how many Weasels
the user will need to get 10,000 Digits and 1,500 Nuggets. Of course, in this
example, it won't be able to get any Digits, so it'll simply try to solve for
the Nuggets and simply say that there are still 10,000 Digits left over.

Also, since there are so many products, Excel would need to be able to
calculate the best way to get the subproducts. For example, if the person
selects Weasels as their product and they want 10,000 Digits and 1,500
Nuggets, Excel will calculate for how many Weasels it takes to get all the
subproducts they need. Now, if that user adds another product to the list,
such as the Widgets, Excel will re-calculate and find out if it's better to
get more Widgets first (To fulfill both the Digits and Nuggets requirement)
OR if it should first use Weasels to fulfill the Nuggets requirement and then
calculate how many Widgets are needed to fullfill the Digits requirement.

Note: In the above example, and for this sheet, it will not matter if there
is a left over product. Say Excel fulfills all the Nuggets by getting 1,000
Weasels, and then to get all the Digits, another 5,000 Widgets are needed,
but the Widgets create another 500 Nuggets. We're then stuck with an extra
500 Nuggets. But that's fine! We still solved the order.

I hope this is clear enough. All I know is that it has to do something with
Circular references... or something...

Maybe using dependent Validation lists will work.
Debra Dalgleish shows how here... http://www.contextures.on.ca/xlDataVal02.html
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"GTX"
wrote in message
I hope I can make this as clear as possible without an example, but I will
try my best on this.
I'm not even sure what this is called in Excel so I'm not sure what to
search for in the board here. If someone could either answer this question or
direct me to the right place to find out more, it will be much appreciated.

So, here's what I'm trying to do:
I'm using Excel 2003 and want to create a sheet where I can reference data
from one section of the sheet to the other using drop down menus.

I have a "Data" sheet where I list all the products (there are about 25
total) and their data. Each product can be broken down into 7 other
subproducts (each subproduct is the same for every product). For example, the
first product is Widgets. 300 Widgets can equal 1,000 Digits and 500 Nuggets.
The next product may be Weasels. 500 Weasels can equal 500 Nuggets as well,
but not any Digits.

Each product will be able to be broken down into at LEAST 1 subproduct, and
some may be able to be broken down into all 7.

Now, on the main sheet that the user will input data on, they will be able
to put in how many subproducts they need. For example, let's say the user
wants to be able to get 10,000 Digits and 1,500 Nuggets. However, they only
want to use Weasels. Well, they can select from a drop down menu, lets say, 5
Products to choose from (Out of the original 25), and they only want Weasels.
The sheet will then try to solve the problem of figuring out how many Weasels
the user will need to get 10,000 Digits and 1,500 Nuggets. Of course, in this
example, it won't be able to get any Digits, so it'll simply try to solve for
the Nuggets and simply say that there are still 10,000 Digits left over.

Also, since there are so many products, Excel would need to be able to
calculate the best way to get the subproducts. For example, if the person
selects Weasels as their product and they want 10,000 Digits and 1,500
Nuggets, Excel will calculate for how many Weasels it takes to get all the
subproducts they need. Now, if that user adds another product to the list,
such as the Widgets, Excel will re-calculate and find out if it's better to
get more Widgets first (To fulfill both the Digits and Nuggets requirement)
OR if it should first use Weasels to fulfill the Nuggets requirement and then
calculate how many Widgets are needed to fullfill the Digits requirement.

Note: In the above example, and for this sheet, it will not matter if there
is a left over product. Say Excel fulfills all the Nuggets by getting 1,000
Weasels, and then to get all the Digits, another 5,000 Widgets are needed,
but the Widgets create another 500 Nuggets. We're then stuck with an extra
500 Nuggets. But that's fine! We still solved the order.

I hope this is clear enough. All I know is that it has to do something with
Circular references... or something...