|
problem with =isnumber(match(right(...
=ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0
The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
I doubt it. MATCH doesn't like 2D.
Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Okay that worked but I only got a "1" where true is the case. I am looking
for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Is this what you mean?
=IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I
works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
No sweat. Thanks for letting us know.
-- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Bob,
Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
I can't understand that data, it doesn't come over as structured enough for
me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Bob,
wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Grrrrr try this if it stays put
A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Okay I have a since of humor,
Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Okay so now that I successfully posted a sample sheet, the problem I am
having is that when I put the formula into column "G", I get results, and then if I drop down to say, cell A300 and enter new data, ALL of fthe results in column "G" change to different results when I hit enter. Likewise if I edit another cell, the results change again automatically a soon as I hit enter. for example I might have results in G9 and G56 and when I edit any given cell, those two results ("G9" & "G56") disappear and some other cells in column G displays results. I won't ask if that makes since because I have never experienced it before. I think it has something to do with the mix of numbers, dates and text. I hope this helps Thank you. Luke "Luke" wrote: Okay I have a since of humor, Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
I am not sure we are getting anywhere here.
Using your data and your updated formula I don't get the results that you get. I get 667 in G1, and nowhere do I get the 45 in column G that you get. And without the data it is hard to envisage the problem that you describe, G9 in your example is already empty, but it could just be that now it finds matches where it didn't before. -- HTH Bob Phillips "Luke" wrote in message ... Okay so now that I successfully posted a sample sheet, the problem I am having is that when I put the formula into column "G", I get results, and then if I drop down to say, cell A300 and enter new data, ALL of fthe results in column "G" change to different results when I hit enter. Likewise if I edit another cell, the results change again automatically a soon as I hit enter. for example I might have results in G9 and G56 and when I edit any given cell, those two results ("G9" & "G56") disappear and some other cells in column G displays results. I won't ask if that makes since because I have never experienced it before. I think it has something to do with the mix of numbers, dates and text. I hope this helps Thank you. Luke "Luke" wrote: Okay I have a since of humor, Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
opps incomplete array in the formula. My apologies
=IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+0),A6,"") I rebuilt this on a blank sheet and I can't get it to do that erratic thing either. Perhaps I have my raw data as messed up as my posts have been. With the formulas above I get the 667 in G1, "since" in G3, "run" in G6 and 45 in G8 So if we can just eliminate the text from showing up I can redo my original sheet. I have a lot of different formulas in the original sheet and a ton of data so it just may be that something I did in there is interfering with it. I'll look it over. Luke "Bob Phillips" wrote: I am not sure we are getting anywhere here. Using your data and your updated formula I don't get the results that you get. I get 667 in G1, and nowhere do I get the 45 in column G that you get. And without the data it is hard to envisage the problem that you describe, G9 in your example is already empty, but it could just be that now it finds matches where it didn't before. -- HTH Bob Phillips "Luke" wrote in message ... Okay so now that I successfully posted a sample sheet, the problem I am having is that when I put the formula into column "G", I get results, and then if I drop down to say, cell A300 and enter new data, ALL of fthe results in column "G" change to different results when I hit enter. Likewise if I edit another cell, the results change again automatically a soon as I hit enter. for example I might have results in G9 and G56 and when I edit any given cell, those two results ("G9" & "G56") disappear and some other cells in column G displays results. I won't ask if that makes since because I have never experienced it before. I think it has something to do with the mix of numbers, dates and text. I hope this helps Thank you. Luke "Luke" wrote: Okay I have a since of humor, Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Bob
I figured out how to stop the text and as far as the erratic changing problem, it went away when I copied the data over to a different sheet. go figure. Sorry for the hassel.. =If(a6=t(A6),"",IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+ 0),A6,"")) Luke "Luke" wrote: opps incomplete array in the formula. My apologies =IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+0),A6,"") I rebuilt this on a blank sheet and I can't get it to do that erratic thing either. Perhaps I have my raw data as messed up as my posts have been. With the formulas above I get the 667 in G1, "since" in G3, "run" in G6 and 45 in G8 So if we can just eliminate the text from showing up I can redo my original sheet. I have a lot of different formulas in the original sheet and a ton of data so it just may be that something I did in there is interfering with it. I'll look it over. Luke "Bob Phillips" wrote: I am not sure we are getting anywhere here. Using your data and your updated formula I don't get the results that you get. I get 667 in G1, and nowhere do I get the 45 in column G that you get. And without the data it is hard to envisage the problem that you describe, G9 in your example is already empty, but it could just be that now it finds matches where it didn't before. -- HTH Bob Phillips "Luke" wrote in message ... Okay so now that I successfully posted a sample sheet, the problem I am having is that when I put the formula into column "G", I get results, and then if I drop down to say, cell A300 and enter new data, ALL of fthe results in column "G" change to different results when I hit enter. Likewise if I edit another cell, the results change again automatically a soon as I hit enter. for example I might have results in G9 and G56 and when I edit any given cell, those two results ("G9" & "G56") disappear and some other cells in column G displays results. I won't ask if that makes since because I have never experienced it before. I think it has something to do with the mix of numbers, dates and text. I hope this helps Thank you. Luke "Luke" wrote: Okay I have a since of humor, Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
Aah, I get what you want now - too late.
I would use =IF(NOT(ISNUMBER(A6)),"",IF(COUNTIF($C$6:$E$14,RIG HT(B6,2)+0),A6,"")) it is a bit more intuitive. -- HTH Bob Phillips "Luke" wrote in message ... Bob I figured out how to stop the text and as far as the erratic changing problem, it went away when I copied the data over to a different sheet. go figure. Sorry for the hassel.. =If(a6=t(A6),"",IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+ 0),A6,"")) Luke "Luke" wrote: opps incomplete array in the formula. My apologies =IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+0),A6,"") I rebuilt this on a blank sheet and I can't get it to do that erratic thing either. Perhaps I have my raw data as messed up as my posts have been. With the formulas above I get the 667 in G1, "since" in G3, "run" in G6 and 45 in G8 So if we can just eliminate the text from showing up I can redo my original sheet. I have a lot of different formulas in the original sheet and a ton of data so it just may be that something I did in there is interfering with it. I'll look it over. Luke "Bob Phillips" wrote: I am not sure we are getting anywhere here. Using your data and your updated formula I don't get the results that you get. I get 667 in G1, and nowhere do I get the 45 in column G that you get. And without the data it is hard to envisage the problem that you describe, G9 in your example is already empty, but it could just be that now it finds matches where it didn't before. -- HTH Bob Phillips "Luke" wrote in message ... Okay so now that I successfully posted a sample sheet, the problem I am having is that when I put the formula into column "G", I get results, and then if I drop down to say, cell A300 and enter new data, ALL of fthe results in column "G" change to different results when I hit enter. Likewise if I edit another cell, the results change again automatically a soon as I hit enter. for example I might have results in G9 and G56 and when I edit any given cell, those two results ("G9" & "G56") disappear and some other cells in column G displays results. I won't ask if that makes since because I have never experienced it before. I think it has something to do with the mix of numbers, dates and text. I hope this helps Thank you. Luke "Luke" wrote: Okay I have a since of humor, Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the case. I am looking for a specific result, of which I didn't mention... Just thought the formula would automatically do it I guess. I digress. Let's do this: A B C D E F G 50 45 48 49 55 752 695 60 65 58 59 66 0 2596 70 75 68 69 77 1595 215 80 85 78 79 88 7355 795 90 95 98 89 99 7 638 If "F" column (match right 2 digits with any of A:E) then display G Does that make since. sorry I left out the details Bob. Thanks Luke "Bob Phillips" wrote: I doubt it. MATCH doesn't like 2D. Try this =COUNTIF($A$6:$E$10,RIGHT(F15,2)+0) -- HTH Bob Phillips "Luke" wrote in message ... =ISNUMBER(MATCH(RIGHT(F15,2)+0,$A$6:$E$10,0))+0 The formula above worked, I thought, but now for some reason I can't get anything but zero. Here's a sample of sheet. A B C D E F 50 45 48 49 55 752 60 65 58 59 66 0 70 75 68 69 77 1595 80 85 78 79 88 7355 90 95 98 89 99 7 Any help would be great Thank you! Luke |
There you go figuring stuff out again :)
That does it even better than mine.. way faster Thanks Luke "Bob Phillips" wrote: Aah, I get what you want now - too late. I would use =IF(NOT(ISNUMBER(A6)),"",IF(COUNTIF($C$6:$E$14,RIG HT(B6,2)+0),A6,"")) it is a bit more intuitive. -- HTH Bob Phillips "Luke" wrote in message ... Bob I figured out how to stop the text and as far as the erratic changing problem, it went away when I copied the data over to a different sheet. go figure. Sorry for the hassel.. =If(a6=t(A6),"",IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+ 0),A6,"")) Luke "Luke" wrote: opps incomplete array in the formula. My apologies =IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+0),A6,"") I rebuilt this on a blank sheet and I can't get it to do that erratic thing either. Perhaps I have my raw data as messed up as my posts have been. With the formulas above I get the 667 in G1, "since" in G3, "run" in G6 and 45 in G8 So if we can just eliminate the text from showing up I can redo my original sheet. I have a lot of different formulas in the original sheet and a ton of data so it just may be that something I did in there is interfering with it. I'll look it over. Luke "Bob Phillips" wrote: I am not sure we are getting anywhere here. Using your data and your updated formula I don't get the results that you get. I get 667 in G1, and nowhere do I get the 45 in column G that you get. And without the data it is hard to envisage the problem that you describe, G9 in your example is already empty, but it could just be that now it finds matches where it didn't before. -- HTH Bob Phillips "Luke" wrote in message ... Okay so now that I successfully posted a sample sheet, the problem I am having is that when I put the formula into column "G", I get results, and then if I drop down to say, cell A300 and enter new data, ALL of fthe results in column "G" change to different results when I hit enter. Likewise if I edit another cell, the results change again automatically a soon as I hit enter. for example I might have results in G9 and G56 and when I edit any given cell, those two results ("G9" & "G56") disappear and some other cells in column G displays results. I won't ask if that makes since because I have never experienced it before. I think it has something to do with the mix of numbers, dates and text. I hope this helps Thank you. Luke "Luke" wrote: Okay I have a since of humor, Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the |
Well I am glad I could finally be of some help :-)
Regards Bob "Luke" wrote in message ... There you go figuring stuff out again :) That does it even better than mine.. way faster Thanks Luke "Bob Phillips" wrote: Aah, I get what you want now - too late. I would use =IF(NOT(ISNUMBER(A6)),"",IF(COUNTIF($C$6:$E$14,RIG HT(B6,2)+0),A6,"")) it is a bit more intuitive. -- HTH Bob Phillips "Luke" wrote in message ... Bob I figured out how to stop the text and as far as the erratic changing problem, it went away when I copied the data over to a different sheet. go figure. Sorry for the hassel.. =If(a6=t(A6),"",IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+ 0),A6,"")) Luke "Luke" wrote: opps incomplete array in the formula. My apologies =IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+0),A6,"") I rebuilt this on a blank sheet and I can't get it to do that erratic thing either. Perhaps I have my raw data as messed up as my posts have been. With the formulas above I get the 667 in G1, "since" in G3, "run" in G6 and 45 in G8 So if we can just eliminate the text from showing up I can redo my original sheet. I have a lot of different formulas in the original sheet and a ton of data so it just may be that something I did in there is interfering with it. I'll look it over. Luke "Bob Phillips" wrote: I am not sure we are getting anywhere here. Using your data and your updated formula I don't get the results that you get. I get 667 in G1, and nowhere do I get the 45 in column G that you get. And without the data it is hard to envisage the problem that you describe, G9 in your example is already empty, but it could just be that now it finds matches where it didn't before. -- HTH Bob Phillips "Luke" wrote in message ... Okay so now that I successfully posted a sample sheet, the problem I am having is that when I put the formula into column "G", I get results, and then if I drop down to say, cell A300 and enter new data, ALL of fthe results in column "G" change to different results when I hit enter. Likewise if I edit another cell, the results change again automatically a soon as I hit enter. for example I might have results in G9 and G56 and when I edit any given cell, those two results ("G9" & "G56") disappear and some other cells in column G displays results. I won't ask if that makes since because I have never experienced it before. I think it has something to do with the mix of numbers, dates and text. I hope this helps Thank you. Luke "Luke" wrote: Okay I have a since of humor, Try this one A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since 595 30 25 28 first 40 35 38 days 50 45 48 RunTot 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Grrrrr try this if it stays put A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 "Luke" wrote: Bob, wow that looked great when I sent it.. that's not "exactly" how it really looks. Here try this: A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") The formula needs to say; display any "right two digits" in col "B" that match any numbers in the array $C$6:$E$10, and display content of respective cell in col "A" into col. "G" without any text. Again in this example "45" is the only result that should be displayed in "G" which is what does work, but two things are happening: 1. results in "G" column are changing erratically when I add data to other cells. 2. Text are a result of the formula and I only want numbers displayed. I hope this helps. Thank you Luke "Bob Phillips" wrote: I can't understand that data, it doesn't come over as structured enough for me to tell what is in which cell. I also don't understand what problem you are getting. -- HTH Bob Phillips "Luke" wrote in message ... Bob, Curiously the formula results change every time I edit a cell, any cell, even if they are not in relation to the formula. I think I know why but don't know how to fix it. The modified formula is IN COLUMN G6 =IF(COUNTIF($C$6:$E$10,RIGHT(B6,2)+0),A6,"") In "$C$6:$E$15" there are only numbers, no text In "RIGHT(B6,2)+0".. "B6" column has numbers (from formulas) as well as dates In "A6" column there are text and numbers. Again, I didn't think :) it would be a problem so I left out those details.. Sorry for that. Here is a real sample of EXACTLY what I have. A B C D E G 667 9/23/05 10 5 8 John 20 15 18 Since last 595 30 25 28 first out 40 35 38 days between 50 45 48 Running Total 595 60 65 58 5 1629 70 75 68 45 520 80 85 78 45 23 855 90 95 98 So in this example "45" is the only result that should be displayed in "G" with out changing when I add data to other cells. Is That Possible? Thank You Luke "Bob Phillips" wrote: No sweat. Thanks for letting us know. -- HTH Bob Phillips "Luke" wrote in message ... Yes Bob, adding the absolutes for the array $A$6:$E$10 was all it needed. I works Great. Sorry I took so long to answer.. Had to leave for work. Thanks again Luke "Bob Phillips" wrote: Is this what you mean? =IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"") -- HTH Bob Phillips "Luke" wrote in message ... Okay that worked but I only got a "1" where true is the |
| All times are GMT +1. The time now is 10:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com