Drop down validation & additional data

**Bbongo8** · 01-06-2012, 03:10 PM

Hi,

I have a worksheet with several drop down lists. List 1 determines what is available in list 2, list 2 dictates list 3. I want to have a fourth list, but the criteria for this list is also dependent on list 2 and when creating the validity, I am asked if I want to replace what is set up for list 3 - I don't! Is there a way to do this, or get around it?

Also, the options in each list are parts for sale, is there any way I can associate a cost with each of these (either in the drop down list, or in the table where the list is populated from) and display this in the adjacent cell, to be totaled later (this I can handle)

Thanks in advance for any advice
Ben

**dilipandey** · 01-06-2012, 03:47 PM

Hi Ben,

Welcome to the forum.
This is achievable using defined name ranges and Indirect function(s), please share the sample workbook describing clearly what you are looking for. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Bbongo8** · 01-06-2012, 04:31 PM

The file is attached. I wish to add the pink table data to the pink drop down list, based on the light green selection.

Also, each of the selections is the first 2 lists have a cost attached, how can I get the cost displayed in the red boxes?

Thanks
Ben

**dilipandey** · 01-07-2012, 02:30 AM

Hi Ben,

Thanks for the attachment. Below are my observations and solution as well - Off-course

Attachment has the data but should be formatted better - like first row of the data is header ( which you forget to mention and I figured out later)
Also, there is no cost (amount) listed on the sheet. You would need a cost to be display in B2 if the Door is Middleton and then for others as well.. right?

Now your main question. You need to add pink data in pink drop down list, based on light green selection.
Just a catch here, while navigating I found that if the light green selection has Solid then there is no respective selection for pink data i.e., pink data has the following headings only and Solid is missing here :-

SIMPLICITY MACKINTOSH ELEGANCE SPIRIT VIVARA BRILLIANCE PRAIRIE DIAMOND BEVEL_DIAMOND CLASSIC MURANO STD CLEAR

I am sure, this is something you can correct later as I have seen you have used the defined named correctly, so it would not be a big issue for you.

See the attached file and let me know if this helps you. Feel free to get back if you stuck somewhere

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

**Bbongo8** · 01-07-2012, 03:16 AM

Thanks DILIPandey,

Sorry for the poor explanation but thanks for your patience!

That's great. However, the tables will not be couloured, I only did that to make it easy to explain, so how do I replace the &"PINK" section of the formula?

I have added 'prices' to the doors and glass designs (attached), can you show me how to get the relevant price pulled into the red boxes when the selection is made, please?

Thanks again for you solution and explanation.

**dilipandey** · 01-07-2012, 03:29 AM

Hi Ben,

Pink is just a identifier which is used to complete a defined name in the formula and is nothing to do with pink color

e.g., if I need to use name "Diamond_Pink"), that is where I have included "_Pink" in the formula which you can change as per your choice.

Also, I have formulated the sheet to pick up the cost as well, see the attachment and let me know if that is fine.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Bbongo8** · 01-07-2012, 04:05 AM

That absoulutely great. Thank you very much - seriously!

I don't really understand it yet, but copy/paste and swapping cell numbers will sort the rest.

Thanks again

**dilipandey** · 01-07-2012, 04:10 AM

You are welcome Ben..

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

**Bbongo8** · 01-07-2012, 02:07 PM

Hi,

Sorry about this, but I have added another column (AO) and its outside of the pink pre-selected range. Can you explain the formula so I can add the new column - and use it in other places as the workbook grows?

Thanks
Ben

**dilipandey** · 01-08-2012, 06:16 AM

Hi Ben,

Any column on the right side of pink area can be added easily. If you have added AO, where the data is in AO8: AO27 (including headers), then you can add it like data is at AG (STD_CLEAR) which covers the range (AO9:AO27) with the defined name STD_CLEAR_PINK.
Also, it can be used in Cell G2 in Data Validation (Alt + D + L) as a list using following formula:-

=INDIRECT(C2&"_PINK")

STD_CLEAR would be used as name STD_CLEAR_PINK.

Hope this clarifies to some extent.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Haseeb A** · 01-08-2012, 02:15 PM

Another way,

If you have new entry insert a row above the 'End' section in each category.
If you have new column just add it to the end, make sure 'End' is placed at the end.

See the attached.