MaryLN

I'm using Excel 2007, and want to use a 3-D reference to sum a named range across multiple workbooks. The formula I entered is:
=SUM('Sheet1:Sheet99'!OrderTotal) where Sheet1 and Sheet99 refer to the first and last worksheets I want to include, and OrderTotal is a named range defined on each worksheet (defined OrderTotal on each worksheet with scope set to that worksheet). I get a #NAME? error when I do this. Any suggestions? I don't want to use a specific cell reference, since the location of OrderTotal may vary depending on how many lines of data appear on each worksheet.

Elkar

The problem is likely that your Range "OrderTotal" already refers to a specific sheet(s). So its kind of redundant to use a named ranged combined with Sheet references. Try setting up a new Named range to refer to all of the cells you want to add, and then use:

=SUM(NewOrderTotal)

Where NewOrderTotal is equal to 'Sheet1:Sheet99'!$A$1:$A$100 or whatever your actual range is.

HTH
Elkar

MaryLN

OrderTotal refers to a single cell on each worksheet, and that cell can move depending on how much detail appears on each sheet. I used the named range to avoid having to specify something like $E$10, since that cell might not actually be the order total.

I tried the solution you recommended, and got the #NAME? error again.

Elkar

I don't think named ranges will work in this case, since the cell references are not consistent.

An alternative may be to use some sort of unique identifier on each sheet to indicate where the Order Total will be located. For example, if your order total always appears somewhere in column E, you could have next to it in column D the text "Order Total:".

Then on your summary sheet, you could use the formula:

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:99"))&"!D1:D100"),"Order Total:",INDIRECT("Sheet"&ROW(INDIRECT("1:99"))&"!E1:E100")))

HTH
Elkar