Conditional data validation

[BremhillBob]

Hi All,

I am one of a group of volunteers who look after a village hall. We have a basic accounts sheet with a column for income and another for expense. We also have a column for 'category'.

The category is to help us group certain types of incomes and expenditures - examples of incomes might be Donations, Bookings, Events (actually precious few!). Details of categories of expense would include Licenses, Utilities, Maintenance, Cleaning etc ... a lot more expenses than incomes!

I have data validation set up so that all items can be given a category and the categories then display the sum of all appropriate values - so I can see at a glance how much is spend on cleaning etc.

The Category column contains the validation and is key to me being able to summarise annual accounts. Currently the data validation list is separate and contains both incomes and expenses. What I'd like is to delete the data validation master list and to use the two summary lists instead. The Category colum would have data validation that depends on whether there was an amount on the 'Income' column or in the 'Expense' column.

SO ... in excel english what I'd like is to set up data validation that says:
If there is no amount in either the income or expense cell for this row error "please enter an income or expense amount first"
if the cell in the income cell for this row has a value then use the validation list "incomelist"
If the cell in the expense cell for this row has a value then use the validation list "expenselist"

I've been having a bit of a look around and found the 'indirect' function that I don't fully understand but I continue to work on that.

https://www.excelforum.com/attachmen...1&d=1617027462

Hoping this wont be too complex but I can't seem to get it at the moment.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[Fluff13]

Greetings from up the road.
How about using this formula in the data validation

Please Login or Register  to view this content.

[BremhillBob]

Hi Fluff ... good to know there is an excel guru close by ...

I noticed a naming error in my list names (ExpenceNames rather than ExpenseNames) now fixed but ....

What you suggest is pretty much what I want and if I type that into a custom validation then it works for the ExpenseNames list but not for the IncomeNames list.

As an ugly workaround I have created an extra column that calculates which list should be used in the validation then my data validation simply says validate by a list = indirect(cell reference - in my case H19).
This kind of works and give me a little error checking on the incomes and expenses entries

https://www.excelforum.com/attachmen...1&d=1617030512

[Fluff13]

I've changed the formula to

Please Login or Register  to view this content.

so that you don't get a list if both Income & Expense are blank, but otherwise it works for me

[BremhillBob]

Perfect. It took me quite a while to sort out - I just couldn't get it to work on my actual sheet even though the 'demo' sheet worked perfectly. Turns out my 'actual' data started on row 18 so I was validating on the next row down. What I call a 'PICNIC' problem...
(Problem In Chair Not In Computer).
Thanks for your help. Happy bunny

[Fluff13]

Glad you sorted it & thanks for the feedback.