Conditional Linking Between a Series of Work Sheets

Mitch

New member
Joined
Sep 26, 2014
Messages
4
Reaction score
0
Points
0
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!
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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?
 

Mitch

New member
Joined
Sep 26, 2014
Messages
4
Reaction score
0
Points
0

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 

Attachments

  • Content Tracking Forms--Excel Cell Linking Test 4.xlsx
    17.6 KB · Views: 138

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Sorry - I think the attachment earlier was missing Col L.
 

Attachments

  • Content Tracking Forms--Excel Cell Linking Test 4.xlsx
    17.9 KB · Views: 140

Mitch

New member
Joined
Sep 26, 2014
Messages
4
Reaction score
0
Points
0
Thanks

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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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.
 

Attachments

  • Content Tracking Forms--Excel Cell Linking Test 4.xlsx
    21.3 KB · Views: 95

Mitch

New member
Joined
Sep 26, 2014
Messages
4
Reaction score
0
Points
0
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
 
Top