Offset() relative addressing Row parameter

[Jim May]

On sheet1 cell F90 I have =Offset('Sheet2'!$E$22,1,0)

I'd like to copy F90 to G90:M90
whereby the row parameter in F90
will increase by 1 with each column
for example J90 would have in it:
the equivalent of:
=Offset('Sheet2'!$E$22,5,0)

How should I alter my F90 to accomplish?
TIA,

[Bob Phillips]

Change the formula to

=Offset('Sheet2'!$E$22,Column(A1),0)

and then copy across.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" <[email protected]> wrote in message
news:A5xGd.30248$jn.20884@lakeread06...
> On sheet1 cell F90 I have =Offset('Sheet2'!$E$22,1,0)
>
> I'd like to copy F90 to G90:M90
> whereby the row parameter in F90
> will increase by 1 with each column
> for example J90 would have in it:
> the equivalent of:
> =Offset('Sheet2'!$E$22,5,0)
>
> How should I alter my F90 to accomplish?
> TIA,
>
>
>

[JMay]

Thanks Bob, I'll use your suggestion<< I also understand it's use>>.
At times however / somehow (???) in similar formulas I've seen the use of
either:
(1:1)
Indirect("1:1")
Rows(1:1)
Row(1:1)
etc...??

and somehow the "1" character
gets incremented to 2, 3, 4 etc.
Can something like this (not exactly what I've offered -- since none work)
be used to accomplish the same result as the Column(a1) parameter?
Thanks,

"Bob Phillips" <[email protected]> wrote in message
news:Os$oq$%23%[email protected]...
> Change the formula to
>
> =Offset('Sheet2'!$E$22,Column(A1),0)
>
> and then copy across.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Jim May" <[email protected]> wrote in message
> news:A5xGd.30248$jn.20884@lakeread06...
> > On sheet1 cell F90 I have =Offset('Sheet2'!$E$22,1,0)
> >
> > I'd like to copy F90 to G90:M90
> > whereby the row parameter in F90
> > will increase by 1 with each column
> > for example J90 would have in it:
> > the equivalent of:
> > =Offset('Sheet2'!$E$22,5,0)
> >
> > How should I alter my F90 to accomplish?
> > TIA,
> >
> >
> >
>
>

[Bob Phillips]

Jim,

Rows(1:n) is often used in array formula to pass an array of numbers to a
formula, such as
=SUM(SMALL(A1:A10,ROW(1:5)))
which picks up the 5 smallest values and SUMs them.

In this formula, you could use
=OFFSET(Sheet11!$E$22,ROW(1:1),0)
in the first cell, but when you copy it across to column G, it won't update,
as you are on the same row. So, clearly, you might think we can use
=OFFSET(Sheet11!$E$22,COLUMN(1:1),0)
but you cannot as that returns an array with value for every row for that
column (all 65536 of them). It also doesn't increment across, as there is no
column letter, so we can resolve both these by using COLUMN(A1), that is
=OFFSET(Sheet11!$E$22,COLUMN(A1),0)

BTW, You could also use
=OFFSET(Sheet11!$E$22,COLUMN(1)-5,0)
but I thought the former was a bit more resilient.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JMay" <[email protected]> wrote in message
news:uczGd.30270$jn.20965@lakeread06...
> Thanks Bob, I'll use your suggestion<< I also understand it's use>>.
> At times however / somehow (???) in similar formulas I've seen the use of
> either:
> (1:1)
> Indirect("1:1")
> Rows(1:1)
> Row(1:1)
> etc...??
>
> and somehow the "1" character
> gets incremented to 2, 3, 4 etc.
> Can something like this (not exactly what I've offered -- since none work)
> be used to accomplish the same result as the Column(a1) parameter?
> Thanks,
>
> "Bob Phillips" <[email protected]> wrote in message
> news:Os$oq$%23%[email protected]...
> > Change the formula to
> >
> > =Offset('Sheet2'!$E$22,Column(A1),0)
> >
> > and then copy across.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Jim May" <[email protected]> wrote in message
> > news:A5xGd.30248$jn.20884@lakeread06...
> > > On sheet1 cell F90 I have =Offset('Sheet2'!$E$22,1,0)
> > >
> > > I'd like to copy F90 to G90:M90
> > > whereby the row parameter in F90
> > > will increase by 1 with each column
> > > for example J90 would have in it:
> > > the equivalent of:
> > > =Offset('Sheet2'!$E$22,5,0)
> > >
> > > How should I alter my F90 to accomplish?
> > > TIA,
> > >
> > >
> > >
> >
> >
>
>

[Jim May]

Bob:
Thanks for a perfect explanation of the contrasting methods and the issues
involved with each. Good learning experience here.
Jim



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Jim,
>
> Rows(1:n) is often used in array formula to pass an array of numbers to a
> formula, such as
> =SUM(SMALL(A1:A10,ROW(1:5)))
> which picks up the 5 smallest values and SUMs them.
>
> In this formula, you could use
> =OFFSET(Sheet11!$E$22,ROW(1:1),0)
> in the first cell, but when you copy it across to column G, it won't
update,
> as you are on the same row. So, clearly, you might think we can use
> =OFFSET(Sheet11!$E$22,COLUMN(1:1),0)
> but you cannot as that returns an array with value for every row for that
> column (all 65536 of them). It also doesn't increment across, as there is
no
> column letter, so we can resolve both these by using COLUMN(A1), that is
> =OFFSET(Sheet11!$E$22,COLUMN(A1),0)
>
> BTW, You could also use
> =OFFSET(Sheet11!$E$22,COLUMN(1)-5,0)
> but I thought the former was a bit more resilient.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JMay" <[email protected]> wrote in message
> news:uczGd.30270$jn.20965@lakeread06...
> > Thanks Bob, I'll use your suggestion<< I also understand it's use>>.
> > At times however / somehow (???) in similar formulas I've seen the use
of
> > either:
> > (1:1)
> > Indirect("1:1")
> > Rows(1:1)
> > Row(1:1)
> > etc...??
> >
> > and somehow the "1" character
> > gets incremented to 2, 3, 4 etc.
> > Can something like this (not exactly what I've offered -- since none
work)
> > be used to accomplish the same result as the Column(a1) parameter?
> > Thanks,
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:Os$oq$%23%[email protected]...
> > > Change the formula to
> > >
> > > =Offset('Sheet2'!$E$22,Column(A1),0)
> > >
> > > and then copy across.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Jim May" <[email protected]> wrote in message
> > > news:A5xGd.30248$jn.20884@lakeread06...
> > > > On sheet1 cell F90 I have =Offset('Sheet2'!$E$22,1,0)
> > > >
> > > > I'd like to copy F90 to G90:M90
> > > > whereby the row parameter in F90
> > > > will increase by 1 with each column
> > > > for example J90 would have in it:
> > > > the equivalent of:
> > > > =Offset('Sheet2'!$E$22,5,0)
> > > >
> > > > How should I alter my F90 to accomplish?
> > > > TIA,
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[Bob Phillips]

Jim,

Interestingly, there is a reply today that uses this technique. It is
entitled 'Multiple VLOOKUP', posted by Rody. If you look at it, you will see
the reply uses Row(A1:A8). I have looked too closely at it, but it seems to
me it would work just as well with Row(1:8).

Regards

Bob

"Jim May" <[email protected]> wrote in message
news:CMBGd.30397$jn.17358@lakeread06...
> Bob:
> Thanks for a perfect explanation of the contrasting methods and the issues
> involved with each. Good learning experience here.
> Jim
>
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > Jim,
> >
> > Rows(1:n) is often used in array formula to pass an array of numbers to
a
> > formula, such as
> > =SUM(SMALL(A1:A10,ROW(1:5)))
> > which picks up the 5 smallest values and SUMs them.
> >
> > In this formula, you could use
> > =OFFSET(Sheet11!$E$22,ROW(1:1),0)
> > in the first cell, but when you copy it across to column G, it won't
> update,
> > as you are on the same row. So, clearly, you might think we can use
> > =OFFSET(Sheet11!$E$22,COLUMN(1:1),0)
> > but you cannot as that returns an array with value for every row for
that
> > column (all 65536 of them). It also doesn't increment across, as there
is
> no
> > column letter, so we can resolve both these by using COLUMN(A1), that is
> > =OFFSET(Sheet11!$E$22,COLUMN(A1),0)
> >
> > BTW, You could also use
> > =OFFSET(Sheet11!$E$22,COLUMN(1)-5,0)
> > but I thought the former was a bit more resilient.
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "JMay" <[email protected]> wrote in message
> > news:uczGd.30270$jn.20965@lakeread06...
> > > Thanks Bob, I'll use your suggestion<< I also understand it's use>>.
> > > At times however / somehow (???) in similar formulas I've seen the use
> of
> > > either:
> > > (1:1)
> > > Indirect("1:1")
> > > Rows(1:1)
> > > Row(1:1)
> > > etc...??
> > >
> > > and somehow the "1" character
> > > gets incremented to 2, 3, 4 etc.
> > > Can something like this (not exactly what I've offered -- since none
> work)
> > > be used to accomplish the same result as the Column(a1) parameter?
> > > Thanks,
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:Os$oq$%23%[email protected]...
> > > > Change the formula to
> > > >
> > > > =Offset('Sheet2'!$E$22,Column(A1),0)
> > > >
> > > > and then copy across.
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Jim May" <[email protected]> wrote in message
> > > > news:A5xGd.30248$jn.20884@lakeread06...
> > > > > On sheet1 cell F90 I have =Offset('Sheet2'!$E$22,1,0)
> > > > >
> > > > > I'd like to copy F90 to G90:M90
> > > > > whereby the row parameter in F90
> > > > > will increase by 1 with each column
> > > > > for example J90 would have in it:
> > > > > the equivalent of:
> > > > > =Offset('Sheet2'!$E$22,5,0)
> > > > >
> > > > > How should I alter my F90 to accomplish?
> > > > > TIA,
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[JMay]

Bob, Thanks Again
Jim May

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Jim,
>
> Interestingly, there is a reply today that uses this technique. It is
> entitled 'Multiple VLOOKUP', posted by Rody. If you look at it, you will
see
> the reply uses Row(A1:A8). I have looked too closely at it, but it seems
to
> me it would work just as well with Row(1:8).
>
> Regards
>
> Bob
>
> "Jim May" <[email protected]> wrote in message
> news:CMBGd.30397$jn.17358@lakeread06...
> > Bob:
> > Thanks for a perfect explanation of the contrasting methods and the
issues
> > involved with each. Good learning experience here.
> > Jim
> >
> >
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > Jim,
> > >
> > > Rows(1:n) is often used in array formula to pass an array of numbers
to
> a
> > > formula, such as
> > > =SUM(SMALL(A1:A10,ROW(1:5)))
> > > which picks up the 5 smallest values and SUMs them.
> > >
> > > In this formula, you could use
> > > =OFFSET(Sheet11!$E$22,ROW(1:1),0)
> > > in the first cell, but when you copy it across to column G, it won't
> > update,
> > > as you are on the same row. So, clearly, you might think we can use
> > > =OFFSET(Sheet11!$E$22,COLUMN(1:1),0)
> > > but you cannot as that returns an array with value for every row for
> that
> > > column (all 65536 of them). It also doesn't increment across, as there
> is
> > no
> > > column letter, so we can resolve both these by using COLUMN(A1), that
is
> > > =OFFSET(Sheet11!$E$22,COLUMN(A1),0)
> > >
> > > BTW, You could also use
> > > =OFFSET(Sheet11!$E$22,COLUMN(1)-5,0)
> > > but I thought the former was a bit more resilient.
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "JMay" <[email protected]> wrote in message
> > > news:uczGd.30270$jn.20965@lakeread06...
> > > > Thanks Bob, I'll use your suggestion<< I also understand it's use>>.
> > > > At times however / somehow (???) in similar formulas I've seen the
use
> > of
> > > > either:
> > > > (1:1)
> > > > Indirect("1:1")
> > > > Rows(1:1)
> > > > Row(1:1)
> > > > etc...??
> > > >
> > > > and somehow the "1" character
> > > > gets incremented to 2, 3, 4 etc.
> > > > Can something like this (not exactly what I've offered -- since none
> > work)
> > > > be used to accomplish the same result as the Column(a1) parameter?
> > > > Thanks,
> > > >
> > > > "Bob Phillips" <[email protected]> wrote in message
> > > > news:Os$oq$%23%[email protected]...
> > > > > Change the formula to
> > > > >
> > > > > =Offset('Sheet2'!$E$22,Column(A1),0)
> > > > >
> > > > > and then copy across.
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Jim May" <[email protected]> wrote in message
> > > > > news:A5xGd.30248$jn.20884@lakeread06...
> > > > > > On sheet1 cell F90 I have =Offset('Sheet2'!$E$22,1,0)
> > > > > >
> > > > > > I'd like to copy F90 to G90:M90
> > > > > > whereby the row parameter in F90
> > > > > > will increase by 1 with each column
> > > > > > for example J90 would have in it:
> > > > > > the equivalent of:
> > > > > > =Offset('Sheet2'!$E$22,5,0)
> > > > > >
> > > > > > How should I alter my F90 to accomplish?
> > > > > > TIA,
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>