Insert a value in a cell without using formula in the same cell

[H_gupta]

Hi,

I want to default value of a cell into multiple cells based on a condition

For example : If cell C10 = "Yes", then put value of cell F10 in multiple cells like C42, I42, G42.

If cell C10 = "No", then the user can enter their own values in C42, I42, G42 etc.

I cannot put conditional formula in these cells (C42, I42, G42 ) because as soon as the user puts their own value, the formula gets deleted.

I want to be able to use the formula as well as make it editable. Is that possible ? Any workaround ?

Have tried using Macro VBA, but it gets stuck. So looking for a formula based workaround.

 

[Joe4]

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor Window:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("C10").Address Then
        If Target = "Yes" Then
            Application.EnableEvents = False
            Range("C42,G42,I42").Value = Range("F10").Value
            Application.EnableEvents = True
        End If
    End If
    
End Sub

This code will run automatically when cell C10 is manually set to "Yes".

 

[Joe4]

It looks like you already posted that question here: https://www.mrexcel.com/forum/excel...paste-based-another-column-2.html#post5249560

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

I am locking this thread down, since you already have the other one going on it.