• 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.

Sheet name and Cell Reference (EDITED)

J

Jayadev Sarangi

Member
Hello, I need some urgent help on the below explained.


Link string - ='C:UsersjsarangiDocumentsFNNFY 2000Q2Working Files1 23 2000[FY13 Q3 Fnn Quarty Comson (01 23 2000).xlsm]Cypora'!$T$8


Need to give a cell reference to "Cypora (Sheet Name)" in the string.


Kindly advise


Regards

Jay
 
Hi Jayadev ,


If you say the reference is a string , then it would be :


"'C:UsersjsarangiDocumentsFNNFY 2000Q2Working Files1 23 2000[FY13 Q3 Fnn Quarty Comson (01 23 2000).xlsm]Cypora'!$T$8"


Now , if in the above string , the sheet name ( which is Cypora in the string ) is to referred to using a cell address , say J17 , then the above string would be :


"'C:UsersjsarangiDocumentsFNNFY 2000Q2Working Files1 23 2000[FY13 Q3 Fnn Quarty Comson (01 23 2000).xlsm]" & J17 & "'!$T$8"


Narayan
 
Thanks, But when I do the same and press enter it asks me to choose worksheet name as it does not recognize J17 as the sheet name.


Kindly advise


Regards

Jay
 
Hi Jayadev ,


Can you say what cell you are using for the sheet name , and what that cell contains ?


What use are you making of this link string ?


Narayan
 
Actually, I am linking one file to another with a drop down of sheet name. That is, as I choose a name from the drop down the link should move to another sheet. the sheet name and the drop down list are the same.


The cell may contain both numeric and text.


Let me know if you need any further information


Regards

Jay
 
Hi Jayadev ,


What is the meaning of linking ? Are you using the sheet name selected in the drop-down in a formula ? If so , can you give an example of the formula ? All you have to do is copy and paste it here. I am not able to visualize what exactly you are trying to do.


Narayan
 
Hi Narayan,


Yes, I am using the sheet name selected in the drop-down in a formula.I copy pasted the formula above at the beginning of this converstaion. again copying the same below.


='C:UsersjsarangiDocumentsFNNFY 2000Q2Working Files1 23 2000[FY13 Q3 Fnn Quarty Comson (01 23 2000).xlsm]Cypora'!$T$8


Cypora (sheet name) is in a drop down list in a cell. If I change, let's say from cypora to Cyper in the drop down the formula should also should change accordingly.


Regards

Jay
 
Hi Jayadev ,


Let me understand this :


You have used the formula :


='C:UsersjsarangiDocumentsFNNFY 2000Q2Working Files1 23 2000[FY13 Q3 Fnn Quarty Comson (01 23 2000).xlsm]Cypora'!$T$8


in some cell , in some sheet and workbook.


Now , you wish to make the sheet name , which is Cypora in the formula , variable , so that it can be selected via a drop-down.


In such a case , the formula cannot use a cell reference ; what you need to do is use the INDIRECT function as follows :


=INDIRECT("'C:UsersjsarangiDocumentsFNNFY 2000Q2Working Files1 23 2000[FY13 Q3 Fnn Quarty Comson (01 23 2000).xlsm]" & J17 & "'!$T$8")


where J17 contains the drop-down selection.


Note that for this to work , the external file you are referring to in the formula needs to be open.


Narayan
 
What Narayan posted would be the correct way to go if INDIRECT worked on external (closed) workbooks. Sadly, Microsoft didn't give us that capability.

Other threads about this topic:

http://chandoo.org/forums/topic/indirect-formulas


If you REALLY need to create a bunch of links and you have a list of different sheet names, we could write a quick macro to go through and create the links. They would not be dynamic though.
 
Hi Jayadev ,


If you are getting a #REF! error display , it means that the external file is not open ; once you open it , you should get the correct result displayed.


Narayan
 
Thanks Narayan. It is working. But everytime I have to open the external file to refresh the data. Is there any other way, where the data will be refreshed automatically withouth opening the external file?


Regards

Jay
 
Hi Jayadev ,


Sorry , but that's the way the INDIRECT function works.


Put this in Google , and check out the results :


Harlan Grove pull function


Narayan
 
You must log in or register to reply here.
Back
Top