Toggle data set from drop down list?

[cptwhite1]

I currently have a data set for prices per metre on a range of products. I'm needing the ability to switch the data from "per metre" to "stock length" prices within the same cell range so my equations still work. I'm envisaging a drop down list which simply switches between the values for each. Can someone explain how I would implement this so 1 cell can have value A or value B applied to it depending which option from the drop down list was selected?

[pareshj]

Hi,

Attach a sample workbook with your inputs and expected output.

Regards,
Paresh J

[cptwhite1]

I've attached a simple example using 2 product codes - 01 and 04

I have "Each" "Length" and "Profile Code" - all manually input.

I then use a simple equation to calculate the price per piece (each) based on Length, Customer Discount and Profile Code. This works well when only dealing with 1 set of prices (Per Metre price - column J data).

However sometimes customers will order Stock Lengths (5m lengths). I now need to use the same reference cells (column H), but have the prices toggle to the data values in column K. So column H is just looking up the values in either column J or K, depending which option is selected. I then work with the fixed cell references in column H in my equation.

My full costings sheet is somewhat more complicated than this. Our product codes have a number, or number followed by a letter (eg. 92L). So I've devised a system for easy cell reference - for example product code 92L uses cell L92 to list its price. Hence the need to work from that cell value and switch the value in that particular cell. Having two seperate data sets on the same worksheet defeats the system I've developed for easy recollection of the prices when inputting my equation (there are hundreds of values from A1 to Z999 normally).

There are other variables as well that can further lengthen the equation needed, so I'm trying to keep the equation calculating the price easy to read and edit. I don't want it to start referencing seperate worksheets as this further complicates the equation.

Ideally I would be able to specify prices for both "Per Metre" and "Per Stock Length" at the same time. For example a customer may order

1 no. 1.5m 92L (per metre price)
1 no. 5m 92L (stock length price)

However if they're both referencing the same cell this probably won't be possible. Instead I will probably have to split the order into 2 worksheets - 1 for the cut lengths only, and 1 for the stock lengths only). This isn't a major problem as it will only happen very rarely.

[cptwhite1]

Sorry to resurrect this thread, but I never figured out a solution. Can someone let me know if they think it's possible to implement?