Incrementing cell reference in Lookup formula
 

Hi Group,

With the following formula in B10
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A 2:AE2),"")

If I input 3 into L8 I need the formula to update to
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A 5:AE5),"")

If I input 10 into L8 I need the formula to update to
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A 12:AE12),"")
A zero or blank in L8 should leave the formula unchanged.

Thank you for any help
Martin

Incrementing cell reference in Lookup formula
 

Try using INDIRECT, viz.:
=IF(A10=1,LOOKUP(DAY(H$5),Random!A$1:AE$1,INDIRECT ("Random!A"&L8+2&":AE"&L8+2)),"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MartinW" wrote in message
...
Hi Group,

With the following formula in B10
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A 2:AE2),"")

If I input 3 into L8 I need the formula to update to
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A 5:AE5),"")

If I input 10 into L8 I need the formula to update to
=IF(A10=1,LOOKUP(DAY(H$5),RANDOM!A$1:AE$1,RANDOM!A 12:AE12),"")
A zero or blank in L8 should leave the formula unchanged.

Thank you for any help
Martin

Incrementing cell reference in Lookup formula
 

Thank you Max, Works like a charm!
I had actually attempted that approach but got hopelessly lost in the
syntax.
As usual I was over-complicating things.

Thanks again!
Martin

Incrementing cell reference in Lookup formula
 

welcome, Martin. good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MartinW" wrote in message
...
Thank you Max, Works like a charm!
I had actually attempted that approach but got hopelessly lost in the
syntax.
As usual I was over-complicating things.

Thanks again!
Martin