Reply to thread

Message: [QUOTE="Dave Peterson, post: 3126423"] First, could you just change your formulas to include all of column B? If there are non-numeric headers in B1 and nothing under B5, then you could just: =SUM('C:\My Documents\excel\[book1.xls]Sheet1'!$B:$B) If not, then I think I'd define a range that points at B2:H5 (H is my last used column). But I'd lock Row 5 and force them to insert rows between 2 and 5. Then when the range expands, the name will adjust. Inside each source workbook: Insert|Name|Define names in workbook: myRng (for my example) refers to: =Sheet1!$B$2:$H$5 Then save and close that workbook. Now adjust your formula to look like: =SUM(INDEX('C:\My Documents\excel\book1.xls'!myRng,,1)) The final 1 represents the first column in that range. So to sum the 4th column: =SUM(INDEX('C:\My Documents\excel\book1.xls'!myRng,,4)) ====== This kind of thing works ok with closed workbooks. If your source workbooks were always open, you could use a dynamic range name. kind of like: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$99),7) That counts the cells used (no gaps) from B2:B99. But this kind of formula won't work with a closed workbook. (I got #ref! errors back.) If you want to learn more about dynamic range names, visit Debra Dalgleish's site: [URL]http://www.contextures.com/xlNames01.html#Dynamic[/URL] [/QUOTE]