Links that auto update

Bleakley McDowell

Hello,

I have a sheet that tracks inventory of items. All of the items are in boxes, with each box having a unique number. Multiple items are in each box. Each box also has a location.

In one sheet I track which box items are in (the ITEM sheet), along with a bunch of other information about the items, with each item having its own row. In another sheet I track the location of the boxes (the BOX sheet), with each box having its own row.

Items sometimes move between boxes and boxes sometimes move location. I would like the ITEM sheet link to the BOX sheet for that specific box. If the item moves boxes I would update the new box number on the ITEM sheet and have the link auto update to the correct, corresponding row in the BOX sheet.

Is this possible?

Adam Murphy

This sounds very doable with an INDEX/MATCH or INDEX/COLLECT formula. On ITEM sheet "lookup" the box location from the BOX sheet. This would also require that you create a helper column in BOX sheet that has the link for the row (I think you will need to manually add this when you add a new box), and use that field as the value to return when box matches. Hope that makes sense!