Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.newusers
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default How do I correct a range 'reference'?

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



  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 13
Default How do I correct a range 'reference'?

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" wrote in message
...
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





  #4   Report Post  
Posted to microsoft.public.excel.newusers
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default How do I correct a range 'reference'?

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" wrote in message
...
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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reference Data Range based on cell contents PCLIVE Charts and Charting in Excel 0 February 27th 06 04:01 PM
Reference range of cells and perform subtraction or nothing depending on contents bob-hamilton Excel Worksheet Functions 3 February 5th 06 05:24 PM
Using a NAME as a reference in another range cdavidson Excel Discussion (Misc queries) 1 October 17th 05 04:31 PM
Using a formula to create named range reference [email protected] Excel Worksheet Functions 4 June 29th 05 08:03 PM
How to dynamically reference a dynamic named range paris3 Excel Worksheet Functions 4 June 24th 05 01:22 AM


All times are GMT +1. The time now is 08:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"