Linked Data

[PhilipCCFE]

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

 

[Hui]

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

 

[PhilipCCFE]

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

 

[Hui]

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?

 

[PhilipCCFE]

Excel 2002, all the references are to the file itself; there are no external spreadsheets.

 

[Hui]

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?

 

[PhilipCCFE]

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.

 

[Hui]

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"))

 

[PhilipCCFE]

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.

 

[Hui]

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"