Trying to build a sheet that will total data from 35 sheets
I have 35 customer sheets built. I want to build a sheet that will total everything from these sheets to the Total sheet.
After I get so far I get the error message :
Unable to create a cross sheet reference because this sheet already contains the maximum number of different cross sheet references.
I was only at 15 cross references...meaning 15 cells that summed 35 sheets of a cell.
Is it really this limited?
Comments
-
Pflanz,
The limit is 25,000 cells referenced, I believe.
When you are selecting the range from "Reference Another Sheet", are you selecting just the one cell you want to reference?
Or are you selecting multiple columns? If you are selecting more cells than necessary to pull the data, those extra references count toward your 25,000 limit even if you don't display them on your new sheet.
-
Hi,
Maybe there are old references that still are active?
I hope this helps you!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
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.
-
You could save some references by tallying your data on the original sheet, then pulling those tallies into your new sheet instead and trying to reference all the data on all 35 sheets.
-
Here is an example of the formula on my total Sheet:
=SUM({DCF Security May Capacity Plan Range 1}, {DCF AD & Messaging May Capacity Plan Range 1}, {DCF EDS May Capacity Plan Range 1}, {DCF ESS May Capacity Plan Range 1}, {DCF FCS May Capacity Plan Range 1}, {DCF Network May Capacity Plan Range 1}, {DCF SAMH May Capacity Plan Range 1})
Range 1 is a single cell....so here it is only 7 sheets trying to be rolled up into the total sheet. I could not even get 15 of these cells set up
-
What about displaying the data in the total sheet by just linking the single cells in individual rows... then summing it within the sheet without using all the references. Does it work then? Or do you get the same errors?
-
I agree with Mike and if possible, I would sum it on the sheet and then cell link everything to another sheet.
Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help.
Would that work?
Best,
Andrée
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.
-
I have 35 identical customer work sheets.
Each has two columns for data entry, one being a beginning of year submission and the other being a 6 months later submission. Each of these columns have 27 cells that need to be added across all worksheets. They do not need to be added down the column, but rather across all columns.
I have an identical worksheet that is meant to total the cells from those columns
Data entry works fine.....However I can not seem to be able to collect the data for each cell across all worksheets. I get about halfway down, and it will not let me do any more cross sheet references.
-
Each item on the customer worksheets is unique, so the column can not just be added vertically down the sheet, but must be added horizontally across all sheets.
-
Ok.
Who is putting in the data and how?
Best,
Andrée
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.
-
Can you share something so that I can get a better understanding of your structure?
Best,
Andrée
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.
-
Can you share a screenshot with us?
-
The attached screen shot is the data entry sheet. In my real situation there are 35 of these. I want a new sheet which will not be data entry but will simply sum the cells from the data entry columns of these other 35 sheets
-
Thanks!
I would probably cell link each column with the data to another sheet and then create the columns for each type and sum everything there on the new sheet.
Would that work?
Best,
Andrée
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.
-
Ah, Thank you Andre' I think that will work!
-
Great!
Happy to help!
Best,
Andrée
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives