Passing Cell Address to Offset
 

The following function returns the cell address I want to use in an offset
function:

=ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)

however, if I imbed this function in the first argument of the offset
function i.e.

OFFSET(ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4),-9,0,1,1)

I get "The formula you typed contains an error." Any suggestions?

Try

=OFFSET(INDIRECT(ADDRESS(MATCH(C$8,C$10:C$28,0)+8, D$1,4)),-9,0,1,1)

having said that there is probably better to skip INDIRECT and ADDRESS and
use the match and the other data directly in either OFFSET or INDEX, this is
like swimming to the other side of the river to get a drink

For instance this

=OFFSET(A1,MATCH(C$8,C$10:C$28,0)+8-1,D1-1)

would return what's in

=ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)

meaning

=OFFSET(A1,MATCH(C$8,C$10:C$28,0)-2,D1-1)

will return

=OFFSET(INDIRECT(ADDRESS(MATCH(C$8,C$10:C$28,0)+8, D$1,4)),-9,0,1,1)




Regards,

Peo Sjoblom


"Bob" wrote:

The following function returns the cell address I want to use in an offset
function:

=ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)

however, if I imbed this function in the first argument of the offset
function i.e.

OFFSET(ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4),-9,0,1,1)

I get "The formula you typed contains an error." Any suggestions?

Use match to find the row and then use offset from a given to use the match.
look at help for offset to see what I mean
=offset(a1,matchrow,column)

--
Don Guillett
SalesAid Software

"Bob" wrote in message
...
The following function returns the cell address I want to use in an offset
function:

=ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4)

however, if I imbed this function in the first argument of the offset
function i.e.

OFFSET(ADDRESS(MATCH(C$8,C$10:C$28,0)+8,D$1,4),-9,0,1,1)

I get "The formula you typed contains an error." Any suggestions?