Hi all,

First, here is how my sheets are set up:

Sheet 1

A B C D
Manual# Rev. Manual Type Rev. date

so for example
1 25 Manual 1 10/10/08
9 10 Manual 23 01/02/05
..
..
..

and on Sheet 2

A B C
Manual Type Rev. Revision Date.

so for example

1 26 01/08/08
2 1 12/12/07
..
..
..

Here is my problem. Im trying to create a formula that will compare the two
lists (Manual Name) and if they then check the revision numbers (Rev). If the
revision numbers match, then display True, and if they dont display false.
From there Im going to use a conditional format to display the inital cell as
either green or red, depending on if its true or false.

I would have thought an IF statement would have worked, but for some
reason it is always coming up wit either INVALID, #value or #name and Ive
spent the better part of the week trying to fix this one formula!

If it matters, I am working with 16 manual types and about 30 of each manual
that I want to check the revision numbers against!

Thanks ahead of time!

Are you trying to use VLOOKUP to compare the Manual# in one list to
another? What is your existing formula trying?
ScottD

On Thu, 21 Feb 2008 09:10:02 -0800, JMK
wrote:

Hi all,

First, here is how my sheets are set up:

Sheet 1

A B C D
Manual# Rev. Manual Type Rev. date

so for example
1 25 Manual 1 10/10/08
9 10 Manual 23 01/02/05
.
.
.

and on Sheet 2

A B C
Manual Type Rev. Revision Date.

so for example

1 26 01/08/08
2 1 12/12/07
.
.
.

Here is my problem. Im trying to create a formula that will compare the two
lists (Manual Name) and if they then check the revision numbers (Rev). If the
revision numbers match, then display True, and if they dont display false.
From there Im going to use a conditional format to display the inital cell as
either green or red, depending on if its true or false.

I would have thought an IF statement would have worked, but for some
reason it is always coming up wit either INVALID, #value or #name and Ive
spent the better part of the week trying to fix this one formula!

If it matters, I am working with 16 manual types and about 30 of each manual
that I want to check the revision numbers against!

Thanks ahead of time!

