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

 

[keranali]

Hi Peter I have been trying to work this sheet all day with no luck this is sheet one

Excel Workbook
	A	B	C	D	E	H	I
1	TRACKING NUMBER	BRANCH NAME	ADDRESS	TEL.FAX	EMAIL.WWW	NAME	POSITION
2	1	Gulf City	Couva Shopping Complex, Southern Main Road, Couva	Telephone: (868) 111-1111 [Ex.5555] Fax: (868)111-2222	*	JANE DOE	Account Manager
3	2	BRANCH NAME	ADDRESS	Telephone: (868) 654-1111; Fax: (868) 111-6888	john.doe@gmail.com	John Doe	m.d
4	*	BRANCH NAME	ADDRESS	*	*	*	*
Sheet1

Column B2 and C2 has the data validation lists reading from sheet 2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	BRANCH NAME	ADDRESS	Address 2	Address 3	Address 3	Address 4	Address 5	Address 6	Address 7	Address 8	Address 9	Address 10
2	Arima	Corner Arima	Royal Plaza, Main Road	*	*	*	*	*	*	*	*	*
3	Carlton Centre	St. San Fernando	*	*	*	*	*	*	*	*	*	*
Sheet2

Now Some branches has more than one addresses I need for sheet 1 Col B2 validation list when a branch is selected only that row of data in B2 to L2 will be displayed in col C2 and the others rows below that has the data lists.

Thanks Keran

 

[Peter_SSs]

Could you post your sheets again with ..

1. The results you want (manually entered) in Sheet1.

2. When you are doing your Sheet1 Excel jeanie post, click the 'Data Validation' box in the centre section of the Excel jeanie screen so I can see just what is happening there.

3. With the Sheet2 screen shot, show the corresponding data to Sheet1 so I can see just what data has been transferred from where to where. You can hide some rows before doing Excel jeanie so the data corresponding to Sheet1 shows just below the heading.

 

[keranali]

Hi Peter thanks for your reply I hope this is what you mean

Excel Workbook
	A	B	C
1	TRACKING NUMBER	BRANCH NAME	ADDRESS
2	1	Arima	Corner Pro Streets, Arima
3	2	Carlton Centre	ADDRESS 1
4		BRANCH NAME	ADDRESS
5		BRANCH NAME	ADDRESS
Sheet1

Col B and C has data validation boxes all the way down to row 30

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	BRANCH NAME	ADDRESS 1	Address 2	Address 3	Address 4	Address 5	Address 6	Address 7	Address 8	Address 9	Address 10
2	Arima	Corner Pro Streets, Arima	Johnson Land Street	James Street	Long Island Road
3	Carlton Centre	St. James Street, Carlton road	France Street	Marcia Road Portland	AA Drive South St.	John blvd.	Shepperd gardens	Apple Trace Road	17 Scotland Ave.	Tee Ave. Gopaul street	Jenny Drive
Sheet2

This is sheet 2 where all the data is kept for sheet 1 data validation list. How can I get sheet 1 col C data list to be dependent on what I choose in col B data list eg,
If I choose Arima In the drop down list from sheet 1 Col B, Col C data validation list should only show Corner Pro Streets, Arima Johnson Land Street James Street Long Island Road from that row, and if I should choose Carlton Centre I should only see what there is in b3 to k3
St. James Street, Carlton road France Street Marcia Road Portland AA Drive South St. John blvd. Shepperd gardens Apple Trace Road 17 Scotland Ave. Tee Ave. Gopaul street Jenny Drive

K

 

[Peter_SSs]

How can I get sheet 1 col C data list to be dependent on what I choose in col B data list eg,
If I choose Arima In the drop down list from sheet 1 Col B, Col C data validation list should only show Corner Pro Streets, Arima Johnson Land Street James Street Long Island Road from that row, and if I should choose Carlton Centre I should only see what there is in b3 to k3
St. James Street, Carlton road France Street Marcia Road Portland AA Drive South St. John blvd. Shepperd gardens Apple Trace Road 17 Scotland Ave. Tee Ave. Gopaul street Jenny Drive

Try searching in this forum (and elsewhere) for terms like "dependant data validation". Here's a couple of links to start you off:
http://www.contextures.com/xlDataVal02.html
http://www.mrexcel.com/forum/showthread.php?t=32 (see Aladin's post #4)

 

[keranali]

Hello Peter sorry about the PM, I am using the code you supplied on this thread and it works very well I believe the code some how locks copy and paste features never the less that isn't too necessary, I have the spread sheet and to complete I need help on one more VBA to move data left if the cell is blank example

Excel Workbook
	J	K	L	M
2		abc	def	ghi
3	mno		jkl	868-680-0000
4			pqr
5				stu
6		vwxy34
Sheet1

and when I apply the macro it changes to this

Excel Workbook
	J	K	L	M
2	abc	def	ghi
3	mno	jkl	868-680-0000
4	pqr
5	stu
6	vwxy34
Sheet1

Now the change has to be within M to J then from H to E then D to C, so when the column to the left is blank within the respective columns it moves the data from the right to the left.


Thanks
K

 

[Peter_SSs]

Could you show a 'before' and 'after' screen shot that includes all columns C:M? Just use some small dummy data so that your columns can be fairly narrow to help keep the screen shot size down. 4-5 rows as you did above should be enough.

 

[keranali]

OK thanks for the reply this the before

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
2	jane	pos	dep	uni	addre		addrs	em	12345	111	12345
3	john	pos		uni	addre	adrs		em	12345			12345
Sheet1

and after

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
2	jane	pos	dep	uni	addre	addrs		em	12345	111	12345
3	john	pos	uni		addre	adrs		em	12345	12345
Sheet1

Thanks

 

[Peter_SSs]

Now the change has to be within M to J then from H to E then D to C, ..

Not sure that I have understood this correctly as your last sample doesn't seem to me to quite fit this description. However, I think this should be the basis of what you need.

Test in a copy of your workbook.


<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MoveLeft()<br>    <SPAN style="color:#00007F">Dim</SPAN> Blnks <SPAN style="color:#00007F">As</SPAN> Range, A <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, cols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> ChkCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C:D,G:G,J:L"<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Range("B" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> r = 2 <SPAN style="color:#00007F">To</SPAN> LR<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> Blnks = Intersect(Rows(r), Range(ChkCols)).SpecialCells(xlCellTypeBlanks)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Blnks <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> A <SPAN style="color:#00007F">In</SPAN> Blnks.Areas<br>                cols = A.Columns.Count<br>                A.Offset(, cols + 1).Insert Shift:=xlToRight<br>                A.Delete Shift:=xlToLeft<br>            <SPAN style="color:#00007F">Next</SPAN> A<br>            <SPAN style="color:#00007F">Set</SPAN> Blnks = <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> r<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[keranali]

Hi peter its working excellent the only thing is when I press the code once it moves one step correctly however the moved data creates an empty which was previously occupied the cell that is now empty has to be filled so I press again and its filled can it become like a chain reaction thanks very much again.

K

 

[Peter_SSs]

.. however the moved data creates an empty which was previously occupied ...

That's what your example shows (eg H2, E3) and what I thought your description was saying.

.. the cell that is now empty has to be filled ..

1. With what?

2. I can't see anything about that either in your descriptions or your examples.

.. so I press again and its filled can it become like a chain reaction thanks very much again.

I don't understand this.

Was the before/after sample you posted in post #27 what you really want?

If so,
I think my code does that. I tested it on that 'before' sample data and it produced the 'after' layout. The 'after' layout did not change further if the code was run again.

If not,
please post before/after of what you do really want.