CODEWITHSUNDEEP
vbaexcel
Cornelis
Cornelis

Reputation: 435

Prevent Cell formula from changing

I've got an Excel sheet where values can be entered.

For example: 

A2 = product name

B2 = product number

C2 = company number

D2 = "#" (allway's the # sign

E2 = "A1"&"D1"&"B1"&"D1"&"C1"

After a button is pressed a macro wil run. The values will automaticly be cut-paste to a history sheet.

The formula of Cell E2 changes into the location where the values are pasted(in this case th hisory sheet).

I'd like to prevent the formula in Cell E2 to change, so I entered $ signs before the numbers and letters. and entered sheets names before it. This did'nt make a differnce at all.

Does anyone knows a sollution for this? (or do i juist have to write a vba code that modifys cell E2 back to the formula I need every time the values are cut and paste into the history sheet?)

Anwser thanks to Jeeped

Use the Indirect function to keep your references static!

Works perfect for me!

=INDIRECT("'Sheet1'!A2")&INDIRECT("'Sheet1'!D2")& ect.

Upvotes: 2

Views: 1648

Answers (1)

user4039065
user4039065

Reputation:

If you want to keep the formula static no matter wherever you move, copy, displace or other, then the references have to be static text and the INDIRECT is used to return them to valid cell references.

In E2 as,

 =INDIRECT("'Sheet1'!A2")&INDIRECT("'Sheet1'!D2")&INDIRECT("'Sheet1'!B2")&INDIRECT("'Sheet1'!D2")&INDIRECT("'Sheet1'!C2")

Adjust the worksheet name if necessary. Those cell addresses will not change no matter what you do with them (beyond a text edit).

Upvotes: 2

Related Questions