Dynamic Named Ranges

Hi guys,

The workbook attached has a table of employees with the corresponding clients they serve.

I have employees working at more than one client and I'm trying to figure out the profit I get after I pay them.

The table attached is just a sample of what I'm working on, but the way it works is that I select the Client (column H) that my employee works at and the "Pay Per Client - Monthly" and the Monthly Invoice" columns update automatically according to the client selected.

My goal is to have the "Profit per Client" and "Profit Percentage" cells for each employee fill out automatically based on the Client selected. The tricky part is that the Profit per Client has to take into account that there are other employees that work for that same client and it should take that amount into consideration when calculating that profit amount. So no matter the employee I'm looking at, I should be able to look at the same client and show the same profit amount.

But here is my problem, I'm having trouble trying to get my named range function created - thanks to Gabhan Berry - ("GreaterThan") to work since my Dynamic Named Range (the "client" column) contains blank rows, and when I try to reference it within a formula it doesn't work. I've also tried a function - created by Jon Acampora - called "FindLast" to be able to create that Dynamic Named Range but I have failed to use it correctly.

So here is an example of what I'm hoping to achieve: if I select client "ABC" for my employee "David Hart", the "Profit Per Client" cell should look for other employees in my Dynamic Named Range: "ClientSelected" - column H, that work for that same client, add those values up and subtract that sum from the Monthly Invoice value in cell C4. The profit percentage value would be easy to calculate once I have that info.

Any help will be greatly appreciated.