formula or macro
 

Hi. I am trying to create a budget in excel. in the first row, i have the
year. in the second row, i have the months of the year. what i would like
to do is the following: I would like to enter a line item for the budget
(e.g., license fee). next to that, i would like to list the amount, followed
by the month and the year that it should appear in the budget.
e.g., license fee.....$10,000......July......2007

is there a command/macro that I can use that will take the dollar amount and
place it in the right cell in the budget that corresponds to the month and
year listed? my goal is to not have to manually move dollar amounts around
as timing changes during the budget development process.

Thanks for any suggestions.

formula or macro
 

How is this different than the other post you just submitted?

--
Don Guillett
SalesAid Software

"CMD" wrote in message
...
Hi. I am trying to create a budget in excel. in the first row, i have
the
year. in the second row, i have the months of the year. what i would
like
to do is the following: I would like to enter a line item for the budget
(e.g., license fee). next to that, i would like to list the amount,
followed
by the month and the year that it should appear in the budget.
e.g., license fee.....$10,000......July......2007

is there a command/macro that I can use that will take the dollar amount
and
place it in the right cell in the budget that corresponds to the month and
year listed? my goal is to not have to manually move dollar amounts
around
as timing changes during the budget development process.

Thanks for any suggestions.

formula or macro
 

Sorry....its not....network went down just as i was sending and wasn't sure
it went through

"Don Guillett" wrote:

How is this different than the other post you just submitted?

--
Don Guillett
SalesAid Software

"CMD" wrote in message
...
Hi. I am trying to create a budget in excel. in the first row, i have
the
year. in the second row, i have the months of the year. what i would
like
to do is the following: I would like to enter a line item for the budget
(e.g., license fee). next to that, i would like to list the amount,
followed
by the month and the year that it should appear in the budget.
e.g., license fee.....$10,000......July......2007

is there a command/macro that I can use that will take the dollar amount
and
place it in the right cell in the budget that corresponds to the month and
year listed? my goal is to not have to manually move dollar amounts
around
as timing changes during the budget development process.

Thanks for any suggestions.

formula or macro
 

Yes. this pseudo code should give you some ideas:

Dim rng as range, rng1 as Range
Dim cell as Range, rngMonth as Range
Dim rngCat as Range, res as Variant
Dim res1 as Variant
With worksheets("Vertical_List")
set rng = .Range(.cells(2,1),.Cells(2,1).End(xldown))
End with
with worksheets("Months")
set rngMonth = .Range("B2").Resize(1,12)
set rngCat = .Range(.Cells(2,1),.Cells(2,1).end(xldown))
End with
for each cell in rng
res = application.Match(cell.offset(0,2),rngMonth,0)
res1 = application.Match(cell,rngCat,0)
if not iserror(res) and not iserror(res1) then
set rng1 = rngMonth(1,res)
set rng2 = rngCat(res1)
With worksheets("Months")
.cells(rng2.row, rng1.column).Value = cell.offset(0,1)
End with
end if
Next

If multiple years are involved, that is not evident in your question.

--
Regards,
Tom Ogilvy



"CMD" wrote:

Hi. I am trying to create a budget in excel. in the first row, i have the
year. in the second row, i have the months of the year. what i would like
to do is the following: I would like to enter a line item for the budget
(e.g., license fee). next to that, i would like to list the amount, followed
by the month and the year that it should appear in the budget.
e.g., license fee.....$10,000......July......2007

is there a command/macro that I can use that will take the dollar amount and
place it in the right cell in the budget that corresponds to the month and
year listed? my goal is to not have to manually move dollar amounts around
as timing changes during the budget development process.

Thanks for any suggestions.

formula or macro
 

Thanks, Tom. Yes, multiple years would be involved. That is what makes it
so complicated doing it manually...I have about 200 line items that spread
out over 5 years.

Chris

"Tom Ogilvy" wrote:

Yes. this pseudo code should give you some ideas:

Dim rng as range, rng1 as Range
Dim cell as Range, rngMonth as Range
Dim rngCat as Range, res as Variant
Dim res1 as Variant
With worksheets("Vertical_List")
set rng = .Range(.cells(2,1),.Cells(2,1).End(xldown))
End with
with worksheets("Months")
set rngMonth = .Range("B2").Resize(1,12)
set rngCat = .Range(.Cells(2,1),.Cells(2,1).end(xldown))
End with
for each cell in rng
res = application.Match(cell.offset(0,2),rngMonth,0)
res1 = application.Match(cell,rngCat,0)
if not iserror(res) and not iserror(res1) then
set rng1 = rngMonth(1,res)
set rng2 = rngCat(res1)
With worksheets("Months")
.cells(rng2.row, rng1.column).Value = cell.offset(0,1)
End with
end if
Next

If multiple years are involved, that is not evident in your question.

--
Regards,
Tom Ogilvy



"CMD" wrote:

Hi. I am trying to create a budget in excel. in the first row, i have the
year. in the second row, i have the months of the year. what i would like
to do is the following: I would like to enter a line item for the budget
(e.g., license fee). next to that, i would like to list the amount, followed
by the month and the year that it should appear in the budget.
e.g., license fee.....$10,000......July......2007

is there a command/macro that I can use that will take the dollar amount and
place it in the right cell in the budget that corresponds to the month and
year listed? my goal is to not have to manually move dollar amounts around
as timing changes during the budget development process.

Thanks for any suggestions.

formula or macro
 

Sounds like you don't ahve categories on the left as I assumed, so I suggest
you just select your data and create a pivot Table.

Data=Pivot Table

--
Regards,
Tom Ogilvy


"CMD" wrote in message
...
Thanks, Tom. Yes, multiple years would be involved. That is what makes
it
so complicated doing it manually...I have about 200 line items that spread
out over 5 years.

Chris

"Tom Ogilvy" wrote:

Yes. this pseudo code should give you some ideas:

Dim rng as range, rng1 as Range
Dim cell as Range, rngMonth as Range
Dim rngCat as Range, res as Variant
Dim res1 as Variant
With worksheets("Vertical_List")
set rng = .Range(.cells(2,1),.Cells(2,1).End(xldown))
End with
with worksheets("Months")
set rngMonth = .Range("B2").Resize(1,12)
set rngCat = .Range(.Cells(2,1),.Cells(2,1).end(xldown))
End with
for each cell in rng
res = application.Match(cell.offset(0,2),rngMonth,0)
res1 = application.Match(cell,rngCat,0)
if not iserror(res) and not iserror(res1) then
set rng1 = rngMonth(1,res)
set rng2 = rngCat(res1)
With worksheets("Months")
.cells(rng2.row, rng1.column).Value = cell.offset(0,1)
End with
end if
Next

If multiple years are involved, that is not evident in your question.

--
Regards,
Tom Ogilvy



"CMD" wrote:

Hi. I am trying to create a budget in excel. in the first row, i have
the
year. in the second row, i have the months of the year. what i would
like
to do is the following: I would like to enter a line item for the
budget
(e.g., license fee). next to that, i would like to list the amount,
followed
by the month and the year that it should appear in the budget.
e.g., license fee.....$10,000......July......2007

is there a command/macro that I can use that will take the dollar
amount and
place it in the right cell in the budget that corresponds to the month
and
year listed? my goal is to not have to manually move dollar amounts
around
as timing changes during the budget development process.

Thanks for any suggestions.