Index/Match (any limitations?)
I'm using index/match to reference account parameters on a master sheet from other sheets (sheet A, sheet B, sheet C, etc.). How well will this scale up? Can you use index/match to pull values from the master sheet to an infinite number of sheets or is there a limit to the number of times you can query the master sheet (or a limit to the number of requests made by the supported sheets A,B,C,...). I hope this makes sense. Thanks!
Comments
-
Hello,
Thanks for your post! It sounds like your wondering what the limitations are for cross sheet references in your formula specifically regarding the INDEX/MATCH functions. There are a few limitations that you should be aware of:
- A single sheet can contain up to 100 unique cross-sheet references in formulas. Each cross sheet reference can be used in any number of formulas, but only 100 unique references may be created.
- The cross-sheet references in a sheet may not exceed 25,000 unique referenced cells from other sheets. If one of your cross sheet references looks at two columns on a sheet with 100 rows, this will count as 200 cells toward the 25,000 cell reference limit.
The above information can also be found in our Help Center here (https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets).
If you think you may exceed these limitations, I would recommend creating a copy of the master sheet and deleting the cross sheet references in the copy so that you can create new ones. You can then consolidate both of these sheets into a single Report (Building Reports https://help.smartsheet.com/articles/522214-creating-reports). Note that cross-sheet references take about 30 minutes to an hour to be dissolved from a sheet after deleting them from the cells, so if you reach the limit, copy the sheet, and delete the current references, then you'll need to save the sheet and wait some time before you're able to add different references.
I hope this helps!
Isaac J
Smartsheet Support -
Isaac, thanks for the great info. If sheet A contains 5 unique cross sheet references (each filled down through 1000 rows) in order to look up values on the master sheet and I delete all of the rows in sheet A, does that also delete (free up) those 5000 previously used cross sheet references? Thanks!
-
Hi there,
I know I'm extremely late on a response here (I guess I don't have community notifications set up!) but I wanted to circle back since I stumbled across this post while working on something else.
To answer your question, deleting those 1000 rows will indeed free up the 5000 previously used cross sheet references.
Regards,
Isaac J
Smartsheet Support
