Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Hyperlink Function With A Match/lookup

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have a sheet of names (first name A11 and last name B11). There are many columns of data after the names, but the last column is AG which has notes and comments. I have decided I want to move the notes to another sheet. So I will have on the new sheet at column A5 first name and B5 last name anc C5 Notes.

I would like to create hyperlink to the notes from the first sheet. I tried using Named reference, but I have close to 2000 names anc counting. I looked at HYPERLINK, but I can not get the lookup and or match to work. I have two ways I would like it to to work:

1. I believe this is the hardest of the two. If I select the name on the first sheet that takes me to the corresponding notes on the 2nd sheet.


2. In column AG of Sheet1 I have the text "See Notes" which is a link to the the corresponding notes on the 2nd Sheet.

Also I need the links to be maintained when I sort the rows on the first sheet.

Thanks for any help.

View Answers     

Similar Excel Tutorials

HLOOKUP in Excel
The Hlookup function allows you to scan a row from left to right in search of a value and then return the contents ...
How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...

Helpful Excel Macros

Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means

Similar Topics

Hi guys,

I have something that resembles the layout below on a sheet.
I'd like to be able to at will be able to select which of the 3 (height, weight, notes) I can see at any one time. I know this is possible with a pivot table, but I cannot use one because the notes are always text and not numbers... anyone know a good way to tackle this?


HTML Code:

Name     Height     Weight      Notes
Name 1    178         75           Notes abt p1
Name 2     82          82          Notes abt p2
Name 3    171         63           Notes abt p3

I have a sheet with 600+ rows(customers) and would like users to enter in new notes and be able to view old notes along with the date entered.

I am trying to create a page that will allow the user to enter in multiple notes in one row via some kind of drop down that will date the notes as they are entered. This way the user can click the drop down and see all the notes entered for that data set ect.

If this is not possible is there another solution? perhaps a referencing function that displays all prior notes and allows you to enter new notes dating each one?

I have two worksheets in a file that have information I need to combine together. Sheet #1 has the name of Accounts with a unique ID associated with each account. Sheet #2 has notes for each one of these Aaccounts and also has the unique ID so that we can figure out which account from Sheet #1 goes with the notes from Sheet #2. My problem is that there might be 10 rows of notes in Sheet #2 that are associated with a single account on sheet #1.

Somehow I need to get all the notes from Sheet #2 associated with their Account from Sheet #1 using the unique ID so that I have an account name associated with each line of notes.


I'm trying to link my Notes db docs to Excel and it
not working the way I want it to. When I copy a Notes
doc link to Excel, I have to use Special Paste and
paste a Notes Icon (floating)link. It works but what
is needed is a Notes doc link that is assigned to a
cell in Execl, like a hyperlink that is assigned to an
Excel cell. How do I create cell hyperlink in Excel
that links to Notes db docs?

Thank you.

I'm trying to link my Notes db docs to Excel and it
not working the way I want it to. When I copy a Notes
doc link to Excel, I have to use Special Paste and
paste a Notes Icon (floating)link. It works but what
is needed is a Notes doc link that is assigned to a
cell in Execl, like a hyperlink that is assigned to an
Excel cell. How do I create cell hyperlink in Excel
that links to Notes db docs?

Thank you.

I have 2 Sheets, "Template" and "Notes".

