Help me "dissect" this function (Excel 2002 [XP])

[Mike Webb]

=DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue
Projects'!$D$2,C1:C3)

I'm trying to adapt a budget worksheet from another non-profit for our use.
I don't know who the author is and I've had it a couple years, so trying to
go that route for help is a non-starter.

I don't understand why they have Membership twice; I guess one is for the
worksheet and the other is the column header, but I don't get it as $C$49
points to a a column label named "Revenue". and C1:C3 point to 2 blank cells
and one with the column label "2002". Somehow, the orginal formula was
pulling $10,000 frm this worksheet. I can't figure out how. In the 2nd
half of the function I don't have a worksheet named Revenue, but do ahve one
named Revenue Projects - but no column lable of Revenue. Anyway $D$2 is a
column label called "Monthly Giving" (don't know why I need that in this
formula), and C1:C3 are a blank line, column header, and some text. Now the
number I CAN pull frm MY formula - $600 - can be found in cell D4. In case
it's useful, cell A4 has trhe following text: "Monthly Giving".

I can provide much more info on the workbook, but I limited myself to the
immediate problem. (P.S. What do some formulas use $ signs in front of the
cell column and row ID's and others don't? Couldn't find it in the Help
menu.)

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

[kassie]

I think you will have a much clearer understanding if you hit help in Excel,
and type in DSUM. It not only explains the syntax, but also gives a very
good example of how to use inter alia DSUM.

As for the last part of your question, the $ sign is used to make a cell
reference absolute, as opposed to relative. Iow, if you enter the following
formula in B1 - =A1, and you copy it to C1, it becomes =B1. If you copy it
to B2, it becomes A2. However, $A1, if copied to C1, will still read =$A1.
If you copy this to B2, it will read =$A2.
=$A$1 will remain as =$A$1, no matter where you copy it to.
A$1, when copied to B1, will read =B$1, but if copied to B2, will read =A$1

"Mike Webb" wrote:

> =DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue
> Projects'!$D$2,C1:C3)
>
> I'm trying to adapt a budget worksheet from another non-profit for our use.
> I don't know who the author is and I've had it a couple years, so trying to
> go that route for help is a non-starter.
>
> I don't understand why they have Membership twice; I guess one is for the
> worksheet and the other is the column header, but I don't get it as $C$49
> points to a a column label named "Revenue". and C1:C3 point to 2 blank cells
> and one with the column label "2002". Somehow, the orginal formula was
> pulling $10,000 frm this worksheet. I can't figure out how. In the 2nd
> half of the function I don't have a worksheet named Revenue, but do ahve one
> named Revenue Projects - but no column lable of Revenue. Anyway $D$2 is a
> column label called "Monthly Giving" (don't know why I need that in this
> formula), and C1:C3 are a blank line, column header, and some text. Now the
> number I CAN pull frm MY formula - $600 - can be found in cell D4. In case
> it's useful, cell A4 has trhe following text: "Monthly Giving".
>
> I can provide much more info on the workbook, but I limited myself to the
> immediate problem. (P.S. What do some formulas use $ signs in front of the
> cell column and row ID's and others don't? Couldn't find it in the Help
> menu.)
>
> --
> Mike Webb
> Platte River Whooping Crane Maintenance Trust, Inc.
> a 501 (c)(3) conservation non-profit organization
>
>
>

[Mike Webb]

Thanks for the explanation of the $ sign - it helps a lot.

However, I'm still unsure about DSUM.

I just enterred DSUM into Help and got the definition -
Database-Field-Criteria. Using this as the "decoder", the 1st "Membership"
would be the Database (worksheet), "Membership!$C$49" is the Field, and
"C1:C3" is the criteria - correct?
If the Field is the column used, then I don't get the "Membership" half.
That word is used in only one cell on the worksheet - A1. As I mentioned
below, $C$49 refers to a column label named "Revenue" (which is not used on
the original worksheet that has the cell with this formula). And the
criteria makes no sense when I look at what's in those cells. I tried
hitting "Example" in Help for DSUM, but their example:
=DSUM(A4:E10,"Profit",A1:F2) The total profit from apple trees with a height
between 10 and 16. (75)

doesn't help me.

Can someone give me a bit more to go on?

Mike

