The Index function can supply a reference, which we can use in the Cell function to give us the address. So now we have row number and column number, and we want to create an address from that. Then we can find the column number with the formula So let's assume that column U has a heading (in Row 1) of Partnums. Although you can use a macro to create the named ranges.Īnother 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.

One way would be to give each linked to location a name, then you could use something like:īut if you have tables full of partnumbers that could be a bit of a pain. Any help would be appreciated, maybe another formula will be better or a VBA? Thanks for your time. I have a lot of these hyperlinks and it would be a pain to change them everytime I remove a column, etc. To my knowledge the quotations cannot be removed. 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).

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. 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.Īn example of a back hyperlink I have: =HYPERLINK('#Sheet1!U'&MATCH($A1,Sheet1!$A:$A,0),'Back')

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.

Relative cell reference excel 2016