Cell Linking & Reference Table Too Large

Jonathan Dayan · 03/11/21

Hi all,

We are trying to pull in specific data from a large tracking sheet we have for all vendors to share with just one vendor. We do not need all the columns from the source sheet to be in this vendor specific sheet, but the order of the columns must be maintained in the source sheet. The difficulty we are facing is that when referencing all the consecutive columns, the reference exceeds the 100,000 cell limit and all columns referenced must be next to each other.

How can we work around this or devise another solution? Reports will not work because we do not want the vendor seeing internal comments. Thank you in advance.

Genevieve P. · 03/12/21

Hi @Jonathan Dayan

Is the purpose of this just to provide the information to the vendor without them needing to edit or make updates?

If so, it does sound like a Report would be a better option; you could filter by the Vendor and reorganize columns as needed. If the Comments section is what's preventing you from using a Report, what about embedding the Report as a Report Widget on a Dashboard? Report Widgets don't show attachment or comment columns.

That said, there may be some limitations with this. For example, a maximum of 150 rows will appear from the report in this widget-version. See: Widget Types: Report Widget

If this won't work for you, can you clarify how you're cell-linking the content from one sheet to another? If you're using a VLOOKUP formula, you could change this to be an INDEX(MATCH type of formula. Where a VLOOKUP requires a table (so perhaps spanning across multiple unnecessary columns), an INDEX(MATCH compares two individual columns. You can see an example of how to build this type of formula on this other Community post, here.

Let me know if either of this has helped!

Cheers,

Genevieve

Genevieve P. · 03/15/21

Hi @Jonathan Dayan

No problem, I'm happy to help clarify. The Unique ID will be what you MATCH in the second half of the formula. The value you want returned will be identified in the INDEX function at the beginning.

You'll need to add a new cross-sheet reference for each individual column's values that you want pulled across... so if you have 5 columns you want to duplicate, you'll need 5 individual formulas, each in their corresponding column.

So for the first column with information you're pulling through:

=INDEX({Vendor Column 1}, MATCH([Unique ID]@row, {Unique ID Column in other sheet}, 0))

Then in the second column, delete out and add a new reference at the beginning:

=INDEX({Vendor Column 2}, MATCH([Unique ID]@row, {Unique ID Column in other sheet}, 0))

Each of the cross-sheet references, ex {Vendor Column 2}, only references one column, not a range. If you still need some help, it would be useful to see a screen capture of each sheet, but please block out sensitive data.

Thanks!

Genevieve

Genevieve P. · 04/06/21

Hi @Jonathan Dayan

With 10 columns I'm not sure if this is the best solution. You would need 10 helper columns, one for each of your formulas to check the other sheet.

In this image below you'll see I have two helper columns that say "Sheet 2" to pull the value from a second sheet. This also shows how the IFERROR function (see here) would wrap around the formula:

=IFERROR(INDEX({Sheet 2 - Moved Data Status}, MATCH([Unique ID]@row, {Sheet 2 - Moved Data Unique ID}, 0)), "Not This Sheet")

Screen Shot 2021-04-06 at 2.34.11 PM.png

