Mapping Data

[keranali]

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

 

[Haseeb Avarakkan]

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]

Thanks for the reply something like this

Excel Workbook
	A	B	C	D	G	H	N	O
1	BRANCH NAME	ADDRESS	TEL.FAX	EMAIL	NAME	POSITION
2	A	B	C	D	E	F		E
3								F
4
5
6								A
7								B
8								C
9								D
Sheet1

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

Thanks

 

[keranali]

Or B2 c2 d2 column etc the column O updates

 

[Peter_SSs]

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]

Thanks very much It works amazing however I used the wrong rows and columns
these are what i need

Excel Workbook
	B	C	D	E	H	I	O	P	Q	R	S	T	U	V	W
1	BRANCH NAME	ADDRESS	TEL.FAX	EMAIL	NAME	POSITION
2	b	c	d	e	h	i
3
4															H
5															I
6
7
8															B
9															C
10															D
11															E
Sheet1

Thanks a million

 

[keranali]

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]

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]

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]

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!