Max out Cross sheet reference ?

Lynna

I have an error message "unable to create cross sheet reference because this sheet already contains the maximum number of different cross sheet references." 

How to resolve this issue? I already deleted many rows to reduce the cross reference but didn't work.

Paul Newcome

It isn't necessarily how many times a cross sheet reference is used but how many unique cross sheet references. THe key word there is "unique" or in the case of the alert "different".

If I create a single cross sheet reference, I can use it 1,000 times with no issue. But I can only create 100 unique cross sheet references within a single sheet.

You are going to need to find a way to consolidate your cross sheet references possibly combining ranges and/or using cell links and consolidated data in helper columns on your source sheet(s).

Andrée Starå

Hi @Lynna

Each unique reference that is between like below.

{Reference Name}

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

In your above example, it would be four.

{Text A} and {Text B} would count as two different cross sheet references, but {Text A} and {Text A} would only count as one different/unique cross sheet reference. Even though it is used multiple times, it only counts as one unique.

Paul Newcome

You are going to want to use a SUMIFS if you are adding things together.

=SUMIFS({range to add}, {Parameters}, Parameters@row)

Paul Newcome

It isn't necessarily how many times a cross sheet reference is used but how many unique cross sheet references. THe key word there is "unique" or in the case of the alert "different".

If I create a single cross sheet reference, I can use it 1,000 times with no issue. But I can only create 100 unique cross sheet references within a single sheet.

You are going to need to find a way to consolidate your cross sheet references possibly combining ranges and/or using cell links and consolidated data in helper columns on your source sheet(s).

Lynna

Thanks!  How do you count cross sheet reference?    For example " =INDEX({AizuApr}, MATCH([Level1 Parameter]@row, {Aizu SPI Level 1 Parameter}, 0)) + INDEX({BelgiumApr}, MATCH([Level1 Parameter]@row, {Belgium SPILevel1Parameter} "   how many cross sheet reference is this?  1? 2? 6?

Andrée Starå

Hi @Lynna

Each unique reference that is between like below.

{Reference Name}

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

In your above example, it would be four.

{Text A} and {Text B} would count as two different cross sheet references, but {Text A} and {Text A} would only count as one different/unique cross sheet reference. Even though it is used multiple times, it only counts as one unique.

Lynna

I am combining sheets into one file (see attached SPI Data) to reduce cross sheet reference. Each Site (e.g Aizu, Belgium..etc) populate their 47 rows of parameters for the different months in one big sheet.

I would like to roll up data to the SPI Data Summary file but I won't be able to use =index formula with this new format. What formula works best for this setup? THANKS!!

For example, I need to add up all the sites for electricity for Jan, Feb, Mar...etc. and do the same for each row of Parameters.

SPI Data.xlsx

Uploaded Jan 22, 202133.82 KB

SPI Data - Summary.xlsx

Uploaded Jan 22, 202137.14 KB

Andrée Starå

@Lynna

You can use a VLOOKUP formula instead.

VLOOKUP Function | Smartsheet Learning Center

https://help.smartsheet.com/function/vlookup

Looks up a value and returns a corresponding value in the same row but from a different column.

Would that work/help?

Paul Newcome

You can still use the INDEX function, adjust your ranges to cover multiple columns, then leverage the column number portion of the INDEX function.

Lynna

For the example I provided, I can only search the first site with the following formula, how do I write the formula so it can keep going down the column to add all the sites (e.g. Aizu, Belgium, Bucheon...etc) electricity ? THANKS!!

=INDEX({Jan}, MATCH(Parameters@row, {SiteParamters}, 0))

Paul Newcome

Are those sites in the Parameters column?

Lynna

No, there is a column named "SITE" (see attached) , each site have 47 rows of unique "Parameters" . I need to add up all electricity for all sites, then add all STEAM, and the rest ...etc. roll these data into the summary sheet (attached)

SPI Data - Summary.xlsx

Uploaded Jan 22, 202137.14 KB

SPI Data.xlsx

Uploaded Jan 22, 202133.82 KB

What's the best formula to use ? Can you provide an example on how to write the formula?

Paul Newcome

You are going to want to use a SUMIFS if you are adding things together.

=SUMIFS({range to add}, {Parameters}, Parameters@row)

Lynna

THANK YOU SO MUCH! The formula worked!!

Paul Newcome

Happy to help. 👍️

Melanie Proebstel

@Paul Newcome What is considered a sheet for the 100 limit? Is it a workspace? I have broken down a sheet into 10 different sheets and I still receive the same error.

Paul Newcome

@Melanie Proebstel The 100 limit is 100 unique cross sheet references on a single sheet. Whichever sheet has the {Cross Sheet Reference} cannot have more than {100 Different Ranges} on that one sheet.