I hope "we're" done with this one! <g

Good luck!

Biff

"Killer" wrote in message
...
Stupid me! works awesome!

Thanks a million......



"T. Valko" wrote:

Did you enter it as an array?

CTRL,SHIFT,ENTER (not just ENTER)

screencap:

http://img157.imageshack.us/img157/9123/poolzy0.jpg

Biff

"Killer" wrote in message
...
Hey Biff

I gave what you have suggested a try and it's not showing the correct
result.

Example:
DAL, IND, NE, CAR should be all "P"
And
CIN, NO should be all "X"

Right now they are showing as #N/A with this formula.
I have uploaded an example again.

Thanks!


"T. Valko" wrote:

Enter this array formula on the Killer sheet in cell E15:

=IF(D15="","",IF(ISNUMBER(MATCH(D15,'W1'!C$47:AK$4 7,0)),"?",IF(INDEX('W1'!C$47:AK$47,MATCH(1,--ISNUMBER(SEARCH(D15,'W1'!C$44:AK$44&'W1'!C$45:AK$4 5)),0))="Push","P","X")))

Copy down as needed.

Biff

"Killer" wrote in message
...
WOW I see what you mean now by merged cells and useless formulas.

I have removed the formula in cell E, E44:E47 as it now required it
was
a
bad copy and paste on my behalf, my bad. I have also removed the
merged
cells
as well in week one and will do the same for the rest of the weeks.

I have uploaded the fix to the link below.
http://www.nghl.ca/Football_Pool.zip

Now you should have no problems fixing the formula to show the
correct
result.

Again thank you for your help Biff you are the best.


"T. Valko" wrote:

Oh, OK I see what you mean. I thought those picks would all be for
the
same
game but I see they're for *any* game.

There's a problem however. All those merged cells are messing up
the
ability
to create a formula for this. I don't understand what this is for:
on
sheet
W1 column E, E44:E47 are merged cells and have this formula in it:

=COUNTIF(D45,"?*")

D45 will only contain the score for the underdog. So what's this
formula
for? Why are those cells merged?

Biff

"Killer" wrote in message
...
Hey Biff

I think there still a problem whatever the result is in the first
column
in
Cell C47 it puts the result for every cell no matter what team
has
been
chosen.

I have up load the file so you can see the example..

http://www.nghl.ca/Football_Pool.zip

Take Care!

Rob


"T. Valko" wrote:

The only formula that I need fixed is the following
=IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47=" push","P","X")))

It works OK for me. If on W1 I make Ind the fav, the spread is
14
and
the
score is Ind 14 Dal 0, then the above formula resurns "P" as it
should.

Biff

"Killer" wrote in message
...


"T. Valko" wrote:

This isn't putting the names in order for each week this is
placing
names
in
a leader board under the Killer picks based on the records
same
as
the
master
sheet has been done.

My reply about putting the names in order on the weekly
sheets
was
in
response to your email.

Oh ok so it's best I manually enter the names to save the file
space
got
it.....



right now it's at 1.19 MB and almost completed.

Yeah, but you don't have any data in it yet! And, the last
I've
seen
of
it,
you only have formulas up to week1 and its size at that point
was
.98
mb.
There's still 16 other sheets that need those same formulas.

All formulas have been entered throughout the 17 weeks and
that's
the
reason
the file has reached 1.19 MB

The only formula that I need fixed is the following
=IF(D15="","",IF(D15='W1'!C$47,"?",IF('W1'!C$47="p ush","P","X")))
it shows a Push as a X. and not as a P then I'm done.

Thank You for your help!




Biff

"Killer" wrote in message
...
Hey Buff

This isn't putting the names in order for each week this is
placing
names
in
a leader board under the Killer picks based on the records
same
as
the
master
sheet has been done.

I didn't understand the comment of being "busy" LOL what is
the
average
file
size considered suitable? As it stands right now it's at
1.19
MB
and
almost
completed.

Thanks
Rob


"T. Valko" wrote:

You can't. You'd either have to split that formula into 2
cells
or
you'd
have to change the other formula but then you'd be getting
into
overly
complex monster formula territory. I told you that your
file
is
really
"busy". Well, this is what I meant: You're making things
overly
complex
because of the design of your layout. You can't put the
names
in
order
on
the weekly sheets becuase it results in a circular
reference.
Look
at
how
big your file is already and you don't even have any data
in
it
yet.
How
big
do you think it will be by the time you reach week 17? It
may
be
unusable!

To be perfectly honest, I think you should start over and
keep
it
simple.
Get rid of all those merged cells. Keep any formatting to
a
minimum
and
don't format until you have all the formulas in place and
the
layout
finished. Keep the data in contiguous ranges.

Biff

"Killer" wrote in
message
...
How would chnage it so this formula would work.
=COUNTIF(D15:AK15,"?")+COUNTIF(D15:AK15,"P")*0.5&" -
"&COUNTIF(D15:AK15,"X*")+COUNTIF(D15:AK15,"P") *0.5



"T. Valko" wrote:

The formula in AM15 returns a TEXT string.

When the other formula tries to calculare this
expression:



That's what causes the error.

Biff

"Killer" wrote in
message
...
Sorry it's AM15 not AN15 nothing to do with AN cell
at
all.

In Cell C7 it should list the person name with the
top
record
based
on
cell
AM15 : AM47
instead I get an error #value from cell AM15.. If I
remove
the
formula
and
just add numbers the #value error is removed and
shows
the
person
who
the
top
winner.

The formula in cell AM15: AM47 shows up as 1.5 - 1.5
as
a
test
but
comes
up
as an error #value in cell C7 & E7

C7 is for the name
E7 should list the record

Hope this helps