how to name the selected cells in a sheet?

I got some helpful reply from a user Harlan on how to move several columns of
data into one column. But because of my poor excel skill, I am stuck in the
first step, i.e. to name the selected cells as say TB1. Is there somebody who
can help me out? Thanks!


> Landa wrote...
> >Let me illustrate my question in detail: Let say:
> >Row 1 is category. e.g. A1 is Fruit, B1 is Dessert, C1 is Drinks
> >Under each column, there are terms for the categories. E.g.
> >A2: Apple, A3: Orange, A4: Lemon
> >B2: Cake; B3: Chocolate
> >C2: Coffee, C3: Tea
> >What I want to do is to put all the category in Column A, and all the
> >corresponding terms in Column B.
> >i.e.
> >Fruit Apple
> >Fruit Orange
> >Fruit Lemon
> >Dessert Cake
> >Dessert Chocolate
> >Drinks Coffee
> >Drinks Tea
> >Is there anyone who can help me to do this in a faster way? Of course, I can
> >move the terms to the desired boxes manually, but having several hundreds of
> >categories, it's really time-consuming. Thanks a lot!!
>
> For the heck of it, formulas to do this.
>
> If your table in A1:C4,
>
> Fruit____Dessert___Drinks
> Apple___Cake_____Coffee
> Orange__Chocolate_Tea
> Lemon_________________
>
> were named Tbl, and the top-left result cell were A11, try these
> formulas.
>
> A11:
> =INDEX(Tbl,1,1)
>
> B11:
> =INDEX(Tbl,2,1)
>
> A12:
> =IF(COUNTIF(A$11:A11,A11)<COUNTA(INDEX(Tbl,0,MATCH(A11,
> INDEX(Tbl,1,0),0)))-1,A11,INDEX(Tbl,1,MATCH(A11,INDEX(Tbl,1,0),0)+1))
>
> B12:
> =INDEX(Tbl,COUNTIF(A$11:A12,A12)+1,MATCH(A12,INDEX(Tbl,1,0),0))
>
> Select A12:B12 and fill down until the formulas return #REF! .
>

Hi Landa,

One way is to select the cells and go to

Insert -> Name -> Define

Here you can give it a name and click Add and Ok.

Regards,
Bondi

"Landa" wrote:
> I got some helpful reply from a user Harlan on how to move several columns of
> data into one column. But because of my poor excel skill, I am stuck in the
> first step, i.e. to name the selected cells as say TB1

First, a quick correction: .. Harlan is hardly a "user" <g>,
He's a very senior responder / contributor to many newsgroups, an excel
grandmaster / maestro who has posted tens of thousands of responses over the
years!

Ok .. One quick way to define ranges is to use the namebox
(the box with the drop arrow just to the left of the formula bar)

There are 2 defined / named ranges that Harlan describes in his response

One is: Tbl

