dynamic offset

In the old 2020 spreadsheet, you could reference offset cells by entering
something like *+5.*-2 (which means 5 rows over and 2 rows down from the
current cell location) I've gone through all the help and questions I could
find, and the offset function is not what I'm really looking for.

Does excel have a function similar to above?

=INDIRECT(ADDRESS(ROW()+2,COLUMN()+5))

if you meant 2 rows down and 5 columns across
will return what is in that cell
if you just want the address

=ADDRESS(ROW()+2,COLUMN()+5)

--

Regards,

Peo Sjoblom


"[email protected]"
<[email protected]> wrote in message
news:[email protected]...
> In the old 2020 spreadsheet, you could reference offset cells by entering
> something like *+5.*-2 (which means 5 rows over and 2 rows down from the
> current cell location) I've gone through all the help and questions I
could
> find, and the offset function is not what I'm really looking for.
>
> Does excel have a function similar to above?

Excellent! Thank you, but a followup:

How would you write it in a macro? as in
" goto INDIRECT(ADDRESS(ROW()+2,COLUMN()+5)) " ?

"Peo Sjoblom" wrote:

> =INDIRECT(ADDRESS(ROW()+2,COLUMN()+5))
>
> if you meant 2 rows down and 5 columns across
> will return what is in that cell
> if you just want the address
>
> =ADDRESS(ROW()+2,COLUMN()+5)
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "[email protected]"
> <[email protected]> wrote in message
> news:[email protected]...
> > In the old 2020 spreadsheet, you could reference offset cells by entering
> > something like *+5.*-2 (which means 5 rows over and 2 rows down from the
> > current cell location) I've gone through all the help and questions I
> could
> > find, and the offset function is not what I'm really looking for.
> >
> > Does excel have a function similar to above?
>
>
>

Dim myCell as range

set myCell = activesheet.range("c9") 'for instance

mycell.offset(2,5).select

(maybe????)

[email protected] wrote:
>
> Excellent! Thank you, but a followup:
>
> How would you write it in a macro? as in
> " goto INDIRECT(ADDRESS(ROW()+2,COLUMN()+5)) " ?
>
> "Peo Sjoblom" wrote:
>
> > =INDIRECT(ADDRESS(ROW()+2,COLUMN()+5))
> >
> > if you meant 2 rows down and 5 columns across
> > will return what is in that cell
> > if you just want the address
> >
> > =ADDRESS(ROW()+2,COLUMN()+5)
> >
> > --
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "[email protected]"
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > In the old 2020 spreadsheet, you could reference offset cells by entering
> > > something like *+5.*-2 (which means 5 rows over and 2 rows down from the
> > > current cell location) I've gone through all the help and questions I
> > could
> > > find, and the offset function is not what I'm really looking for.
> > >
> > > Does excel have a function similar to above?
> >
> >
> >

--

Dave Peterson