"kassie" <[email protected]> wrote in message
news:[email protected]...
>I think you will have a much clearer understanding if you hit help in
>Excel,
> and type in DSUM. It not only explains the syntax, but also gives a very
> good example of how to use inter alia DSUM.
>
> As for the last part of your question, the $ sign is used to make a cell
> reference absolute, as opposed to relative. Iow, if you enter the
> following
> formula in B1 - =A1, and you copy it to C1, it becomes =B1. If you copy
> it
> to B2, it becomes A2. However, $A1, if copied to C1, will still read
> =$A1.
> If you copy this to B2, it will read =$A2.
> =$A$1 will remain as =$A$1, no matter where you copy it to.
> A$1, when copied to B1, will read =B$1, but if copied to B2, will read
> =A$1
>
> "Mike Webb" wrote:
>
>> =DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue
>> Projects'!$D$2,C1:C3)
>>
>> I'm trying to adapt a budget worksheet from another non-profit for our
>> use.
>> I don't know who the author is and I've had it a couple years, so trying
>> to
>> go that route for help is a non-starter.
>>
>> I don't understand why they have Membership twice; I guess one is for the
>> worksheet and the other is the column header, but I don't get it as $C$49
>> points to a a column label named "Revenue". and C1:C3 point to 2 blank
>> cells
>> and one with the column label "2002". Somehow, the orginal formula was
>> pulling $10,000 frm this worksheet. I can't figure out how. In the 2nd
>> half of the function I don't have a worksheet named Revenue, but do ahve
>> one
>> named Revenue Projects - but no column lable of Revenue. Anyway $D$2 is
>> a
>> column label called "Monthly Giving" (don't know why I need that in this
>> formula), and C1:C3 are a blank line, column header, and some text. Now
>> the
>> number I CAN pull frm MY formula - $600 - can be found in cell D4. In
>> case
>> it's useful, cell A4 has trhe following text: "Monthly Giving".
>>
>> I can provide much more info on the workbook, but I limited myself to the
>> immediate problem. (P.S. What do some formulas use $ signs in front of
>> the
>> cell column and row ID's and others don't? Couldn't find it in the Help
>> menu.)
>>
>> --
>> Mike Webb
>> Platte River Whooping Crane Maintenance Trust, Inc.
>> a 501 (c)(3) conservation non-profit organization
>>
>>
>>

[kassie]

Hi Mike

Glad I could help a litle bit. For help on the DSUM issue, Iwould really
need to examine your file. If you do not get any other responses to your
post, I would suggest you email me your file(s). I will then be in a much
better position to assist you. Mail me at [email protected], but
change the sjoe to hot

"Mike Webb" wrote:

> Thanks for the explanation of the $ sign - it helps a lot.
>
> However, I'm still unsure about DSUM.
>
> I just enterred DSUM into Help and got the definition -
> Database-Field-Criteria. Using this as the "decoder", the 1st "Membership"
> would be the Database (worksheet), "Membership!$C$49" is the Field, and
> "C1:C3" is the criteria - correct?
> If the Field is the column used, then I don't get the "Membership" half.
> That word is used in only one cell on the worksheet - A1. As I mentioned
> below, $C$49 refers to a column label named "Revenue" (which is not used on
> the original worksheet that has the cell with this formula). And the
> criteria makes no sense when I look at what's in those cells. I tried
> hitting "Example" in Help for DSUM, but their example:
> =DSUM(A4:E10,"Profit",A1:F2) The total profit from apple trees with a height
> between 10 and 16. (75)
>
> doesn't help me.
>
> Can someone give me a bit more to go on?
>
> Mike
>
> "kassie" <[email protected]> wrote in message
> news:[email protected]...
> >I think you will have a much clearer understanding if you hit help in
> >Excel,
> > and type in DSUM. It not only explains the syntax, but also gives a very
> > good example of how to use inter alia DSUM.
> >
> > As for the last part of your question, the $ sign is used to make a cell
> > reference absolute, as opposed to relative. Iow, if you enter the
> > following
> > formula in B1 - =A1, and you copy it to C1, it becomes =B1. If you copy
> > it
> > to B2, it becomes A2. However, $A1, if copied to C1, will still read
> > =$A1.
> > If you copy this to B2, it will read =$A2.
> > =$A$1 will remain as =$A$1, no matter where you copy it to.
> > A$1, when copied to B1, will read =B$1, but if copied to B2, will read
> > =A$1
> >
> > "Mike Webb" wrote:
> >
> >> =DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue
> >> Projects'!$D$2,C1:C3)
> >>
> >> I'm trying to adapt a budget worksheet from another non-profit for our
> >> use.
> >> I don't know who the author is and I've had it a couple years, so trying
> >> to
> >> go that route for help is a non-starter.
> >>
> >> I don't understand why they have Membership twice; I guess one is for the
> >> worksheet and the other is the column header, but I don't get it as $C$49
> >> points to a a column label named "Revenue". and C1:C3 point to 2 blank
> >> cells
> >> and one with the column label "2002". Somehow, the orginal formula was
> >> pulling $10,000 frm this worksheet. I can't figure out how. In the 2nd
> >> half of the function I don't have a worksheet named Revenue, but do ahve
> >> one
> >> named Revenue Projects - but no column lable of Revenue. Anyway $D$2 is
> >> a
> >> column label called "Monthly Giving" (don't know why I need that in this
> >> formula), and C1:C3 are a blank line, column header, and some text. Now
> >> the
> >> number I CAN pull frm MY formula - $600 - can be found in cell D4. In
> >> case
> >> it's useful, cell A4 has trhe following text: "Monthly Giving".
> >>
> >> I can provide much more info on the workbook, but I limited myself to the
> >> immediate problem. (P.S. What do some formulas use $ signs in front of
> >> the
> >> cell column and row ID's and others don't? Couldn't find it in the Help
> >> menu.)
> >>
> >> --
> >> Mike Webb
> >> Platte River Whooping Crane Maintenance Trust, Inc.
> >> a 501 (c)(3) conservation non-profit organization
> >>
> >>
> >>
>
>
>

