Bob, Thanks Again
Jim May
"Bob Phillips" wrote in message
...
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" 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" wrote in message
...
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" 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" wrote in message
...
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" 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,
|