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.

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"

The formula I see for Clients

Formula:

=OFFSET(Sheet2!$B$2,1,0,COUNTA(Sheet2!$B:$B)-1)

would have that drawback with respect to blank rows. Though very popular I've never been a fan of COUNTA constructions in DNRs for that very reason.

Try this alternative for text DNRs.

Formula:

=$B$3:INDEX($B:$B,MATCH("zzzzzz",$B:$B,1))

It will always match on the last row of data even if there are blanks in the range. Please note that the match type is 1 for approximate match.

If you should ever have occasion to need a DNR for numbers

Formula:

=$B$3:INDEX($B:$B,MATCH(1E+306,$B:$B,1))

I've never seen or Googled Jon Acampora's - "FindLast". Perhaps it's the same formula.


I hope this helps.

There is no range PayPerClient. So I took a guess and made one up.

Formula:

=Sheet1!$B$4:INDEX(Sheet1!$B:$B,MATCH(1E+306,Sheet1!$B:$B,1))

It also appears you are trying to use reference to Clients that is in 'Sheet2' when what you want to do is reference the Clients in 'Sheet1' column H. So I took another guess renamed the previous Clients to Clients_Lookup, removed the duplicates(?) in that range and then defined Clients that are in column H 'Sheet1' with

Formula:

=Sheet1!$H$4:INDEX(Sheet1!$H:$H,MATCH(1E+306,Sheet1!$B:$B,1))

Then the formula (without wildcards) returns values, but I do not know if they are what you expect.

Formula:

=SUMIF(Clients,H4,PayPerClient)

You're welcome. Glad to help. Thanks for the feedback and marking your thread Solved.