• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula in the corresponding coloumns in a drop down list

B

babyaratrika

New Member
Dear All,

I have a spread sheet named as data sheet consisting of 100 rows containing product list and 25 corresponding coloumns containing product details such as mfg date, batch No, weight, price etc. I would like to prepare a drop down list in a seperate sheet at A1 with the data saved at A1 to A100 with the data sheet. Is there any formula which can be applied at B1 to Z1 in the new sheet so that if i select a product from the drop down list the corresponding coloumns B1 to Z1 will automaticaly pick up the corresponding data from the data sheet. For example if I select a product say PLC from the drop down list the corresponding B1 to Z1 coloumn will pick up the data as saved at data sheet against the product PLC. Similarly if I select product SGO from drop dowm list the corresponding list will pick up data accordingly

Look forward to an affirmative reply from kind hearted members as always.

Thanks & regards
 
Hi,

Welcome to chandoo forum.

First convert your data to table, by selecting in any one cell inside data, press Ctrl+T, select the option my table has header.

Now this table will be named as Table1.

Now on second sheet.

Select column B1, put below formula.

=VLOOKUP(A1,Table1,columns($A1:B1),0)

and copy across.

Regards,
 
Babyaratrika

Firstly, Welcome to the Chandoo.org Forums

can you please post a sample file
 
Hi SM,
It's a great pleasure to have a solution so quickly but I forgot to say that I am using excel 2003 version where Ctrl+T doesn't work. Do you have any solution for that which can be used in 2003 version. However thank u once again.
 
@babyaratrika

Don't convert your data to table then and use the the ref of the range,

like on sheet2 B1 cell put below formula
=VLOOKUP(A1,Sheet1!$A$2:$Y$500,columns($A1:B1),0)

Red part must be your table ref.

Considering row 1 has headers and data starts from row 2 to row 500.

Regards,
 
You must log in or register to reply here.
Back
Top