FILTER function

Kirstine

I'm sorry - this must be so annoying for you!

Just so I can keep up, can we call the two sheets that the info is copied from 'Invoicing sheet' and 'Site Pack sheet'. (IS and SPS to make it easier perhaps?!) The 'Source Sheet' is where these two sheets copy the info to, correct? So then do I need a fourth sheet called 'Alert Sheet'?

Sorry again that I'm being so difficult. 😔

Paul Newcome

No worries at all.

You are correct that my "Source Sheet" references are looking a the sheet that is receiving the Copy Row automations from the "IS" and "SPS" sheets.

You are also correct that you will need a 4th sheet called "Alert Sheet".

Kirstine

OK! I've done it all, the only thing is that I don't think the Invoices column on the Alert sheet is gathering the invoice numbers.

Paul Newcome

That's odd. Try removing the IF portion for now to see what comes in.

Kirstine

Still blank. Is it something to do with the second COLLECT, as I think it needs a criterion? I've not used that before though, so sorry if it's a silly question. 🙈

Paul Newcome

Which COLLECT exactly are you referring to? I had it working in my sheets, and I copy/pasted the formulas directly from there.

Are all invoice numbers numerical or do some have letters (or do all have letters)?

Kirstine

There's only one COLLECT in that column:

=IF(RefNum@row <> "", JOIN(COLLECT({Source Sheet Invoice Number Column}, {Source Sheet Invoice Number Column}, @cell <> "", {Source Sheet Our ref Column}, RefNum@row), ", "))

This is as I've entered it, but I just copied yours!

=IF(RefNum@row <> "", JOIN(COLLECT({Source sheet Invoice#}, {Source sheet Invoice#}, @cell <> "", {Source sheet Our ref}, RefNum@row), ", "))

Paul Newcome

Are all invoice numbers numerical or do some have letters (or do all have letters)?

Kirstine

There would be all sorts as contractor has their own way of numbering invoices. 😏

Paul Newcome

That may be the issue. All data must be of the same type (numeric OR text).

Create a helper column on the "Source Sheet" and use a column formula of...

=[Invoice Number Column Name]@row + ""

This will convert everything to text values.

Then in the alert sheet, reference this new column in your {Source sheet Invoice#} range.

Kirstine

OK, thank you. And do I then reference that in the "Alerts Sheet" where I was referencing the invoice number column before?

Paul Newcome

Yes. That's correct.

"Then in the alert sheet, reference this new column in your {Source sheet Invoice#} range."

Kirstine

Sorry - just realised that the Invoice number column doesn't seem to be copying across! So that'll be the glitch. I'm going to have a look at that now and try to figure it out. x

Paul Newcome

Try adding in another column, replicating the invoice number data, then referencing this new column in the =[Invoice Number Column Name]@row + "" formula.

Hopefully recreating the column will solve the problem. You may have to loop in support if it seems like a bug within the sheet.

Kirstine

IT WORKS!!!! 😆😆😆 Thank you! 😆😆😆