I am using the Data Uploader to update a list of Store Addresses, which is used as a link in another
The Data Uploader is working fine for the update of the Store Address Sheet; however, I have the Store Address Sheet linked to another sheet, which is not updating when the Store Address Sheet is updated. When I go to Manage References the Sheet Reference Manager is showing the Status as Broken. In addition, I noticed on the Reference Another Sheet, if I click on one of the Status Lines that each time that I update the Store Address Sheet, it is generating a new Sheet Address Range, and if I look at the Cell Reference, the {Store# Address Range #}, the #, does not change. Since I am currently on Sore# Address Range 5, if I change the 4 to a 5 it works. Is there a way to get this to automatically update? I have multiple cells with link references, so this would be impractical to update the link each time I update the Store Address Sheet.
=VLOOKUP([Store Name]@row, {Store# Address Range 4}, 2, false)
Answers
-
I've tested what I believe you're describing and I'm unable to replicate what you're seeing: the cross-sheet reference range shouldn't change.
It sounds like you have two columns in your Store Address Sheet, the Store and the Address. The VLOOKUP range should be looking into those two columns (range 4, or 5, or whatever the range label is for just those two columns). Then Data Uploader updates the cells in those two columns which would update the formula. Data Uploader shouldn't be creating entirely new columns, which is the only reason I could think of that would cause you to need to create a new reference.
Sine the cross-sheet reference behaviour you're describing is unexpected, I would suggest reaching out to Smartsheet Support with a screen recording of the issue, showing the broken status in the Sheet Reference Manager.
Out of curiosity, does an INDEX(MATCH keep the references, instead? An INDEX(MATCH will work the same way, but instead of looking across a table of columns and rows, you will reference each of the two individual columns.
An INDEX(MATCH works like this:
=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value you’re looking to match}), 0)
So:
=INDEX({Column with Store Address}, MATCH([Store Name]@row, {Column with Store Name}), 0)
Let me know if this works!
Cheers,
Genevieve
-
I appreciate your response, I will try the Index and see if that returns the results that I am expecting, and I will reach out to support as well. Thank You
