Sorry about double post here....unsure if no answers due to subject line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating the MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from this
group....
I am also using conditional formatting from B4:AZ65, which highlights the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and so on),
each time I start a new col.otherwise it fails to enter the number of WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry

Hi!

Why not use dynamic ranges?

If there will not be any empty cells within the range A4:x4 or A67:x67

Goto InsertNameDefine
Name: Rng1
Refers to: =OFFSET(Sheet1!$A$4,,,,COUNTA(Sheet1!$4:$4))

Add
Name: Rng2
Refers to: =OFFSET(Sheet1!$A$67,,,,COUNTA(Sheet1!$67:$67))

Then your Sumproduct formula would look like:

=SUMPRODUCT(--(Rng1=Rng2)

For a brief time (probably seconds) the formula will return #VALUE! because
the two ranges will be different sizes until you make entries in both. You
may not even see this but it's possible.

Biff

"Terry" wrote in message
...
Sorry about double post here....unsure if no answers due to subject line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating the MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from this
group....
I am also using conditional formatting from B4:AZ65, which highlights the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and so on),
each time I start a new col.otherwise it fails to enter the number of
WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry

Thanks Biff......
I should have mentioned I am not too familiar with functions beyond the
basic ones.
Your suggestion??
I have tried but my results are "way out".

1) I will clear any entries that exist currently for this excersise.
2) Empty cells: A4:AZ4(score cells) and B67:AZ67 (Max score for each
col.),apart from MAX formula in B67:AZ67.
3) Still have A4:A65 (members names)

Based on this info' will you kindly take me thro' it stage by stage as I am
unsure where I insert the OFFSET formulae you show. I am familier with
creating a named range.

Terry

"Biff" wrote in message
...
Hi!

Why not use dynamic ranges?

If there will not be any empty cells within the range A4:x4 or A67:x67

Goto InsertNameDefine
Name: Rng1
Refers to: =OFFSET(Sheet1!$A$4,,,,COUNTA(Sheet1!$4:$4))

Add
Name: Rng2
Refers to: =OFFSET(Sheet1!$A$67,,,,COUNTA(Sheet1!$67:$67))

Then your Sumproduct formula would look like:

=SUMPRODUCT(--(Rng1=Rng2)

For a brief time (probably seconds) the formula will return #VALUE!
because
the two ranges will be different sizes until you make entries in both. You
may not even see this but it's possible.

Biff

"Terry" wrote in message
...
Sorry about double post here....unsure if no answers due to subject
line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating the
MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from this
group....
I am also using conditional formatting from B4:AZ65, which highlights
the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and so
on),
each time I start a new col.otherwise it fails to enter the number of
WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry

Hi!

Ok, now I'm confused!

Can you send me a copy of your file so that I can see what you're trying to
do? If so, here's my addy:

xl can help at comcast period net

Remove can and change the obvious.

Biff

"Terry" wrote in message
...
Thanks Biff......
I should have mentioned I am not too familiar with functions beyond the
basic ones.
Your suggestion??
I have tried but my results are "way out".

1) I will clear any entries that exist currently for this excersise.
2) Empty cells: A4:AZ4(score cells) and B67:AZ67 (Max score for each
col.),apart from MAX formula in B67:AZ67.
3) Still have A4:A65 (members names)

Based on this info' will you kindly take me thro' it stage by stage as I
am
unsure where I insert the OFFSET formulae you show. I am familier with
creating a named range.

Terry

"Biff" wrote in message
...
Hi!

Why not use dynamic ranges?

If there will not be any empty cells within the range A4:x4 or A67:x67

Goto InsertNameDefine
Name: Rng1
Refers to: =OFFSET(Sheet1!$A$4,,,,COUNTA(Sheet1!$4:$4))

Add
Name: Rng2
Refers to: =OFFSET(Sheet1!$A$67,,,,COUNTA(Sheet1!$67:$67))

Then your Sumproduct formula would look like:

