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.

Step	Temp	Time	Setting	Program
C4	66	T4	-35	"alarm on and next step"
C5	66	T5	0	Pause

<tbody>
</tbody>

 

[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?

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

<tbody>
</tbody>

 

[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.