Forum Discussion
Copy formula in Rows and Excel without changing the reference manually.
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
5 Replies
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)
If numbers, not texts, I'd simplify to =(E$3>=$C4)*(E$3<=$D4)
- 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' Hi max342ss
Looking at the image I understand you are trying to reference a formula on PivotTable,
- Keep the cell on the Pivot Table
- PivotTable Analyze & Design Tab will be activated in the Excel Ribbon
- 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
- 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?
