Prev: Using SUMIFs on a changing Pivot Table - Help!
Next: Macro Message Bar
From: maniarasan on 4 Jun 2010 08:06
On Jun 4, 4:22 pm, "steve" <steven_gr...(a)hotmail.com> wrote:
> I think I am starting to understand, as you have access to the news groups
> could you try looking in "microsoft.public.excel.programming". I think that
> the thread started by "Michael Hudston" about an hour ago, might answer your
> question.
>
> Regards
>   Steve"maniarasan" <maniarasa...(a)gmail.com> wrote in message
>
> news:ff043664-da88-4355-be5e-fc60ab9b5c63(a)a2g2000prd.googlegroups.com...
> On Jun 4, 3:53 pm, maniarasan <maniarasa...(a)gmail.com> wrote:
>
>
>
>
>
> > On Jun 4, 3:24 pm, "steve" <steven_gr...(a)hotmail.com> wrote:
>
> > > What exactly do you mean by "selected" where does this selection take
> > > place,
> > > and where should the list then show up.
>
> > > Regards
> > > Steve"maniarasan" <maniarasa...(a)gmail.com> wrote in message
>
> > >news:8fc764da-6b4e-4122-bc93-d33a1a3814d7(a)y6g2000pra.googlegroups.com....
>
> > > > State :
> > > > Type1 :
> > > > Type2 :
> > > > Type3 :
>
> > > > Alaska Arizona
> > > > Type1 Alaska1 Type1 Arizona1
> > > > Type2 Alaska2 Type2 Arizona2
> > > > Type3 Alaska3 Type3 Arizona3
>
> > > > Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
> > > > 1.1 2.1 3.1 11.1 12.1 13.1
> > > > 1.2 2.2 3.2 11.2 12.2 13.2
> > > > 1.3 2.3 3.3 11.3 12.3 13.3
> > > > 1.4 2.4 3.4 11.4 12.4
> > > > 1.5 2.5 3.5 11.5 12.5
>
> > > > Above is the worksheet which i'm trying to solve.
> > > > When state "Alaska" is selected, Type 1 should list the values 1.1 -
> > > > 1.5; Type 2 should list the values 2.1 - 2.5, so on
> > > > When State "Arizona" is selected, type 1 should list the values 11.1 -
> > > > 11.5 and so on
>
> > > > Please help me on this.....- Hide quoted text -
>
> > > - Show quoted text -
>
> > The selection will be made from the dropdown list "State", such that
> > the values should be listed in type1, type2, type3 as dropdown list
> > based on state selected.- Hide quoted text -
>
> > - Show quoted text -
>
> I tried with INDIRECT function but it doesn't worked out completely,
> any suggestins would be appreciated- Hide quoted text -
>
> - Show quoted text -

It doesn't works out as i need to list all the three types with the
single selection (i.e. state)
From: Steve Dunn on 4 Jun 2010 08:39
If "State:" is in A1 and your data is in A12:F17, put this in B2:

=INDEX($A$12:$F$17,COLUMN()-COLUMN($B$1)+2,
MATCH($B$1&ROW()-ROW($B$1),$A$12:$F$12,0))

Copy across and and down as far as F4

HTH
Steve D.


"maniarasan" <maniarasan83(a)gmail.com> wrote in message
news:8fc764da-6b4e-4122-bc93-d33a1a3814d7(a)y6g2000pra.googlegroups.com...
> State :
> Type1 :
> Type2 :
> Type3 :
>
> Alaska Arizona
> Type1 Alaska1 Type1 Arizona1
> Type2 Alaska2 Type2 Arizona2
> Type3 Alaska3 Type3 Arizona3
>
>
> Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
> 1.1 2.1 3.1 11.1 12.1 13.1
> 1.2 2.2 3.2 11.2 12.2 13.2
> 1.3 2.3 3.3 11.3 12.3 13.3
> 1.4 2.4 3.4 11.4 12.4
> 1.5 2.5 3.5 11.5 12.5
>
> Above is the worksheet which i'm trying to solve.
> When state "Alaska" is selected, Type 1 should list the values 1.1 -
> 1.5; Type 2 should list the values 2.1 - 2.5, so on
> When State "Arizona" is selected, type 1 should list the values 11.1 -
> 11.5 and so on
>
> Please help me on this.....

