Mapping Data

keranali

Rules Violation
Joined
Oct 4, 2010
Messages
234
Office Version
  1. 365
Platform
  1. Windows
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
 
Hi Peter I have been trying to work this sheet all day with no luck this is sheet one

Excel Workbook
ABCDEHI
1TRACKING NUMBERBRANCH NAMEADDRESSTEL.FAXEMAIL.WWWNAMEPOSITION
21Gulf CityCouva Shopping Complex, Southern Main Road, CouvaTelephone: (868) 111-1111 [Ex.5555] Fax: (868)111-2222*JANE DOEAccount Manager
32BRANCH NAMEADDRESSTelephone: (868) 654-1111; Fax: (868) 111-6888john.doe@gmail.comJohn Doem.d
4*BRANCH NAMEADDRESS****
Sheet1



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

Excel Workbook
ABCDEFGHIJKL
1BRANCH NAMEADDRESSAddress 2Address 3Address 3Address 4Address 5Address 6Address 7Address 8Address 9Address 10
2ArimaCorner ArimaRoyal Plaza, Main Road*********
3Carlton CentreSt. 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
 
Last edited:
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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.
 
Upvote 0
Hi Peter thanks for your reply I hope this is what you mean

Excel Workbook
ABC
1TRACKING NUMBERBRANCH NAMEADDRESS
21ArimaCorner Pro Streets, Arima
32Carlton CentreADDRESS 1
4BRANCH NAMEADDRESS
5BRANCH NAMEADDRESS
Sheet1


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

Excel Workbook
ABCDEFGHIJK
1BRANCH NAMEADDRESS 1Address 2Address 3Address 4Address 5Address 6Address 7Address 8Address 9Address 10
2ArimaCorner Pro Streets, ArimaJohnson Land StreetJames StreetLong Island Road
3Carlton CentreSt. James Street, Carlton roadFrance StreetMarcia Road PortlandAA Drive South St.John blvd.Shepperd gardensApple Trace Road17 Scotland Ave.Tee Ave. Gopaul streetJenny 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
 
Upvote 0
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)
 
Upvote 0
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
JKLM
2abcdefghi
3mnojkl868-680-0000
4pqr
5stu
6vwxy34
Sheet1



and when I apply the macro it changes to this


Excel Workbook
JKLM
2abcdefghi
3mnojkl868-680-0000
4pqr
5stu
6vwxy34
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
 
Upvote 0
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.
 
Upvote 0
OK thanks for the reply this the before



Excel Workbook
BCDEFGHIJKLM
2janeposdepuniaddreaddrsem1234511112345
3johnposuniaddreadrsem1234512345
Sheet1



and after



Excel Workbook
BCDEFGHIJKLM
2janeposdepuniaddreaddrsem1234511112345
3johnposuniaddreadrsem1234512345
Sheet1



Thanks
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
.. 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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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