In the Notes Sheet I have 3 Columns:
- Column A contains hyperlinks that activate only if the corresponding cell in Column B has a cell address in it. The hyperlink links back to the "Template" Sheet and to the cell address displayed in the corresponding cell in Column B. Column C is simply the text associated with that cell address on the "Template" sheet.
- So, the "Notes" sheet, e.g., has a list of 10 lines of Hyperlinks, cell addresses, and notes in their respective columns.
- The cell address in Column B and the Note in Column C are populated using a Macro ("Submit _Note" attached to a Command button on the "Template" sheet.
- Each time a user submits a note, a new line is created at the bottom of the existing list of hyperlinks and notes.

I would like to add code to the "Submit_Note" Macro that returns the user to the cell that that they just recorded the note for, e.g., user a writes a note in cell C3 of "Template" Sheet and clicks on the command button that runs the "Submit_Note" macro, which copies the note from C3 (and related cell address from C2**) to the "Notes" Tab.
** a separate macro records the cell address which is the subject of the note to cell C2

Thank you in advance.

I'm trying to link my Notes db docs to Excel and it
not working the way I want it to. When I copy a Notes
doc link to Excel, I have to use Special Paste and
paste a Notes Icon (floating)link. It works but what
is needed is a Notes doc link that is assigned to a
cell in Execl, like a hyperlink that is assigned to an
Excel cell. How do I create cell hyperlink in Excel
that links to Notes db docs?

Thank you

I'm really hoping this hasn't been asked already, but I've searched everywhere and can't find an answer. I've reached the "ready to throw my computer out the window" stage because I know this can't be as difficult as it seems.

Basically, I'm building a spreadsheet for our sales team to be able to keep track of their prospects. I have a main page which has a column of prospect names, and some other columns with various different types of information.

I'd like to create a hyperlink that when you click the prospect name on the main worksheet (called "Prospect List"), it links to a cell on another sheet (called "Notes") in which you can enter notes (the Notes sheet has an identical list of prospects, and the note cell would be adjacent to the corresponding prospect) . The catch is that there are over 500 entries, so the code has to be dynamic so I can just drag it down the column. That's what I can't seem to get to work.

I hope my explanation wasn't too complicated. Any help would be GREATLY appreciated.


Hi All,

Ive managed to create a spreadsheet that lists sales managers and their clients and can be updated to add new clients from a system report spreadsheet using a macro and advanced filter. One of the columns on the spreadsheet has notes on each client, the problem i am having is when I update the client list and sort it in ascending order the notes become out of sync with their respective client.
Now each client has a client ID number and I was wondering if there was anyway to link the Notes cell to a particular ID? So even after the sheet has been refreshed the notes still link to the correct client?

Thanks in advance

Hello everyone,

In my workbook, I have a "summary report" sheet for students. The data in the report depends on the drop down menu that has all of the student names.

In a previous sheet, I have a chart for "notes" and dates. The chart is arranged vertically with the student names in column A and then horizontally with blank spaces for notes, then dates.

From the "summary report" page, what I am trying to do is accomplish three things when I push the Save as PDF button I have created:

1) Save a PDF report in a user chosen directory[B](solved!)-thank you, thank you ProtonLeah
2) I want to automatically create a note with a date on the "notes" sheet ,in two separate columns, in the appropriate student row based upon the name information on the previous "student summary" page. (stored in J3)
3) I want to increase a value in column av1 by1. This too would be using the same row information on the "notes" sheet and it is counting how many reports have been produced and sent home. This number is important to help keep track of number of reports and the "student summary" save function uses the report number in the PDF file name that is created and saved.

I think I have figured out how to move about the row once I can figure out how to identify and select it.

Here is my very rough code so far. I have tried it with identifying a specific row and it does what I want...finds the next empty column and inserts information, but I am lost as to how to move from the "student summary" sheet to the Notes sheet, do the actions and move back to the student summary sheet. I think I have the part about going back to the student summary sheet.

Please Login or Register  to view this content.

Thanks for any help or suggestions you could give.


I have an excel sheet which creates delivery notes. Every time a delivery note is printed, I want to track it in another sheet.

I have the following line of code linked to a button.

ActiveWindow.SelectedSheets.PrintOut copies:=5

The problem is that the below macro runs when the sheet is printed using a BeforePrint. The macro only runs if a manual print is done but it breaks if I use the button.

