Sumproduct..help please

[Terry]

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]

Hi!

Why not use dynamic ranges?

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

Goto Insert>Name>Define
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]

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]

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]

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]

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]

Thanks Biff

I will study the suggested examples.

Terry