Excel Formula using IF across tabs

Question

Hello all,

I do the invoicing at work and its so old school and one particular part of it is charging for late vehicles. I have to go through the Log to look for which were late and hand write out the Booking Ref, Customer Ref and customer name and how late they were.

I wanted to create a spreadsheet where I can copy and paste the 'raw' data from the log onto the first tab, and have it fill out information in a table I made in the next tab - but only if 'late' is written in the W column. (I have taken out registrations ect)

Highlighted is the information I want only if 'W' is late.

and I want it in this in row on the next tab. (FYI we can get 10+ a week this is what I have to handwrite out)

Can anyone help me ? If I knew a formula for one I could do it but I'm not sure how to do it for just IF column W is 'late'.

Thanks!!

Meg

Answer 1

Thanks for your reply - I have Office 2021.

Thank you I will try that

Answer 2

Do you have Microsoft 365 or Office 2021? If so:

I don't see the minutes late and charge in your log data. If you don't already have columns for them, add them, for example in columns X and Y.

Enter the following formula in B11:

=FILTER(INDEX('Log'!A2:Y1000,SEQUENCE(ROWS('Log'!A2:Y1000)),{3,2,4,24,25}),'Log'!W2:W1000="Late","None")

Change Log to the real name of the log sheet, and adjust the ranges if you have more than 1000 rows of data (it doesn't matter if you have fewer).