If a character is entered, copy formula into cells?

[Stoo]

How can one have a function/formula that when one character is entered into a cell, one formula is copied from two cells (from the same place each time) into the corresponding specified row. And if another character is entered ino the same cell, another formula is copied from two cells (frm the same place each time) into the corresponding specified row?

Eg.

If 'Y' is entered - Copy formula from Cell C1 & D1 to C10 & D10
If 'N' is entered - Copy formula from Cell C2 & D2 to C10 & D10

When the cell formula is copied it needs to automatically update so if it's copied to C10 it uses that rows data, and if is copied to C11 it uses that rows data.

Thanks.

[DonkeyOte]

Stoo, if you don't want to embed the formulae, ie:

C10: =IF(x="Y",formula used in C1,formula used in C2)
D10: =IF(x="Y",formula used in D1,formula used in D2)

then you will need to use VBA but you will need to provide more info in terms of where the Y/N is being entered & how (ie manually typed or use of Data Validation, formulae etc...)

[Stoo]

DonkeyOte,

Thanks for your response.

The 'Y' and the 'N' will be entered manually into a cell.

Please see attached example.

Cheers.

[zbor]

Write this in F6 and G6

=IF(C6="Y";$F$1;$F$2) =IF(C6="Y";$G$1;$G$2)

(replac ; with , if needed)

This will return formula F1 if Y is in C6 or anythign else (can be N but can be any simbol) if not

[DonkeyOte]

Assuming you want to use VBA - see below (alter Intersect Range to encompass the range you intend to use this with) -- to insert the below right click on "Sheet1" tab and select View Code and paste above into resulting window
(note you can have only one Worksheet_Change event per sheet object)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Or Intersect(Target, Range("C6:C16")) Is Nothing Then Exit Sub
With Target.Offset(, 3).Resize(, 2)
    .FormulaR1C1 = .Offset((1 + Abs(UCase(Target.Value) = "N")) - .Row).FormulaR1C1
End With
End Sub

[Stoo]

My previous sheet was explained slightly wrong. Apologies.

Please find attached corrected sheet.

I'd rather stick with functions in cells if possible, makes it easier for me to understand and tweak things if required.

Thanks for your responses.

[DonkeyOte]

A formula can not physically copy anything.

As already outlined, if you want to use formulae you should use construct of:

=IF(C6="Y",your formula for Y,your formula for N)

there is no shortcut other than perhaps using a Volatile Named Range which would be ill advised IMO.