In your instance, you can build the check directly into your INDEX(MATCH formula it will just get a little more complex. You would say, IFERROR (or, if there's an error that says "NO MATCH"), then complete a second formula to run the INDEX(MATCH to the other sheet.

Try something like this:

=IFERROR(INDEX({Sheet 1 Primary Column}, MATCH([Unique ID]@row, {Sheet 1 Unique ID}, 0)), INDEX({Sheet 2 - Moved Data Primary Column}, MATCH([Unique ID]@row, {Sheet 2 - Moved Data Unique ID}, 0)))

This way you can get rid of the helper columns:

Screen Shot 2021-04-06 at 2.40.33 PM.png

Does this make sense?

Cheers!

Genevieve

Genevieve P. · 03/12/21

Hi @Jonathan Dayan

Is the purpose of this just to provide the information to the vendor without them needing to edit or make updates?

If so, it does sound like a Report would be a better option; you could filter by the Vendor and reorganize columns as needed. If the Comments section is what's preventing you from using a Report, what about embedding the Report as a Report Widget on a Dashboard? Report Widgets don't show attachment or comment columns.

That said, there may be some limitations with this. For example, a maximum of 150 rows will appear from the report in this widget-version. See: Widget Types: Report Widget

If this won't work for you, can you clarify how you're cell-linking the content from one sheet to another? If you're using a VLOOKUP formula, you could change this to be an INDEX(MATCH type of formula. Where a VLOOKUP requires a table (so perhaps spanning across multiple unnecessary columns), an INDEX(MATCH compares two individual columns. You can see an example of how to build this type of formula on this other Community post, here.

Let me know if either of this has helped!

Cheers,

Genevieve

Jonathan Dayan · 03/12/21

https://community.smartsheet.com/discussion/comment/283398#Comment_283398

Hi Genevieve,

Thank you for response! I think the latter option would work better precisely because of the limitations you mentioned with the first option. We are using a vlookup in the sheet. I will try an index(match formula instead and see how that goes.

Jonathan Dayan · 03/12/21

https://community.smartsheet.com/discussion/comment/283398#Comment_283398

Hi Genevieve,

Just following up to get some more clarity on the syntax of the actual formula. We want to bring in all the data from rows where vendor column is labelled as a specific vendor's name. Each row has a unique identifier/ID. How do we set the formula up to get the data to pull in only this one vendor's ID and other corresponding row data?

Genevieve P. · 03/15/21

Hi @Jonathan Dayan

No problem, I'm happy to help clarify. The Unique ID will be what you MATCH in the second half of the formula. The value you want returned will be identified in the INDEX function at the beginning.

You'll need to add a new cross-sheet reference for each individual column's values that you want pulled across... so if you have 5 columns you want to duplicate, you'll need 5 individual formulas, each in their corresponding column.

So for the first column with information you're pulling through:

=INDEX({Vendor Column 1}, MATCH([Unique ID]@row, {Unique ID Column in other sheet}, 0))

Then in the second column, delete out and add a new reference at the beginning:

=INDEX({Vendor Column 2}, MATCH([Unique ID]@row, {Unique ID Column in other sheet}, 0))

Each of the cross-sheet references, ex {Vendor Column 2}, only references one column, not a range. If you still need some help, it would be useful to see a screen capture of each sheet, but please block out sensitive data.

Thanks!

Genevieve

Jonathan Dayan · 03/15/21

https://community.smartsheet.com/discussion/comment/283545#Comment_283545

Hi Genevieve,

Thank you very much! I figured it out yesterday. Appreciate your help.

Genevieve P. · 03/15/21

Hi Jonathan, I'm glad to hear that! No problem at all.

Jonathan Dayan · 04/05/21

https://community.smartsheet.com/discussion/comment/283547#Comment_283547

Hi Genevieve!

We have successfully used index and match to address the issue I broached about a month ago. I am curious, however, if there is a way to use index and match to reference multiple sheets where the desired data match can possibly be found. To clarify, we created a sheet to which cases that have been resolved automatically move. They are no longer on the main reference sheet we used for the index match formula, so a lot of no matches are being yielded. We would like to reference both the original source sheet and the finalized case sheet. Thank you for your response in advance!

Genevieve P. · 04/06/21

Hi @Jonathan Dayan

How many columns are using this Index(Match?

The easiest thing to do may be to add a helper column which does an Index(Match into your second sheet where rows are moved to... you can wrap an IFERROR statement around the formula so if there is no match you can have it say something like "Not This Sheet".

Then in your current formula, first check this other column to see if there's data in the helper column. If it says "Not This Sheet", check the original sheet. Otherwise, if there's data, return that exact data.

Ex:

=IF([Helper Column]@row = "Not This Sheet", INDEX({Vendor Column 1}, MATCH([Unique ID]@row, {Unique ID Column in other sheet}, 0)), [Helper Column]@row)

Does that make sense? This is the alternative to building a more complicated formula into your current columns.

Cheers!

Genevieve

Jonathan Dayan · 04/06/21

https://community.smartsheet.com/discussion/comment/286223#Comment_286223

Hi Genevieve,

Thank you for your response! We are using the index(match) for about 10 columns with a unique ID for each case serving as the match. The "not this sheet column" would only need to reference the ID, correct? Also, can you provide an example of how to implement the IFERROR in the syntax to yield that "not this sheet?" Thank you!

Genevieve P. · 04/06/21

Hi @Jonathan Dayan

With 10 columns I'm not sure if this is the best solution. You would need 10 helper columns, one for each of your formulas to check the other sheet.

In this image below you'll see I have two helper columns that say "Sheet 2" to pull the value from a second sheet. This also shows how the IFERROR function (see here) would wrap around the formula:

=IFERROR(INDEX({Sheet 2 - Moved Data Status}, MATCH([Unique ID]@row, {Sheet 2 - Moved Data Unique ID}, 0)), "Not This Sheet")

In your instance, you can build the check directly into your INDEX(MATCH formula it will just get a little more complex. You would say, IFERROR (or, if there's an error that says "NO MATCH"), then complete a second formula to run the INDEX(MATCH to the other sheet.

Try something like this:

=IFERROR(INDEX({Sheet 1 Primary Column}, MATCH([Unique ID]@row, {Sheet 1 Unique ID}, 0)), INDEX({Sheet 2 - Moved Data Primary Column}, MATCH([Unique ID]@row, {Sheet 2 - Moved Data Unique ID}, 0)))

This way you can get rid of the helper columns:

Does this make sense?

Cheers!

Genevieve

Jonathan Dayan · 04/06/21

https://community.smartsheet.com/discussion/comment/286263#Comment_286263

Yes, it certainly makes sense! I will give it a try. Thank you for your assistance and clear explanation.

Genevieve P. · 04/06/21

Happy to help! 🙂

Cell Linking & Reference Table Too Large

Best Answers

Answers

Categories