Allow to be edited while keeping the original formula

[daniel_dani95]

Hello,

I want to do some complex "IF/IFS" which will display mostly textual data (information cells) based on the term you enter into specific cell (term cell), but I would like to allow those (information cells) to be manually edited if there is no information about given term.

For example:
Term cell: Paris
Information cell 1: Eiffel Tower
Information cell 2: Louvre Museum
Information cell 3: Orsay Museum

Information cells 1, 2 and 3 present data because they are in database (another excel sheet or adjacent table which will be hidden)

Now if I put London into the term cell, and there is no information about London in the database, I would like to be able to manually edit information cells 1,2, and 3 with attractions and keep the formula in them to display information if term from database is entered later/some other time.

I'd appreciate if somebody could help or at least tell me that it's impossible to do it in excel.

Thanks in advance

 

[Fluff]

Hi & welcome to MrExcel.
A cell can hold a formula or a value, but not both.
The only way to do what you want would be with a macro.

 

[daniel_dani95]

Hi & welcome to MrExcel.
A cell can hold a formula or a value, but not both.
The only way to do what you want would be with a macro.

Thanks for fast reply, I've seen a question here which deals in, let's say, very simple example of my problem. The given answer is to edit the code of the worksheet to do it. When i try to reshape the code to my needs it doesn't work. How can I use macro to my advantage with this problem

 

[Fluff]

What is the code you are using?

 

[daniel_dani95]

What is the code you are using?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Or $B$1  Then
    Application.EnableEvents = False
    Range("C1").Formula = "=A1*B1"
End If
Application.EnableEvents = True
End Sub

This is the code someone posted here on question how to make cells A and B multiply into cell C, but still be able to change the result in cell C

 

[Fluff]

What is the formula you want to put into the cells?

 

[daniel_dani95]

What is the formula you want to put into the cells?

From the picture in the thread my formula (from what i know) would be let's say for INFO 1 =ifs(A2="Paris",L3, A2="Rome",L4). Same principle but other rows/columns for other INFO fields

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      If Not Target.Offset(, 2).HasFormula Then
         Target.Offset(, 2).Formula2R1C1 = "=ifs(rc1=""Paris"",r3c12, rc1=""Rome"",r4c12)"
      End If
   End If
End Sub

 

[daniel_dani95]

It works perfectly now, but since i'm pretty new at this, could you tell me which part of your code picks cell C2 to display information? So, if i just copy paste this code, which variable do i need to change to get data in other columns

 

[Fluff]

The offset(,2) is the part that is looking at column C