+ Reply to Thread
Results 1 to 13 of 13

Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

  1. #1
    Registered User
    Join Date
    10-18-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    7

    Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Just Joined the site (Hi Everyone :D )

    I am trying to create a drop-down menu on worksheet 1, where the data is on worksheet 2.

    The Data is based in column 'A' ONLY, BUT has blank cells which the drop down menu picks up.

    Example data

    1 - [Text here]
    2 - [blank cell]
    3 - [Text here]
    4 - [Blank Cell]
    5 - [Blank Cell]
    6 - [Text here]

    Drop down menu currently displaying the same as above.

    What i want it to look like is;

    1 - [Text here]
    3 - [Text here]
    6 - [Text here]

    So it will ignore all the blank cells and just process to the next cell populated with text.

    i have tried using the following formulae;

    =IFERROR(INDEX($A$2:$A$40,AGGREGATE(15,3,ROW($A$2:$A$40)-ROW($A$1)/($A$2:$A$40<>""),ROWS($A$2:A2))),"")

    But when i enter it into data validation as a list, i get an error "The list source must be a delimited list, or a reference to a single row or column.

    Any help would be appreciated

    EDIT: Tried to insert my worksheet, but can't exactly find out how ( paperclip icon does not allow me to do anything.
    Last edited by RaveStarStyle; 10-18-2018 at 09:51 PM. Reason: tried to post attachment

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Try this below:
    Apply the following formula to copy and paste only the non-blank cell values, please enter this formula: =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(B:B,SMALL(IF($B$1:$B$13<>"",ROW($B$1:$B$13)),ROWS($D$1:D1))))) into a blank cell D1 for example, and then press Ctrl + Shift + Enter keys together to get the result.

    Create data validation to above pasted cells.

    Read more here:
    https://www.extendoffice.com/documen...ore-blank.html
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Registered User
    Join Date
    10-18-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Thanks for the quick reply, I edited the above formula and it partially works, i can make a separate list then use that new list to make a drop down menu with no spaces.... but i don't want an additional list in the table as it looks messier.

    Im looking for a method that from the list with the blank cells going straight to a drop-down menu with no blank cells showing. If you have any ideas or suggestions im willing to hear you out.


    P.S the etided formulae i used with =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$40<>"",ROW($A$1:$A$40)),ROWS($A$1:A1))))) . If i failed to do anything correctly let me know. The drop down menu was still the same as when i used the other formulae (showing blank cells).
    Last edited by AliGW; 10-19-2018 at 07:26 AM. Reason: Please do not quote the post immediately before your response - it's just clutter!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    If you are worried about the way it looks, put it on a hidden tab or in a hidden column.

    Please amend your user profile to give some clue as to your location, as helpers will use this information when tailoring their responses for your locale. UK, USA, India, Europe, etc. is specific enough.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    10-18-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Hi Ali,

    I was thinking that i may have to put it on a seperate worksheet to have it out the way, i would like to hope that there is an actual formula could be used to achieve what im looking for tho. i will keep the post open for now incase anyone else has any ingenious ideas on how to achieve this tho. .

    P.S edited profile to add country location.
    Last edited by AliGW; 10-19-2018 at 07:38 AM. Reason: Please don't quote whole posts.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    There may be a way (I will have a look), however, you do not need to worry about helper columns. Sometimes they make maintaining spreadsheets easier going forward, so don't get so caught up in the desire to have it all in one clever formula and lose sight of the logistics!

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Your only options are an actual range, or a literal comma-separated string. You could do the latter with VBA but it will cause you problems if the string could go over 255 characters.
    Rory

  8. #8
    Registered User
    Join Date
    10-18-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    I was trying to adapt the above lookup formula a previously used that worked on the same worksheet to another worksheet, but i am having trouble getting it done correctly. spent a few hours on it to no avail.

    to formula i ended up with is as follows;

    =LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX('All Items'!A:A,SMALL(IF('All Items'!$A$2:$A$22<>"",ROW('All Items'!$A$2:$A$22)),ROWS('All Items'!$A$2:A2-ROW('All Items'!$A$1))))))

    where All Items is the page with the array on. Im trying to get it on a worksheet named 'Filtered Catagory' in cell A1.

    P.S still cant upload docs. it shows to a blank white box with no options.

    EDIT. FILE EXAMPLE SHOULD BE ATTACHED.
    Attached Files Attached Files
    Last edited by RaveStarStyle; 10-21-2018 at 03:02 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  10. #10
    Registered User
    Join Date
    10-18-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Thanks Ali i managed to get a sample data file uploaded in the previous reply. would greatly appreciate if anyone has a solution

    Kind Regards,

    Marc.

  11. #11
    Registered User
    Join Date
    10-18-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    BUMP. Still no luck with this. spread sheet attached to see what im refering to,
    Attached Files Attached Files

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Try changing the formula in DV manager to this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    That will take care of the blank rows.

    I can't tell what you are wanting to do with the rest of the workbook.
    Last edited by FlameRetired; 10-22-2018 at 08:54 PM.
    Dave

  13. #13
    Registered User
    Join Date
    10-18-2018
    Location
    United Kingdom
    MS-Off Ver
    2016
    Posts
    7

    Re: Dynamic Drop Down Menu Ignoring Blank Cells (Dynamic)

    Hi flame,

    I tried the formula above and data validation rejected it, as you cant use other worksheets or something along those lines.

    what i need is the crafting calc, drop down menus to ignore the extra blank spaces from the filtered list.

    mainly the drop down in cell A2 is the problem as cell B2 is using =INDIRECT(A2) to get its data.

+ 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. Ignoring blanks in dynamic data validation drop-down
    By joekhub in forum Excel General
    Replies: 3
    Last Post: 04-18-2017, 01:23 PM
  2. [SOLVED] Add values from diffrent sheets to drop down menu and ignoring blank cells.
    By RinorM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2014, 02:37 AM
  3. How do I create a dynamic drop down menu from horizontal cells?
    By jocelyn.gillespie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-14-2014, 04:44 PM
  4. Dynamic Ranges - Ignoring Blank Cells with Underlying Formulas
    By jmm722 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 04:14 PM
  5. [SOLVED] Dynamic drop down menu
    By madhatter40 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-18-2013, 06:33 PM
  6. [SOLVED] Data validation using dynamic range ignoring blank formulas
    By gerainta in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-22-2013, 09:24 AM
  7. Dynamic Chart with Drop-Down Menu
    By Katrotat in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-30-2011, 05:30 PM

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