Is it possible to link a column from one sheet to another?

RonH

I've got a sheet where I'm keeping track of the status of certain items for a particular customer, and another sheet where those items are stored as they're worked on across all customers. Would it be possible for me to take the status column from the internal board across all customers and have it update automagically in the customer specific sheet?

So like, say I have a column called "Item Status" and "Item Number" on both sheets. On the internal sheet if someone changes the status from say "Pending" to "In progress" for item number "1", is there a formula I could use on the customer specific sheet to change item 1's "Item Status" automatically from "Pending" to "In progress"?

As it is I'm having to manually swap back and forth to ensure the two sheets reflect accurate information and that's a lot of overhead.

RonH

I've tried this but it returns unparseable

=VLOOKUP(“<search item>”,{Table Name},<column number>,0)

Kelly Moore

Hey @RonH

Try changing your [match type] from 0 to false

=VLOOKUP(“<search item>”,{Table Name},<column number>, false)

Does that work for you?

https://help.smartsheet.com/function/vlookup

RonH

https://community.smartsheet.com/discussion/comment/327117#Comment_327117

Thanks for the suggestion!

Unfortunately that doesn't seem to have solved it. Still shows up as #unparseable.

Kelly Moore

Hey @RonH

Not seeing your exact formula, I will give you this one. Remember you will have to physically insert the cross sheet references into your sheet - you cannot simply copy paste. I changed the names of the ranges (which is a good practice) to clarify what column I was referencing. In the Index/Match function you use individual columns as the specific range, not the entire table range as in vLookup.

This formula would go into your customer Status column

=INDEX({internal sheet Status}, MATCH([Item Number]@row, {internal sheet Item Number},0))

Does that work for you?

Kelly

RonH

Awesome! That was a huge help. It's still not quite doing what I need to but at least it's telling me NOMATCH instead of unparseable now.

Kelly Moore

Ok, let's work on the NoMatch

Just to verify - the row on the customer sheet where you put in the formula - it does have an actual match in the other sheet? In other words, on that row, there should be a match.

Assuming yes there should be a match, is it possible that you can give me screenshots of your two sheets - I'm really only interested in seeing the data the columns listed above. Don't share sensitive info like customer names, etc. Also, if you can take a screenshot of your actual formula - I'm interested in seeing the colored text.

Once we get the formula working properly we can take care of the legit NoMatches when they show up.

As we work, if there's anything you don't understand, don't hesitate to ask.

Kelly

RonH

Hi Kelly!

Thanks for your help so far and sorry to revive this post from the dead but I'm still working on this and would love your help if you're available. This is the formula that results in the nomatch. The [tracker number] field does exist in both sheets. Please let me know if you have anything else I should try. Again I really appreciate your assistance thus far.

-Ron

Kelly Moore

Hey Ron

Let's see if we can get this working for you.

Thanks for the screenshot - it is always so helpful for anyone in the community that is helping with a question. To verify - your {Development Planner RM Status 7} is a single column that would match your answer in your [Tracker Number] column?

I'm wondering if smartsheet thinks the data in one column is text and the data in the matching column is a number (this is assuming your item number is a number and not alphanumeric textstrings). Often you can tell by looking at the columns in both sheets, assuming you haven't manually formatted the justification. If one is left justified and the other is right justified, then one set of data is behaving as a number and the other as text. Not knowing what the [Tracker Numbers] look like in either sheet, I'm guessing at common possible causes. Let's try this

=INDEX({Development Planner}, MATCH(VALUE([Tracker Number]@row), {Development Planner RB Status 7},0))

If that doesn't work, delete the reference to {Development Planner RB Status 7} completely from the formula and go back in and re-insert it from the formula dialog box. You wouldn't believe the number of times I've found an error in what I thought I inserted.

Kelly

RonH

Hi Kelly! Still no luck, HOWEVER, I have noticed that part of the issue might be that there's a schedule health column to the left of the tracker number column maybe? It's being referenced correctly in the attached screenshot in that it's outlined in blue. But it's an alphanumeric on the tracker list and a "primary column" on the development planner. Maybe that has something to do with it? Request ID is the column in the Development Planner sheet I'm trying to match against the Tracker Number.

Kelly Moore

Hey

No, that shouldn't have anything to do with it. Can you show me a few of your tracker numbers in both columns? I don't need to know what they are linked to - just to see the format of your data. It would be good to see lines where the same data exists in both columns.

Is the tracker number data being hand entered or by formula in that source sheet?

RonH

First is the project sheet and the second is the development planner. Also I noticed that the nomatch was being returned because there really was no match. So I moved to a different number. With this number I'm now getting the request ID column returned instead of the RB status column! New error! Progress! lol

Kelly Moore

That actually is good news. Please delete the reference {Development Planner} from your formula. Through the formula wizard, click Insert reference and select the correct column. Before click the Insert into Sheet, remember you can rename that Range as a good practice. Hopefully the re-insertion will clear up the wrong column data being inserted.

Once the formula is working as it should (and only after the formula is working as it should) we can make the NoMatches go away, if you wanted them to. Or, if desired, we can make it say something else. We always do this step last after making sure the formula is working properly since this step masks any errors the formula is producing. Two reasons would drive you to mask the NoMatch. One is to clean up the appearance of the data. The second is that these errors can sometimes cascade through to other calculations, causing calculations to not execute.

Let me know what happens after you do the first step of deletion, reinsertion.

Kelly

RonH

So, back to #nomatch after replacing the field using the insert into sheet thing. However, good news is now the correct cell on the development planner is showing "Used in formula on:" with the name of the other sheet. Does it matter that the field I'm trying to carry over is a single select dropdown and the field with the formula is alphanumeric?

Kelly Moore

No it doesn't matter in this case but it's always a good check on dropdowns - you could get possible errors between single select and multi-select columns. So it's good it's not that.

I have to back us up to the NoMatch. To verify, the row you are sitting where there is a #NoMatch does have a match on your Development Planner sheet and your {Development Planner RB 7} refers to the entire column and not a single cell.

Is the Tracker Number of Development Planner sheet entered manually, or by formula?

Kelly

RonH

On Development Planner it's entered by smartsheet automation via API I believe. On the other sheet it's entered manually.

The tracker number does match on the correct row between the two sheets. Development Planner RB 7 should apply to the whole column. My thought by having it work through the whole column was to have it automatically track that field for each row when I copy/paste the formula down the rest of the row in the original sheet. If that makes any sense. I really appreciate all your help so far by the way.