How do I correct a range 'reference'?

[Mike Webb]

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.)

 

[Guest]

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]

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

 

[Guest]

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.