How to Auto Fetch row data upon drop down selection in a cell.

**ankit-jain** · 03-07-2013, 08:36 AM

Hi All,

Need your help in solving one excel Query, don't know exactly how to solve it but might require Macros VBA coding.

Query:

I have Excel file which contain 2 sheets ("sheet 1", "sheet 2").

Sheet 1: Contain drop down list in all column A cells reference sheet 2 column A

Sheet 2: Contain more than 10000 rows of data, where Column A contain serial no. (1 to 10000) like 1, 1.1, 1.2,2,2.1....... etc.

what my query here is "When i select from drop down list on cell of sheet 1 Column A then data in that pariticular row of sheet 2 should be displayed in sheet 1 that row all columns. "

Example :

Sheet 2
S.NO. NAME NUMBER ADDRESS
1 Ankit 123 sdf
2 jain 456 sdfsd
3 Ram 789 dsfvdv
4 Prasad 123 gfhbg
5 Mohit 456 mnjhk
6 Dubey 789 uik
7 Prem 789 uik
8 Daga 4561 ryt
9 Sourabh 132456 trg
10 Dhingra 456789 vbn

Sheet 1
S.NO. NAME Address

>>> here S.NO. column contain drop down list in all cell reference Sheet2

When user select 3 in any row on serial no. column than data in sheet 2 i.e. "Ram dsfvdv " should be displayed in sheet 1 in below format

S.NO. NAME Address
3 Ram dsfvdv

please help in solving above query.

Thanks in advance.

**stanleydgromjr** · 03-07-2013, 08:56 AM

ankit-jain,

Welcome to the Excel Forum.

So that we can get it right the first time:

Can we have a workbook, containing Sheet1, and Sheet2, with, say 20 rows of raw data in each?

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

**ankit-jain** · 03-07-2013, 09:21 AM

Hi,

attached file name Book1

where sheet2 contain some records as in required format while sheet1 contain drop down in column 1 reference sheet2 column 1 i.e. S.NO.

Thanks,
ankit

**stanleydgromjr** · 03-07-2013, 05:49 PM

ankit-jain,

Thanks for the workbook.

Open up the workbook.

Click on the Sheet2 tab.

Highlite cells A2:A11, or down column A to the end of your list.

Click in the box above the column A identifier, and type in Sheet2List and press the ENTER key.

Click on the Sheet1 tab.

Copy a blank cell, say cell C2, and paste into cell A2.

Click on Data, Data Validation, Data Validation.

In the Allow: box, click on List

In the Source box enter:
=Sheet2List

And press the OK button.

Copy cell A2 down to A3 for safe keeping.

Click in cell A2, and make a selection from the drop down arrow.

If it works, then copy cell A3 to cell A2. Then copy cell A2 down to the rest of the cells in column A.

Let me know if you are successful.

If you are, then we are done.

If not, then let me know and I will open your workbook, and modify it as above, and send it back to you.

where Column A contain serial no. (1 to 10000) like 1, 1.1, 1.2,2,2.1....... etc.

I am unsure of how long a list data validation will allow.

**stanleydgromjr** · 03-07-2013, 06:27 PM

ankit-jain,

Detach/open workbook DataValidation RangeName VLOOKUP - ankit-jain - EF905097 - SDG12.xlsx, and play with Sheet1 range A2:A11.

There are 3 different VLOOKUP formulae in range B2:D11.