Drop down list + working out amount

[mantis]

Hi There,

I'm hoping someone can help me create an excel spreadsheet that will help me work out my product costs.

On the second sheet my columns are as follows,
A - is the description of an item I've bought used to build my final product
B - is how many I bought
C - is how much they cost
D - divides C by B to work out what one unit costs

On the first sheet is where I have all the products we make. the column are as follows,
A - The final product name
B - The items used to make it
C - What one item costs (currently references the corresponding cell in column D on sheet 2)
D - How many of that item are used to make the final product
E - Multiplies C and D together

What I would ideally like on sheet 1 is, column B's cells when clicked on bring up a drop down list of all the item descriptions from column A on sheet 2.

Lets say I select the third item down the drop down list which corresponds to cell A3 on sheet 2. It will then automatically take the value from D3 on sheet 2 and put it in the cell in column C of the same row I just used the drop down list in.

I hope I explained this clearly. Please let me know if its unclear in anyway.

Any assistance would be greatly appreciated.

Thank you,
Mark

 

[Akashwani]

Hi Mark,

So you need a Data Validation List for column B on sheet1 and a Vlookup for Column C on sheet1.

Take a look here on how to create a data validation list (or 2)...
http://www.youtube.com/user/ExcelIsFun#p/search/0/So51kA_LVK8

Highlight your range in B of sheet1 and press alt-D-L.
From the "Allow" drop down, select List, click in the source box then click on sheet2 and highlight the range that you want to create your drop down list from, click Ok.

In column C of Sheet1 you will need a Vlookup like this =VLOOKUP(B3,Sheet2!$B$3:$C$33,2,0)

I hope this has shed some light on a solution for you.

Good luck

Ak

 

[mantis]

Thanks so much Akashwani,

I will give that all a look/try tomorrow morning.

Thanks again,
Mark

 

[mantis]

Hi Ak, thanks again for your help. Didn't get a chance to work on it yesterday so am tyring now.

I did as you said but excel 2007 will not allow referencing to other work sheets or workbooks when using data validation. is this a setting I can change? dont really want to put everything on the same worksheet.

Thanks,
Mark

 

[Akashwani]

Hi Mark,

I'm not sure what your problem is, I'm sure a Data Validation list can be used on any sheet within your Workbook.

Try this, create a Named Range on sheet2 of the list that you want to create a drop down list from, then refer to that Named Range in your Data Validation List on sheet1.
To create a Named Range, highlight your data, right click, select Name A Range, name it what you like, something relevant!! On Sheet1 highlight the range that you want your drop down list to appear in, press alt-D-L or go to Data-Data Validation, in the Allow box select List, click in the Source box and press F3, double click on your Named Range and then click OK. You should now have the drop down list in all your selected cells.

Good Luck and check out the Data Validation videos at ExcelIsFun.

Ak

 

[mantis]

Thanks AK,

Yes you're spot on. It embarrassing I didn't work that out myself as I've been naming ranges for vlookup throughout the worksheet and linking them to the data validation you showed me how to do. I never thought to name that range and then retry the data validation once it was named.

So anyone reading who's excel wont let them link data validation to a different sheet? Do as AK said, name the range then link your data validation to that range....works perfectly.

Thanks for been a life saver AK,
Mark