IF Command based on cell or IF/AND command -HELP needed

is this a 3-week cycle, or how do we know where to put the answer?

and btw, its always better to avoid "merge and center" and use "center across selection" instead. it makes calcs and references much easier

see if the attached gives you what you need?

I added data validation to your "shift name" on sheet2 to make sure the correct names are used

another suggestion would be to transpose your table so that the days go down instead of across. that way you can get a whole year into 1 sheet. i always find it better to construct a work sheet such that the data that will grow (ie as more records/data is added) goes from top to bottom, while fixed headings that will rarely increase/decrease go across the top

Thank you so much...this is just what I needed.
However a quick question...I know your formula is spread over 3 weeks but i wanted it to auto populate all the columns..to the end
This is your formula: =IF(VLOOKUP($B7,Shifts!$A$5:$AQ$52,COLUMN()-1,FALSE)=0,"",VLOOKUP($B7,Shifts!$A$5:$AQ$52,COLUMN()-1,FALSE))

Would I change AQ to IV ?

yes, my formula was based on you only having 3 weeks on your original sheet, so if you add more weeks, change the $AQ$ to AQ$, it should adjust itself across as you copy.

however, (and i know you have already put a lot of work into this), i would still suggest that you consider swinging your table around - you have far more rows than you have columns

If this answered your question, please take a moment to mark the tread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below