Help with CELL Formula (Troubleshooting)

  • Thread starter Thread starter Jeff J
  • Start date Start date
J

Jeff J

I have a range of cells containing a classification, e.g.
1, 2, 3 or 4. I have a macro to sort the cells based on
classification, now I'm trying to create a formula that
will allow me to find the first cell and the last cell
(cell addresses) containing, for example "2" in range J3-
J1000 in another workbook. I fixed the syntax of the
formula, but I get an N/A.

Is my approach feasible, do I have the formula structured
incorrectly or have I encountered a limitation in Excel?

The basic formula works, but I encounter the problem when
I try to integrate the INDIRECT function.

Here is the setup:
B C
1 Workbook.xls J3
2 Sheet1 J1000
3 "2"

Formula for first occurance of "2":
=CELL("address",INDEX(INDIRECT
("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2),MATCH(B3,(INDIRECT
("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2)),0)))

The formula for the last occurance is the same, except
the 0 becomes 1.

Any thoughts/fixes/comments would be greatly appreciated.

Thanks,
Jeff
 
Hi Jeff,

I went through your challenge, and it was a challenge.

I got it to work and here is my solution.

=CELL("address",INDEX(INDIRECT("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2), LINE 1

MATCH(B3,(INDIRECT("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2)),1))) LINE 2

Careful of the line wrap. I have provided the LINE numbers to assist you.

If you look closely, you should find that this equation is identical to your
equation ~except~ "split" in different spots. What I seem to notice is when
I used alt enter to split the equation in different spots, the equation
often failed. I am guessing (and I do mean guessing) that the line break in
text monkeys the indirect statement. I don't know. Maybe I did something
wrong with the splitting? Could be. But in any event, I used this equation
to located your first "2." Oh, and that reminds me, in cell B3 I have a
numerical (as opposed to text) 2. So it is just 2, not "2". If I change
the 2 to "2", I get your #NA. Whew!!
The formula for the last occurance is the same, except
the 0 becomes 1.
Click to expand...

Don't think so. It ain't quite that easy. If the match is -1 or 1, then
your J3:J1000 must be sorted in ascending or descending order.

I have been collecting interesting posts, and Harlan Grover recently wrote
something that I think will help you. I will post my notes of Harlan's
comments, and I believe that should assist.

~~~~~~~~~

Title: Nth instance of LookValue

Created On: 9 Dec 2003

By: Harlan Grove

Keywords: Instance, Array, Range

Notes: XXX

Posted to Newsgroup: worksheet.functions

============================================================

The index of the Nth instance of LookValue from the 'beginning' of Array
searching 'forward' is given by the array formula

=IF(COUNTIF(Array,LookupValue)>=N,
SMALL(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)

The Nth instance from the 'end' of Array searching 'backward' is given by
the array formula

=IF(COUNTIF(Array,LookupValue)>=N,
LARGE(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)

Dispense with the IF() wrapper and use just the SMALL or LARGE function
calls if you can accept the #NUM! error value when there are fewer than N
instances of LookupValue in Array.

Harlan Grove said:
=IF(COUNTIF(Array,LookupValue)>=N,
SMALL(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)
Click to expand...
....

Doesn't work for arrays per se (not ranges). Change that to

=IF(SUMPRODUCT(--(Array=LookupValue))>=N,
SMALL(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)

if you're dealing with arrays rather than ranges.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

I think you want the first instance at the END of an array or range. So
hopefully one of those formulas should help you out. Given what you have
done so far, I think you are as well equipped as I am to finish your
challenge.

Good luck.

Regards,
Kevin


Jeff J said:
I have a range of cells containing a classification, e.g.
1, 2, 3 or 4. I have a macro to sort the cells based on
classification, now I'm trying to create a formula that
will allow me to find the first cell and the last cell
(cell addresses) containing, for example "2" in range J3-
J1000 in another workbook. I fixed the syntax of the
formula, but I get an N/A.

Is my approach feasible, do I have the formula structured
incorrectly or have I encountered a limitation in Excel?

The basic formula works, but I encounter the problem when
I try to integrate the INDIRECT function.

Here is the setup:
B C
1 Workbook.xls J3
2 Sheet1 J1000
3 "2"

Formula for first occurance of "2":
=CELL("address",INDEX(INDIRECT
("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2),MATCH(B3,(INDIRECT
("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2)),0)))

The formula for the last occurance is the same, except
the 0 becomes 1.

Any thoughts/fixes/comments would be greatly appreciated.

Thanks,
Jeff
Click to expand...
 
I am just correcting a typo. I mispelled Harlan's last name in the original
post. Names are important; I have made the correction in the version below.

Regards,
Kevin


I have been collecting interesting posts, and Harlan Grove recently wrote
something that I think will help you. I will post my notes of Harlan's
comments, and I believe that should assist.
 
I have a range of cells containing a classification, e.g.
1, 2, 3 or 4. I have a macro to sort the cells based on
classification, now I'm trying to create a formula that
will allow me to find the first cell and the last cell
(cell addresses) containing, for example "2" in range J3-
J1000 in another workbook. I fixed the syntax of the
formula, but I get an N/A. ...
The basic formula works, but I encounter the problem when
I try to integrate the INDIRECT function.

Here is the setup:
B C
1 Workbook.xls J3
2 Sheet1 J1000
3 "2"

Formula for first occurance of "2":
=CELL("address",INDEX(INDIRECT
("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2),MATCH(B3,(INDIRECT
("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2)),0)))

The formula for the last occurance is the same, except
the 0 becomes 1.
Click to expand...
...

Is the other workbook always open? If not, you can't use INDIRECT. If it is,
then while MATCH(.,.,0) will always give the first match, MATCH(.,.,1) won't
necessarily always give the last match. More reliable would be

Topmost match [array formula]:
=CELL("Address",OFFSET(INDIRECT("'["&$B1&"]"&$B2&"'!"&$C1),
MIN(IF(INDIRECT("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2)=B3,
ROW(INDIRECT($C1&":"&$C2))-ROW(INDIRECT($C1)))),0))

Bottommost match [array formula]:
=CELL("Address",OFFSET(INDIRECT("'["&$B1&"]"&$B2&"'!"&$C1),
MAX(IF(INDIRECT("'["&$B1&"]"&$B2&"'!"&$C1&":"&$C2)=B3,
ROW(INDIRECT($C1&":"&$C2))-ROW(INDIRECT($C1)))),0))
 
Thanks Harlan and Kevin!! I apologize for not getting
back sooner, I was detained on another project for a few
days and had to put this one on hold. So I'm back, eager
to try out your recommendations.

Jeff
 
Back
Top