Cross Sheet Formula

Nathan Slatton

Trying to do an if and statement. If the PO# matches on sheet one with PO# on sheet 2 and the Count of POs (a count helper column restars at 1 with each new PO) equals 1 then post the Inv amount.

=IF(AND({FY25 Payables Tracker PO#} = [PO #]@row, {FY25 Payables Tracker Count POs} = 1),{FY25 Payables Tracker Invoice Totals By PO},""))

Does this make sence?

Paul Newcome

I would suggest using the MAX/COLLECT method as opposed to the JOIN function. The JOIN function will output a text string which means you would need to include some extra steps to be able to use the result as a number in further calcs.

prime_nathaniel

Nathan Slatton I do not understand this portion

the Count of POs (a count helper column restars at 1 with each new PO) equals 1

I assume that means there can be multiple invoices for one PO on your source sheet and you only want the Invoice value of the FIRST PO.

assuming
target.PO# = {FY25 Payables Tracker PO#}

target.PO_Count = {FY25 Payables Tracker Count POs}

target.PO_Invoice = {FY25 Payables Tracker Invoice Totals By PO}

and your current row you want to populate with invoice value is [PO #]@row

=JOIN(collect({FY25 Payables Tracker Invoice Totals By PO}, {FY25 Payables Tracker PO#}, [PO #]@row, {FY25 Payables Tracker Count POs}, 1), "")

you can also use max, min, average, etc it shouldn't matter if there is only 1 entry for count = 1

Nathan Slatton

I have miltiple invoice amounts added togeather to create a Total invoice amount for each PO but it is repetitive and only need one of them per PO. (eg 5 invoices for 1 PO but each row has PO #, PO Amount, Inv# and Inv amount so 5 rows for 1 PO. but only 1 row per invoice), So i did a count that resets for each PO that way i can have only 1 of the invoice totals show up. I am trying to pull that 1 invoice total over to a new sheet and match up the PO to that invoice total so I can get what is remaining on the PO.

Example of the invoice set up.

I can get invoice totals and have a list of all POs and PO amounts but am trying to pull the invoice totals for each PO into another sheet.

prime_nathaniel

@Nathan Slatton

What I described earlier works then, honestly if the PO Amount never changes and is just a dupe, you dont even need to know how many there are.

=JOIN(collect({FY25 Payables Tracker Invoice Totals By PO}, {FY25 Payables Tracker PO#}, [PO #]@row, {FY25 Payables Tracker Count POs}, 1), "")

or

=MAX(collect({FY25 Payables Tracker Invoice Totals By PO}, {FY25 Payables Tracker PO#}, [PO #]@row))

Collect is just getting you the subset of invoices that match the PO#. Once you have that collection you can run pretty much whatever you want on it to get the total for the PO

example the collection of 199212 is

91707.30
91707.30
91707.30
91707.30

So if you run a max on that = 91707.30, if you run avg = 91707.30, min = 91707.30, or you could sum them all and divide by count and it equals = 91707.30

Nathan Slatton

Thank you

Paul Newcome

I would suggest using the MAX/COLLECT method as opposed to the JOIN function. The JOIN function will output a text string which means you would need to include some extra steps to be able to use the result as a number in further calcs.