How do I correct a range 'reference'?

Using Excel XP.
=====================
I've had a frustrating time with DSUM on a workbook I'm building from 
someone else's model - I keep getting #VALUE! errors.  After lots of looking 
"underneath the hood" I think I see the problem, but don't know how to fix 
it.

One of the formulas is:  =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3)

The problem comes up with "Revenue".  When I use the Evaluate Formula tool, 
it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue 
Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again and 
get the #VALUE! error.

When I took a hard lok at this, I think the problem is that the worksheet 
('Revenue Projects') refered to by the database (Revenue), has the WRONG 
range of cells. It should be $A$1:$O$19.

How do I change this "inside" Excel?  (This is the only thing I can find 
that might be causing this error.)

-- 
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501 (c)(3) conservation non-profit organization 


0
Mike_Webb (49)
8/2/2006 8:32:26 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
289 Views

Similar Articles

[PageSpeed] 53

If the menus are similar to XL2000 - try clicking Insert/Names/Define, and 
select Revenue from the list.  Click in the RefersTo Box and hit F2 to enter 
edit mode.  Then change the range reference.

"Mike Webb" wrote:

> Using Excel XP.
> =====================
> I've had a frustrating time with DSUM on a workbook I'm building from 
> someone else's model - I keep getting #VALUE! errors.  After lots of looking 
> "underneath the hood" I think I see the problem, but don't know how to fix 
> it.
> 
> One of the formulas is:  =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3)
> 
> The problem comes up with "Revenue".  When I use the Evaluate Formula tool, 
> it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue 
> Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again and 
> get the #VALUE! error.
> 
> When I took a hard lok at this, I think the problem is that the worksheet 
> ('Revenue Projects') refered to by the database (Revenue), has the WRONG 
> range of cells. It should be $A$1:$O$19.
> 
> How do I change this "inside" Excel?  (This is the only thing I can find 
> that might be causing this error.)
> 
> -- 
> Mike Webb
> Platte River Whooping Crane Maintenance Trust, Inc.
> a 501 (c)(3) conservation non-profit organization 
> 
> 
> 
0
jmb (270)
8/2/2006 11:31:01 PM
Tried that - but no change.  However, I made one change that seemed to do 
the trick. Rows 1 and 2 are empty so I changed the range reference to start 
with A3 vice A1.  Went back to the worksheet with the DSUM errors and almost 
all are gone!  I'll do some digging to see why they didn't all get fixed, 
but I feel I'm closer.

Mike

"JMB" <JMB@discussions.microsoft.com> wrote in message 
news:4C48894D-B1E7-4C6A-B411-FD093C5A0BF9@microsoft.com...
> If the menus are similar to XL2000 - try clicking Insert/Names/Define, and
> select Revenue from the list.  Click in the RefersTo Box and hit F2 to 
> enter
> edit mode.  Then change the range reference.
>
> "Mike Webb" wrote:
>
>> Using Excel XP.
>> =====================
>> I've had a frustrating time with DSUM on a workbook I'm building from
>> someone else's model - I keep getting #VALUE! errors.  After lots of 
>> looking
>> "underneath the hood" I think I see the problem, but don't know how to 
>> fix
>> it.
>>
>> One of the formulas is:  =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3)
>>
>> The problem comes up with "Revenue".  When I use the Evaluate Formula 
>> tool,
>> it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue
>> Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again 
>> and
>> get the #VALUE! error.
>>
>> When I took a hard lok at this, I think the problem is that the worksheet
>> ('Revenue Projects') refered to by the database (Revenue), has the WRONG
>> range of cells. It should be $A$1:$O$19.
>>
>> How do I change this "inside" Excel?  (This is the only thing I can find
>> that might be causing this error.)
>>
>> -- 
>> Mike Webb
>> Platte River Whooping Crane Maintenance Trust, Inc.
>> a 501 (c)(3) conservation non-profit organization
>>
>>
>> 


0
Mike_Webb (49)
8/3/2006 2:02:57 PM
First, my aplogies for untimely response.  Been at a friends since Thursday 
and his internet would not connect to MS's newsgroups.

I've not used DSUM extensively, but it seemed fine with having empty rows in 
the middle of the data.

Is the named range a dynamic named range?  Empty rows/columns would cause 
problems w/ these types of range references.

See 
http://www.cpearson.com/excel/named.htm#Dynamic
for details and a downloadable example.


"Mike Webb" wrote:

