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]

 

[jtakw]

Hi,

Are you adding 5 because of the number 5 IN the value C5? or C5 happens to be in row 5?

 

[Truman46]

Im adding 5 because C5 is the next step, regardless of what row number it is. It could also be C8 or even C15 is some instances. Its always the NEXT step number.

 

[jtakw]

.

 

[Truman46]

.

???? I have no idea what you mean by .

 

[jtakw]

Got interrupted while posting, preparing diner...

If it Always the Number for the next step, according to what you said in post #3 , then use this, but I don't understanding why 1*30, and not just 30:

Book1
	C	D	E	F	G
3	Step	Temp	Time	Setting	Program
4	C4	66	T4	-35	alarm on and next step
5	C5	66	T5	0	Pause
Sheet62

Cell Formulas
Range	Formula
F4	=IF(G4="alarm on and next step",-(1*30+MID(OFFSET(F4,1,-3),2,255)),0)

Change the "Value if False" at end of formula to whatever you need, I used 0.

 

[jtakw]

You can also use this:

Book1
	A	B	C	D	E
1	Step	Temp	Time	Setting	Program
2	C4	66	T4	-35	alarm on and next step
3	C5	66	T5	0	Pause
Sheet62

Cell Formulas
Range	Formula
D2	=IF(E2="alarm on and next step",-(1*30+MID(A3,2,255)),0)

 

[Truman46]

I don't understanding why 1*30, and not just 30:/QUOTE]

Ahh Thats because there are a few different alarm parameters such as Al2 off is 5*30+Next step. Al1 on = 2*30+next step etc etc.

So how do I set it up for a few different options such as below? So I choose an option from the drop down validated list under Program. And it puts a value into setting based on the formulas shown. Obviously Hold alarm 1 minute and Pause are easy but how do I put it all into a nested formula. Or can I use VLookup for this?

Thanks for your help.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Setting[/TD]
[TD]Program[/TD]
[/TR]
[TR]
[TD]=-(1*30+next step)[/TD]
[TD]Alarm2 on and next step[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Hold alarm 1 minute[/TD]
[/TR]
[TR]
[TD]=-(5*30+next step)[/TD]
[TD]Alarm 2 off and next step[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]Pause[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Yes, probably a good idea to use a Table for the formula, either VLOOKUP or INDEX/MATCH, you can use the same list for you drop down:

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,G2:H6,2,0)*30+MID(A3,2,255))))

D2 formula copied down.

 

[Truman46]

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