ExcelBanter - Display values set in a range??

For starters, your reference argument is invalid as you don't have a letter
indicating which column the range is referring to as the last column. Did
you mean A1:A150, A1:IV150 (Which would be the same as 1:150), or some other
reference? If you meant entire rows, then you wouldn't be able to offset
the reference by any column difference as the function would return '#REF!'
error message for attempting to extend the range to one that the worksheet
wouldn't allow.

Often times, when I'm attempting to display something within a particular
cell from another cell, I end up using the INDIRECT, and ADDRESS functions
like:

Example: If I am looking for a value in A5 within the O column to return
the result of the P column, and I need to have the first 15 rows of the P
column starting with the row that the MATCH function returned as a valid row
number within it's range returned to the A6:A20 range, I would type the
following formula below into the cell of A6, then copy the formula down to
A20.

=INDIRECT(ADDRESS(MATCH($A$5,O:O,0)+ROWS()-6,COLUMNS(P:P)))

This can be time consuming if you have a lot of cells like this, which then
you can shorten the range that the MATCH function is looking within, but be
warned, it's only going to return the Nth row within that range that if
found the result. If no result is found, it will return an '#N/A!' error
message, which case, if you have any possibility of expecting that to
happen, you would need to use the MATCH function within an ISERROR function
to catch this error and direct it otherwise via an IF function, such as the
following:

=IF(ISERROR(MATCH($A$5,O:O,0)),"",INDIRECT(ADDRESS (MATCH($A$5,O6:O3000,0)+RO
WS()-1,COLUMNS(P:P))))

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"rpp114" wrote in
message ...

I have selected a dynamic range that is defined similar to Values =
offset(a1:150,index_1,index_2,15,1). I want to display the numbers
saved in the Values range. I can get them to display, but when they do
they only appear in the cooresponding row that is looked up. How can I
show the values of the range, where no matter the first value of it, it
will show up in a defined cell?

Basically, I want the numbers stored in the Values range to display in
a1:a15 no matter what the vertical offset is.

If this makes sense thanks, if not I will attempt to clarify.

--
rpp114
------------------------------------------------------------------------
rpp114's Profile:
http://www.excelforum.com/member.php...o&userid=34131
View this thread: http://www.excelforum.com/showthread...hreadid=539003