Prev: populate month-yyyy when compare 2 cells
Next: Grouping and sorting details
From: TooN on 12 May 2010 05:17
Hello,

I have a question regarding a pull down. I have two worksheets. In the fist
worksheet i have a pull down. In the second worksheet i have about 30 columns
that look like this

Column1 Column2 Column3 ...etc

Name1 Name2 Name3 ... etc
2 3 1
3 1 3
1 5 6
5 3 9
4 8 5
3 4 1
6 8 6
7 9 3
etc etc etc

What i would like to have is that if i select a name in the pull down
(sheet1) it receives the data of the whole column. So when i select name1,
the data under the pull down will be the same as the data that is under name1
in sheet2.

Hopefully i made clear what i want. Any help is appreciated

Thanks
From: Roger Govier on 12 May 2010 06:06
Hi

assuming your dropdown is in cell B1 of Sheet1 then
enter in B2
=INDEX(Sheet2!$A:$AD,ROWS($1:2),MATCH(Sheet1!$B$1,Sheet2!$A$1:$AD$1,0))
Copy down as far as required
--
Regards
Roger Govier

TooN wrote:
> Hello,
>
> I have a question regarding a pull down. I have two worksheets. In the fist
> worksheet i have a pull down. In the second worksheet i have about 30 columns
> that look like this
>
> Column1 Column2 Column3 ...etc
>
> Name1 Name2 Name3 ... etc
> 2 3 1
> 3 1 3
> 1 5 6
> 5 3 9
> 4 8 5
> 3 4 1
> 6 8 6
> 7 9 3
> etc etc etc
>
> What i would like to have is that if i select a name in the pull down
> (sheet1) it receives the data of the whole column. So when i select name1,
> the data under the pull down will be the same as the data that is under name1
> in sheet2.
>
> Hopefully i made clear what i want. Any help is appreciated
>
> Thanks
From: Jacob Skaria on 12 May 2010 06:20
With names in Row1 of Sheet2; and the lookup name in Sheet1 cell A1 apply the
below formula to cell A2 and copy down as required.......Try changing the
names in cell A1.

=IF(OFFSET(Sheet2!$A$1,ROW()-1,MATCH($A$1,Sheet2!$1:$1,0)-1)="","",
OFFSET(Sheet2!$A$1,ROW()-1,MATCH($A$1,Sheet2!$1:$1,0)-1))

--
Jacob (MVP - Excel)


"TooN" wrote:

> Hello,
>
> I have a question regarding a pull down. I have two worksheets. In the fist
> worksheet i have a pull down. In the second worksheet i have about 30 columns
> that look like this
>
> Column1 Column2 Column3 ...etc
>
> Name1 Name2 Name3 ... etc
> 2 3 1
> 3 1 3
> 1 5 6
> 5 3 9
> 4 8 5
> 3 4 1
> 6 8 6
> 7 9 3
> etc etc etc
>
> What i would like to have is that if i select a name in the pull down
> (sheet1) it receives the data of the whole column. So when i select name1,
> the data under the pull down will be the same as the data that is under name1
> in sheet2.
>
> Hopefully i made clear what i want. Any help is appreciated
>
> Thanks
 | 
Pages: 1
Prev: populate month-yyyy when compare 2 cells
Next: Grouping and sorting details