Data Validation in Excel used in Format Range

[Former Member]

Hi Experts,

Im just wondering if there's a way i can use format range to carry out the data validation of excel.

i mean, i need to limit the users to enter only numberic data to the input cells, hence they cannot input any letter and negative values.

i tried applying the data validation in format range but, its not spreading the data validation.

if i apply data validation to every cell, when i expand, the data validation disappears.

Any advise.

Thanks much as always...

[Former Member · Accepted solution]

Hi,

I was not able to use Data Validation in FORMAT RANGE.

If you want to apply this data validation for the whole data range, you can apply this data validation in the first cell of the first row,first column of evdre. It will apply to the whole data range.

If you want to apply this for certain columns, its a bit tricky.

You must break your columnKeyRange into 2 and insert a column in between. In the 1st columnkeyrange, get the data, hide this column alone and add this column in getonlyrange. Next, in the newly inserted columns between the 2 column key ranges, you can use your Data Validation in 1st row. While expanding, it will automatically copy the data validation to the remaining rows. Now, In the second columnkeyrange, get the value based on the data validation cell, hide this column alone. So now you will have 3 columns, with those in the column key ranges as hidden ones and the one in between will be shown to the users.

I know, this workaround is a bit tricky, but it works flawlessly. I have used this for umpteen sheets.

Karthik AJ

[Former Member · Accepted solution]

Hi,

Thanks for the reply, i wanted to apply the data validation all throughout the data range and not for some columns.

The data validation applied to the first cell of row and column was deleted when i tried to expand and refresh, this is because i think i enabled the format range. So instead of copying down the data validation, it was deleted.

One might disabled the format range so data validation can be applied, but the problem is that i need format range to be active at the same time the data validation. I guess the format range clears data validation when expanded.

Is there any workaround about this that leaves format range and data validation active? I really just wanted to prevent the user to enter letters in the cell input.

Thanks.

[Former Member · Accepted solution]

Hi,

Can you please tell us which version are you using?

I am able to use both data validation and the FORMAT RANGE in the same approach i have described earlier in 7.5MS for the whole sheet.

Karthik AJ

[Former Member · Accepted solution]

Or,

Use a Conditional Formatting in the FORMAT RANGE to apply to all data cells.

If the users enters something other than what you want them to enter, use different color code to warn them about it.

That might alert the user of invalid entries.

Karthik AJ

[Former Member · Accepted solution]

Hi,

Thanks for the replies, It seems data validation is spreading through out the data range. and was able to restrict the users to input letters and negative values.

I have now two options, its either use data validation to every first row and first col in data range. (i used pipe mostly so i need to apply data validation every pipe) and the other is to use conditional formatting to warn the user f they input incorrect data.( though i haven't tried the conditional formating in format range )

ciao.