Data organisation puzzle - creating a bespoke list from 3 criteria - read from a single set of data

Occasional Contributor

Morning All!


A "nearly Merry Xmas" to all members


I am seeking a little help, please.  I have a set of data that identifies "Employees", their "Department" and their delegated financial "Authority".  When a Department seeks to spend some money, it is necessary for someone with adequate (i.e. more than the spend value) financial Authority to approve the spend.


So; taking the inputs "Department" and "spend value", I wish to output a list of "Employees".  Easy, you say - and I agree; however, to make this more complicated, I am trying to achieve this in a single cell that takes Department and spend value from other cells on the same row - each row of the worksheet is an individual Spend Request and there are hundreds.  In addition, there are 2 types of spend to account for (procurement and change), for which Employees have separate levels of Authority (typically 100% and 20% - but this is not a certainty, so Employees have 2 values of Authority).


Method; once the Department (list) is selected, a type of spend identified (procure or change) and a spend value is input, the "Authority" cell needs to offer the user only those options that are applicable.


To give an idea of the data (although not to the scale I need to action), please see the attached.


I would like to thank you for reading this and in advance for any useful solutions that are offered.


Regards, David. 

1 Reply


It should be possible to do this using the FILTER function, but for some reason I couldn't get it to work.

So here is a solution using VBA which should work in all (desktop) versions of Excel. You'll have to allow macros when you open the workbook.