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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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.
 

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21

ADVERTISEMENT

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?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
 

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21

ADVERTISEMENT

HalfAce,
i did everything you instructed me to but nothing is auto filling??
 

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21
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?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
 

Forum statistics

Threads
1,141,681
Messages
5,707,795
Members
421,528
Latest member
datdude151

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
Top