Auto fill multiple cells depending on single cell value

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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_value_henrat_wks.xls

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