Data Validation - All cells adjacent to matching description must equal 100

Hi all,

I have a spreadsheet where there are a list of asset classes i.e. Fixed Interest and Equities. Each one of these must total am amount in a table. Is there a way to get data validation to make sure that all the cells equal the result in the table rather than everytime you make a change and it doesnt add up, you get an error and cant move forward? The attached book should explain what I am trying to do a lot clearer.

Many thanks in advance!!

Alex

Originally Posted by simmo86

Is there a way to get data validation to make sure that all the cells equal the result in the table rather than everytime you make a change and it doesnt add up, you get an error and cant move forward?

It's not clear what user experience you are trying to establish. What you just described is exactly how data validation works--every time you make a change, the result is validated, and if it does not pass you cannot move forward. Are you saying something like you want the user to fill out all the yellow cells first then determine if the total matches the desired total of 5.5? If so it would probably be better to use a macro. If you fill in all the yellow cells, and the total is not 5.5, then the error will occur on the last cell you enter and it will be difficult to make the necessary updates to get the total correct. If you use a macro, then you can just give an error message and the user can easily keep juggling numbers. I have attached an example. It will check the sum once all yellow cells are filled in.