Select A1:C3 (9 cells)
Notice that you have 8 cells that are shaded and one that is white.

The activecell is that white cell.

Are you sure you correctly selected the starting range?

Are you sure that those cells that look empty are really empty?

newyorkjoy wrote:

Dave, thank you , but when I do edit, go to, special, constants, it selects
the entire worksheet, not just the constants. Also, what is the "activecell"?

--
newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

You could try this against a copy of your worksheet(!).

Select the data range--no row headers, no column headers.

In your test data, I selected B2:D5
Then Edit|goto|special|Constants
(convert any formulas to constants before this step)
Notice that the selection changed to just the cells with something in them

Take note of the activecell's column.
(For me, the activecell was C2
Type this formula in the cell:
=c$1
(change the letter to match the column the activecell is in. But use $1. It
means that the formula will always point to row 1.)

Now hit ctrl-enter (instead of enter)
The selected cells now have a formula that points to row 1 of the column that
it's in.

Select the whole range again (B2:D5) for me
edit|copy
Edit|Paste special|values

With the whole range selected still
edit|goto|special|Blanks
Notice that just the empty cells are selected.

Edit|Delete
Choose shift cells left




newyorkjoy wrote:

I have a very large spreadsheet with the first column showing dates of all
weekdays from 1/2/1900 through the present. the rest of the columns (about
500 of them are different categories. Each date may have one or more of
those categories. Eacch row consists of a date, withabout 500 cells across
which may or may not have data, (Usually a 1, or a -1, or a 0 or a 2 or a
4)depending on which category it is. I need to keep the spreadsheet this
way, but I need something else that will show me on the leftr column the
dates, and the rows across will show only those categories that have data in
it.

This is what my spreadsheet looks like now, in miniature.
DAte Category1 Category 2 Category 3
1/2/00 1
1/3/00 2 0
1/4/00 1
1/5/00 1 1

Th is is what I need to see
Date whichcategory? Which Category? Which Category?
1/2/00 Category 2
1/3/00 Category 1 Category 3
1/4/00 Category 1
1/5/00 Category 2 Category 3

I hope this is clear. Thank you.

--
newyorkjoy
thanks for the help!

--

Dave Peterson


--

Dave Peterson