DROP DOWN LIST, AUTO FILL

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21
I HAVE TWO SHEETS IN MY BOOK, ONE IS THE MATERIALS LIST SHEET WITH MATERIAL NAMES, COST OF, RETAIL OF, AND PROFIT OF. THE OTHER IS THE "INPUT" SHEET WHERE I HAVE A DROP DOWN LIST IN EACH CELL (A1-A100) POINTING TO THE MATERIAL LIST, BUT ITS ONLY POINTING TO THE MATERIAL LIST NAMES AND NOT COST RETAIL AND PROFIT. HOW CAN I MAKE IT SO WHEN I SELECT, SAY "ROCK" I THEN AUTO FILLS THE COST, RETAIL AND PROFIT ON THE "INPUT" SHEET?
 

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
so what did you try? You might want to post a small sample of your data.

BTW - all caps is very hard to read and is considered shouting.
 
Upvote 0
sorry..
material list sheet has:
(ex.) rock uom cost retail profit

input sheet has a drop down list showing "rock".

i want the input sheet to auto fill uom and retail
(ex.) rock uom retail

am i making sence?
 
Upvote 0
Hello jonnykats, welcome to the board.
A couple things . . .
(1) Please turn off your CAPS LOCK, eh? It makes your message more difficult to read and makes it look like you're shouting at us. :biggrin:
(2) Here's an example of what SteveO59L is suggesting.
On your Materials List sheet, highlight the entire range of material names, costs, retails & profits and name this range. (Insert > Name > Define . . . and name it MaterialsData
Next, highlight just the matarials names (column A?) of that same range and name that range MaterialsNames

Now, in your Input sheet in B1 along side your dropdown menu, enter this formula: =IF($A1="","",VLOOKUP($A1,MaterialsData,2,0))
In C1, enter this one: =IF($A1="","",VLOOKUP($A1,MaterialsData,3,0))
and in C1 enter this one: =IF($A1="","",VLOOKUP($A1,MaterialsData,4,0))
Copy these down columns B:D to the bottom of your list.

Now, when you select one of the material names in column A, the cost, retail & profit data should show up in the columns to the right.
 
Upvote 0
HalfAce,
am i to asume that im entering the formula in b1 because thats where is originaly on the material list sheet? or does it matter?
 
Upvote 0
am i to asume that im entering the formula in b1 because thats where is originaly on the material list sheet? or does it matter?
No, you're entering the formula in B1 because that's where you want the 'autofilled' data to display. It doesn't matter where you enter the formula, enter it where ever you want the return from it to be.

I'm afraid I can't really tell you what's wrong without knowing exactly what was done, where the data really resides and what results you're getting.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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