I just saved it as a csv file and reopenedit as a csv file, and the blanks
are still showing up as constants. What else can I do?
--
newyorkjoy
thanks for the help!


"Dave Peterson" wrote:

Do you have any formulas in those cells?

Maybe you can save it as a .csv file and then reopen that .csv file?

newyorkjoy wrote:

HELP! I spent almost a day and anight converting the blanks to $$$$$ and
then the $$$$$ back to blanks and guess what? When I look for constants, I
still get the blank cells?! Only the ones that I highlight and then press
DEL show up as blanks. That will be impossible for me to do on such a large
spreadsheet!! What do I do now?
--
newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

If I know I'm converting formulas to values, I'll usually use something like
this:

=if(somethingistrue,"realformula",na())

Then I convert to values and replace the na()'s with nothing.

It saves that first step of converting to $$$$$ then back to nothing.

newyorkjoy wrote:

Right you are! Most of my blank cells were created by inserting values of a
formula that returned "". Since it is such a large spreadsheet, It took the
computer all night to change the blanks to $$$$$. Now I am changing the
$$$$$ back to blanks. I will keep you posted.
--
newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

First, those cells that look empty that are still selected are not empty.

Try selecting a few of those cells manually and then hit the Delete key on the
keyboard to clear the contents. Then test it again. You'll see that these
cells that you just fixed are not in the selection after you used
edit|goto|special|constants.

So if you want to try that technique that I suggested, you're going to have to
really clear those cells.

Try this using an offending cell (I used A1 as my cell).

Find two empty cells and put this into those cells:
=len(a1)
=counta(a1)

If the =len() formula returns 0, but =counta() returns 1, then maybe you had
formulas that evaluated to "" that were converted to values.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

=======
If that doesn't help, post back the results of those two formulas.

newyorkjoy wrote:

The empty cells are absolutely empty. I am using excel 2007, so I went to
the Home tab and then at the far right went to find and select and went to
goto special constants. Then some cells were not selected anymore, but most
of the cells were, including the empty ones. Also, I don't have any row or
column headers, do I just select the entire worksheet? or do I not select
column one that has the dates, and row one that has the names of the
categories?

newyorkjoy
thanks for the help!

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson