Email:      Pass:    Pass?
Advertisements


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.

OR

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.


Similar Excel Video Tutorials

Helpful Excel Macros

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
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
Combine Multiple Workbooks into One
- This macro for Microsoft Excel allows you to combine multiple workbooks and worksheets into one new workbook and workshe
Delete All Comments in a Worksheet in Excel Macro
- Excel macro that will delete all of the comment contained within the active or current worksheet in Excel. This macro d
Delete Hidden Rows in a Workbook
- This macro will delete hidden rows from every worksheet in an entire workbook. However, note that this will only delete

Similar Topics







I have one sheet with first and last name in columns A and B (starting at row 11). I have a 2nd sheet with the same information in columns A and B, but
with comments/notes about the person in column C

What I originally wanted was if I selected the first or last name in the first sheet it would take me to sheet two to see the comments. However entering the links manually was taking way too much time.

I then decided in the first spreadsheet column AG (where the notes/comments were) I would use the HYPERLINK function to link to the corresponding notes. This worked with the following formula:

Code:

=HYPERLINK("[mysheet.xls]WRN!A11", A11&" "&B11&" Notes")


But when I copied this to the other cells the WRN!A11 never changed, so subsequent cells looked like this:

Code:

=HYPERLINK("[mysheet.xls]WRN!A11", A12&" "&B12&" Notes")


I then tried this formula, but it linked to A1 at the top of sheet2 rather than at the correct line:

Code:

=HYPERLINK("#'WRN'!A" & MATCH(A11&" "&B11,WRN!$D$11:$D$232,0),A11&" "&B11&" Notes")


Can anyone help me get the right formula? I'll take either way an easy way to get the links to work with the names directory or using a seperate column to hold the link. Thanks.


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?

Thanks

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.

Thanks!!


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

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

-Jamie


I would like to place instructions on how to enter data into the sheet,
placing the instruction notes off to the side ...but do not want to have
these notes printable.

Is this possible? any other suggestions?

I am using Microsoft Excel 2000

Thanks





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


Hi,

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.
Code:

 
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.

Code:

 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Print only if Note filled in properly
If Range("B32").Value  "" Or Range("Q2").Value  "" Then
Else
Cancel = True
MsgBox ("Please fill in Orginator box and the Consignment Note number!")
GoTo End1
End If
If Range("B32").Value  "" Then
Else
Cancel = True
MsgBox ("Please fill in the Orginator box!")
GoTo End1
End If
If Range("Q2").Value  "" Then
Else
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
Loop
'Tracking
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
'Description
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
'Save
Workbooks("Printed Notes.xls").Save
Workbooks("Printed Notes.xls").Close
End1:
Application.ScreenUpdating = True
End Sub


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

Thanks


I have an excel workbook which contains info on all credit notes we issue a month, the colunms a Date, Credit Note No., Relating invoice, Amount, Reason and Account. I also have a folder (C:\Documents and Settings\My Documents\Credit Notes) which contains all the credit notes in pdf format which are named by their credit note no. to match the excel sheet. Currently i am manually matching the hyperlinks to the corresponding file, but this is taking up far to much time. I was wondering if their is a formular i could use which would automatically match the cell name with the pdf file and create a hyperlink for each credit note.

Many Thanks
Ben


I am using CONCATENATE/INDIRECT to pull in 10 individual notes into 1 overall notes section. The issue is that some notes are getting cut off or incomplete notes are being pulled in.

I have a spreadsheet where individuals have three ActiveX Text boxes for notes. The spreadsheet is for monitoring 3 calls so each call has it's own notes section that the evaluator can note or comment on the score they gave for that call. The ActiveX boxes are linked to cells so the text from each box goes into their corresponding cells.

On a Call1 tab, the linked cell for the Call1 notes gets pulled in for up to 10 different people/tabs (indirect). This is set up similar to below

E34- Evaluator 1, Call 1 Notes
E35- Evaluator 2, Call 1 Notes
E36- Evaluator 3, Call 1 Notes
...etc to Evaluator 10, Call 1 Notes

I then have in column F pull in people's names and which call the notes are from so it would pull in my name and call number and notes:
"Name Call 1 Notes:
I like this call and scored it as a 100 although I would coach on yadda yadda yadda"

The formula above is dragged down to do the same for all 10 notes.

Finally, I have a concatenate of all 10 of the notes section. In summary it is filled out on individual tabs, pulled into one tab, named and call concanated with notes. The cell is linked to a big ActiveX text box. Unfortunately, notes are getting cut off and I don't know why.


I attached an example, you can see Jill on call 1 the notes get cut off. Could anyone elaborate on why and a possible solution? The spreadsheet has been stripped several components due to size constritions and confidentiality. The features in question are still in place.


I have racked my brain with this one all weekend and have so far come up with some pretty messy 1/2 solutions... I thought I'd see if anyone here could lead me in a cleaner direction... Here is what I am trying to do:

I am basically using Excel and Macros to format two different reports into one neat report. I put the two sheets in the workbook (Delinquency & Yardi Notes)

