Prevent formulas from changing when macro is run

0

Hi, I have a worksheet with a macro that I manually run customer data through every week. I have created an additional sheet in the same workbook which uses a series of "if" formulas to populate client name and dates of contact from the macro sheet into a separate sheet. It works fine...until I update the macro....When I run new data, the formulas all change to return data in a different column. So, say that customer name is in column b and last time I spoke to them in column f, when i run the macro, the columns change so now it returns c and g and it's no longer pulling the data I want. How can I prevent this from happening?

Answer
Discuss

Answers

0

In your formula cell references, use absolute references. Put dollar signs in front of the rows and columns in the formula.

This:

=A1

would be come this:

=$A$1

Do this for your IF statements and none of the references will change no matter where the formulas are moved.

Discuss

Discussion

Hi Don, Thanks for the idea but my formula already uses absolutes. The problem is that when I run a macro, I think it rewrites data and forcibly changes my formulas. So, as an example before running the macro the formula is : 
=IF(AL!$BW2>$A$1,ROW(),"")
when it is run, it changes to:
=IF(AL!$CF2>$A$1,ROW(),"")
seseley Oct 2, '16 at 3:46 pm
I'd need to see your macro to be able to troubleshoot it. If you edit your original question and post the macro there, I can take a look at it.
don (rep: 1482) Oct 2, '16 at 6:27 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login