Relative Cell Reference w/in Hyperlink Formula

[tr33s]

Hello All,

I cannot find and information on this, maybe I am not searching it correctly, but I decided to make an account ask you smart folks.

I have a large excel file that I am working on and have many hyperlink formulas added in (my links often break using the excel hyperlink function that is built in, that is why I use the formula) referencing different tabs within the file.

An example of a back hyperlink I have: =HYPERLINK("#Sheet1!U"&MATCH($A1,Sheet1!$A:$A,0),"Back")
This links back to a cell (in column U, Sheet1) that corresponds to the same part # in column A of the sheet the formula is in.

My problem is since the specification "#Sheet1!U" is in quotations, it is an absolute refence, and the formula does not adjust if the cell moves (say if I remove a column in sheet1, the cell I want to reference will now be in column T, but the formula will still reference U). To my knowledge the quotations cannot be removed.

I have a lot of these hyperlinks and it would be a pain to change them everytime I remove a column, etc. Any help would be appreciated, maybe another formula will be better or a VBA? Thanks for your time.

 

[sijpie]

One way would be to give each linked to location a name, then you could use something like:
=HYPERLINK("#Sups","Back")
to link to location named Sups
or if A1 contains the name:
=HYPERLINK("#"&A1,"Back")

but if you have tables full of partnumbers that could be a bit of a pain. Although you can use a macro to create the named ranges.

Another way, closer to what you have now, is to not only match the value of Cell A1 to find the row, but to also match a value which is equal to the heading of the column U.
So let's assume that column U has a heading (in Row 1) of Partnums. Then we can find the column number with the formula
=MATCH("Partnums",Sheet1!1:1)
So now we have row number and column number, and we want to create an address from that.
The Index function can supply a reference, which we can use in the Cell function to give us the address. So:
=INDEX(A1:Z200,G2,G3)
will give the value found in U9 (if E2 and E3 contain the two MATCH() functions), but when we put this same formula in another function, it will act as if it says U9! For instance
=CELL("Address",INDEX(A1:Z200,E2,E3))
will show: $U$9
Just what we want in our hyperlink. And because it searches for the correct header, insert a column annd the reference changes to V9. The only thing we need to make sure of is that the range given in Index is big enough, so make sure the last column (in you case at least column U and the last row of the table is included. I set it to A1:Z200 in this demo.

Excel 2010
	A	B	C	D	E
1	ENGINEERING				demo of individual
2					parts of formula
3					9
4					21
5			Back		#Sheet1!$U$9
6
7			?
8			Hyperlink
Result

Cell Formulas
Range	Formula
C5	=HYPERLINK("#Sheet1!"&CELL("address",INDEX(A1:X200,MATCH($A1,Sheet1!$A:$A,0),MATCH("Partnums",Sheet1!1:1))),"Back")
E3	=MATCH($A1,Sheet1!$A:$A,0)
E4	=MATCH("Partnums",Sheet1!1:1)
E5	="#Sheet1!"&CELL("address",INDEX(A1:X200,E3,E4))

 

[tr33s]

Thank you so much for answering, and in such a helpful and concise way! This is a great idea, I've actually been working on this type of formula but having trouble execcuting, so I'm glad I checked back here.