Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confused
Hi there.
I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confused
One way:
=SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confused
no problem the simple way would be =VLOOKUP formula think of the second sheet as raw data that will only to popluate the next cell with the corresponding data. in sheet1 Column C Column D Contains value Retrieves the value for the second sheet the forlmual in column would be =VLOOKUP(C1,Sheet2!$b$1:$C$3,2,FALSE) I know it looks scary but its not. VLOOKUP is the formula. C1 is were the data is coming form. Sheet2!$b$1:$C$3 is called a table array The next (2) is the column of data form the table array you would like to see False mean it will pull the exact text sting ascending order does not matter "grateful" wrote: Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
Hi, That worked beautifully! Thank you so much. This is what worked for me.
=SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
Hi Dylan, Thank you for your response....I think you're right that VLookup
should work somehow...I've come across it when I was trying to figure it out...even though I couldn't get it to work... I don't really understand it. I tried your formula and it comes back #N/A....and I also tried it this way: =VLOOKUP(C7,'[Workbook2.xls]Current'!$B$1:$C$146,2,FALSE) for example in Row 7, but it still comes back #N/A...so maybe I don't understand how the array thing works.... Thanks a lot. If you have time....I wouldn't mind understanding VLookUp, but if you don't I understand. Thanks! "Dylan @ UAFC" wrote: no problem the simple way would be =VLOOKUP formula think of the second sheet as raw data that will only to popluate the next cell with the corresponding data. in sheet1 Column C Column D Contains value Retrieves the value for the second sheet the forlmual in column would be =VLOOKUP(C1,Sheet2!$b$1:$C$3,2,FALSE) I know it looks scary but its not. VLOOKUP is the formula. C1 is were the data is coming form. Sheet2!$b$1:$C$3 is called a table array The next (2) is the column of data form the table array you would like to see False mean it will pull the exact text sting ascending order does not matter "grateful" wrote: Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
1. That's how SEARCH handles empty cells. You could use an IF test to see if
the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
Thank you so much for your help.
I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t
Found") it tells me I am missing a parentheses Yep... =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? No, you can't do it like that. You'd have to test each file as a separate condition: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Thank you so much for your help. I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
hmmmm....
This is what I have: =IF(SUMPRODUCT(--([Worksheet1.xls]Current!C:C=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--([Worksheet2.xls]Current!C:C=MID(B3,FIND(":",B3)+1,50)))+SUMPRODUCT (--([Worksheet3.xls]Current!C:C=MID(B3,FIND(":",B3)+1,50)))+SUMPRODUCT (--('[Worksheet4.xls]Current'!C:C=MID(B3,FIND(":",B3)+1,50))),"","Not Found") and it is returning: #NUM! Can you see what I am doing wrong? "T. Valko" wrote: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") it tells me I am missing a parentheses Yep... =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? No, you can't do it like that. You'd have to test each file as a separate condition: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Thank you so much for your help. I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
You can't use full-column references with SUMPRODUCT (unless you have
XL2007), so you will have to change all those C:C to C$3:C$146 (or whatever). Hope this helps. Pete On Jan 8, 2:28*pm, grateful wrote: hmmmm.... This is what I have: =IF(SUMPRODUCT(--([Worksheet1.xls]Current!C:C=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--([Worksheet2.xls]Current!C:C=MID(B3,FIND(":",B3)+1,50)))+SUMPR*ODUC T(--([Worksheet3.xls]Current!C:C=MID(B3,FIND(":",B3)+1,50)))+SUMPRODUCT *(--('[Worksheet4.xls]Current'!C:C=MID(B3,FIND(":",B3)+1,50))),"","Not Found") and it is returning: #NUM! Can you see what I am doing wrong? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
Hi Again....
So I think it's working now! Thank you very much. I was wondering if you could tell me what the +1,50 is referring to? Just so I can understand. Thank you. "T. Valko" wrote: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") it tells me I am missing a parentheses Yep... =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? No, you can't do it like that. You'd have to test each file as a separate condition: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Thank you so much for your help. I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
The "+1,50" is part of the MID function.
MID(B3,FIND(":",B3)+1,50) MID retruns a substring of a larger string. Consider this example: B3 = abc:xyz =MID(B3,FIND(":",B3)+1,50) Returns xyz The arguments for MID a B3 = the larger string to look at FIND(":",B3)+1 = the starting position of the substring that we want to extract 50 = the number of characters to extract =MID(abc:xyz,FIND(":",B3)+1,50) The FIND function returns the starting position of the character or characters that we want to look for. We want to extract everything to the right of the colon so we use FIND to find the position of the colon. The colon is the 4th character so FIND(":",abc:xyz) returns 4. We want to extract everything to the right of the colon so we add 1 to the result of the FIND function: FIND(":",B3)+1 = 5. This is now our starting position for the MID function: =MID(abc:xyz,5,50) 50 = the number of characters to extract. 50 is an arbitrary number that is large enough that "guarantees" we extract everything to the right of the colon when what follows the colon is of variable length. abc:xyz abc:xyz123 abc:xyz0 So, in plain English: With the string abc:xyz, starting at character number 5, extract the next 50 characters or however many charaters there are to the end of the string. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi Again.... So I think it's working now! Thank you very much. I was wondering if you could tell me what the +1,50 is referring to? Just so I can understand. Thank you. "T. Valko" wrote: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") it tells me I am missing a parentheses Yep... =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? No, you can't do it like that. You'd have to test each file as a separate condition: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Thank you so much for your help. I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
That was so clear.
Thank you VERY much. "T. Valko" wrote: The "+1,50" is part of the MID function. MID(B3,FIND(":",B3)+1,50) MID retruns a substring of a larger string. Consider this example: B3 = abc:xyz =MID(B3,FIND(":",B3)+1,50) Returns xyz The arguments for MID a B3 = the larger string to look at FIND(":",B3)+1 = the starting position of the substring that we want to extract 50 = the number of characters to extract =MID(abc:xyz,FIND(":",B3)+1,50) The FIND function returns the starting position of the character or characters that we want to look for. We want to extract everything to the right of the colon so we use FIND to find the position of the colon. The colon is the 4th character so FIND(":",abc:xyz) returns 4. We want to extract everything to the right of the colon so we add 1 to the result of the FIND function: FIND(":",B3)+1 = 5. This is now our starting position for the MID function: =MID(abc:xyz,5,50) 50 = the number of characters to extract. 50 is an arbitrary number that is large enough that "guarantees" we extract everything to the right of the colon when what follows the colon is of variable length. abc:xyz abc:xyz123 abc:xyz0 So, in plain English: With the string abc:xyz, starting at character number 5, extract the next 50 characters or however many charaters there are to the end of the string. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi Again.... So I think it's working now! Thank you very much. I was wondering if you could tell me what the +1,50 is referring to? Just so I can understand. Thank you. "T. Valko" wrote: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") it tells me I am missing a parentheses Yep... =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? No, you can't do it like that. You'd have to test each file as a separate condition: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Thank you so much for your help. I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I think I need to use Index, Match, and IsNumber but am confus
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "grateful" wrote in message ... That was so clear. Thank you VERY much. "T. Valko" wrote: The "+1,50" is part of the MID function. MID(B3,FIND(":",B3)+1,50) MID retruns a substring of a larger string. Consider this example: B3 = abc:xyz =MID(B3,FIND(":",B3)+1,50) Returns xyz The arguments for MID a B3 = the larger string to look at FIND(":",B3)+1 = the starting position of the substring that we want to extract 50 = the number of characters to extract =MID(abc:xyz,FIND(":",B3)+1,50) The FIND function returns the starting position of the character or characters that we want to look for. We want to extract everything to the right of the colon so we use FIND to find the position of the colon. The colon is the 4th character so FIND(":",abc:xyz) returns 4. We want to extract everything to the right of the colon so we add 1 to the result of the FIND function: FIND(":",B3)+1 = 5. This is now our starting position for the MID function: =MID(abc:xyz,5,50) 50 = the number of characters to extract. 50 is an arbitrary number that is large enough that "guarantees" we extract everything to the right of the colon when what follows the colon is of variable length. abc:xyz abc:xyz123 abc:xyz0 So, in plain English: With the string abc:xyz, starting at character number 5, extract the next 50 characters or however many charaters there are to the end of the string. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi Again.... So I think it's working now! Thank you very much. I was wondering if you could tell me what the +1,50 is referring to? Just so I can understand. Thank you. "T. Valko" wrote: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") it tells me I am missing a parentheses Yep... =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? No, you can't do it like that. You'd have to test each file as a separate condition: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+ SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Thank you so much for your help. I'm sorry it has taken me so long to get back. I was off on a two week course, and then was away during the holidays. The solution for problem 1, worked perfectectly! Thank you! For problem 2...I am going to work with it. Right now, it tells me I am missing a parentheses, but if I add it to the end...it still doesn't work. If you know what I am missing please let me know. This is what I have: =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Also...If I want it to scan 4 workbooks simulataneously, can I do that, by putting commas between the workbooks? For example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") Can you still help me? :o) Thank you! "T. Valko" wrote: 1. That's how SEARCH handles empty cells. You could use an IF test to see if the cell is empty: =IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............ If the cell is empty the formula returns a blank. 2. As long as the colon is a standard character in all the project names in book2 you can use a formula like this to "flag" the projects: SubProject 1 MainProject:SubProject 1 =IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t Found") -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi, That worked beautifully! Thank you so much. This is what worked for me. =SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146) I have two questions now....I don't know if you can help me. 1) Not all the rows in the first work book have projects...so the formula is actually returning the number "8752". Do you know why it's doing that? 2) Also is there a formula I could use in Work Book 2, to highlight all the projects that are not found in workbook 1? Thank you so much! "T. Valko" wrote: One way: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400) Have the other file open. Start typing the formula: =SUMPRODUCT(--(ISNUMBER(SEARCH(C1, When you get that far use your mouse to point to the range(s) in the other file. -- Biff Microsoft Excel MVP "grateful" wrote in message ... Hi there. I have two workbooks. In the first workbook column C has about 200 subproject names. (Ex. C SubProject 1 SubProject 2 SubProject 3) In the second workbook column B there are about 400 projects (Ex. B C MainProject:SubProject 1 10 Mainproject:SubProject 3 5 Mainproject:SubProject 2 20) Those 200 projects are somewhere in the 400 projects listed in the 2nd workbook. I need a formula in Workbook 1 Column D to look for the sameproject in Workbook 2, column B, even though column B in workbook 2 contains more text than Column C in workbook 1...and then put the corresponding value into Column D Worksheet 1 in the row with the matching project. My difficulty is making an Index Formula that uses Match...when the two cells aren't IDENTICAL....I can use ISNUMBER...but that only gives me a True or False...But I'm pretty sure I then need to INDEX... Can anyone help?? :o) Thank you so much! You are all so smart. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if(and(isnumber(match(... vs if(and(match(... | Excel Discussion (Misc queries) | |||
Isnumber Match Index help request | Excel Discussion (Misc queries) | |||
if, Isnumber, Match, &index query | Excel Discussion (Misc queries) | |||
Match, Index, Vlookup, If (confused) | Excel Discussion (Misc queries) | |||
problem with =isnumber(match(right(... | Excel Worksheet Functions |