Office spreadsheet question for you lurkers

[Taem]

I asked this on some technical forums yesterday, but figure I might as well ask here also since a few of you know VB pretty well.

I have a budget sheet in Excel that my wife and I have used for a long time now. Each month has it's own worksheet and there is a seperate Finance worksheet for breaking down and keeping track of specific expenses, such as "Food". On the monthly worksheets, I made dropdown lists vertically of expenses (for each day of the week) to choose from followed by its numerical equavelant in the immediate cell to the right. The whole system works great and the sheet automatically balances the totals.

In the past, when we updated the Finance worksheet, we have had to manually find each instance of what we were looking for in the given month, then have the cell we wanted the information in add up the value of each of these instances. Very tedius.

The problem I'm having is I can't figure out how to automatically have the cell in the Finance sheet do this for me by, for example, adding up the column to the right if there is the word "Food" next to it. So far, the best I can come up with is " =COUNTIF(JAN!A1:JAN!X26,"Food") " but that only tells me how many "Food" entries there are and does not add up any variables in the column to the right. Everything I've researched on LOOKUP and FIND and SEARCH all require an dedicaed array where Column A has the name and Column B has the value, but for this problem, that simply cannot be done because there are columns for each day of the week.

The best idea I can come up with right now is a Macro, but I'm fairly rusty in VB. From what I remember, I need to declare a variable, then have it add to itself, i.e. N=N+CELL'X' . That should give me a total of all the cells, however I'm not sure what the VB commands are to do what I want in an Excel spreedsheet.

As always, any help or advice would be greatly appreciated.

[Frag]

The SUMIF command should do it. E.g.
=SUMIF(JAN!A1:JAN!x26,"Food",JAN!B1:JAN!x26) or something along those lines, I believe.

~FragB)

[Concillian]

I'm sure there's a function in Excel that will do what you need without using a macro, but I do not memorize the functions in Excel. Instead I know how to make macros, so it's easiest to help by just making a macro to do what you want to do.

I'm assuming you have something that looks like this"

Code:

Food   10.50
Toys   8.48
Gas  38.50
Food   42.00

and want to sum only the food ones. As I said, I'm sure that there's a function in excel that will do this, I just don't know what it is. My deal is learn how to make a macro and you can create anything you need without trying to figure out some bogus syntax... though you do have to figure out VBA, so that logic may have a flaw in it.

Code:

Sub Food_macro

dim i as integer
dim FoodTotal as single

[A1].select  'set this to the upper left cell

FoodTotal = 0
i=0

  do until ActiveCell.offset(i,0).value = ""
   if ActiveCell.Offset(i,0).value = "Food"
      FoodTotal = FoodTotal + Activecell.Offset(i,1).Value
   else
      
   end if
  loop
[G1].value = FoodTotal

end sub

If you want to get fancy, you set all your varaibles to sum at the same time

Code:

Sub Food_macro

dim i as integer
dim FoodTotal as single
dim GasTotal as single
dim ToysTotal as single
dim PotTotal as single

[A1].select  'set this to the upper left cell

FoodTotal = 0
i=0

  do until ActiveCell.offset(i,0).value = ""
   Select Case ActiveCell.Offset(i,0).value
      Case is = "Food"
         FoodTotal = FoodTotal + Activecell.Offset(i,1).value
      Case is = "Toys"
         ToysTotal = ToysTotal + Activecell.Offset(i,1).value
      Case is = "Gas"
         GasTotal = GasTotal + Activecell.Offset(i,1).value
      Case is = "Pot"
         PotTotal = ToysTotal + Activecell.Offset(i,1).value
   end Select
   i=i+1

   Loop

   [G1].value = FoodTotal
   [G2].value = ToysTotal
'  etc...

End Sub

If you need to change sheets, the syntax is
Sheets("Finance").select

It might be a good idea to put this into the macro so you don't go editing the wrong sheet inadvertently.

Also, the loop will continue until it finds a cell with nothing in it, so if you have a different ending condition (perhaps a cell that says "Total" or something) then replace the "" with "<unique value of the cell>". You can also use any other cell a fixed offset from it by using Activecell.Offset(i+x,y).

Someone else probably replied with a much simpler solution by now, but I'm gonna go ahead and post this anyway.

[Taem]

The SUMIF command should do it. E.g.
=SUMIF(JAN!A1:JAN!x26,"Food",JAN!B1:JAN!x26) or something along those lines, I believe.

~FragB)

Wow it worked! Thanks a million. I thought you'd have to tell the cell specifically where to retrieve the data, but I guess if you displace it by one column, it automatically does it. It's so simple that I didn't think it would work, lol.

Concillian is correct though that if you know how to use VB, you can create your own macros to do just about anything. I'd love to get back into that. Some of my fondest memories as a kid (this sounds so geeky as I write it out, but its true) were creating programs in Basic on my dads Atari ST. That was back when you had to give every line a line number, i.e. 001 Begin. Now with Excel and Macros, it reminds me of a sandboxed version of VB made specifically for the end user. Someday, time permitting, I'll get back into it.

[kandrathe]

Now with Excel and Macros, it reminds me of a sandboxed version of VB made specifically for the end user. Someday, time permitting, I'll get back into it.

Other than as a hobby, there may be little point. With graphical systems being so powerful now, the latest trend in object oriented "programming" looks more like VISIO diagrams than text/syntax based languages most of us are familiar with.

Something like; http://www.q-branch.com/technology.html