CODEWITHSUNDEEP
excel
Thijs
Thijs

Reputation: 11

How do i refer to a cell in a specific column but the row is determined by input in other cell

I want a cell to display a value from a list elsewhere in my sheet. The user inputs a value lets say 50 in cell A1. in that case i want cell B1 to display the value given in cell X50.

when the user enters a 61 in cell A2 i want B2 to display the value of X61

Upvotes: 0

Views: 36

Answers (2)

Scott Craner
Scott Craner

Reputation: 152595

When knowing the column and sheet using the nonvolatile INDEX() function is an advantage.

Indirect is a volatile function, meaning that it recomputes every time excel recomputes whether the data for which it refers changed or not. If the workbook is filled with indirect function it will slow down the computation time.

The following INDEX Formula will only recompute when the data to which it refers changes.

In B1 put:

=INDEX(X:X,A1)

Then copy down the desired number of rows. The A1 will change To A2 and so forth.

Upvotes: 1

usamazf
usamazf

Reputation: 3215

This is possible with use of INDIRECT()... How can you use it? Simply do this

Type the following in you cell B1:

=INDIRECT("X"&A1)

And in you B2 type this:

=INDIRECT("X"&A2)

Upvotes: 1

Related Questions