+ Reply to Thread
Results 1 to 7 of 7

Horizontal dynamic range not working

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    4

    Horizontal dynamic range not working

    I have tried several versions to get my horizontal dynamic range working in a dropdown-list elsewhere in my sheets but this does not work. I use Excel 365 in windows 7.

    My data is in sheet ‘methodes’ in a row from column C to H, for this case in row 8. I have other sets of data in other rows, these are for other dropdown-lists and they contain between 2 and 6 values, hence column C to H.

    Range: (C8:D8) works in the dropdown-list, but not if more than two values (of course)
    Range: (C8:H8) works but I get empty rows in my drop-down-lists if less than 6 values exist.
    Range: OFFSET(methodes!C8,,,,COUNTA(methodes!C8:H8)) gives no data in dropdown-list
    Range: $C8:INDEX($C8:$H8,MATCH(TRUE,INDEX(C8:H8="",0),0)-1), no data in the list
    Range: (C8:INDEX(C8:H8,COUNTA(C8:H8))), no data in the list.

    Some of these give me one empty row in the drop-down-list, but I didn’t note which. None of these give me exactly what I want. What am I doing wrong?

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Horizontal dynamic range not working

    try

    =Sheet1!$C$8:INDEX(Sheet1!$C$8:$H$8,1,COUNTA(Sheet1!$C$8:$H$8))

    Your last one is closest but you need to use the COUNTA value as number of columns rather than rows.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    04-11-2013
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Horizontal dynamic range not working

    Dear Andy Pope, it works to my surprise and relief! The reason is not CountA though, since the values for these particular dropdownlists are numbers and 'count' works too.
    But thanks to your answer I now discover that the '$'-signes are mandatory. I did not use them because I have several dropdowns and I copied their range-formula from one ro to the next. Now I did the same, but added the $-signs afterwords and it is working like a charm. Thank you very much for your quick answer.

    Also for some reason sometimes the range is converted to the same formula but between quotes!

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Horizontal dynamic range not working

    No the real problem was you use INDEX and specified the number of rows to use rather than columns.

    this
    C8:INDEX(C8:H8,COUNTA(C8:H8))

    should have been
    C8:INDEX(C8:H8,1,COUNTA(C8:H8))

  5. #5
    Registered User
    Join Date
    04-11-2013
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Horizontal dynamic range not working

    You are right about messing up rows and columns there, thank you for pointing that out.
    But I just changed the working range into a list with just one empty row, by deleting just the $-signs. After re-inserting the $-signs, the list is visible again.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Horizontal dynamic range not working

    My bad, you need the $ in order to keep the references absolute. Other wise the named range will be relevant and keep on changing.

    Bit more info here
    http://www.jkp-ads.com/articles/excelnames03.asp

  7. #7
    Registered User
    Join Date
    04-11-2013
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Horizontal dynamic range not working

    Yes, now I get it. I have been blind for this while I actually knew this feature. Pfffew. Thanks again for pointing this out, you saved me a lot of time and frustration. Now I will read the forum rules to see if I can find how to put the 'solved' sign on.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Finding the (Dynamic) End of a Horizontal Range
    By Lemmy Kickit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2016, 06:52 PM
  2. Dynamic Chart Range for Horizontal values
    By phill_howz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-03-2014, 11:47 AM
  3. PC vs MAC dynamic range not working
    By CluelessCrowbarr in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 16
    Last Post: 03-07-2014, 05:03 AM
  4. [SOLVED] Dynamic name range not working correctly
    By Nitefox in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 10
    Last Post: 12-14-2013, 09:09 PM
  5. [SOLVED] Sort dynamic range alphabetically - cant get it working
    By Taktiker in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-08-2013, 11:35 AM
  6. How do I know if Dynamic Range is working?
    By livemusic in forum Excel General
    Replies: 9
    Last Post: 07-02-2011, 11:16 PM
  7. sumproduct w/horizontal range not working
    By dcd123 in forum Excel Formulas & Functions
    Replies: 66
    Last Post: 09-06-2005, 07:05 PM

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