> Tried that - but no change.  However, I made one change that seemed to do 
> the trick. Rows 1 and 2 are empty so I changed the range reference to start 
> with A3 vice A1.  Went back to the worksheet with the DSUM errors and almost 
> all are gone!  I'll do some digging to see why they didn't all get fixed, 
> but I feel I'm closer.
> 
> Mike
> 
> "JMB" <JMB@discussions.microsoft.com> wrote in message 
> news:4C48894D-B1E7-4C6A-B411-FD093C5A0BF9@microsoft.com...
> > If the menus are similar to XL2000 - try clicking Insert/Names/Define, and
> > select Revenue from the list.  Click in the RefersTo Box and hit F2 to 
> > enter
> > edit mode.  Then change the range reference.
> >
> > "Mike Webb" wrote:
> >
> >> Using Excel XP.
> >> =====================
> >> I've had a frustrating time with DSUM on a workbook I'm building from
> >> someone else's model - I keep getting #VALUE! errors.  After lots of 
> >> looking
> >> "underneath the hood" I think I see the problem, but don't know how to 
> >> fix
> >> it.
> >>
> >> One of the formulas is:  =DSUM(Revenue,'Revenue Projects'!$D$3,B1:B3)
> >>
> >> The problem comes up with "Revenue".  When I use the Evaluate Formula 
> >> tool,
> >> it italicizes "Revenue", and when I click on Evaluate, I see 'Revenue
> >> Projects'$A$1:$J$14 in it's place - italicized. I click Evaluate again 
> >> and
> >> get the #VALUE! error.
> >>
> >> When I took a hard lok at this, I think the problem is that the worksheet
> >> ('Revenue Projects') refered to by the database (Revenue), has the WRONG
> >> range of cells. It should be $A$1:$O$19.
> >>
> >> How do I change this "inside" Excel?  (This is the only thing I can find
> >> that might be causing this error.)
> >>
> >> -- 
> >> Mike Webb
> >> Platte River Whooping Crane Maintenance Trust, Inc.
> >> a 501 (c)(3) conservation non-profit organization
> >>
> >>
> >> 
> 
> 
> 
0
jmb (270)
8/6/2006 11:53:01 PM
Reply:

Similar Artilces:

Range names are displaying in big blue letters on sheet
Does anyone know how to turn off this display? It seems so basic, but I can't find it. The range names will appear if you set the zoom level below 40%. I don't know of any way to turn this feature off. Katie wrote: > Does anyone know how to turn off this display? > It seems so basic, but I can't find it. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Katie Are you sure it is range names and not page numbers? Try View>Normal Other than that, I'm at a loss without more detail. What do the range names look like other...

Watch this corrective update that came from Microsoft Corporation
--onkdsuysvvng Content-Type: multipart/related; boundary="bfbhdbklwehwbiu"; type="multipart/alternative" --bfbhdbklwehwbiu Content-Type: multipart/alternative; boundary="ampkwllbdcvecaddz" --ampkwllbdcvecaddz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install n...

Increasing range for autofilter?
Is there anyway to increase the range of the autofilter without havin to use advanced filter? I have about 3,000 lines in my spreadsheet s far and its only showing about the first 1,000 of the category chosen If I must use advanced filter is there a link I can go to that wil show me. I am having trouble understanding the guide in Excel -- Message posted from http://www.ExcelForum.com Hi Have a look here: http://www.contextures.com/xlautofilter02.html#Limits -- Andy. "jkb724 >" <<jkb724.19ui1p@excelforum-nospam.com> wrote in message news:jkb724.19ui1p@excelforum-...

Query Based on Time Rane and Date Range
How can I create a query that can searh for a time range Example between 6pm and 11pm base on a date ragnge example 6/1/09 - 9/1/09 I want the query to return everthing between 6pm and 11pm from June 1st to stepember 1st. Is this possible? Thanks Little Penny Hi Penny, SELECT fieldlist FROM [tablename] WHERE DateValue([date_field]) BETWEEN #6/1/2009# AND #9/1/2009# AND TimeValue([date_field]) BETWEEN #18:00# AND #23:00# Warm Regards, Crystal remote programming and training http://MSAccessGurus.com free video tutorials http://www.YouTube.c...

Taste corrective patch
--zncknrruviimfhh Content-Type: multipart/related; boundary="nqkfxcjees"; type="multipart/alternative" --nqkfxcjees Content-Type: multipart/alternative; boundary="iqpjepqzbj" --iqpjepqzbj Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "September 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect your...

revenue ranges in scatterplot graph
Hello, Does anyone know how to put revenue ranges for the y-axis in scatterplot graph rather than just a typical revenue scale? Any hel would be muchly appreciated. Thanks. T -- sfish ----------------------------------------------------------------------- sfishy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2879 View this thread: http://www.excelforum.com/showthread.php?threadid=48486 sorry, i meant the x-axis with revenue ranges on a scatter......thanks! -- sfishy ------------------------------------------------------------------------ sfishy's P...

