sorting with a formula

I am a teacher working with EXCEL. I have a list - 3 classes each
alphabetical, but not with final grades. I want a list - 1 list with grade
and then alphabetical listing. I am trying to set this up before the final
grades are list (and subject to change).

The original list has last name (A), first name (B), student ID (C), class 9
thru 12 (D), and I want to put the average in (E).

I am copying CTRL-C and CTRL-V from A-D and moving to G-J and then I tried
in cell K3 the formula =E3 and dragged that down thru the column. When I
sorted G thru K based on J and A and B the K column stayed in the same order
while the other columns sorted. How do I get the K column to 'sort' like
the other (I) columns did?

I tried the idea of putting a '$' on the number part of the formula, but I
know that will not drag down. Instead of =E3, I made it =E$3, and that one
moved but K3 now was still = E3. Is there an easy way to put the dollar sign
on all (the whole column) ?

You could use a formula of

=INDEX($E$2:$E$200,MATCH(J2&K2,$A$2:$A$200&$B$2:$B$200,0))

which is an array formula, committed with Ctrl-Shift-Enter

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"[email protected]" <[email protected]> wrote
in message news:[email protected]...
> I am a teacher working with EXCEL. I have a list - 3 classes each
> alphabetical, but not with final grades. I want a list - 1 list with
grade
> and then alphabetical listing. I am trying to set this up before the
final
> grades are list (and subject to change).
>
> The original list has last name (A), first name (B), student ID (C), class
9
> thru 12 (D), and I want to put the average in (E).
>
> I am copying CTRL-C and CTRL-V from A-D and moving to G-J and then I tried
> in cell K3 the formula =E3 and dragged that down thru the column. When I
> sorted G thru K based on J and A and B the K column stayed in the same
order
> while the other columns sorted. How do I get the K column to 'sort' like
> the other (I) columns did?
>
> I tried the idea of putting a '$' on the number part of the formula, but
I
> know that will not drag down. Instead of =E3, I made it =E$3, and that
one
> moved but K3 now was still = E3. Is there an easy way to put the dollar
sign
> on all (the whole column) ?

I am just getting "#VALUE", and am not getting the cell to change. When I go
to fx at the top it shows the value of 7 as I expected, but it does not show
in the cell. It asks whether it is an array or a reference.

"Bob Phillips" wrote:

> You could use a formula of
>
> =INDEX($E$2:$E$200,MATCH(J2&K2,$A$2:$A$200&$B$2:$B$200,0))
>
> which is an array formula, committed with Ctrl-Shift-Enter
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "[email protected]" <[email protected]> wrote
> in message news:[email protected]...
> > I am a teacher working with EXCEL. I have a list - 3 classes each
> > alphabetical, but not with final grades. I want a list - 1 list with
> grade
> > and then alphabetical listing. I am trying to set this up before the
> final
> > grades are list (and subject to change).
> >
> > The original list has last name (A), first name (B), student ID (C), class
> 9
> > thru 12 (D), and I want to put the average in (E).
> >
> > I am copying CTRL-C and CTRL-V from A-D and moving to G-J and then I tried
> > in cell K3 the formula =E3 and dragged that down thru the column. When I
> > sorted G thru K based on J and A and B the K column stayed in the same
> order
> > while the other columns sorted. How do I get the K column to 'sort' like
> > the other (I) columns did?
> >
> > I tried the idea of putting a '$' on the number part of the formula, but
> I
> > know that will not drag down. Instead of =E3, I made it =E$3, and that
> one
> > moved but K3 now was still = E3. Is there an easy way to put the dollar
> sign
> > on all (the whole column) ?
>
>
>

Did you array-enter it?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"[email protected]" <[email protected]> wrote
in message news:[email protected]...
> I am just getting "#VALUE", and am not getting the cell to change. When I
go
> to fx at the top it shows the value of 7 as I expected, but it does not
show
> in the cell. It asks whether it is an array or a reference.
>
> "Bob Phillips" wrote:
>
> > You could use a formula of
> >
> > =INDEX($E$2:$E$200,MATCH(J2&K2,$A$2:$A$200&$B$2:$B$200,0))
> >
> > which is an array formula, committed with Ctrl-Shift-Enter
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "[email protected]" <[email protected]>
wrote
> > in message news:[email protected]...
> > > I am a teacher working with EXCEL. I have a list - 3 classes each
> > > alphabetical, but not with final grades. I want a list - 1 list with
> > grade
> > > and then alphabetical listing. I am trying to set this up before the
> > final
> > > grades are list (and subject to change).
> > >
> > > The original list has last name (A), first name (B), student ID (C),
class
> > 9
> > > thru 12 (D), and I want to put the average in (E).
> > >
> > > I am copying CTRL-C and CTRL-V from A-D and moving to G-J and then I
tried
> > > in cell K3 the formula =E3 and dragged that down thru the column.
When I
> > > sorted G thru K based on J and A and B the K column stayed in the same
> > order
> > > while the other columns sorted. How do I get the K column to 'sort'
like
> > > the other (I) columns did?
> > >
> > > I tried the idea of putting a '$' on the number part of the formula,
but
> > I
> > > know that will not drag down. Instead of =E3, I made it =E$3, and
that
> > one
> > > moved but K3 now was still = E3. Is there an easy way to put the
dollar
> > sign
> > > on all (the whole column) ?
> >
> >
> >

what do you mean array-enter it? How do you do this?

"Bob Phillips" wrote:

