Asset Utilisation Help

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))

Find more posts tagged with

Accepted answers

All comments

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:

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.