Problem with Indirect when I try to link data to multiple worksheets

Hey All,

I have been trying this for the past 3 days now but I am missing something and not able to do it.


[B] [G] [H] [I] [J]...
[1] 2013 2014 2015 2016
[2] 500 588 676 764
[3]Horse 11 22 33 44 - Final File--------> This is where I enter the formulas by referencing it to respective sheets.
[4]Dog 12 23 34 45
[5]Cat 13 24 35 46
[6]Not living 14 25 36 47



[B] [G] [H] [I] [J]...
[1] 2013 2014 2015 2016
[2] 500 588 676 764 Driver From factory sheet---> This is the sheet that I reference to for my output.
[3]Horse 11 22 33 44
[4]Dog 12 23 34 45
[5]Cat 13 24 35 46
[6]Not living 14 25 36 47




The formula that I am using is:

=INDEX(INDIRECT("'"&$B$11&"'!$G$3:$J$6"),MATCH($B3,INDIRECT("'"&$B$11&"'!$B$3:$B$6"),0),MATCH(G$1,INDIRECT("'"&$B$11&"'!$G$1:$J$1"),0))

Where, $B$11 refers to the drop down cell that I created (in this case Driver from factory) to choose any particular sheet from the list of available sheets with the same formats with the intent that I should be able to change the sheet names using the drop down and get the result in my final sheet at any time.
However, the problem that I am facing is that if i insert a row anywhere between B3 and B6 in Driver from factory sheet my formulas in the Final file do not change accordingly and I get #NA for values referring to row B6.

Is there a way that if a row is inserted in the driver from factory file, it does not affect the final file and I get the output as I were to get if there was no row inserted in the driver from factory file.

Thanks for your help, I really need your help to break this.

Just change the range as below.
=INDEX(INDIRECT("'"&$B$11&"'!$G$3:$J$60"),MATCH($B3,INDIRECT("'"&$B$11&"'!$B$3:$B$60"),0),MATCH(G$1,INDIRECT("'"&$B$11&"'!$G$1:$J$1"),0))

The sample file that I posted was just an abstract from a very large file of a financial model. In reality, I have data below and above Row B so changing the range from G3:J6 to G3:J60 would actually translate to including rows that belong to other KPI's.

Also, I am looking to apply the formula to different line items within KPI's such as revenues, cost, etc and this would cause a problem.

This will take care irrespective of total rows. But System may become slow.

Please Login or Register  to view this content.

Another alternative is, if total rows does not exceed even after insertion rows beyond say 20000

Please Login or Register  to view this content.