Using Index and collect

I am looking to do the following:

I have this sheet with project milestones:

and then this sheet of Billings

In the Jan Billing column I want it to pull in the Final date from the milestones sheet but only if, the line number between the two sheets match, Bill month =1, bill year =2024 and Bill? =Yes

I have done multiple iterations of formulas and can't figure it out, this is what I have

=INDEX(COLLECT({2024 Final Dates}, {2024 month}, @cell = 1, {2024 year}, @cell = 2024, {2024 Forecasting line number}, @cell = [Line Number]@row, {2024 Bill}, @cell = "Yes"), 0)

I keep getting #date expected but final dates colum and Jan Billing date are both setup as date format

Find more posts tagged with

Accepted answers

All comments

sharkasits

@CDS is there only ever 1 date for January, or could there be multiple? If it's only ever 1 date, you should be able to just change your "0" at the end of the INDEX() to a 1.

CDS

There is only ever 1 date per line number per month but changing the 0 to a 1 gets me #invalid value

Paul Newcome

That particular error means that the formula is TRYING to output something, but that something is not a date value. Since you have set both columns as date type columns, how exactly is the Final Date in the source sheet being populated?

Since there is only ever one date per line number, try a MAX/COLLECT combo instead. We may be able to make that a little more simplified.

=MAX(COLLECT({2024 Final Dates}, {2024 Forecasting line number}, @cell = [Line Number]@row))

CDS

@Paul Newcome sorry for the confusion each line number will have at least 6 dates but never in the same month

Paul Newcome

Ok. So to make sure I am clear... You are wanting to use the following logic to populate the [Jan Billing Date] column in the second screenshot from the first screenshot using the following logic?

Billing Month = 1

Billing Year = 2024

Bill? = Yes

Line Number = Line Number

If the above is correct, then we should be able to use your original INDEX/COLLECT with only a slight adjustment (as indicated by @sharkasits).

=INDEX(COLLECT({2024 Final Dates}, {2024 month}, @cell = 1, {2024 year}, @cell = 2024, {2024 Forecasting line number}, @cell = [Line Number]@row, {2024 Bill}, @cell = "Yes"), 1)

But you said this gives you an error?

sharkasits

@CDS can you screenshot the formula you put in? Sometimes when you get that error it's a syntax error (like a missing quotation mark or comma in the wrong spot).

Also, just to confirm, the Jan Billing Date column is a Date Type, right?

CDS

Forgive me @Paul Newcome and @sharkasits when I change the 0 to 1 it works I had the formula in the wrong row! Thank you thank you

CDS

the only question I have for the line numbers that don't have Jan dates is it possible to hide the #invalid value and just have the cell be blank?

Paul Newcome

Yes. You would use an IFERROR statement like so:

=IFERROR(original_formula, "")

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.