Payroll corrections after Payroll Year is closed
I have a client who closed their payroll year and issued W2s. Now, they've realized that because the last payroll of 2008 was printed on 12/31 but not distributed until January 2nd, it needs to fall into 2009 for wage reporting purposes. My first thought to correct this was to void the checks from the last payroll and re-issue them but I've discovered GP won't let you do this once the payroll year has been closed. Has anyone ever run into this scenario and if so, how was it corrected? Thx, Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com blog: www....

Emails correct sent?
Hallo, Sometimes when i am forwarding a mail to somebody the mail in the map "messages send" is seen as send to myself? So, i am not sure if the person has really received the mail. Regards Huib That is an indication that a problem occurred during the send(process interrupted by user, the ISP, or an AV client are a few of the causes) - The message may have been sent and received(only the recipient can verify if delivered). - WLM creates that message in that form just in case you need to recover the contents and recreate the message -- ...winston ms-mv...

Import auto correct to Word 2007
I am a lousy typist. I am switching from Word 2003 to Word 2007. Is there a simple way a non tech guy like me can export my auto correct setting to Word 2007 without risking screwing up everything? The simpler the solution, the more grateful I will be! Thanks. Hello, On 2010.01.21 17:38, in article 9F0A4A5F-9136-4F3C-8079-520E1A1D93E5@microsoft.com, "ShivN" <ShivN@discussions.microsoft.com> wrote: > I am a lousy typist. I am switching from Word 2003 to Word 2007. Is there a > simple way a non tech guy like me can export my auto correct setting to Word...

correction
What is process "tkupd32.exe"? Safe to end process? It's pegging my processor at 100% utilization. Thx. Looks like it is something to do with Groupshield, and there is a hotfix: http://ktp.e-isa.com/Downloads/McAfee/Patches/GroupShield/Hotfix7.txt -- Mark Fugatt Microsoft Exchange MVP www.exchangetrainer.com www.msexchange.org "jallen" <anonymous@discussions.microsoft.com> wrote in message news:08e501c3c963$a59beaf0$a401280a@phx.gbl... > What is process "tkupd32.exe"? Safe to end process? It's > pegging my processor at 100% utilization. ...

Copy and paste two named ranges together.
I am attempting to copy and combine two named ranges of equal size into a blank spreadsheet. How does one copy the first named range and concurrently seperate each copied row with a blank row into the blank spreadsheet, and copy the second range and paste those copied records into the blank rows? I am looking for a systematic way of doing this consolidation. There can be hundreds of rows of data. Also the named ranges can very in size month to month. The end result is to combine two ranges for a journal entry upload into a financial accounting entry. The two arrays represent the ...

Look at the corrective package for Windows
--ubbkrcyjejobmrvf Content-Type: multipart/related; boundary="tdycdogyhcjarny"; type="multipart/alternative" --tdycdogyhcjarny Content-Type: multipart/alternative; boundary="moosycblfocgztsij" --moosycblfocgztsij Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabiliti...

Generic adjacent cell reference
This should be simple but I am not finding it in the docs... I have a formula which I want should always refer to the adjacent cell. For instance The data is in A1 and I put the formula in A2 as =getURL(a1). So I need this to be completely generic so that I can paste it into hundreds of cells and always have it refer to the cell directly to the left regardless as to which column or row it is in. Thanks Paul Paul, For the cell directly to the left, use this in the cell, replacing the B1 with the actual cell address =OFFSET(B1,0,-1) For the cell above: =OFFSET(A2,-1,0) HTH, Bernie MS E...

Lookup reference help
Hi Folks, I have a sheet, where the contents of cell A1 is a result of other criteria being met. For the purpose of this example, the result in A1 is "Oral Health" In cells B1 thru to W1 are column headers, one of which will match the result in A1 (Oral Health is E1, Population Health is K1, etc). Under each of these header cells are seven or eight cells with a range of vehicles (B2-B9 thru W2-W9 respectively) What I am trying to do is create a custom list of vehicles so that if A1 = Oral Health, and the Oral Health header is E1, I'd like the contents of E2 - E9 ...

Select A Random Cell From A Defined Range No Dups
Select A Random Cell From A Defined Range No Dups I want to select a ramdom Bull in a rodeo and assign it to a rando rider. If you can help thanks billvogt@juno.co -- mose ----------------------------------------------------------------------- moses's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594 View this thread: http://www.excelforum.com/showthread.php?threadid=27429 Hi if your data is in A1:A10 try =INDEX(A1:A10,RANDBETWEEN(1,10)) -- Regards Frank Kabel Frankfurt, Germany moses wrote: > Select A Random Cell From A Defined Range No Dups > I wa...

