Auto fill multiple cells depending on single cell value

  • Thread starter Thread starter henrat
  • Start date Start date
H

henrat

Hi,

I have a table which lists many different materials along the top row
The six cells below each material name contain different number values
These are different for each material. These are all stored in a Shee
called "Values".

I would like to have a cell on another sheet which, when a certai
material name is typed in (or selected fom a dropdown menu if it
possbile?), would automatically fill in 6 cells below it with th
relevant data from the "Values" sheet.

This is just to prevent an error in copying out a potentially larg
amount of data.

Anyone have any ideas?

Thanks
Henr
 
One way to accomplish it ..

Sample construct to play with available at:
http://www.savefile.com/files/7311540
AutoFill multiple cells depending on single cell value_henrat_wks.xls

Assume the source table is in sheet: MT,
in cols A to J, labels in row 1, data in rows 2-6, viz.:

Material1 Material2 Material3, etc
1553 1459 1216
1844 1089 1306
1558 1509 1232
1100 1162 1972
1209 1390 1588
1891 1619 1352

In a sheet: DV
------------------
List the materials in A1 down: Material1 Material2 Material3, etc
Create a dynamic range for the list in col A via:

Click Insert > Name > Define
Names in workbook: MatList
Refers to: =OFFSET('DV-Source'!$A$1,,,COUNTA('DV-Source'!$A:$A))
Click OK

Then in say, Sheet1
--------------------------
Create the DV droplists in row 1 (A1:IV1)

Select row1
Click Data > Validation
Allow: List
Source: =MatList
Click OK

Set-up a multi-cell array formula to extract the details
in the 6 rows below the material selected in row1

Select A2:A7
Put in the formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=IF(A$1="","",
OFFSET(MT!$A$2:$A$7,,MATCH(A$1,MT!1:1,0)-1,))
Copy A2:A7 across as desired

The 6 cells of data for the material selected in row1 will be auto-returned
 
Back
Top