VB script allowing entering data in a cell and preserving the formula written on the cell

[RadoslavBudinov]

Hi all,

Hope you are all doing fine and are having nice holidays!

I am trying to solve the following for one of my excell documents.
1. Having a data validation drop-down list with all vendors. Once I chose the needed vendor it autofills data in other cells on the same sheet with information about the vendor using =vlookup function on each cell.
2. I am having a cell with prices of the vendors that should be filled-in. In half of the vendors prices are presetted so the =vlookup function is working since prices are on enterred on the same array on Sheet2.
3. The other half of the vendors have their prices being changed monthly so I need to fill them in manually. The idea is when on the cell on which I have the formula "=IF(AND(B28<>"Vendor Name";VLOOKUP(Sheet1!B28;Sheet2!A1:E18;5;FALSE)<>"");VLOOKUP(Sheet1!B28;Sheet2!A1:E18;5;FALSE);"") " is actually Blank/Empty because the vendor does not have presetted data for Price to be able to put the current monthy price there without losing the above mentioned formula.
4. I have read somewhere on all excell forums it is possible by VBS. Trying and not being that good with vbs scripting I got this example for another almost similar issue:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "Yes" Then Target.Offset(0, 1).Value = "100%"
End If
End Sub

So I have to rewrite it to work for my needs and I got to that one which is not working:
Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Range("G14").Value) = True Then
Range("G14").Offset(0, 6).Value = "100%"
End If
End Sub

G14 is the cell where I have the formula above and is getting Blank if no presetted data for price is existing for the vendor.

Do you have any ideas if what I request is possible to be done and if you happen to know a better way should be perfect?

 

[jasonb75]

Why not have 2 tables, one for preset prices (Sheet2) and another for those that change monthly (Sheet3)?

=IF(B28="Vendor Name";"";IFERROR(IFERROR(VLOOKUP(Sheet1!B28;Sheet2!$A$1:$E$18;5;FALSE);VLOOKUP(Sheet1!B28;Sheet3!$A$1:$E$18;5;FALSE);""))

You can not have a formula and a value in any cell at the same time (even with vba) you can only use one to overwite the other.