Applying custom data validation to multiple columns

[D_N_L]

Hi all,

If I entered this, with column A highlighted, as a custom data validation rule:

Please Login or Register  to view this content.

... then no numbers - on their own - would be accepted anywhere within Column A. However I need to apply this to about 100 columns. Anyway of doing this?

[TMS]

As the range (A1) is a relative address, I would have thought you just needed to select all columns and apply the same DV.


Regards, TMS

[D_N_L]

Hi Trevor,

Hmm - that's what I thought but it isn't working like that. Bummer.

[TMS]

Works fine for me. Selected entire columns A through to CU and applied the Custom Data Validation:

Please Login or Register  to view this content.

Please see the attached example. You can't even type

Please Login or Register  to view this content.

in those columns because it will produce a numeric value.


Regards, TMS

[TMS]

Note: it will get confused if you don't have the whole columns selected. If you started from, say, cell C5 and put in

Please Login or Register  to view this content.

the Data Validation would be offset.

Regards, TMS

[D_N_L]

Hi TMS, sorry for the delay in getting back to you. I'm trying to select Columns A, C, D, F, H etc i.e. columns that aren't joined together.

[TMS]

One way: select column A, then Ctrl-Select columns C, E, G, etc (alternate columns). Then Ctrl-Shift back on column A.

Apply the Custom DV:

Please Login or Register  to view this content.

Alternatively, select column A and apply the Custom DV:

Please Login or Register  to view this content.

Then copy and paste to columns C, E, G, etc.

See the updated example attached .


Regards, TMS

[TMS]

Just re-read your follow up. I thought it was alternate columns so you can forget that. The principle still applies. Select the columns, then go back to column A before applying the DV

Regards, TMS

[D_N_L]

OMG of course I never thought of having to go back to A1 to 'validate' the formula. Jeez. Aint it the simple things!

Thanks very much! : )

[TMS]

You're welcome. Thanks for the rep.