Problem referring to "names" in formulas across sheets

[Rene]

Hi Excel - experts!

I'm somewhat baffeled:

On sheet 1 I have defined a "name" for cells A3:A500 as "AllDays".

On sheet 3 I have a formula using this name: =SUBTOTAL(9;AllDays)

However if I check the formula with "evaluate formula" this equals to
=SUBTOTAL(9;A3:A97).
Evidently referring to names across sheets is limited to 100 cells.

If I use the same formula on sheet 1 everything seems to work.

What am I doing wrong?

Thanks,
Rene

 

[Pete_UK]

I use named ranges across sheets which are tens of thousands of rows
deep, so there should not be a limit like the one you have observed.
Did you have a filter active on sheet 1 at the time you did "evaluate
formula"? Perhaps the filter was only showing rows up to 97.
Alternatively, you might have two named ranges called AllDays - one for
Sheet1 and the other for Sheet3, and your Sheet3 range might be A3:A97.
To check this, do Insert | Name | Define and select AllDays from the
list and see what it refers to.

Hope this helps.

Pete

 

[Rene]

Hi Pete,

using a filter was actually the way I found the problem.... I selected
a filter on sheet1, which only displayed rows 100-150 and the formula
on sheet3 showed a Div/0 error

If I sellect "AllDays" (just occurred to me what a stupid name that
is...wasn't that a female hygene product...) the true range of A3:A500
is selected.

Like I said the exact same formula on sheet1 works.

I found a workaround: if I select =SUBTOTAL(9;'Filename.xls'!AllDays)
it seems to work.

Any reason why?

Cheers,
Rene

 

[Guest]

Hi Rene,

Try changing your formula to:

=SUBTOTAL(9,'Sheet1'!AllDays) ' (notice the comma instead of a colon "as
posted"!)

When you reference another sheet, include the sheetname so Excel knows it's
not the activesheet you are referencing. Also, make sure the name is local to
Sheet1. (it will show the sheetname to the right in the Defined Names dialog.

Regards,
GS

 

[Rene]

Hi!

Thanks for your comments....

here some more questions:
schrieb:

Hi Rene,

Try changing your formula to:

=SUBTOTAL(9,'Sheet1'!AllDays) ' (notice the comma instead of a colon "as
posted"!)

I've done that (BTW I'm using a german version of Excel... that's why
colon vs. comma...I think).... however, when I enter 'Sheet1'AllDays)
the formula is automatically updated and includes the file name instead
of the sheet name. Why is that?

When you reference another sheet, include the sheetname so Excel knows it's
not the activesheet you are referencing. Also, make sure the name is local to
Sheet1. (it will show the sheetname to the right in the Defined Names dialog.

Why does the name have to be local to Sheet1. I thought the beauty of
"names" was that I could use them throuout the entire workbook.

Thanks for all your help!
Rene

 

[Guest]

Hi Rene,

It's just good practice to give defined names local scope. It tells Excel
that the name belongs to a specific sheet in the active workbook. If you had
20 sheets that used that name, Excel knows which sheet it belongs to by
reference. Leaving the sheet reference out tells Excel it's the name on the
active sheet.

Another point to consider is, if you copy/move a sheet from another workbook
that used the same name, you'll get a names conflict message that asks if you
want to replace the existing reference with the one you're importing with the
copied/moved sheet. This doesn't happen when the names are local.

Try this: enter your formula as before, but physically select the
referenced range on the other sheet. See what Excel automatically enters. It
should be similar to this:

'SheetName'!RangeReference

As far as seeing 'filename.xls' goes, I've had that happen where the
sheetname was the same as the workbook name. Otherwise, I can say why you're
seeing it. It shouldn't happened if the sheet is in the active workbook
(normally).

Check some of the other posts here for anything to do with names, or defined
names, or naming ranges. There's lots of replies with links to sites with
good info about using names. You may find them helpful!

Regards,
GS