I need the Delinquency sheet to use tenant code (ie C8, C18) and the amount of time the charge is overdue (either 30 days, 30-60 days, 60-90, and over 90, (COLUMNS J:M)) to pull notes from the Yardi Notes sheet and put them in the Notes column(O) starting with the first line of that tenant (newest note to oldest note) How far back the charges go determines how far back to pull notes (90 days old being the max) The notes also need to be word wrapped so they all show up in the notes section. If only 150 characters of each note were transferred that would be primo.

I am having a real hard time getting them to match up with the tenant code and its charges.

I have attached the sheet and anyhelp would be friggin amazing, this project has been a thorn for a while...


Hi There,

I have 6 tables on a worksheet - these are ranges:

B2:I22 ; B25:I45 ; B48:I68 ; B71:I91 ; B94:I114 ; B117:I137

Now, sometimes I will have notes at the side of the table - I put these into
column J, so that any notes for the first tabe would appear in a cell in the
range J2:J22 ; any notes for the second table would appear in a cell in the
range J25:J45 ; etc.

What I want is a macro that will print each of the 6 tables above on a
separate sheet (landscape, centred horizontally & vertically, with Row &
column Headings), and if the table has notes in column J, these should be
displayed on the same sheet. I.e. if the table does not have any notes in
column J, then I don't want to see column J appear in the printout, as this
reduces unneccessarily the size of the text in the printout.

So I need a macro which prints out each table, but before doing so does a
search on the relative range in Column J (for that table) and decides whether
there are notes in that range and therefore whether to include column J in
the printout for that table.

Thanks for any help,

Gary T.



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 #
B=State
C=ZIP
D=Manager Name
E=Notes

Data Page
A=Store #,
B=State,
C=Zip......
...............
H=Notes


All,
I've searched the forum but didn't see what I think I need (do I know enought to recognize what I need?)...

I have a simple database with 2 tables. One titled Serial, other is Notes.

Columns in Serial:
SN | Location | Condition | Comment

columns in notes:
ID | Serial | Date_of_action | Comment | Location | Configuration | Condition

My goal is to update the comment entry in the Serial table with the most recent entry for that serial number from the Notes table.
I've (me, google, and smart people who contribute to the web) figured out this query to return the most recent comment per SN:

SELECT serial, comment
FROM notes
WHERE notes.id in (
SELECT TOP 1 ID
FROM Notes as dupe
where dupe.serial = notes.serial
order by date_of_action desc, id desc)
ORDER BY notes.serial, notes.date_of_action, notes.id;

My challenge now is that I can't figure out how to use the result of that query in the update query. It seems that an update statement like this would do the trick:

update serial
set comment=
(SELECT comment
FROM notes
WHERE notes.id in (
SELECT TOP 1 ID
FROM Notes as dupe
where dupe.serial = notes.serial
order by date_of_action desc, id desc)
ORDER BY notes.serial, notes.date_of_action, notes.id)
where serial.sn=notes.serial
;

In my mind, the where statement (where serial.sn=notes.serial) should let the query check for the matching serial, and use the comment from that matching serial. However, I get ye olde error message: "Invalid Memo, OLE, or Hyperlink Object in subquery 'comment'." Can anyone help me understand this?


I have a spreadsheet which I use to enter notes related to particular invoice numbers. A v-lookup pulls the notes into another tab. What I'm running into is that the notes can be quite lengthy - in excess of 1,000 characters. When the notes get too long, they don't all appear in a single cell (unless it's extremely wide).

I'm wondering if there may be a way to maintain my notes in a word doc instead? Maybe in a table that pulls in using a v-lookup? But I'm not able to figure out how to direct a v-lookup to a word doc.

Any suggestions?

Thanks in advance.


This is basically what I'm trying to do in Excel.
I'm trying to find all the possible combinations of a number between 2795 and 5000 exclusive which can increase in increments of 5, and that is made up of 92 notes that include 5 euro notes, 10 euro notes, 20 euro notes, 50 euro notes and 100 euro notes.

Can anyone code this for me?
Thanks.


I am wondering if this is possible. I have a small FileMakerPro database that has 6 columns when exporting out to Excel. Columns 1-4 have headers and values of 1-10 all the way down, simple. Column 5 has dates all the way down. Column 6 however has notes, not for every day, but some can be quite lengthy. Is there a way to convert this column to just show carets in the upper left hand corner (letting the user know there is notes there) and when clicked can pop open the notes on that given day?

Basically I want to keep the cells the standard size (so no wrap texting) and I dont want the cell to drag all the way over to column Z because the notes are so long.

Thanks!


Please can you help me with the following formula. I am trying to get a colour coded calendar which will show a date but when it falls on a weekend it highlights the previous Friday. The formula entered in cell AD2 on worksheet Calendar is

=IF(Notes!H18>=1,DATE(YEAR(Notes!H18),MONTH(Notes!H18)+21,DAY(Notes!H18)),DATE(YEAR(Notes!H15),MONTH (Notes!H15)-3,DAY(Notes!H15)))

This highlights the dates based on my criteria but it is showing on a Sunday. The result of my formula could be any date in the year depending on the criteria set on the worksheet called Notes.

I have attached a file which will hopefully help. I also require the same assistance with the formula AD8 on the same sheet which needs to avoid weekends also.

If I have attacked this from the wrong angle please advise. Any help is greatly appreciated.


Hi,
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 $:
150
200
200
220
260
340
440
470
770
1420

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.

Thanks


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 !