Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Act like I'm changing a value of a cell

  1. #1
    New Member
    Join Date
    Nov 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Act like I'm changing a value of a cell

    I have a formula with an input and an output but I don't want to manually enter the input multiple times then see what the output it and manually enter it into another cell. Is their a simple way to do this?

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,370
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    you need to explain what you want to do and expect to see, give examples, tell use where inputs and outputs occur
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Nov 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    Sorry, here is my scenario. I want the D36 cell to be my actual shock length when frame height is at the height of C36. Right now I must manually change cell D7 and see what cell F33 actually is. Then I have to manually enter that value into cell C36. Does this help?


  4. #4
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,370
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    are the variations constant, or could they be calculated on the fly (is z always 0,497 of x eg) , if stock I would be looking at LOOKUP as an array, or using VLOOKUP against a set of fixed values

    A much better explanation than your first offering which didn't give me anything to think about

    what is the calculation between D7 to achieve F33

    knowing the calculation would help
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  5. #5
    New Member
    Join Date
    Nov 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    Quote Originally Posted by mole999 View Post
    are the variations constant, or could they be calculated on the fly (is z always 0,497 of x eg) , if stock I would be looking at LOOKUP as an array, or using VLOOKUP against a set of fixed values

    A much better explanation than your first offering which didn't give me anything to think about

    what is the calculation between D7 to achieve F33

    knowing the calculation would help
    The problem with this is that the shock length is changing at a different rate than what frame height is. Which is the whole point behind this program. The calculations between D7 and F33 is pretty extensive. It uses all of the yellow modifiable inputs to come up with the shock length. That's why I was hoping there is any easier way of approaching this. I don't know anything about Arrays and I looked into Vlookup but didn't understand how it would work in this application.

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,370
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    whoever constructs the formula, needs to understand the interplay between your variables, so starting at 12 what makes 17.454 your final value ?
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    New Member
    Join Date
    Nov 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    Quote Originally Posted by mole999 View Post
    whoever constructs the formula, needs to understand the interplay between your variables, so starting at 12 what makes 17.454 your final value ?
    There are about 30 different calculations to come up with the 17.454 including many trig functions and finding the connection of 2 circles to get to the end result. You can't put all of these calculations into 1 long trig function. This is the problem!

  8. #8
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,370
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    you achieve it manually ?, then it must be possible to programme it. One option is to establish helper cells that do part of the work before the next stage
    • Yes I know there are better ways to do it. I just wish I knew them. - 2003, 2007, 2010, 2013 & 2016
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  9. #9
    New Member
    Join Date
    Nov 2014
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    Yes I can achieve it manually by entering the frame height value in D7 then it tells me shock length in F33. I have no idea what you mean in the last post.

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,242
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    10 Thread(s)

    Default Re: Act like I'm changing a value of a cell

    I think that what Mole is after is that the steps that the spreadsheet does to calculate the shock length to the frame height can be coded into a VBA UDF.

    But given your current set-up this Macro should fill in the chart thats at the bottom of your data set.
    You'll want to make the "Sheet1" match your sheet name and you might want to fiddle with the range through which X goes.

    Code:
    Sub test()
        Dim X as Double
        Dim myCell as Range
    
        With Sheets("Sheet1")
            Set myCell = .Range("C36")
    
            For X = 15 To 3 Step -.025
                myCell.Value = x
                .Range("D7").Value = x
                Calculate
                myCell.Offset(0,1).Value = .Range("F33").Value
                Set MyCell = myCell.Offset(1, 0)
            Next X
        End With
    End Sub
    Actualy if you are going to be doing this regularly, you could use VBA to create a lookup chart of values.
    Then rather than going through the D7/F33 routine you could either look up the value from the chart or interpolate for intermediate values (I don't me you personally, I mean that spread sheet formulas can do that, while they can't do the D7/F333)

    Are there any other variable besides D7? or is this the shocklenght for a particular frame of a particular construction and if that changes the result will change.

    If F33 is purely a function of one variable (D77) it would probably be worth your time to code it in a VBA UDF rather that go through the worksheet.
    Last edited by mikerickson; Nov 9th, 2014 at 08:18 PM.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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