How can i permanently reference an Item in a dropdown list, even when it is not selected?

**kyle-ops** · 02-16-2013, 09:06 PM

I am new to the forum so please forgive any mistakes/lack of information. I have pretty decent knowledge with Excel but not where it should be. I greatly appreciate any help with this, as i am stumped.

General Description:
The sheet that I am working with is used to quote injection mold tools (big blocks of steel that create plastic parts). I have a dropdown list with the items: Simple, Default, Complex. These items are selected on how complex the tool will be to build/design, therefore giving me a different price for a paticular tool, based on complexity.

In my notes section i always list the price given for Simple, Default, and Complex and then average all 3 for a middle of the road price. To this point i have been doing this manually but would like to automate it so that there is less room for error and also more time efficient.

Detailed Description:
Attached is a screenshot of my sheet that should include everything needed to reference. The arrows are traced dependents. In case it is hard to see, cell K25 is dependent on cell D20 (dropdown list referenced earlier) and the 'comlexity' range in column K. The list '0.75, 1.00, 1.25' is used for Simple, Default, Complex, respectively. Not shown but in cell D50 is my total tool cost that changes as the complexity is changed. Cell D50 uses the function =SUM(D33+D37+D45). What I need is for cells G38:G40 to populate according to the complexity and not be changed as i change the selected item in the dropdown list. I realize i could chang this sheet and easily achieve this but i need all listed items to reference for a breakdown of the total cost.

I hope this is a good explanation of what i am looking for. I appreciate everyones help!

**martindwilson** · 02-16-2013, 09:25 PM

In case it is hard to see

its really difficult to recreate a workbook from images!
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**protonLeah** · 02-16-2013, 09:40 PM

Why is this a poll?

**jeffreybrown** · 02-16-2013, 09:42 PM

Why does it matter? If individuals choose not to vote then they choose not to vote...

I see no harm...

**kyle-ops** · 02-16-2013, 09:53 PM

Martin,

Attached is a sample workbork with a blank sheet and a populated sheet. In cells G38, G39, G40 I have the total tool cost for Simple, Default, Complex. It is a manual input that I get from cell d50. Right now i have to select simple in cell D20, then look at the total cost in D50 and input that into cell G38, then repeat for the default and Complex selections in D20.

I would like G38, G39, G40 to populate accordingly and still be able to change my drop down selection in D20. Hopefully this is enough for you to provide any type of help. Thanks! Let me know if any thing else is needed.

**jeffreybrown** · 02-16-2013, 10:06 PM

Hi kyle-ops,

So are you saying based on the selection in D20 you would only have one of either D38, D39, or D40 filled in?

If so, place in D38 and copied down...

=IF($D$20=F38,$D$50,"")

Note: Sorry just read your original post and it sounds like you want all three filled in no matter the choice in D20...

**kyle-ops** · 02-16-2013, 10:08 PM

ProtonLeah,

I apologize for the poll. This is my first post on the forum and I guess i do not understand the intended use of polls. I posted the poll just to get some real Excel user feedback on the complexity of this post so that i can see where I am as far as my current knowledge of excel.

I looked for a way to remove it but did not see anything. If it is appropriate to delete it and you know how it can be removed, please let me know and i will take it down.

**jeffreybrown** · 02-16-2013, 10:24 PM

Hi kyle-ops,

Your post is fine so no need for any delete or such.

If you want all three cells (D38:D40) to have a result no matter what the selection in D20 then you will just have to make the calculations available somewhere else and point to that cell.

If you you are hiding column anyway, why not store all the results and link them?

**kyle-ops** · 02-16-2013, 10:27 PM

Hello jeffreybrown,

I need to have all 3 (G38,G39,G40) filled in. With the function you provided, if D20 has 'Simple,' for example, then only G38 returns the Total tool cost and G39,G40 return 'FALSE'.

To try and further clarify, cells G38,G39, G40 all need to be filled in with the respective Total Tool Cost(Cell D50). So D38 will show Total tool cost for Simple, G39 will show total tool cost for Default, and G40 for Complex.

But i need to be able to change the selection in D20 as needed, without affecting G38, G39, or G40.

Example... if Simple is selected from the dropdown list in D20, Cell G39 should show what the total tool cost (D50) is for a 'Default' selection and G40 would return total tool cost for a complex selection.

**kyle-ops** · 02-16-2013, 10:32 PM

Jeffreybrown,

I thought of that as well but curiousity got the best of me and I wanted to find a solution, if there is one. I also do not know what calculations would need to be made in a hidden column to achieve this. If you could provide an example i will do it that way. That would be a huge help!

**jeffreybrown** · 02-16-2013, 10:42 PM

If there is a solution outside of a helper column it escapes me..

As far as an example, I don't have one for your data, this is something you are going to have to think through...

If D50 holds the appropriate value as cell D20 is toggled between choices, you will just have to recreate the formula in one of the hidden columns to produce all three results.

Once you do this, you can create a vlookup in D50 to match the value in D20 to a lookup table.

Example:
P1 = Simple
P2 = Default
P3 = Complex

Q1 = Formula to calculate Simple
Q2 = Formula to calculate Default
Q3 = Formula to calculate Complex

In D50 >> = VLOOKUP(D20,P1:Q3,2,0)

Now in D38:D40 link to Q1:Q3

**kyle-ops** · 02-16-2013, 10:47 PM

Great, thank you for all of the help and quick responses. I will work through it and get it formatted accordingly.

**jeffreybrown** · 02-16-2013, 10:53 PM

Sounds good and thanks for the feedback. Good luck on the rest of the project.

View Poll Results: How complex is this solution?

How can i permanently reference an Item in a dropdown list, even when it is not selected?

LinkBack

Thread Tools

Rate This Thread

Display

How can i permanently reference an Item in a dropdown list, even when it is not selected?

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Re: How can i permanently reference an Item in a dropdown list, even when it is not select

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions