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

Linked Data

PhilipCCFE

New Member
I have a spread sheet called index.xls, that contains several worksheets;


The Main worksheet is called index and contains the following:

Column A is a three letter Identifier, relating to data on other sheets. Each sheet is called by the three letter Identifier,

Column B is a Descripton.

Column C has a hyper link to each of the worksheets, made with the formula =HYPERLINK("[index.xls]"&A5&"!A1","Click Here")


Column D, E, and F relate to a number on one of the other worksheets; at current each cell is filled in with a formula as such =ACT!$B$4 , =ACT!$C$4 , =ACT!$D$4 where ACT is the code is A5. I would like to be able to use reference A5 rather than type out the code by hand for each cell.


Also is there a way so that the hyperlink in Column C can be name agnostic, allowing me to have a different name for the database?


Kind Regards


Philip McGaw

http://ccfe.ac.uk
 
Philip

Q1. Have a look at using the Indirect function.

=Act!$B$4 could be replaced by =INDIRECT(CONCATENATE(A1,"!",B4))

etc


Q2. Use the hyperlink function

so for your example, try:

=HYPERLINK(CONCATENATE("[Book1]",A1,"!",B1),"Link Name")

Note: You need to include the Workbook name in the Hyperlinks address
 
Hi Hui,


Thank you for your help; however it seems to not be working still.


I tried replacing with your equations; however it returned a #REF error.


I have uploaded the workbook to http://dl.dropbox.com/u/2861216/index.xls
 
Philip

The Index page links work for me with Excel 2010

The other links don't work as I don't have the appropriate spreadsheets


What version of Excel are you using?
 
Philip

The Links on the Index page work ok for me in Excel 2002

The links on the other pages refer to files on K:itermitimit

with further sub-directories and file names


Where exactly is your problem?
 
the problem is not with the links to external files; I am only trying to fix the index worksheet.


Index, D5 contains =ACT!$B$4, replacing that cell with =INDIRECT(CONCATENATE(A5,"!",$B$4)) brings up a #REF! error with B4 being selected on the Index sheet; not the ACT sheet.
 
Philip

I am lost

Do you want the links in Index:C5 or D5 to refer to something ?

What do you want them to refer to?


If you want D5 to return the value from $B$4 from the Sheet in A5 use:

=INDIRECT(CONCATENATE(A5,"!$B$4"))
 
Putting =INDIRECT(CONCATENATE(A5,"!$B$4")) in D5 works; =INDIRECT(CONCATENATE(A5,"!",$B$4)) does not.


Thank you for your continued help; it now works in a much more useful way.
 
Phillip

Indirect needs a Text value as a parameter which represents a valid address

=INDIRECT(CONCATENATE(A5,"!",$B$4)) is trying to concatenate A5, "!" and the value of B4 which in this case is empty resulting in an address of "Act!" without a Range component

So although the Concatenate returns a text value it is a meaningless address


=INDIRECT(CONCATENATE(A5,"!$B$4")) adds the value of $B$4 as a text to the address resulting in a meaningful address "Act!$B$4"
 
Back
Top