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!
Answers
-
Hi Lao,
Greetings as one of the confused smartsheet users,
Unfortunately no, you can't do any formulas with 2 different source sheet.
-
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☺️
-
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?
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!