# Setting links that are changable.

#### guin36

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Andrew Poulsom

##### MrExcel MVP
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

##### New Member
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

##### Board Regular
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

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

#### Andrew Poulsom

##### MrExcel MVP
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.

Replies
6
Views
843
Replies
3
Views
743
Replies
4
Views
183
Replies
2
Views
220
Replies
5
Views
819

1,181,896
Messages
5,932,676
Members
436,850
Latest member
Jasperlee93

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back