filling down formats (excel 97)

Topic

New Reply

I have been training 3 people today on Excel 97. We used the data validation feature to display a drop down list. I then got them to autofill, holding down their right mouse button, so we could get the option to fill down the formatting only. This worked find on two pcs but copied nothing on the third. I tried again using the paste special function – again nothing. I tried colouring in the background and the text. This filled down perfectly. These pcs were only ghosted with the software a day or so ago and have only been used for a power point course! I checked every setting I could think of (even closing down excel and reopening it), but could find nothing.

Does anyone have any ideas!?

Thanks

Reply | Quote

Replies

Hi, Roberta.

What result were you trying to achieve? I’m assuming you were trying to replicate the data validation into other cells. If so, I don’t believe that’s possible with autofill>formatting.

Although data validation is considered part of the cell’s properties, I don’t believe it’s formatting per se. Of course a conventional autofill will copy the validation property to the other cells. I always leave the validated cell blank and then do the normal autofill as not to copy any contents.

There’s a few other techniques you can use. A copy, then paste special>validation will work. You can also select from the validated cell down and hit Ctrl+D or Edit>Fill>Down (Ctrl+R I think to go right).

Having said that, I want to point out that version 2002 has in it’s right-click menu, “Fill without Formatting”. This does what you are trying to do. It will fill all cell properties except for the formatting (fill color, font, border, etc.) The feature might be found in version 2000 also (I don’t use that one), but it’s not present in version 97.

-Lenny

Reply | Quote

The strange thing was that it worked ok on the other two pc’s. using either the right click autofill, formats and using paste special. It just ignored me on the third!? strange!

Reply | Quote

Well, the third one was correct.

When you say “paste special” to which command in that feature are you referring? Paste Special>Validation will work every time, but Paste Special>Formats should not do what you want.

Are you able to return to those PC’s and replicate the occurance where AutoFill>Formatting copies data validation into other cells? Or where Paste Special>Formats copies data validation into other cells?

-Lenny

Reply | Quote