From: steve on 4 Jun 2010 09:12
Hi maniarasan,

The answer is easy, but the hard part is the question, could you be more
specific.
Is State: a named range and is that used as data validation in a cell which
you select
And are Type1:, Type2: & Type3: also named ranges which you would like to
have the contents change depending on the selection of State:
Should Type!: etc also form a drop down list in a cell with data
validation.

Could you tell me which cells contain the drop down lists and how you have
(done/want to do) this.

Stick with it we will get there.

Regards
Steve

"maniarasan" <maniarasan83(a)gmail.com> wrote in message
news:d9508d47-94b6-4a8b-8528-a5842434749b(a)y18g2000prn.googlegroups.com...
On Jun 4, 4:22 pm, "steve" <steven_gr...(a)hotmail.com> wrote:
> I think I am starting to understand, as you have access to the news groups
> could you try looking in "microsoft.public.excel.programming". I think
> that
> the thread started by "Michael Hudston" about an hour ago, might answer
> your
> question.
>
> Regards
> Steve"maniarasan" <maniarasa...(a)gmail.com> wrote in message
>
> news:ff043664-da88-4355-be5e-fc60ab9b5c63(a)a2g2000prd.googlegroups.com...
> On Jun 4, 3:53 pm, maniarasan <maniarasa...(a)gmail.com> wrote:
>
>
>
>
>
> > On Jun 4, 3:24 pm, "steve" <steven_gr...(a)hotmail.com> wrote:
>
> > > What exactly do you mean by "selected" where does this selection take
> > > place,
> > > and where should the list then show up.
>
> > > Regards
> > > Steve"maniarasan" <maniarasa...(a)gmail.com> wrote in message
>
> > >news:8fc764da-6b4e-4122-bc93-d33a1a3814d7(a)y6g2000pra.googlegroups.com...
>
> > > > State :
> > > > Type1 :
> > > > Type2 :
> > > > Type3 :
>
> > > > Alaska Arizona
> > > > Type1 Alaska1 Type1 Arizona1
> > > > Type2 Alaska2 Type2 Arizona2
> > > > Type3 Alaska3 Type3 Arizona3
>
> > > > Alaska1 Alaska2 Alaska3 Arizona1 Arizona2 Arizona3
> > > > 1.1 2.1 3.1 11.1 12.1 13.1
> > > > 1.2 2.2 3.2 11.2 12.2 13.2
> > > > 1.3 2.3 3.3 11.3 12.3 13.3
> > > > 1.4 2.4 3.4 11.4 12.4
> > > > 1.5 2.5 3.5 11.5 12.5
>
> > > > Above is the worksheet which i'm trying to solve.
> > > > When state "Alaska" is selected, Type 1 should list the values 1.1 -
> > > > 1.5; Type 2 should list the values 2.1 - 2.5, so on
> > > > When State "Arizona" is selected, type 1 should list the values
> > > > 11.1 -
> > > > 11.5 and so on
>
> > > > Please help me on this.....- Hide quoted text -
>
> > > - Show quoted text -
>
> > The selection will be made from the dropdown list "State", such that
> > the values should be listed in type1, type2, type3 as dropdown list
> > based on state selected.- Hide quoted text -
>
> > - Show quoted text -
>
> I tried with INDIRECT function but it doesn't worked out completely,
> any suggestins would be appreciated- Hide quoted text -
>
> - Show quoted text -

It doesn't works out as i need to list all the three types with the
single selection (i.e. state)


First  |  Prev  | 
Pages: 1 2
Prev: Using SUMIFs on a changing Pivot Table - Help!
Next: Macro Message Bar