+ Reply to Thread
Results 1 to 14 of 14

Inputting data

  1. #1
    Registered User
    Join Date
    07-22-2015
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    10

    Inputting data

    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...

  2. #2
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Quebec, Canada
    MS-Off Ver
    2013
    Posts
    232

    Re: Inputting data

    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.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Inputting data

    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

  4. #4
    Registered User
    Join Date
    07-22-2015
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    10
    Quote Originally Posted by FDibbins View Post
    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
    Thanks. Is there any way that I can have it automatically jump let's say from A10 - B1, B10-C1,etc. ?

  5. #5
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Quebec, Canada
    MS-Off Ver
    2013
    Posts
    232

    Re: Inputting data

    Quote Originally Posted by mszypa50 View Post
    Thanks. Is there any way that I can have it automatically jump let's say from A10 - B1, B10-C1,etc. ?
    I don't know if locking it was supposed to work, but have you tried my suggestion?

  6. #6
    Registered User
    Join Date
    07-22-2015
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    10

    Re: Inputting data

    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

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Inputting data

    Quote Originally Posted by mszypa50 View Post
    Thanks. Is there any way that I can have it automatically jump let's say from A10 - B1, B10-C1,etc. ?
    If you applied the protection/unprotection, it should do that for you anyway?

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Inputting data

    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

  9. #9
    Registered User
    Join Date
    07-22-2015
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    10

    Re: Inputting data

    It doesn't seem to work for me.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Inputting data

    ooops I think that was my mistake...
    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 UNchecked
    If it still doesnt work for you, I can upload a sample file for you

  11. #11
    Registered User
    Join Date
    07-22-2015
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    10
    Quote Originally Posted by FDibbins View Post
    ooops I think that was my mistake...


    If it still doesnt work for you, I can upload a sample file for you
    Let me give it a try. Thanks!

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Inputting data

    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.

  13. #13
    Registered User
    Join Date
    07-22-2015
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    10

    Re: Inputting data

    I think that would be best

  14. #14
    Registered User
    Join Date
    07-22-2015
    Location
    Detroit, MI
    MS-Off Ver
    2010
    Posts
    10
    Quote Originally Posted by mszypa50 View Post
    Let me give it a try. Thanks!
    Great. Thanks.

+ 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. Replies: 0
    Last Post: 07-26-2013, 01:27 PM
  2. need help inputting data from a form
    By jimmy21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-03-2013, 12:26 PM
  3. [SOLVED] prompt box for inputting data
    By Declan.Ryan in forum Excel General
    Replies: 17
    Last Post: 11-16-2012, 07:26 AM
  4. Inputting data from USERFORM to next available row
    By WoodsyMP5K in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2012, 06:18 AM
  5. Inputting data from a formula
    By JohnFex in forum Excel General
    Replies: 13
    Last Post: 02-21-2012, 01:37 PM
  6. Inputting Data
    By 2nice in forum Excel General
    Replies: 5
    Last Post: 09-30-2010, 05:43 PM
  7. inputting data
    By Shanor in forum Excel General
    Replies: 0
    Last Post: 06-15-2006, 05:55 AM
  8. [SOLVED] Update a chart immediately after inputting data into data source
    By MELMEL in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-01-2005, 05:40 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07873 seconds
  • Memory Usage 9,245KB
  • Queries Executed 15 (?)
More Information
Template Usage (35):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (6)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (14)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (14)postbit_legacy
  • (14)postbit_onlinestatus
  • (14)postbit_wrapper
  • (4)showthread_bookmarksite
  • (8)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (48):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1