Why can't I insert a row in my worksheet?

[susanholland3]

I am trying to insert a row of simple data into my worksheet. When I do, I
get the following message:

"This operation is not allowed. The operation is attempting to shift cells
in a list on your worksheet."

I don't understand this. I have created lists from the worksheet but don't
have any now. Why does it think I have a list on my worksheet? And, if I have
one, how do I remove it?

I couldn't find anything on this in the knowledge base and am very
frustrated. Thanks for any help.

 

[Michael M]

Hi Susan
It sounds like the location your are trying to insert a row has merged cells
in it.
You will need to either unmerge the cells or go 1 row above or below the
merged area.

HTH
Michael

 

[susanholland3]

I don't have any merged cells, at least not that I know of or that I
intended. I have created lists from this worksheet and wonder if it thinks I
still have a list open, but don't know how to correct for that.

I tried locating the row above and below where I need it----it didn't work.
Thanks for trying.

 

[Michael M]

OK
Are the some of the toolbars greyed out ??
If so, I would suggest that you're sheet is protected and needs to be
unprotected.
"The shift cells error " definately appears when there are merged cells

HTH
Michael M

 

[Michael M]

Susan
This can also apply to cells in columns as well

MM

 

[susanholland3]

Well, I hate to be difficult but it's still not working. None of the toolbars
are grayed out. The sheet is not protected (I double-checked) and the cells
are not merged (I double-checked on that, too).

But here's some more information I'm gleaning as I work with this: I have
created lists from the columns of this worksheet. On the column headings
where I have previously created lists, I click on the heading and get the
drop down list--as though the list is still there and part of the worksheet.
I think that is why I can't add a row----a row cuts across those columns that
think they still have lists.

Does that make any sense? I think I need to somehow get rid of the lists or
convince the worksheet that I don't care about that.

Thanks for the efforts so far.....

Susan

 

[susanholland3]

Well, after all that.....I figured it out! I clicked on the columns that were
lists and used the list drop down menu----clicked on "change to range" (I
still don't know what that means). At any rate, the worksheet no longer has
lists attached and I was able to add the row.

Thanks for the help.

 

[SteveW]

Are you able to delete a row ?

Then it must be you lsits.

I've recreated your problem by giving a cell data validation with a list
And using a whole column for that list

Rows can be inserted, deleted - until you put a value in the last row !

Check the list definitions - guess some of them refer to columns

Now in normal case the empty cells of the column will be *empty*
but maybe you filled the column with blanks or or or

Steve

 

[SteveW]

Good,
I posted a reply before I read yours
But I think you must have a later version of Excel than me (2000)
as I don't get any of your List menus, etc etc.

Anyway glad you got it sorted.
Steve

 

[cheryl church]

You may have found one of the quirks about an array formula somewhere in the
range that you are trying to add to.