syncing data between two sheets

Cindy Bowker

Thanks I will try that and let you know what I find out.

Cindy Bowker

so I have tried the whole formula and then each individual formula, I get numbers back for item # , length and finish, nothing for PO#. I am not sure what these numbers represent as they don't match what is on the other sheet. Not sure why the PO# doesn't come through.

would it help if I share the sheets with you?

Genevieve P.

Hi @Cindy Bowker

This is great, thank you! This shows me that the formula cannot find a match for the Value in your PO column across both sheets.

I've just realized that you use a number starting with a 0 in your Primary column. I have seen an issue with matching this number type across sheets before, which can be solved if we use an = sign in the formula before the criteria.

You can test this with the COUNTIFS:

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

If that now returns a number, then this should be the final, working formula:

=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), ", ")

Cheers!

Genevieve

Cindy Bowker

So it appears that I get the correct # back now but it also adds and extra value. how do I get rid of the second value.

Cindy

Cindy Bowker

Hi Genevieve,

I figured out the 2 numbers in the cell, there are actually 2 values for that same part on the same PO#. So my next question would be how can I add those 2 numbers together so that they show only one sum. I would then like to take this sum from the actual amount ordered to see if we are over or under our order amount.

Thanks

Cindy

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

Cindy Bowker

Thanks Genevieve

That solved the problem. I appreciate all the help.

Cindy

Genevieve P.

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