Hyperlink text from linked workbook

I have a seperat workbook for each of my clients with the clientno as the 
filename i.e. 1001.xlsm, 1002.xlsm etc. All these files are in lets say 
c:/docs. First sheet in each clientworkbook is "Factsheet". In each factsheet 
A1 states the clientname.

In the same directory I have my mastersheet.
In the first column I have the client numbers.
In the second column I want to have the hyperlinkformulas that, based on the 
value in 1. column, makes the link to the relevant clientworkbook AND with 
the clientname picked up from the linked workbook Factsheet!A1 as the linktext

I have two formulas that each does half of the job:
=HYPERLINK("C:\Docs\"&$a2&".xlsm";a2)
In this case the hyperlink works and opens the relevant file, but the 
hyperlinktest is not the client name from the Factsheet

=HYPERLINK("C:\Docs\"&$a2&".xlsm";'C:\Docs\[1001.xlsm]Factsheet'!$a$1)

In this formula I get the clientname from the 1001.xlsm's factsheets cell 
A1, but the hyperlink does not work AND the filename in the formula is not a 
reference to the clientnumber in the first column.

Can anybody please help me with a formula that does both the tricks?
Thank you very much in advance.


0
Utf
1/3/2010 11:33:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
1014 Views

Similar Articles

[PageSpeed] 14

hi, Albert !

I guess your best bet is to use vba:

- in '_change' event for the cells where you put the client number (column 1 ?)
- to read from closed workbooks and write the content (i.e.) in column 2 ?
- after that, you could use column 2 as the hyperlink-text function

one question: do you use to modify several cells in column 1 at one time ?

regards,
hector.

__ OP __
> I have a seperat workbook for each of my clients with the clientno as the filename i.e. 1001.xlsm, 1002.xlsm etc.
> All these files are in lets say c:/docs.
> First sheet in each clientworkbook is "Factsheet".
> In each factsheet A1 states the clientname.
> In the same directory I have my mastersheet.
> In the first column I have the client numbers.
> In the second column I want to have the hyperlink formulas that
> based on the value in 1. column makes the link to the relevant clientworkbook
> AND with the clientname picked up from the linked workbook Factsheet!A1 as the linktext
> I have two formulas that each does half of the job:
> =HYPERLINK("C:\Docs\"&$a2&".xlsm";a2)
> In this case the hyperlink works and opens the relevant file
> but the hyperlinktest is not the client name from the Factsheet
> =HYPERLINK("C:\Docs\"&$a2&".xlsm";'C:\Docs\[1001.xlsm]Factsheet'!$a$1)
> In this formula I get the clientname from the 1001.xlsm's factsheets cell A1, but the hyperlink does not work
> AND the filename in the formula is not a reference to the clientnumber in the first column.
> Can anybody please help me with a formula that does both the tricks? 


0
H
1/4/2010 6:30:24 PM
Reply:

Similar Artilces:

Linking charts #3
Hi, I'm wondering if I can have a chart on one workbook page linked to a chart on another workbook page (it will be the same chart derived from the same source data), and when I refresh the data, both charts will update. Here's why: I have the data and 1st version of chart in a "working" section of the workbook. Then I have a "report" view of the chart that need to be a certain size- this is the 2nd copy of the working graph. Currently I've been copying and pasting the "working" chart into the "report" chartbut that is kind of a pain. ...

hyperlink #4
Hello, I have. a spreadsheet with 300+ records. One column has website addresses. How can I format those cells so that when I click on the web address, it will go to the websites? I know Excel explains it but I'm not understanding it. if someone could help me with this, I would really appreciate it. Thanks, Mindy Mindy Select the cells, hit F2 and ENTER as many times as needed to roll through the range and change to hyperlinks. Whole column at a time use this macro from David McRitchie. Sub MakeHyperlinks() Dim cell As Range For Each cell In Intersect(Selection, _ ...

Compare text
I have two huge paragraphs. I want to check if both of them are same. Can macros help me in easing this process? If I have left out any word in the para it shd should indicate which word I have left out. Is that possible? thanks for the help The following macro should work. Enter the numbers of the first and second paragraphs to check in the dialog boxes. If the paragraphs are the same the macro will report that. If not it will highlight the first non-matching character in the second chosen paragraph. Sub CompareParagraphs() Dim FirstPara As Range Dim OtherPara As Range Dim...

links not updating although autocalc is On?
Hi My very large workbook has developed a strange problem that cell references from one sheet to another have stopped updating. e.g. if data is entered on one sheet it will not update linked cells in other sheets. Stragely, if I recreate the link - which is precisely the same- it works once but then becomes 'frozen' and will not update if the data is changed. Would be grateful if someone could help me remove this critical spanner from my works. Thanks Keith maybe, just maybe, if you do: Edit|replace what: = (equal sign) with: = (equal sign) replace all it'll wake excel up...

Save Workbook Under Custom Name
I have came across assistance to save a workbook using a macro which will save a workbook with a name typed in a specific cell. It works nicely, but I want to specify also the path and sub-directory in which the file must be saved. I have used the following code ActiveWorkbook.SaveAs Range("Name").Value The contents in Range "Name" will be the file name, but it will safe the workbook under the new name in the same directory in which the master file is allocated. Once I am able to specify the path, I want to expand my macro to clear all ranges as well, then the master wi...

Hyperlinks
I have a spreadsheet that is 3604 rows & 6 columns. Column E has only hyperlinks to URL's. I have filtered my data via column B. I tried to A-Z Column A. When I do, the hyperlinks do not stay with the data. The mask looks correct but when you scroll over the hyperlink you see a completely different URL.????? Can anyone tell me how to make sure the Hyperlink keeps the correct URL when filtering and sorting the data. Try using the =HYPERLINK() worksheet function Tim "LeisaA" <LeisaA@discussions.microsoft.com> wrote in message news:3362C55C-FBA2-43...

Signature and text format query
I have created a signature which inserts automatically when I click New, Reply or Forward in Outlook. But when right clicking a file and selecting Send To, Mail Recipient it opens a new untitled message in Plain Text with a signature/footnote ("The message is ready to be sent...). How can I have that message open up in HTML format instead? Also, how can I set it up so that my own signature that I have created automatically appears when the untitled message opens up (instead of having to insert it manually afterwards)? The same question applies when I have an open file (eg: ...

Shortcut for wrap text or vertical center in cell
Is there a keyboard shortcut for wrap text? how about centering data vertically in a cell? Have you considered assigning a short macro to the KB combination of i.e. <Ctrl>+<Shift>+W? Dennis "webbuzzard" wrote: > Is there a keyboard shortcut for wrap text? how about centering data > vertically in a cell? Buzz, Looking in Help for formatting shorcuts yielded nothing. You can roll your own, using the underlines. For wrap text, it's a bit long. Alt-o, e, a, Alt-w, Enter. For vertical centering, Alt-o, e, a, Alt-V, c, Enter. Or you could record a ma...

Excel 2000 Hyperlinks
Probably a silly question, but how do you turn off hyperlinks? I know how to remove them, but I want the entire feature turned off so that when I type an email address it doesn't turn it into an active hyperlink. In XP, you can go to Tools, Autocorrect options, but I can't seem to find the option in 2000. >>but I can't seem to find the option in 2000. Me either. You could precede the entry with an apostrophe. -- Jim Rech Excel MVP .... or hit CTRL-Z right after it converts. Rgds, Andy ...

Hyperlinks and e-mail
I want to be able to send an e-mail as a result of a vlookup. so if I have the name in Col A,the email address in Col B,the case number in Col C and the Vlookup formula on a different page I want to be able to lookup the name and also get the email hyperlink, with the case number included in the subject field. Then I need to automate that process with a Macro.......... Ade Hi not quite sure this is what you want but for mailing macros have a look at Ron's site: http://www.rondebruin.nl/sendmail.htm -- Regards Frank Kabel Frankfurt, Germany Ade wrote: > I want to be able to send a...

Splitting Text in a Cell
Using Excel XP, I have a cell that has full names of people (First & Last Names). Can I split these two text somehow recognizing the space in between them? Thank you. Brig, have a look at data, text to columns, post back if you need any help with it -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 97 & 2000 ** remove news from my email address to reply by email ** "Brig" <golteb@netzero.net> wrote in message news:ed9422dXEH...

Can you hyperlink to a hyperlink?
I'm am having trouble linking/referencing a cell that contains another hyperlink. There is a master list on one tab, that has hyperlinks to all documents listed. Our other tabs (within the same spreadsheet) contain the same information that is stored in the master list tab, but specific to a group. (i.e. a training tab, lists only the training documents) The idea is to maintain only the hyperlink on the master list page, but allow others to click of the hyperlink to a document on their specific group tab, and it would open the document. Originally, I just used the &q...

CEdit display text with Gdiplus:Font, Graphics
Hello, I display the text in an own 'COEdit : public CEdit' - class with Gdiplus - Graphics to make the text a little bit transparent. The display of text is ok, but the caret is wrong. I do not call edt->SetFont(...); because I have an own font, but it is an 'Gdiplus::Font' and no 'CFont'. So the carent must be in the wrong position. Thanks --- Frank Iversen I haven't done this before. I guess the edit control is doing its calucations based on the default font. But try this: Create a CFont object with the same font name and size as the one you are us...

Excel 2002
Hello, I've got a problem with formatting changes not updating in linked document... Simply put, if you have a document that has a linked cell to another worksheet, the formatting ie...COLORS doesn't change when you change the source document with the linked document??? Yes, for instance text will be updated... but again, not formatting like making it BOLD or changing the colors of the text! Any ideas... I did see a KB article 1009182 regarding a problem linking WORD with Excel... but not Excel with Excel and the formatting not holding... it possibly is the case with Excel to Excel ...

Mode function for text
Is there a function that will return the most frequently occurring text for a specified range of cells? I know that MODE will work for numeric values, but is there a MODEA that will do this for text values? Ed Ed Assuming data in B1:B20, this array formula will do the trick: =INDEX(B1:B20,MODE(MATCH(B1:B20,B1:B20,0))) Must be entered with <Shift><Ctrl><Enter> instead of <Enter>, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed by curly brackets { } Don't enter these brackets yourself. -- Best Regards L...

Hyperlinks not hyperlinked in long messages
I am receiving a regular mail which contains many hyperlinks. It seems that, when the message exceeds a certain size, only the first hyperlinks work (colored blue and underlined), but they stop working somewhere in the last third of the message. I see this happening with messages with sizes of 129, 185, 210 KB. But on another message with 43KB all the hyperlinks work. (I can send the mentioned messages to someone willing to suggest a solution) I don't know if the problem has to do with the size of the message or the number of hyperlinks within. But if I break one of these messages i...

checking other pages and workbooks for repeating numbers?
How do I check to see if a number has already been used in another workbook or on another page? Such as (j11=7pfo1by) is billable area and (k11=54635) is billable item this can only be billed once and I would like for excel to check the entire weekly workbook and other workbooks for this specific set of numbers. If I type them in it would tell me that set of numbers have already been used. Both sets of numbers must be the same I could have the same item number in another billiable area but I will not have the same item and aera twice. ...

Hyperlinking images
I have a worksheet with several columns. One of the columns is entitled image which I've hyperlinked to image files. I thought that if I were to email this worksheet to someone else that my images would go along with it. Not happening. How do I set it up, so that the images are embedded on each row as a file name and if I email it to someone, all of the images are transferred also. I know it's a simple solution, but I'm not finding it. Thanks. You must either send all the image files along with the workbook or insert the images into the workbook. Sending ...

Hyperlink?
I have been trying to put a hyperlink to a bank in an Excel cell. After difficulty I tried very carefully to follow instructions. When I click on the link, I get a message that starts with "Unable to open <address>" and ends with "Cannot download the information you requested." I do not know if the following is a clue. The address in the cell starts with http://. If I manually copy the address and paste it into the browser it works. But the address that finally appears starts with https://. The rest of the address is modified as well. I am using a relatively...

Wrapping Text
Excel 2007 XP SP3 How do I get a cell to automatically resize itself when you paste text that extends beyond the cell after changing the allignment to "wrap text"? Thanks for reading and your help 8^> Try paste special - radio button, values, ok i'm assuming you'd already formatted cell to wrap "WSR" wrote: > Excel 2007 > XP SP3 > > How do I get a cell to automatically resize itself when you paste text that > extends beyond the cell after changing the allignment to "wrap text"? > > Thanks for reading and your help >...

Formatting question. Manual line num tab letter tab text mult line
How to setup this... Manual line num tab letter tab text multiple lines so that text lines after first auto indent to second tab. I'm not sure what you are describing here... A hanging indent perhaps? -- Stefan Blom Microsoft Word MVP "Mel_3" <Mel3@discussions.microsoft.com> wrote in message news:5EEB8597-0A53-414D-82B7-66F98ADC2604@microsoft.com... > How to setup this... > Manual line num tab letter tab text multiple lines so that text lines > after > first auto indent to second tab. ...

Creating XML Text
Hello, I need to create some well formed XML text live in memory. What I really want is to use the XmlTextWriter object applied to some location in memory (Stream or something?), because it does a nice job of formatting/identing, etc. But I can't figure out how to instantiate the XmlTextWriter object correctly w/o a filespec. I'm not allowed to directly instantiate a Stream & therefore a StreamWriter for that matter since there's no filespec involved. Can someone show me some code to make this work? TIA, -- John C. Bowman Software Engineer Thermo Electron Scientific I...

Numbers show instead of text
I entered text into some cells, and it shows up as numbers. What do I do? What sort of text? Where is it entered? What type of numbers? A small example would be nice. Gord Dibben MS Excel MVP On Sat, 7 Oct 2006 14:36:02 -0700, Mcoste <Mcoste@discussions.microsoft.com> wrote: >I entered text into some cells, and it shows up as numbers. What do I do? It sounds like you have the data formatted for numeric in the first row of that column. Select the problem cells, right click, select format cells, and select Number type of "General". Good Luck! "Mcoste&quo...

create a hyperlink field for a tamplate?
I have a template with various fields, when I make use of it, all fields are displayed correctly. One of this fields is a text string that holds pathname and file name of a document related to the record. I would like to have this string come in as a hyperlink, and not have to go and manually change each cell in the column with the Insert - Hyperlink commands. Does anyone knows how to do this? NEW If using Excel 2002 or 2003 you can set this up under Tools>Autocorrect Options>Autoformat as you type. For existing strings a formula in an adjacent cell....... =HYPERLINK(cellref) ...

Can Pivot display text as data values
Hi! I'm trying to create a "dynamic phonebook" using Pivot. I.e. I can select from Page e.g. City and department, in Row Field there's listed Role and Name and In Data Items phone number and email-address. It basicly looks great, but it seems its impossible to display text values of the data. Is it possible to display text in data items? Example Page: City:Capitol City Dep.:IT-Department Rows: Role: Field Engineer Name: John Doe Data: Phone: 01 23456 Email: john.doe@mail.com I know Pivot wasn't meant for this purpose, but it would be a good tool for tha...