Selecting cell references based on value of other cell and can be filled right

The Problem with Static References

When building spreadsheets, users often encounter a limitation where a formula points to a fixed range, such as =A1+B1. If you need this logic to apply across multiple rows but shift the column reference for each row (e.g., Row 1 uses Columns A and B, Row 2 uses Columns C and D), manually editing every cell becomes tedious and error-prone. Standard absolute references ($A$1) do not change when filled, while standard relative references (A1) change both row and column indices simultaneously. To solve the specific problem of changing only the column based on a trigger value or position, you need a dynamic referencing strategy.

Using INDIRECT for Dynamic Resolution

The core solution relies on the INDIRECT function, which takes a text string representing a cell address and converts it into an actual reference. For example, if cell D1 contains the text "A1", the formula =INDIRECT(D1) will return the value stored in cell A1. This decouples the formula from the physical location of the data, allowing you to construct addresses programmatically.

To make this work when filling right, you must generate the column part of the address dynamically. You can achieve this by concatenating the column letter with the row number. Suppose you want to pull values from columns that are determined by a header row or a sequence number. If your headers are in row 1 (A1, B1, C1...) and you want to reference the cell in the current column's header row for a specific data row, you can use:

=INDIRECT(ADDRESS(ROW(), COLUMN()))

However, a more common scenario involves referencing a column based on a numeric index stored in another cell. If cell E1 holds the number 3, and you want to reference the third column of the current row, you can use:

=INDIRECT(CHAR(64+E1) & ROW())

This formula constructs the string "C" followed by the current row number. When you fill this formula to the right, ROW() stays constant for that row, but if you adjust the index in E1 or use a relative offset, the referenced column shifts. This is particularly useful when creating dashboards where the user selects a metric via a dropdown, and the formula fetches the corresponding data column.

Combining with Column Offsets for Horizontal Filling

If your goal is to have a formula that automatically moves one column to the right each time you fill it across, you can combine INDIRECT with the COLUMN() function. The COLUMN() function returns the integer representing the current column's position (e.g., 1 for A, 2 for B). By adding an offset to this value, you can target adjacent columns.

For instance, if you start in cell F1 and want to reference the cell in column A of the same row, then column B in the next filled cell, you might use a base offset. However, a simpler method for consistent horizontal progression is to use INDEX and COLUMN together, which is often more robust than INDIRECT because it does not recalculate unnecessarily. But sticking to the INDIRECT requirement for text-based resolution, consider this pattern:

=INDIRECT(CHAR(64+COLUMN()-5) & ROW())

In this example, assuming you start in column F (column 6), COLUMN()-5 equals 1. CHAR(65) is "A". So it references A1. When you fill right to G1, COLUMN() becomes 7, 7-5=2, CHAR(66) is "B", referencing B1. This creates a seamless horizontal chain of references based on the formula's position. You can read more about automating these references in our guide on Automating Cell Referencing in Excel: A Step-by-Step Guide.

Implementing in Google Sheets

The syntax described above works identically in Google Sheets. Both platforms support INDIRECT, ADDRESS, CHAR, and COLUMN functions. When implementing this in Google Sheets, ensure that your data types are consistent. If the result of INDIRECT is expected to be a number but is treated as text, you may need to wrap the entire expression in VALUE() or ensure the source cells contain numeric data. Additionally, Google Sheets handles circular references differently, so ensure your dynamic reference does not point back to the cell containing the formula itself.

Best Practices and Pitfalls

While INDIRECT is powerful, it is volatile, meaning it recalculates every time any change occurs in the workbook. For large datasets, this can impact performance. In such cases, consider using INDEX with MATCH or COLUMN as a non-volatile alternative. Also, always validate the text string passed to INDIRECT to avoid #REF! errors. If the constructed address does not exist, the formula breaks. Using IFERROR around your INDIRECT calls can provide cleaner output during testing.

For scenarios involving conditional formatting rather than data retrieval, you might explore how to handle references in other tools. See our article on Cell references for Conditional Formatting in LibreOffice Calc using the "Formula is" condition for comparative insights on handling dynamic ranges in different environments.

Frequently Asked Questions

Why does my INDIRECT formula return #REF!

This error occurs when the text string constructed by your formula does not correspond to a valid cell address. Ensure that the column letters generated by CHAR() are within the A-Z range and that the row numbers exist in your sheet. Check for hidden spaces in concatenated strings.

Can I use INDIRECT with named ranges?

Yes, INDIRECT can resolve named ranges if you pass the name as a text string. For example, =INDIRECT("MyDataRange") will return the value of the first cell in that named range. This is useful when the name of the range changes dynamically based on user input.

Is there a faster alternative to INDIRECT for performance?

Yes, the INDEX function is non-volatile and generally faster. You can replicate many INDIRECT behaviors using INDEX(Sheet1!A:D, ROW(), COLUMN()). This approach avoids the overhead of parsing text strings into addresses on every calculation.

How do I handle merged cells with dynamic references?

Dynamic references like INDIRECT typically fail or behave unpredictably with merged cells because they rely on precise grid coordinates. It is best practice to unmerge cells and use center-across-selection alignment instead, ensuring that INDIRECT can accurately locate individual cells.

Does this technique work in Microsoft Excel Online?

Yes, all the functions mentioned (INDIRECT, COLUMN, CHAR, ROW) are fully supported in Excel Online. The behavior regarding volatility and calculation order remains consistent with the desktop version.