+ Reply to Thread
Results 1 to 12 of 12

Automate Name entry from a list

  1. #1
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Automate Name entry from a list

    Hello All,

    I would like to be able to automate the Entry of (Spot Checker) Names into the Cells E26:E36,E46:E57,E62:E63,J26:J36,J38:J40,J46:J49 of my form.

    The form on the "REPORT" Sheet Starting in Cell Q6. The list from an advanced filter (so it is not static).

    The idea is to use the Khaki list of name in Q6 and loop thru the names adding one to each cell. The catch is I wat the user to select the name from that list start from entering that name into cell E26. So if I where to choose the last name in the list it would go to the top of the list and run through filling the 39 cells with the Names until filled.

    Please see the attached file, all data is completely false.

    I don't know how to approach the solution and want to Thank your for looking at this project.

    V/r,
    Jim
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: Automate Name entry from a list

    Hi,

    Let me understand this.

    You have 44 names in Q6:Q49.
    Are you saying that that you want to fill the 39 cell in the ranges you mentioned using names from the 44 names. The rules being:

    1. The 39 names are picked in the strict order of listing in column Q, starting with a name that you choose as the starting name which you put in E26.
    2. If the name picked starts at Q12 or after, meaning there are only 38 names beneath, you then rotate to the top of the list at Q6 and start adding names going down until you have picked 39 names
    3. The order of filling the columns is first E26:E36 then E46:E57, E62:E63, J26:J36, J38:J40, J46:J49

    If that's correct then see the attached workbook. I've made E26 a drop down so you just pick a name.
    Attached Files Attached Files
    Last edited by Richard Buttrey; 07-09-2020 at 07:33 AM.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Re: Automate Name entry from a list

    Richard,

    Essentially, Yes. The sequence/order of cells was simply down each Column but not required. Also would there be a Random option for applying the names to the Cells?

    Our current method is manually entering each member in to a completely manual Sheet. An absolute waste of time.

    Thank you for looking.

    V/r,
    Jim

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: Automate Name entry from a list

    Hi,

    Yes after submitting the workbook it struck me that a picking the first name by random and then having the rest listed randomly would perhaps be useful.

    If the current workbook is OK then let me know and I'll put in some random functionality.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: Automate Name entry from a list

    See Attached

    I've added the following macro.

    When you double click in E26 that starts the macro.
    It first populates a random list of numbers in AH6:AH49
    Then copies all the names from column Q to AG6
    Then finally sorts the range AG6:AH49 in ascending order of column AH

    The names in the body of the report are now linked with a simple =AG6, =AG7, =AG8 ...etc to the randomised list of names.


    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Smile Re: Automate Name entry from a list

    Richard,

    Both solutions are great. Thank you.

    I was look for a MACRO for your first solution then I realized you created a formula to solve it. In my mind I was thinking it you require some sort of LOOP and IF statements.

    I'm a few hours ahead of you here in Japan and off to work now. As I'm in the US Navy as my have guessed.

    Again, Thank you.

    V/r,
    Jim
    Last edited by sorensjp; 07-09-2020 at 03:57 PM.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: Automate Name entry from a list

    My pleasure and thanks for the rep.

    Yes it was sort of clear that you're in the US Navy

    Presumably you're at the Naval base at Tokyo Harbour?
    I've spent quite a lot of time in Japan, much of it in Tokyo so I know it fairly well.
    As you clearly know it's a wonderful culture and so different to the West, although it has been gradually changing over the last 30 years.

    I first went to Tokyo in 1991 and then many times over the next 30+ years. I was last there about three years ago and found it had changed quite a lot.

  8. #8
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Re: Automate Name entry from a list

    Richard,

    Yes, Yokosuka Naval Base. My time here has been mainly spent at Sea.
    I did enjoy my time in the UK while I was stationed in Cornwall at RAF St Mawgan just outside of Newquay.

    I hate to request an option a MACRO based option ISO your first solution. Reason being is if the name of a "SPOT CHECKER" requires a change then the formula is "lost" for that changed Cell. The premise is the end users don't have a deep knowledge of Excel. I was excited to see your solution since I'm very poor with formulas.

    Thank you again for your time.
    V/r,
    Jim

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: Automate Name entry from a list

    Hi,

    Could you clarify what you mean by a Spot Checker name change causing a formula being lost please.

    What do you do to change a Spot Checker name, i.e. which cells are altered (manually or by running a macro and if so which macro) and which formula gets lost?

    I've tried changing a name in column Q in the Report tab and from G20 onwards in the Admin tab then running the Sub KhakiSptCkr_RptTable() macro, but this doesn't change any of the formulae I used so I'm puzzled at the moment.

    Which version of the two solutions I sent are you thinking of. The very first, which I'll call the 'rotating names' where the names stay in the same cyclical order but in different cells on the Report, OR the second version which randomises names?

  10. #10
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Re: Automate Name entry from a list

    Richard,

    I meant the names in the Cells E26:E36,E46:E57,E62:E63,J26:J36,J38:J40,J46:J49 - 'rotating names' as your solution uses a formula in each cell. If someone were to change the name in any one of the effected cells the formula is then removed (my brain saying 'lost').

    I hope it makes sense.

    V/r,
    Jim

  11. #11
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,443

    Re: Automate Name entry from a list

    OK. Understood.

    I had assumed that column Q on Report was where you kept the master list of names.
    To obtain the functionality you want will require a formulae in those cell ranges you mention.

    It sounds like the essential requirement is one of controlling the names available. i.e. being able to edit, add or delete Names from the system. It sounds like where those names are listed is not relevant to the user.

    The functionality you want would be better achieved I suggest by giving the user a pop up form which would allow them to manage the names. The names would continue to be listed in column Q although the user would not need to know about or use this range at all. When a name needed adding or deleting then there could be a button on the Report form that they click and then they'd see a pop up form for managing the names.

    I notice the master list of names seems to be G20:G80 on the Admin Report since this currently contains 50 names. Are all these available to be used for column Q on the Report (and then via formula in the specific ranges you have identified)?
    If so it seems that the pop up Names management form should work with those names rather than those the Report column Q. The Pop up form would contain all the 8 pieces of information that the table in E20:L80 requires

    Does that seem a way forward?

  12. #12
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2007 and 2016
    Posts
    190

    Re: Automate Name entry from a list

    Richard,

    I just wanted to say thank you for efforts. What I realized is using your formulas solution and just offset the location. Then copy & Paste values to the proper location.

    I made some adjustments to the formula so as to make it dynamic as the list of name will vary from week to week.

    V/r,
    Jim
    Attached Files Attached Files

+ 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. Trying to automate conditional data entry from one worksheet to another
    By mikeecoon in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-15-2019, 01:28 PM
  2. Automate Internet Explorer Windows Security credential entry thorough VBA
    By haripopuri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-28-2017, 06:28 AM
  3. Automate Data Entry with array or Macro
    By tsdpurdue in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-01-2017, 04:05 PM
  4. Looking For Alternatives to VBA/Maccro to AUTOMATE Data Entry
    By 777merlin777 in forum Excel General
    Replies: 21
    Last Post: 04-26-2017, 10:13 AM
  5. Trying to automate data entry into one workbook from multiple workbooks
    By gej2017 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2017, 07:33 AM
  6. [SOLVED] How to automate text addition after a new entry linked to another table ?
    By guibro in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2015, 11:47 AM
  7. Automate data entry from single cell reference!
    By Spellbound in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2009, 08:36 AM

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