Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Location
    Efrat, Israel
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bypassing Scenarios (Ex 2002)

    Hi,

    I have a complicated spreadsheet that after cutting down, does the following:
    A1 5
    B1 6
    C1 A1*B1
    D1 8
    E1 C1/D1

    I

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bypassing Scenarios (Ex 2002)

    Not sure exactly what you are after.
    Where are the 20 values of "A1" going to be?
    Are you just putting the 20 values in G1:g20 and want in H1:H20 the calcs? Then H1 is:
    =G1*$B$1/$D$1

    and you can copy this to H2:H20.

    If you want the sum of all the 20 to go in H1 then H1:
    =sum(G1:G20)*$B$1/$D$1

    Am I missing something?

    Steve

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Location
    Efrat, Israel
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bypassing Scenarios (Ex 2002)

    Hi

    Sorry, I

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bypassing Scenarios (Ex 2002)

    I still don't understand what you are looking for.

    How do you want to see 20 answers in E1?
    Are you looking for the sum with the 20 possibilities of A1?
    If so, then the sum is (as I mentioned):
    The ("sum of the A1s") *B1/D1 [You didn't answer where you want to put the "A1"s]

    Where do you want the "nearby Table of possibilities"? I was thinking you wanted:
    The 20 "A1"s in G1:g20 and then the 20 "E1"s in H1:H20.

    If this is not what you are after you will have to explain better.

    Steve

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Location
    Efrat, Israel
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bypassing Scenarios (Ex 2002)

    Hi,
    Attached is a file that I hope explains my question.

    Mordy

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bypassing Scenarios (Ex 2002)

    Your spreadhseet is "more or less" what I understood it to be. In F5 enter the formula:
    <pre>=E5*$A$3/$A$5</pre>


    and copy it down to F24.

    I understand that your equations are more complex, but it is the same principle. Have the formula as a function of "A1" and enter it down the column.

    If the formula is too complex and you literally want to enter the values in E5:E24 one by one into A1 and put the results in F5:F24 you could do this with a macro:

    <pre>Option Explicit
    Sub CreateTable()
    Dim rngSource As Range
    Dim rngReplace As Range
    Dim rngResult As Range
    Dim rCell As Range
    Dim vOriReplace

    Set rngSource = Range("E5:E24")
    Set rngReplace = Range("A2")
    Set rngResult = Range("A6")

    vOriReplace = rngReplace.Value

    For Each rCell In rngSource
    rngReplace.Value = rCell.Value
    ActiveSheet.Calculate
    rCell.Offset(0, 1).Value = rngResult.Value
    Next
    rngReplace.Value = vOriReplace
    ActiveSheet.Calculate

    Set rngSource = Nothing
    Set rngReplace = Nothing
    Set rngResult = Nothing
    End Sub</pre>


    If neither result is what you are after, I guess I am still confused.

    Steve

  7. #7
    New Lounger
    Join Date
    Jul 2002
    Location
    Efrat, Israel
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bypassing Scenarios (Ex 2002)

    Hi,

    Thank you very much. Tommorow (its 10pm here) i'll redo the macro in the "real world".

    Mordy

Posting Permissions

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