Select A1:C4, then click inside the namebox
Key-in the name: Tbl, then press ENTER
(Note that the name is spelt: Tbl (with a lowercase "L", not: Tb1)

The 2nd one is : All
Select A1, then click inside the namebox
Key-in the name: All, then press ENTER

The normal way to create defined ranges is via clicking:
Insert > Name > Define
(as pointed by the other responder)

The options are there to create and delete defined ranges

There are also dynamic ranges - defined ranges which expand and contract
automatically - which must be created via this route.

And in case needed,
here's a quick sample implementation of Harlan's response:
http://cjoint.com/?fqj1Uge4fv
Landa_wks.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Landa" wrote:
> I got some helpful reply from a user Harlan on how to move several columns of
> data into one column. But because of my poor excel skill, I am stuck in the
> first step, i.e. to name the selected cells as say TB1. Is there somebody who
> can help me out? Thanks!
>
>
> > Landa wrote...
> > >Let me illustrate my question in detail: Let say:
> > >Row 1 is category. e.g. A1 is Fruit, B1 is Dessert, C1 is Drinks
> > >Under each column, there are terms for the categories. E.g.
> > >A2: Apple, A3: Orange, A4: Lemon
> > >B2: Cake; B3: Chocolate
> > >C2: Coffee, C3: Tea
> > >What I want to do is to put all the category in Column A, and all the
> > >corresponding terms in Column B.
> > >i.e.
> > >Fruit Apple
> > >Fruit Orange
> > >Fruit Lemon
> > >Dessert Cake
> > >Dessert Chocolate
> > >Drinks Coffee
> > >Drinks Tea
> > >Is there anyone who can help me to do this in a faster way? Of course, I can
> > >move the terms to the desired boxes manually, but having several hundreds of
> > >categories, it's really time-consuming. Thanks a lot!!
> >
> > For the heck of it, formulas to do this.
> >
> > If your table in A1:C4,
> >
> > Fruit____Dessert___Drinks
> > Apple___Cake_____Coffee
> > Orange__Chocolate_Tea
> > Lemon_________________
> >
> > were named Tbl, and the top-left result cell were A11, try these
> > formulas.
> >
> > A11:
> > =INDEX(Tbl,1,1)
> >
> > B11:
> > =INDEX(Tbl,2,1)
> >
> > A12:
> > =IF(COUNTIF(A$11:A11,A11)<COUNTA(INDEX(Tbl,0,MATCH(A11,
> > INDEX(Tbl,1,0),0)))-1,A11,INDEX(Tbl,1,MATCH(A11,INDEX(Tbl,1,0),0)+1))
> >
> > B12:
> > =INDEX(Tbl,COUNTIF(A$11:A12,A12)+1,MATCH(A12,INDEX(Tbl,1,0),0))
> >
> > Select A12:B12 and fill down until the formulas return #REF! .
> >
>

Thank you soooo much, Max, Harlan the Excel Grandmaster and Bondi!!!!! You
guys have really helped me save a lot of my time! Now I can go for a coffee
break. Cheers ^0^


"Max" wrote:

> "Landa" wrote:
> > I got some helpful reply from a user Harlan on how to move several columns of
> > data into one column. But because of my poor excel skill, I am stuck in the
> > first step, i.e. to name the selected cells as say TB1
>
> First, a quick correction: .. Harlan is hardly a "user" <g>,
> He's a very senior responder / contributor to many newsgroups, an excel
> grandmaster / maestro who has posted tens of thousands of responses over the
> years!
>
> Ok .. One quick way to define ranges is to use the namebox
> (the box with the drop arrow just to the left of the formula bar)
>
> There are 2 defined / named ranges that Harlan describes in his response
>
> One is: Tbl
>
> Select A1:C4, then click inside the namebox
> Key-in the name: Tbl, then press ENTER
> (Note that the name is spelt: Tbl (with a lowercase "L", not: Tb1)
>
> The 2nd one is : All
> Select A1, then click inside the namebox
> Key-in the name: All, then press ENTER
>
> The normal way to create defined ranges is via clicking:
> Insert > Name > Define
> (as pointed by the other responder)
>
> The options are there to create and delete defined ranges
>
> There are also dynamic ranges - defined ranges which expand and contract
> automatically - which must be created via this route.
>
> And in case needed,
> here's a quick sample implementation of Harlan's response:
> http://cjoint.com/?fqj1Uge4fv
> Landa_wks.xls
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "Landa" wrote:
> > I got some helpful reply from a user Harlan on how to move several columns of
> > data into one column. But because of my poor excel skill, I am stuck in the
> > first step, i.e. to name the selected cells as say TB1. Is there somebody who
> > can help me out? Thanks!
> >
> >
> > > Landa wrote...
> > > >Let me illustrate my question in detail: Let say:
> > > >Row 1 is category. e.g. A1 is Fruit, B1 is Dessert, C1 is Drinks
> > > >Under each column, there are terms for the categories. E.g.
> > > >A2: Apple, A3: Orange, A4: Lemon
> > > >B2: Cake; B3: Chocolate
> > > >C2: Coffee, C3: Tea
> > > >What I want to do is to put all the category in Column A, and all the
> > > >corresponding terms in Column B.
> > > >i.e.
> > > >Fruit Apple
> > > >Fruit Orange
> > > >Fruit Lemon
> > > >Dessert Cake
> > > >Dessert Chocolate
> > > >Drinks Coffee
> > > >Drinks Tea
> > > >Is there anyone who can help me to do this in a faster way? Of course, I can
> > > >move the terms to the desired boxes manually, but having several hundreds of
> > > >categories, it's really time-consuming. Thanks a lot!!
> > >
> > > For the heck of it, formulas to do this.
> > >
> > > If your table in A1:C4,
> > >
> > > Fruit____Dessert___Drinks
> > > Apple___Cake_____Coffee
> > > Orange__Chocolate_Tea
> > > Lemon_________________
> > >
> > > were named Tbl, and the top-left result cell were A11, try these
> > > formulas.
> > >
> > > A11:
> > > =INDEX(Tbl,1,1)
> > >
> > > B11:
> > > =INDEX(Tbl,2,1)
> > >
> > > A12:
> > > =IF(COUNTIF(A$11:A11,A11)<COUNTA(INDEX(Tbl,0,MATCH(A11,
> > > INDEX(Tbl,1,0),0)))-1,A11,INDEX(Tbl,1,MATCH(A11,INDEX(Tbl,1,0),0)+1))
> > >
> > > B12:
> > > =INDEX(Tbl,COUNTIF(A$11:A12,A12)+1,MATCH(A12,INDEX(Tbl,1,0),0))
> > >
> > > Select A12:B12 and fill down until the formulas return #REF! .
> > >
> >

Glad it helped !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Landa" wrote:
> Thank you soooo much, Max, Harlan the Excel Grandmaster and Bondi!!!!! You
> guys have really helped me save a lot of my time! Now I can go for a coffee
> break. Cheers ^0^