+ Reply to Thread
Results 1 to 18 of 18

complex vlookup - search similar word from all worksheet n enter in summary sheet

  1. #1
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    complex vlookup - search similar word from all worksheet n enter in summary sheet

    I have attached the file to this thread.

    I have been working on this master file for my site total manpower crew list.

    in the summary file i have linked it with all the worksheets in the particular file.

    what i am looking for is, the summary file should automatically update if a welder is added or subtracted from the rest of the worksheets. for example if a welder is deleted from the flowline-1 and 3 welders are added in flowline-2, the summary sheet should automatically count and make the necessary amendments. same goes with foremans, welders, riggers, helpers.

    is it possible?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    I would it solve it this way.

    First ran the macro below.

    After that I made an pivot table of that consolidated sheet.

    See the attached file.

    Please reply.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    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.

  3. #3
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    thanks for the prompt reply.

    when i update any worksheet by adding/deleting a welder or rigger, the pivot table is not updating automatically.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    did you refresh your pivot table?

    Excel 2007 => data => refresh

  5. #5
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    thank you.

    i am beginner to excel.
    in order to update the pivotable we need to edit the consolidated file not the worksheet. is it possible if without making the consolidated file can we pivotable be updated automatically?

    one more question do i need to make this consolidated file everytime i update/edit any worksheet?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    Please Login or Register  to view this content.
    Yes, you have to update the consolidated sheet.

    You can push the button on the sheet pivot table to update the consolidated sheet.

    After that you can refresh the pivot table to get the update data in the pivot table.

    See the attached file.

    Change some data in this test sheet and let me know if it works for you.

    So please reply.
    Attached Files Attached Files
    Last edited by oeldere; 08-17-2013 at 03:47 AM.

  7. #7
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    when i click on the update the consolidated file it gives me an error which is attached to this reply.
    Attached Images Attached Images

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    It seems me you did not accepted (the messege) to allow macro's.

    Please reply.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    Maybe you can check the text below.

    (i'm using the dutch version, so the text (names) could be differant)

    Excel 2007 (accept macro’’s)
    Red button (in the left corner)
    Options for excel
    Security centre (8 option)
    Options for security centre
    Options for macro’s (5 option)

    I choosed the 4 option.
    You can also choose the 2 option (with notification).
    Ok
    Ok
    Now you have to be able to allow macro’s.

  10. #10
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    it is updating but when i deleted the welder row and updated the consolidated file and then pivot table, instead of deleting 1 welder from the total of pivot table it deleted total of 7 numbers!.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    Just show me the changed file, i will take a look at it, later today.

  12. #12
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    attached!

    i deleted the welder from flowline-1 sheet and refreshed the consolidated and pivot file.
    original grand total was 87 but after deleting just one welder the grand total was 80.

    i really appreciate your help and you are too good with excel.

    Regards,
    Attached Files Attached Files

  13. #13
    Forum Contributor rajeshturaha's Avatar
    Join Date
    08-22-2012
    Location
    Assam, India
    MS-Off Ver
    Excel 2003, 2007
    Posts
    236

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    Quote Originally Posted by wailay View Post
    I have attached the file to this thread.

    I have been working on this master file for my site total manpower crew list.

    in the summary file i have linked it with all the worksheets in the particular file.

    what i am looking for is, the summary file should automatically update if a welder is added or subtracted from the rest of the worksheets. for example if a welder is deleted from the flowline-1 and 3 welders are added in flowline-2, the summary sheet should automatically count and make the necessary amendments. same goes with foremans, welders, riggers, helpers.

    is it possible?
    Going through your file i have up with the following solution without using macro. Refer to the attachment.
    In col q put all the sheet names, col r contains the total nos of entries and s is a helper col. Col contain the header. see the formula used in col a>k, drag it down as per your requirement.
    Attached Files Attached Files
    Rgd
    RT
    If my answer(s) helped you, please add me reputation by click on *

  14. #14
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    After updating you have to make an new pivot table (very easy if you know how it works).

    Maybe this can be also done by VBA, but I didn't implemented that.

    Please reply.

  15. #15
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    I have no idea how to make the pivot table! you have already helped a lot and asking more would be giving trouble to u!

    @rajeshturaha i am confused about your file. unable to figure out the formulas maybe too advance for a beginner?

  16. #16
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    Excel 2007

    consolidated sheet click in cel b2.

    Insert => table => click on OK (now the data is formated as an table).

    After that:

    Insert => pivot table.

    You see an pop up screen with => table that's OK

    choose new sheet.

    Now you get an new sheet with on the left site an pivot table (empty).

    On the right site you see dropdown boxes.

    1) drag designation from the rapportfilter into the row label (box).

    Now you find all works in the pivot table (on your left side).

    2) drag designation from the rapportfilter into the value label (box).

    You choose count for the values.

    Now you can make up the format of those cells.

    after that the pivot table is complete.

    Try it, and please reply back.

  17. #17
    Registered User
    Join Date
    08-17-2013
    Location
    muscat
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    PIVOT table i am able to create! but about the consolidated sheet you made i am unable to capture whole information in every sheet!

  18. #18
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: complex vlookup - search similar word from all worksheet n enter in summary sheet

    Please Login or Register  to view this content.
    Great job, congratulations.


    Please Login or Register  to view this content.
    What's missing ?

    Show the file and add e.g. with color what's not in the consolidated sheet.

    Without seeing your file it is very difficult to understand what you mean.

+ 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. [SOLVED] Create User Input Dialog Box to Enter Keywords for Word Search in a Cell
    By vcs1161 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2013, 01:36 PM
  2. Complex Micro/Formula to Search from one sheet and Return Value on other sheet
    By atthershabbir in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-30-2012, 12:57 PM
  3. Automatically enter details in Summary sheet when new sheet created
    By unley in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2010, 05:13 PM
  4. Replies: 2
    Last Post: 04-29-2010, 04:27 AM
  5. [SOLVED] Lookup info in support sheets and enter in summary sheet
    By WendiL99 in forum Excel General
    Replies: 6
    Last Post: 06-21-2006, 12:40 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1