# Thread: Bypassing Scenarios (Ex 2002)

1. ## 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. ## 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

Hi

Sorry, I

4. ## 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. ## Re: Bypassing Scenarios (Ex 2002)

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

Mordy

6. ## 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. ## 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
•