The below is the code Im trying to run. It breaks on the line in blue.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Print only if Note filled in properly
If Range("B32").Value  "" Or Range("Q2").Value  "" Then
Cancel = True
MsgBox ("Please fill in Orginator box and the Consignment Note number!")
GoTo End1
End If
If Range("B32").Value  "" Then
Cancel = True
MsgBox ("Please fill in the Orginator box!")
GoTo End1
End If
If Range("Q2").Value  "" Then
Cancel = True
MsgBox ("Please fill in the Consignment Note number!")
GoTo End1
End If
'Printed Notes
Application.ScreenUpdating = False
Set a = ActiveSheet
Workbooks.Open Filename:= _
    "C:\Documents and Settings\Jason.THESIMPSONS.001\Desktop\Printed Notes.xls"
j = 1
Workbooks("ASCO Consignment Note.xls").Activate
 Do Until IsEmpty(Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("A" & j)) 'Code Breaks Here. 
j = j + 1
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("A" & j).Value = a.Range("G7").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("B" & j).Value = a.Range("R33:U33").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("C" & j).Value = a.Range("R34:U34").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("D" & j).Value = a.Range("R35:U35").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("E" & j).Value = a.Range("R36:U36").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("F" & j).Value = Now()
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("G" & j).Value = a.Range("B32").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("H" & j).Value = a.Range("Q2").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("I" & j).Value = a.Range("G10").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("J" & j).Value = a.Range("G11").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("K" & j).Value = a.Range("G12").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("L" & j).Value = a.Range("L11").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("M" & j).Value = a.Range("L12").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("N" & j).Value = a.Range("Q11").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("O" & j).Value = a.Range("Q12").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("P" & j).Value = a.Range("G17").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("Q" & j).Value = a.Range("G18").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("R" & j).Value = a.Range("G19").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("S" & j).Value = a.Range("G20").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("T" & j).Value = a.Range("G21").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("U" & j).Value = a.Range("G22").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("V" & j).Value = a.Range("G23").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("W" & j).Value = a.Range("G24").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("X" & j).Value = a.Range("G25").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("Y" & j).Value = a.Range("G26").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("Z" & j).Value = a.Range("G27").Value
Workbooks("Printed Notes.xls").Sheets("Printed Notes").Range("AA" & j).Value = a.Range("G28").Value
Workbooks("Printed Notes.xls").Save
Workbooks("Printed Notes.xls").Close
Application.ScreenUpdating = True
End Sub

Any help will be appreciated greatly as it will save a lot of time at work.


I have a userform where the user can select a value from a combobox. Several textbox values are completed depending on the value of the combobox through a vlookup command in VBA. Additionally, there is a commandbutton titled [NOTES] which will open another userform and allow the user to see the notes for each item. I have it all set up for the user to be able to edit notes, etc. All of the information that is pulled for the userform is on a hidden sheet.

Whenever the notes field exceeds 255 characters, the user is still able to select a value with the combobox, but none of the textbox options are filled in, regardless of what value they pick (meaning, whether or not the exceeded notes field is associated with the combobox value or not). If they click the notes button, the notes come up as they should, no problem. I've tested this and confirmed that the issue starts once ANY 'notes' cell exceeds 255 characters within the column they are stored in.

I know the cells have a limit, though i thought it was 1024, or somewhere around there.

I've been trying to make a sample sheet should it be required, but of course the problem isn't coming up there, and the original sheet has alot of code and would be quite the burden to look through. I'll try to water it down if need be.

Any thoughts?

Help Please !
I have created a dashboard that a state manager can use to pull up just the stores in their state. I need to return the notes for each store typed in column E of "manager" beginning with E5 back to the data set on the "Data" sheet.
example of process
1) notes data is entered in "manager" E5 for Store 84
2) notes data is entered in "manager" E6 for store 195

I need to lookup the store # from "manager" A5, locate the same store # in "Data" column A (let's say row 42 is the corresponding number), then the notes from "manager" E5 need to paste into "data" H42. I need it to loop down to "manger" E6,E7,E8...until it hits an empty cell

