dynamic table

[Forestq]

hi,

in sheet1 I have source table:
[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD]Car[/TD]
[TD]Year[/TD]
[TD]Color[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]2000[/TD]
[TD]Red[/TD]
[TD]Sport[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]2001[/TD]
[TD]Black[/TD]
[TD]Coupe[/TD]
[/TR]
[TR]
[TD]Volvo[/TD]
[TD]1998[/TD]
[TD]Yellow[/TD]
[TD]Combi[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]1996[/TD]
[TD]Black[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Fiat[/TD]
[TD]2003[/TD]
[TD]Red[/TD]
[TD]Family[/TD]
[/TR]
</tbody>[/TABLE]
Then in sheet2 in A1 I will be selecting car name (Audi, BMW etc).

Now if I choose "Audi" I want to in sheet2 in table display all data contains information about audi.

Column "CAR" will be contains ONLY uniqe names (audi, fiat will occur only once).

Example:
A1 = Audi

[TABLE="class: outer_border, width: 200"]
<tbody>[TR]
[TD]Audi[/TD]
[TD]2000[/TD]
[TD]Red[/TD]
[TD]Sport[/TD]
[/TR]
</tbody>[/TABLE]

Do you know what formula I should use ? You can just help me with one (like how to display year). I know that I can you vlookup, but is other way?

 

[MartinS]

Hi
This is how I would do it.
Create a range name for your column titles on sheet 1 (covering Car to Type) (say, ColTitles)
I would then create a validation list using the Car column as the source. In the following columns, I'd use a formula like so:
=VLOOKUP($H2,Sheet1!$A$2:$D$6,MATCH(I$1,ColTitles,0),FALSE)
H2 refers to the cell containing your list of cars to choose from
Sheet1!A2:D6 refers to the range of your table of car details (excluding the title row), and
I1 is the name of the column you want to get details for the chosen car.
So on sheet2, it would look something like this if you chose VW:
Car Year Color Type
VW 1996 Black -
Or this:
Car Year Color Type
BMW 2001 Black Coupe
if you chose BMW
Hope that helps/makes sense
Martin