+ Reply to Thread
Results 1 to 9 of 9

Auto Populate, Auto-Complete Text

  1. #1
    Registered User
    Join Date
    11-06-2020
    Location
    Semarang, Indonesia
    MS-Off Ver
    2010, 2016, 2021
    Posts
    23

    Question Auto Populate, Auto-Complete Text

    Hello Sensei,

    My name is Alfred,


    I have some problem, and need some help...

    could you please advise how I write the correct formula if the condition;

    1. While "typing SKU" in column H, the cell with auto-complete or give option for us to chose and press enter to finish.
    2. Provide Information in column H... Column I, J, K, L, M, N will auto-finish.
    3. is there any VBA/Macros to make it even easier please also advise.

    Please let me know if you need any further explanation.

    Thanks,
    Alfred
    Attached Files Attached Files
    Last edited by CokZz; 11-13-2020 at 09:20 PM. Reason: Problem Solved

  2. #2
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    Re: Auto Populate, Auto-Complete Text

    Hi Alfred,
    My solution. First, I created some named ranges. The first range is your entire SKU chart B2:H194 which I called SKUChart. The second range is the list of SKU’s B3:B194 which I call ItemCode. You can see them in the Name Manger. These are not necessary, but I think they are easier to work with. On the DATA tab I created a Data Validation List in the SKU/ITEM CODE column so you can choose the SKU you want on a line by line basis. Highlight the H column rows 3 through the end, then select Data Validation under the Data tab. In the settings box change Allow: from Any Value to List.
    In the source box put = ItemCode (or you can put =$B$3:$B$194 if you do not want to use named range.)
    Now, when you select a cell, like H3, you will see a drop down arrow that will list all of the SKU’s. Select your sku and the rest (I-N) will auto populate using vlookups.
    Data Validation drop downs do not have auto-complete capability. You can create Active X combo-boxes which can have the auto complete feature, but you will have to link each one to its specific cell in the H column one at a time. Very time consuming. The data-validation feature can be drag-copied.

    Squeaky
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-06-2020
    Location
    Semarang, Indonesia
    MS-Off Ver
    2010, 2016, 2021
    Posts
    23

    Re: Auto Populate, Auto-Complete Text

    Hi Sensei Squeaky

    Thank you for your guidance, I've used your formula and it's very helpful, but there is a little glitch/minor error in the file.

    In Condition;

    1. Assuming I had a repeat order, and I just need to copy one of the SKU from "SKU/ITEM CODE" to the new row, column I, J, K, l, M, N still not auto-populate, and is still need to drag down the formula from the above row, I don't know why this is happening, could you please advise?
    2. Does the VLOOKUP formula still work? if I add a new row for the "New SKU" to the "Item Master Sheet"?
    3. I found that we can also use INDEX - MATCH formula, which one do you think is easier? lookups VS Index-match? just to let you know, that I prefer to do "hard work" in the beginning.

    Thank you so much!

    Best, Alfred
    Last edited by AliGW; 11-10-2020 at 10:50 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Auto Populate, Auto-Complete Text

    1. To fix that delete last row (row 366) which is empty in Squeaky's file, then convert table to range, the convert range back to table. Then it should work.

    2. If you insert the new SKU between rows 193 and 194 (insert a new row there or anywhere above), the VLOOKUP should work.
    However, instead of making a range named SKUChart, I would have made a Table which automatically expands when adding new rows, thus automatically expanding the range of VLOOKUP.

    3. VLOOKUP is easier to use if you don't plan to change the Item Master columns - ie insert a new column between other 2 columns. If you insert a new column you have to manually change the VLOOKUP formulas in order to reflect the changes because columns are mentioned as static numbers inside VLOOKUP.
    INDEX-MATCH would do that automatically, though the syntax is a tad more complex. I always use INDEX-MATCH, but it's a matter of preference really. I know many people who use VLOOKUP, but they don't change their raw data column structure, while I sometimes do.
    Last edited by Mrrrr; 11-10-2020 at 06:50 AM.
    To show your appreciation
    Click ★ Add reputation!

  5. #5
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    Re: Auto Populate, Auto-Complete Text

    Hi CokZz (and Mrrr),
    I go back and forth between vlookup and index match.
    It was not made clear whether this was to be an expanding list or not so I chose the Vlookup. As Mrrr mentioned, adding rows in the middle of the chart will make the range expand on its own, and that is why I leave the last row blank and part of the range, so I always have an easy way to add a line to it anywhere. Much of this is what you get used to. I would have also added the IFERROR clause to clean up DATA, but I did not notice the N/A at the bottom.
    My motto is: I work hard to be lazy. I like your "hard work" attitude.

    Squeaky.

  6. #6
    Forum Contributor
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2019; 365
    Posts
    231

    Re: Auto Populate, Auto-Complete Text

    Hi Squeaky,
    Couldn't agree more on the hard work attitude.

    CokZz, if the matter is solved, please mark thread as solved: top of your first post - right side - Thread Tools - Mark as solved.

    And thanks for the rep mate.
    I hope Squeaky also got his share.

  7. #7
    Registered User
    Join Date
    11-06-2020
    Location
    Semarang, Indonesia
    MS-Off Ver
    2010, 2016, 2021
    Posts
    23

    Re: Auto Populate, Auto-Complete Text

    Hello Mrrrr & Squeaky,


    I just found an issue on my master file, I don't know why the lookup won't work, could you please advise?

    Anyway, thank you on your best advise, it work well on Squeaky's file... but mine still not work.

    Best, Alfred
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-12-2020
    Location
    Atlanta, Georgia
    MS-Off Ver
    Office 16
    Posts
    241

    Re: Auto Populate, Auto-Complete Text

    The cell with the formula is in text format. Change the format to GENERAL for the entire column, then put your cursor at the end of the formula (after the ")") and press enter. It should work. Then you can click and drag, or put your mouse in the lower right corner and when you see the + double click so it auto fills.

  9. #9
    Registered User
    Join Date
    11-06-2020
    Location
    Semarang, Indonesia
    MS-Off Ver
    2010, 2016, 2021
    Posts
    23

    Re: Auto Populate, Auto-Complete Text

    Thank youu ! it work...

    Apologize for my stupid question, I'am really new in "excel"

    then, it solved guys...

    Once again, thank you for Squeaky & Mrrrr

    Best,
    Alfred

+ 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. [SOLVED] Auto Complete Text from list
    By haarleen in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-20-2019, 05:47 AM
  2. [HELP]Auto Complete Text from the List
    By jamzsaludares in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-22-2014, 04:21 AM
  3. Auto complete the next cell after inputted a text
    By aditbaco in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-30-2012, 09:55 AM
  4. [SOLVED] auto complete text from data in another cell
    By godleyhdg in forum Excel General
    Replies: 3
    Last Post: 08-08-2012, 06:16 PM
  5. Auto Complete/Auto Fill address field
    By moates in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 05-09-2012, 09:15 AM
  6. Replies: 3
    Last Post: 02-28-2012, 11:54 AM
  7. Auto Complete Text Excel 2007
    By zrupnick in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-07-2009, 10:19 AM

Tags for this Thread

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