Datavalidation

[raghavender_g.]

Hi,

I have cell with formula sum(xxx:xx).

This cell should have the datavalidation to not to allow more than 100%.

Can some body help how to achieve this.

Thanks,

 

[JoeMo]

You can't use data validation on a cell with a formula. You could use a worksheet macro though. Here's one you can try. I assume the cell with the sum formula is A10 on this worksheet. Change this in the code to fit your sheet. Also I assume the sum cell and its precedent cells are formatted as %.

To install the code:

1. Open the file to the worksheet with your sum formula
2. Right-click the sheet name tab and choose 'View Code'
3. Copy the code below and paste it into the white space in the VB editor window that has opened.
4. Save the workbook. If you are using Excel 2007 or 2010, you must save it with a .xlsm (not .xlsx) file extension or the code will be removed when you close the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Range("A10").Value > 1 Then
        Dim adr As String
        adr = Range("A10").Formula
        adr = Mid(adr, InStr(1, adr, "(") + 1, InStr(1, adr, ")") - 1 - InStr(1, adr, "("))
        If Not Intersect(Target, Range(adr)) Is Nothing Then
            Range(adr).Select
            MsgBox "The selected range must sum to <= 100%"
            Target.ClearContents
            Target.Select
        End If
    End If
End Sub

 

[raghavender_g.]

Hi, thanks for the reply.

It appears that the formula is correct, but i am getting the following error ....

Error description:

Run-time error '1004':
Method 'Range' of object '_worksheet failed....

Options given "End" and Debug

Please help how to resolve this....

Thanks,

 

[pgc01]

Hi,

I have cell with formula sum(xxx:xx).

This cell should have the datavalidation to not to allow more than 100%.

Can some body help how to achieve this.

Thanks,

Although you cannot use Data Validation on a formula, you can signal that the value is invalid, either

- changing the formula:

if(sum(xxx:xx)<=1,sum(xxx:xx),"Maximum exceeded")

or using Conditional Formatting, for ex. painting the cell with red if the maximum is exceeded.

Would this help?

 

[raghavender_g.]

Thanks for your inputs, this Was helpful.

 

[JoeMo]

Hi, thanks for the reply.

It appears that the formula is correct, but i am getting the following error ....

Error description:

Run-time error '1004':
Method 'Range' of object '_worksheet failed....

Options given "End" and Debug

Please help how to resolve this....

Thanks,

When the error occurs, click on Debug. This will take you to the offending line of code. Copy that line and post it. Also, tell me if the values you are summing are all in sequential cells.