Help required in SUMPRODUCT formula

[MYEM1983]

Dear All,

If I have wrote following formula in cell C86 and if I copy the same formula on the adjacent cell then value i.e. DATA!$A8 is not changing automatically to DATA!$A9 or value DATA!$B11 is not changing to DATA!$B12

=SUMPRODUCT(('MAIN SHEET'!$C$2:$C$3726=DATA!$A8)*('MAIN SHEET'!$D$2:$D$3726=DATA!$B11)*('MAIN SHEET'!$H$2:$H$3726=DATA!$C$2)*('MAIN SHEET'!$I$2:$I$3726=DATA!$E$3)*('MAIN SHEET'!$J$2:$J$3726=DATA!$G$22)*('MAIN SHEET'!$G2:$G$3726))

Is there is any way these value change automatically if we copy and past?

Thanks in advance for your kind and quick reply.

[sktneer]

May be this......if you are copying the formula across

Please Login or Register  to view this content.

[MYEM1983]

thanks but it didn't worked may be I must not be doing it correctly, so finally I edited all the 1689 formulas in one sheet manually I will look into the above again in detail later.

thanks again!

[FDibbins]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Jonmo1]

Exactly HOW are you copying/pasting the formula from one cell to another?
Are you copying the cell, or entering the cell and copying the formula text from the formula bar?
Also, what cell addresses are you copying from/to ?