Mapping Data

keranali

Board Regular
Joined
Oct 4, 2010
Messages
148
Hi all how do you click on rows to populate columns lets say A2 has a row to text straight to G2 and A3 to G3 and so on, I want to populate column I with the row info, now when I click either A2 row or A3 row the same column changes to reflect the clicked row information.

Please help
thanks
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Haseeb Avarakkan

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
Hi,

This would require VBA. Hope some one will help you.

But, If you can enter your Col A2 values in Col I2, a simple VLOOKUP (hope there is no multiple entries for an item) will give you the output. You can also make drop down list of A:A in I2. Whene ever you change the value in I2, will update the VLOOKUP.
 

keranali

Board Regular
Joined
Oct 4, 2010
Messages
148
Thanks for the reply something like this

Excel Workbook
ABCDGHNO
1BRANCH NAMEADDRESSTEL.FAXEMAILNAMEPOSITION
2ABCDEFE
3F
4
5
6A
7B
8C
9D
Sheet1



however when I click A3 and A4 and so on the same col updates retaining its position.

Thanks
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,329
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try this in a copy of your workbook.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Select various cells in your sheet.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ThisRw <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    LastRw = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">If</SPAN> Intersect(ActiveCell, Range("A2:H" & LastRw)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range("O2:O3,O6:O9").ClearContents<br>    <SPAN style="color:#00007F">Else</SPAN><br>        ThisRw = ActiveCell.Row<br>        Range("O2:O3").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 2).Value)<br>        Range("O6:O9").Value = Application.Transpose(Cells(ThisRw, "A").Resize(, 4).Value)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 

keranali

Board Regular
Joined
Oct 4, 2010
Messages
148
Thanks very much It works amazing however I used the wrong rows and columns
these are what i need

Excel Workbook
BCDEHIOPQRSTUVW
1BRANCH NAMEADDRESSTEL.FAXEMAILNAMEPOSITION
2bcdehi
3
4H
5I
6
7
8B
9C
10D
11E
Sheet1



Thanks a million
 

keranali

Board Regular
Joined
Oct 4, 2010
Messages
148

ADVERTISEMENT

Also column A has to be a part of the formulation but not to display any of its data and cells 6 and 7 has to be blank.

Thanks again
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,329
Office Version
  1. 365
Platform
  1. Windows
Thanks very much It works amazing however I used the wrong rows and columns

Also column A has to be a part of the formulation but not to display any of its data and cells 6 and 7 has to be blank.
Why don't you have a go at modifying the code yourself? :)

If you look at my code in relation to your screenshot in post #3 you should be able to see what needs changing in the code to suit your new ranges.

If you end up not quite getting it to work, post your changed code and I'll certainly lend a hand again. When posting code you can copy and paste it straight into your post but please put Code Tags around it to preserve the indentation formatting. See my signature block below for how to do that.
 

keranali

Board Regular
Joined
Oct 4, 2010
Messages
148
Thanks I have been trying so far

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
Range("w4:w5,w8:w11").ClearContents
Else
ThisRw = ActiveCell.Row
Range("w4:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 2).Value)
Range("w8:w11").Value = Application.Transpose(Cells(ThisRw, "A").Resize(, 4).Value)
End If
End Sub

but i am not getting col I and I dont know how to shift over to Col b instead of A, I am open for Ideas,


thanks
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,329
Office Version
  1. 365
Platform
  1. Windows
Firstly, I need to repeat this:
When posting code you can copy and paste it straight into your post but please put Code Tags around it to preserve the indentation formatting. See my signature block below for how to do that.



Thanks I have been trying so far

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastRw As Long, ThisRw As Long

LastRw = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(ActiveCell, Range("A2:I" & LastRw)) Is Nothing Then
Range("w4:w5,w8:w11").ClearContents
Else
ThisRw = ActiveCell.Row
Range("w4:w5").Value = Application.Transpose(Cells(ThisRw, "G").Resize(, 2).Value)
Range("w8:w11").Value = Application.Transpose(Cells(ThisRw, "A").Resize(, 4).Value)
End If
End Sub

but i am not getting col I and I dont know how to shift over to Col b instead of A, I am open for Ideas,


thanks
OK, looks like you are getting the correct cells cleared when the selection moves out of the 'target' range and you have expanded the 'Target range to include column I correctly. :)

Use the colored text as hints:

You are not getting column I, but you are still getting column G - which you don't want any more.

As I understand it, the value in W8 should not be coming from column A, but from column B.

It may be possible that other changes are also needed, but that should get you very close!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,492
Messages
5,529,176
Members
409,854
Latest member
rickcoba
Top