+ Reply to Thread
Results 1 to 4 of 4

Copy or Auto complete table from data set in another sheet

  1. #1
    Registered User
    Join Date
    12-18-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Copy or Auto complete table from data set in another sheet

    Hi,

    I am trying to create a "monthly return" from a data set in a separate sheet.

    Effectively, I would like to create a macro to auto populate a new table containing the data entered for the chosen month.

    This would then be exported or printed to pdf and filed on our internal document manager (which I can do).

    Any help would be appreciated.

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy or Auto complete table from data set in another sheet

    You can use formulas to extract the data onto that sheet - I have added them to your file - note that they are array formulas, and need to be entered using Ctrl-Shift-Enter instead of just Enter.
    Attached Files Attached Files
    Last edited by Bernie Deitrick; 12-18-2017 at 02:57 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    12-18-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Re: Copy or Auto complete table from data set in another sheet

    Thanks Bernie, this works a treat. If I wanted to copy over text in column B instead of numbers, how to would that work?

    Regards

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Copy or Auto complete table from data set in another sheet

    Change the formula in B5 to - array entered, using Ctrl-Shift-Enter -

    =IFERROR(INDEX('Data Sheet'!B:B,SMALL(IF(MONTH('Data Sheet'!$A$4:$A$200)=MONTH($B$2),ROW('Data Sheet'!$A$4:$A$200)),ROW($A1))),"")

    I used the 1/(1/.... construct to hide the blanks (which would display as all zero values), since your example was all numbers.

+ 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: 2
    Last Post: 06-28-2016, 03:57 PM
  2. Auto Complete Fields in Sheet 1 based on Sheet 2 information
    By Heather0240 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-09-2015, 02:19 PM
  3. [SOLVED] Auto complete from 1 sheet to another (can not repeat cells info)
    By ricdamiani in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 08-12-2013, 02:27 AM
  4. Table Auto Complete no longer working
    By narrowgate88 in forum Excel General
    Replies: 0
    Last Post: 02-05-2013, 03:31 PM
  5. Replies: 0
    Last Post: 08-03-2012, 11:34 AM
  6. [SOLVED] Auto-complete names from another sheet
    By x65140 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2012, 09:14 AM
  7. Auto Complete in a table from another sheet
    By ganderson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2011, 01:40 PM

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