Vlookup and Get comments ( Macro or VBA or magic)

[OrochiNoob]

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

 

[OrochiNoob]

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 !!

 

[DanteAmor]

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.

 

[OrochiNoob]

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

 

[DanteAmor]

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.

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

 

[OrochiNoob]

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

 

[DanteAmor]

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

That makes the macro.

Which data is not putting the macro correctly?

 

[OrochiNoob]

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 )

 

[DanteAmor]

The positioning is wrong,

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?

 

[OrochiNoob]

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 )