Need Cells with a value other then what is displayed.

[ubenhad4]

Hello

I need a little help.

I am trying to make a list that displays a material size but the actual value is different then what is displayed.

So a list would Read
Displayed Value for calculation
1x4 3 1/2
1x6 5 1/2
1x8 7 1/2
1x10 9 1/2



What im trying to create is this

Side material Top Material
(from list) 1x4 (From list) 1x8
value for calc 3 1/2 value for calc 7 1/2

I would like to be able to do a formula like side material 1x4 + top material 1x8 *2 +1 and the answer be 19 1/2

Hope this makes sense I really appreciate the help. Im just learning how to try and do a lot more with excel for calculating various things we build in a home like mirror frames, doors, timber trusses. Its crazy how much time the little bit I have tried has saved me.

[Richard Buttrey]

Hi,

Does the attached give you some ideas. Select from drop downs in yellow cells.

[ubenhad4]

That helped a ton thank you.

Now a new problem. I got it to work perfectly and then when I reopened the following was what the table looked like. I lost the drop down list under the material selection. It was working great and everything in the initial table was correct. What happened?

I really appreciate the help.Cut list.xls

[ubenhad4]

Here is my table its under the mirror sheet

[Richard Buttrey]

Hi.

Would you indicate which are your data entry/drop down cells and which are results cells. Also would you manually add your results explaining your calculatiuon in a note so that we know the end goal.

[ubenhad4]

This should explain. I want the material table as a drop down but only the left column as the choice but the value to be the second column.

[Richard Buttrey]

Hi,

See attached.

[ubenhad4]

Richard

That nailed it. Thank you very much.

No worries if you dont have time but can you explain the formula to me. I think I understand what it is doing but want to be clear. I have a couple other applications I want to make similar list for.

[Richard Buttrey]

Hi,

Re column H, The first IF(COUNTA() bit is just there to avoid having error #N/A for rows where there are no values in all of C:E.

The rest is just your original formula except that I created a range name 'material' which is set to H13:I20

The range name H13:H20 is given a range name 'lst_material' which is used in the data validation. It's good practice to use range names and if you need to use a data validation where the list is on another sheet then it's mandatory.

[ubenhad4]

Richard

If I want to extend the table to lets say 30 rows how do I get the formula to transfer to the new cells. I tried dragging the formula columns down and coping and pasting formula and it will not pull up the drop down list on the new rows.

[Richard Buttrey]

Hi,

You are copying Validations not contents or formats. Therefore you need to copy the existing cells, select the cells to which you want to paste and choose Paste Validations from the Paste Special options.

The short cut keys that I always use after the Ctrl-C for copy, is Ctrl-n for paste validations.

[ubenhad4]

Heres what I tried still does not bring the drop downs. Dont get what Im doing wrong here.

[ubenhad4]

I got it thank You

I was not using data validation of just 1st material.