I think it needs a dowhilenot loop but I'm not sure how to construct it.
I appreciate any help!

Manager Page:

A=Store #
D=Manager Name

Data Page
A=Store #,

Can anyone help me in solving my following problem.

I have few currency notes of different denominations totalling to $4470. I now want to distribute these notes under different lots of $:

The notes I have are of following denominations:
Value Notes
$1000 1
$200 4
$100 19
$50 7
$20 18
$10 6

Is there any functions that I can use to allocate this automatically.

Any hints would be highly appreciated.


Dear members,

I am attempting to create a lookup spreadsheet. The big picture is this: I need a quick way to look up what paperwork i have and what i still need for each of my 200+ clients. I have created checkboxes and a document matrix where document names get color coded when a checkbox is checked next to clients name. Now i am attempting to add another feature where when the box is checked off i can see notes ive put in for that client. I picture it as a textbox with a scrollbar linked to a cell on a different sheet where the notes are stored. I cannot to get it to work, i am able to insert the textbox but the notes appear in a single Line, no matter how big the texbox is, which makes the scroll bar not function. Please help in any way possible. Thank You !

Hi Guys

I am trying to run a macro which will effectivly pop my notes database up and copy the files then paste them in excel

I have the code to pop notes up

Sub LotusNotes_CVXDocLib()
Application.ScreenUpdating = False
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
Application.ActiveWorkbook.FollowHyperlink Address:="Notes://address", NewWindow:=True
End If
Exit Sub
End Sub

but I can not get it to select all and copy. Any ideas?

Hi . . .

I need some help with a macro please . . .

I have table1 in Sheet 1 and the same table on sheet 2.
Table2 in Sheet 2 has a few additional columns for notes / comments.

Table on Sheet 1 = 5 columns. ( the third column in the table having unique values )
Table on Sheet 2 = Same 5 columns as table1 on sheet1 plus extra 4 columns for notes.

I need to be able to update any changes to table1 in Sheet 1 into table2 on sheet 2 -
( Including new rows, and keeping the notes that were made in the extra columns on table2).

Thank you


Hi all,

Thanks to this forum, my workload has been extremely simplyfied due to the help on offer and for that I am grateful, but I am back now with another question which I am not sure is even possible but here goes...

What I would like is a button on my spreadsheet in cell H6 a button which brings up a "notes" popup which is relevant to the worksheet of the workbook that I am working on...

These notes would obviously need to be editable, but inside these notes I would like to have a drop down menu of contacted by

incoming, outgoing
Letter phone email

with editable options for each (such as if I choose outgoing and letter then I can choose from all the relevant letter references we use such as 21A or Noise Disruption)

Aswell as the normal area for general notes. Each note would also need to be dated automatically.

I dont even know if it is possible, and if it is what would be the ideal way to do it? would it be best to do it as I have suggested or just add a new sheet and call it notes and add the drop downs to that etc
(although I can do it myself this way, It will make my workbook extremely messy which is why i want a button that I can click to open and close the "notes")

Anyway all help, Ideas and suggestions are very welcome,

Thanks in advance

I've got forms created by others that are fine until I start entering fields with the required information. Then what looks like editors' notes pop up on the right. That's okay EXCEPT these notes print whereas they should not print. And the form is shrunk to fit in these notes. Somebody once explained to me how to change something in my PC to prevent these obnoxious notes from printing. I don't use Excel often enough to remember. Please help.

Also, is there a way to prevent these notes from appearing at all so that I can forward the form to a client to simply print and sign?


Actually this could be two questions, I suppose.

For a business report I am using MAC office Excel for OSX. Page one is Excel on a landscape format. I need to link (from a "Notes" column) of certain entries to a Word document that is several pages in length.

How do I attach Word document to Excel with each being on separate sheets?
How do I hyperlink from a Notes cell in Excel (sheet 1) to this Word document (sheet 2).

