| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can you lookup a value & get the cell reference? | Excel Worksheet Functions | |||
| two way lookup and cell reference | Excel Worksheet Functions | |||
| Return cell reference of lookup value | Excel Worksheet Functions | |||
| incrementing formula reference by 7 | Excel Discussion (Misc queries) | |||
| Incrementing rows (or columns) to a reference in another worksheet | Excel Worksheet Functions | |||
Linear Mode
