Cell referencing

[ajlo]

In the attached document, I have 3 sheets to make a schedule.

Sheet 1 is the master.
Sheet 2 is the first K class (K1).
Sheet 3 is the second K class (K2).

I'm wondering if there is a function or functions to extract the data from the master to the K2 sheet and have any changes made in the master schedule to be reflected in K2.
I have manually setup exactly what I want in the K1 sheet.

Any help would be much appreciated.
Thank you.

[FDibbins]

short answer...with all that merging...not with a regular formula, corry

Now, if you can drag yourself away from needing to have all those merged cells, and just go with regular cells, then maybe we can get somewhere

[ramananhrm]

FDibbins is right.

Please try this file if possible.

[ajlo]

Thanks for your help. This works great. How were you able to reference the sort sheet by only putting "!" instead of the whole sheet name i.e. 'MASTER'!

[ramananhrm]

The '!' is used to differentiating the cell name & the sheet name.

So we can replace the sheet name & refer to the name listed in the cells using Indirect()

I have attached the reference & marked in Yellow.

Please have a look.

[FDibbins]

You can shorten that formula a bit to this...
=INDEX(INDIRECT("'"&B$2&"'!$B$2:$B$43"),MATCH($A3,INDIRECT("'"&B$2&"'!$A$2:$A$43"),0))

[ajlo]

Yes. I've got it now. Thank you for this. I realized this shortly after I sent it. I'm just used to referencing the sheet simply by clicking on it to select my range reference. This is a neat trick though.

[ajlo]

Thank you for your help! I have two more challenges for you.

Challenge 1.
I would like to extract all of the PE classes from the Monday sheet to the PE sheet. Is there a formula for that?
I have manually input the data in on the attached workbook as to what I want. I realize including classes, "KC', "KS", maybe a stretch, but was wondering if there was a formula to save some time from manual input.

Challenge 2
On the Monday sheet, I have figured out a way to lookup the special classes that each classroom teacher has. I'm wondering if there is a way to get rid of the blanks and look up the next instance of "1" in the lookup table.

For example, I have the following right now:
ART
Blank cell
MUSIC
Blank cell

and I want:
ART
MUSIC

Any help would be much appreciated. Thank you!