Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Please help
From a cell I want to find out what is the first cell going upwards in the
same column that is not empty or does not contain a zero value. I don't want to know the value I want the cell reference so I can use it in a formula. Does anyone know how I can do this in Excel 2003? I hope you can help me. I appreciate any help or information given. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Please help
Neil,
you can use this array formula (commit with Ctrl+Shift+Enter) =ADDRESS(MAX(IF(A1:A1000<0,ROW(A1:A1000)),_column _) For _column use the number of the column where data is located. HTH Kostis Vezerides Neil Hindry wrote: From a cell I want to find out what is the first cell going upwards in the same column that is not empty or does not contain a zero value. I don't want to know the value I want the cell reference so I can use it in a formula. Does anyone know how I can do this in Excel 2003? I hope you can help me. I appreciate any help or information given. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Please help
Hi Neil
One way =MAX(MATCH(LOOKUP(99^99,A:A),A:A),MATCH(LOOKUP(REP T("z",255),A:A),A:A)) -- Regards Roger Govier "Neil Hindry" wrote in message ... From a cell I want to find out what is the first cell going upwards in the same column that is not empty or does not contain a zero value. I don't want to know the value I want the cell reference so I can use it in a formula. Does anyone know how I can do this in Excel 2003? I hope you can help me. I appreciate any help or information given. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Please help
You're right, DL..Thanks for spotting the error......The formulas return the
value, not the reference. My first post does the job. =CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20)))) or The formula reference variation.... =INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20))) as proven by =ROW(INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20)))) *********** Regards, Ron XL2002, WinXP "daddylonglegs" wrote: I don't think these will work, Ron. Wouldn't you need MATCH not LOOKUP?, i.e. =INDEX(A1:A20,MATCH(2,1/(A1:A20<0))) confirmed with CTRL+SHIFT+ENTER "Ron Coderre" wrote: Actually.....these are shorter: For the cell address: =CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20))) or....to be used in a formula (as you mentioned): =INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: To deteremine the address of the LAST non-blank, non-zero value above cell A21 Try this: =CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20)))) Does that help? *********** Regards, Ron XL2002, WinXP "Neil Hindry" wrote: From a cell I want to find out what is the first cell going upwards in the same column that is not empty or does not contain a zero value. I don't want to know the value I want the cell reference so I can use it in a formula. Does anyone know how I can do this in Excel 2003? I hope you can help me. I appreciate any help or information given. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Please help
Actually.....these are shorter:
For the cell address: =CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20))) or....to be used in a formula (as you mentioned): =INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: To deteremine the address of the LAST non-blank, non-zero value above cell A21 Try this: =CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20)))) Does that help? *********** Regards, Ron XL2002, WinXP "Neil Hindry" wrote: From a cell I want to find out what is the first cell going upwards in the same column that is not empty or does not contain a zero value. I don't want to know the value I want the cell reference so I can use it in a formula. Does anyone know how I can do this in Excel 2003? I hope you can help me. I appreciate any help or information given. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Please help
Assuming your formula is in A100 try this
=INDEX(A$1:A99,MAX(IF(A$1:A99<0,ROW(A$1:A99)-ROW(A$1)+1))) confirmed with CTRL+SHIFT+ENTER Note that if you use this formula on its own it will return the last value in the column (that isn't zero) BUT used within a formula it WILL return the cell reference as requested "Neil Hindry" wrote: From a cell I want to find out what is the first cell going upwards in the same column that is not empty or does not contain a zero value. I don't want to know the value I want the cell reference so I can use it in a formula. Does anyone know how I can do this in Excel 2003? I hope you can help me. I appreciate any help or information given. Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Function - Please help
I don't think these will work, Ron. Wouldn't you need MATCH not LOOKUP?, i.e.
=INDEX(A1:A20,MATCH(2,1/(A1:A20<0))) confirmed with CTRL+SHIFT+ENTER "Ron Coderre" wrote: Actually.....these are shorter: For the cell address: =CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20))) or....to be used in a formula (as you mentioned): =INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),A1:A20)) Does that help? *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: To deteremine the address of the LAST non-blank, non-zero value above cell A21 Try this: =CELL("address",INDEX(A1:A20,LOOKUP(2,1/(A1:A20<0),ROW(A1:A20)))) Does that help? *********** Regards, Ron XL2002, WinXP "Neil Hindry" wrote: From a cell I want to find out what is the first cell going upwards in the same column that is not empty or does not contain a zero value. I don't want to know the value I want the cell reference so I can use it in a formula. Does anyone know how I can do this in Excel 2003? I hope you can help me. I appreciate any help or information given. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
IS THERE AN INTERPOLATION FUNCTION IN EXCEL 2003 | Excel Worksheet Functions | |||
How do i execute a VBA function by clicking on an excel cell? | Excel Discussion (Misc queries) | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) |