Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INDIRECT and OFFSET oddity (Excel 97-SR2)

    Can anyone offer any insight into an oddity that I seem to have encountered. I often use the OFFSET idea to create an extensible lists or tables:

    = OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$20),3 )

    These work brilliantly for all kinds of stuff.. The current project I'm working on needs to switch between data tables dependent upon user selections. All of the formulas to do that work well to. However, if I try to use INDIRECT to access the table or list, it crashes and burns, BUT..... the exact same command will work if I substitute the INDIRECT(cell_ref) with the name of the Named Range created by the above OFFSET command. e.g.

    A1 = tblList
    A2 = SUM(INDEX(INDIRECT(A1),0,2))
    A3 = SUM(INDEX(tblList,0,2))

    A2 will produce the #REF! error
    A3 will produce the correct answer

    If I create a 'static' named range called tblList then BOTH commands in A2 and A3 above will work correctly. Ergo, it's the combination of OFFSET and INDIRECT that appears to cause the problem? The attached sheet has a worked example.

    What am I doing wrong and/or, is there a work around?

    As usual, thanks in advance for any assistance.

    Regards
    Peter

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: INDIRECT and OFFSET oddity (Excel 97-SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Peter

    Could it be because F$9 evaluates to "tblTest2" vs =SUM(INDEX(tblTest2,0,2)) notice no<font color=red> "</font color=red>?

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Is extensible a new Microsoft Lingo word? <img src=/S/drop.gif border=0 alt=drop width=23 height=23> <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15> <img src=/S/flee.gif border=0 alt=flee width=25 height=25>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT and OFFSET oddity (Excel 97-SR2)

    Hi Wassim

    No.. I don't think so... otherwise the formulas in E10:E12 wouldn't work either? The ones in F10:F12 are exactly the same except that they refer to a named range created by the OFFSET formula, therefore... It must be the OFFSET formula that is causing the problem? Weird!

    Regards
    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: INDIRECT and OFFSET oddity (Excel 97-SR2)

    Apparently, INDIRECT and dynamic named ranges don't go together, see for example here.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT and OFFSET oddity (Excel 97-SR2)

    Hi Hans

    Darn! OK.... Plan B........

    Regards
    Peter

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: INDIRECT and OFFSET oddity (Excel 97-SR2)

    I have noticed this behavior also. I have worked under the assumption that INDIRECT expects something that "looks" like a range (a range address or a range name) but not something that is a range object (like OFFSET yields).

    It would be "equivalent" to passing a string with a range name as a parameter for a function. The program expects a "string" and would use something like
    range(strPassed)
    in the code

    You could not pass the actual range object since the code would cause an error.

    There many ways around this. How exactly depends on what your "indirect" is really doing. Some could actually be more efficient and use less range names to accomplish it.

    If you are naming "ranges" that are just a group of columns, Your formula in the workbook could use OFFSET and then use INDIRECT to get the column number (or even use match to get a column number)

    If your "names" change all the parameters in offset: you could create a table of values for each of your desired "range names" and use your cell (currently used for INDIRECT to lookup the 5 values in the table and use those in the formula.

    This way instead of having multiple named ranges you have only hte named range of the lookup table. It is actually more dynamic since new values can be added to the lookup table without haveing to create more named ranges.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT and OFFSET oddity (Excel 97-SR2)

    Hi Steve

    Thanks for the response and suggested work rounds, I'll use them on future projects I'm sure.. In this case the work round is really simple, I just won't use the OFFSET method of creating the range name.

    The INDIRECT function is the most important part of the model I am building. The users need to be able to change the sequence that a set of data tables are analysed in. E.g. Table1, Table2, Table3, etc or Table3, Table9, Table6, etc. The INDIRECT function supports this brilliantly. So, in this instance manually defining the name range of each of the data tables once, and re-naming on the rare occasion once might change, is actually a very small price to pay for the real functionality that I want.

    Many thanks again

    Regards
    Peter

  8. #8
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INDIRECT and OFFSET oddity (Excel 97-SR2)

    That's right. INDIRECT does not accept array objects, only range objects. A name created by means of dynamic formulas with Offset or Index is de facto an array object.
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •