+ Reply to Thread
Results 1 to 6 of 6

Dynamic Named Ranges

  1. #1
    Registered User
    Join Date
    08-23-2014
    Location
    Miami, USA
    MS-Off Ver
    365
    Posts
    69

    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.
    Attached Files Attached Files
    Last edited by sandy1977; 10-04-2018 at 10:35 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Dynamic Named Ranges

    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: copy to clipboard
    Please Login or Register  to view this content.
    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: copy to clipboard
    Please Login or Register  to view this content.
    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: copy to clipboard
    Please Login or Register  to view this content.


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


    I hope this helps.
    Dave

  3. #3
    Registered User
    Join Date
    08-23-2014
    Location
    Miami, USA
    MS-Off Ver
    365
    Posts
    69

    Re: Dynamic Named Ranges

    Excellent Dave, that does the trick for my named ranges.

    Would you know how would I go about adding the values in a "Pay Per Client - Monthly" named range when there is a match in the "Client" named range so that I can calculate the profit per client selected? I was trying to use the SUMIF function:

    =SUMIF(Clients,"*"&H4&"*",PayPerClient) - It would add all employees that are being paid for that working for that client.

    This gives me a "0" value. Is this because my named ranges have text, numbers and blanks?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Dynamic Named Ranges

    There is no range PayPerClient. So I took a guess and made one up.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    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: copy to clipboard
    Please Login or Register  to view this content.


    Then the formula (without wildcards) returns values, but I do not know if they are what you expect.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-23-2014
    Location
    Miami, USA
    MS-Off Ver
    365
    Posts
    69

    Re: Dynamic Named Ranges

    Perfect, thanks Dave!!! Once a gain we've been of great help. I believe my whole problem were the DNRs.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Dynamic Named Ranges

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy values of all named range in wb1 to identically named ranges in wb2
    By JAMIAM in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-24-2016, 06:58 PM
  2. [SOLVED] VBA creates named ranges, but named ranges disappear
    By BrotherNeptune in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-05-2015, 03:22 PM
  3. Define only 2 named ranges from a list of named ranges...
    By abhi900 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-29-2014, 04:20 AM
  4. Looping Mutliple Named Resized Ranges in next empty row below another named range
    By gingumdog in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2014, 08:15 PM
  5. [SOLVED] Determining if the value of a cell can be a named range, then assigning named ranges after
    By Romulo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-15-2013, 06:05 PM
  6. Copy data in named ranges to a newer version of the same template to identical ranges
    By handstand in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2006, 10:51 AM
  7. Replies: 1
    Last Post: 03-21-2006, 06:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1