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

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? ;-)

Find more posts tagged with

Formulas

Accepted answers

LisaFlorida

It worked!

This is the formula I ended up with:

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

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.