Strategies for Managing Column Counting in Workarounds for Smartsheet Cross-Sheet Reference Limits?
![alamn](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
Hello Smartsheet community,
I've hit the limit of cross-sheet references allowed in Smartsheet, which has led me to adopt a workaround using formulas like =INDEX({Cross-Sheet Range}, MATCH(target_value, {Match_Range}, 0), column_index)
. This solution allows me to continue referencing data across sheets without adding more cross-sheet references. However, this method requires manually counting columns to determine the correct column_index
, which is prone to errors and quite cumbersome as the number of columns grows.
Is there a more efficient way to determine the correct column index without manually counting each time? Perhaps a function or a feature within Smartsheet that I might not be aware of that could simplify or automate this part of the process?
Any advice or tips to make this easier would be greatly appreciated!
Thank you!
Answers
-
@alamn Hello! I have begun using lots of INDEX/MATCH formulas as well. I don't have an elegant solution, but what I am doing is modifying column names to include the column number (1-Name; 2-Phone, etc.) so that the number is easily identifiable.
The one challenge is that you can't move the columns around in the sheets — something I have tended to do a lot, depending on the task at hand. Someone else may have a better idea!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!