Annoyed by Tools DATA VALIDATION SOURCE

[actjfc]

When I get in to Tools-DATA-VALIDATION-SOURCE, and I want to introduce a formula two things happen:

1) Paste a formula from other area of the workbook is disabled, so I cannot copy and paste a formula inside this SOURCE window.

2) If I try to modify the very long formula that is currently inside of the source and I use arrows to reach out the right side of the formula that that does show, it starts to show +$X:$Y addresses automatically, damaging the formula currently inside.

En conclusion, the issue is how can I built a very long formula inside the windows Tools DATA VALIDATION SOURCE? Both points, 1 and 2, are very annoying. Can somebody point out my mistake and the right procedure to do it?

Thanks!

 

[Jonmo1]

Yes, I agree it's annoying....but...

1. Copy the formula first, THEN go to Data - Validation...

2. Resolving #1 should in turn resolve #2.
Try writing the formula out in a cell first...then copy/paste it from there.

Hope that helps..

 

[shift-del]

Hi

2) If I try to modify the very long formula that is currently inside of the source and I use arrows to reach out the right side of the formula that that does show, it starts to show +$X:$Y addresses automatically, damaging the formula currently inside.

Press F2.

 

[actjfc]

That's my issue. I tested it several times!. If I copy the formula from anywhere and paste to anywhere within the workbook, it works. But when I go inside of the Tools-DATA-VALIDATION-SOURCE the paste feature is somehow disabled. If I do copy and paste just inside the SOURCE it works! But I have not been able to modify the formula as I want, yet. it is too long for this small SOURCE window.

Thanks for any ideas! I am getting hopeless!

 

[actjfc]

I figure it out! My formula is too long. It has a maximum allowed number of letters/numbers, so the paste feature is disabled when I reach that maximum. The F2 trick is so far a must have trick! It solves the issue number 2.

Thanks!