+ Reply to Thread
Results 1 to 8 of 8

Link multiple sheets with data entered into one propagating to others w/different color

Hybrid View

  1. #1
    Registered User
    Join Date
    01-25-2017
    Location
    New York
    MS-Off Ver
    2011
    Posts
    8

    Link multiple sheets with data entered into one propagating to others w/different color

    Hi All, I'd appreciate feedback on this.

    I have sheets A,B,C in a workbook. This is for appointment scheduling of 3 doctors (Doctor A, Doctor B, Doctor C) sharing office rooms.
    - Anything entered into A should get cell fill color Red and propagate to cells in sheets B and C using Red fill color.
    - Anything entered into B should get cell fill color yellow and propagate to cells in sheets A and C using yellow fill color.
    - Anything entered into C should get cell fill color green and propagate to cells in sheets A and B using green fill color.
    - I can delete any of the entered data in any sheet and it will be deleted from all sheets and clear the fill color.

    I know I can link cells in different sheets but not sure if this is used for copying v.s. just formula. I can also color code regions in each sheet based on conditional formatting (if cell has data make green) but don't know how this would work if each sheet has a different color. i.e.

    please see attached xlsx sheet sample.



    Any help here appreciated !!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Link multiple sheets with data entered into one propagating to others w/different colo

    Hi @ jlukar

    I think the easiest way to do what you need is to use some macros involving Worksheet_Change events.
    I've prepared this test book for you.
    please check it.
    Attached Files Attached Files
    Barriers are there for those who don't want to dream

  3. #3
    Registered User
    Join Date
    01-25-2017
    Location
    New York
    MS-Off Ver
    2011
    Posts
    8

    Thumbs up Re: Link multiple sheets with data entered into one propagating to others w/different colo

    Thank you so much @ AMvichopalacios

    this is what I needed! My only issue now is that
    if I merge two cells for say "Doctor A", only the 1st cell data and red fill color
    is carried over to other sheets. If you point me to the right direction
    I can fiddle more with the VB script.

    Thanks again!

  4. #4
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Link multiple sheets with data entered into one propagating to others w/different colo

    Yes, I've put some restriction for one cell only in each sheet code. (There is one for each Dr'page.)
    If Target.Cells.Count > 1 Then Exit Sub
    and then worked everything for one cell.

    If you want to work with more cells (I imagine 2 or 3 cells in the same column, to form a LONG visit), you'll have to define the range to work with, and then modify all 3 macros to act over the range.

    Tell us how are you doing , and if you need help, just ask.

  5. #5
    Registered User
    Join Date
    01-25-2017
    Location
    New York
    MS-Off Ver
    2011
    Posts
    8

    Thumbs up Re: Link multiple sheets with data entered into one propagating to others w/different colo

    This is awesome help @ vichopalacios. I will work on it and let you know.

  6. #6
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Link multiple sheets with data entered into one propagating to others w/different colo

    Just a suggestion:
    Merged cells may look better or nice, but when copying, deleting, moving using VBA may become a headache!!
    It is much, much easier to work with 2 or 3 cells ( with the same patient name), than with a merged range, at least when one is beginning to work with VBA.

    Vicho

  7. #7
    Registered User
    Join Date
    01-25-2017
    Location
    New York
    MS-Off Ver
    2011
    Posts
    8

    Re: Link multiple sheets with data entered into one propagating to others w/different colo

    This is what i will do as I don't have too much experience with VB. I will take your advise. TY.

  8. #8
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Link multiple sheets with data entered into one propagating to others w/different colo

    @ jlukar

    Following the forum rules, please mark the thread as SOLVED.
    If you feel that my help has been useful for you , I'll appreciate a click on "*AddReputation", down on the lower left corner of the reply window.

+ 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. Replies: 2
    Last Post: 05-30-2013, 11:53 AM
  2. Macro to copy specific color cells frm multiple sheets & paste link to other similar wrkbk
    By rahulmalhotra in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2013, 11:23 AM
  3. Replies: 1
    Last Post: 08-19-2012, 02:59 PM
  4. Propagating changes from 1 spreadsheet to multiple ones
    By fred2028 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-09-2011, 04:41 AM
  5. Link sheets & populate cell based on entered text
    By Vachoen in forum Excel General
    Replies: 4
    Last Post: 02-16-2009, 10:10 PM
  6. propagating tabed sheets
    By mammut89 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2008, 08:11 AM
  7. Compiling all data from multiple sheets as its entered
    By MatthewFlinchem in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-14-2007, 01:58 PM
  8. [SOLVED] data entered on multiple sheets also added to summary sheet
    By Nadia in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-14-2005, 04:05 AM

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