# Calculate based on highest values.

1. ## Calculate based on highest values.

Hello All,

I have this file were in sheet1 there is 5 people for now.They are each given a list to place certain bids in.

if for example sheet1 J3 is greater than K3 and if sheet2 numbers in F3 ,G3 exactly match these numbers then person 1 gets 3pts
if j3 is greater than k3 but the numbers in F3,G3 are not the same then they get 1 point.

if K3 and J3 are equal and match sheet2 numbers then they again get 3pts but again if they are the same (J3,K3) but different to sheet2 F3,G3 and sheet2 numbers are
equal they get 1 point.
The values in sheet1 won't be calculated until values in sheet2 cols F & G are filled in each week.

I can do this in formula's but i won't be running this workbook and i know it will be messed up,so i think if this could run with vba then it can't be messed up.
I know i can protect the sheet,but i would like to study a vba solution.

I have attached a mocked up example.

Thanks for any help with this.  Register To Reply

2. ## Re: Calculate based on highest values. ``Please Login or Register  to view this content.``
assuming that the rows are the same for the sheet1 and sheet2  Register To Reply

3. ## Re: Calculate based on highest values.

Hello rcm,

Thanks for your efforts.
I have done this with formula similar to yours,but i was hoping to get a macro solution.  Register To Reply

4. ## Re: Calculate based on highest values.

Based on your "rules", I dont agree with some of your sample answers...

 J K L M 2 Person 1 Total 3 45 3 1 1 4 97 98 0 0 5 16 25 1 0 6 17 73 1 0

In M, i have my answers using this, copied down...
=IF(J3>K3,IF(AND(J3=Sheet2!\$F3,Sheet1!K3=Sheet2!\$G3),3,1),0)

if j3 is greater than k3 but the numbers in F3,G3 are not the same then they get 1 point.
J5<K5, answer should be 0, you have 1
J6<K6, answer should be 0, you have 1  Register To Reply

5. ## Re: Calculate based on highest values.

OK, here goes the macro ``Please Login or Register  to view this content.``  Register To Reply

6. ## Re: Calculate based on highest values.

Hello rcm,

Thanks for your macro.

Would you mind taking a little time to explain this line ``Please Login or Register  to view this content.``
Thanks.  Register To Reply

7. ## Re: Calculate based on highest values.

the formula offsets the column number (c) so for J=1 then c=10, for j=2 then c=14. c(j) "as a function of j" marks the first column for the person in question for all 5
you could also say c=j*4+6  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1