Excel 2007 - Extracting Data from Worksheet

**timharding** · 09-22-2011, 04:52 AM

Excel 2007 - Extracting Data from Worksheet

Gurus,

I am trying to extract data from different excel sheets, into one summary sheet (See Example A attached).

I have tried just linking but this is not what I want, as I do not want to have the blanks in the summary sheet.

What is the best way to do this?

Thanks in advance.

Tim

**Richard Buttrey** · 09-22-2011, 05:39 AM

Hi,

Why not keep all the staff details from the two countries on the same sheet and treat this as the summary sheet then use simple filters to extract or view specific subsets of it. There seems no obvious reason to keep two sheets since the country is already a field name.

Regards

**timharding** · 09-22-2011, 05:41 AM

Originally Posted by Richard Buttrey

Hi,

Why not keep all the staff details from the two countries on the same sheet and treat this as the summary sheet then use simple filters to extract or view specific subsets of it. There seems no obvious reason to keep two sheets since the country is already a field name.

Regards

The attachment (sorry) is an extract of a file that contains over 100 countries, and has various other information etc, and multiple data.

Therefore that is not possible.

Thanks

Tim

**Richard Buttrey** · 09-22-2011, 06:23 AM

Hi,

Never say something is not possible with Excel.

Nevertheless, and since you ask, the best way is undoubtedly to build a proper database all on a single sheet. Which is in fact the very first request in your original post.

As long as your current sheets' data is consistent and has the same fields of information then a simple macro could be written to automatically create this master database from the 100+ sheets.

Believe me this is a much better way to proceed. All you're doing is throwing away your current Summary, and replacing it.

Not only would it make your current request extremely simple, but you would also be able to use Excel's most powerful summary and analysis feature - viz a Pivot Table, to produce just about any report you could conceive.

Regards