Excel - Dynamic cell reference

Microsoft Office excel 2003
November 26, 2010 at 12:12:00
Specs: Windows XP
I was wondering if it is possible to refer to a Excel cell by defining its column in another cell. For example, let's say that A1 contains the number 5. If I want to access the cell C5, is it possible for the column number (5) to be provided by the content of the cell A1?
Thank you!

See More: Excel - Dynamic cell reference

Report •

#1
November 26, 2010 at 19:52:18
re: "If I want to access the cell C5, is it possible for the column number (5) to be provided by the content of the cell A1?

"5" is not the column number for C5, it is the row number.

Assuming that that is what you meant, read up on the INDIRECT function in the Excel Help files.

With 5 in A1, this will return the value from C5:

=INDIRECT("C" & A1)

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 27, 2010 at 04:12:33
Yes, right this is the column I want.
Now is there a way to "construct" the cell coordinates by using another cell reference, e.g. have as a result "C5" by having the number 5 in a cell, but also being able to use that coordinate in a function?

Report •

#3
November 27, 2010 at 07:09:05
Since I can't see your spreadsheet from where I'm sitting, it's not clear to me what you mean by "have as a result "C5" by having the number 5 in a cell."

In any case, I'll explain it this way:

INDIRECT can be combined with other functions to create a cell reference that Excel can use. Just about any place where you could use C5 as a cell reference in a function, you could use the INDIRECT function to "build" a reference to C5 - or any other cell.

For example...

A1 = C
A2 = 5
A3 = A1 & A2 (displays: C5)
C5 = 25

=C5 * 2 would return 50, as would all of the following formulas:

=INDIRECT(A1 & A2) * 2

=INDIRECT(A3) * 2

=INDIRECT("C" & A2) * 2

=INDIRECT(A1 & 5) * 2

=SUM(C5, RIGHT(INDIRECT(A3), 1), INDIRECT(A1 & A2) - INDIRECT("A" & 2))

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •
Related Solutions


Ask Question