Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|