Mark if cells match on 2 sheets
 

I need a macro to tell me which tools from one worksheet are on order o
another worksheet. Worksheet 'NSN LIST REF' has every tool. Workshee
'ORDER' only has the tools on order.

From what I know the macro should work like this:

Match cell in column F9:F4635 'NSN LIST REF' to cell on 'ORDER' colum
N,
if it matchs and cells in 'ORDER' column AK equals anything other tha
"RCV'D" then put an "O" on worksheet 'NSN LIST REF' column S9:S4635. I
'ORDER' column AK does equal "RCV'D" or is not there at all then leav
all those cells blank on 'NSN LIST REF'.

It should look like this:

'NSN LIST REF'
_Column_F_____/____Column_S_
Screwdriver___/____O
Hammer

'ORDER'
_Column_N_____/____Column_AK_
Screwdriver ___/____209BAGSA
Hammer______/____RCV'D

Since Hammer on 'ORDER' is 'RCV'D" there won't be an "O" on 'NSN LIS
REF'.
If Hammer wasn't on 'ORDER' there shouldn't be anything next to Hamme
on 'NSN LIST REF'

--
Optitro
-----------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=56742

Mark if cells match on 2 sheets
 

Have a look at this formula that you should place in column "S" of th
NSN LIST REF.

=IF(AND(NOT(ISERROR(VLOOKUP(F1,ORDER!$N$1:$N$2,1,F ALSE))),UPPER(VLOOKUP(F1,ORDER!$N$1:$AK$2,24,FALSE ))<"RCV'D"),"O","")


Optitron Wrote:
I need a macro to tell me which tools from one worksheet are on order o
another worksheet. Worksheet 'NSN LIST REF' has every tool. Workshee
'ORDER' only has the tools on order.

From what I know the macro should work like this:

Match cell in column F9:F4635 'NSN LIST REF' to cell on 'ORDER' colum
N,
if it matchs and cells in 'ORDER' column AK equals anything other tha
"RCV'D" then put an "O" on worksheet 'NSN LIST REF' column S9:S4635. I
'ORDER' column AK does equal "RCV'D" or is not there at all then leav
all those cells blank on 'NSN LIST REF'.

It should look like this:

'NSN LIST REF'
_Column_F_____/____Column_S_
Screwdriver___/____O
Hammer

'ORDER'
_Column_N_____/____Column_AK_
Screwdriver ___/____209BAGSA
Hammer______/____RCV'D

Since Hammer on 'ORDER' is 'RCV'D" there won't be an "O" on 'NSN LIS
REF'.
If Hammer wasn't on 'ORDER' there shouldn't be anything next to Hamme
on 'NSN LIST REF'

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56742

Mark if cells match on 2 sheets
 

I copied that into S9:S4635 but all I get is "#N/A"


Excelenator Wrote:
Have a look at this formula that you should place in column "S" of th
NSN LIST REF.

=IF(AND(NOT(ISERROR(VLOOKUP(F1,ORDER!$N$1:$N$2,1,F ALSE))),UPPER(VLOOKUP(F1,ORDER!$N$1:$AK$2,24,FALSE ))<"RCV'D"),"O",""

--
Optitro
-----------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=56742

Mark if cells match on 2 sheets
 

Did you adjust the ranges in the formula to match your ranges?

=IF(AND(NOT(ISERROR(VLOOKUP(F*9*,ORDER!$N$*9*:$N$* 4635*,1,F
ALSE))),UPPER(VLOOKUP(F*9*,ORDER!$N$*9*:$AK$*4635* ,24,FALSE
))<"RCV'D"),"O","")

Optitron Wrote:
I copied that into S9:S4635 but all I get is "#N/A"


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567425

Mark if cells match on 2 sheets
 

OK that works. Now the problem is I either get "#N/A", "O", or nothing.
It gives me nothing when that tool has been ordered but now says
"RCV'D". It gives me "#N/A" when that tool hasn't been on 'ORDER' at
all. I would rather have either nothing or "O".


Excelenator Wrote:
Did you adjust the ranges in the formula to match your ranges?

=IF(AND(NOT(ISERROR(VLOOKUP(F*9*,ORDER!$N$*9*:$N$* 4635*,1,F
ALSE))),UPPER(VLOOKUP(F*9*,ORDER!$N$*9*:$AK$*4635* ,24,FALSE
))<"RCV'D"),"O","")


--
Optitron
------------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...o&userid=26729
View this thread: http://www.excelforum.com/showthread...hreadid=567425

Mark if cells match on 2 sheets
 

Ok here goes:


Code:
--------------------
=IF(AND(NOT(ISERROR(VLOOKUP(F9,ORDER!$N$9:$N$4634, 1,FALSE))),IF(NOT(ISERROR(VLOOKUP(F9,ORDER!$N$9:$A K$4634,24,FALSE ))),UPPER(VLOOKUP(F9,ORDER!$N$9:$AK$4634,24,FALSE ))<"RCV'D",FALSE)),"O","")
--------------------


Optitron Wrote:
OK that works. Now the problem is I either get "#N/A", "O", or nothing.
It gives me nothing when that tool has been ordered but now says
"RCV'D". It gives me "#N/A" when that tool hasn't been on 'ORDER' at
all. I would rather have either nothing or "O".


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567425

Mark if cells match on 2 sheets
 

That did it. I appreciate it.

Shaw

--
Optitro
-----------------------------------------------------------------------
Optitron's Profile: http://www.excelforum.com/member.php...fo&userid=2672
View this thread: http://www.excelforum.com/showthread.php?threadid=56742

Mark if cells match on 2 sheets
 

any time!

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56742