RE: Extracting Data from multiple sheets/rows

**stuckinlondon** · 08-19-2011, 07:41 AM

Hi everybody,
Apologies if this has been answered previously, as it strikes me as the kind of thing that lots of people ask questions about - just different variations of the same thing. I've been looking for weeks for a solution to this problem, and I can't for the life of me work it out.

I need a front summary page that provides an accurate reflection of the data within. In this instance (and as I've tried to reflect in my example sheet) I need to provide an efficient indication as to which positions (or in the example, numbers) are open without manually going through sheet by sheet and cutting and pasting those numbers/positions which are open.

So for example; I have the following data
Example 1 - On Sheet Two
1 Open
2 Open
3 Closed
4 Open
5 Closed
6 Closed

Example 2 - On Sheet Three
7 Closed
8 Open
9 Closed
10 Closed
11 Closed
12 Open

So on Sheet One I want to automatically display all the rows within the other two sheets that are 'Open' as indicated via column B.

Is this more complicated than I think it is? Because I can't work it out.

Thanks so much for your help.

Yours,
SIL

**royUK** · 08-19-2011, 07:50 AM

I can never understand why data is spread across sheets. Have all data on one sheet then use AutoFilter

**JBeaucaire** · 08-19-2011, 08:10 AM

Roy's right... if you have control of your working environment, one data sheet is the proper method. Reporting is easy once you do that.

I've had to help enough people who don't have control of the working environment to have an approach or two for this, one is attached. Adding a "helper" column to each of your data sheets, each sheet picks up where the previous one left off... creates a unique index. Back on the main sheet you can then pick those items as shown. This is far from elegant, but it works.

One hopes you'll be able to put your data on one sheet instead.

**stuckinlondon** · 08-19-2011, 09:00 AM

Thanks gents.

Unfortunately I do not have control over the sheets as the data is spread out over various legal business entities and it would not make sense to consolidate them for the people I report to. I also don't want to create a second sheet that does consolidate them, requiring two updates for every change of information.

Thanks though - I'll keep thinking through a solution.