macro or formula

[Guest]

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 create a general template where I
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 correct 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.

 

[JE McGimpsey]

If I understand you correctly, one way, using formulae:

On your list sheet (say, 'LIST'):

A B C D
1 ITEM AMT MONTH YEAR
2 license fee 10000 July 2007
3 ...

on your summary sheet

A B C D
1 2007 2007 2007
2 ITEM JULY AUGUST SEPTEMBER
3 license fee

then

B3: =SUMPRODUCT(--(LIST!$A$2:$A$1000=$A3),--(LIST!$C$2:$C$1000=B$2),
--(LIST!$D$2:$D$2000=B$1),LIST!$B$2:$B$1000)

Which you can copy down and across as necessary.


But you'd probably be better off using a Pivot Table...

A macro would probably be my last choice unless calculation time got too
long for either of the others.

 

[Guest]

Thanks JE!!

If I understand you correctly, one way, using formulae:

On your list sheet (say, 'LIST'):

A B C D
1 ITEM AMT MONTH YEAR
2 license fee 10000 July 2007
3 ...

on your summary sheet

A B C D
1 2007 2007 2007
2 ITEM JULY AUGUST SEPTEMBER
3 license fee

then

B3: =SUMPRODUCT(--(LIST!$A$2:$A$1000=$A3),--(LIST!$C$2:$C$1000=B$2),
--(LIST!$D$2:$D$2000=B$1),LIST!$B$2:$B$1000)

Which you can copy down and across as necessary.


But you'd probably be better off using a Pivot Table...

A macro would probably be my last choice unless calculation time got too
long for either of the others.