I am building a workbook which has the following sheets
1. Notes
2. service details
3 - 9. property details 1-7
5. data sheet.

The data sheet cell g42 will contain a variable between 1-8

The idea is that the next sheet is made visible as a sheet is completed.

On starting, the notes page should always be visible, others all hidden
if G42 = 1 then Notes + service details should be visible, all others hidden

if G42= 2 then notes + service details + property details 1, visible, others hidden.

if g42= 3 then notes + service details + property details 1 + property details 2, visible

and so on.

I've been messing about trying to adapt some code that I had for another purpose but........ my vb is still poor.

Grateful any help


I have been struggling with what seems to be a simple requirement..

I have two sheets in a workbook.

Sheet A called (CONTACTS) contains column A (Rows A:2 - A:7537) full of unique CONTACT ID records..Next to each ID number is the Contacts full name (B:2 - B:7537).

SHeet B (NOTES) contains several rows of Notes related to the CONTACT ID. (Rows A:2 - A:18879).. THese notes are only identified by the contact ID

I need to add a column into sheet B that will look up the CONTACT ID on the CONTACTS sheet and copy the Contacts full name (Column B from CONTACTS Sheet) to the cell next to the CONTACT ID on the NOTE sheet..

Should I use Vlookup and name the CONTACT ID rows ? or is there a lookup macro that would / could run ?

Any help or direction will allow me to sleep once again... Thanks

CONTACTS SHEET screen shot

NOTES SHEET screen shot

I have oversimplified the following scenario in order to get to a specific question...

I have the following two excel files:
File #1 = ModelData.xls
File #2 = DistrictFormat.xls

File #1 contains one worksheet named "basn". The worksheet includes a column with header "NAME" and text data below it.

File #2 contains two worksheets; "Notes" and "BASIN_GN"

The "Notes" worksheet contains a cell (A1) with the following value


The "BASINS_GN" contains a column (column A) with the header "NAME" but no data below it.

Now here's my question...

I would like to use a HLOOKUP function in worksheet BASIN_GN to return a value from the column "NAME" in the basn worksheet. The hitch is that I don't want to use the value in cell A1 of the Notes worksheet instead of hard entering the lookup range. The following is the formula that I used.


Why doesn't it work. How can I make this work so that I can change the value in cell A1 so that HLOOKUP function will retreive data from different files (when value in A1 is changed)?

I realize that it is setting Notes!A1 as the lookup range. How can I get it to do what I want instead? Thank you for your help.

Is there a faster way to hyperlink in excel 2003 other that Help/Microsoft Excel Help/Create a link between cells in the same worksheet or workbook

I have (1) workbook with 2 sheets.

If I use column A in sheet 1 to list the following going down the list like this:
(A1) Apple
(A2) Boy
(A3) Charlie
(A4) Dan
(A5) Ed
(A6) Frank
(A7) Ginger

And in Sheet 2, I have the same names again, but use colums B,C,D,E,and F for other information (address, phone, fax, email and notes)

Is there a way that I can hyperlink Charlie from Sheet 1 (A3) to Charlie in sheet 2 (Who may now be (A41) , without going to Sheet 2 to see what cell each contact name (or Charlie) is in ?

Sheet 2 is really a Master Contact list with 3,000 names that will grow only for 1 year.

Sheet 1 may only have 30 names total that need linked. There will be a total of about 10-15 sheets with different names, but the idea is to link the name from any sheet to the master list of Sheet 2


I have a simple query. Column 1 is Customers. Column 2 is items associated with those customers. Column 3 is notes that are manually inputed on the sheet based on what's in columns 1 and 2. These notes are changed regularly. My problem is that when the query is refreshed the notes don't stay with the apropriate row. I have tried the 3 different options in the properties tab and that didn't help. Is there a way around that or is ther a better way to accomplish this?