Allowing a cell with a formula to also allow for user input.

Hi,

as far as I know this will be hard (impossible?) without VBA, can you provide a sample workbook with 2 scenario examples?

The only way that I could see that working is to have a VBA Worksheet Change event monitoring the drop down list of starting points. The source for the drop down list could be the headings of a matrix. The second dimension of the matrix would be a list of cell references and the content of the matrix would be columns of values for each of the cell references.

So, when an entry is selected from the drop down box, the change event would loop through the relevant column of cells and populate the worksheet cells.

Then the user could play to their heart's content and reset with the same or a different starting point.

Regards, TMS

Hi,

check attached example, each time the value in C$ is changed the formulas are reapplied, at the same time the user can write values into C9-C11: Sample_Maintanance_Cost.xlsm

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C4")) Is Nothing Then
        Range("C9").FormulaR1C1 = "=IF(R[-5]C=""*Other"","""",INDEX(Data!R[-7]C[-1]:R[-2]C[2],MATCH(DataEntry!R[-5]C,Data!R[-7]C[-1]:R[-2]C[-1],0),MATCH(DataEntry!RC[-1],Data!R[-7]C[-1]:R[-7]C[2],0)))"
        Range("C10").FormulaR1C1 = "=IF(R[-6]C=""*Other"","""",INDEX(Data!R[-8]C[-1]:R[-3]C[2],MATCH(DataEntry!R[-6]C,Data!R[-8]C[-1]:R[-3]C[-1],0),MATCH(DataEntry!RC[-1],Data!R[-8]C[-1]:R[-8]C[2],0)))"
        Range("C11").FormulaR1C1 = "=IF(R[-7]C=""*Other"","""",INDEX(Data!R[-9]C[-1]:R[-4]C[2],MATCH(DataEntry!R[-7]C,Data!R[-9]C[-1]:R[-4]C[-1],0),MATCH(DataEntry!RC[-1],Data!R[-9]C[-1]:R[-9]C[2],0)))"
    End If
End Sub

This is how it would work in principle. I haven't done much testing but it seemed OK with your sample.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim lVehicle As Long
Dim rStart As Range
Dim rEnd As Range
Dim cell As Range

If Intersect(Target, Range("C4")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

With Sheets("Data")
    lVehicle = awf.Match( _
                    Target.Value, _
                    .Range("B1").EntireColumn, _
                    0)
    Set rStart = .Cells(1, 1).End(xlToRight)
    Set rEnd = .Cells(1, .Columns.Count).End(xlToLeft)

    Application.EnableEvents = False
    For Each cell In .Range(rStart, rEnd)
        Sheets("DataEntry").Range(cell.Value) = _
            .Cells(lVehicle, cell.Column)
    Next 'cell
    Application.EnableEvents = True
End With

End Sub

You're welcome. Thanks for the rep.

You even managed to hide the individual cell-functions from the user. (how?)

They're not hidden, they're no longer present.

Making a change on the sheet causes the worksheet change event to fire.

When you change the vehicle in the drop down list, the code works out which row that vehicle is in. It then loops across the top row picking up cell references ... it uses those to put the data for the vehicle in the appropriate cell.

This, effectively, replaces your IF/INDEX/MATCH/MATCH in code.


Hope that helps,

Regards, TMS