Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
So close... now how to turn TEXT to a Cell Reference
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
So close... now how to turn TEXT to a Cell Reference
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)) -- HTH... Jim Thomlinson "JulieMK" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
So close... now how to turn TEXT to a Cell Reference
Very nice Jim.
-- Gary''s Student - gsnu2007xx "Jim Thomlinson" wrote: 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)) -- HTH... Jim Thomlinson "JulieMK" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
turn off/on automatic calc upon open/close workbook? | Excel Programming | |||
How turn on excel functn, dbleclick on cell 2 show reference? | Setting up and Configuration of Excel | |||
How do I turn around 'Circulation Reference' msgbox? | Excel Programming | |||
Excel-can't turn off multiple cell selection or add text/numbers | Excel Discussion (Misc queries) | |||
How can I reference data or text within a text box to a cell in Ex | Excel Discussion (Misc queries) |