Dependable drop down list based on cell value

[Twixie]

Hello,

I want to create a dependend drop down list from a cell created by a macro.

So on sheet A there are teams with names in it. A2:A20 is team 1 A21:A39 is team 2 A40:A58 is team 3.
The macro fills Column D on Sheet B with team numbers for instance D2=3 D4=1 D5=1 D6=2 etc.

I would like a depenend drop down list in column E on Sheet B. So if the macro gave D2=3 i want E2 to only show the names from team 3.

I have seen how it is done with a drop downlist on column D and make the drop down list on E dependable. But i dont want a drop down list on column D.

Thank you twixie

[AliGW]

Is this for 365? If so, please add 365 to your forum profile.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[JohnTopley]

One way ....

Create named ranges "Team1","Team2","Team3"

in D2

Data Validation

Allow:

=List

Source:

=INDIRECT("Team" & D2)

[Twixie]

I’ve added a sample workbook.
In my workbook sheet 2 is generated by a macro

What I mean is on the opkomers sheet if in column E there is a 1 then I want a drop down list in column F only showing the names of the balancering sheet from A2:A7.

If there is a 2 then I want a drop down list in column F only showing the names of the balancering sheet from A8:A14.

Twixie

[AliGW]

And your Excel product, please? You have not yet updated your forum profile.

[Twixie]

This works thanks a lot!!