• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dragging Hyperlink

S

sundeep

New Member
I want to hyperlink from a cell on sheet 1 (B2) that links to the info on sheet 2 (C2). So far so good, I've done this.


What I can't work out is how to copy the hyperlink so that the row cell reference changes in context so that sheet1 B4 links to sheet2 C4, B5 to C5 and so on without editing every cell.


If possible without using VBA as I am new to excel


Please help me out.... Thanks in advance for the help
 
Sundeep


I assume you have used Insert, Hyperlink and directed the link to Sheet2 C2

When I do that and drag down it changes to C3, C4 etc


The Hyperlink address should be
Code: 
Sheet2!C2 not [code]Sheet2!$C$2


Similarily if you have used the Hyperlink Formula it should be

=HYPERLINK(Sheet2!C2,"Test")

not

=HYPERLINK(Sheet2!$C$2,"Test")[/code]
 
Thanks Hui for the help


Sorry I might have not conveyed you my question rightly. I am attaching a file here please check it http://speedy.sh/AZVVW/2.xlsx


Please look it at once and kindly explain me how to do


Thanks you very much for the help
 
Hi ,


Try the following formula in C1 on Sheet1 :


=HYPERLINK("#Sheet2!"&ADDRESS(ROW(C1),COLUMN(C1)))


Copy this down. The above will take you to C1 , C2 , C3 on Sheet2 ; your two examples of Sheet2 B2 and Sheet2 D2 are not clear ; can you give details of what should happen for another two hyperlinks ? Should it be F2 , H2 and so on ?


EDIT :


Try the following formula in C4 ; copy it across to D4 , E4 ,...


=HYPERLINK("#Sheet2!"&ADDRESS(ROW()+1,COLUMN($B$1)+(COLUMN()-3)*2),COLUMN(A1))


Narayan
 
Thanks Narayan,


That works like a gem.... Problem here is it works to link Jan 1 to Jan 31... but while trying to copy it Feb1 its not working ...Plz help


One more request..... Plz explain the formula

=HYPERLINK("#Sheet2!"&ADDRESS(ROW()+1,COLUMN($B$1)+(COLUMN()-3)*2),COLUMN(A1))


As far as my knowledge I read this as


Hyperlink a cell in Sheet2 (#sheet2!) and the address is (ADDRESS(ROW()+1,COLUMN($B$1)+(COLUMN()-3)*2,COLUMN(A1))


1)Here you have written Row()+1 what does it mean


2)COLUMN($B$1)+(COLUMN()-3)*2 (does this mean add to B1 and -3 to it and multiply by two) Plz guide me


3)COLUMN(A1) what does it indicate


Sorry for asking such basis questions.... I tried to learn myself googled how to understand formulas but unable to find any article..... If you can explain me it would be a great help for me


Lastly I want to say a BIG BIG BIG thanks to you...... Really you made my job easy
 
Hi ,


I think you have followed almost everything ! My explanation needs to cover a very small part.


1. ROW() returns the current row number ; since the first formula is in C4 , it will return 4. 1 is being added to this because you want to start from row 5 on Sheet2 ; if you omit this , you will start from row 4 on Sheet2.


2. COLUMN($B$1)+(COLUMN()-3)*2) : we need to start from column B , so the first $B$1 is explained ; thereafter , we need to skip alternate columns i.e. after column 2 , we need to consider columns 4 , 6 , 8 , 10 and so on ; this explains the multiplication by 2. Since we are starting the formula in column C , COLUMN() will return 3 ; since we don't need any offset at first , we need to have 0 , which explains the -3.


3. COLUMN(A1) is only to get the numbers 1 , 2 , 3 , 4 ,... as you had originally.


4. If you need to copy this to the next row , for February , you can figure out the following changes will need to be made :


Point #1 affects the ROW ; since February is on row 5 , and you want to remain in row 5 , you will have to omit the +1.


Point #2 affects the COLUMN ; since February ( on Sheet2 ) is in column BL , the $B$1 will have to be replaced by $BL$1.


