Problem referring to "names" in formulas across sheets

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

0
rhessling (10)
2/17/2006 10:16:28 AM
excel 39879 articles. 2 followers. Follow

5 Replies
367 Views

Similar Articles

[PageSpeed] 18

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

0
pashurst (2576)
2/17/2006 10:56:52 AM
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



Pete_UK schrieb:

> 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

0
rhessling (10)
2/17/2006 12:27:21 PM
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" wrote:

> 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
> 
> 
0
GS1 (20)
2/17/2006 10:07:27 PM
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

0
rhessling (10)
2/18/2006 10:11:15 PM
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



"Rene" wrote:

> 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
> 
> 
0
GS1 (20)
2/18/2006 11:36:27 PM
Reply:

Similar Artilces:

Using Range names in selecting chart series.
Hi, I have a range named DataCount. This will count the number of data points i have in a column. I would like to use this information when specifying the series range in chart. ActiveChart.SeriesCollection(1).Values = "='System 4'!R2C13:R35C13" In this example I need to substitute R35 with DataCount. What is the correct syntax? Thanks in advance, Cherrs! What kind of variable is DataCount? Is it a named range? A VBA Excel.Range object? A string that contains an address? Does it reference R35 or R35C13? - Jon ------- Jon P...

Names Of People In GBroup Appear In The "To:" Field
When I define and populate a group in Outlook Oxpress then send an email to that group, the names of all the members in the group appear in the "To:" field on the recipient's emails. Is there a way to suppress that so only the group name appears in the "To:" field? I'm using Windows XP Pro and Outlook Express 6. Thanks! ***************** Cameron Hughes ***************** ************ cah2011@med.cornell.edu ************* *********** '00 Sportster 1200 Custom ************ **************** Carpe diem, baby **************** Not possible that I know of. Use the ...

Invisible Column names and Row numbers
Friends, I received a work sheet from my friend which has data from a1 to m60. But surprisingly after the column M, whole letters are invisible and same in the numbers after 60 it is invisible, like sealed with a color pack. How do I see number 61 onwards and column M onwards N O P etc etc .. Please assist.. Thanks Suresh Sounds like your friend hid the columns/rows. Column M, and drag to the right. Then right-click, unhide. Repeat similarly for rows. -- Best Regards, Luke M "Suresh Lohar" <suresh.lohar@gmail.com> wrote in message news:57D...

why are some file names blue and some are black
I have a group of excel documents saved. For some reason, in the my documents folder, the file name has changed from black to blue ? Why is this ? I am using excel 2003. Is it possible that your IS department recently converted your network folder(s) to a compressed format for storage pace issues? On a network, Blue file names typically mean compressed files and Black file names are uncompressed. Hope this helps, Gary Brown "Trudy" wrote: > I have a group of excel documents saved. For some reason, in the my > documents folder, the file name has changed from...

Managing Payee Names in Money Home & Bus 2007
I have tried all the help suggestions regarding managing payees in MM, yet I still have a huge list of duplicate "similar" names. I bank at a credit union that does not connect automatically--I have to do a download, so I'm not sure if that is part of the problem. I have a long list of excluded names, but my bank puts transaction numbers or dates on many of my check card purchases. MM is not automatically changing the name for me. Here is an example: "CHK PUR" is at the beginning of all my debit card purchases, and MM won't change the name, so I end up wit...

Range names referring to other range names
How do I create a range name that points to another range name, and then edit the data source for the pie chart to the new range name? Jon Peltier has examples of dynamic charts in which a range is defined, and another range is defined as offset from the first range: http://www.peltiertech.com/Excel/Charts/DynamicColumnChart1.html Jo Anna wrote: > How do I create a range name that points to another range name, and then edit the data source for the pie chart to the new range name? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Pivot Table Repeats Names in multiple rows
I have data on hours worked for about 70 employees (Names are rows) for the last year (Months are columns). For about 15 of those names, the Pivot Table is not consolidating all the data for that employee. Rather, it repeats the name for 2 or more rows as if those names were spelled in various ways. The hours data for those affected employees are also broken out among the various rows. The source data originally had this problem, but I fixed all the misspellings. By filtering the names field in my source data, I have confirmed that there is now only one version of each name. Why c...

I need to exclude duplicate patient names for dates of service is.
"Shannon" <Shannon@discussions.microsoft.com> wrote in message news:4E5EA619-0BC7-4CE7-9D96-32EE847A6CD6@microsoft.com... Please explain what your problem is in the body of the message, not in the header. /Fredrik You want to exclude duplicate patient names for dates of service is ... WHAT? Is what? Also, what are you trying to do? Not enough information. Edwin Tam edwintam@vonixx.com http://www.vonixx.com "Shannon" wrote: > ...

