sorting with a formula

[Guest]

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) ?

 

[Bob Phillips]

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)

 

[Guest]

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]

Did you array-enter it?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Thanks, it now seems to work fine. We were entering the Ctrl-Shift-Enter
after leaving the cell, in order to recalculate the worksheet.

 

[Guest]

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