Editing a formula in an Excel Data Valication source box 1

[SUSANVV]

I use formulas in some Data Validation source boxes. They are usually longer that the size of the box itself. To edit the portion not visible I have to use the right arrow key but this enters a cell reference which I then have to delete. I do this until the end of the formula. Is there an easier way to edit or enter those formulas. Or can I make the box longer?
Thanks for any help.

 

[SkipVought]

Hi,

Do the formula in an empty cell on the sheet. Then COPY the formula N PASTE in the DV window.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SUSANVV]

Hi,
Thanks for the reply but on 2010 that doesn't seem to work either in the first dialog or when you open the source window. If you got it to work, please let me know exactly how.

thanks again.

Sue

 

[SkipVought]

BEFORE navigating to Data > Data Validation, you already know what formula you want to construct, so

1) in a cell on the sheet, build the formula where you have plenty of room, especially in the Formula Bar. You can even ENTER the formula (i.e. hit the ENTER key) to determine that it's doing what you intend for it to do.

2) COPY the formula,

3) open the Data Validation wizard in whatever cell you want, and then

4) PASTE the formula into the box that you said is too small to really EDIT a honkin' long formula.

Done it many times.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SUSANVV]

Hi Skip,
Thank you. The mention of the formula bar was the answer. I was copying it from the cell. When I copied it from the formula bar, Voila, Paste was available. Many thanks. have a great day.

 

[SkipVought]

Yes, the Formula Bar is the easiest place to SELECT and COPY a formula or any part of a formula. However, it is also possible to do that in the cell. Notice that you first must SELECT the formula before ctrl+c.

Glad you got things to work.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]