Hi Scott.
The current formula is trying to take the entire list of formulas on sheet
1 (which include all of the manuals (all with different names) and compare
them to a master list on sheet 2 (which lists simply the manual name, and not
the manual number). Once that comes back as true Im trying to get the formula
then to check the revision number for that manual on sheet 1 against the
actual revision number on sheet 2 (for the corresponding manual). If all that
comes back properly then itll come back as true, and if not, false
(obviously). Im not sure though where Im getting mixed up!

"scott" wrote:

Are you trying to use VLOOKUP to compare the Manual# in one list to
another? What is your existing formula trying?
ScottD

On Thu, 21 Feb 2008 09:10:02 -0800, JMK
wrote:

Hi all,

First, here is how my sheets are set up:

Sheet 1

A B C D
Manual# Rev. Manual Type Rev. date

so for example
1 25 Manual 1 10/10/08
9 10 Manual 23 01/02/05
.
.
.

and on Sheet 2

A B C
Manual Type Rev. Revision Date.

so for example

1 26 01/08/08
2 1 12/12/07
.
.
.

Here is my problem. Im trying to create a formula that will compare the two
lists (Manual Name) and if they then check the revision numbers (Rev). If the
revision numbers match, then display True, and if they dont display false.
From there Im going to use a conditional format to display the inital cell as
either green or red, depending on if its true or false.

I would have thought an IF statement would have worked, but for some
reason it is always coming up wit either INVALID, #value or #name and Ive
spent the better part of the week trying to fix this one formula!

If it matters, I am working with 16 manual types and about 30 of each manual
that I want to check the revision numbers against!

Thanks ahead of time!

Does the manual name(Manual Type) include the the word 'Manual' such
as 'Manual23' in your example of sheet1?
And sheet2 only has a number for the Manual Type?

On Thu, 21 Feb 2008 11:11:01 -0800, JMK
wrote:

Hi Scott.
The current formula is trying to take the entire list of formulas on sheet
1 (which include all of the manuals (all with different names) and compare
them to a master list on sheet 2 (which lists simply the manual name, and not
the manual number). Once that comes back as true Im trying to get the formula
then to check the revision number for that manual on sheet 1 against the
actual revision number on sheet 2 (for the corresponding manual). If all that
comes back properly then itll come back as true, and if not, false
(obviously). Im not sure though where Im getting mixed up!

"scott" wrote:

Are you trying to use VLOOKUP to compare the Manual# in one list to
another? What is your existing formula trying?
ScottD

On Thu, 21 Feb 2008 09:10:02 -0800, JMK
wrote:

Hi all,

First, here is how my sheets are set up:

Sheet 1

A B C D
Manual# Rev. Manual Type Rev. date

so for example
1 25 Manual 1 10/10/08
9 10 Manual 23 01/02/05
.
.
.

and on Sheet 2

A B C
Manual Type Rev. Revision Date.

so for example

1 26 01/08/08
2 1 12/12/07
.
.
.

Here is my problem. Im trying to create a formula that will compare the two
lists (Manual Name) and if they then check the revision numbers (Rev). If the
revision numbers match, then display True, and if they dont display false.
From there Im going to use a conditional format to display the inital cell as
either green or red, depending on if its true or false.

I would have thought an IF statement would have worked, but for some
reason it is always coming up wit either INVALID, #value or #name and Ive
spent the better part of the week trying to fix this one formula!

If it matters, I am working with 16 manual types and about 30 of each manual
that I want to check the revision numbers against!

Thanks ahead of time!

For most of our manuals, yes, it includes the word manual - which if I think
what you are thinking on this, then we can easily change the rest to include
the word. And yes, the second sheet only has the manual type.
Over lunch I had thought of possibly assigning a number to the manuals and
search via numbers, rather than a name.

"scott" wrote:


Does the manual name(Manual Type) include the the word 'Manual' such
as 'Manual23' in your example of sheet1?
And sheet2 only has a number for the Manual Type?

On Thu, 21 Feb 2008 11:11:01 -0800, JMK
wrote:

Hi Scott.
The current formula is trying to take the entire list of formulas on sheet
1 (which include all of the manuals (all with different names) and compare
them to a master list on sheet 2 (which lists simply the manual name, and not
the manual number). Once that comes back as true Im trying to get the formula
then to check the revision number for that manual on sheet 1 against the
actual revision number on sheet 2 (for the corresponding manual). If all that
comes back properly then itll come back as true, and if not, false
(obviously). Im not sure though where Im getting mixed up!

"scott" wrote:

Are you trying to use VLOOKUP to compare the Manual# in one list to
another? What is your existing formula trying?
ScottD

On Thu, 21 Feb 2008 09:10:02 -0800, JMK
wrote:

Hi all,

First, here is how my sheets are set up:

Sheet 1

A B C D
Manual# Rev. Manual Type Rev. date

so for example
1 25 Manual 1 10/10/08
9 10 Manual 23 01/02/05
.
.
.

and on Sheet 2

A B C
Manual Type Rev. Revision Date.

so for example

1 26 01/08/08
2 1 12/12/07
.
.
.

Here is my problem. Im trying to create a formula that will compare the two
lists (Manual Name) and if they then check the revision numbers (Rev). If the
revision numbers match, then display True, and if they dont display false.
From there Im going to use a conditional format to display the inital cell as
either green or red, depending on if its true or false.

I would have thought an IF statement would have worked, but for some
reason it is always coming up wit either INVALID, #value or #name and Ive
spent the better part of the week trying to fix this one formula!

If it matters, I am working with 16 manual types and about 30 of each manual
that I want to check the revision numbers against!

Thanks ahead of time!

JMK,
If you didn't have control of the inclusion of the name, but it was
consistently "Manual 123" etc. you could do something similar to this
on sheet1

=VLOOKUP(ABS(SUBSTITUTE(C2,"Manual ","")),Sheet2!$A$1:$C$500,2,FALSE)

basically this just eliminates the word "Manual " (with a space) and
is using ABS because the numbers didn't appear to be text on Sheet2.
Then compare the lookup result to the revision # on sheet1 and if they
match "ok" or "not"

=IF(B2=VLOOKUP(ABS(SUBSTITUTE(C2,"Manual
","")),Sheet2!$A$1:$C$500,2,FALSE),"ok","not")

I would do something beyond color so if you want to sort / filter the
results you can see just what you need to address, but that is your
call.


Scott

On Thu, 21 Feb 2008 12:17:03 -0800, JMK
wrote:

For most of our manuals, yes, it includes the word manual - which if I think
what you are thinking on this, then we can easily change the rest to include
the word. And yes, the second sheet only has the manual type.
Over lunch I had thought of possibly assigning a number to the manuals and
search via numbers, rather than a name.

"scott" wrote:


Does the manual name(Manual Type) include the the word 'Manual' such
as 'Manual23' in your example of sheet1?
And sheet2 only has a number for the Manual Type?

On Thu, 21 Feb 2008 11:11:01 -0800, JMK
wrote:

Hi Scott.
The current formula is trying to take the entire list of formulas on sheet
1 (which include all of the manuals (all with different names) and compare
them to a master list on sheet 2 (which lists simply the manual name, and not
the manual number). Once that comes back as true Im trying to get the formula
then to check the revision number for that manual on sheet 1 against the
actual revision number on sheet 2 (for the corresponding manual). If all that
comes back properly then itll come back as true, and if not, false
(obviously). Im not sure though where Im getting mixed up!

"scott" wrote:

Are you trying to use VLOOKUP to compare the Manual# in one list to
another? What is your existing formula trying?
ScottD

On Thu, 21 Feb 2008 09:10:02 -0800, JMK
wrote:

Hi all,

First, here is how my sheets are set up:

Sheet 1

A B C D
Manual# Rev. Manual Type Rev. date

so for example
1 25 Manual 1 10/10/08
9 10 Manual 23 01/02/05
.
.
.

and on Sheet 2

A B C
Manual Type Rev. Revision Date.

so for example

1 26 01/08/08
2 1 12/12/07
.
.
.

Here is my problem. Im trying to create a formula that will compare the two
lists (Manual Name) and if they then check the revision numbers (Rev). If the
revision numbers match, then display True, and if they dont display false.
From there Im going to use a conditional format to display the inital cell as
either green or red, depending on if its true or false.

I would have thought an IF statement would have worked, but for some
reason it is always coming up wit either INVALID, #value or #name and Ive
spent the better part of the week trying to fix this one formula!

If it matters, I am working with 16 manual types and about 30 of each manual
that I want to check the revision numbers against!

Thanks ahead of time!