+ Reply to Thread
Results 1 to 6 of 6

Evaluate result cell value for each input cell value from a column

  1. #1
    Registered User
    Join Date
    04-18-2019
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    6

    Evaluate result cell value for each input cell value from a column

    Hi,

    Just so you know my level, I am a dummy user of vba, who was able to accomplish couple simple codes and now want more .
    I am interested in following question. Not even sure if this is something to involve VBA or maybe there is an excel function to handle this?
    It is easier to describe what I mean on the attached example:
    I have excel based calculations, based on various inputs a "result" cell value is being calculated (Cell B21).
    For one of input cells (Cell B3) I have a table of possible values (F2:F13). I need to get result cell values for each of possible input cell values and collect them in the table next to inputs (G2:G13).
    Is there a way to make this work without copying entire original table of calculations multiple types and changing input values manually?

    Thank you
    Attached Files Attached Files

  2. #2
    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,091

    Re: Evaluate result cell value for each input cell value from a column

    Try:

    Please Login or Register  to view this content.
    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


  3. #3
    Registered User
    Join Date
    04-18-2019
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    6

    Re: Evaluate result cell value for each input cell value from a column

    Thanks. This works fine .
    I was trying to make something similar in a function.
    It works first for one cell, but excel dies if I try to copy this function for other cells or change an input cell value. Probably I force it into a loop...
    Do you think it is possible to make this function stable?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Red Nova; 06-20-2019 at 03:47 PM.

  4. #4
    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,091

    Re: Evaluate result cell value for each input cell value from a column

    The subroutine does exactly what you described ... it drops a value in at the top of the calculation and returns the result from the bottom.

    I don't think you can have a UDF to do this as there is only one input and one output.

    The only way to do it in a function would be to replicate all the intermediate calculations.

    Your choice.

  5. #5
    Registered User
    Join Date
    04-18-2019
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    6

    Re: Evaluate result cell value for each input cell value from a column

    TMS, Thank you.
    I suspected it will not work as a function, but wanted to make sure. Replicating all steps does not worth it since I am planning to apply it to some "heavy" calculations.
    So Sub it is...

  6. #6
    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,091

    Re: Evaluate result cell value for each input cell value from a column

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. [SOLVED] Make column cell input mandatory if initial cell has input
    By Joceau in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-28-2017, 03:46 AM
  2. [SOLVED] input and result of formula in the same cell
    By moomphas in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-07-2016, 07:05 AM
  3. [SOLVED] Evaluate each cell in a table column - quickest method?
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-30-2015, 05:08 PM
  4. Replies: 1
    Last Post: 09-18-2013, 01:58 PM
  5. [SOLVED] Conditional Formatting to evaluate formula result not cell value
    By snoproladd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2012, 11:11 PM
  6. many input in 1 cell, total result in other cell?
    By docthusinh in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-23-2007, 07:48 PM
  7. Evaluate a cell evalute 3 cell based on result
    By stevem8435 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2006, 05:25 AM

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