vranjit138


Hi

In Sheet 1 i have columns (A,b,c,d) "Names", "Marks", "Age" and "Location"

i have thrity rows of data for these columns

in sheet 2 i have one column showing the names (thirty rows) - Column A . i would like to populate in column B the data relating to "marks" or "age". The column reference in the vlookup formula should be picked from the list box placed in the excel sheet. The Listbox has been placed from the Control Toolbox.

List box contains values 1,2,3,4. If i pick 2 from the list box column B should populate with Data of Marks, if 3 is selected then Age data should be populated for these thirty rows

Please Help

Thanks in advance

Ranjit




Re: vlookup for a range of cells

Andy Pope


Hi,

If the names on sheet2 are in the same order as on Sheet1 then you could use the INDEX formula rather than VLOOKUP.

Firstly set the LinkedCell property of the listbox control to a spare cell, for example sheet2!c1
The this formula can be entered into B1 and filled down 30 rows.

B1: =INDEX(Sheet1!$B$1:$D$30,ROW(),Sheet2!$C$1)






Re: vlookup for a range of cells

vranjit138

Thanks andy that works

But it is possible in VBA

B1=application.worksheetfunction.vlookup(range("x"),sheets("sheet2").range("b1:d30"),listbox1.text,false)

i would like to draw the reference of the col index from the listbox

the vlookup function work fine for Cell B1 iam unable to fill this function to B2,b3,b4....... upto b30

Please help me

Regards

Ranjit






Re: vlookup for a range of cells

Andy Pope

Try this, checking sheet code names are correct and the listbox is indeed on sheet1

Sub XX()

Dim rngCell As Range

For Each rngCell In Sheet1.Range("A1:A30")
rngCell.Offset(0, 1) = Application.WorksheetFunction.VLookup(rngCell.Value, Sheets("sheet2").Range("A1:d30"), Sheet1.ListBox1.Text, False)
Next
End Sub






Re: vlookup for a range of cells

vranjit138

Great .... it works perfectly

thanks a lot andy

Regards

Ranjit