Inserting metal values using data validation
 

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

Inserting metal values using data validation
 

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 InsertNameDefine
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michael" wrote:
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

Inserting metal values using data validation
 

Thanks heaps, thats a great help!

Michael

"Max" wrote:

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 InsertNameDefine
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Michael" wrote:
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

Inserting metal values using data validation
 

"Michael" wrote:
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---