+ Reply to Thread
Results 1 to 4 of 4

Replicate columns onto separate tab in same workbook

  1. #1
    Registered User
    Join Date
    07-20-2017
    Location
    Ipswich, England
    MS-Off Ver
    10
    Posts
    6

    Replicate columns onto separate tab in same workbook

    I have a spreadsheet with 46 columns with essential data in.

    In order to make visualisation of important data easier for a client, to avoid them having to interrogate multiple columns, I need to replicate (and automatically update) half a dozen or so of the most important and relevant columns onto a separate tab but the data in the columns needs to update as the data in the main spreadsheet is updated.......

    Is this possible? TIA

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,766

    Re: Replicate columns onto separate tab in same workbook

    Read the yellow banner at the top of this page and attach a sample file.

  3. #3
    Registered User
    Join Date
    07-20-2017
    Location
    Ipswich, England
    MS-Off Ver
    10
    Posts
    6

    Re: Replicate columns onto separate tab in same workbook

    Sample file attached as requested
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,087

    Re: Replicate columns onto separate tab in same workbook

    My first step would be to convert the range of data on sheet 1 into a table (Table1).
    One option would be to produce a pivot table as modeled on sheet 2, that displays the six columns. When new data is added to the table then selecting a cell in the pivot table and choosing refresh from the pivot table tools tab, analyze subtab, will display the new data.
    I have also included a formula option on sheet 3 using: =IFERROR(INDEX(Table1[[ACAPS Job Reference Number]:[TP Nš]],ROWS($A$1:$A1),MATCH(A$1,Table1[[#Headers],[ACAPS Job Reference Number]:[TP Nš]],0)),"")
    For the formula option you would need to copy the formula down the sheet for at least as many rows as in the data that is being retrieved from sheet 1. You could anticipate the maximum number of rows that will be filled on sheet 1 and copy the formula at least that far down on sheet 3.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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