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.
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.
"The true value of a human being is determined primarily by the measure and the sense in which he has attained liberation from the self." -Albert Einsetin