> Did you array-enter it?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "[email protected]" <[email protected]> wrote
> in message news:[email protected]...
> > I am just getting "#VALUE", and am not getting the cell to change. When I
> go
> > to fx at the top it shows the value of 7 as I expected, but it does not
> show
> > in the cell. It asks whether it is an array or a reference.
> >
> > "Bob Phillips" wrote:
> >
> > > You could use a formula of
> > >
> > > =INDEX($E$2:$E$200,MATCH(J2&K2,$A$2:$A$200&$B$2:$B$200,0))
> > >
> > > which is an array formula, committed with Ctrl-Shift-Enter
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "[email protected]" <[email protected]>
> wrote
> > > in message news:[email protected]...
> > > > I am a teacher working with EXCEL. I have a list - 3 classes each
> > > > alphabetical, but not with final grades. I want a list - 1 list with
> > > grade
> > > > and then alphabetical listing. I am trying to set this up before the
> > > final
> > > > grades are list (and subject to change).
> > > >
> > > > The original list has last name (A), first name (B), student ID (C),
> class
> > > 9
> > > > thru 12 (D), and I want to put the average in (E).
> > > >
> > > > I am copying CTRL-C and CTRL-V from A-D and moving to G-J and then I
> tried
> > > > in cell K3 the formula =E3 and dragged that down thru the column.
> When I
> > > > sorted G thru K based on J and A and B the K column stayed in the same
> > > order
> > > > while the other columns sorted. How do I get the K column to 'sort'
> like
> > > > the other (I) columns did?
> > > >
> > > > I tried the idea of putting a '$' on the number part of the formula,
> but
> > > I
> > > > know that will not drag down. Instead of =E3, I made it =E$3, and
> that
> > > one
> > > > moved but K3 now was still = E3. Is there an easy way to put the
> dollar
> > > sign
> > > > on all (the whole column) ?
> > >
> > >
> > >
>
>
>

When you type the formula in, don't press Enter, but Ctrl-Shift-Enter
together. If successful, Excel inserts {..} around the formula in the
formula bar. When you re-edit, you commit in the same way.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"[email protected]" <[email protected]> wrote
in message news:[email protected]...
> what do you mean array-enter it? How do you do this?
>
> "Bob Phillips" wrote:
>
> > Did you array-enter it?
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "[email protected]" <[email protected]>
wrote
> > in message news:[email protected]...
> > > I am just getting "#VALUE", and am not getting the cell to change.
When I
> > go
> > > to fx at the top it shows the value of 7 as I expected, but it does
not
> > show
> > > in the cell. It asks whether it is an array or a reference.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > You could use a formula of
> > > >
> > > > =INDEX($E$2:$E$200,MATCH(J2&K2,$A$2:$A$200&$B$2:$B$200,0))
> > > >
> > > > which is an array formula, committed with Ctrl-Shift-Enter
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "[email protected]"
<[email protected]>
> > wrote
> > > > in message
news:[email protected]...
> > > > > I am a teacher working with EXCEL. I have a list - 3 classes each
> > > > > alphabetical, but not with final grades. I want a list - 1 list
with
> > > > grade
> > > > > and then alphabetical listing. I am trying to set this up before
the
> > > > final
> > > > > grades are list (and subject to change).
> > > > >
> > > > > The original list has last name (A), first name (B), student ID
(C),
> > class
> > > > 9
> > > > > thru 12 (D), and I want to put the average in (E).
> > > > >
> > > > > I am copying CTRL-C and CTRL-V from A-D and moving to G-J and then
I
> > tried
> > > > > in cell K3 the formula =E3 and dragged that down thru the column.
> > When I
> > > > > sorted G thru K based on J and A and B the K column stayed in the
same
> > > > order
> > > > > while the other columns sorted. How do I get the K column to
'sort'
> > like
> > > > > the other (I) columns did?
> > > > >
> > > > > I tried the idea of putting a '$' on the number part of the
formula,
> > but
> > > > I
> > > > > know that will not drag down. Instead of =E3, I made it =E$3, and
> > that
> > > > one
> > > > > moved but K3 now was still = E3. Is there an easy way to put the
> > dollar
> > > > sign
> > > > > on all (the whole column) ?
> > > >
> > > >
> > > >
> >
> >
> >

You can copy and past entire rows or columns by clicking on the column itself
or row itself. Also, if you want to just look at one column, you can hide
the other columns.

Also, you can put the same information on different Sheets, rather than
putting them side by side.

When sorting, be sure you are not hiding any columns or rows, it will not
sort correctly. Also be sure each column has a heading before sorting, then
when you click on sort, select column header.

$ makes the column or row "absolute". If you put $E3 then the column will
stay the same but the row will change when your copy it. If you put E$3, the
row will change, but the colum will stay the same. If you put $E$3, then the
data will stay the same no matter where you copy it.

Lucybocca

"[email protected]" wrote:

> I am a teacher working with EXCEL. I have a list - 3 classes each
> alphabetical, but not with final grades. I want a list - 1 list with grade
> and then alphabetical listing. I am trying to set this up before the final
> grades are list (and subject to change).
>
> The original list has last name (A), first name (B), student ID (C), class 9
> thru 12 (D), and I want to put the average in (E).
>
> I am copying CTRL-C and CTRL-V from A-D and moving to G-J and then I tried
> in cell K3 the formula =E3 and dragged that down thru the column. When I
> sorted G thru K based on J and A and B the K column stayed in the same order
> while the other columns sorted. How do I get the K column to 'sort' like
> the other (I) columns did?
>
> I tried the idea of putting a '$' on the number part of the formula, but I
> know that will not drag down. Instead of =E3, I made it =E$3, and that one
> moved but K3 now was still = E3. Is there an easy way to put the dollar sign
> on all (the whole column) ?