Setting links that are changable.

guin36

New Member
Joined
Jul 22, 2002
Messages
33
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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jul 22, 2002
Messages
33
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
Joined
Oct 23, 2002
Messages
217
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,256
Latest member
downeybm

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top