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
|