Cross Sheet Reference Error

Options
Sian.G
Sian.G ✭✭✭
edited 02/09/23 in Formulas and Functions

I have a data sheet that has all the stats for our projects, i had this sheet set up to index lookup each of those details depending on what project number you put in the primary column (Pronto JN:). I only have 17 individual references (1 column each within the data sheet) however i keep getting the attached error. Everything i read online says a reference range can include a maximum of 100,000 inbound cells each of these only have 6,000 cells so I'm confused.

Any assistance would be greatly appreciated.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Sian.G That inbound reference limit is on a per sheet basis. So if you have 17 references each looking at 6,000 cells...

    17 x 6,000 = 102,000

    The way I get around this is to split my metrics sheet into two sheets, Metrics1 and Metrics2. Each sheet then stays under the 100,000 limit. On Metrics1, use direct cell links from Metrics2 to bring the data values over so that you have all the metrics on a single sheet.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @Sian.G That inbound reference limit is on a per sheet basis. So if you have 17 references each looking at 6,000 cells...

    17 x 6,000 = 102,000

    The way I get around this is to split my metrics sheet into two sheets, Metrics1 and Metrics2. Each sheet then stays under the 100,000 limit. On Metrics1, use direct cell links from Metrics2 to bring the data values over so that you have all the metrics on a single sheet.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Sian.G
    Sian.G ✭✭✭
    Options

    @Jeff Reisman Thank you, its deceiving that they say you can have up to 100 reference ranges and a reference range limit of 100,000 but that's actually the sheet limit.

    Can you possibly explain in a bit more detail how you bring the data into a single sheet?

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Sian.G

    Sure. Right click on the cell in Metrics1 that you want to populate from a cell in Metrics2, then choose "Link from Cell in Other Sheet." Follow the prompts to navigate to Metrics2, and click on the cell that you want to link to. You can select multiple rows in a column at once in Metrics1 and link to a set of rows the same size in Metrics2 if you want to do many at once.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!