Data Validation with an extra entry for "All" in the list

Hi all, I have DV setup on a cell giving the user a list of factories. I want the user to select a factory or have a setting to select ALL. This will tell the formulas whether to add up all orders for just the factory selected or to add them for all of the factories.

I tried =OR("All",Factories!$A:$A) but it returns an error.

This was entered into the list option in DV.

Don't really want to add a row called All to the factory tab nor do I want to use VBA.

Any suggestions greatly appreciated if there is any way to achieve this.

Thanks

Hi HGazFerg,
If your data is organised in a Table, you could simply use a Slicer where the user can select All, One or Some of the factories and get the associated data.

Hi ORoos, thanks for the reply. The slicer gives a filtered view of the data table, the user then needs to select the column to see the sum of the transactions.

I want to keep it more simple for them (they are just basic excel users).

I have another tab showing factory, customer and product, they simply select the item in each drop-down (or All) and the tab has each year in columns, the performance will then show against each category (qyt sold, value sold, etc) based on their selections.

I realise this is just the same as using a filter or slicer or even pivot but want to give then something they can easily view, study and print on one simple view.

I'm thinking of creating a hidden row called All at the start of the factory (plus product & customer) list, was just hoping there was a more scientific formula based solution.

Should you or anyone else have any more ideas please do not hesitate to share.

Thanks

You could use a 'dashboard' style sheet. Basically you start with your user selectin via drop-down box. The options are: All, Factory 1, Factory 2...., next dropdown box...
You then could use SUMIFS formula which would use the values of the drop-down selections as the criteria.

Maybe easier if you could attach a sample workbook (without any confidential data) and a scenario to try a solution.

That's exactly what I want to do except I can't get the word "All" into the drop-down list without adding ghost rows onto the tables.

The attached is a much simpler cut down version of the real file with all names etc changed.

If you select the performance tab you can make the results 'filter' by selecting the various combinations of customer, factory, product.

The invoice detail tab of the real file holds a few thousand records so a simple solution is needed.

I have added in ghost lines in order to make the word ALL appear but was looking to remove them so that confusion is avoided.

Any questions, just ask.

Thanks

Without a ghost line in your data, you could use an Array formula somewhere in your sheet for the Factories, Customers etc.
Leave the top row reserved for 'ALL', then a unique list of all options in your tables.
The data validation then is looking at this list.
In the attached file on the Performance sheet (you can do it somewhere else and hide the sheet), I added the formulas in the shaded area. Note the array formula must be entered by clicking Ctrl+Shift+Enter, this will create the curly brackets
I have also set the array for the formula further down (to row 10) then you have data, hence the last entry is 0.

Hi Oroos, thanks for trying but when I hand over the file they will need to be independent and I can't trust new rows will end up in the array.

Also, I try to avoid arrays as they are so memory hungry.

Attached is the solution I am going to use. What I have done is rename the titles on the respective factory and customer tabs to include the word ALL. I have then range named the list twice, once including row 1 and once without.

The user effectively sees no difference and the drop-downs have the all option included in order for the filter formula on the invoice detail to work.

I still feel Microsoft could have easily made it so that 2 range names could be combined from 2 different locations into one DV list. Maybe in the next version (he says hopefully).

Thanks for your help, just talking these things through really does benefit and your guidance has helped me towards the solution.

If any other options are out there then I'm all ears.

Cheers