Point #3 will not be affected ; instead of COLUMN(A1) , when you copy it down , Excel will change this reference to COLUMN(A2) ; however , since we are looking at only the column number , COLUMN(A1) and COLUMN(A2) will both return 1 , as we want. In the interest of being 'proper' , you can change this also to COLUMN(A1).


The revised formula , made for February , will therefore be :


=HYPERLINK("#Sheet2!"&ADDRESS(ROW(),COLUMN($BL$1)+(COLUMN()-3)*2),COLUMN(A1))


Narayan
 
Hi Narayan..


You are one of the best Formula Evaluator..


Can you please explain me.. only the single missing part from your "Formula Evaluate" post :)


"#Sheet2!" why is this HASH? Is this
Code: 
ThisWorkBook.Path
..


Regards,

Deb
 
Dear Narayan


Thanks for such an detailed explanation.....One more doubt in Point #3 regarding COLUMN(A1) in A1 column I have not written any number how its taken as 1 and so on


I have learned how to write it.........Thanks for that


Now I had one more stop ..... How to hyperlink a cell that already has formula..

I have prepared a calendar, wish to link that..I have uploaded the file here http://speedy.sh/Rnsqg/3.xlsx


Really thanks a ton Narayan
 
Hi ,


The ROW and COLUMN functions return the row number and the column number respectively.


Each of them has two versions - one where the cell reference is specified e.g. =ROW(J17) , which will return 17 , =COLUMN(J17) will return 10 , since column J is the tenth column.


The second version is one where the cell reference is not given e.g. =ROW() , which will return the row number where the formula is entered e.g. if you place your cursor in cell K2 , and enter the formula , Excel will display 2 ; if you place your cursor in cell K23 , and enter the formula , Excel will display 23.


So , when you say ROW(A1) , it will return 1 ; so also will COLUMN(A1) since column A is the first column.


The purpose of the above usage is so that when you copy across columns or down rows , the A1 will automatically be incremented by Excel , so that it becomes B1 , C1 , D1 ,... across columns , or A2 , A3 , A4 ,... down rows.


Regarding the other points , I will respond later.


Narayan
 
Hi Deb ,


I think you are right. See here :


http://answers.officewriter.com/how-to-create-hyperlinks-between-cells-in-an-excel-spreadsheet


Narayan
 
Dear Narayan


You are my excel guru...... Because as a newbie I am finding tough to understand the formulas, tried to learn myself by googling it but not found anyone with such an detailed explanation......Though these are basics to you Its a big devil for starters like me


I have tried some other formulas after I had learned to read the formula ......Thanks for that. (Even thanks is a small word)


Because today I am able to write some formulas and I am able to understand formulas because of you.


Anyways thanks for the time you have taken to answer me....
 
Hi ,


I have gone through your file ; the calendar looks beautiful.


Since your calendar will depend on the year selected in G2 , this is a complicated exercise , since Jan-1 will change according to the year , accordingly , so will all the other dates. Putting together a formula for all eventualities is going to be difficult.


I think it will be easier to implement this whole system using a macro ( VBA ). Is this OK with you ?


Narayan
 
Dear Narayan


I don't know even ABC of macro(VBA) even don't know where to apply in excel... If G2(Year) is the problem will lock G2 to that year i.e. 2013 and for 2014 I will prepare a new workbook


If the above solution doesnt work then proceed with VBA... From today I will start learning VBA


Narayan waiting for your reply in Managing data post also


Thank you
 
Dear Narayan


Please leave this I have found an alternative...... Instead of locking the year .....I just copied the whole sheet data to another sheet without formulas and Hyperlinks the numbers (Which you have taught me earlier


Thanks for the effort you put in here........


Narayan if possible plz comment on my another problem in the "Summing" topic (I think its very easy for you) But I am not able to move even 1 step further


Once again Thanks for all the work and for all the teaching
 
You must log in or register to reply here.
Back
Top