syncing data between two sheets

Cindy Bowker

I have two sheets which we want to use for material tracking. The one sheet is based on what comes in and is traced back to a purchase order. The second sheet is what is actually used. I would like to be able to take the actually used total and match it to the same item on the other sheet so that we know if we have over used or if we have over ordered material. Not to sure how to do this, I would assume I use some kind of VLookup, but really don't know.

thanks for the help.

Cindy Bowker

Glastech Glazing Contractors Ltd

Genevieve P.

Hi @Cindy Bowker

Yes, the JOIN function brings together values into the same cell, so you can see each one individually. We used ", " at the end to identify how to separate the values.

See: JOIN Function

If you're looking to add these values together, you can use SUMIFS instead of JOIN(COLLECT, see:  SUMIFS Function

It's the exact same structure, just with one function and no ", " at the end:

=SUMIFS({Material Usage total usage}, {Material Usage Range 1}, =[PO#]@row, {Material Usage ITEM}, Item@row, {Material Usage length}, Length@row, {Material Usage finish}, Finish@row)

Cheers,

Genevieve

Genevieve P.

Wonderful! I'm glad we were able to get there in the end. 🙂

Andrée Starå

Hi @Cindy Bowker

I hope you're well and safe!

Yes, you're correct.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

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

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

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

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Cindy Bowker

Hi Andree,

I am not sure which sheet I put the formulas on, if I share the sheets with you could you help me out.

Thanks

Cindy

Cindy Bowker

Hi Andree,

I have written the following formula and it works,    

=INDEX({Material Usage total usage}, MATCH(Item@row, {Material Usage ITEM}, 0))

I would like to be able to add a couple more matches for the following columns - PO#, length, Finish. When I try to add these in I get error messages. Usually says invalid #. I am not sure what I am doing wrong with the multiple Matches. I am wondering if there could be format issues as well.

Ideally I would like the formulas to search in the following order: PO#,Part#,Length,Finish to come up with the stock used for each line.

Can you help, I have shared both sheets with you if that helps.

Thanks,

Cindy Bowker

Glastech Glazing Contractors Ltd.

Genevieve P.

Hi @Cindy Bowker

Can you check the column type for where you're entering the formula and what data you're looking to bring back?

For example, if your "Finish" column that you're looking into is a Date type of column, the formula would need to be entered in a Date type of column as well.

You would also need to create an entirely new {reference} to that new column:

=INDEX({PO #}, MATCH(Item@row, {Material Usage ITEM}, 0))

=INDEX({Part#}, MATCH(Item@row, {Material Usage ITEM}, 0))

=INDEX({Length}, MATCH(Item@row, {Material Usage ITEM}, 0))

etc.

Cheers,

Genevieve

Cindy Bowker

I am assuming by data you mean text, all the columns are text columns, I was hoping to be able to return the number used based on it matching the PO#, Part # , Length and finish all in one formula. Is this possible. I thought I should be able to write something like the following:

=INDEX({Material Usage Total Usage},MATCH(Item@row,{Material Usage ITEM},MATCH(.......

Thanks

Cindy

Genevieve P.

Ah, thank you for the clarification!

Yes, you can do this, but with INDEX(COLLECT instead of MATCH. The Collect function works as a sort of Filter, where you can narrow down each match.

Here's another post with an example structure, and another one that goes over JOIN(COLLECT as an alternative.

Cheers,

Genevieve

Cindy Bowker

Thanks so much I will look into that, is there a certain order that I need to place the items that I am trying to filter through?

Cindy

Cindy Bowker

I guess my next question would be is the first range to pull from the entire sheet that contains all the criteria data.

thanks

Cindy

Genevieve P.

Hi @Cindy Bowker

https://community.smartsheet.com/discussion/comment/325105#Comment_325105

There's no order in the COLLECT Function. After you list the column the bring back, you just need to list the Column with the matching value and then the Value in your current sheet.

Ex:

=INDEX(COLLECT({Column To Return}, {Material Usage ITEM}, Item@row, {PO #}, [PO #]@row, {Part#}, [Part#]@row), 1)

https://community.smartsheet.com/discussion/comment/325106#Comment_325106

The first range listed, or the range to pull from {Column To Return}, is the column you're looking to bring back data from: one single column.

If you have multiple columns to bring back, this is the range you would swap out for each data point to return.

If none of this has helped, it would be useful to see full screen captures of both sheets, identifying what you're looking to bring from one sheet to another, and what the matching value columns are (all of them), but please block out sensitive data.

Thanks!

Genevieve

Cindy Bowker

The first screenshot is the data sheet that I want to pull from. I would like to have the total usage column be brought into the Material Stock sheet in the Used column.

This is what I wrote, but it doesn't bring back a number:

=JOIN(COLLECT({Material Usage total usage}, {Material Usage Range 1}, [PO#]@row, {Material Usage ITEM}, Item@row, {Material Usage length}, Length@row, {Material Usage finish}, 1))

I think this is the correct way to go but not sure I have the syntax correct.

Cindy

Genevieve P.

Hi @Cindy Bowker

This formula looks correct! However there are two small details to tweak.

You have:

{Material Usage finish}, 1

However I don't see any cells in your "Finish" column that have a 1 in them. Did you perhaps mean:

{Material Usage finish}, Finish@row

And then with the JOIN function we'll want to add a note for how you want multiple matches to be returned... how about with a comma?

Try this:

=JOIN(COLLECT({Material Usage total usage}, {Material Usage Range 1}, [PO#]@row, {Material Usage ITEM}, Item@row, {Material Usage length}, Length@row, {Material Usage finish}, Finish@row), ", ")

Keep in mind this joins values together, so the output would be something like:

3, 5, 8

Were you instead looking to SUM together values? (See: SUMIFS Function)

Cheers,

Genevieve

Cindy Bowker

What it want to return is the total used material from the Usage sheet to the Stock sheet used column. The only problem is that we may have part numbers that are the same but with different finishes or lengths and they also may be on different PO#'s. I guess I am just not sure of the best way to pull all that data out of the usage sheet.

=JOIN(COLLECT({Material Usage total usage}, {Material Usage Range 1}, [PO#]@row, {Material Usage ITEM}, Item@row, {Material Usage length}, Length@row, {Material Usage finish},Finish@row),","))

this comes back with unparsable. Maybe I am trying to be to specific in what I want or maybe I am having to sort through to many criterias.

Cindy

Genevieve P.

Hi Cindy,

You're very close! I believe this formula will bring back exactly what you need, we just have to get the exact proper syntax for it to work.

You have two closing parentheses at the end:

,Finish@row),","))

The ", " should be between two:

Finish@row), ", ")

Ex:

JOIN(COLLECT(formula), ", ")

Does that make sense?

Cindy Bowker

So I changed that and it did get rid of the unparsable error, but now I am getting a blank cell back

Genevieve P.

Hi Cindy,

This indicates that it's not finding a match between your sheets.

Try using a COUNTIFS to see if it's finding any rows:

=COUNTIFS({Material Usage Range 1}, [PO#]@row, {Material Usage ITEM}, Item@row, {Material Usage length}, Length@row, {Material Usage finish}, Finish@row)

If this brings back 0, we know it's not finding any rows that have matching values. This means we need to search each range to see which one is not finding a match:

=COUNTIFS({Material Usage Range 1}, [PO#]@row)

=COUNTIFS({Material Usage ITEM}, Item@row)

=COUNTIFS({Material Usage length}, Length@row)

=COUNTIFS({Material Usage finish}, Finish@row)