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.

did you refresh your pivot table?

Excel 2007 => data => refresh

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.

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

Please reply.

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.

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

Originally Posted by wailay

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.

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.

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.

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.