Auto fill multiple cells depending on single cell value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

henrat - 23 Nov 2005 21:20 GMT

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

Reply to this Message

Max - 28 Nov 2005 02:08 GMT

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
--

> Hi,
>
[quoted text clipped - 21 lines]
> henrat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29001
> View this thread: http://www.excelforum.com/showthread.php?threadid=487779

Reply to this Message

Max - 28 Nov 2005 04:59 GMT

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
--

Reply to this Message