site to site free/busy data not replicating correctly
We have two Exchange 5.5 servers. A and B connected through a 256k frame line. Site A can see free/busy information for both sites. B can only see free/busy information on it's site, and not A. Server A crashed. Free/Busy information was working before the crash. OS was reinstall as well as Exchange services. We recreated the replication connectors from A during the reinstall. Now, site A has two Free/busy folder and site B has only one. Affinity is set for both servers. Now, Site A has two free/busy folders, one for local site, and one for remote, and site B has only it's own free/bu...

Auto Correct
I am using Word 2007. Whenever I type HTE, the auto correct feature changes it into The. But I want to retain what I type (HTE) without peremanetly removing it from auto correct options. How do I do it? Any help will be highly appreciated. Thanks in advance. -RM Press CTRL+Z immediately after it makes the change to revert to what you typed, or delete the autocorrect entry. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web...

How do you get Publisher to print transparent areas correctly?
I have boxes with a gradient that goes from one color to transparent. When I print on my desktop printer, the transparent areas print as white. Is there any way around this problem? Thanks so much! Your printer prints white? "Dan Pongett" <Dan Pongett@discussions.microsoft.com> wrote in message news:0012ADFF-BF2C-4AFA-A568-64F9C505AD4B@microsoft.com... |I have boxes with a gradient that goes from one color to transparent. When I | print on my desktop printer, the transparent areas print as white. Is there | any way around this problem? | | Thanks so much! The pape...

ClearContents method on a passed range
I want to create a range of results in one worksheet based on a range of input values in another worksheet in the same workbook. I've 'named' the input and result ranges in Excel but am unable to clear the result range using the following code called from Excel with '=Test1(results)' speciified in a cell: Public Function Test1(RangeResult As Range) RangeResult.ClearContents End Function The code runs o.k. but the range isn't cleared and zero is returned. I thought I was getting the hang of VBA but this has stumped me. bryan wrote... >I want to create a range o...

two arrays correction
Hi, Sorry the frist one fell apart when it sent. I am trying again. In col A througn F there are numbers about 150 rows down. In G1 through L1 there are numbers and these will change from time to time. G2 through L2 and down I want a formula that will find the numbers that are in A through F and put YES in the corresponding cell under G to L. So any cell in A:F that has one of the numbers in G:L the formula will put yes in the corresponding cell under G:L if the numbers is in G1:L1. A B C D E F G H I J 2 8 11 50 4...

AutoText/Correct
I need to add an autotext or autocorrect to many machines without replaced their current list or backing up what they have and adding just the one new entry. any ideas? This is for Word 2003. Thanks. Autotext would be the simpler option. Save it in a blank template and provide the template as an add-in to the users. Autotexts in add-ins take precedence over those in the normal template of the same name. For autocorrect, you would need to provide a macro solution for the users to add the entry to their systems. Formatted autocorrect entries are stored in the users' norma...

What is error 623? How can I correct it?
When I close Outlook I get the message,"error 623:the system could not find the phone book entry for this address." What can I do to correcct this? ...

Worksheet relative reference
Hi, I have created in a workbook a worksheet containing formulas with relative and absolute references to other worksheets in the same workbook. As I have a large number of workbooks with exactly the same structure (one workbook for each operating company), I tried to copy the worksheet with the formulas to each of the workbooks. However, the formulas in the copied worksheets have absolute references to the initial workbook. Is there any way to specify that the formulas should have relative and not absolute worksheet references? Thnak you, E.g. original formula: =I...

how to update member value correct
Hello! I'm reading a book called programming Visual C++. The book says "Finally, you need to update the trackbar's m_nTrackbar1 data member when the user clicks OK. Your natural instinct would be to put this code in the OnOk button handler. You would have a problem, however, if a data exchange validation error occurred involving any other control in the dialog. Your handler would set m_nTrackbar1 even though the user might choose to cancel the dialog. To avoid this problem, add your code in the DoDataExchange function as shown below." if (pDX->m_bSaveAndValidate...

Copy a Textbox to a range on different worksheet
Can someone please assist me in how I would go about copying a textbox value to a specific range in same workbook but different sheet? Thanks in advance LRay This is the syntax... Sheets("Sheet1").Range("C4").Value = _ Sheets("Sheet2").TextBox1.Value -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "LRay67" wrote: > Can someone please assist me in how I would go about copying a textbox value > to a specific range in same workbook but different sheet? ...