2 possible solutions, 0 clue help requested :)

[ellmason]

Hi Guys and Gals,

first post wondering if you can help! I am trying to use a lookup to lookup a name of a student in a list of students and return some information from a table about the students. I can of course complete this task with a =lookup(etc) function. what i would ideally like to do however is return the result (from the lookup vector) as a comment in a cell (so that a big result i.e. lots of text does not wreck the sheet.
e.g

A1 B1
John Smith Yes

So i want to lookup John Smith in the table see that there is an entry (i.e. "yes") and instead of returning the info in C1 just add it as a comment to cell B1.
Hope that makes sense

The other option as i see it would be if i could use the idea of dependant cells i.e. in a cell to have =another cell, but to retain the comments of the precendant cell on the dependant cell.
e.g. if A1= blah blah *hello* where *hello* is a comment... and i say D1=A1 can i make it return the *hello* as well as the blah blah.
or as a worse but possible solution if i had
A1= blah blah as a hyperlink to another document, could my D1=A1 cell display blah blah as a hyperlink?



thanks guys,
sorry to ramble...
Ell

 

[BenMiller]

I meant on Sheet2, column B. Either it says YES or ... it's blank?

Also, I'm leaving work now so give me about an hour to get home and I'll try to work on it then. I did something similar a while ago; just need to refresh my brain cells.

 

[ellmason]

Yes, they would be blank. I am uk so will be in bed anyway, thanks for the help!

 

[BenMiller]

Try

=IF(VLOOKUP(A1,Sheet2!A:B,2,FALSE)="Yes",HYPERLINK("[Document.xlsx]Sheet2!$A$"&MATCH(A1,Sheet2!$A$1:$A$5,0),"Link"),"")

on Sheet1 in B1.

This is assuming your document is named Document.xlsx; your sheets are named Sheet1 and Sheet2, and you're linking to a cell in column A on Sheet2. Also looking for an exact match from Sheet1_Col_A to Sheet2_Col_A.

Can all be adjusted.

 

[ellmason]

Sorry have been stuck all day!!
terrible weather over here, windy as a windy thing! Firstly thanks for all your help! i cant quite get this to work but can use the match and hyperlink part to return either a link or N/a and that is well enough for now, thanks again you are a star!
Ell

 

[BenMiller]

What part isn't working?