find Max value and copy paste add value

[bigMe]

dear friends,
please help me on this matter.
i have 2 sheets, in sheet1 contain the Max value address and a number, in sheet2 a column contain several number and a column for result
i need to do a VBA calculation in sheet2 to the MAX value so the result equal with value in sheet1 using copy special paste add, thank you.
note : there are 2 condition depend on the value on column D in sheet1

 

[fjns]

Hi, see the linked file for a possible solution...

The formulas used in the table...
Sheet1a!B2: =ADDRESS(MATCH(MAX(Sheet2a!B2:B8),Sheet2a!B2:B8,0)+1,2,1,,"Sheet2a")
Sheet1a!D2: =Sheet2a!B9-C2
Sheet1b!B2: =ADDRESS(MATCH(MAX(Sheet2b!B2:B8),Sheet2a!B2:B8,0)+1,2,1,,"Sheet2b")
Sheet1b!D2: =Sheet2b!B9-C2
Sheet2a!C2: =IF(AND(MAX(B$2:B$8)=B2,COUNTIF(B$2:B2,B2)=1),B2-Sheet1a!D$2,B2) (Range: C2:C8)
Sheet2b!C2: =IF(AND(MAX(B$2:B$8)=B2,COUNTIF(B$2:B2,B2)=1),B2-Sheet1b!D$2,B2) (Range: C2:C8)

FindMax.xlsx

 

[bigMe]

dear fjns,
thank you very much for the solutions.

regards,
bigMe

 

[fjns]

Dear Friend,
I am very glad that the formulas work.
If I have enough knowledge, I am happy to help at any time.