Using Index and collect

CDS ✭✭✭✭
edited 07/05/23 in Formulas and Functions

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!