Excel: Dropdown list depending on colorsLocked
I have 2 column say City(Column) & Area(Column B) within City. I need to create a dropdown list for column B depending on the Background Color Present in Cell in Column A
Column A Details
London(Cell with Green Background)
Mumbai(Cell with Yellow Background)
Newyork(Cell with Yellow Background)
Area 1(Cell with Yellow Background)
Area 2(Cell with Green Background)
Area 3(Cell with Yellow Background)
Now Column C is a list containing values in column A & Column D containing the lists from Column B.
My requirement is When I select London in column C, Column D should only list Area 2 and when I select Mumbai or Newyork, Column D should list Area1 & Area3.
Can someone help me with the same?