I need to return information on employees from about 10 separate spreadsheets
to a master employee list. I guess this would involve looking up the
employees name which is in column A on all sheets and returning the word yes
or no from column J on all sheets to column L on the master sheet at the same
employees name?????

One crack at this toughie ..
(do hang around awhile, there could be a better solution out there)

You could try this set-up

In your master sheet,
assume the names are listed in A2 down

Using 10 empty cols to the right of col L, viz cols M to V

In M1:V1, carefully enter the exact sheetnames
of the 10 other sheets that the names are found
(the names within each sheet are assumed listed in A2 down)

Place in M2, normal ENTER:
=INDEX(INDIRECT("'"&M$1&"'!J:J"),MATCH($A2,INDIREC T("'"&M$1&"'!A:A"),0))
Copy M2 across to V2, fill down to the last name in col A to populate. If
you see #REF!, that means the sheetname(s) entered within M1:V1 do not match
the names on the tabs. Re-check and correct if required. Don't worry about
the #N/As.

Then put in L2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(M2:V2,MATCH(TRUE,NOT(ISERROR(M2:V2)),0))
Copy L2 down to the same extent. Col L will return the required results from
col J in each of the 10 sheets.

The above assumes of course that names are uniquely listed, ie any name
listed in the master sheet will appear only on 1 sheet within the 10 other
sheets.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pj" wrote:
I need to return information on employees from about 10 separate spreadsheets
to a master employee list. I guess this would involve looking up the
employees name which is in column A on all sheets and returning the word yes
or no from column J on all sheets to column L on the master sheet at the same
employees name?????

Thanks so much!!! I'll give it a try.

PJ

"Max" wrote:

One crack at this toughie ..
(do hang around awhile, there could be a better solution out there)

You could try this set-up

In your master sheet,
assume the names are listed in A2 down

Using 10 empty cols to the right of col L, viz cols M to V

In M1:V1, carefully enter the exact sheetnames
of the 10 other sheets that the names are found
(the names within each sheet are assumed listed in A2 down)

Place in M2, normal ENTER:
=INDEX(INDIRECT("'"&M$1&"'!J:J"),MATCH($A2,INDIREC T("'"&M$1&"'!A:A"),0))
Copy M2 across to V2, fill down to the last name in col A to populate. If
you see #REF!, that means the sheetname(s) entered within M1:V1 do not match
the names on the tabs. Re-check and correct if required. Don't worry about
the #N/As.

Then put in L2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=INDEX(M2:V2,MATCH(TRUE,NOT(ISERROR(M2:V2)),0))
Copy L2 down to the same extent. Col L will return the required results from
col J in each of the 10 sheets.

The above assumes of course that names are uniquely listed, ie any name
listed in the master sheet will appear only on 1 sheet within the 10 other
sheets.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pj" wrote:
I need to return information on employees from about 10 separate spreadsheets
to a master employee list. I guess this would involve looking up the
employees name which is in column A on all sheets and returning the word yes
or no from column J on all sheets to column L on the master sheet at the same
employees name?????

welcome. post back on how it went for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TX caterer" wrote in message
...
Thanks so much!!! I'll give it a try.

PJ