Excel help please

[Athenajm80]

Hi. I am making an annual leave spreadsheet for work so have worksheets for each month and rows for each member of staff. I have done drop-down lists for the various leave reasons, which I feel probably could have been done more easily but it's done now.

I want to get Excel to magically fill in people's non working days for me. I don't know if this is possible? So for example, row 5 on each sheet had the days of the week. I am row 7 and let's say row 10 is someone who doesn't work Tuesday or Thurs. Is there a formula or something where I can say if row 5 says Wed then row 7 on same column says NWD, if row 5 says Tues or Thurs then row 10 says NWD (etc for each row/staff member).

I have googled and tried a few things but I am not getting anywhere.

Pushing my luck,but if it is possible, then is there a way I can say do that but for each sheet, without having to manually put the formula on each sheet.

Thanks if anyone reads this and can help!

=IF(OR(B5=“Tues”,B5=“Thurs”),”NWD”,””)

You can use $ to lock to a particular cell too, which can help for bulk formulas etc

Great formula above, but wouldn't VLOOKUP do it, too?

@HollowTalk

Great formula above, but wouldn't VLOOKUP do it, too?

If you have Excel 365 then check out XLOOKUP. Game changer.

If there are a few schedule patterns, I would put those in a table on one page. You could assign these by job role, individual, or just with a code value. I’m guessing code value will make the most sense since people’s schedules may change during the year.

Then on any given month you can associate a person with a schedule pattern code value and the appropriate formulas will apply with a vLookup.

Much less prone to human error and much less fiddly.

@NoVaxDjokovic

=IF(OR(B5=“Tues”,B5=“Thurs”),”NWD”,””)

So would I put this formula across the whole row for the person? And is there anyway to make it automatically do it for each sheet, or do I have to put it in manually each month?

How would VLOOKUP work?

I don't think we have 365 at work although I do on my home computer.

Thank you for everyone's help! I am ok on Excel to a point, but this level is beyond me and I struggle to Google without knowing the right terms as then I end up on random sites! I have a control sheet which so far only has the list for the drop down menus for each day (so AL, NWD, HALF DAY, SICK ETC) but, remembering back to one we had in a previous job, I'm sure there was more on that sheet. I vaguely remember a colleague fiddling with stuff on the control sheet which altered the main sheets. Admittedly that tracker was more fancy than the one I want, we don't need that level of detail in mine as the team is much smaller.

Finally, does anyone know of a guide to this bit of Excel as I guess it would be handy for reference in the future if I do want to add more features.

Thanks again 😊