Line Charts
!Hi! I need to build a LINE CHART with diferent SERIES NAMES, but I cannot get different names for the serie. This is the chart JAN $100 FEB $200 MAR$ 300 and so on for the year. Last year I manually change month by month the Serie NAme on the plot area, but there has to be a way to get it when I add the info for the next month. Help? Are you saying that each series has only one point? That seems a strange way of drawing a chart. You haven't told us which version of Excel, but in Excel 2003 the "Source Data" item (in the chart menu or by right-clicking on the chart) inclu...

Incoming/Outgoing server names?
I have just brought a new PC and am trying to set up Outlook Express. I have an ISP provider and an email address, but do not want to use their homepage to access my email. I appears I need an Incoming server name (POP3) and Outgoing server name (SMTP)?? I have no idea what these should be and can't find any reference to them in the manual that came with the PC. Has anyone else come across this before and knows of a solution? Thanks! And your ISP is? you really need to check the website, do a search for POP3 and SMTP details. For example Bellsouth.net is: (POP) mail.bellsouth...

Are there any shortcuts to typing range names?
As I prepare to link workbooks, I have to type descriptive range names, e.g.: CDA_GrossSales_Case1 Now, the workbook contains 3 different scenarios, therefore there will also be named ranges: CDA_GrossSales_Case2 and CDA_GrossSales_Case3 There will be several other sets of named ranges and, in each instance, the name will only differ by that one differentiating number on the end. Both from the point of view of reducing typos and unnecessary typing, I have been looking (in vain!) for a way to copy the names from one range to the next, so that I all would have to do would be to chang...

Assign values to names in a drop-down list?
Is it possible to assign values to names in a list, so that when you validate it as a drop-down list, you can select a name from the drop-down and it's corresponding value will be added to separate cell? Basically, I have survey questions which have five possible responses: Excellent - Very Good - Good - Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very Good and so on, so that when a response is selected from a drop-down, it's value appears in a separate cell (so that we can calculate a total and average score from the selections). Thanks B Hi Barry ...

convert repeated sheet 1 names to once in sheet2
i have a work sheet contained name with score, in that sheet names are repeating scores also repeated but values are change( ex: a=10, b= 6, c= 2, a= 18, b= 1, c= 8, a =1) in the sheet 2 i want only once of the name with total value what i do plesase help me Naanishiva - It looks like you need a pivot table. Use the pivot table wizard to do this - you will to select the data area of the worksheet including the column titles (name, score), and then put the results in the second worksheet. Use the Layout button in the wizard to put the name in the Rows section, and the ...

Excel Spreadsheet from Access. List of names changes as names are
I have a Excel sheet from Access. List of names and other info. Then I use MS Attendance Sheet and pick up names from the other Excel sheet. Problem. When a name is added in Access and the sheet is outputted to Excel it comes into the Attendance sheet, adding a row for the name but not the whole row so that the attendance info from the row below becomes info for the new name. How do I get Excel to add the whole row automatically? I'm using MS template "Weekly Class attendance Record" "Gordy w/Hi Expectations" wrote: > I have a Excel sheet from Access. List of n...

Seeing Text File Names when Saving Excel Files
I am new to Excel 2007. I am saving a new Excel file and I want to give it a similar name to an existing text file so that I know by the filename that the two files are related to the same project. However, when I "Save As" the Excel file in the selected directory, the only other files I see in that directory are the other Excel files. I don't see any of the other files that I know are in there (word, powerpoint, pdf, etc.). In prior versions of Excel, I have been able to see all of the files in a destination directory....is that still possible with the 2007 version of ...

