Annoyed by Tools DATA VALIDATION SOURCE

actjfc

Active Member
Joined
Jun 28, 2003
Messages
412
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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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..
 
Last edited:

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
516
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 28, 2003
Messages
412
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

Active Member
Joined
Jun 28, 2003
Messages
412
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,536
Messages
5,596,748
Members
414,095
Latest member
George53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top