Drop down chooses defined list

This requirement needs more definition. I cannot see how you "Copy and Paste" from the Clinic Setup sheet to the daily sheets. There is obviously some shuffling around being done. Also, where are the names in row 10 coming from?

For example I am looking at cells I23 to J37 for Monday and Tuesday. How did you determine that Mondays have multiple scheduling slots while Tuesday has a general open slot? And where, on the Clinic Setup Sheet did this data come from?

There might be a solution if the data on the Clinic Setup Sheet were "normalized" and put into an Excel table, that would make lookups a lot easier. Also it would help to get rid of merged cells. There are ways to work around this.

Remove C6 to C11 and then paste this into C6:

=TRANSPOSE(LET(f,FILTER(Data!$B$2:$G$52,Data!$A$2:$A$52=Sheet2!C5),FILTER(f,f<>0)))

You can remove all other data and copy paste this to the first cell of each block. Is this what you want?

There is no drop-down selector on the daily sheets in that file (which I did not realise was different, so I've just wasted 10 minutes of my life - good job I'm retired!).

Do you need help with adding drop-down boxes? If so, where do you want them to be?

I think you could have explianed this all in a more straightforward way, but that's water under the bridge - let's start afresh.

Merged cells aren't going to work, by the way - where there are two possible patterns, each will need its own name.

Yes - perfect sense - so you want a drop-down above each doctor - yes?

Much more complicated than the other red herring workbook (grrrr!), but doable with a few tweaks to the Clinic Setup tab - I presume this is OK?

Happy to look at it, but will take a little while. I am probably going to spend time on this, then come back and find that someone else has done it faster!

OK - so please IGNORE formatting for now - that can be solved later using conritional formatting, but might need simplifying a little.

Look ONLY at Dr Ratna on the Monday sheet:

1. Check that the drop-down list above contains the correct surgeries. If not, which should not be there?
2. Try changing the surgery - do the correct entries and times populate below?

I have ONLY done it for Dr Ratna on Monday at this stage - it's important to get that right before proliferating the formula through the workbook.

I have changed the attachment - you might need to close and download again.

There will be aesthetic tweaks - that doesn't matter at the moment.

OK - so I am guessing that we really want only AM clinics in that list and the PM ones in a drop-down lower down the page - would this be a fair assumption? I want to get everything right for that first doctor before we move forward, and if changes need to be made to the layout, we need to do that now.

Right. Please first check ALL surgeries in the drop-down list - do they ALL have either AM or PM in their name? If not, which should have what? Once you've anwered this, I'll take the next step.

OK - so you didn't quite answer the question, but I've made a few assumptions and changed the layout of the surgeries setup sheet a bit.

Look at the first doctor on Monday - do AM and PM both do what you want? Consider all possible combinations, please.

Well, they don't. I have just spotted two mistakes, so have a look at this and check it PROPERLY, please. That is, check ALL surgeries are happening in the right place and that ALL surgeries are on the correct list.

OK - I am going to do this for an entire sheet for you to check, then we'll look at the formatting.

Whilst I am doing this, please make a list of ALL formatting and conditions that you'd like to include if possible.

Once all this is done, you should be able to copy the Monday sheet yourself and tweak it for the other four days.

Here's a full Monday populated. There is now a NONE option at the top of the drop-down lists so that this can be chosen where no surgery will be happening. This can be changed to whatever you like on the LOOKUPS sheet in the formula.

I am breaking for a (very late!) breakfast now. Back soon.

What about the times? Are you prissy about the times only being formatted when there are times there, or can it be a generic grid big enough for any number of times? The more prissy we get, the more processor intensive the CF will be come, so simple is best.

I don't know how that answers my question ... ???

Attached with all but gey background formatting (and dates not done yet) - comments?

Have a look at the attached. Is there any formatting that ideally would be different? Say so now if there is.

It will not break your formula as long as:

1. You do not MOVE anything.
2. All surgery names contain either AM or PM.
3. If you add surgeries, you add them following the same pattern (four columns and a blank column between each surgery). There's room for TWO more at the moment - any more than that, we'd need to tweak the formula.

I suggest you have a play with the attached, making the sort of changes you might want to make (and making a not of each of those changes so that we can unpick them if anything goes wrong). If anything breaks, provide the workbook and tell me EXACTLY what steps you took to break it.

Once you have thoroughly stress tested the Monday and setup sheets, then you can go ahead and duplicate the sheets (I would do this by copying the SHEETS themselves, NOT cutting amd pasting. Conditional formatting SHOULD carry across that way, but you'll need to check. But again, don't do this until AFTER the testing phase.

EDIT:

I have a meeting to attend but will be back in about 30 minutes

Take your time - better not to rush this.

If you want to put it across the last two columns, then the formula will change to this:

=IFNA(LET(m,MATCH(D8,'Clinic Setup'!$B$10:$DT$10,0),d,'Clinic Setup'!$B$11:$DT$52,c,CHOOSECOLS(d,m-2,m-1,m,m+1),f,IF(c=0,"",c),FILTER(f,INDEX(f,,1)<>"")),"")

This would need copying to the origin cell (time top left) of each surgery block. Copy and paste the CELL once applied to B11 on the Monday sheet - don't copy and paste the formula into other cells as the list reference (D8) won't change if you do.

Make sense? Have a go.

I've done it for you in this copy.

No - I've done it for you (see previous post). You mean D10 and E10.

Glad to have helped.

Let us know how you get on - shout if you have any issues.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.