Inserting metal values using data validation

[Michael]

I was wondering what excel tool or function I would use if I wanted to be
able to select a word from a data validation cell, in this case metals for
example

Copper
Aluminium ... etc

Once a metal had been chosen, certain cells in my sheet would take
appropriate preset values.

For example if copper was selected then the cells labelled conductivity,
weight... etc would then change to their set numerical values.

The only way I can think of is using a whole series of IF statements, i.e.
IF cell a1 = Copper, cell value = 0.0000465. But this does not seem like a
very eloquent way of doing it.

Thanks in advance,

Michael

 

[Max]

The only way I can think of is using a whole series of IF statements, i.e.

IF cell a1 = Copper, cell value = 0.0000465. But this does not seem like a
very eloquent way of doing it.

Using either vlookup or index/match would be far easier ways
to extract the details from the reference table

Here's an example file which illustrates it using vlookup:
http://www.savefile.com/files/1434608
DV n vlookup example.xls

The example construct:
Source reference table is assumed in Sheet2, cols A to C, data from row2
down, where col A = Metal, col B = Wt, col C = Conductivity

A defined range, Metal was created via Insert>Name>Define
Names in workbook: Metal
Refers to: =Sheet2!$A$2:$A$4

In Sheet1,
DVs to select the metal in B2 down created via selecting the range (say
B2:B4), then clicking Data > Validation, Allow: List, Source: =Metal

Then in C3, copied across/filled down:
=IF($B2="","",VLOOKUP($B2,Sheet2!$A:$C,COLUMNS($A:A)+1,0))
will return the results for wt & conductivity for the selected metal in col
B from the reference table in Sheet2

---

 

[Michael]

Thanks heaps, thats a great help!

Michael

Using either vlookup or index/match would be far easier ways
to extract the details from the reference table

Here's an example file which illustrates it using vlookup:
http://www.savefile.com/files/1434608
DV n vlookup example.xls

The example construct:
Source reference table is assumed in Sheet2, cols A to C, data from row2
down, where col A = Metal, col B = Wt, col C = Conductivity

A defined range, Metal was created via Insert>Name>Define
Names in workbook: Metal
Refers to: =Sheet2!$A$2:$A$4

In Sheet1,
DVs to select the metal in B2 down created via selecting the range (say
B2:B4), then clicking Data > Validation, Allow: List, Source: =Metal

Then in C3, copied across/filled down:
=IF($B2="","",VLOOKUP($B2,Sheet2!$A:$C,COLUMNS($A:A)+1,0))
will return the results for wt & conductivity for the selected metal in col
B from the reference table in Sheet2

 

[Max]

Thanks heaps, thats a great help!

Welcome. Perhaps you could just take a moment to press the "Yes" button to
the question: "Was this post helpful to you?" from where you're reading this.
It'll ensure a longer shelf life to this thread for the general benefit of
other readers.

---