none
Linking an excel spreadsheet to an excel spreadsheet with conditional formatting RRS feed

  • Question

  • I track expiration dates via rules for various different projects and I am wondering if there is a way to link one spread sheet to another. 

    The background:

    I work for a construction company and each individual construction project has a specific subcontractor spreadsheet that we use to track expiration dates for different aspects of the project. I have set up rules for our dated columns  to turn blue when they are about to expire- rule is if the date in the cell is "this month" the cell with turn blue. And another rule when they do expire- if the date in the cell =less than NOW() it turns pink, that way we can keep track of what is coming up and what needs attention immediately. Every month I go through and pull all of the pink and blue cells from ever individual subcontractor spreadsheet and put them on a different spreadsheet so I can track it outside of the rest of the project, so not to overwhelm the project teams with what I am doing. So my question is, is there a way to set up the multiple subcontractor spreadsheets to my one tracking spreadsheet of approaching expiration and Expired dates when the cells changes to the blue or pink color? 

    In other words, when Subcontractor A has 4 blues cells and 2 pink cells, instead of me going through the whole Subcontractor A spreadsheet to pull the data, can it be linked to my overall tracking sheet and have the data appear on mine when it the cell changes colors?

    Any direction is helpful, thanks!

    Thursday, February 1, 2018 2:07 PM

All replies

  • The easiest way would be to use a macro that opens every file and look at all the dates, and copies any row that meets your criteria over to one workbook/worksheet.  How that is best done really depends on the structure of all of the sheets - if the information is largely the same, and in the same places, that would simplify the task. It also depends on your file storage system - if you need help with the macro, post back describing how the workbooks are stored and organized, and your actual criteria for choosing the data.
    Thursday, February 1, 2018 7:30 PM
  • Hi ErikaDoim,

    It will be helpful for us if you post a sample workbook with dummy data in it.

    We can try to provide a VBA code for moving the cells with blue and pink colors to any other sheets.

    From where you can easily find them and take appropriate actions.

    Also let us know, If you just want the solution using Conditional Formatting.

    I will try to move this thread to suitable forum for that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 2, 2018 2:56 AM
    Moderator
  • Hi ErikaDoim,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 7, 2018 9:39 AM
    Moderator
  • I track expiration dates via rules for various different projects and I am wondering if there is a way to link one spread sheet to another. 

    The background:

    I work for a construction company and each individual construction project has a specific subcontractor spreadsheet that we use to track expiration dates for different aspects of the project. I have set up rules for our dated columns  to turn blue when they are about to expire- rule is if the date in the cell is "this month" the cell with turn blue. And another rule when they do expire- if the date in the cell =less than NOW() it turns pink, that way we can keep track of what is coming up and what needs attention immediately. Every month I go through and pull all of the pink and blue cells from ever individual subcontractor spreadsheet and put them on a different spreadsheet so I can track it outside of the rest of the project, so not to overwhelm the project teams with what I am doing. So my question is, is there a way to set up the multiple subcontractor spreadsheets to my one tracking spreadsheet of approaching expiration and Expired dates when the cells changes to the blue or pink color? 

    In other words, when Subcontractor A has 4 blues cells and 2 pink cells, instead of me going through the whole Subcontractor A spreadsheet to pull the data, can it be linked to my overall tracking sheet and have the data appear on mine when it the cell changes colors?

    Any direction is helpful, thanks!

    Monday, February 12, 2018 3:17 PM
  • Hi ErikaDoim,

    I suppose it would be possible. But I'm not sure if I understand how your Sheets are.
    So, would you please share your file via cloud storage (e.g. OneDrive, Dropbox, and so on)?
    If it is shared, I will try to accomplish your needs.

    # Remember to edit/modify your vital/corporate/important data, before sharing.

    Regards,

    Ashidacchi

    Tuesday, February 13, 2018 1:22 AM
  • Hi ErikaDoim,

    I can see that you had created a duplicate issue.

    I suggest you to take follow ups and provide the information asked by the community members.

    The community members will try to provide you the suggestions to solve the issue.

    To avoid the duplication , I merge both threads.

    You can continue your discussion to get the solution for your issue.

    Thanks for your understanding.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 13, 2018 1:47 AM
    Moderator