Copy formula in Rows and Excel without changing the reference manually.

[max342ss]

Is there any easy way I can copy IF statement formula to rows and columns both. I have to change the reference for every row to drag it to corresponding columns. I am enclosing the screenshot as an example

[#2]

Hi @max342ss

Looking at the image I understand you are trying to reference a formula on PivotTable,

1. Keep the cell on the Pivot Table
2. PivotTable Analyze & Design Tab will be activated in the Excel Ribbon
3. Select PivotTable Analyze > Option > Uncheck “Generate GetPivotData”

this blog might be helpful for your query

How to remove GETPIVOTDATA in Excel for Mac and Windows 

Regards, Faraz Shaikh | Microsoft MVP, MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget to mark it as Official/Best Answer to help the other members find it more

[#3]

Hi Faraz,
No, the formula has been applied to a normal cell (The pivot table is only until Column D) It's an IF statement that needs to be copied in both rows and columns, we have to change the reference every time for the row to drag it to corresponding columns. Is there a way to do it easy it quick?

[#4]

If i understand you correctly, then what you need to look into is the absolute/relative references
https://trumpexcel.com/absolute-relative-mixed-cell-references/
This is the way to determine how formulas adapts to be 'dragged'

[#5]

@max342ss 

Try this:

=IF(AND(E$3>=$C4,E$3<=$D4,"1",0")

This will do what you ask for.

But perhaps better to use:

=--AND(E$3>=$C4,E$3<=$D4)

[#6]

@Riny_van_Eekelen 

If numbers, not texts, I'd simplify to =(E$3>=$C4)*(E$3<=$D4)