0

For example: Link to Cell A1 is "=A1"
A - column name
1 - row number

Can I calculate row number in formulas to make specific links?
Something like =A{some calculations} so =A{1+1} will be a link for Cell A2

3 Answers 3

0

Yes, but it is done by
=OFFSET(Start_Cell, Rows_Up_or_Down, Columns_Right_or_Left [, Number_of_Rows [, Number_of_Columns]])
So, suppose your information starts at C5 and you want 1 cell that is down 4 rows and right 2 columns:
=OFFSET(C5,4,2)

0

There are several ways to do this in Excel. LDC3's answer describes OFFSET. Another is:

    =INDIRECT(reference,style)

This creates a reference to a cell from a text entry. This provides a lot of freedom to calculate or create a cell reference.

    =INDIRECT("A"&(1+1))   is equivalent to =A2
    =INDIRECT("A"&ROW())   if this formula is in cell C5, 
                           this would be equivalent to =A5

If the style parameter is "TRUE" or omitted, it assumes normal cell reference formatting, as above.

INDIRECT also allows a different style of row and column specification. If the style parameter is "FALSE", it uses R#C# where you use the letter R to indicate that the next number is the row number and the letter C to indicate that the next number is the column number (everything is specified as numbers). If you omit a number after the R or C, it assumes you mean the current row or column.

    =INDIRECT("R1C1",FALSE)    is equivalent to =A1
    =INDIRECT("R"&(ROW()-4)&"C",FALSE) if this formula is in cell C5, 
                                       this would be equivalent to =C1 
                                       (current column, and row number equal to 
                                        the current row -4).

This format lets you calculate both row and column references as numbers. It also function like OFFSET, but relative to the current location rather than an anchor cell. If you put the number in square brackets, it means relative position (the number of rows or columns away from the current cell).

    =INDIRECT("R3C[-2]",FALSE)  if this formula is in cell C5, 
                                this would be equivalent to =A3
0
=INDEX(A1:A100,1,2)       [this will give you row 1, column B]

OR

=INDEX(A1:Z100,1+1,2+2)   [this will give you row 2, column D]

= INDEX (TARGET:AREA, ROW, COLUMN)

If you use tons of these like I do. Indexes won't lag your documents. Indirect and offset eat up a LOT more resources. Boot some thousand indirects or offsets and your docs will begin to drag with all the volatility being serviced.

Obviously you can pack it in different ways such as:

=INDEX(A1:Z100,ROW(A1)+1,COLUMN(B1)+2)  

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .