+ Reply to Thread
Results 1 to 10 of 10

Linking conditionally formatted cells to other sheets in smae workbook

  1. #1
    Registered User
    Join Date
    11-27-2015
    Location
    Queensland
    MS-Off Ver
    2010
    Posts
    3

    Angry 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
    Attached Files Attached Files
    Last edited by guins; 11-27-2015 at 11:32 PM. Reason: Updates

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,114

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    Hi, welcome to the forum

    Only sheet1 has any data on it, the others are all empty? Did you upload the correct file?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    11-27-2015
    Location
    Queensland
    MS-Off Ver
    2010
    Posts
    3

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    Hi Ford,

    Thanks for the welcome - super keen to continue on the Excel journey

    Yeah, I uploaded the correct one.

    Sheet 1 is the master sheet and I wish to link certain elements as explained in the original thread to the other sheets.

    Apologies if my original thread didn't make sense - my brains probs not working properly due to this blessed spreadsheet.

    Cheers,
    Joe

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,114

    Re: Linking conditionally formatted cells to other sheets in smae workbook

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

  5. #5
    Registered User
    Join Date
    11-27-2015
    Location
    Queensland
    MS-Off Ver
    2010
    Posts
    3

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    Hey Ford,

    Thanks an absolute million for taking the time to answer mate, it is greatly appreciated.
    If we can get to where I think must surely be possible, it will be the biggest time saver EVER!!!!

    Essentially, every page after the master is simply a replica of certain rows and columns from the master.

    Copy and paste works fine, but obviously, this does not then auto update every time a change is made on the master, which is the ultimate goal.

    The dream would be that every time an update is made on the master, it automatically updates the other sheets with the relevant info.

    My guess is that it is a link function required, but just not sure exactly how to achieve it

    Struggling to verbalise exactly the desired outcome Ford - hope this ( and attached sheet) clears it up mate.

    As previously mentioned, work Excel is 2010 version.

    Cheers again Ford - you're a legend!!!!

    Regards,
    Joe
    Attached Files Attached Files

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    9,005

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    • 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
    Last edited by sandy666; 11-28-2015 at 10:03 PM.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,114

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    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
    Attached Files Attached Files

  8. #8
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    9,005

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    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

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,114

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    Quote Originally Posted by sandy666 View Post
    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?)

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    San Escobar but I could be anywhere in the world
    MS-Off Ver
    power BI
    Posts
    9,005

    Re: Linking conditionally formatted cells to other sheets in smae workbook

    Quote Originally Posted by guins View Post
    ...
    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
    Last edited by sandy666; 11-28-2015 at 10:09 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to Sum cells that have been conditionally formatted
    By kbaz555 in forum Excel General
    Replies: 2
    Last Post: 08-25-2014, 12:24 PM
  2. Replies: 2
    Last Post: 05-30-2013, 11:53 AM
  3. Linking conditionally formatted cells from one to another maintaining the format
    By adkinsj3 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2013, 05:06 PM
  4. counting conditionally formatted cells
    By chop924 in forum Excel General
    Replies: 3
    Last Post: 05-09-2012, 11:44 AM
  5. [SOLVED] Conditionally Copy Cells from Multiple Sheets and Paste in Another Workbook
    By Gos-C in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-07-2012, 09:22 AM
  6. Replies: 10
    Last Post: 11-08-2010, 10:36 AM
  7. Sum conditionally formatted cells
    By akabraha in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-04-2010, 02:22 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1