---

MS Excel 2003: Update all formulas to reference data in a particular row

This Excel tutorial explains how to write a macro to update all formulas to reference data in a particular row in Excel 2003 and older versions (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2003/XP/2000/97, I want to create a macro button that when clicked will update all formulas to reference data in a particular row. How can I do this?

Answer: This can be done with a macro.

Let's 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