Need Formula to "Adjust" to New Column Assignment
Within the same workbook, I have 3 worksheets: Control, Upload, and Audit.
Upload J2 = 500 Control J2 = 200 Currently, I have the following formula in the Audit worksheet: =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2) So, Audit J2 = 300. I then need to insert a column in all three worksheets between I and J. So the values that were formerly in Column J are now in Column K. Upload K2 value = 500 Control K2 value = 200 But the formula in Audit K2 still references J, so it's not showing 300. I need the formula that is now in Column K to be: =IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2) Is there a way to make the formulas adjust automatically when a column is inserted or deleted? Please help! Thanks Beth |
Need Formula to "Adjust" to New Column Assignment
I am unable to duplicate your problem but your formula isn't correct.
=IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2) Correcting the formula to: =IF(Upload!J2-Control!J2=0,"",Upload!J2-Control!J2) Using that corrected formula, when I insert new columns on the sheets the formula automatically changes to: =IF(Upload!K2-Control!K2=0,"",Upload!K2-Control!K2) -- Biff Microsoft Excel MVP "bethe" wrote in message ... Within the same workbook, I have 3 worksheets: Control, Upload, and Audit. Upload J2 = 500 Control J2 = 200 Currently, I have the following formula in the Audit worksheet: =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2) So, Audit J2 = 300. I then need to insert a column in all three worksheets between I and J. So the values that were formerly in Column J are now in Column K. Upload K2 value = 500 Control K2 value = 200 But the formula in Audit K2 still references J, so it's not showing 300. I need the formula that is now in Column K to be: =IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2) Is there a way to make the formulas adjust automatically when a column is inserted or deleted? Please help! Thanks Beth |
Need Formula to "Adjust" to New Column Assignment
Thanks for your reply, but isn't working in my situation.
It works when I insert a column in Control, then insert the same column into Upload, then insert the same column into Audit. However, I am inserting data into Upload and Control that already has additional columns. Then I need to adjust the Audit sheet to match the columns in Upload and Control, so then I need to insert. This formula does not automatically adjust in the above situation. "T. Valko" wrote: I am unable to duplicate your problem but your formula isn't correct. =IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2) Correcting the formula to: =IF(Upload!J2-Control!J2=0,"",Upload!J2-Control!J2) Using that corrected formula, when I insert new columns on the sheets the formula automatically changes to: =IF(Upload!K2-Control!K2=0,"",Upload!K2-Control!K2) -- Biff Microsoft Excel MVP "bethe" wrote in message ... Within the same workbook, I have 3 worksheets: Control, Upload, and Audit. Upload J2 = 500 Control J2 = 200 Currently, I have the following formula in the Audit worksheet: =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2) So, Audit J2 = 300. I then need to insert a column in all three worksheets between I and J. So the values that were formerly in Column J are now in Column K. Upload K2 value = 500 Control K2 value = 200 But the formula in Audit K2 still references J, so it's not showing 300. I need the formula that is now in Column K to be: =IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2) Is there a way to make the formulas adjust automatically when a column is inserted or deleted? Please help! Thanks Beth . |
Need Formula to "Adjust" to New Column Assignment
I'm with T. Valko, bethe; when I put values in Control!J2 and Upload!J2 and
=IF(Upload!K2-Control!K2=0," ",Upload!K2-Control!K2) in Audit!J2, then insert a new column in all three worksheets, the formula changes correctly. I'm using Excel 2003; what's your version? --- "bethe" wrote: Within the same workbook, I have 3 worksheets: Control, Upload, and Audit. Upload J2 = 500 Control J2 = 200 Currently, I have the following formula in the Audit worksheet: =IF((upload!J2-control!J2=0)," ",(upload!J2-control!J2) So, Audit J2 = 300. I then need to insert a column in all three worksheets between I and J. So the values that were formerly in Column J are now in Column K. Upload K2 value = 500 Control K2 value = 200 But the formula in Audit K2 still references J, so it's not showing 300. I need the formula that is now in Column K to be: =IF((upload!K2-control!K2=0)," ",(upload!K2-control!K2) |
All times are GMT +1. The time now is 10:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com