Vlookup and Get comments ( Macro or VBA or magic)

OrochiNoob

New Member
Joined
May 8, 2019
Messages
20
Hello to all

So Im trying to accomplish 2 things, I have a table that was poorly formatted were I need to get information 50+ times a day
I was able to get the information but there are comments on SOME cells,

This is what I got so far
1st DropDown ( Main Category )
2nd Indirect Dropdown ( Second Category )

Vlookup to look for the Indirect result

I need help getting the comments from the Vllokup result to and adjacent cell


A
1 Main Drop Selection
2 Indirect DropdownSelection

A B C
10 Field1 Vlookup Indirect DropdownSelection / Comments TO STRING original Cell
11 Field2 Vlookup Indirect DropdownSelection / Comments TO STRING original Cell
12 Field3 Vlookup Indirect DropdownSelection / Comments TO STRING original Cell
13 Field4 Vlookup Indirect DropdownSelection / Comments TO STRING original Cell


C10:C13 Are missing

Super Thank you in advanced
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
I'm thinking the questions is to open or not clear enough , but I'll try to make it clearer :)

The scenario is: The workbook has 2 sheets,
Sheet1 - Main user sheet
Sheet2 - Data ( where I pulled the information from )

I have setup a dropdown in Sheet1, when I select the role from it, the information is fetched from Sheet2, that is all set up but and working properly, but I don't have a way to get comments copied/pasted.

Usage scenario

I'm setting up a role based on template, but the template has special notes like

Role 1 has access to Word ( but only editing )
Role 2 has access to Word ( full access )
Role 3 has no access to Word

From the dropdown I select Role 2, but I cant see the comment stating that he has full access.

TL;DR
Is there a way to dynamically update a cell with the comments from another sheet ?

Thank you again !!
 
Upvote 0
In which cell has the dropdown in Sheet1?
In which cell do you want the search result?
In which cell do you want the comment?

Write your vlookup formula here.

You can put a couple of images. Sheet1 and sheet2 where you show the result you need.
 
Upvote 0
In which cell has the dropdown in Sheet1? Sheet Reformatted B9
In which cell do you want the search result? G3:G17 - Simple Yes/Na with the vlookup works like a charm
In which cell do you want the comment? H3:H17

Write your vlookup formula here.

VLOOKUP($B$9,Origin!$C$4:$V$14,4,0)

You can put a couple of images. Sheet1 and sheet2 where you show the result you need.

Im currently updating the Origin sheet to insert manually all the comments , but that is not ideal


MrExcel1125650.jpg
 
Upvote 0
Put the following code in the events of your sheet "Reformatted", every time you change the data in cell B9 automatically cells G3 to H17 will be filled.

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Target.Value = "" Then Exit Sub
  
  If Target.Address(0, 0) = "B9" Then
    Dim sh As Worksheet, f As Range, j As Long, i As Long
    
    i = 3
    Range("G3:H17").ClearContents
    
    Set sh = Sheets("Origen")
    Set f = sh.Range("C4:C14").Find(Target, , xlValues, xlWhole)
    If Not f Is Nothing Then
      For j = Columns("D").Column To Columns("R").Column
        Cells(i, "G").Value = sh.Cells(f.Row, j).Value
        If Not sh.Cells(f.Row, j).Comment Is Nothing Then
          Cells(i, "H").Value = sh.Cells(f.Row, j).Comment.Text
        End If
        i = i + 1
      Next
    End If
  End If
End Sub
 
Upvote 0
Wow that is sooo close of what I needed ( 100 % my fault for not explaining properly )

Can you make it so that when :

B9 is updated in Reformatted it updated G3:G17 with the actual contents ( like the vlookup with YES or N/A)
AND
The same update from B9 copies the comments H3:H17

Effectible I need
Contents in G3:G17
Comments H3:H17


Thank you !!

Trying to figure it out to edit is a long shot for me level lol
 
Upvote 0
The positioning is wrong,

Will test a bit more, and will see if I can upload the file or use the XL2BB ( problem is I cant install things on my pc )
 
Upvote 0
The macro works according to your images.
The macro should put the data in G3 and H3 down.
Or where the macro is putting the data?

For some reason on my original xls it start at G4, but on a new one ( test one ) it works great


So Im reworking my original xls, and retest ( will post the result later today )
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top