Question:  In Excel, I want to create a macro button that when clicked will replace all formulas in my spreadsheet. The tricky part is that I want the formulas to reference the data from a particular row in the spreadsheet - one that I specify. How can I do this?

Answer:  This can be done with a macro.

Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, we have two sheets called Form and Data. On the sheet called Data, we've placed information in rows 2 to 6. This will be the information referenced by all formulas in the spreadsheet.

The sheet called Form is where the formulas reside. Each cell with a red border contains a formula that references data on the Data sheet.

We've placed a button on the Form sheet that when clicked will prompt for the row number that all formulas will reference.

In our example, we've chosen to have all formulas reference the data in row 3 on the Data sheet.

The macro will then replace all formulas on the Form sheet. When it has completed, you will see the following message box appear:

Now when you return to the spreadsheet, you can see that all of the formulas now reference row 3 on the Data sheet.

To view the macro, press Alt-F11 and double-click on the module called Module1 in the left window.

Macro Code:

This macro code looks like this:

Sub UpdateFormulas()

    Dim LRowNumber As Long

    LRowNumber = InputBox("Please enter the row number to update the formulas.")

    Sheets("Form").Select

    'All following code will copy a formula into the destination if the source
    'has a value. If the source does not have a value, it will copy a blank to
    'the destination.

    'Item #1
    Range("F7").Select
    If IsEmpty(Range("Data!A" & LRowNumber).Value) Then
        ActiveCell.Value = ""
    Else
        ActiveCell.Formula = "=Data!A" & LRowNumber
    End If

    'Item #2
    Range("F9").Select
    If IsEmpty(Range("Data!B" & LRowNumber).Value) Then
        ActiveCell.Value = ""
    Else
        ActiveCell.Formula = "=Data!B" & LRowNumber
    End If

    'Item #3
    Range("J10").Select
    If IsEmpty(Range("Data!C" & LRowNumber).Value) Then
        ActiveCell.Value = ""
    Else
        ActiveCell.Formula = "=Data!C" & LRowNumber
    End If

    'Item #4
    Range("H11").Select
    If IsEmpty(Range("Data!D" & LRowNumber).Value) Then
        ActiveCell.Value = ""
    Else
        ActiveCell.Formula = "=Data!D" & LRowNumber
    End If

    'Item #5
    Range("D11").Select
    If IsEmpty(Range("Data!E" & LRowNumber).Value) Then
        ActiveCell.Value = ""
    Else
        ActiveCell.Formula = "=Data!E" & LRowNumber
    End If

    'Reposition back on item #1
    Range("F7").Select

    MsgBox ("The formulas were successfully updated to row " & LRowNumber & ".")

End Sub