Use vlookup to lookup cell comments

Hi there

Ive been using vlookup for some time now and have just come across a wall that i cannot seem to overcome.

I work with large spreasheets with a huge amount of information on them, and hence i use vlookup to compile reports etc etc. Now my latest report requires that in combination with a vlookup to a multitude of information i require a vlookup funtion that can paste in a cell the "inserted comments" of the referenced cell, but not the data of the cell, on another sheet in the same workbook.

I have tried searching the forums all over the web but cannot seem to find an answer to this. VBA is not my forte and so i understand very little of it, so was wondering if there was not a simpler way to do this

Any help in this regard would be appreciated

Clint

Thanks, But thread was removed for some reason.

Thanks

I had a look at this, tried it, and it doesnt work for me

Works for me.

Can you post a sample workbook with the sheets you are using and including your VLOOKUP formula?

OK I have uploaded a simple condensed version of my actual file which im working with,

What i need to do is this:

Vlookup the comments from sheet "AG_Final" Column L Only with the corresponding lookup data from sheet "Trust Acc Deposits" and place the comments in column I

The named range in the first spreadsheet is Named SORTAREA

As you will see from the example, some comments have multiple rows which all need to be displayed in a single cell.

The vlookup formula at this stage is =VLOOKUP(B4,SORTAREA,12,0) but this returns the data in the cell, and not the comment only.

Looking forward to your assistance

Clint

Hi Clint

Sorry I could not reply earlier. There was an error in the original example and I have corrected it so that it works with your file.

It should have used the MATCH function rather than VLOOKUP as MATCH returns the row number required and also the ADDRESS function for your example needs the Sheet name as the formula is located on a different page.

To summarize, the Match function combined with the Address and Indirect functions give you the location of the cell containing a comment. The UDF MyComment will display the comment from the target cell, the UDF MUST be present in a Module in the VBE, you can see it clicking ALT+F11.

Your revised file is attached.

Regards

Jeff

Maybe im being stupid here, but when i open the revised spreadhseet u sent i get an error in the cell where you pasted the formula =mycomment(INDIRECT(ADDRESS(MATCH(B4,'AG_Final List'!A:A,0),12,4, TRUE, "AG_Final List")))

Which is #NAME?

Whem i copy the code and formula to my spreadsheet the same error message displays.

Clint

Hi Clint

The most likely reason is that you have macros disabled, because there is a UDF you need to have macros enabled, also you need to copy the function from the VBE in the test file to your working sheet.

Regards

Jeff

Hi jeff

I did copy the code across and enabled macros but the same error occurs, will try again and get back to you.

Sorry for being a pain

Clint

Hi Jeff,

No change in the error code. I have uploaded my form for you to see, amybe point me where im going wrong.

Thanks

Clint

Hi Clint

The problem is the location of the UDF, it needs to be in a Module not on the sheet, Go to the VBE and in the Project Panel right click in the area used by your file and select Insert|Module, then copy the UDF into the module, delete the code from the sheet, save and restart and it will work okay, at least it does for me.

Regards

Jeff

Dear

please find attached file.

i want cell value along with cell's comment

Please do not hijack others' threads.