Search text based upon date

PJay128

Hi Im struggle to put together a (what should be simple) statement to search a column (section) based on the month it was checked.

From the attached table i want to pull the data out of the sheet into another and record that the check has been completed for each month.

So the output would be in march electrical repairs, dust suppression, environmental checks were all completed however site buildings and pedestrian & Traffic were not.

The output in Feb would be vice versa..

Any help would be greatly appreciated :P

 

Andrée Starå

Hi,

Does it have to be on another sheet or would it work with a report instead?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

PJay128

It could be used on a report yes however i need it for every month. My understanding of reports is that it will only so me data that is available. Therefore any checks which have not been completed will be missing.

I need to be able to spot the checks which have not been completed in the month. I don't need to know how many times its been done. Once a month will suffice.

It would be nice to make it visual using ticks etc but not a requirement. eg below

 

Paul Newcome

You could use something along the lines of...

 

=IF(FIND([Section Column Name]@row, JOIN(COLLECT({Section Column Reference}, {Date Column Reference}, IFERROR(MONTH(@cell), 0) = ##), ",")) > 0, "Yes", "No")

 

Basically you are joining all of the cells from the master sheet that have the month of ##. You are then searching for the particular text in the [Section Name Column] in that string. If it finds the text then you will get your green check. If it does not, then you will get your red x.

 

Just replace the ## with whatever month number you are looking for.

Andrée Starå

Reports can show everything that's on the sheet(s). The report could probably be set up to show what you need, and it could also be automated so that a report would send out each month if needed.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Best,

Andrée

PJay128

Thankyou Paul never used the collect before however using that statement means i will pull the information from previous years.

I've tried to add the year to the argument but get an error.

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, IFERROR(MONTH(@cell), 0) = 11), ",")) > 0, "Yes", "No")

I dont want to pickup previous years checks just 2019's

Cheers

 

Paul Newcome

Are you getting the #INCORRECT ARGUMENT error? It looks like your range/criteria syntax got a little mixed up.

 

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, @{Stourton Maintenance Range 2}, IFERROR(YEAR(@cell), 0) = 2019, {Stourton Maintenance Range 1}, @{Stourton Maintenance Range 2}, IFERROR(MONTH(@cell), 0) = 11), ",")) > 0, "Yes", "No")

 

If you look at the above (your current formula) you will see that you have range then criteria which is correct. But then you have range, range, criteria. You have an extra range in there. I believe you may also have an extra closing parenthesis in there too After your JOIN statement is closed out. Give the below a try...

 

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, AND(IFERROR(YEAR(@cell), 0) = 2019, IFERROR(MONTH(@cell), 0) = 11)), ",") > 0, "Yes", "No")

PJay128

Yes i was getting the Incorrect argument error and noticed i had the extra range but still getting the error.

I've tried as you suggested and still the same error

Paul Newcome

What is in Range 1 and Range 2 for your cross sheet references? Are both ranges the same size?

PJay128

Yes i have chosen the column as the range on both so they are the same number of rows.

I have the dates as a system generated Column so ive added another column mirroring it but not a system one.

That is also throwing up the same error.

:S

Paul Newcome

Try wrapping the formula you are using to mirror in a DATEONLY function.

 

=DATEONLY([System Generated Column Name])

PJay128

Same error although i might have done it wrong :S

The original formula worked fine but as soon as the year was added it went wrong.

Should the AND be between the month and year?

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, IFERROR(YEAR(@cell), 0) = 2019, AND(IFERROR(MONTH(@cell), 0)) = 11)), ",") > 0, "Yes", "No")

 

Paul Newcome

No. The AND should be wrapping the two sets of criteria.

I just noticed that I had missed a parenthesis. Try this corrected formula...

 

=IF(FIND([Primary Column]@row, JOIN(COLLECT({Stourton Maintenance Range 1}, {Stourton Maintenance Range 2}, AND(IFERROR(YEAR(@cell), 0) = 2019, IFERROR(MONTH(@cell), 0) = 11)), ",")) > 0, "Yes", "No")

PJay128

Yes the year works and the month.

 

Just need them together lol

 

Paul Newcome

I realized I had missed a parenthesis in my original formula and edit my last post. Try that one...

PJay128

Was it a ) missing? sick of counting the bloody things

It works Paul your a saint!

Thankyou for your help