Using drop down list as selection criteria for the next cell

**rm7302** · 05-31-2008, 06:48 PM

Hi,
I am working to create a spreadsheet which is going to be designed as follows:

One worksheet will have a library of job elements for assembly process with standard times on it.For example:Pick up a bolt(0.621m),install gasket(0.231m) etc.
The main worksheet will have a dropdown list which will list all the job elements discussed above.It will also have the quantity.So in otherwords,to pick up one bolt the time would be 0.621 minutes.But if anyone picks up 4 bolts the time would be 2.115 minutes,which can be calculated by the formula.So I want to display the job element,qty and the time in three separate rows.This worksheet will also calculate the total time for the assembly of the components which might contain more than thousand job elements.
My question is how do I ensure that picking up any of these job elements will give me the correct time.Is this something that can be done by formulas or anything thats built in excel or I need to run a macro or VBA to run some codes for IF...ELSE....THEN statements.There are too many conditions like If pick up one bolt time is 0.621 minutes,if pick up 4 bolts time is 2.115 minutes or if pick up a gasket time is 0.231 minutes.....how do I satisfy all the conditions.
Hope I am able to explain the scenario.Please let me know if anybody have any suggestions.

**oldchippy** · 06-01-2008, 03:36 AM

Hi,

May be these links will help?

http://www.techonthenet.com/excel/formulas/vlookup.php

http://www.contextures.com/xlFunctions02.html

**rm7302** · 06-01-2008, 04:50 PM

This helps but how would I address the following scenario:
If from the pick up menu I select pick up bolt and then in qty I enter a value of 2,3,4 etc I will need to calculate the time for picking up 2 bolts or 3 or 4,which are different so I have to write a formula for that.Again,If i select a different job element like install a gasket that will have a different time,so how do I incorporate all these formula in one cell.Do I have to use IF and Nested IF but how many Nested IF functions will excel support.Hope this helps to clarify my scenario.
Thanks for the help

**oldchippy** · 06-01-2008, 05:36 PM

Hi,

It would be better if you could up load a zipped sample of your data, with the expected result, then we would have a better idea on what you are trying to achieve.

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**rm7302** · 06-01-2008, 06:13 PM

Hello,

I created this example file in Star Writer but saved in .xls format.I think you should be able to open this.Thanks for you help oldchippy.

**oldchippy** · 06-02-2008, 05:46 AM

Hi,

Here's a starter, but you need to tell me how the other costs are calculated when the quantity is more than 1.

**rm7302** · 06-02-2008, 09:49 AM

oldchippy,thanks for your help.I have attached the spreadsheet with more information and also a word doc for an example of time elements for you.I will transfer these times from word doc to excel and create a database.
When you open up the spreadhseet go to the Databse tab where I have more information,which hopefully better explains the situtation.Thanks again

Confidential information removed by oldchippy

**oldchippy** · 06-02-2008, 11:33 AM

Hi,

Does this help? I've put your list in the database with a column for the additional costs if over a unit of 1

Confidential information removed by oldchippy

**rm7302** · 06-02-2008, 07:33 PM

Thanks a lot buddy....it works....u r a genius!!!!!!!!
Can I contact you in future if I need.

**oldchippy** · 06-03-2008, 03:25 AM

Yes, if it's related to this original question, just ask another question here. If it's unrelated start a new thread, if you want me to respond to a new thread then PM me as well.

Good luck and thanks for the feedback