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 Sheet
called "Values".

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

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

Anyone have any ideas?

Thanks
Henry


--
henrat
------------------------------------------------------------------------
henrat's Profile: http://www.excelforum.com/member.php...o&userid=29001
View this thread: http://www.excelforum.com/showthread...hreadid=487779

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"henrat" wrote in
message ...

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 Sheet
called "Values".

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

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

Anyone have any ideas?

Thanks
Henry


--
henrat
------------------------------------------------------------------------
henrat's Profile:
http://www.excelforum.com/member.php...o&userid=29001
View this thread: http://www.excelforum.com/showthread...hreadid=487779

Sorry, correction to this line
Refers to: =OFFSET('DV-Source'!$A$1,,,COUNTA('DV-Source'!$A:$A))

It should read as:
Refers to: =OFFSET(DV!$A$1,,,COUNTA(DV!$A:$A))

(Forgot to update change made to the sheetname from 'DV-Source' to 'DV')

Here's a new link to the sample construct:
http://www.savefile.com/files/6529637
AutoFill_multiple_cells_depending_on_single_cell_v alue_henrat_wks.xls

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--