I'm using a Join Distinct Collect formula to return a list of product numbers from a detailed schedule (formula below). I use this and other formulas to populate a weekly summary sheet.
=JOIN(DISTINCT(COLLECT({Schedule Item}, {Schedule Make Date}, =Date@row, {Schedule Room Name}, =RmName@row)), ", ")
Whenever the schedule has a cleaning placeholder on it, the formula returns #INVALID DATA TYPE. Otherwise it returns a list of the product numbers. It omits blank rows in the schedule that are visual separators. Works perfectly except for when it encounters rows that include cleaning. Screenshots of the summary sheet and the main detailed schedule below. There's a date column to the left on the summary sheet that is omitted from the screenshot.
Anyone have any ideas?
