validated list returning a formula that adds next row number?

[Truman46]

Hi excel gurus, Just wondering if someone could help me out please. I want to create a validated list where if someone chooses and option from the list it uses a formula that adds the next row number to the end of the formula.

So for example my list option would be "alarm on and next step". The formula for this is -(1*30+"next step") so in my example below when I choose "Alarm on and next step from the list I want it to put the value of the formula into the Setting column. But it has to add the next steps value so in this case it would be -(1*30+5) because C5 is the next step.

Thanks heaps.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Step[/TD]
[TD]Temp[/TD]
[TD]Time[/TD]
[TD]Setting[/TD]
[TD]Program[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]66[/TD]
[TD]T4[/TD]
[TD]-35[/TD]
[TD]"alarm on and next step"[/TD]
[/TR]
[TR]
[TD]C5[/TD]
[TD]66[/TD]
[TD]T5[/TD]
[TD]0[/TD]
[TD]Pause[/TD]
[/TR]
</tbody>[/TABLE]

 

[Truman46]

Here is an example of what it should look like. Its a pid program for a brewery controller. So the figures in the Temp and settings column are what is entered into the pid when you program it. So you go step C1 and enter 55 then next and you enter 5 next to T1 and so on. I want to be able to create a spreadsheet that automatically works out the Alarm on and Alarm off formulas. Although it would be nice to choose from a drop down list and use VLookup to fill in the parameters in Settings for all options there are probably too many options to choose like different temperature settings and even different times. Some beer recipes have different temps and step times etc. I suppose I could create a template using your VLookup example and then manually type in the temp settings and Hold at 72C over 10 mins, but choose from the validated list for alarm on and off etc. Then save the file with a new name so I dont overwrite the original. What do you think?

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1007"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Step[/TD]
[TD]Temp[/TD]
[TD]Time[/TD]
[TD]Setting[/TD]
[TD]Notes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]55[/TD]
[TD]T1[/TD]
[TD]5[/TD]
[TD]55C for 5 minutes[/TD]
[TD][/TD]
[TD]Alarm 2 on and next step[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]55[/TD]
[TD]T2[/TD]
[TD]7[/TD]
[TD]Raise to 62C over 7 mins[/TD]
[TD][/TD]
[TD]Hold alarm 1 minute[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]C3[/TD]
[TD]62[/TD]
[TD]T3[/TD]
[TD]35[/TD]
[TD]Hold at 62C for 35 mins[/TD]
[TD][/TD]
[TD]Alarm 2 off and next step[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]C4[/TD]
[TD]62[/TD]
[TD]T4[/TD]
[TD]10[/TD]
[TD]Raise to 72C over 10 mins[/TD]
[TD][/TD]
[TD]Pause[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C5[/TD]
[TD]72[/TD]
[TD]T5[/TD]
[TD]20[/TD]
[TD]Hold at 72C for 20 ins[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C6[/TD]
[TD]72[/TD]
[TD]T6[/TD]
[TD]5[/TD]
[TD]Raise to 77C over 5 mins[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C7[/TD]
[TD]77[/TD]
[TD]T7[/TD]
[TD]5[/TD]
[TD]Hold at 77C for 5 mins[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C8[/TD]
[TD]77[/TD]
[TD]T8[/TD]
[TD]-39[/TD]
[TD]Alarm 2 on and step 9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C9[/TD]
[TD]77[/TD]
[TD]T9[/TD]
[TD]1[/TD]
[TD]Hold alarm 1 min[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C10[/TD]
[TD]77[/TD]
[TD]T10[/TD]
[TD]-161[/TD]
[TD]Alarm 2 off and step 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C11[/TD]
[TD]77[/TD]
[TD]T11[/TD]
[TD]0[/TD]
[TD]Pause[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C12[/TD]
[TD]77[/TD]
[TD]T12[/TD]
[TD]5[/TD]
[TD]5 min sparge at 77C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C13[/TD]
[TD]77[/TD]
[TD]T13[/TD]
[TD]-44[/TD]
[TD]Alarm 2 on and step 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C14[/TD]
[TD]77[/TD]
[TD]T14[/TD]
[TD]1[/TD]
[TD]alarm 1 min[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C15[/TD]
[TD]77[/TD]
[TD]T15[/TD]
[TD]0[/TD]
[TD]Pause[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C16[/TD]
[TD]77[/TD]
[TD]T16[/TD]
[TD]-121[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Thanks heaps for that. I will give it a go and see how it goes. Thanks again, much appreciated.

You're welcome, but it seems either age or other causes, I've been forgetting to add absolute references when needed, please try this instead, same formula, just needed the absolute reference for the table:

Book1
	A	B	C	D	E	F	G	H
1	Step	Temp	Time	Setting	Program
2	C4	66	T4	-155	Alarm 2 off and next step		Alarm on and next step	1
3	C5	66	T5	0	Pause		Alarm 2 on and next step	1
4							Hold alarm 1 minute
5							Alarm 2 off and next step	5
6							Pause
Sheet62

Cell Formulas
Range	Formula
D2	=IF(E2="Hold alarm 1 minute",1,IF(E2="Pause",0,-(VLOOKUP(E2,G$2:H$6,2,0)*30+MID(A3,2,255))))

 

[jtakw]

OK, my last post above was just an edit to my Post #9 , just now read your most recent post, need to think about that in the morning, really late where I am.