How to Use Variable Row Number as Cell Reference in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview-Variable Row Number as Cell Reference in Excel

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


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

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.

Dataset-Variable Row Number as Cell Reference in Excel


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 function 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

indirect function-Variable Row Number as Cell Reference in Excel

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

Result

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: How to Display Text from Another Cell in Excel


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.

offset function-Variable Row Number as Cell Reference in Excel

Step 2: Hit ENTER to display the total sum.

Outcome

Read More: How to Reference Text in Another Cell in Excel


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 optional 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.

index function-Variable Row Number as Cell Reference in Excel

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

Result

Read More: How to Reference Cell by Row and Column Number in Excel


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.

vba -Variable Row Number as Cell Reference in Excel

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.

Module insertion

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

Macro

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.

row number insertion

➤ 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.

Result

Read More: How to Find and Replace Cell Reference in Excel Formula


Download Excel Workbook


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


<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo