+ Reply to Thread
Results 1 to 13 of 13

Form control question regarding blank cells

  1. #1
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Form control question regarding blank cells

    I'm using a form control drop down on a worksheet titled "Docket Sheets", which lists data from column "A" on my "Template" worksheet. I was curious if anyone is aware of a formula or other way to remove the dead spaces from the drop down so that it will only display the visible data options? The data could change in column "A", meaning I could have 1, 2 or 150 or more numbers in that column, but the data will always begin in cell A3 down. So I would like the dead spaces from A1 and A2 gone in the drop down as well as any empty cells after the data ends, etc. I have other formulas in the other worksheet that associate with both the Column A data along with data in Column C in the "template" worksheet. So when using the drop down menu and selecting an ID number the formulas in the "Docket" sheet associate row data, etc. So I cannot just use a formula to move the data in column "A" up to cell one, without opening a lot of other issues. I'm hoping this question is being asked in the correct forum and any suggestions would be appreciated if this is possible.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by lilsnoop; 08-16-2020 at 02:42 PM.

  2. #2
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Form control question regarding blank cells

    Change to formula for yr datavalidation list to:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This will remove all the blank rows from the dropdown list.
    Cheers!
    Tsjallie




    --------
    If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!

    If you think design is an expensive waste of time, try doing without ...

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Form control question regarding blank cells

    Tsjallie Thank you for that solution and I feel like an idiot as my example should have had a form control drop down and not the data validation type, which your formula works for. Unfortunately I can't use a data validation drop down due to the data associations I spoke about in my original post. My apologies for not having the correct sample drop down. Any chance you know of a way to do this with the form control. I've updated my sample workbook for the type of control I'm referring to.
    Attached Files Attached Files

  4. #4
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Form control question regarding blank cells

    In the Name Manager redefine Ducket as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Do note however that this assumes the numbers to be a consecutive list.
    So any empty cells will be make the list to small.
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Form control question regarding blank cells

    Tsjallie-I really appreciate your time and patience with this issue of mine. It is getting closer to be solved, but when I pick the ID number in column "A" it associates with data 2 rows up and I need it to associate with the data in the same row for my form to populate the correct data/association. I've attached a sample workbook with your formula that will explain better and demonstrate this issue better. Thanks again!
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Form control question regarding blank cells

    You need to add 2 to the value in B4, like
    =INDEX(Template!D:D,B4+2)

  7. #7
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Form control question regarding blank cells

    Fluff13-Thank you. I'm not entirely sure where I need to put that formula. Is it in my drop down form control function? or other. Thanks still trying to figure out the form control stuff. I apologize for my ignorance.

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Form control question regarding blank cells

    You need to change the formulae, not the dropdown.
    So in B12 you have
    =INDEX(Template!H:H,B4)
    and you need to change it to
    =INDEX(Template!H:H,B4+2)
    and do the same for the other similar formulae.

    Also the formula in B23 needs to become
    =IFERROR(INDEX(Template!$C$3:$C500, SMALL(IF(INDEX(Template!$A$3:$A$500,$B$4)=Template!$A$3:$A$500, ROW(Template!$A$3:$A$500)-ROW(Template!$A$3)+1), COLUMN(Template!A$4))),"")

  9. #9
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Form control question regarding blank cells

    Fluff13- Thank you so much for the clarification! I have one formula left that I need to adjust to associate:
    Please Login or Register  to view this content.
    . Can you advise how to adjust it? I tried "+2" after the B4 part but it didn't associate correctly.

    Appreciate the help!

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Form control question regarding blank cells

    That doesn't need to be changed, it should be correct as it is.

  11. #11
    Valued Forum Contributor
    Join Date
    03-17-2007
    Location
    Michigan
    MS-Off Ver
    Excel 2016
    Posts
    951

    Re: Form control question regarding blank cells

    Really appreciate all the help! Thanks Tsjallie & Fluff13!

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Form control question regarding blank cells

    You're welcome & thanks for the feedback.

  13. #13
    Forum Expert Tsjallie's Avatar
    Join Date
    09-15-2012
    Location
    NL
    MS-Off Ver
    2010, 2013, 2016
    Posts
    2,077

    Re: Form control question regarding blank cells

    Glad I could @lilsnoop. Thx for the rep.

+ 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. Button (Form Control) Question
    By EdwardSnowden in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-06-2019, 09:33 AM
  2. VBA question for Scroll Bar (form control)
    By mgilani in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2016, 11:36 AM
  3. control end with blank cells
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 11-11-2014, 03:07 PM
  4. Form Control Button asking a question and filling the answer to a cell?
    By utrolig in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-19-2014, 02:55 PM
  5. [SOLVED] A big ask! form control question
    By mario0102 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-28-2013, 03:44 AM
  6. Insert Blank date values into Access from text form control
    By mhm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-06-2013, 11:52 AM
  7. Replies: 6
    Last Post: 04-10-2012, 04:50 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