Multiple worksheet link with Index and Match formulas

[Bendy]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/Arnu/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Hi,
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
I am hoping someone can help me in giving me advice to a problem I have been struggling with all day. I have a summary sheet with a fancy formula using Index and Match functions which works well on the one page that I have it linked too.
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
However, I need obtain the answers from each worksheet (there are lots of them). Normally I would presume a ‘sum’ formula with a colon ‘:’ between the first worksheet tab name and the last worksheet tab name, would do the trick. Unfortunately that does not work.
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Can anyone advise me how I can do this.
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
The formula I have so far is below, but this is only linked to one worksheet and I need it to calculate across many sheets.
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
=INDEX('10 BAINBRIDGE RD'!$M$13:$M$30,MATCH(B13,'10 BAINBRIDGE RD'!$N$13:$N$30,0))
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Thanks for your assistance
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Regards
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
[FONT=&quot]Mark (AKA Ben)
[/FONT]

 

[Derek Brown]

Hello and welcome to The Board.
First of all, have you considered using the "Consolidate" feature of Excel? In Excel 2003 you would find this on the Data menu. You get the choice of creating using values or using links (best if the data is going to change). Look at Podcast 832 for an example of how to do it.
OK, so to your question ... you are looking for the 'spearing' technique and you will find good examples in Podcasts 834 and 974:
832 & 834:
http://www.mrexcel.com/podcast/2008_08_01_archive.html
974:
http://www.mrexcel.com/podcast/2009/03/mrexcels-learn-excel-974-noncontiguous.html