So close... now how to turn TEXT to a Cell Reference

[JulieMK]

Excel 2002 for XP... this is regarding my Bill of Materials Date Rollup post
earlier.

Using MATCH and ROW, I now am able to determine where one assembly of parts
(or rows beneath) ends, and the next begins.

I am trying to get this formula to do a MAX on. I need the results of these
formulas, to become the END of my array.

I even have formulas to cocatenate the results into something like:
A19

Unfortunately, THIS IS TEXT. So nothing happens. Is there any way to make my
TEXT operate like it says, A19?

If this is not the way to go about it, can anyone tell me how I can use the
results of MATCH (which is a number, like 19, as in, it's 19 rows down, but
NOT a cell reference...) so that my =MAX(a1: ) array knows to stop?

If the reason I am getting no responses is because I am somehow not making
sense - please ask me further questions. I need some help very badly, nearly
40 hours into this investment. Would love to see it through! ANY help
appreciated!

 

[Jim Thomlinson]

This should do the trick. It allows you to define an array of cells based on
a starting point.

A1, 0 rows over,0 rows down, size 19 rows, size 1 column

=max(offset(A1, 0,0,match(...), 1))

 

[Gary''s Student]

Very nice Jim.
--
Gary''s Student - gsnu2007xx

This should do the trick. It allows you to define an array of cells based on
a starting point.

A1, 0 rows over,0 rows down, size 19 rows, size 1 column

=max(offset(A1, 0,0,match(...), 1))