| Home |
| Search |
| Today's Posts |
|
#9
|
|||
|
|||
Disregard!
I got it working. Biff "Biff" wrote in message ... Hi Harlan Can't get that to work. This portion (both instances): COUNTIF(OFFSET(tbl,ROW(tbl)-CELL("Row",tbl),0,1),A15) Evaluates to: COUNTIF({#VALUE!,#VALUE!,#VALUE!},A15) So the final result of the formula is #N/A. Biff "Harlan Grove" wrote in message ups.com... Biff wrote... Well, you didn't mention that the range had merged cells(they usually cause nothing but grief, as you're finding out) I also see in your reply to BJ that you may have multiple occurances. So, your options are extremely limited. Maybe BJ's macro will solve your problem. ... VBA not needed, and merged cells aren't a problem for this. As for duplicates, either you search first by row then by column or first by column then by row. For example, searching for 5 in 1 2 3 4 6 7 5 8 9 5 0 1 should the result be 3 (matching the 5 in the 2nd row, 3rd column first, so mathcing by row then column) or 7 (matching the 5 in the 2nd column, 3rd row first, so matching by column then row)? To match by row then column, you could use an array formula like =CELL("Address",INDEX(Tbl,MATCH(TRUE,COUNTIF(OFFSE T(Tbl, ROW(Tbl)-CELL("Row",Tbl),0,1,),v)0,0)-1, MATCH(v,INDEX(Tbl,MATCH(TRUE,COUNTIF(OFFSET(Tbl, ROW(Tbl)-CELL("Row",Tbl),0,1,),v)0,0),0),0))) where Tbl represents the data range and v the value to match. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
| Excel should let me circle a cell or number in the spreadsheet fo. | Excel Discussion (Misc queries) | |||
| How do I format a cell for a custom part number? | Excel Discussion (Misc queries) | |||
| function cell range limitations | Excel Worksheet Functions | |||
| multiply by actual number in cell | Excel Worksheet Functions | |||
Threaded Mode
