How to refer to a cell like (row, column) instead of G7

[Hermanator]

Hi people,

I just have a really stupid question here and I'm amazed that I haven't found the solution yet:

In Excel, I want to create a reference to a certain cell, with a variable position. For instance: The number in cell C5 determines the column number of the cell to copy to cell D6.

The only way that I can do this is to put in cell C6:
=INDIRECT(ADDRESS(7,C5)). This first creates a reference as a text to the cell C3 and then converts it to a real reference. Isn't there a way to do this directly? There must be a formula like: =CELL(7,C5) !

 

[AnotherHiggins]

I don't see why you are using the ADDRESS function. Try this:

[COLOR=blue white]=INDIRECT("G"&C5)[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[GlennUK]

How about using OFFSET to point to a cell.

=OFFSET(A6,0,C5-1,1,1)

The parameters are:
= OFFSET ( Origin_cell, rows_to_offset_by, cols_to_offset_by, num_rows_to_return, num_cols_to_return )



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Zathras]

Take a look at the INDEX function.

 

[DPlank]

I reckon you should name the cell. Whenever it changes position, the name will follow it and your formulae (or vba) which relies on the contents of this cell will follow it.

Click on the Cell name in the far left of the formula bar (eg G1). It will highlight and you can name the cell something useful. Press Enter to save the name of the cell and Robert is your mother's brother.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!