+ Reply to Thread
Results 1 to 9 of 9

Allowing a cell with a formula to also allow for user input.

  1. #1
    Registered User
    Join Date
    09-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Allowing a cell with a formula to also allow for user input.

    I wasn't sure how to write the title of this post, or whether or not the title actually applies to what I am trying to do...
    but here is my scenario:

    I have a spreadsheet that has a lot of cells that require user-input.
    I want to have a master drop-down-selection cell (at the very beginning of the spreadsheet) that allows a user to select a "starting point".

    The drop-down "starting-point" selection would pre-populate all of the spreadsheet cells,
    then the user can go through and manipulate some of the pre-populated values to their liking.

    Then (perhaps I'm getting ahead of myself here) I'd like the user to be able to go back to the master drop-down-selection cell and be able to choose a new "starting-point",
    Which would then go back through the spreadsheet pre-populating over anything the user has manually input.

    ...

    so basically:

    User chooses a pre-populated starting-point.
    User goes through and tweaks values he/she would like to tweak.
    User looks at result... say's "hum, that's interesting..."
    User can then go back and choose another pre-populated starting-point and start all over again.

    ...

    Any ideas?

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Allowing a cell with a formula to also allow for user input.

    Hi,

    as far as I know this will be hard (impossible?) without VBA, can you provide a sample workbook with 2 scenario examples?
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,457

    Re: Allowing a cell with a formula to also allow for user input.

    The only way that I could see that working is to have a VBA Worksheet Change event monitoring the drop down list of starting points. The source for the drop down list could be the headings of a matrix. The second dimension of the matrix would be a list of cell references and the content of the matrix would be columns of values for each of the cell references.

    So, when an entry is selected from the drop down box, the change event would loop through the relevant column of cells and populate the worksheet cells.

    Then the user could play to their heart's content and reset with the same or a different starting point.

    Regards, TMS
    Last edited by TMS; 09-24-2013 at 02:18 PM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    09-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Allowing a cell with a formula to also allow for user input.

    sample uploaded.
    Hopefully that help clarify.

    I don't know anything about VBA, so perhaps its time I learn...
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Allowing a cell with a formula to also allow for user input.

    Hi,

    check attached example, each time the value in C$ is changed the formulas are reapplied, at the same time the user can write values into C9-C11: Sample_Maintanance_Cost.xlsm

    Code:
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    09-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Allowing a cell with a formula to also allow for user input.

    Thanks guys for all the help.
    I now just need to take the time to learn the basics of VBA.

    And thanks tehneXus for proving to me that it is possible w/ the use of VBA.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,457

    Re: Allowing a cell with a formula to also allow for user input.

    This is how it would work in principle. I haven't done much testing but it seemed OK with your sample.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-24-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Allowing a cell with a formula to also allow for user input.

    (I seem to have messed-up my first/second reply, so here we go again):

    TMShucks:

    Your example file seems to function perfectly! (exactly as I described)
    You even managed to hide the individual cell-functions from the user. (how?)

    If you could explain in laymens terms what you did, that would be very helpful.
    Otherwise I'll start picking away at that code trying to figure it out one bit at a time.

    Even if you could just give me a broad: "this is what your file was originally, I did ____ to it". statement that would help me get started.
    (or did you just do as you described in your first post?)

    Hopefully I can learn what you did and apply it to my larger data-sheets.

    Thanks!

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,457

    Re: Allowing a cell with a formula to also allow for user input.

    You're welcome. Thanks for the rep.


    You even managed to hide the individual cell-functions from the user. (how?)
    They're not hidden, they're no longer present.

    Making a change on the sheet causes the worksheet change event to fire.

    When you change the vehicle in the drop down list, the code works out which row that vehicle is in. It then loops across the top row picking up cell references ... it uses those to put the data for the vehicle in the appropriate cell.

    This, effectively, replaces your IF/INDEX/MATCH/MATCH in code.


    Hope that helps,

    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cell that allows user input and formula
    By brickwall015 in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 06-28-2013, 11:54 AM
  2. Cell based on formula and allow user input?
    By proepert in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-27-2013, 06:00 PM
  3. How to lock a cell only not allowing user change format?
    By ohlalayeah in forum Excel General
    Replies: 5
    Last Post: 07-26-2012, 09:24 AM
  4. Allowing user to select a cell but not edit it?
    By Falantar in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-26-2009, 09:18 AM
  5. Replies: 0
    Last Post: 05-20-2009, 10:40 PM

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