Consulting

Results 1 to 12 of 12

Thread: Force user to go only to next row in a spreadsheet

  1. 06-25-2009, 08:19 AM #1
    Gingertrees
    Gingertrees is offline
    VBAX Contributor Gingertrees's Avatar
    Joined
    Jul 2008
    Posts
    154
    Location

    Question Force user to go only to next row in a spreadsheet

    Hello,
    This is related but sufficiently different from this question:
    http://vbaexpress.com/forum/showthread.php?t=27315

    I just learned about Dynamic Named Ranges using COUNTA function, and they sure are neat. However, they can be emasculated by a lot of open space within that range. As in my example picture: when the data has rows between it, COUNTA omits several pieces of data. Then when the dynamic range is called by a DataValidation on another sheet, it only shows a little of the range.

    My question: how can I keep the user from skipping rows when they are entering information? I realize if I knew anything about a userform that would've solved the problem, but being that I gave the users control of the whole sheet, how can I fix it so it gives them an error message if they try to input data on anything but the next row south from the last entry?
    Reply With Quote Reply With Quote
  2. 06-25-2009, 10:25 AM #2
    Simon Lloyd
    Simon Lloyd is offline
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    The dynamic named trange wasn't meant to be your user input range, you should have a list on a seperate sheet of all the ID numbers, make that your dynamic named range, use validation on your user input range referring to the dynamic name, the sheet will then act as you wish, adding to the list on a seperate sheet will cause the named range to expand automatically.
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
    Reply With Quote Reply With Quote
  3. 06-25-2009, 11:16 AM #3
    Gingertrees
    Gingertrees is offline
    VBAX Contributor Gingertrees's Avatar
    Joined
    Jul 2008
    Posts
    154
    Location
    Ideal world yes, that could be the solution. Unfortunately, I have no idea how to do that, and be able to make that background-dynamic range sheet update if the user changes or deletes data.

    It seems like there should be some little section of code so an error will appear or something.

    ***Please, anyone, ideas on how to make the user just NOT put those annoying empty rows in there? Ideas that don't involve poking users with cattle prods? Though, that could be amusing...
    Reply With Quote Reply With Quote
  4. 06-25-2009, 11:20 AM #4
    Simon Lloyd
    Simon Lloyd is offline
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Attach a workbook so we can take a look for you!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)
    Reply With Quote Reply With Quote
  5. 06-25-2009, 01:43 PM #5
    Gingertrees
    Gingertrees is offline
    VBAX Contributor Gingertrees's Avatar
    Joined
    Jul 2008
    Posts
    154
    Location

    Post example

    Oops. That would help, now wouldn't it?

    In this example, the data in column A has a named range (NAMES) and a dynamic named range (Namelist). The data validation on "Plan of Care" sheet references Namelist, and the data validation on "Financial" sheet references NAMES. Just for comparison's sake.

    Please let me know if there is a way to force user to:
    -type the first client info in the first row, row #5, and
    -[whatever the variable last row used,] enter new client's info the row directly below that last taken row. Thank you!
    Reply With Quote Reply With Quote
  6. 06-25-2009, 09:40 PM #6
    Aussiebear
    Aussiebear is offline
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,064
    Location
    How will the workbook know that the new data is to go to the next available line? Will all the cells in the row above need to be filled in first?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link
    Reply With Quote Reply With Quote
  7. 06-25-2009, 10:24 PM #7
    GTO
    GTO is offline
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Quote Originally Posted by Gingertrees
    Please let me know if there is a way to force user to:
    -type the first client info in the first row, row #5, and
    -[whatever the variable last row used,] enter new client's info the row directly below that last taken row. Thank you!
    I didn't quite understand that last part.

    Just a thought, maybe bad, maybe good, but would allowing the user to enter data as norm, but building our dynamic validation list off to the side (so-to-speak) work?

    Presuming that on worksheet "Cases", Columns BH;BI;BJ can be used...

    In BH5: =IF(NOT(A5=""),ROW(),"")

    Drag this formula down to BH204

    In BI5:BI204: =IF(NOT(ISERROR(SMALL($BH$5:$BH$204,ROW()-4))),SMALL($BH$5:$BH$204,ROW()-4),"")

    Enter by CSE

    In BJ5: =IF(ISNUMBER($BI5),INDIRECT("A"&$BI5),"")

    Drag this formula down to BJ204

    =========================================================================== ============
    Then re-define 'NameList' as :
    =OFFSET(Cases!$BJ$5,0,0,COUNTA(Cases!$BJ$5:$BJ$204)-COUNTBLANK(Cases!$BJ$5:$BJ$204),1)

    There's probably a neater way of doing this, but this should get the names found in Col A all brought up to the "top" of Col BJ (starting at row 5 of course) and the formula for NameList then can work.

    Hope that helps,

    Mark
    Reply With Quote Reply With Quote
  8. 06-26-2009, 06:18 AM #8
    Gingertrees
    Gingertrees is offline
    VBAX Contributor Gingertrees's Avatar
    Joined
    Jul 2008
    Posts
    154
    Location

    Red face thank you!!!

    Wow! I have so much to learn...

    I don't understand how that works...but it does. I even screwed around with the names a bit and I haven't broken it yet.

    Thanks a lot! This forum is a godsend...
    Reply With Quote Reply With Quote
  9. 06-26-2009, 06:12 PM #9
    GTO
    GTO is offline
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Quote Originally Posted by Gingertrees
    Wow! I have so much to learn...

    I don't understand how that works...but it does. I even screwed around with the names a bit and I haven't broken it yet.

    Thanks a lot! This forum is a godsend...
    Gosh, don't try too hard (to bust it), I ain't "all that" on formulas by any means...

    I would mention that by my (sometimes daffy) memory, I was sure that the workbook I studied a bit was downloaded from the KB here. Alas, three searches to no avail, but I did notice that the example wb appears to be written by Debra Dalgleish, who has nice examples at contextures.com.

    Glad it worked, and happy to help

    Mark
    Reply With Quote Reply With Quote
  10. 09-11-2009, 08:57 AM #10
    Gingertrees
    Gingertrees is offline
    VBAX Contributor Gingertrees's Avatar
    Joined
    Jul 2008
    Posts
    154
    Location

    Question Relocated the range and killed it...

    Hello again,
    GTO's code above worked great, but I'd like to move it to the bottom of columns A, B and C to prevent accidental deletions by yours truly, the forgetful programmer. I thought just changing the cell references would work, but now it errors if I try to make Namelist into a drop-down on another sheet. What am I missing? I bolded my changes below each of GTO's original instructions:

    Presuming that on worksheet "Cases", Columns BH;BI;BJ can be used...


    In BH5: =IF(NOT(A5=""),ROW(),"")
    In A358: =IF(NOT(A5=””),ROW(),””)

    Drag this formula down to BH204
    DRAG DOWN TO A557

    In BI5:BI204: =IF(NOT(ISERROR(SMALL($BH$5:$BH$204,ROW()-4))),SMALL($BH$5:$BH$204,ROW()-4),"")
    In B358:B557: =IF(NOT(ISERROR(SMALL($A$358:$A$557,ROW()-4))),SMALL($A$358:$A$557,ROW()-4),””)

    Enter by CSE

    In BJ5: =IF(ISNUMBER($BI5),INDIRECT("A"&$BI5),"")
    In C358: =IF(ISNUMBER($B358),INDIRECT(“A”&$B358),””)

    Drag this formula down to BJ204
    Drag down to C557

    =====================================================================
    Then re-define 'NameList' as :
    =OFFSET(Cases!$BJ$5,0,0,COUNTA(Cases!$BJ$5:$BJ$204)-COUNTBLANK(Cases!$BJ$5:$BJ$204),1)
    =OFFSET(Cases!$C$358,0,0,COUNTA(Cases!$C$358:$C$557)-COUNTBLANK(Cases!$C$358:$C$557),1)
    help? Same database as included in above posts.
    Reply With Quote Reply With Quote
  11. 09-11-2009, 07:08 PM #11
    GTO
    GTO is offline
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,368
    Location
    Greetings Ariel

    I think you just need to adjust for wherever ROW() or ROW()-4 is in the formulas.

    Try:
    =IF(NOT(A5=""),ROW()-353,"")
    (Drag down)
    =IF(NOT(ISERROR(SMALL($A$358:$A$557,ROW()-357))),SMALL($A$358:$A$557,ROW()-357),"")
    (CSE)

    I think you already have the last formula and re-defined 'NameList' properly adjusted.

    Try the above and let us know :-)

    Mark
    Reply With Quote Reply With Quote
  12. 09-14-2009, 06:43 AM #12
    Gingertrees
    Gingertrees is offline
    VBAX Contributor Gingertrees's Avatar
    Joined
    Jul 2008
    Posts
    154
    Location
    Thanks Mark! That was what I needed. Have a great day!
    ~Ariel
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules