Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or formula
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or formula
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. In article , 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 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro or formula
Thanks JE!!
"JE McGimpsey" wrote: 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. In article , 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Macro | Excel Discussion (Misc queries) | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Formula to Macro help PLEASE | Excel Programming | |||
Macro and Formula | Excel Worksheet Functions | |||
Help on Macro or Formula | Excel Discussion (Misc queries) |