syncing data between two sheets

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

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

All comments

Andrée Starå

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

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/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!

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

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.

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

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

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

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

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

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

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

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

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

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?

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