using index/collect to gather data from 2 sheets

Hello - I have several budget sheets that use an "index/collect" formula to grab information from a master data sheet. However, we are now moving some rows from the master data sheet to an archived master data sheet. Once that row is moved, all of the index/collect cells are broken since the original row with the data is gone.

I'd like to know if I can set up an index/collect formula to search 2 sheets instead of one. There will never be duplicates of the data we are indexing. Each unique combination will be on one of two sheets - the original or the archive.

Here's the formula:

=INDEX(COLLECT({All Programs - AY}, {All Programs - Title}, [Program Name]#, {All Programs - Calendar Year}, [Calendar Year]#, {All Programs - Term}, Term#), 1)

I'd like to have it index and collect the same info (AY, Title, Calendar Year, Term) but use 2 different source sheets instead of one. Is it possible?

Thanks to anyone who might have ideas!

Find more posts tagged with

Formulas

Accepted answers

All comments

George Lie

Hi Lao,

Greetings as one of the confused smartsheet users,

Unfortunately no, you can't do any formulas with 2 different source sheet.

che.rabajante

Hello @LAO

Yes you can reference 2 or more different source sheets, you just need to provide a sheet ID column (unique per sheet) and use IF, INDEX, COLLECT functions.

=IF([Sheet ID]@row = "1", INDEX(COLLECT({All Programs - AY}, {All Programs - Title}, [Program Name]#, {All Programs - Calendar Year}, [Calendar Year]#, {All Programs - Term}, Term#), 1), IF([Sheet ID]@row = "2", INDEX(COLLECT....

1 (unique sheet id) --- say for Master Data

2 (unique sheet id) --- say for Archive Master Data

Please note that the sheet ID should be listed in all rows with data/value

Hope this helps☺️

LAO

Thank you! I'm pleased to know this is possible.

I have tried this but get the error message "UNPARSEABLE". Here's the formula I used:

=IF([Sheet ID]@row = "1", INDEX(COLLECT({All Programs - Assigned To}, {All Programs - Title}, [Program Name]#, {All Programs - Calendar Year}, [Calendar Year]#, {All Programs - Term}, Term#), 1), IF([Sheet ID]@row = "2", INDEX(COLLECT({Archive - Assigned To}, {Archive - Title}, [Program Name]#, {Archive - Calendar Year}, [Calendar Year]#, {Archive - Term}, Term#, 1)

I added a Sheet ID column to each sheet and put a 1 in each cell of that column in my orignal sheet. In the archive sheet I put a 2 in each cell of that column.

Any ideas of what I might be doing wrong?

Andrée Starå

Hi @LAO

I hope you're well and safe!

At a glance, I noticed you were surrounding numbers with " ", which will make Smartsheet read as text.

Try something like this.

=IF([Sheet ID]@row = 1, INDEX(COLLECT({All Programs - Assigned To}, {All Programs - Title}, [Program Name]#, {All Programs - Calendar Year}, [Calendar Year]#, {All Programs - Term}, Term#), 1), IF([Sheet ID]@row = 2, INDEX(COLLECT({Archive - Assigned To}, {Archive - Title}, [Program Name]#, {Archive - Calendar Year}, [Calendar Year]#, {Archive - Term}, Term#, 1)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.