|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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:
[Quoted Text] > 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
>
>
>
|
|
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[ at ]discussions.microsoft.com> wrote in message
news:4C48894D-B1E7-4C6A-B411-FD093C5A0BF9[ at ]microsoft.com...
[Quoted Text] > 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
>>
>>
>>
|
|
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:
[Quoted Text] > 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[ at ]discussions.microsoft.com> wrote in message
> news:4C48894D-B1E7-4C6A-B411-FD093C5A0BF9[ at ]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
> >>
> >>
> >>
>
>
>
|
|
|