Help with getting variable outputs from a single input

[Mikbob]

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]

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]

[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

	A​	B​	C​	D​	E​	F​
1​	input	output			input list	output list
2​	10​	200​			1​	20​
3​					2​	40​
4​					3​	60​
5​					4​	80​
6​					5​	100​
7​					6​	120​
8​					7​	140​
9​					8​	160​
10​					9​	180​
11​					10​	200​

<tbody>
</tbody>

Sheet: Sheet2

<tbody>
</tbody>

 

[Mikbob]

Thanks for the help, although I haven't used Visual Basic before, so I was reduced to following an online tutorial how to use it.

It seemed to just give me a list of unrelated numbers after I altered it, so I gave up and did it the long way around.

Thanks anyway!

 

[Akuini]

Ok, glad you figured it out.