Linking conditionally formatted cells to other sheets in smae workbook

Hi all,

Micro version of overall master version attached.

Current
Sheet 1 - Master version

Desired
Sheet 2 - Trainer 1 plan
* Columns A-F

Sheet 3 - Trainer 2 plan
* Columns A & G-L

Sheet 4 - Company A plan
* Rows 2-16

Sheet 5 - Company B plan
* Rows 2-8 & 17-22

When I try copy / paste / link to sheets 2-5, the conditionally formatted cells come up as letters only with no colour and every empty cell in master sheet comes up as 0.

Furthermore, I'm hopin that when I update the Master (Sheet 1), all correspondingly effected sheets will also automatically update.

Been trying for AGES to find a solution, any and all help would be greatly appreciated folks

Regards,
Joe

Hi, welcome to the forum

Only sheet1 has any data on it, the others are all empty? Did you upload the correct file?

OK, so what would a sample of your expected output look like?

• copy master one time
• will be master (2)
• in every cell on master (2) add: master!(address). e.g in A2 type master!A2 enter
  
• if there are any formulas add the same to cells or ranges, e.g. =COUNTIF(E10:E12,"Y")+COUNTIF(E14:E16,"Y")+COUNTIF(E18:E23,"Y") will be =COUNTIF(master!E10:E12,"Y")+COUNTIF(master!E14:E16,"Y")+COUNTIF(master!E18:E23,"Y") or type there ony master!(cell address) because every value will be mirrored from master to all other tabs. only master tab will be counting, the rest is a values mirror.
• do that for all cells in your table (you will do that only one time)
• copy "prepared" master (2) as many times as you need (in this case probably 5 because one was copied from the begining)
• change suitable names of tabs
• go to first copied tab (master (2) - now: Trainer 1)
  1. you want to see only A:J columns so select K:AC and hide it
  2. do the same for any other tabs with columns or rows, leave unhided what you want visible, hide what you want invisible
• now every changes on master tab will be mirrored to all other tabs

edit:
try (if you wish of course) on copy of your workbook, not original version

I have set up the 1st sheet for you, some things that I have added/changed/deleted...

1. I have saved your file in .xlsx format (which is for excel versions above 2003), it allows more functions than .xls (which is for versions 2003 and below)

2. I have used this formula to pull in the data you want...
B4=INDEX(Master!$A:$AC,MATCH($A4,Master!$A:$A,0),MATCH(B$3,Master!$3:$3,0))
You can copy this to all cells you need to have the data pulled in to. It looks at the row "heading" (column A) and teh column "heading" (row 3)
By doing it this way, you can copy to whichever sheet you want, and it will ONLY pull in the contents from matching row/column intersects

3. I removed the Merge-Center in Trainer and Room and replaced it with Center Across Selection (find taht under Format/Alignment/Horizontal. Merge/Center can cause problems with formulas when you copy the, we all try to avoid M/C if possible

4. I deleted some duplicate CF rules and changed the Applies To range on those that remained from...
=$E$10:$E$12,$E$14:$E$16,$B$10:$B$12,$B$14:$B$16,$B$18:$B$23,$J$10:$J$12,$J$18:$J$23,$E$18:$E$23,$H$18:$H$23,$H$10:$H$12,$H$14:$H$16,$J$38:$J$40
to...
=$E$10:$E$23,$H$10:$H$23,$J$10:$J$23,$B$10:$B$23
You dont need to exclude the heading rows (13, 17, etc)

See the attached file for the changes I have made

Originally Posted by sandy666

Ford,
it's just a small note
example:
IF I want change SOA to any other, e.g. XXZ, I need to go to every tab

Not so, the 2nd MATCH will make the forumla adjust by itself
Each tab (my understanding) will have it's own (plus some common) headings, so that MATCH will read those headings and pull from the relevant column
(or am I missing something here?)

Originally Posted by guins

...
Essentially, every page after the master is simply a replica of certain rows and columns from the master.
...
Copy and paste works fine (...)
Joe

as above , every tab is a mirror of master, so everything there is like on master.

ps. sorry for a long time answer but the system does not send e-mails or something happen I can't get any