PDA

View Full Version : [SOLVED] Force user to go only to next row in a spreadsheet



Gingertrees
06-25-2009, 08:19 AM
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?

Simon Lloyd
06-25-2009, 10:25 AM
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.

Gingertrees
06-25-2009, 11:16 AM
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...

Simon Lloyd
06-25-2009, 11:20 AM
Attach a workbook so we can take a look for you!

Gingertrees
06-25-2009, 01:43 PM
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!

Aussiebear
06-25-2009, 09:40 PM
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?

GTO
06-25-2009, 10:24 PM
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

Gingertrees
06-26-2009, 06:18 AM
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...

GTO
06-26-2009, 06:12 PM
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...

:rotlaugh: 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:friends:

Mark

Gingertrees
09-11-2009, 08:57 AM
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.

GTO
09-11-2009, 07:08 PM
Greetings Ariel :hi:

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

Gingertrees
09-14-2009, 06:43 AM
Thanks Mark! That was what I needed. Have a great day!
~Ariel