Dropdown list depending on another dropdown list and second condition

[Shanaya]

Hello,

I need help with an excel formula, which would really help me with my work and I just can't figure it out.

Example:
I have objects A and B. Each one of them can be small or large. I would like the color to be a drop down list, that depends on the drop down chosen in object and the certain column (small, large).

Capture1.PNG

I would need a following formula: if I choose from dropdown list the object A, than in the column Small, it would give me the options (dropdown) to choose either Black or Blue and in the column Large the options for Red or White.
If I choose from dropdown list the object B, than in the column Small, it would give me the options (dropdown) to choose either Black or green and in the column Large the options for Blue or yellow.

Capture.PNG


I hope you understand, what I need.

I would really appreciate your suggestions.


Shanaya

[mehmetcik]

I have created a drop down in A1, B1, C1

A1 uses the list A, B
B1 uses the List Small, Large
C1 uses the range M1:M2

Then I put this formula in M1
=IF(A1&B1="ASmall","Black","") & IF(A1&B1="ALarge"," Red","")&IF(A1&B1="BSmall","Black","")&IF(A1&B1="BLarge","Blue","")

and this formula in M2
=IF(A1&B1="ASmall","Blue","") & IF(A1&B1="ALarge","White","")&IF(A1&B1="BSmall","Green","")&IF(A1&B1="BLarge","Yellow","")

[Shanaya]

Thank you for such a quick reply, but I don't think this would work, because in the actual table, I have 512 possibilities (512 rows).

I attached my Example document. In Sheet1 if I choose from column A the object A, I would like in column B (Small) a drop down list from Sheet "Dropdown" and column "Colour" that applies to both conditions: Object = A, Size = Small

[mehmetcik]

I would use a Macro.

Right Click on Sheet1 at the bottom of excel and select view code.
Paste this code into the module that opens and close it.

Please Login or Register  to view this content.

Then Paste this into a normal macro module and close it.

Select "Dropdown" then select "Sheet1" to run the Macros

Please Login or Register  to view this content.

[Shanaya]

mehmetcik, you've helped me a lot, just by posting to use a macro.
I'm quite a beginner in excel, I know and use the basic functions and before your post, I never even heard of macros.
Over these few days, I've googled a lot about VBA and macros and I managed to record, what I want excel to do and it's actually working

In the cell B10 I have a dropdown. If I select a certain Word from this drop down, I'm going to run a specific macro, if I select another, it will run another macro with this code, which I found online:

Please Login or Register  to view this content.

I just have a little problem. The code I recorded is meant for the recorded row only (row 10). I would need the code to be changed, so that if I run it in cell B20, it should do the recorded procedure in row 20 only, if I run it in cell B145, it should do the recorded procedure only in row 145.
What do I need to change in the code?
Instead of for example "Range("D10").Select" do I have to write Range Column D? But this way excel would use the macro in the intire column at once, wouldn't it?

The code:

Please Login or Register  to view this content.

And how do I than set the "Run" macro for the whole column B?

[mehmetcik]

Administrative Note:

Hi

The modification is quite easy. But you need to insert Code Tags.


Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Shanaya]

Never mind, I got it with ActiveCell instead of Range.

mehmetcik, thank you for the tip