Setting links that are changable.

[guin36]

I would like to put the row number in column A and have the links in the next few columns use that number to link to another page.

Is it possible to set up a link that would be dynamic like that?

Just to be as clear as possible I would like to put 50 in column A and have the link in column B lookup using the number in A. But if I change that number then the formulas would just look to the new number in column A.

Guin... trying to keep from manually linking to all these rows.

 

[Andrew Poulsom]

You can use:

=INDIRECT("Sheet2!A"&A1)

which will return the value in the row specified in cell A1 from Sheet2 column A.

The trouble with INDIRECT is the "Sheet2!A" is text and will not change when you copy the formula eg across columns. Another approach is:

=INDEX(Sheet2!A1:A50,A1,1)

By mixing relative and absolute references you will be able to copy the formula elsewhere.

 

[guin36]

What formula allows you to only return the row number to a cell?

Lets say that you setup this formula to look at what is currently A1000 and the result is 1000. Now if you delete 5 rows and that cell is A995 the result should now be 995.

I have found that using the indirect was getting messed up by deleting a row.

 

[apurk45]

guin36

I have used this once and worked for me

in cell A1 put: =IF(B1="","",1)
in cell A2 put: =IF(B2="","",MAX(A$1:A2)+1)
in cell A3 put: =IF(B3="","",MAX(A$1:A3)+1)
....
and so on.
This formula works even if you delete some of the rows.
See if you can modify the cell address ti fit your needs

 

[guin36]

So is there no way to return just the row value from a link?

 

[Andrew Poulsom]

The ROW function returns the row number of a reference. So:

=ROW(A1000)

would return 1000. The reference (and therefore the result) would adjust if rows are inserted or deleted. But if you deleted row 1000 you would get the #REF! error.

If the reference is omitted like this:

=ROW()

it is assumed to be the reference of the cell in which the ROW function appears.