+ Reply to Thread
Results 1 to 8 of 8

Autofilling data from a table??

  1. 01-20-2019, 10:19 AM #1
    Nickpop
    Nickpop is offline
    Registered User
    Join Date
    01-20-2019
    Location
    Belfast
    MS-Off Ver
    2013
    Posts
    8

    Autofilling data from a table??

    Hey everyone!!! I am new here!!!

    Looking a bit of help, and sorry if my questions sound a bit silly, but here we go....

    I want to a create a spreadsheet which i only need to key in a Town name, and the next 3 cells along will fill with the Address, No of Employees, and Shop opening time

    So for example..... I key in (A2) London = B2,C2, & D2 complete as 12 Long Road, 12, 9am

    I need to complete this daily and rarely will be in the same order, so just keying in the town name when i receive a phone call from them, will it autocomplete for me.

    I assume that i would have to create a table somewhere (probably on a different tab)? Then what foruma do i need so that it will prefill when A2 is completed?

    Please be as basic with the instructions as possible :-)

    thanks all inadvance
    Nick
    Last edited by Nickpop; 01-20-2019 at 12:24 PM.
    Reply With QuoteRegister To Reply
  2. 01-20-2019, 10:37 AM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Autofilling data from a table??

    Hi Nick and welcome

    No such thing as a silly question!

    Your assumption is correct around building a separate lookup table. It doesn't have to be on a separate sheet, but it could be.

    In the attached I've got the lookup table in columns I:L.
    The below formula in in the yellow cell and copied right and down to cover the green cells. You can obviously copy it down as far as you need.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    You could also use:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Look into how those formulas work and feel free to come back with any questions.

    BSB
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 01-20-2019, 11:24 AM #3
    Nickpop
    Nickpop is offline
    Registered User
    Join Date
    01-20-2019
    Location
    Belfast
    MS-Off Ver
    2013
    Posts
    8

    Re: Autofilling data from a table??

    That is brilliant !! thank you so much!!!

    Just one more thing...

    I have created my sheet and have placed all the code in, and it works a charm...

    I have dragged the code down so that its live for the next 100 entries as i key them in, but at the moment they all have #N/A (obviously as there are no data in the first cell), but is there a way that it could be just blank instead of having the N/A?

    Thanks for all the help!
    Nick
    Reply With QuoteRegister To Reply
  4. 01-20-2019, 11:26 AM #4
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Autofilling data from a table??

    Sure, try this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    or:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Depending on which approach you used.

    BSB
    Reply With QuoteRegister To Reply
  5. 01-20-2019, 11:26 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Autofilling data from a table??

    Wrap the formula in an error trap:

    =IFNA(your_formula,"")
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 01-20-2019, 11:47 AM #6
    Nickpop
    Nickpop is offline
    Registered User
    Join Date
    01-20-2019
    Location
    Belfast
    MS-Off Ver
    2013
    Posts
    8

    Re: Autofilling data from a table??

    Thank BSB!!!! Worked a treat again

    Pints on me!!!
    Reply With QuoteRegister To Reply
  7. 01-20-2019, 11:48 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,647

    Re: Autofilling data from a table??

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  8. 01-20-2019, 11:49 AM #8
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Autofilling data from a table??

    No problem mate. Happy to help.

    Quote Originally Posted by Nickpop View Post
    Pints on me!!!
    WHOOOHOOOOOO!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Issue with my table not autofilling
    By MikeSta4ord in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-20-2015, 07:02 PM
  2. [SOLVED] One formula in a table is not autofilling when inserting a row
    By chrisb84 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2013, 05:14 AM
  3. How to keep formatted table column reference static when autofilling
    By chrisjames25 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-14-2012, 10:23 AM
  4. Formulas not autofilling in 2010 Table
    By Melvinrobb in forum Excel General
    Replies: 5
    Last Post: 07-30-2012, 02:28 PM
  5. autofilling data
    By pod_dclxvi in forum Excel General
    Replies: 2
    Last Post: 11-03-2010, 09:48 AM
  6. Excel 2007 : turn off autofilling of column in 2007 table/list?
    By The Headacher in forum Excel General
    Replies: 2
    Last Post: 01-21-2010, 03:33 AM
  7. Autofilling data
    By impala096 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-13-2008, 02:47 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1