| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell reference question
Version:Excel 2003
I am trying to reference the first and the last cell within a group of cell with the same value. Here is what I have: B22=ADDRESS(MIN(IF(table=A22,ROW(table))),MIN(IF(t able=A22,COLUMN(table))),, 1) & "-" & ADDRESS(MAX(IF(table=A22,ROW(table))),MAX(IF(table =A22,COLUMN(table))),, 1) Where table is A27:A10377 A22 = "REF VALUE" The cell B22 returns #VALUE, however when I use the formula button (fx), the formula result is displayed as $A$34-$A$97 (which is correct). When I evaluate the formula, I get and error #value ADDRESS(MIN(IF(#value!=A22, ..... or in other words: IF(A27:A10377... I even tried to change the cell reference to a number (A22=60155), thinking it was something with the MIN MAX functions, but alas, the same result. If anyone can help, or explain why this doesn't work I would greatly appreciate it. Thank you Ivar |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell reference question
Did you array enter the formula? Array formulas need to be entered using the
key combination of CTRL,SHIFT,ENTER (not just ENTER) Here's another way to do this that doesn't require an array formula: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table)) -- Biff Microsoft Excel MVP "blumonkey" wrote in message ... Version:Excel 2003 I am trying to reference the first and the last cell within a group of cell with the same value. Here is what I have: B22=ADDRESS(MIN(IF(table=A22,ROW(table))),MIN(IF(t able=A22,COLUMN(table))),, 1) & "-" & ADDRESS(MAX(IF(table=A22,ROW(table))),MAX(IF(table =A22,COLUMN(table))),, 1) Where table is A27:A10377 A22 = "REF VALUE" The cell B22 returns #VALUE, however when I use the formula button (fx), the formula result is displayed as $A$34-$A$97 (which is correct). When I evaluate the formula, I get and error #value ADDRESS(MIN(IF(#value!=A22, ..... or in other words: IF(A27:A10377... I even tried to change the cell reference to a number (A22=60155), thinking it was something with the MIN MAX functions, but alas, the same result. If anyone can help, or explain why this doesn't work I would greatly appreciate it. Thank you Ivar |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell reference question
Correction:
="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table)) Should be: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(2,1/(table=A22),ROW(table)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Did you array enter the formula? Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Here's another way to do this that doesn't require an array formula: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table)) -- Biff Microsoft Excel MVP "blumonkey" wrote in message ... Version:Excel 2003 I am trying to reference the first and the last cell within a group of cell with the same value. Here is what I have: B22=ADDRESS(MIN(IF(table=A22,ROW(table))),MIN(IF(t able=A22,COLUMN(table))),, 1) & "-" & ADDRESS(MAX(IF(table=A22,ROW(table))),MAX(IF(table =A22,COLUMN(table))),, 1) Where table is A27:A10377 A22 = "REF VALUE" The cell B22 returns #VALUE, however when I use the formula button (fx), the formula result is displayed as $A$34-$A$97 (which is correct). When I evaluate the formula, I get and error #value ADDRESS(MIN(IF(#value!=A22, ..... or in other words: IF(A27:A10377... I even tried to change the cell reference to a number (A22=60155), thinking it was something with the MIN MAX functions, but alas, the same result. If anyone can help, or explain why this doesn't work I would greatly appreciate it. Thank you Ivar |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell reference question
On May 19, 12:57 pm, "T. Valko" wrote:
Correction: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table)) Should be: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(2,1/(table=A22),ROW(table)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Did you array enter the formula? Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Here's another way to do this that doesn't require an array formula: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table)) -- Biff Microsoft Excel MVP "blumonkey" wrote in message ... Version:Excel 2003 I am trying to reference the first and the last cell within a group of cell with the same value. Here is what I have: B22=ADDRESS(MIN(IF(table=A22,ROW(table))),MIN(IF(t able=A22,COLUMN(table))),, 1) & "-" & ADDRESS(MAX(IF(table=A22,ROW(table))),MAX(IF(table =A22,COLUMN(table))),, 1) Where table is A27:A10377 A22 = "REF VALUE" The cell B22 returns #VALUE, however when I use the formula button (fx), the formula result is displayed as $A$34-$A$97 (which is correct). When I evaluate the formula, I get and error #value ADDRESS(MIN(IF(#value!=A22, ..... or in other words: IF(A27:A10377... I even tried to change the cell reference to a number (A22=60155), thinking it was something with the MIN MAX functions, but alas, the same result. If anyone can help, or explain why this doesn't work I would greatly appreciate it. Thank you Ivar The Array enter was the thing I was missing, didn't even know about it :-) Like your solution better though, just to help me understand (if you have the time), so if I use the LOOKUP and the ROW in a combination, it would look up the last value found of A22? Is INDEX something similar to ADDRESS, but list the row only? Thank you again! Ivar |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell reference question
"blumonkey" wrote in message
... On May 19, 12:57 pm, "T. Valko" wrote: Correction: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table)) Should be: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(2,1/(table=A22),ROW(table)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Did you array enter the formula? Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Here's another way to do this that doesn't require an array formula: ="A"&INDEX(ROW(table),MATCH(A22,table,0))&"-A"&LOOKUP(A22,table,ROW(table)) -- Biff Microsoft Excel MVP "blumonkey" wrote in message ... Version:Excel 2003 I am trying to reference the first and the last cell within a group of cell with the same value. Here is what I have: B22=ADDRESS(MIN(IF(table=A22,ROW(table))),MIN(IF(t able=A22,COLUMN(table))),, 1) & "-" & ADDRESS(MAX(IF(table=A22,ROW(table))),MAX(IF(table =A22,COLUMN(table))),, 1) Where table is A27:A10377 A22 = "REF VALUE" The cell B22 returns #VALUE, however when I use the formula button (fx), the formula result is displayed as $A$34-$A$97 (which is correct). When I evaluate the formula, I get and error #value ADDRESS(MIN(IF(#value!=A22, ..... or in other words: IF(A27:A10377... I even tried to change the cell reference to a number (A22=60155), thinking it was something with the MIN MAX functions, but alas, the same result. If anyone can help, or explain why this doesn't work I would greatly appreciate it. Thank you Ivar The Array enter was the thing I was missing, didn't even know about it :-) Like your solution better though, just to help me understand (if you have the time), so if I use the LOOKUP and the ROW in a combination, it would look up the last value found of A22? Is INDEX something similar to ADDRESS, but list the row only? Thank you again! Ivar if I use the LOOKUP and the ROW in a combination, it would look up the last value found of A22? Yes, use the "corrected" version. Is INDEX something similar to ADDRESS, but list the row only? Sort of. You give INDEX a range of values to "hold". Those values (in this case) are the row numbers for your named range Table. We then tell INDEX we want the row number that corresponds to the first location of "REF VALUE" by using the MATCH function. -- Biff Microsoft Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Reference Question | Excel Discussion (Misc queries) | |||
| cell reference question | New Users to Excel | |||
| Cell Reference Question | Excel Worksheet Functions | |||
| Cell reference question | Excel Discussion (Misc queries) | |||
| cell reference question | Excel Discussion (Misc queries) | |||
Linear Mode