=SUMPRODUCT(--(Rng1=Rng2)

For a brief time (probably seconds) the formula will return #VALUE!
because
the two ranges will be different sizes until you make entries in both.
You
may not even see this but it's possible.

Biff

"Terry" wrote in message
...
Sorry about double post here....unsure if no answers due to subject
line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating the
MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from this
group....
I am also using conditional formatting from B4:AZ65, which highlights
the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and so
on),
each time I start a new col.otherwise it fails to enter the number of
WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry

Biff.........thank you for the help.

You were correct...."No need to use "DYNAMIC" ranges, just account for empty
cells so they would not be counted".

May I ask where I can obtain help with finding out more about such as what
"DYNAMIC" means in the above reference.

Terry

"Biff" wrote in message
...
Hi!

Ok, now I'm confused!

Can you send me a copy of your file so that I can see what you're trying
to
do? If so, here's my addy:

xl can help at comcast period net

Remove can and change the obvious.

Biff

"Terry" wrote in message
...
Thanks Biff......
I should have mentioned I am not too familiar with functions beyond the
basic ones.
Your suggestion??
I have tried but my results are "way out".

1) I will clear any entries that exist currently for this excersise.
2) Empty cells: A4:AZ4(score cells) and B67:AZ67 (Max score for each
col.),apart from MAX formula in B67:AZ67.
3) Still have A4:A65 (members names)

Based on this info' will you kindly take me thro' it stage by stage as I
am
unsure where I insert the OFFSET formulae you show. I am familier with
creating a named range.

Terry

"Biff" wrote in message
...
Hi!

Why not use dynamic ranges?

If there will not be any empty cells within the range A4:x4 or A67:x67

Goto InsertNameDefine
Name: Rng1
Refers to: =OFFSET(Sheet1!$A$4,,,,COUNTA(Sheet1!$4:$4))

Add
Name: Rng2
Refers to: =OFFSET(Sheet1!$A$67,,,,COUNTA(Sheet1!$67:$67))

Then your Sumproduct formula would look like:

=SUMPRODUCT(--(Rng1=Rng2)

For a brief time (probably seconds) the formula will return #VALUE!
because
the two ranges will be different sizes until you make entries in both.
You
may not even see this but it's possible.

Biff

"Terry" wrote in message
...
Sorry about double post here....unsure if no answers due to subject
line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating the
MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from
this
group....
I am also using conditional formatting from B4:AZ65, which highlights
the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and so
on),
each time I start a new col.otherwise it fails to enter the number of
WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry

Hi!

Dynamic means that it changes or is not static.

For example: you add new data to the end of a list on a daily basis.

You can create a named range that is dynamic so that when you do
calculations on that data you don't have to edit your formulas every day to
account for the newly added entries. The dynamic named range automatically
adjusts it's size so that you don't have to do it manually.

See this for examples:

http://contextures.com/xlNames01.html#Dynamic

Biff

"Terry" wrote in message
...
Biff.........thank you for the help.

You were correct...."No need to use "DYNAMIC" ranges, just account for
empty
cells so they would not be counted".

May I ask where I can obtain help with finding out more about such as what
"DYNAMIC" means in the above reference.

Terry

"Biff" wrote in message
...
Hi!

Ok, now I'm confused!

Can you send me a copy of your file so that I can see what you're trying
to
do? If so, here's my addy:

xl can help at comcast period net

Remove can and change the obvious.

Biff

"Terry" wrote in message
...
Thanks Biff......
I should have mentioned I am not too familiar with functions beyond the
basic ones.
Your suggestion??
I have tried but my results are "way out".

1) I will clear any entries that exist currently for this excersise.
2) Empty cells: A4:AZ4(score cells) and B67:AZ67 (Max score for each
col.),apart from MAX formula in B67:AZ67.
3) Still have A4:A65 (members names)

Based on this info' will you kindly take me thro' it stage by stage as
I
am
unsure where I insert the OFFSET formulae you show. I am familier with
creating a named range.

Terry

"Biff" wrote in message
...
Hi!

Why not use dynamic ranges?

If there will not be any empty cells within the range A4:x4 or A67:x67

