Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset
I have data in sheet1 as folows:
row data calucations 1 a 12 2 b 0 3 c 14 I need to use offset formula to get results in sheet2 for rows withoud 0 in col C row data calucations 1 a 12 3 c 14 Thanks Eva |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset
Assuming source data in cols A to C in Sheet1, from row1 down,
where the key col = col C In Sheet2, Put in A1: =IF(OR(Sheet1!C1="",Sheet1!C1=0),"",ROW()) Put in B1: =IF(ROW()COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($ A:$A,ROW()))) Copy B1 to D1. Select A1:D1, fill down to cover the max expected extent of data in Sheet1, eg down to C100. Hide away col A. Cols B to D will return the required results, ie only lines without zero values (or blanks) in Sheet1's col C. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Eva" wrote: I have data in sheet1 as folows: row data calucations 1 a 12 2 b 0 3 c 14 I need to use offset formula to get results in sheet2 for rows withoud 0 in col C row data calucations 1 a 12 3 c 14 Thanks Eva |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of Offset function in array formula | Excel Worksheet Functions | |||
How to offset cell value from thick border lines? | Excel Discussion (Misc queries) | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Question for use of offset and range | Excel Worksheet Functions |