I have a master spreadsheet that has a list of all employees in the company,
I have 5 other spreadsheets that have a list of employees that have access
to certain applications. What I want to do is scan through spreadsheets
2,3,4,5 and see which users are in that spreadsheet that are also listed in
the master spreadsheet. If the user is found in the spreadsheets 2-5 I want
it to flag a column in the master spreadsheet... I hope this makes sense...
here is an example..

user A is on the master spreadsheet and is also in spreadsheets 2 and 4
....on the master spreadsheet I have column 1(Webapps) and column 2(Database
apps) ... I would like column 1 and 2 be flagged with a 1 and then columns 3
and 5 flagged with a 0...

I hope this is making sense and any help on this would be appreciated.

Thanks
Gavin....

You could use vlookup formulae on your master spreadsheet to achieve
this. For example in column1 your formula would be something like:
=if(isna(vlookup(employee,sheet2!employeeList,1,0) ),0,1)

Hope this helps
Rowan

Gavin wrote:
I have a master spreadsheet that has a list of all employees in the company,
I have 5 other spreadsheets that have a list of employees that have access
to certain applications. What I want to do is scan through spreadsheets
2,3,4,5 and see which users are in that spreadsheet that are also listed in
the master spreadsheet. If the user is found in the spreadsheets 2-5 I want
it to flag a column in the master spreadsheet... I hope this makes sense...
here is an example..

user A is on the master spreadsheet and is also in spreadsheets 2 and 4
...on the master spreadsheet I have column 1(Webapps) and column 2(Database
apps) ... I would like column 1 and 2 be flagged with a 1 and then columns 3
and 5 flagged with a 0...

I hope this is making sense and any help on this would be appreciated.

Thanks
Gavin....

That's exactly what I was looking for... thanks... just one more question,
how can I apply this to the entire column with the same criteria, when I do
a copy it changes the values in the formula?

Thanks
Gavin...

"Rowan Drummond" wrote in message
...
You could use vlookup formulae on your master spreadsheet to achieve this.
For example in column1 your formula would be something like:
=if(isna(vlookup(employee,sheet2!employeeList,1,0) ),0,1)

Hope this helps
Rowan

Gavin wrote:
I have a master spreadsheet that has a list of all employees in the
company, I have 5 other spreadsheets that have a list of employees that
have access to certain applications. What I want to do is scan through
spreadsheets 2,3,4,5 and see which users are in that spreadsheet that are
also listed in the master spreadsheet. If the user is found in the
spreadsheets 2-5 I want it to flag a column in the master spreadsheet...
I hope this makes sense... here is an example..

user A is on the master spreadsheet and is also in spreadsheets 2 and 4
...on the master spreadsheet I have column 1(Webapps) and column
2(Database apps) ... I would like column 1 and 2 be flagged with a 1 and
then columns 3 and 5 flagged with a 0...

I hope this is making sense and any help on this would be appreciated.

Thanks
Gavin....

You need to use absolute references in the vlookup's second argument. So
something like this:
=if(isna(vlookup(A1,mastersheet!$A$1:$A$500,1,0)), 0,1)
That way when you copy it down the "mastersheet!$A$1:$A$500" section
will be unchanged.

Regards
Rowan

Gavin wrote:
That's exactly what I was looking for... thanks... just one more question,
how can I apply this to the entire column with the same criteria, when I do
a copy it changes the values in the formula?

Thanks
Gavin...

"Rowan Drummond" wrote in message
...

You could use vlookup formulae on your master spreadsheet to achieve this.
For example in column1 your formula would be something like:
=if(isna(vlookup(employee,sheet2!employeeList,1, 0)),0,1)

Hope this helps
Rowan

Gavin wrote:

I have a master spreadsheet that has a list of all employees in the
company, I have 5 other spreadsheets that have a list of employees that
have access to certain applications. What I want to do is scan through
spreadsheets 2,3,4,5 and see which users are in that spreadsheet that are
also listed in the master spreadsheet. If the user is found in the
spreadsheets 2-5 I want it to flag a column in the master spreadsheet...
I hope this makes sense... here is an example..

user A is on the master spreadsheet and is also in spreadsheets 2 and 4
...on the master spreadsheet I have column 1(Webapps) and column
2(Database apps) ... I would like column 1 and 2 be flagged with a 1 and
then columns 3 and 5 flagged with a 0...

I hope this is making sense and any help on this would be appreciated.

Thanks
Gavin....

Post your formula, otherwise I would just be guessing.

Regards
Rowan

Gavin wrote:
Maybee I am missing something here, I can get this to work using the yes/no
method, but am having problems on the 1 and 0 ... any ideas what I might be
missing?

Thanks
Gavin...

"Rowan Drummond" wrote in message
...

You need to use absolute references in the vlookup's second argument. So
something like this:
=if(isna(vlookup(A1,mastersheet!$A$1:$A$500,1,0) ),0,1)
That way when you copy it down the "mastersheet!$A$1:$A$500" section
will be unchanged.

Regards
Rowan

Gavin wrote:

That's exactly what I was looking for... thanks... just one more
question,
how can I apply this to the entire column with the same criteria, when I
do
a copy it changes the values in the formula?

Thanks
Gavin...

"Rowan Drummond" wrote in message
...


You could use vlookup formulae on your master spreadsheet to achieve
this.
For example in column1 your formula would be something like:
=if(isna(vlookup(employee,sheet2!employeeList, 1,0)),0,1)

Hope this helps
Rowan

Gavin wrote:


I have a master spreadsheet that has a list of all employees in the
company, I have 5 other spreadsheets that have a list of employees that
have access to certain applications. What I want to do is scan through
spreadsheets 2,3,4,5 and see which users are in that spreadsheet that
are
also listed in the master spreadsheet. If the user is found in the
spreadsheets 2-5 I want it to flag a column in the master spreadsheet...
I hope this makes sense... here is an example..

user A is on the master spreadsheet and is also in spreadsheets 2 and 4
...on the master spreadsheet I have column 1(Webapps) and column
2(Database apps) ... I would like column 1 and 2 be flagged with a 1 and
then columns 3 and 5 flagged with a 0...

I hope this is making sense and any help on this would be appreciated.

Thanks
Gavin....