why does named range only show up on some worksheets?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

XL 2003, SP2
The title about sums it up. On sheet A I create a range Resource_List. I
can access the range, it shows up when I hit F3, etc. If I move over to
another worksheet and hit F3 it is not listed. It seems some ranges are
"universal" and others I create only live with the sheet whence they were
created. This seems bizzaro to me. Especially when the range used to show
up everywhere. Any ideas on how to get these things to work ubiquitously?

I did add a DB query to drop data into the area of the range. Seems like it
hasn't worked since then. Could the query be "stepping" on it somehow?


thanks - Russ
 
I created a range in the area next to where the DB query returns data. That
range works fine, across all worksheets. Looks like it is pointing to the
idea that a query returned data area can't be part of a range. Can anyone
confirm this?

thx
 
Ok. I'm answering my own question here...

I can't say exactly why the problem occurs, but I can figure out how to fix
it. It seems to have to do with whether Calculation is turned on or off. I
have been leaving it off as I have a big hairy worksheet. With calc off, I
took what used to be a static Range and replaced the cells with a DB query.
I figured that the remaining portions of the workbook wouldn't care. Turns
out they do. If I drop the new query in the old Range and then hit Calc, no
dice. Going to another worksheet and trying to use Data Validation that
referred to that range doesn't work. Hitting f3 on other worksheets don't
even list the Range. On the other hand if I turn calc to auto and then drop
a new db query into the Range it seems to juggle everything just fine. The
other tabs that reference the Range continue to work as if the Range just
contains the old static data.
 
If you define a range called my_range in Sheet1 then it would also be
accessible from other sheets.

However, you can also define a range in Sheet2 with the same name, and
if you try to access my_range from Sheet2 Excel will assume that you
mean the range which is in Sheet2, unless you prefix the reference
with the sheet name, i.e. Sheet1!my_range.

Perhaps you have named ranges with the same name defined in multiple
sheets like this, so use the sheet name before the range name to
access the one you want.

Hope this helps.

Pete
 
Hey Pete,

Maybe that is unintentionally it. I do notice that when I hit
Insert/Name/Define, the Define Window pops up. On the left hand side of the
box are the Names in the workbook. Some of those names also have some text
to the right of them in single quotes listing the name of a worksheet. Most
do not. Do you know the significance of why some show the worksheet name
thusly? - thx much

January
February
March 'RMIS Sheet'
April 'RMIS Sheet'
May
 
Yes, you were probably in the RMIS Sheet when you hit Insert | Name |
Define, and the fact that March and April show the sheet name against
them implies that you also have March and April defined as named
ranges in other sheets, whereas January, February and May are only
defined once in the workbook.

If you move to another sheet and do Insert | Name | Define again, you
may see a different sheet name against March and April, so you can
track down where they are this way.

Hope this helps.

Pete
 
Back
Top