Goto InsertNameDefine
Name: Rng1
Refers to: =OFFSET(Sheet1!$A$4,,,,COUNTA(Sheet1!$4:$4))

Add
Name: Rng2
Refers to: =OFFSET(Sheet1!$A$67,,,,COUNTA(Sheet1!$67:$67))

Then your Sumproduct formula would look like:

=SUMPRODUCT(--(Rng1=Rng2)

For a brief time (probably seconds) the formula will return #VALUE!
because
the two ranges will be different sizes until you make entries in both.
You
may not even see this but it's possible.

Biff

"Terry" wrote in message
...
Sorry about double post here....unsure if no answers due to subject
line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating
the
MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from
this
group....
I am also using conditional formatting from B4:AZ65, which
highlights
the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and
so
on),
each time I start a new col.otherwise it fails to enter the number
of
WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry

Thanks Biff

I will study the suggested examples.

Terry
"Biff" wrote in message
...
Hi!

Dynamic means that it changes or is not static.

For example: you add new data to the end of a list on a daily basis.

You can create a named range that is dynamic so that when you do
calculations on that data you don't have to edit your formulas every day
to
account for the newly added entries. The dynamic named range automatically
adjusts it's size so that you don't have to do it manually.

See this for examples:

http://contextures.com/xlNames01.html#Dynamic

Biff

"Terry" wrote in message
...
Biff.........thank you for the help.

You were correct...."No need to use "DYNAMIC" ranges, just account for
empty
cells so they would not be counted".

May I ask where I can obtain help with finding out more about such as
what
"DYNAMIC" means in the above reference.

Terry

"Biff" wrote in message
...
Hi!

Ok, now I'm confused!

Can you send me a copy of your file so that I can see what you're
trying
to
do? If so, here's my addy:

xl can help at comcast period net

Remove can and change the obvious.

Biff

"Terry" wrote in message
...
Thanks Biff......
I should have mentioned I am not too familiar with functions beyond
the
basic ones.
Your suggestion??
I have tried but my results are "way out".

1) I will clear any entries that exist currently for this excersise.
2) Empty cells: A4:AZ4(score cells) and B67:AZ67 (Max score for each
col.),apart from MAX formula in B67:AZ67.
3) Still have A4:A65 (members names)

Based on this info' will you kindly take me thro' it stage by stage
as
I
am
unsure where I insert the OFFSET formulae you show. I am familier
with
creating a named range.

Terry

"Biff" wrote in message
...
Hi!

Why not use dynamic ranges?

If there will not be any empty cells within the range A4:x4 or
A67:x67

Goto InsertNameDefine
Name: Rng1
Refers to: =OFFSET(Sheet1!$A$4,,,,COUNTA(Sheet1!$4:$4))

Add
Name: Rng2
Refers to: =OFFSET(Sheet1!$A$67,,,,COUNTA(Sheet1!$67:$67))

Then your Sumproduct formula would look like:

=SUMPRODUCT(--(Rng1=Rng2)

For a brief time (probably seconds) the formula will return #VALUE!
because
the two ranges will be different sizes until you make entries in
both.
You
may not even see this but it's possible.

Biff

"Terry" wrote in message
...
Sorry about double post here....unsure if no answers due to
subject
line?

Win Xp Pr0
Office Xp

I have created a workbook for recording scores at my bowls club.
The MAIN worksheet is where I would appreciate help in automating
the
MAX
score entry for each column entries.
Col. A = all members names.
Cols. B : AZ18 = cells for entering each score.
Col. BD has a formula I use for generating the MAX score in each
Col.(=Sumproduct(--(A4:G4=$A$67:$G67)), I obtained gratefully from
this
group....
I am also using conditional formatting from B4:AZ65, which
highlights
the
MAX score in each column.(=B4=MAX(B$:B65)).
I have to adjust the Sumproduct formula (G4 ...alter to col H4 and
so
on),
each time I start a new col.otherwise it fails to enter the number
of
WIN/s
against the respective MAX scorer/s.
Am I able to automate this particular area please.
Hope I have explained sufficiently for you ?

TIA
Terry