Nested formula

Pamela.Baron

I have a schedule sheet and a production sheet, I am trying to pull the pick up date from the schedule sheet to the production sheet based on an Order Number, which on the Schedule sheet can be surrounded by other information.

Schedule sheet:

Pick Up Date field is a date field.

Production sheet:

I have tried:

=IF(CONTAINS([number-order]@row, {WDC SHIPPINGSCHEDULE Order number/contents}),

"pudate", "false") but I don't know how to replace "pudate" with the actual date on the Schedule sheet.

KPH

You are thinking along the right lines with the CONTAINS @Pamela.Baron and have successfully matched on a partial string.

But the IF or nested IF is not going to get you what you need. You need an INDEX function to scan the list in your schedule sheet and return the row that meets the criteria. IF will not scan through the list - it is more of an absolute choice - IF true do this, if not do something else (you would need a lot of something else's to achieved what you want). INDEX is more efficient.

We can combine INDEX and COLLECT and CONTAINS like this.

=INDEX(COLLECT({WDC SHIPPINGSCHEDULE Pick Up Date}, {WDC SHIPPINGSCHEDULE Order number/contents}, CONTAINS([Number-Order]@row, @cell)), 1)

This COLLECTS the value in the column I have called "WDC SHIPPINGSCHEDULE Pick Up Date" (Pick Up Date in your Schedule sheet), where the Number/Contents column in that sheet (the reference you have already set up) CONTAINS the number-order of the row in the current sheet. It returns the 1st row that matches.

Hope that helps.

Pamela.Baron

That returns #Invalid Column Value

KPH

Is the column that you want to pull the date into set up as Column Type Date?

Pamela.Baron

no

Pamela.Baron

OMG, I changed it and it worked-Thank you so much. You are my hero!

KPH

Can you change it? Right click, edit column properties, and then change it to Date

The format of the column of the schedule sheet needs to match that in the production sheet otherwise you will see the INVALID COLUMN VALUE error.

KPH

Our messages crossed! Enjoy your new formula 😀