allowing a maximum number in a cell which contains a formula and/or reference to other cells / workbooks

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
17
Hi. I am looking for a VBA code what prevents a result of a formula to exceed 98% (the number 98 only will do)
The cells(s) which I want to restrict contain formulas, which will not allow me to use the data function in excel.
the VBA code must return with a msg box telling the user to enter a valid number

Like in the below image - I enter in the Ullage UTI Cell(s) (blue) a certain measurement in mm - it then calculates a correction and gives me a new Final Ullage and in the next cell a percentage. If the final ullage exceeds a maximum allowable number it would result in more than 98% volume loaded in the next cell.
If that is the case, I want the program to stop calculating and show an error message saying something like - "Error! You are overloading the tank. Please enter a valid Ullage"
The actual message is not very important.
I just cannot find any solution because the cell I want to restrict contains a formula which refers to another cell.

Your kind assistance is very much appreciated.



Annotation 2021-03-13 190942.png
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
The cells(s) which I want to restrict contain formulas, which will not allow me to use the data function in excel.
Not true, you can use custom validation to compare the result of formula by using a validation rule like

=$G2<0.98

On the first blue cell (where $G2 refers to the percentage cell for that row).
 

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
17
I need to get less than 98 in the cells row of the percent loaded. That cell contains a reference to the cells row of the final ullage.
I tried your custom formula for a simple addition - in cell D4 e.g. 10 and in cell E4 also 10 (=100) - and in cell F the formula =(D4*E4) and also there the custom data formula is not doing what I need it to do.
Not true, you can use custom validation to compare the result of formula by using a validation rule like

=$G2<0.98

On the first blue cell (where $G2 refers to the percentage cell for that row).
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
If you have 2 cells for manual entry then you need to set the rule on both of them. It works fine for me.
 

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
17

ADVERTISEMENT

That rule works for the blue cell which does not have anything in it already. I am aware.
What I am asking for is to have the limitation on the cells in the row below the percentage - which does contains a formula already and which I cannot use the data rules.
It is why I am asking for a VBA code what can be used instead.
If you have 2 cells for manual entry then you need to set the rule on both of them. It works fine for me
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
Please upload your example with XL2BB so that I can see the formulas and their precedents.
 

sjoerd.bosch

New Member
Joined
Feb 9, 2012
Messages
17

ADVERTISEMENT

Sorry - I cannot access that. I have too many limitations on the work PC. Just only uploading an img is already a struggle.
Also, the program I am using is taking information from several other sheets and not so easy to put in a mini sheet
Basically - I am entering manually a measured number [in the img 1280mm] in the Blue cells, which is giving me a certain volume [not displayed].
Due to some factors [not important to know from where or why] there are corrections applied to give me the corrected or Final Ullage [here 1295mm].
This figure is calculating a volume, which is displayed in the rows below the TOV Volume.
The TOV Volume is a certain percentage of the 100% volume - which is displayed in the cell row below the Percent load [in the img that is 97.7%, and which is still okay.
But should that figure exceed the 98% the cell should display an error and thus stop the calculation and let the user know to correct the input values (blue cell)

As said - I cannot find a way to make that happen. The program just ignores the data restrictions I put in.
I did enter a data restriction in the blue cell at first to restrict the input ullage figure.
because that cell does not contain any formulae and therefore it fine works for that cell. But that is not what I need.

Hope above is clear. You can fill in some bogey numbers and any formula in the cells what calculates the percentage.
Once I have the correct VBA code, I can adjust according to the program. I just need a starting point. VBA is not my strong point - as you must have noticed :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
Is there anything other than the 'Ullage UTI mm' which is entered manually and that can affect the result of the 'Percent Loaded' formula?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
In that case, you do not need vba. The earlier validation selection will work correctly.

To clarify, you need to set the validation rule on the blue cell (Ullage UTI mm) so that the manual entry is validated, but the formula used in the rule needs to look at the cell with the percentage formula (Percent loaded).

If the blue cell is D2 and the percentage is in G2, then you need to apply a validation rule to D2 that uses the formula =$G2<0.98 as a custom validation rule.

This will reject any entry in the Ullage UTI mm column that causes the Percent Loaded formula to exceed 98%.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

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