+ Reply to Thread
Results 1 to 8 of 8

Tracking multiple dependencies

  1. 06-15-2017, 02:47 PM #1
    BWellman
    BWellman is offline
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Tracking multiple dependencies

    Hey all,

    I am looking for a formula that is able to track the iteration of products from their source. It is difficult to explain, so I attached an example. In the attached document, I am looking for a formula in the range F4:F13 that will be able to look at the ID numbers in column E, and be able to trace related iterations through to their source, or first iteration. Column E shows the link between a new product and its last iteration and will be input by users, but cannot track any further beyond that. From there, I need to be able to track how many iterations a product has gone through (column G).

    I included an example of what it should look like if the formula is possible. Any help would be appreciated!

    Cheers,
    B
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-15-2017, 05:47 PM #2
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Tracking multiple dependencies

    Try this in E17 and fill down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    This in F17 and filled down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    In G17
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  3. 06-19-2017, 05:43 AM #3
    BWellman
    BWellman is offline
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Tracking multiple dependencies

    Hey Dave,

    Thanks that's really helpful. Just one small issue. A product iteration may not always have the same name as its predecessor (i.e., car may turn into truck instead of car2). In those cases the last formula breaks. Is it possible to trace back to the source based on the ID numbers as opposed to the product name?

    Thanks,
    B
    Reply With QuoteRegister To Reply
  4. 06-19-2017, 08:20 AM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Tracking multiple dependencies

    If I understand correctly try this in E17 and fill down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    The numbers in F:G "Desired result:" still hold even when I changed Car 2 to Truck.
    Reply With QuoteRegister To Reply
  5. 06-19-2017, 08:40 AM #5
    BWellman
    BWellman is offline
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Tracking multiple dependencies

    Sorry, not the best explanation on my part.

    If you replace the products in D4:D13 and D:17:26 with Test1 through to Test10, the results in columns F:G break. That's what I don't understand if it is just tracking the results based on the ID #s
    Reply With QuoteRegister To Reply
  6. 06-19-2017, 10:04 AM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Tracking multiple dependencies

    I've changed column D as you described. Now there is nothing to link iterations to previous products. The results of referencing ID are now nothing like the expected results. Not sure this is doable without additional information. Will need to think on this. Have errands to run. Will be back.

    In the meantime this is what I am getting:

    Row\Col
    C
    D
    E
    F
    G
    2
    		 Problem example:
    3
    ID
    Products
    Last iteration ID
    Source ID
    Iteration number
    4
    1
    		 Test 1
    5
    2
    		 Test 2
    6
    3
    		 Test 3
    7
    4
    		 Test 4
    8
    5
    		 Test 5
    3
    9
    6
    		 Test 6
    10
    7
    		 Test 7
    5
    11
    8
    		 Test 8
    1
    12
    9
    		 Test 9
    2
    13
    10
    		 Test 10
    7
    14
    15
    		 Desired result:
    16
    ID
    Products
    Last iteration ID
    Source ID
    Iteration number
    17
    1
    		 Test 1
    18
    2
    		 Test 2
    19
    3
    		 Test 3
    20
    4
    		 Test 4
    21
    5
    		 Test 5
    3
    5
    1
    22
    6
    		 Test 6
    23
    7
    		 Test 7
    5
    7
    1
    24
    8
    		 Test 8
    1
    8
    1
    25
    9
    		 Test 9
    2
    9
    1
    26
    10
    		 Test 10
    7
    10
    1
    Reply With QuoteRegister To Reply
  7. 06-20-2017, 04:40 AM #7
    BWellman
    BWellman is offline
    Registered User
    Join Date
    12-23-2013
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Tracking multiple dependencies

    Yeah, I was getting the same thing as well

    It doesn't seem to be too hard of a question (If ID one turns into ID 3, and ID 3 turns into ID 5, what is the source of ID 5), but I have no idea how to solve that in Excel. Do you think it would be possible through macros?

    Thanks for all of your help on this by the way. It's an annoying one.

    B
    Reply With QuoteRegister To Reply
  8. 06-20-2017, 08:09 PM #8
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,742

    Re: Tracking multiple dependencies

    I am sorry B. I have no clue.

    It doesn't seem to be too hard of a question (If ID one turns into ID 3, and ID 3 turns into ID 5, what is the source of ID 5)
    Maybe it isn't too hard of a question if we know the logic which determines ID one becomes ID 3 should become ID 5. Why not ID 7 or 9 or 10? And what does ID 5 turn into ... 4, 8 etc?

    I had considered that each increment by 2, but that pattern doesn't hold throughout.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need help with Multiple Dependencies
    By balu.soln4biz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2014, 01:57 PM
  2. Data Validation with multiple dependencies
    By BWellman in forum Excel General
    Replies: 1
    Last Post: 03-23-2014, 10:34 PM
  3. scheduling and hour tracking for multiple employees at multiple sites
    By nsswcd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-20-2013, 11:32 AM
  4. [HARD]Lists and SubLists - Multiple Dependencies
    By anduingaiden in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 03:06 PM
  5. [SOLVED] IF formula with multiple dependencies
    By jomili in forum Excel General
    Replies: 2
    Last Post: 12-07-2011, 02:42 PM
  6. Data Validation - Multiple Dependencies
    By Nimish in forum Excel General
    Replies: 0
    Last Post: 08-11-2006, 10:50 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1