Help with getting variable outputs from a single input

Mikbob · Aug 11, 2017

Hello,

I like to think I'm fairly proficient at Excel, but this has really stumped me. I've tried searching, but I'm not really sure what I'm asking for.

Basically I have a sheet with a number of formulae spread across it, with numerous inputs and formulae leading on from each other. What I want to create is a list of outputs when I alter one of the input variables.

So, I thought I could create a list of the input variables on another sheet, then interrogate the output cell so that I get an answer if the input cell equals the cell in my list. I've added a screenshot if that helps?

Excel problem.fw.png - Google Drive

I thought an IF function might do it, but I want to place each value in to the cell and see the output, not check a cell equals a value.

I really don't want to have to do this manually as it'll potentially take about as long as I've already spent trying not to do it manually!

Typical engineering student eh..?

If I've just confused you, please do say and I'll try to explain it better.

Mik

Akuini · Aug 12, 2017

Ok, try this:
In this example:
A2 = input
B2 = output
D2 down = input list
E2 down = output list, it’s the result generated by the code
The formula in B2 ‘=A2*20’

Please use Code Tags when posting a code. Like this: [CODE ]Your Code Here[/ CODE]

Code:

[B][COLOR=Royalblue]Sub[/COLOR][/B] a1018625a[B]()[/B]
[B][COLOR=Royalblue]Dim[/COLOR][/B] i [B][COLOR=Royalblue]As[/COLOR][/B] [B][COLOR=Royalblue]Long[/COLOR][/B][B],[/B] va
   
    Application.ScreenUpdating [B]=[/B] [B][COLOR=Royalblue]False[/COLOR][/B]
    Application.Calculation [B]=[/B] xlCalculationManual
 
 
    va [B]=[/B] Range[B]([/B][B][COLOR=brown]"E2"[/COLOR][/B][B],[/B] Cells[B]([/B]Rows.count[B],[/B] [B][COLOR=brown]"E"[/COLOR][/B][B]).[/B][B][COLOR=Royalblue]End[/COLOR][/B][B]([/B]xlUp[B]))[/B]
   
    [B][COLOR=Royalblue]For[/COLOR][/B] i [B]=[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B] [B][COLOR=Royalblue]To[/COLOR][/B] UBound[B]([/B]va[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        Range[B]([/B][B][COLOR=brown]"A2"[/COLOR][/B][B])[/B] [B]=[/B] va[B]([/B]i[B],[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B]
        Range[B]([/B][B][COLOR=brown]"B2"[/COLOR][/B][B]).[/B]Calculate
        Range[B]([/B][B][COLOR=brown]"F"[/COLOR][/B] [B]&[/B] i [B]+[/B] [B][B][COLOR=crimson]1[/COLOR][/B][/B][B])[/B] [B]=[/B] Range[B]([/B][B][COLOR=brown]"B2"[/COLOR][/B][B])[/B]
    [B][COLOR=Royalblue]Next[/COLOR][/B]
 
    Application.ScreenUpdating [B]=[/B] [B][COLOR=Royalblue]True[/COLOR][/B]
    Application.Calculation [B]=[/B] xlCalculationAutomatic
 
[B][COLOR=Royalblue]End[/COLOR][/B] [B][COLOR=Royalblue]Sub[/COLOR][/B]

Excel 2007 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: Darkslateblue"]
[TH][/TH]
[TH]

A

[/TH]
[TH]

B

[/TH]
[TH]

C

[/TH]
[TH]

D

[/TH]
[TH]

E

[/TH]
[TH]

F

[/TH]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]

1

[/TD]
[TD="bgcolor: lightgreen"]input[/TD]
[TD="bgcolor: lightgreen"]output[/TD]
[TD="bgcolor: lightgreen"][/TD]
[TD="bgcolor: lightgreen"][/TD]
[TD="bgcolor: lightgreen"]input list[/TD]
[TD="bgcolor: lightgreen"]output list[/TD]
[/TR]
[TR="bgcolor: white"]
[TD="bgcolor: Darkslateblue"]

2

[/TD]
[TD]

10

[/TD]
[TD]

200

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1

[/TD]
[TD="bgcolor: yellow"]

20