nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Smartsheet Community

Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.

FORMULA HELP Collect number from other sheet, but with different data points to check before collecting

Melissareporting

Here's what I thought the formula would look like:

=INDEX(COLLECT({PM Budget 2026 Range 1}, {PM Budget 2026 Range 2}, [Tactic / Category]@row, {PM Budget 2026 Range 1}, >0), 1) AND({PM Budget 2026 Range 3}, {PM Budget 2026 Range 3}, >=[Start Date]@row, {PM Budget 2026 Range 3}, <=[End Date]@row))

It comes back #unparseable.

I need to collect a number from PM budget 2026 file and place into DOC cell - from PM budget 2026 Range 1 if PM budget range 2 has the correct verbiage labelled, AND if the date in PM budget range 3 is between two dates that fall between tagged date cells in the DOC.

What am I missing? I've tried a bunch of different collection routes.

Without the date - this formula works but I need to add a date range requirement.

=INDEX(COLLECT({PM Budget 2026 Range 1}, {PM Budget 2026 Range 2}, [Tactic / Category]@row, {PM Budget 2026 Range 1}, >0), 1)

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Paul Newcome

Try this:

=INDEX(COLLECT({PM Budget 2026 Range 1}, {PM Budget 2026 Range 2}, [Tactic / Category]@row, {PM Budget 2026 Range 1}, >0, {PM Budget 2026 Range 3}, >=[Start Date]@row, {PM Budget 2026 Range 3}, <=[End Date]@row), 1)

Melissareporting

@Paul Newcome Thanks for trying to help! I appreciate it! With your formula suggestion, it now comes back #invalid

Paul Newcome

Are you able to provide some screenshots for context? If you are getting the #INVALID VALUE error, that is essentially saying there are no rows that meet the criteria.

Melissareporting

@Paul Newcome

Paul Newcome

Based on these screenshots, I don't see a row that should be brought in.

You have one row that is greater than zero, but it doesn't have a date in it. Or you have a few rows with dates that fit, but they are blank in Range 1.

liz.mayeux

If you switch the grid to table view you can ask smartassist to analyze your formula and you ask what is prompting the error message. My experience has the formula reviewed and found the issue and offered suggestions for the correction. You can return your view back to grid once you have corrected the formula.

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, category/bannerUrl, category/bannerImageAlt, category/extensions, category/customizationLabels, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/bannerImageAlt, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.