+ Reply to Thread
Results 1 to 6 of 6

Problems dragging down Indirect formula referring to a range

  1. #1
    Registered User
    Join Date
    02-12-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    23

    Problems dragging down Indirect formula referring to a range

    Dear all

    I have a problem writing the indirect formula correctly so that I can drag it down i.e. automatically increase row numbers even though it refers to a range. My current syntax is like this:

    INDIRECT($AS$6 &"$G" &Row()+69 &":$BB" &Row()+69)

    It is integrated into an Index-match formula:

    =INDEX(INDIRECT($AS$6 &"$G$72:$BB$72");MATCH(INDEX(INDIRECT($AS$6 &"$G" &Row()+69 &":$BB" &Row()+69);MATCH(TRUE;INDEX(ISNUMBER(INDIRECT($AS$6 &"$G73:$BB73"));0);0));INDIRECT($AS$6 &"$G73:$BB73");0))

    I hope someone could figure this one out!

    Thanks a lot!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Problems dragging down Indirect formula referring to a range

    H _hs_,

    row()+69 will increase row number automatically upon drag. Little difficult to figure out the problem without excel workbook, please upload. Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    02-12-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Problems dragging down Indirect formula referring to a range

    Thanks a lot for looking at this!

    Here is a sample sheet.

    The problem is located in column D sheet 1 ("tabelle1"), whereas column B & C contains the values it is supposed to return.

    Hope it is understandable:-)

    Thank you so much!
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Problems dragging down Indirect formula referring to a range

    Okay... Use below formula :

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    INDIRECTPROBLEM.xlsx

    where 'data' is a defined name. Press ctrl + F3 to see the logic used in defined name. Thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Registered User
    Join Date
    02-12-2013
    Location
    Denmark
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Problems dragging down Indirect formula referring to a range

    Thanks a lot for this!

    It is definitely a good solution in this specific case:-)
    The jury is out on whether it is possible to code the indirect formula to work for "dynamic" ranges, however - any thoughts on this? Is it possible at all?

    Have a nice day!

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Problems dragging down Indirect formula referring to a range

    The jury is out on whether it is possible to code the indirect formula to work for "dynamic" ranges, however - any thoughts on this?
    Yes.. it is possible and that's what I did.
    Instead of using defined name, we can use the formula of defined name in your main formula but that would be little lengthy and difficult to manage/ understand in future

    I think using define name is best / neat way


    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1