Hi Gareth

As you have spaces in your worksheet names, then the values in cell BU1 etc
need to be enclosed in single quotes 'WORK 1'
The following amended formula achieves this

=IF(COUNTIF(INDIRECT("'"&B$1&"'!A:A"),$A3),
VLOOKUP($A3,INDIRECT("'"&B$1&"'!A:Z"),
MATCH(B$2,INDIRECT("'"&B$1&"'!A1:Z1"),0),0),"")

In your original posting, you talked about 3-4 sheets being Summarised.
That being so, I was suggesting that we still had 4 sheets of raw Data.
Sheet Summary, would be a copy of WORK 1 (assuming that held all of the data
you wanted).
In cell BU1 you would enter WORK 2, in cell BU2 you would enter ID, assuming
there was a value in column ID of Sheet WORK 2 that was associated with the
name in column A of Summary
Then in BU3 use the new formula as above.

If you are wanting all columns from All sheets, then don't copy WORK 1 to
Summary, just copy the Names from Column A of that sheet to cell A3 of
Summary.
Then selectively Enter the Sheet and Column details from each of the 4
sheets for data that you wish to extract.

If you can't get it to work, mail me a copy of your workbook and I will set
it up for you.
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.
--
Regards
Roger Govier

"Gareth" wrote in message
...
Hi Roger
Sorry dont really understand what your asking me to do here.
If i have 2 worksheets, Work 1 and Work 2 (from now on lets call work 2
Summary)

You have said to enter the sheet name in the first blank colum after all
the
data I have copied into the summary sheet/ So would this sheet name be
Work 1
or Summary?
Once done, do I enter this after all the data ? if so then the colum you
suggest I put above into would be BU1.

if so BU1 would say Work 1
BU2 would be ID (the heading I want)
3rd row would (=IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3),
VLOOKUP($A3,INDIRECT(B$1&"!A:Z"),
MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"")

This is fine so far but it doesnt seem to do anything just produces a lot
of
the above caluatlation with no effect?

Stuck!




"Roger Govier" wrote:

Hi Gareth
You could do this with formulae.

Copy Sheet1 (or whichever sheet contains most of your data) to a new
sheet -
Summary
On Summary, insert 2 new rows at the top.
In the first blank column after your data, enter the Sheet name from
which
you want to copy the data e.g Sheet2, into the first row of that column.
In the second row of this column, enter the name of heading you want to
pick
up.
In the third row, enter this formula
=IF(COUNTIF(INDIRECT(B$1&"!A:A"),$A3),
VLOOKUP($A3,INDIRECT(B$1&"!A:Z"),
MATCH(B$2,INDIRECT(B$1&"!A1:Z1"),0),0),"")

and copy down as far as required.
Repeat the procedure for as many extra columns of information you want to
pick up.

--
Regards
Roger Govier

"Gareth" wrote in message
...
Hi thomas

thank you for the help so far.

When i create a macros and enter this in. it does actually seem to
work,
however it copies the data twice.


The other issue I will have soon is that I need to data to be show
horizontally; its hard to describe what i am after without sending you
an
example. can i do this?

Further issues I think could arise in that the Master file with all
currnt
data on it will need to be updated daily and edited. So therefore this
new
summary file will also need to be updated without having a user to do
it
all.
is this possible?
I do feel that what i am asking is getting beyond excels capabilities
is
this right?

For example

Worksheet 1 may contain
Name DOB Job Addres

mr X 19/09/08 cleaner 137 marther rd
Mrs C 01/01/01 teacher 5 nowhere
Mr D 20/09/08 admin 65 somewhere
Mr A 16/08/08 driver 12 park

work sheet 2 may contain further info such as

Name Qualifications telephone no
status

Mr
x................................................. ...............................................
Mrs c...................................
Mr D......................
Mr A..............

Finally to summarise all of this, i would like a summary sheet to show

name DOB job address qualifications tele no
status





So all data is present horizontallty and can be updated automatically
in
the
correct fields.

I do hope this makes sense?

Thanks once again.

Gareth