+ Reply to Thread
Results 1 to 6 of 6

User Input form into formula

  1. #1
    Registered User
    Join Date
    06-06-2011
    Location
    UK
    MS-Off Ver
    2016
    Posts
    22

    User Input form into formula

    I am putting together a modelling spreadsheet that will show the cost of items we currently buy if they go up by a certain percentage. Since this is sensitive I have made a very basic model of it. I have a couple of questions:

    1) How can I take the user's input from the UserForm1 and put it into the formula =(B5+(B5/100)*2) (The red two is percentage of increase that I would like the user to be able to input via the form)
    2) How can I get this to apply to all of the items in the list?
    3) Is it possible it possible to be able to revert back to the original figures as this is only for modelling purposes?
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: User Input form into formula

    You don't really need a form, actually makes it harder to do. You put the % increase into a cell then your formula is simply:

    =B5*(1+$C$3)

    In that cell I added a drop down selector to make "choosing a percentage" quicker, just like a form.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-06-2011
    Location
    UK
    MS-Off Ver
    2016
    Posts
    22

    Re: User Input form into formula

    I see what you're saying but the spreadsheet is so large I cannot add another column. I need to something that will change this one. The formula I'm using is also more complex as it requires different inputs for difference item types, so it's not the same percentage increase across the board.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: User Input form into formula

    Of course you can add another column. And if you wish to make changes to a set of prices and revert them back to original values in the future, you WILL need a reference set of values somewhere. You could put the reference prices on another (possibly hidden) sheet, if you wish.

    Anyway, even if you're using a FORM, the process is still the same. You will link the value you enter in your form to a cell somewhere and the formulas will update based on those cell percentages.

    If you're using different percentages for different price cells, then of course your formulas will take that percentage from its correct percentage-increment cell, however many you need to have.

  5. #5
    Registered User
    Join Date
    06-06-2011
    Location
    UK
    MS-Off Ver
    2016
    Posts
    22

    Re: User Input form into formula

    I don't want to move columns around as a summary is made in another spreadsheet using data from this one and I think it uses hard coded cells rather than using lookups.

    I assumed that would be the case. Adding a hidden worksheet isn't an issue.

    Okay, I would still like to know how to take user inputs from forms and use them in formulas. If you, or anyone else could advise on that.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: User Input form into formula

    Hi neato
    Try this
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ 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