VLOOKUP Function | Smartsheet Learning Center
https://help.smartsheet.com/function/vlookupLooks up a value and returns a corresponding value in the same row but from a different column.
Smartsheet Basics
Smartsheet Basics
Ask questions about the core Smartsheet application: Sheets, Forms, Reports, Dashboards, and more.
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.
Best Answers
-
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).
-
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!
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.
-
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.
-
You are going to want to use a SUMIFS if you are adding things together.
=SUMIFS({range to add}, {Parameters}, Parameters@row)
Answers
-
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).
-
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?
-
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!
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.
-
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.
-
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.
-
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 can still use the INDEX function, adjust your ranges to cover multiple columns, then leverage the column number portion of the INDEX function.
-
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))
-
-
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)
What's the best formula to use ? Can you provide an example on how to write the formula?
-
You are going to want to use a SUMIFS if you are adding things together.
=SUMIFS({range to add}, {Parameters}, Parameters@row)
-
THANK YOU SO MUCH! The formula worked!!
-
@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.
-
@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.
