+ Reply to Thread
Results 1 to 2 of 2

Change table with buttons (eg. add 10% on electricity)

  1. #1
    Registered User
    Join Date
    12-07-2010
    Location
    Sweden
    MS-Off Ver
    Excel 2010
    Posts
    15

    Change table with buttons (eg. add 10% on electricity)

    I have a work sheet were you enter alot of data, eg power consumption, purchase cost, maintenance cost, etc for different product mixes.
    These costs are then added into a table on another worksheet called "Presentation".

    On this worksheet I would like to have the possibility to show that if I purchase a specific product mix (Range1 contains the values affected) and for example the electricity price goes up 10%, this should be shown in the table and how it affects the total price in the end.
    The same thing for the other costs (Range2, Range3, etc)

    I first thought about having like a button that changes the cells on the data worksheet to 0.9 times the original value but if I used a macro, there would be no way to go back to the original price right?
    Then I thought about using the button to change the cell value in the Presentation worksheet (=Range1) to =Range1*0.9 and then having a button that just says original and changes it back to =Range1.

    The problem now is however that I don't know how to write that code
    I also would like the button that is "activated" to be marked so that I know how many precent lower the electricity price is and how much higher the maintenance price is.

    You guys are the best! I really fell I need to learn much more Excel...

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,701

    Re: Change table with buttons (eg. add 10% on electricity)

    I would suggest identifying a cell on your data worksheet (let's say A14) to be the percent of change you want to apply to the original value. It would be formatted as a percentage so that 0.1 would be formatted as 10%. Then make your formula in the Presentation worksheet say

    =Range1*(1+Data!$A$14)

    where Range1 is the original value.

    You didn't name your first worksheet; change Data to whatever the worksheet name is.

    This is exactly the kind of analysis that Excel is good at without using macros. Even though I love macros I wouldn't use them for this problem.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1