FILTER function

Kirstine

Wow, thanks for this! I'm going to slowly work my way through it today!! 😆

Paul Newcome

Let me know if you need any clarification or help.

Kirstine

I can't add a second auto number to my source sheets, can I use an existing one?

Paul Newcome

Yes. The purpose of the auto-number is so that there is unique data on each row to generate the row number. Since the auto-number column can't contain duplicates, it can have any formatting you want.

Kirstine

Fab, thanks! I'll get back to it. Hold the line please caller...! 😂

Kirstine

Sorry - another question...! 🙈

re: RefNum, I have [Our ref] coming in from both source sheets. How do I get it to apply to both?

Paul Newcome

I didn't realize you were working with two separate source sheets. So your metrics sheet is a 3rd sheet?

Kirstine

Yeah, I've set a couple of sheets up to copy specific rows into the 'metrics sheet'. One is where the invoices are logged, and one is where the 'Site Packs' are logged.

Paul Newcome

Ah. I was under the impression that the site packs and the invoices were all on the same sheet.

Paul Newcome

When you say "copy rows to the metrics sheet", what exactly do you mean by that?

Kirstine

Well I only used the term metrics sheet as that's what you'd called it most recently! 🙈 I'm assuming that's what you were initially calling the "Alert Sheet"? Sorry if I've confused matters.

I have Automated Workflows on my two source sheets which use the 'Copy Row' function to pull across:

a) Invoices for Site Managers' invoices once authorised by their manager

b) Completed 'Site Packs' which have been signed off by H&S

On the source sheet for invoices I also have a formula that checks for duplicates, which uses the same 'Auto' and 'Row' columns along with a MATCH formula.

Paul Newcome

Ah. Ok. Sorry about that. Yes. The "Alert Sheet".

So you have an invoice sheet and a site pack sheet. Both copy rows to a "master sheet"?

Kirstine

Both copy rows to the "Alert Sheet".

Kirstine

Thanks for all your help today! I'm heading home now. x

Paul Newcome

Ah. Ok. That's where the confusion lies then... In the solution I provided, "Source Sheet" is the sheet that is receiving the Copy Row Automation (first screenshot). "Alert Sheet" is the second screenshot that has the final compiling of the invoice numbers next to the site packs.