Currently, I have the following formula in Column J of the audit sheet:
=IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2)

If I insert a column in the audit sheet, the formula in Column J is now in
Column K, but the formula stays the same.
=IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2)

I would want the formula to adjust to:
=IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2)

All three sheets (audit, upload, and control) are in the same workbook.
Is there a way to make the formulas adjust automatically when a column is
deleted or inserted?

To clarify, even though the data in upload and control sheet isn't moving,
you want the formula on the audit sheet to change?

Then, something like this (assuming the formula is currently in column J)


=IF(INDIRECT("upload!"&ADDRESS(2,COLUMN(J2)))-INDIRECT("control!"&ADDRESS(2,COLUMN(J2)))=0,"",INDIRECT("upload!"&ADDRESS(2,COLUMN(J2)))-INDIRECT("control!"&ADDRESS(2,COLUMN(J2))))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"bethe" wrote:

> Currently, I have the following formula in Column J of the audit sheet:
> =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2)
>
> If I insert a column in the audit sheet, the formula in Column J is now in
> Column K, but the formula stays the same.
> =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2)
>
> I would want the formula to adjust to:
> =IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2)
>
> All three sheets (audit, upload, and control) are in the same workbook.
> Is there a way to make the formulas adjust automatically when a column is
> deleted or inserted?