Conditional "Formula To Values" Macro In XL2010

[papplaszlodaniel]

I use Excel 2010 under Windows 7.

I have the following issue and can't find any solution not in Help nor on Google:

I'm using Excel to create ID/Ref. numbers for documents - some data are given in different columns and the ID is generated with the "CONCATENATE" formula. The system contains sequence numbers in the ID as well, so when I have to insert an earlier document which has arrived late, it changes all the ID numbers below this given one.
To make sure the already assigned ID numbers can not be changed after they were created I would like to replace the ID cell formula to value.

Example:
Column A is the column of the ID numbers. It concatenates columns B, C, D and E.
What I would like to have:
If E1 is not empty anymore, then A1 cell's formula gets replaced with it's value..
..if E2 is not empty, then A2 cell's formula gets replaced with it's value..
..if E3 is not empty, then A3 cell's formula gets replaced with it's value..
..if E4 is not empty, then A4 cell's formula gets replaced with it's value..
etc.

Any idea how could I solve it? -- Thank you in advance!

 

[Snakehips]

papplasziodsaniel,

Welcome to MrExcel.

You will need to use vba.

Assuming that there is manual input of data into cell E that is the last input required to generate the Id then maybe....

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub
If Not Target.Column = 5 Then Exit Sub


'**********************
'Optional???? Check that data exists in B, C & D ?????
'Delete all lines between *************** if this aspect not required
For i = -1 To -3 Step -1
If Target.Offset(0, i) = "" Then
MsgBox "Please ensure data in B, C and D before entering in E"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
Next i
'If ok then convert A formula to value
'***********************




If Not Target = "" Then Target.Offset(0, -4) = Target.Offset(0, -4).Value
End Sub

Right click on your sheet's tab >> View Code >> paste code into the code pane on the rhs of the vb editor.


Hope that helps.

 

[papplaszlodaniel]

Dear Tony, thank you and you are awesome!
It work properly as it was described above.


Although if I understand it correctly, the script repeats the concatenation of the columns and then replaces it with it's value. If it is so, then I'm afraid I can't adjust it to the actual excel, where I have a bit more complicated formula, eg. in cell C218 it look like this "=(IF(ISERROR(O218);CONCATENATE(E218;"/";P218;"/";I218;"/";G218);VLOOKUP(B218;sheet2!A$3:B217;2;FALSE)))".

And what I'd like is in the previously mentioned case (C218):
..if L218 is not empty, then C218 cell's formula gets replaced with it's value..
..if L219 is not empty, then C219 cell's formula gets replaced with it's value..
and so on.

There is manual input input in column E, G, I, L but P is also a formula.

Can I use the modification of the previous script or I would need a new one? -- Thanks in advance if you have a tip for this as well!!

 

[Snakehips]

Try replacing the previous code with this....

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub
If Not Target.Column = 12 Then Exit Sub '12 = Column L


'**********************
'Optional???? Check that data exists in E, G & I ?????
'Delete all lines between *************** if this aspect not required
For i = -3 To -7 Step -2
If Target.Offset(0, i) = "" Then
MsgBox "Please ensure data in E, G and I before entering in L"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
Next i
'If ok then convert C formula to value
'***********************


If Not Target = "" Then Target.Offset(0, -9) = Target.Offset(0, -9).Value
End Sub

 

[papplaszlodaniel]

Thanks a lot! Just works perfectly!