problem with =isnumber(match(right(...

[Bob Phillips]

I doubt it. MATCH doesn't like 2D.

Try this

=COUNTIF($A$6:$E$10,RIGHT(F15,2)+0)

--
HTH

Bob Phillips

"Luke" <[email protected]> wrote in message
news:[email protected]...
> =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

[Luke]

=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

[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" <[email protected]> wrote in message
> news:[email protected]...
> > =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 Phillips]

Is this what you mean?

=IF(COUNTIF(A$6:E$10,RIGHT(F6,2)+0),G6,"")

--
HTH

Bob Phillips

"Luke" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > =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
> >
> >
> >

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > =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 Phillips]

No sweat. Thanks for letting us know.

--
HTH

Bob Phillips

"Luke" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > =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
> > > >
> > > >
> > > >
> >
> >
> >

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > =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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > =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
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > =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
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > =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
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > =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
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >

[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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > =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 Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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" <[email protected]> wrote in
message
> > > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > > =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
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >

[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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > > news:[email protected]...
> > > > > > > > > > > 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" <[email protected]> wrote in
> message
> > > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > > =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
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
>
>
>

[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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > > > > news:[email protected]...
> > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > > > news:[email protected]...
> > > > > > > > > > > > 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" <[email protected]> wrote in
> > message
> > > > > > > > > > > > >
> > news:[email protected]...
> > > > > > > > > > > > > > =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 Phillips]

Aah, I get what you want now - too late.

I would use

=IF(NOT(ISNUMBER(A6)),"",IF(COUNTIF($C$6:$E$14,RIGHT(B6,2)+0),A6,""))

it is a bit more intuitive.

--
HTH

Bob Phillips

"Luke" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > > > > news:[email protected]...
> > > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > 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" <[email protected]> wrote in
message
> > > > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > > > 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" <[email protected]> wrote in
> > > message
> > > > > > > > > > > > > >
> > > news:[email protected]...
> > > > > > > > > > > > > > >
=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
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >

[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,RIGHT(B6,2)+0),A6,""))
>
> it is a bit more intuitive.
>
> --
> HTH
>
> Bob Phillips
>
> "Luke" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > > > > > news:[email protected]...
> > > > > > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > 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" <[email protected]> wrote in
> message
> > > > > > > > > > > > >
> news:[email protected]...
> > > > > > > > > > > > > > Okay that worked but I only got a "1" where true
> is the

[Bob Phillips]

Well I am glad I could finally be of some help :-)

Regards

Bob
"Luke" <[email protected]> wrote in message
news:[email protected]...
> 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,RIGHT(B6,2)+0),A6,""))
> >
> > it is a bit more intuitive.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Luke" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > > > > >
news:[email protected]...
> > > > > > > > > > > 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" <[email protected]> wrote in
message
> > > > > > > > > > > >
> > news:[email protected]...
> > > > > > > > > > > > > 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" <[email protected]> wrote in
> > message
> > > > > > > > > > > > > >
> > news:[email protected]...
> > > > > > > > > > > > > > > Okay that worked but I only got a "1" where
true
> > is the