5- year budget template: Macro to inflate cells in non-contiguous range

[spena129]

Hi All,

I am creating a 5-year budget template and want to incorporate "INFLATE" buttons into the workbook. On the worksheet ("Start") the user would input assumptions including the inflation factor for certain expense categories each year after year 1 (i.e. year 2 = 5%, year 3 = 10%, and so on). On the actual budget worksheet ("BudgetPage"), I have inserted "INFLATE" buttons next to the expense category headings. Each expense category has multiple line items (a range of cells each year) and subtotal. My goal is to create a macro that, upon clicking the "INFLATE" button, would loop through each cell in the line item cell range of the previous budget year inflate it by the factor on sheet "Start", and insert that value in the next budget year.

Example: User inputs 5% inflate travel category for year 2 (Cell= Start!C16) and 8% for years 3-5 (Start!D16:F16) on the "Start" sheet. On the "BudgetPage", user inputs $250 and $500 values in year 1 line items (BudgetPage!I46:I47). User clicks "INFLATE", and years 2-5 line item values (BudgetPage!N46:N47; S46:S47; X46:X47; AC46:AC47) are inflated. If adjustments are needed to target certain yearly/total budget amounts, values can be altered.

Can anyone help?

 

[Hui]

Steve

Add a row for input of estimated inflation

Then add another row for cumulative inflation

Then at your costs change the formula


From

=ABC


To

=if(InflateCell=True,ABC*CummInflation, ABC)


Then link a Button to the InflateCell


Inflation solved, no macros

 

[spena129]

Thanks, Hui. First, the "INFLATE" button is round rectangle shape I inserted. Can I link it to a cell without macros? If that is possible, it might just work. Second, I initially just had a formula similar to what you suggested, but I thought that since the budgets I normally construct target a yearly/cumulative total, there are frequent adjustments needed after inflating. I thought that having a macro that inputs just a value in each cell will serve this purpose, and adjustments can go from there instead of having to worry about altering a formula (most of the users would probably overwrite or alter the formula incorrectly). Thoughts?

 

[Hui]

Shapes have to be linked to a macro

But you can insert a Button that can be linked directly to a cell

It's on the Developer Tab, which may be hidden and need turning on

 

[Hui]

I don’t see Developer Ribbon. Now what?


If you do not see Developer ribbon, follow these instructions.


Excel 2007:


1. Click on Office button (top left)

2. Go to Excel Options

3. Go to Popular

4. Check “Show Developer Tab in Ribbon” (3rd Check box)

5. Click ok.


Excel 2010:


1. Click on File Menu (top left)

2. Go to Options

3. Select “Customize Ribbon”

4. Make sure “Developer tab” is checked in right side area

5. Click ok.


Plagiarized from: http://chandoo.org/wp/2011/08/29/introduction-to-vba-macros/

 

[spena129]

Thanks again, Hui. I might be missing something, but the "Button" under form controls does not link to a cell. Sorry for my ignorance.

 

[Hui]

Sorry, my fault


use a Check Box not a Button