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)

 

[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]

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)

 

[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]

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

 

[JMay]

Bob, Thanks Again
Jim May

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

to