[Mike Webb]

Wil do, thanks.
"kassie" <[email protected]> wrote in message
news:[email protected]...
> Hi Mike
>
> Glad I could help a litle bit. For help on the DSUM issue, Iwould really
> need to examine your file. If you do not get any other responses to your
> post, I would suggest you email me your file(s). I will then be in a much
> better position to assist you. Mail me at [email protected],
> but
> change the sjoe to hot
>
> "Mike Webb" wrote:
>
>> Thanks for the explanation of the $ sign - it helps a lot.
>>
>> However, I'm still unsure about DSUM.
>>
>> I just enterred DSUM into Help and got the definition -
>> Database-Field-Criteria. Using this as the "decoder", the 1st
>> "Membership"
>> would be the Database (worksheet), "Membership!$C$49" is the Field, and
>> "C1:C3" is the criteria - correct?
>> If the Field is the column used, then I don't get the "Membership" half.
>> That word is used in only one cell on the worksheet - A1. As I mentioned
>> below, $C$49 refers to a column label named "Revenue" (which is not used
>> on
>> the original worksheet that has the cell with this formula). And the
>> criteria makes no sense when I look at what's in those cells. I tried
>> hitting "Example" in Help for DSUM, but their example:
>> =DSUM(A4:E10,"Profit",A1:F2) The total profit from apple trees with a
>> height
>> between 10 and 16. (75)
>>
>> doesn't help me.
>>
>> Can someone give me a bit more to go on?
>>
>> Mike
>>
>> "kassie" <[email protected]> wrote in message
>> news:[email protected]...
>> >I think you will have a much clearer understanding if you hit help in
>> >Excel,
>> > and type in DSUM. It not only explains the syntax, but also gives a
>> > very
>> > good example of how to use inter alia DSUM.
>> >
>> > As for the last part of your question, the $ sign is used to make a
>> > cell
>> > reference absolute, as opposed to relative. Iow, if you enter the
>> > following
>> > formula in B1 - =A1, and you copy it to C1, it becomes =B1. If you
>> > copy
>> > it
>> > to B2, it becomes A2. However, $A1, if copied to C1, will still read
>> > =$A1.
>> > If you copy this to B2, it will read =$A2.
>> > =$A$1 will remain as =$A$1, no matter where you copy it to.
>> > A$1, when copied to B1, will read =B$1, but if copied to B2, will read
>> > =A$1
>> >
>> > "Mike Webb" wrote:
>> >
>> >> =DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue
>> >> Projects'!$D$2,C1:C3)
>> >>
>> >> I'm trying to adapt a budget worksheet from another non-profit for our
>> >> use.
>> >> I don't know who the author is and I've had it a couple years, so
>> >> trying
>> >> to
>> >> go that route for help is a non-starter.
>> >>
>> >> I don't understand why they have Membership twice; I guess one is for
>> >> the
>> >> worksheet and the other is the column header, but I don't get it as
>> >> $C$49
>> >> points to a a column label named "Revenue". and C1:C3 point to 2 blank
>> >> cells
>> >> and one with the column label "2002". Somehow, the orginal formula
>> >> was
>> >> pulling $10,000 frm this worksheet. I can't figure out how. In the
>> >> 2nd
>> >> half of the function I don't have a worksheet named Revenue, but do
>> >> ahve
>> >> one
>> >> named Revenue Projects - but no column lable of Revenue. Anyway $D$2
>> >> is
>> >> a
>> >> column label called "Monthly Giving" (don't know why I need that in
>> >> this
>> >> formula), and C1:C3 are a blank line, column header, and some text.
>> >> Now
>> >> the
>> >> number I CAN pull frm MY formula - $600 - can be found in cell D4. In
>> >> case
>> >> it's useful, cell A4 has trhe following text: "Monthly Giving".
>> >>
>> >> I can provide much more info on the workbook, but I limited myself to
>> >> the
>> >> immediate problem. (P.S. What do some formulas use $ signs in front
>> >> of
>> >> the
>> >> cell column and row ID's and others don't? Couldn't find it in the
>> >> Help
>> >> menu.)
>> >>
>> >> --
>> >> Mike Webb
>> >> Platte River Whooping Crane Maintenance Trust, Inc.
>> >> a 501 (c)(3) conservation non-profit organization
>> >>
>> >>
>> >>
>>
>>
>>