Referencing a cell from a row based on a formula? MAX/COLLECT or something else?

LisaFlorida

I'm using this formula in my TASK sheet to find the most recently submitted invoice for each TA from my INVOICES sheet:

=MAX(COLLECT({Invoices Range 3}, {Invoices Range 1}, TA@row))

I want to add a formula to show the DATE PAID field from that same row of the most recent invoice. I cannot simply use the same formula to find the most recent DATE PAID, because sometimes invoices are not paid in the order that they are received.

How do I reference a row from a formula referencing a different sheet? Am I explaining this clearly? ;-)

LisaFlorida

It worked!

This is the formula I ended up with:

=INDEX(COLLECT({InvoicesDatePaid}, {InvoicesTA}, TA@row, {InvoicesDateProcessed}, [DateProcessed]@row), 1)

Melissa Yamada

Hello @LisaFlorida

Try:

=INDEX(COLLECT({Date Paid Column},{Invoices Range 3},Invoice@row),1)

Where:

{Date Paid Column} = Date paid column you want to return from your Invoices Sheet to your Task Sheet

{Invoices Range 3} = Invoice Column on your Invoices Sheet

Invoice@row = the column where your MAX/COLLECT is.

LisaFlorida

That formula returns a date, but it is not the correct date…

Melissa Yamada

@LisaFlorida is the Invoice on both sheets unique? if yes, then it should return a correct date but if it is not a unique value then I'd recommend adding a unique identifier.

Can you also share a screenshot?

LisaFlorida

It worked!

This is the formula I ended up with:

=INDEX(COLLECT({InvoicesDatePaid}, {InvoicesTA}, TA@row, {InvoicesDateProcessed}, [DateProcessed]@row), 1)