Trying to build a sheet that will total data from 35 sheets

Pflanz

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?

Willf

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.

Andrée Starå

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

Mike Wilday

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.

Pflanz

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

Mike Wilday

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? 

Andrée Starå

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

Pflanz

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.

Pflanz

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.

Andrée Starå

Ok.

Who is putting in the data and how?

Best,

Andrée

Andrée Starå

Can you share something so that I can get a better understanding of your structure?

Best,

Andrée

Mike Wilday

Can you share a screenshot with us? 

Pflanz

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

Andrée Starå

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

Pflanz

Ah, Thank you Andre'   I think that will work!

Andrée Starå

Great!

Happy to help!

Best,

Andrée