Cross Sheet Reference Error

Sian.G

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.

Jeff Reisman

@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.

Jeff Reisman

@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.

Sian.G

@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

@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.