Results 1 to 9 of 9

Thread: Conditional Linking Between a Series of Work Sheets

  1. #1

    Conditional Linking Between a Series of Work Sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to create a project tracking workbook made of multiple worksheets, with each one representing a milestone made of about 100 rows showing each task. Once a task is marked as complete, certain cells in that row should link to a row in the next spreadsheet, etc.

    More specifically--
    On Worksheet 1, I want to link certain cells in each row to cells on Worksheet 2—based on a condition, such as an x appearing in the last column. If there is no x, there is no linking.

    For example, on Worksheet 1, cells B3, C3, D3 link to the cells on Worksheet 2 –if there is an x in H3 on Worksheet 1.
    Then, once that row on Worksheet 2 is marked complete, A3, B3, and E3 link to Worksheet 3.
    The linking does not need to match the same rows on Worksheet, and in fact, it would be better if rows accumulated from the top as tasks are completed on the previous Worksheet.
    I've tried this IF statement, and it works from Worksheet 1 to 2, but not beyond.
    =IF('1. Inventory Existing Site'!$H:$H="[x]",'1. Inventory Existing Site'!A:A,0)
    Any ideas? Formulas and Macros welcomed.
    Thanks!

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Not sure about others viewing, but your explanation has me confused, particularly the way your referring to worksheets by number and name, and also to whole columns. Can you post an example spreadsheet
    illustrating what you are trying to do?

  3. #3
    Quote Originally Posted by Hercules1946 View Post
    Not sure about others viewing, but your explanation has me confused, particularly the way your referring to worksheets by number and name, and also to whole columns. Can you post an example spreadsheet
    illustrating what you are trying to do?
    Here is a workbook that shows how I'm trying to link certain cells from one worksheet to the next one.
    Thanks for your help.
    Mitch


    Content Tracking Forms--Excel Cell Linking Test 4.xlsx

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Hello
    The attachment covers the data needed in the second sheet (2.Audit). Ive added a helper column (A) that calculates the rows that have "[x]" in Col H, using an array formula, when entering or editing these you need to confirm with Ctrl+Shift+Enter.
    My other formulae in cols B, C, D and L are non-array. Ive formulated enough cells to cater for entries from 1. Inventory in Rows 3-30.
    Ill leave you to replicate in the 3rd Sheet.

    HTH
    Hercules
    Attached Files Attached Files

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Sorry - I think the attachment earlier was missing Col L.
    Attached Files Attached Files

  6. #6

    Thanks

    Quote Originally Posted by Hercules1946 View Post
    Sorry - I think the attachment earlier was missing Col L.
    Thanks for the solution. It works just the way I need it to work.

    I am having a bit of a challenge going to the next stage to get link cells from Sheet 2 to Sheet 3. I adjusted the statements this way for the first cells:

    Worksheet 3--
    Col A, Index:
    =IFERROR(SMALL(IF('2. Audit Existing Content'!$O$3:$O$30="[x]",ROW('2. Audit Existing Content'!$O$3:$O$30)-ROW('2. Audit Existing Content'!$O$3)+1),ROWS($A$15:A16)),"")


    Col B, Name:
    =IF(N($A4),INDEX('2. Audit Existing Content'!$B$3:$D$30,$A4,MATCH('2. Audit Existing Content'!B$2,$B$2:$D$2,0)),"")

    I seem to have made a error, but can't decifer what it is.

    Thanks again for your help.

    Mitch

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Hello
    If its OK with you, its easiest for me if I just put in the extra coding for sheet 3, and leave you to do the comparison. I noticed the following errors:
    Your array formula (Index) is correct if placed in A5 (looking for a second instance of "[x]"). For A4, the last cell refs should be $A$15:A15. Also you must confirm with Ctrl+Shift+Enter to get the braces {} around the formula
    In your Col B Formula, the column labels are in Row 3 of Worksheet 3 and not Row 2, so the last but two parameter should be $B$3:$D$3.
    You haven't provided the formula to go in E4 of Worksheet 3.
    Attached Files Attached Files

  8. #8
    Quote Originally Posted by Hercules1946 View Post
    Hello
    If its OK with you, its easiest for me if I just put in the extra coding for sheet 3, and leave you to do the comparison. I noticed the following errors:
    Your array formula (Index) is correct if placed in A5 (looking for a second instance of "[x]"). For A4, the last cell refs should be $A$15:A15. Also you must confirm with Ctrl+Shift+Enter to get the braces {} around the formula
    In your Col B Formula, the column labels are in Row 3 of Worksheet 3 and not Row 2, so the last but two parameter should be $B$3:$D$3.
    You haven't provided the formula to go in E4 of Worksheet 3.

    Thanks for your help! I get the principle now.
    Mitch

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    792
    Articles
    0
    Excel Version
    2010
    Your very welcome, and thanks for letting me know.

Tags for this Thread

Posting Permissions

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