LinkBack URL
About LinkBacks
Lets say I have 5 columns of 10 cells = 50 cells. How do I enter data in first column and when I reach the 10th cell, it automatically goes to the top of the 2nd column to continue entering data and so on...
Lets say I have 5 columns of 10 cells = 50 cells. How do I enter data in first column and when I reach the 10th cell, it automatically goes to the top of the 2nd column to continue entering data and so on...
Are you willing to use VBA? If so, you could try this:
Let's say your range is A10:E10:
Please Login or Register to view this content.
Hi, welcome to the forum
Try this...
1. protect all your range
- highlight the range
- right-click into the range, click Format
- click the Protection tab, make sure Locked is checked
2. Unprotect your cells
- highlight the range
- right-click into the range, click Format
- click the Protection tab, make sure Locked is UNchecked
3. protect the sheet
- right-click into the sheet tab, click Protect
- make sure Locked is checked
No need for VBA for this, it is a built-in feature in excel
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks. Is there any way that I can have it automatically jump let's say from A10 - B1, B10-C1,etc. ?Originally Posted by FDibbins
Hi, welcome to the forum
Try this...
1. protect all your range
- highlight the range
- right-click into the range, click Format
- click the Protection tab, make sure Locked is checked
2. Unprotect your cells
- highlight the range
- right-click into the range, click Format
- click the Protection tab, make sure Locked is UNchecked
3. protect the sheet
- right-click into the sheet tab, click Protect
- make sure Locked is checked
No need for VBA for this, it is a built-in feature in excel
I don't know if locking it was supposed to work, but have you tried my suggestion?
Gives me this error
Private Sub CommandButton1_Click()
Private Sub Worksheet_Change(ByVal Target As Range)
Dim n As Integer
For n = 1 To 4
If Not Intersect(Target, Cells(10, n)) Is Nothing Then
Cells(1, n + 1).Select
End If
Next n
End Sub
End Sub
If you applied the protection/unprotection, it should do that for you anyway?
If the Excel Options, Advanced, Editing Options, AFTER PRESSING ENTER, MOVE SELECTION direction: DOWN this will work.
Select for example A1 to E10. The active cell is A1. Enter a value and hit enter. Repeat until the range is filled. The active cell will go down with each ENTER until the bottom cell of the selected range is reached and filled. It will then jump up to the top of the next column repeating until the range is filled.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
It doesn't seem to work for me.
ooops I think that was my mistake...
If it still doesnt work for you, I can upload a sample file for you1. protect all your range
- highlight the range
- right-click into the range, click Format
- click the Protection tab, make sure Locked is checked
2. Unprotect your cells
- highlight the range
- right-click into the range, click Format
- click the Protection tab, make sure Locked is UNchecked
3. protect the sheet
- right-click into the sheet tab, click Protect
- make sure Locked is UNchecked
Let me give it a try. Thanks!
Did you try setting the Excel Options, Advanced, Editing Options (the first choice) select After pressing Enter, move selection Direction DOWN?
Then all you have to do is select the cells (as per your question "Is there any way that I can have it automatically jump let's say from A10 - B1, B10-C1,etc. ? ). Select A1:E10 (50 cells) and enter the first entry and hit enter. The active cell will go down to A2 enter the next value and hit enter and the active cell becomes A3. This continues to the bottom of the column A selection and jumps up to B1 and continues until the last cell is entered. When the last cell is entered the active cell jumps back to A1.
There are no formulae involved, nothing to lock or unlock. Just make the Option to go DOWN upon enter in Excel Advanced options and select the range that you want to fill and start entering data.
I think that would be best
Great. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks