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))
Answers
-
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)
-
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:
-
Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?
-
Hi Paul.
-
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)
-
Hi Paul,
Really appreciate the help.
I have tried these now:
And:
And:
-
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.
-
Hi Paul,
Unfortunately that did not work either :(
-
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.
-
No worries I will do and thank you very much for your assistance with trying to resolve this Paul!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!