Offset and Indirect functions
 

Hi,
I find Offset and Indirect functions very confusing to me
and MS help just sucks on explaining these.

Can someone point me to a place with examples explanations
etc. where I can get a good sense of these?

Thanks

Hi
for INDIRECT see:
http://www.dicks-blog.com/archives/2...heet-function/
and
http://www.dicks-blog.com/archives/2...oks/trackback/

--
Regards
Frank Kabel
Frankfurt, Germany

Thrava wrote:
Hi,
I find Offset and Indirect functions very confusing to me
and MS help just sucks on explaining these.

Can someone point me to a place with examples explanations
etc. where I can get a good sense of these?

Thanks

In its simplest form:
For the Offset function I like to think of it as "saying in English"
"Bring back the value which can be found at..."
1st if you want to obtain the Value "IN ONLY ONE CELL", then
for example say in b4 you enter = Offset($B$5,2,4)
the result? -- Cell B4 will "bring back/return" the value found
starting by
anchoring itself on Cell $B$5, and then "moving" (first) row-wise
from B5 (in this case) down 2 rows (to B7); then "moving" (second)
column-wise
from B5 (in this case) to-the-right 4 Columns (now to F7)...
So your cell B4 reflects the Value in Cell F7. Done.

Offset continued...
You can also "bring-back" a range, say to enclose in the SUM() function;
This is where you add more arguments to the same above example.
In our example If I were to use
=Sum(Offset($B$5,2,4,3,5)) then the first part Offset($B$5,2,4) holds true
but
with the added 3, and 5 you are instructing Offset to "bring-back" a range
or table,
and in this case a table (of Values) 3 X 5 (3rows down and 5 columns across)
In our case here, range F7:J9.

I'll let someone else do the Indirect()...

HTH (Hope this Helps)

Jim May



"Thrava" wrote in message
...
Hi,
I find Offset and Indirect functions very confusing to me
and MS help just sucks on explaining these.

Can someone point me to a place with examples explanations
etc. where I can get a good sense of these?

Thanks

Thank you Frank

-----Original Message-----
Hi
for INDIRECT see:
http://www.dicks-blog.com/archives/2004/05/21/indirect-
worksheet-function/
and
http://www.dicks-blog.com/archives/2004/12/01/indirect-
and-closed-workbooks/trackback/

--
Regards
Frank Kabel
Frankfurt, Germany

Thrava wrote:
Hi,
I find Offset and Indirect functions very confusing to
me
and MS help just sucks on explaining these.

Can someone point me to a place with examples
explanations
etc. where I can get a good sense of these?

Thanks


.

Jim,
Thank you for taking the time to explain this to me.

-----Original Message-----
In its simplest form:
For the Offset function I like to think of it as "saying
in English"
"Bring back the value which can be found at..."
1st if you want to obtain the Value "IN ONLY ONE CELL",
then
for example say in b4 you enter = Offset($B$5,2,4)
the result? -- Cell B4 will "bring back/return" the
value found
starting by
anchoring itself on Cell $B$5, and then "moving" (first)
row-wise
from B5 (in this case) down 2 rows (to B7); then "moving"
(second)
column-wise
from B5 (in this case) to-the-right 4 Columns (now to
F7)...
So your cell B4 reflects the Value in Cell F7. Done.

Offset continued...
You can also "bring-back" a range, say to enclose in the
SUM() function;
This is where you add more arguments to the same above
example.
In our example If I were to use
=Sum(Offset($B$5,2,4,3,5)) then the first part Offset
($B$5,2,4) holds true
but
with the added 3, and 5 you are instructing Offset
to "bring-back" a range
or table,
and in this case a table (of Values) 3 X 5 (3rows down
and 5 columns across)
In our case here, range F7:J9.

I'll let someone else do the Indirect()...

HTH (Hope this Helps)

Jim May



"Thrava" wrote in
message
...
Hi,
I find Offset and Indirect functions very confusing to
me
and MS help just sucks on explaining these.

Can someone point me to a place with examples
explanations
etc. where I can get a good sense of these?

Thanks



.