Confused on Cross-sheet references

Marlana K.

I have received the below message when attempting to create a Data Collection sheet:

Unable to create cross-sheet reference, because this sheet already contains the maximum number of different cross-sheet references.

I have read the many different cross-reference post but still find myself confused. Here is my situation.

Data Collection Sheet pulls data from 7 different sheets (2-8)

It gives me the message when I get to sheet 7 (Mth 9 Disposition 6 Column and Attempt Date 6 Column)

Here is an example of the formula for row 1 the disposition changes and the date attempt changes.

=COUNTIFS({(2) Pre-engagement (Disposition 1}, "Ready to begin", {(2) Pre-engagement - Date Attempt 1}, >=DATE(2024, 11, 1), {(2) Pre-engagement - Date Attempt 1}, <=DATE(2024, 11, 30))

My assumption is each Disposition is a "Unique" reference and the date counts as one even though it is in the formula twice. Which would make sense as to why it gives the message.

However why when I break the sheet out and try to create new sheets do I still get the error.

I am open to suggestions on this as it is for monthly invoice for support services provided for a program we run. I will have to do this month over month and there is another service provider report that I will need to be able to create data sheet for (currently it works but I have to make an adjustment to look at the dates).

Michelle Choate 2

Hi Marlana - A 'reference' is anything that would be in the {} brackets inside of a formula. If you right click on any cell, you can select "Manage Reference" and it will show you all references you have for the sheet.

This is why I always recommend that best practice is to name your references wisely and in detail so you know what they are, what sheet they are coming from, etc. rather than "Sheet Name Reference 1" and "Sheet Name Reference 2" Etc. I use things like "Sheet Name | Column Name". That is what works best for me.

This way you can also go into your reference manager, and remove any references that you do not need for a specific sheet. If you create a duplicate of a sheet, all of the references remain, even if you delete the columns that contained the formulas using them. You need to decommission/delete the references in order to be able to use more.

Marlana K.

@Michelle Choate 2 That makes sense, I do name my references by sheet name and column name, I just deleted the information in my example as it had information I did not want visible here. I am not sure this is going to work for the monthly invoicing for this solution and now I am getting a bit concerned that not only is the data collection sheet going to be a problem but the whole program is going to grow too big. Back to the drawing board.

Michelle Choate 2

If you want to bounce ideas off of me, feel free to message me at my email below and we can have a whiteboard session. :)

Marlana K.

@Michelle Choate 2 Thanks, I may take you up on that once I regroup and look at what I am going to do. This was not expected to grow as fast as it has. Potential growth was the reason it was split into the various sheets since the program is a year long and has various stages of support and follow up calls. Just to give you an idea you can see in the screen shot the number of calls that could potentially happen. We already have 140 participants and it grows everyday now. While that is amazing growth that means these sheets are going to outgrow the cell, row, column limitations. Intake to archive is not going to work for this either because the individual trickles from phase to phase. This program will eventually move into an application that is being built by our IT but until then it is living in my world.

Michelle Choate 2

That all makes complete sense. I can see how it grew so quickly! Well, I am always available for a good white board session if that is needed :)