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
|