Get names of closed workbooks?
I'm a relative newbie at VB but I've managed to cobble together bits of code gleaned from this NG which enables me to extract selected data from a group of closed workbooks located in a particular folder ( located at "C:\Temp\Survey) and then save it to another workbook called Update.xls, in worksheet called Get Data. This is based on two macros - the most significant of which is based on a development originally done by Nikos Yannacopoulos. I want to write an extra couple of lines that will get the name of each closed workbook in the folder. Here is the code for both macros. the...

How to refer to tab names?
Hi all, I have around 50 tabs (worksheets) to deal with in a workbook. I have to come up with a summary in another worksheet that lists down the tab names and the total items in each tab. So, could anyone please help me figure out on how: 1. to refer to each of the tab names (i.e Food, Place,and etc.) and place them in a cell in another worksheet? 2. to refer to each count of the items at the end of the list for each tab? How can I do it without having to key in them individually? TQ. Use INDIRECT() as below. 'the below will refer to cell B10 of tab name specif...

HOW
PLEASE help me with this. Under Windows XP and Office 2003, for my email program I swiched from OE to Outlook and importing my address(contacts) by dragging one by one between the Windows Address Book and the Outlook Contacts within individual folders. Everything was alright except the names in non-English character.... all the non_English names was not displayed in its language, rather in ????. My OS and Office language setting is set to Korean as a default language and I didn't have any problem with any kind of Office(incl OE) to use both English and Korean. I have spent probably...

Accessing Datareader Columns using column names
Hi, I recently wrote some code and used column names to access values in the datareader, like .... string _firstName = myDr["FirstName"].ToString(); My supervisor instructed me to use index instead, like... string _firstName = myDr.getString(0); I wouldn't mind using index but whats the big deal? I can't understand why is one better than the other? Please help... "mavrick_101" <mavrick101@discussions.microsoft.com> wrote in message news:95C53499-1B9A-4F0D-9965-477FD8D2F2FA@microsoft.com... > Hi, > > I recently wrote so...

Some names not appearing in Gal from Outlook 2003
Using outlook 2003 some names do not appear in global address book Using outlook xp they do. We have a new exchange server does something have to be re-homed. Cheers Keith the people using Outlook 2003 are most likely running in "cached mode" and the OAB has not yet updated... "KJ" <KJ@discussions.microsoft.com> wrote in message news:383AB0F5-3F95-44AE-AE3F-A4B62C87F6D3@microsoft.com... > Using outlook 2003 some names do not appear in global address book > Using outlook xp they do. > > We have a new exchange server does something have to be re-homed. &...

Referring to cells by range names #6
yes, correct on the two spaces vs one. one is sufficient. however, i still cannot make the formula work without indirect an using quotes for the range names. i have enjoyed the help and the spirited discussion -- rllane4 ----------------------------------------------------------------------- rllane47's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1647 View this thread: http://www.excelforum.com/showthread.php?threadid=27830 Shot in the dark. This may be a version issue. Try: <Tools> <Options> <Calculation> tab, And check "Accept Labe...

How do I not specify the sheet when defining names?
Hi, I know there's a way to do this, but I can't remember. What I want to do is name a range without specifying the sheet so that any formula on any given sheet knows that name refers to the range on that same sheet. I thought it was ~![cell], but that doesn't seem to work and I can't find a reference to it anywhere. Thanks, Graham Graham If you set the Refers To value to =!$A$1 the name will refer to cell A1 on whichever sheet is active. Note the ! Also note: your name won't show up in the name box. Gord Dibben XL2002 On Thu, 6 Nov 2003 16:19:13 -0800, &quo...

Formating Names
I have an example where the First and Last Name of my contacts are in a single column. I would like the first and last name in separate columns ie: John in Column A and Brown in column B any help would be appreciated. Richard It may as simple as Data>Text to Columns>De-limited by>Space. Then again, if you have cells with more than just two name, you may require something else. I would recommend Chip Pearson's site. http://www.cpearson.com/excel/FirstLast.htm Gord Dibben MS Excel MVP On Sun, 9 Jul 2006 12:14:05 -0400, "Richard Mahan" <dmachen35@co...

convert capitalized text to small text (with Capitalized names an
Have a database of names and addresses in an excell spreadsheet and want to convert it to small letters. =proper(a1) "trailboss2" wrote: > Have a database of names and addresses in an excell spreadsheet and want to > convert it to small letters. Hi See this webpages http://www.mvps.org/dmcritchie/excel/proper.htm Or http://www.cpearson.com/excel/case.htm Here are a few macro's for changing text cells in the selection Sub Uppercase_macro() Dim selectie As Range Dim cel As Range On Error Resume Next Set selectie = Range(ActiveCell.Address & "," & ...

Linking First & Last Names in a table to a Full Name field in anot
Hi. We have been using Access for a project database for years. However, we just added our customer information (formerly in ACT) to Access. Now I have two fields in our contact table (First Name, Last Name) but only one field in the existing project table (Full Name). I would like to link the customer to his active projects in our existing project table. How do I link these so that when I view a contact I will see a list of his projects? I have created a query that combines the First Name and Last Name but then I am not sure what to do with that query field. Thanks, St...