Formula that recognizes data from a drop down list selection

[dwoodson297]

I am a somewhat beginner at advanced excel spreadsheets...tried to up my skills.

I want to be able to select an item from a list and have a formula that uses a a variation of its information.

For example, In column A, I have a drop down list with multiple items (4x8x16, 8x8x16, etc). In Column B, I want it to recognize my selection and input a corresponding area (4x16 or 8x16 in this case) into a formula. So if I select 4x8x16 from the list, I want 4x16 or 64 to be automatically inserted in my formula. If I select 8x8x16 then it would insert 8x16 or 128.

I hope this makes sense. Any suggestions?

[JBeaucaire]

Put this formula in B1, then copy down as needed:

=IFERROR(TRIM(LEFT(SUBSTITUTE($A1, "x", "     "), 5)) * TRIM(RIGHT(SUBSTITUTE($A1, "x","     "), 5)), "")

[dwoodson297]

Awesome...that works. That is probably the most complicated excel formula I have every seen.

I'll be honest, I see the basic form of the formula, but I'm not sure exactly how each piece of it works. With that said, I have one more question.

How do I modify this formula to take into account the following.
Your formula appears to be taking the first number and multiplying it by the last (4x8x16)=64 or (8x8x16)=128 What if I want to make it do the same for the second and the last numbers? (4x8x16)=128 or (8x8x16)=128

Thanks

[JBeaucaire]

Like so:

=IFERROR(TRIM(MID(SUBSTITUTE($A2, "x", "     "), 5, 5)) * TRIM(RIGHT(SUBSTITUTE($A2, "x","     "), 5)), "")

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.