Forum Discussion
EXCEL URGENT HELP!
Skpenu505Here's a way to have the data validation list change depending on the dept number entered, as you requested. It uses named ranges for each departments codes, and then a VLOOKUP function nested in a CHOOSE function to select the appropriate range name.
Let me know if you have questions.
That aside, though, it would help if you filled in one of the recent sheets with a few hypothetical numbers (on pages 2 and 3....and then describe more completely with multiple examples. Use Text boxes on each sheet, perhaps, to indicate which numbers go where.... and then upload that as yet another example.
Skpenu505 This is a bit tricky. I invite people who know Power Query to step in here, as don't think I have that functionality--if I do I haven't found it--on my Excel for Mac, even though I'm fully up-to-date with the software.
I do know how to write queries using Excel's excellent Database functions (DSUM, for example) but not in a way that would be simple to implement. I have an invoicing system my wife uses for her consulting business, and it does what you're trying to do here, but with a much less extensive set of codes that serve the purpose of your Fund Codes here. I use DSUM to get the sum of all billable amounts (you'd be doing it for expenses) in each Code and then I just manually hide rows that are empty.
It's conceivable that a similar query plus a macro could do that for you....but I don't know how to write the macro.
In the meantime, while you wait for a Power Query solution (if there is one), why don't you research DSUM to see if you could craft your own solution. I will tell you that it's definitely possible.