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
Answers
-
@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.
-
There is only ever 1 date per line number per month but changing the 0 to a 1 gets me #invalid value
-
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))
-
@Paul Newcome sorry for the confusion each line number will have at least 6 dates but never in the same month
-
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?
-
@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?
-
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
-
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?
-
Yes. You would use an IFERROR statement like so:
=IFERROR(original_formula, "")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!