Dec 23 2021 12:31 AM
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.
Dec 26 2021 03:19 AM
SolutionIt 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.
Dec 26 2021 03:19 AM
SolutionIt 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.