Creating a simple find button

[callmeWILA]

Hi Guys, Please help me with this.

i have a drop down list that contains the products and i have my products separated from other worksheet so as to have a clean main worksheet. i created a button "FIND" so if i already selected a product in my dropbox it will display the prices,cost, etc. and all the information in a designated field. Please help me create one. Im an excel newbie. practicaly i dont know where im going.

 

[Stan_C]

As long as you data is aligned the same...

Excel Workbook
	A	B	C	D	E
1	Sheet 1
2
3	Name	pack	size	cost	each
4	Orange	1	130ct	$ 25.00	$ 0.19
5
6
7	Sheet 2
8
9	Name	pack	size	cost	each
10	Orange	1	130ct	$ 25.00	$ 0.19
11	Banana	1	15#	$ 10.00	N/A
12	Grapes	6	2#	$ 24.00	N/A
13	Apple	1	124ct	$ 32.00	$ 0.26
14	Pear	1	113ct	$ 45.00	$ 0.40
Sheet4

#VALUE!

The formula can be dragged across.

 

[callmeWILA]

vlookup would be nice but can i make it work if the products or items and infos are not in the same sheet? and a button would be nice

 

[Stan_C]

Yes it can work if the data is in another sheet. You do not need a button. If you have a validation list you can select the product and everything will populate once selected. If you post data with sheet names we can help you along more.

 

[L. Howard]

Hi WILA,

Lets make these assumptions about you data and the sheets, which I believe you will be able to adapt as necessary.

-A drop down menu of the products in cell B1 of the 'clean sheet'.
-An exact list (spelling and spaces) of those same products in column C of the 'Products Sheet'.
-The products info is in the same row in cells to the right.
-Some of those products have say, three bits of info and some have up to eight.
-Some have gaps in the row like... info, info, blank, blank, info, blank, info, info.
-For this example we assume a max of eight info bits. (can be more in real life on you sheet, just need to make some adjustments to fit)

On your 'clean sheet' select D1 to K1.
While still selected type in this formula: =VLOOKUP(B1,Sheet2!C1:K5,{2,3,4,5,6,7,8,9},0) (Don't hit Enter!)
While all cells are still selected hold down the ctrl key and the shift key and hit enter.
This is array enter and notice the formula will have {...} around it in the formula window.

Now when you select from your drop down in B1 on the 'clean sheet' eight items will be returned AND where there is a blank in the info row it returns 0.

If you need to make changes to the formula, you must re-select all the cells, make your changes and array enter again.
In this formula you see Sheet2!C1:K5. If your products sheet was named Booger then it should read Booger!C1:K5 in the formula.

Ok, recap what the formuls is doing:
-It looks at B1 on 'clean sheet' then finds the same value in column C of 'product sheet' and returns the 2nd to the 9th columns of info to the right of the value found.

Questions, post back.

Regards,
Howard

 

[callmeWILA]

this will help me alot. thanks to the both of you stan and l. howard.!