Asset Utilisation Help

Frank99

Hi all,

I'm trying to create a summary sheet which pulls data from a separate sheet populated from a form.

Basically I want to match and return when an asset was last used (a date - the most recent date to be precise within the last 7 days).

There can be multiple assets in the same cell which is kind of stumping me, I.E cannot use a VLookup so need to separate to match?

Looking for some ideas? I have tried:

=INDEX(COLLECT({Field Reports Range 3}, {Field Reports Range 2}, CONTAINS([Asset ID List]@row), {Field Reports Range 4}, >=TODAY(-7), 1))

Paul Newcome

Looks like you may just have a couple of syntax issues in your posted formula:

=INDEX(COLLECT({Field Reports Range 3}, {Field Reports Range 2}, CONTAINS([Asset ID List]@row, @cell), {Field Reports Range 4}, >=TODAY(-7)), 1)

Frank99

Hi Paul thanks very much for taking a look, I have updated as below but still getting INCORRECT ARGUMENT SET.

=INDEX(COLLECT({Field Reports Range 3}, {Field Reports Range 2}, CONTAINS([Asset ID List]@row, @cell), {Field Reports Range 4}, >=TODAY(-7)), 1)

Couple of images for reference.

Field Reports Sheet:

Asset Utilisation Sheet:

Paul Newcome

Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?

Frank99

Hi Paul.

Paul Newcome

Dates/Date ranges can get a little finicky at times. Let's try an @cell reference with the date criteria. If that doesn't work, move it to be the first range/criteria set in the COLLECT function (after the "range to collect" portion).

=INDEX(COLLECT({Field Reports Range 3}, {Field Reports Range 2}, CONTAINS([Asset ID List]@row, @cell), {Field Reports Range 4}, @cell>=TODAY(-7)), 1)

Frank99

Hi Paul,

Really appreciate the help.

I have tried these now:

And:

And:

Paul Newcome

The only thing that is left is to double check that your ranges are all the same size/shape. Edit each cross sheet reference and ensure you are clicking on only a single column header for each one. You may need to give the sheet a few seconds to load in the window before selecting the column header.

Frank99

Hi Paul,

Unfortunately that did not work either :(

Paul Newcome

In that case you will want to reach out to Support. I don't see anything that should be causing that error, so it may be a back-end bug.

Frank99

No worries I will do and thank you very much for your assistance with trying to resolve this Paul!