Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mark certain cells | Excel Worksheet Functions | |||
How can I add active tick mark/check mark boxes in excel? | Excel Discussion (Misc queries) | |||
Graph on each sheet when I mark same cells on multiple sheets | Excel Discussion (Misc queries) | |||
Copy cell values if cells match on two diff sheets | Excel Programming | |||
Mark files and sheets for identification | Excel Programming |