Drop Down menu in cells

[Haplo]

Can anyone please help me, I am trying to make a works roster simple by adding drop down menus in cells, rather then typing everything in.
I have the following in two columns on Sheet 1 and the roster on Sheet 2

Work Title Required
WORK 1
DCS 1
DCS DRIVING 1
DRIVING 1
STORES 1
STORES AM/PM 1
BASE DAY B
TRAINING T
ANNUAL LEAVE A/L
SICK S

The top six with 1's will be in different colours

So say I click on an empty cell for the Drop Down Menu and I want to say select "WORK," I want it to produce the next cell in the table ie. "1" and so on for the others.
I have tried using =IFNA(VLOOKUP, but it does not allow me to look up in the same cell i want the other information to be placed there, (hoping that is understandable).

Can this be done
Thanks in Advance
Haplo

 

[Haplo]

Here is a pic of the table to make it clearer
Again Thanks

 

[Blasirl]

I am trying to understand what you want. It seems as if you want a drop down where you select a value from the work title column and the output should be the corresponding cell in Required. This seems pretty simplistic at first glance. Every time I tried a solution, it seemed as if I were missing something. It seems that you may be able to make a drop-down with conditional formatting for the Required cell based on the Work Title.

Can you talk through what you are trying to do, in plain english, no Excel info , if this then that etc?

 

[Haplo]

I am trying to understand what you want. It seems as if you want a drop down where you select a value from the work title column and the output should be the corresponding cell in Required. This seems pretty simplistic at first glance. Every time I tried a solution, it seemed as if I were missing something. It seems that you may be able to make a drop-down with conditional formatting for the Required cell based on the Work Title.

Can you talk through what you are trying to do, in plain english, no Excel info , if this then that etc?

Hello Blasirl.
Thank you for replying.

Basically what you said, "It seems as if you want a drop down where you select a value from the work title column and the output should be the corresponding cell in Required", is what I am trying to do, and the same with you, something seems to be missing as I always get an error message.

I set up a cell for the drop down, which is easy enough if you just want what is in the menu, i.e. if I click on WORK, I get WORK, what I want is to click on WORK and get from the next cell i.e.. 1,
I have tried =IFNA(VLOOKUP and =VLOOKUP but all I get is an error message stating, "Microsoft Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you."

Again Thanks

 

[Blasirl]

Again, what is the overall picture here, not the micro view. Can you talk through what you are trying to do, in plain english. Why do you want to do this? I ask as it will help me understand what approach to use.

 

[Haplo]

Again, what is the overall picture here, not the micro view. Can you talk through what you are trying to do, in plain english. Why do you want to do this? I ask as it will help me understand what approach to use.

I am trying to do a 2 week roster for work, for 30 employees, there is no sequential work pattern, so one week a person could work say Monday, Wednesday, Friday, the next week they could be working Monday, Tuesday and Thursday, so each week is different.
What I am trying to achieve is a grid 14x30, where instead of typing each day for each person and then colouring for each activity i.e. either Work, Driving, or DCS Driving etc.
Each cell has a drop down where I can pick their Work Title and the result shows the correct colour (Required Column) as per the Work Table I posted.
The Numbers in the Required Column would be used to total the number of employees working that day.

I hope this explains what i'm trying to do.

Again Thank You