# [mso] Re: Excel question?

• From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
• To: <mso@xxxxxxxxxxxxx>
• Date: Wed, 6 Jun 2007 23:10:20 +1000

You can make use of anchors to let one formula handle all rows and all
columns.  Envisage:

Column A has dates.  Ignore for this.
Column B has types of purchase - e.g. Gas
Column C has the amounts of the purchases
Columns D to Q are set aside for your different categories

A1 is "Date"
B1 is "Purchase"
C1 is "\$"    - these three are just prettification

D1 has "Gas"
E1 has "Electricity"
F1 has "Petrol"
etc
(Sorry, couldn't resist - Gas is what I use in my stove; Petrol is what I
put in my car.)

Now D2 to Q99 are to be used to grab the individual purchase amounts, and
you'll total them in row 100 or whatever.  So D2 becomes:

=IF(\$B2=D\$1,\$C2,"")

and this can be copied down and across.  It matches the value from B in this
row with the heading in row 1 of this column and grabs the amount from
column C if there is a match, or leaves the cell blank if not.

Regards, Dave S

----- Original Message -----
From: <cjb4622@xxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Wednesday, June 06, 2007 12:57 AM
Subject: [mso] Excel question?

> Hey everyone,
>
> Â
>
> I am trying to create a formula for my businesses budget in excel.Â
>
> Â
>
> ---Basically,Â I would like toÂ sum everyÂ â?ogasâ?? expense in column
> â?oDâ??Â (So for every cell that has the word "gas"Â (D14 =Â Gas), the
> formula would pick up the number relating to that gas expense in column EÂ
> (cell E14 = \$35.00).Â From there I would like to be able to sum all of
> those cells, and get that total in a cell in column â?oFâ??, row 1.
>
> Â
>
> Can anyone help?
>
>
>
>
>
> PS - Are we allowed to have attachments when asking our questions?Â I know
> that it would probably be easier to have something to look at.....
>
> ________________________________________________________________________
> AOL now offers free email to everyone.  Find out more about what's free
> from AOL at AOL.com.
>
> *************************************************************
> You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, visit the group's homepage and use the
> dropdown menu at the top.  This will allow you to unsubscribe your email
> address or change your email settings to digest or vacation (no mail).
> http://www.freelists.org/webpage/mso
>
> To be able to share files with the group, you must join our Yahoo sister
> group.  This group will not allow for posting of emails, but will allow
> you to join and share problem files, templates, etc.:
> http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for
> FILE SHARING ONLY.
>
> If you are using Outlook and you see a lot of unnecessary code in your
> email messages, read these instructions that explain why and how to fix
> it:
> http://personal-computer-tutor.com/abc3/v28/greg28.htm
> *************************************************************

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************