Create hyperlinks using VLOOKUP?

I would like to enter a model number(CM3636a) in A1.
When entered, is there a way to lookup in a table and create a hyperlink
automatically?
I have hundreds of model numbers which I want to associate a diagram(jpg).
I only want to do this once, then everytime the same model number is
entered, a link is created to that diagram.
Can anyone think of any ingenious ideas on how to achieve something like
this?

Joe


0
6/14/2004 8:33:43 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
325 Views

Similar Articles

[PageSpeed] 53

Hi
if you have the name of a file in column b try something like
=HYPERLINK("C:\temp\" & VLOOKUP(A1,'lookuptable'!A1:B100,2,0) & ".jpg")

--
Regards
Frank Kabel
Frankfurt, Germany


lunker55 wrote:
> I would like to enter a model number(CM3636a) in A1.
> When entered, is there a way to lookup in a table and create a
> hyperlink automatically?
> I have hundreds of model numbers which I want to associate a
> diagram(jpg). I only want to do this once, then everytime the same
> model number is entered, a link is created to that diagram.
> Can anyone think of any ingenious ideas on how to achieve something
> like this?
>
> Joe

0
frank.kabel (11126)
6/14/2004 8:34:11 PM
Thank you Frank.
Works perfectly. I am so happy.
Am I able to change the visible text in the hyperlink?
Instead of showing the location(very long-server location), can the cell
just show "Layout"?

Joe

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:uWVOO7kUEHA.1152@TK2MSFTNGP09.phx.gbl...
> Hi
> if you have the name of a file in column b try something like
> =HYPERLINK("C:\temp\" & VLOOKUP(A1,'lookuptable'!A1:B100,2,0) & ".jpg")
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> lunker55 wrote:
> > I would like to enter a model number(CM3636a) in A1.
> > When entered, is there a way to lookup in a table and create a
> > hyperlink automatically?
> > I have hundreds of model numbers which I want to associate a
> > diagram(jpg). I only want to do this once, then everytime the same
> > model number is entered, a link is created to that diagram.
> > Can anyone think of any ingenious ideas on how to achieve something
> > like this?
> >
> > Joe
>


0
6/15/2004 2:39:37 PM
Hi
yes, use the second parameter of the HYPERLINK function. e.g.
=HYPERLINK("C:\temp\" & VLOOKUP(A1,'lookuptable'!A1:B100,2,0) &
".jpg","Layout")

--
Regards
Frank Kabel
Frankfurt, Germany


lunker55 wrote:
> Thank you Frank.
> Works perfectly. I am so happy.
> Am I able to change the visible text in the hyperlink?
> Instead of showing the location(very long-server location), can the
> cell just show "Layout"?
>
> Joe
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:uWVOO7kUEHA.1152@TK2MSFTNGP09.phx.gbl...
>> Hi
>> if you have the name of a file in column b try something like
>> =HYPERLINK("C:\temp\" & VLOOKUP(A1,'lookuptable'!A1:B100,2,0) &
>> ".jpg")
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> lunker55 wrote:
>>> I would like to enter a model number(CM3636a) in A1.
>>> When entered, is there a way to lookup in a table and create a
>>> hyperlink automatically?
>>> I have hundreds of model numbers which I want to associate a
>>> diagram(jpg). I only want to do this once, then everytime the same
>>> model number is entered, a link is created to that diagram.
>>> Can anyone think of any ingenious ideas on how to achieve something
>>> like this?
>>>
>>> Joe

0
frank.kabel (11126)
6/15/2004 5:48:58 PM
Thanks Frank.
Perfect!
Joe


"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:uU$bjDwUEHA.2356@TK2MSFTNGP10.phx.gbl...
> Hi
> yes, use the second parameter of the HYPERLINK function. e.g.
> =HYPERLINK("C:\temp\" & VLOOKUP(A1,'lookuptable'!A1:B100,2,0) &
> ".jpg","Layout")
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> lunker55 wrote:
> > Thank you Frank.
> > Works perfectly. I am so happy.
> > Am I able to change the visible text in the hyperlink?
> > Instead of showing the location(very long-server location), can the
> > cell just show "Layout"?
> >
> > Joe
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:uWVOO7kUEHA.1152@TK2MSFTNGP09.phx.gbl...
> >> Hi
> >> if you have the name of a file in column b try something like
> >> =HYPERLINK("C:\temp\" & VLOOKUP(A1,'lookuptable'!A1:B100,2,0) &
> >> ".jpg")
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >>
> >>
> >> lunker55 wrote:
> >>> I would like to enter a model number(CM3636a) in A1.
> >>> When entered, is there a way to lookup in a table and create a
> >>> hyperlink automatically?
> >>> I have hundreds of model numbers which I want to associate a
> >>> diagram(jpg). I only want to do this once, then everytime the same
> >>> model number is entered, a link is created to that diagram.
> >>> Can anyone think of any ingenious ideas on how to achieve something
> >>> like this?
> >>>
> >>> Joe
>


0
6/15/2004 7:36:00 PM
Reply:

Similar Artilces:

Problems with creating a newsletter
I chose the form "Newsletter - email". I have created a 3 page newsletter. Now, I have NO IDEA how to send it out as an email. I do not want to send it as an attachment. I cannot figure out which "save as" format or what I need to do so that i can email this newsletter. Is there a website that goes through how to do this? Am I correct when I say that it has to be html in order for me to send it as email? That is not one of the options. Any help you give me would be greatly appreciated. Thank you, Markis www.adreamforabetterworld.com ...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Vlookup returns "0"
I am using the following formula =VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE and it is returning a zero if there is no data found in that cell. If there is no data found I would like it to display nothing. How can I do this? Carolyn, There are a couple ways so do this. One is to test it, which makes for doing the VLOOKUP function twice. 1) =if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" , VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)) This gets a little messy. Better yet, and simpler is to hide the original cell (column, whatever), an...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Opening pub files created with older Publisher versions #2
I have just upgraded to Publisher 2003 from 2000 and am having trouble with pub files sent to me for our chuch newsletter which is using Publisher 97 The text is not wrapping around graphics boxes. Can I fix this? I really don't want to go back to Pub 200 Thank Richard this is caused by Publisher 97 not been printer independent. Even if you went back to Publisher 2000, unless you have the identical font versions and printer driver you would have issues with formatting. It sounds like the person sending you the file has a garbage HP inkjet printer. Get them to install the HP5P laser p...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Hyperlink problem #3
I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

Creating Text Box in Publisher 2007 Crashes the Application
Hello, we have a clean install of Publisher 2007 under Windows XP SP2, and when we try to create a text box in a document, (both an existing document and a blank document), publisher crashes. Office is fully patched. I ran Office Diagnostics from the help menu and no problems were found and the issue persists. Any thoughts on how to resolve the issue? Thanks, Syd See if selecting a different printer as default helps. How to view error signatures if an Office program experiences a serious error and quits http://support.microsoft.com/kb/289508/en-us -- Mary Sauer MSFT MVP http://of...

Creating a Macro to Delete Commas #2
I have an excel file that the size will varry. I need a macro that will check all the fields for a comma. If there is one I would like to get rid of it. Does anyone have any idea how to do this? I have no idea and I have been assigned this task. Help --- Message posted from http://www.ExcelForum.com/ No macro required. ctrl-H for find/replace. find , replace nothing (leave the replace field blank). You can of course record that within a macro if you wish. Drabbacs >-----Original Message----- >I have an excel file that the size will varry. I need a macro that will >check ...

Create Exchange mailbox from command line
I'm writing a script using dsadd and I was wondering if it's possible to create an exchange mailbox from the command line. Donovan Maybe not exactly what you want but it may help: http://www.joeware.net/win/free/tools/exchmbx.htm -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Donovan Linton" <DonovanLinton@discussions.microsoft.com> wrote in message news:D9C839EF-883D-4E2E-8BE9-57782582F043@microsoft.com... > I'm writing a script using dsadd and I was wondering if it's possible to > create an ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

creating a backup on 2002 for Money 98
I am helping a friend who has 98. I need to make a backup of info on my 2002 for him to use on 98. Any suggestions as to how to do this? M98 can't read any file written by M02 besides .QIF import. M02 can't write any file readable by M98 except for .QIF export. Sounds like QIF export/import is your only choice. I suspect you will find this doesn't do what you want. "Carlotte" <Carlotta41@discussions.microsoft.com> wrote in message news:015b01c3d2fa$bbf8fd60$a101280a@phx.gbl... > I am helping a friend who has 98. I need to make a > backup of info on...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Adding a Macro to a VLookup Function
In Excel 2000 -- I would like to create a summary spreadsheet (sheet2). For each time that "Name" appears in sheet 1, row *, take information from the same row, but columns 2 and 6 and bring it over to sheet 2 in the designated area. I know that you need to do a "VLookup" function and I got that to work. I know that you also need a "Loop" statement, so that it will continue to perform the Vlookup and bring over the information for each time that the name appears. I don't know how to write the formula so that the VLookup and the Loop are combined. Examp...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

Start macro creating a mail with contact data and autotext
Hallo, I am working with an user form. The developing of that form started with Outlook XP with a lot of code inside for different buttons. I changed to Outlook 2007 and unfortunately the code of the form was not longer displayed. What I learned about this is that MS does not support to much code in the form (or maybe a bug). They also do not support any longer. I was sending this form to MS support but they told it is do much code inside and they do not know, why the code is not displayed. In Outlook 2003 the code is displayed as in Outlook XP. Because I do not know real...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...

XML Mapping: Creating a "non-repeating schema element" in VS2005
Question regarding XML mapping in Excel. XML Mapping in Excel allows you to map "non-repeating schema elements" to an individual cell while "repeating schema elements" automatically get handled as an Excel "list". I want a table(XML/XSD) exported from a VS2005 dataset to be CELL mappable rather than LIST mappable. Is there a way to make Excel interpret the native dataset XML/XSD as non-repeating? Said differently, what makes a schema element "repeating" versus "non-repeating"? Is it a unique XPATH statement? If my underlying table has...