+ Reply to Thread
Results 1 to 21 of 21

Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi

    I am trying to figure out if there is a simple way to replicate the contents of multiple sheets into one sheet based on the headings of those multiple sheets.

    Please find attached an example (test.xlsx) of what I am trying to achieve.

    In sheet 1, I have the headings of A-F out of order.

    In sheet 2, I have the headings of A, B and C with various numbers underneath each heading.

    In sheet 3, I have the headings of D, E and F with various numbers underneath each heading.

    What I am trying to do is:
    • Get the contents under the headings of sheets 2 and 3 under the correct headings of sheet 1.
    • As I make alterations to the headings in sheet 1, the information underneath updates automatically (e.g. switch headings around).
    • Make sheet 1 to be live, in that, as I make changes (or add more or delete rows, or paste information) to sheets 2 & 3, that the contents of sheet 1 would automatically update.

    If anyone out there can help, I'd be most appreciative.

    Cheers
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    How about this?
    Attached Files Attached Files
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  3. #3
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi k64

    Thank you. This is almost what I am after.

    Is there a way to have more rows of information added into sheet 1 (after more information is added in further rows) of sheets 2 and 3 without the need to copy the formula.

    Cheers

  4. #4
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Yes, but this way of doing things will only work for relatively small data sets. If you're going to have lots (10,000+) of data, you'll need to switch to simplifying it with helper columns or using macros.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi again k64

    Yes I am working with rows greater than 10,000 and increasing as each month ticks along.

    Having had a quick look at what you added in, it looks like you have hit the nail on the head with what I am after.

    You are brilliant.

    Thank you.

  6. #6
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi again k64

    How do I increase the number of rows in the formula to read down to say row 20,000 ?

    Cheers

    (Please ignore this, I just figured out copy and paste works). cheers
    Last edited by Thomas Andrews; 06-06-2014 at 10:19 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    You're welcome. I'm glad I could help. If it starts getting too slow, you can copy the parts that won't change and paste as values. If that's not an option, then there are other ways to do it faster (mainly with VBA).

  8. #8
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi again K64

    Sorry to bother you again.

    If I was to add sheet 4, how would your formula look on sheet one ?

    Also on another issue, I need a column in sheet 1 to check if a single cell in sheets 2, 3 and 4 equal each other.

    I have attached an updated xlsx file.

    Your help would be greatly appreciated.

    test2.xlsx

  9. #9
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Maybe this could help you, using array formula... please have take a look at the file attach

    Regards
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi Azumi

    Yeah, almost there.

    I just need a formula for column AA to check that Cell A2 in sheets 2, 3 and 4 are the same.

    Please see attached.

    test3.xlsx

  11. #11
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    To check if A2 is equal on all the sheets you can use this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    To add another sheet, just add another IFERROR, so that it checks the 1st sheet, then if error, the 2nd, then if error the 3rd, then if error returns "".
    I don't know how you feel about macros, but if this is going to expand to more sheets, using a macro may be the best option.

  12. #12
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi k64

    Yeah still having issues.

    I'm not good on the macros etc but I thought to quicken the process I have attached a snapshot of the sheet I am working with.

    See what you think you can do.

    Cheers

    test4.xlsx

  13. #13
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Ok you have duplicates header there, and what you need as a results? Is that combine them?

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    @Thomas Andrews

    in sheet 4 you don't have headers on all columns.

    The format of all sheets are the same.

    column A year, column B, month, column E VR Region etc.

    If so, you can use VBA to get all data in one sheet.

    Which column will always have all values in it for all rows?

    Is it column A?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  15. #15
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    As azumi pointed out, you have duplicate headers between your sheets. What exactly are you trying to accomplish? Also, for the check, I'm showing a #REF error, so I'm not sure what it used to be or what problem you're having with it.
    Last edited by k64; 06-07-2014 at 10:42 AM.

  16. #16
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi all

    Sorry to be confusing. I truly do appreciate all the help I am getting.

    What I am trying to achieve is basically this.

    I export 3 sets of data (sheets 2, 3 and 4) from a database which I need to get a piece of information from each one (sheet 1).

    That is what I am trying to get with the iferrror statements.

    To make sure that each row correlates from one sheet to another (on sheets 2, 3 and 4), I am trying to do a check on the cells I have highlighted in red in sheets 2, 3 and 4. And then return a result that those values match or do not match (in the blue cell on sheet 1). That way I can tell that the relevant row of information does not match from one sheet to another.

    My apologies for not having the correct spreadsheet before (test4). I have uploaded a correct one (test5). I have added in the relevant headings and a missing column in sheet 4 (App No).

    test5.xlsx

    On another note, I am noticing that the formulas are referencing an external file. When I go to break the external link, the formula dissappears, is there an easy way to remove the external link whilst retaining the formula.

    Hope this clears things up.

  17. #17
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Is this what you want?
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    Hi k64

    Yep you have done it again.

    Apart from my document trying to reference an external link, you have gotten it to do what I am after.

    Is there a way to remove the external link from the formulas ?

    Cheers

  19. #19
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    To remove the exeternal link:

    Copy => paste special => value

  20. #20
    Registered User
    Join Date
    06-06-2014
    Posts
    13

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    But doing this losses the formula which is something I wish to retain.

    In saying this, I think I will post the excel spreadsheet in another forum as this is another issue aside from what has been solved.

    Thank you all.
    Last edited by Thomas Andrews; 06-09-2014 at 09:07 AM.

  21. #21
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Auto copy contents from sheets 2 and 3 to sheet 1 if column heading contains set words

    You're welcome. Glad I could help and thanks for the +Rep!

+ 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. copy contents of one column to another column BUT change just a few words
    By tlacloche in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-11-2013, 11:01 AM
  2. Copy from sheet to other sheets, after each heading, in a specific row
    By sans in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-17-2012, 03:03 PM
  3. Macro to filter based on column heading then copy and paste to new sheet
    By macattackr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2012, 05:14 PM
  4. Replies: 2
    Last Post: 12-22-2008, 02:01 AM
  5. Search for column heading and copy column onto another sheet
    By chrismann85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-28-2008, 05:53 AM

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