+ Reply to Thread
Results 1 to 4 of 4

Copy Data/Columns to another sheet based on a value

  1. #1
    Registered User
    Join Date
    04-11-2018
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    22

    Copy Data/Columns to another sheet based on a value

    I am doing a new project to track the sales. I want to write formula to copy data to another sheet based on a cell value. Can’t write a VB script as this needs to run Office 365.
    main.JPG

    Based on the above table
    I want to copy the entire row (or selected columns) if the last (S4) cell value is 4x4 to the "4X4 sheet" or "Used cars sheet" if the last cell (S4) value is used cars


    I googled this but still can't find a way to write a formula.
    Thanks in advance

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Copy Data/Columns to another sheet based on a value

    It would be something like this, but with a sample you will get more solutions:

    =IFERROR(INDEX(Data!$B$13:$B$212,SMALL(IF(Data!$S$2:$S$212='4X4'!$S$2,ROW(Data!$S$2:$S$212)-ROW(Data!$S$2)+1),ROWS(Data!$S$2:$S2))),"")

    Array entered. But row S in your secondary tabs should contain 4X4 or Used Car

  3. #3
    Registered User
    Join Date
    04-11-2018
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    22

    Re: Copy Data/Columns to another sheet based on a value

    Hi Paul, thank you for the reply. Honestly !! I cant figure out that formula. Will you able to dum it down based on the below test data.
    sheet1.JPG

    So based on the value of D the data should be copied to the sheet used or new
    EG: Antony 123 Ford mustang should copy to the sheet used
    Arron 789 Subaru WRX should copy to sheet new

    Thanks

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Copy Data/Columns to another sheet based on a value

    Forum rules: https://www.excelforum.com/forum-rul...rum-rules.html
    Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, click Go Advanced below the text edit window, scroll down and click the Manage Attachments link, browse to the required file, and then push the Upload button, then Close Window to return to your post. (The "paper clip" icon for attachments is not currently working.)

+ 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: 11
    Last Post: 05-23-2016, 07:06 PM
  2. [SOLVED] Copy columns to another sheet based on checkboxes
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-17-2016, 01:32 AM
  3. Replies: 0
    Last Post: 11-25-2014, 05:30 PM
  4. Macro to copy 3 columns of data from multiple sheets into 3 columns on 1 sheet
    By bballdcm2007 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-15-2012, 04:37 PM
  5. Copy columns (with formatting) based on data in another sheet
    By jcfransen in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-08-2012, 05:53 AM
  6. Copy rows & columns based on the name to another sheet
    By desitracks in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2010, 10:31 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