Sometimes in Excel, we cannot use certain cells as direct cell references. Therefore, we are compelled to use variable row number as cell reference in Excel. The whole concept is that we use a random row number to assign as a cell reference in entries, formulas, or wherever we want.

As shown in the below screenshot, we want the sum of a couple of numbers. We can simply get the sum by summing the range (i.e., **B5:B11**). However, if we are unable to insert** B11** as cell reference then we use a random row number (i.e.**C5**). The **INDIRECT, OFFSET **or** INDEX** function converts **C5** cell value **11** as **B11** cell reference. So, the overall conversion happens **B(C5)=B11**.

In this article, we demonstrate multiple ways to use row number as cell reference in Excel.

**Table of Contents**hide

**Download Excel Workbook**

**4 Easy Ways to Use Variable Row Number as Cell Reference in Excel **

To demonstrate the use of variable rows as a cell reference, we have a dataset. Our dataset contains *Serial Number* as row number and other columns as shown in the following picture. We want the sum of *Total Price* using variable row number as a cell reference.

**Method 1: INDIRECT Function to Enable Variable Row Number as Cell Reference**

The **INDIRECT** function returns a cell reference taking the text as arguments. The syntax of the **INDIRECT** function is

`=INDIRECT (ref_text, [a1])`

**ref_text**; reference in a text string

**[a1]**; boolean indication of cell **A1**. **TRUE** (by default) = cell **A1** style. **[optional]**

**Step 1: **Paste the following formula in the respective cell (i.e.,** F16**).

`=SUM(F5:INDIRECT("F"&B15+4))`

The **SUM** formula simply sums the range (i.e., **F5:F15**). But first, the **INDIRECT** function takes the **B15** cell value (i.e.,**11**) then adds **4** to make it **15**. At last, **INDIRECT** passes it as **F15** to the formula. As a result, **F(B15)** becomes **F(11+4)**=**F15**

**Step 2:** Press **ENTER**. After that you see the total price amount in cell **F16**.

For simplification, we use row number as cell reference in simple formulas. You can use it in lengthy and complicated formulas, and it works fine. Using row numbers in cell references is an efficient way to avoid complications.

**Read More:** **Cell Reference in Excel VBA (8 Examples)**

**Method 2: Insert Variable Row Number as Cell Reference Using OFFSET**

Similar to the **INDIRECT** function, the Excel **OFFSET** function also returns cell reference. Despite their similarities in the outcome, the **OFFSET** function takes **5** argument inputs. The syntax of the **OFFSET **function is

`=OFFSET (reference, rows, cols, [height], [width])`

**reference**; starting cell from where the row and column number will be counted

**rows**; number of rows below the reference.

**cols**; number of columns right to the reference.

**height**; number of the rows in the returned reference. **[optional]**

**width**; number of columns in the returned reference. **[optional]**

**Step 1: **Type the below formula in the cell** F16**.

`= SUM(F5:OFFSET(F5, B15-1,0,1,1))`

In the above formula, the **OFFSET** function takes **F5 **as a cell **reference**, **B15-1** (i.e., **11-1=10**) as variable **rows**,** 0** as **cols**, **1** as** height** and **width**. By changing the **B15 **or **B15-1** you can insert any number as the cell reference.

**Step 2:** Hit **ENTER **to display the total sum.

**Read More:** **How to Reference Cell by Row and Column Number in Excel (4 Methods)**

**Similar Readings**

**How to Use FormulaR1C1 Absolute Reference in Excel VBA****[Fixed!] Relative Cell Reference Not Working in Excel****Excel VBA: Get Cell Value from Another Workbook without Opening****Relative and Absolute Cell Address in the Spreadsheet****Example of Relative Cell Reference in Excel (3 Criteria)**

**Method 3: INDEX Function to Use Variable Row Number**

To insert a row number as a cell reference, we can return a value to assign it in formulas. The **INDEX** function results in values of the assigned location. The syntax of the **INDEX** function is

`=INDEX (array, row_num, [col_num], [area_num])`

**array**; range or array.

**row_num**; row number in the range or array.

**col_num**; column number in the range or array. **[optional]**

**area_num**; range used in the reference. **[optional]**

**Step 1: **Use the latter formula in any blank cell (i.e., **F16**)

`=SUM(F5:INDEX(F:F,B15+4))`

The **INDEX** function considers the **F** (i.e., **F:F**) column as an array, **B15+4= 15 as the row_num. Other arguments are o**ptional so it’s not necessary to use them. The **INDEX(F:F,B15+4) **portion in the formula returns **$35.4** (i.e.,** F15** cell value). Changing **B15** or **B15+4** results in variable row numbers in the formula.

**Step 2:** Use the** ENTER** key to appear the sum of *Total Price* in cell **F16**.

**Read More:** **Excel VBA: R1C1 Formula with Variable (3 Examples)**

**Method 4: VBA Macro to Take Variable Row Number as Cell Reference**

What if we want to enter a row number every time, we select rows from a range or array? Excel **VBA** macros are efficient in doing this. Suppose we want to highlight specific rows (i.e., **C5:D15**) as shown in the following image in bold ink, **VBA** macros do it with a couple of lines.

**Step 1:** In order to open **Microsoft Visual Basic** window, press **ALT+F11**. In **Microsoft Visual Basic** window, Select **Insert** (from the **Toolbar**) > Click on **Module**.

**Step 2:** Paste the following macro in the **Module**.

```
Sub Row_variable()
Dim R_num As Integer
R_num = InputBox("Provide Preferred Row Number")
Sheets("VBA").Range(Cells(R_num, 3), Cells((R_num + 10), 4)).Select
Selection.Font.Bold = True
End Sub
```

** **

The macro code takes a row number using a **VBA Input Box** then highlights the first **10** rows. The highlight is done using **VBA Selection.Font.Bold** property.**Sheets.Range** statement assigns a specific sheet and range. Also, it defines the range using the **VBA CELL** property.

**Step 3:** Use the **F5** key to run the macro. The macro first displays an input box and asks to enter a row number. After entering the row number (i.e., **5**), click on **OK**.

** **

**➤ **Clicking **OK** takes you to the **Module** window. Return to the worksheet, You see the assigned range (i.e., **C5:D15**) gets highlighted in **Bold**.

**Read More:** **Excel VBA Examples with Cell Reference by Row and Column Number**

**Conclusion**

In this article, we use multiple functions as well as **VBA** macro to use variable row number as cell reference in Excel. Functions such as** INDIRECT**, **OFFSET**, and **INDEX** use row numbers in their arguments to transform the outcomes as a cell reference. Hope these above-described way outs clarify the concept and help you to use them in daily uses. Comment if you have further inquiries or have anything to add.

**Related Articles**

**How to Reference Text in Another Cell in Excel (14 Ways)****How to Find and Replace Cell Reference in Excel Formula****Excel VBA: Cell Reference in Another Sheet (4 Methods)****[Fixed] F4 Not Working in Absolute Cell Reference in Excel (3 Solutions)****Excel VBA: Insert Formula with Relative Reference (All Possible Ways)****How to Use Cell Value as Worksheet Name in Formula Reference in Excel**