+ Reply to Thread
Results 1 to 4 of 4

Sorting figures/Codes Dynamically?

Hybrid View

aidan80 Sorting figures/Codes... 02-24-2009, 12:35 PM
incjourn Re: Sorting figures/Codes... 02-24-2009, 01:35 PM
aidan80 Re: Sorting figures/Codes... 02-24-2009, 05:50 PM
incjourn Re: Sorting figures/Codes... 02-25-2009, 06:25 AM
  1. #1
    Registered User
    Join Date
    02-05-2009
    Location
    Florida
    MS-Off Ver
    Excel 2007
    Posts
    50

    Smile Re: Sorting figures/Codes Dynamically?

    Quote Originally Posted by incjourn View Post
    Try using the sumif formula. E.g. =Sumif(yourrange,"JohnVA",yoursumrange)
    You will need to add text together to create your condition.
    e.g. ="John"&"VA"

    Because the columns are offset you just need to use an if statement.
    E.g. If(Sheet1!B3="",B2,Sheet1!B3)


    see attached for example.
    That actually works perfectly! Thanks for your help..

    I can make this work the only issue I have is… I’m doing this for about 200 people. If I understand your example correctly I’d have to create a reference code in sheet 1 for each person e.g. H2 JohnVA, H3 JohnSL.. etc so it’ll reference the lists B and C with H and bounce back with a total in I. If counted correctly I’d have to manually add approx 5,600 codes for this to work for me!! Tell me I’m missing something simple lol

    To be honest a few days work on this putting in those codes would still save more time in the long run. The only issue this creates is say for example john leaves and Jim comes along taking his place additional codes would need to be added for the new employee. I’m trying if I can to remove people from touching this as much as I can as we happen to make mistakes!

    Don’t get me wrong what you did here works perfectly and I may very well use it as it really does dynamically pickup everything. Thanks again!

  2. #2
    Registered User
    Join Date
    03-16-2004
    Location
    UK
    MS-Off Ver
    2003
    Posts
    85

    Re: Sorting figures/Codes Dynamically?

    A few days or a few seconds?

    You can generate the 5600 entries very quickly though. You just need to have the codes in a row and the names in a column then you can use the concatenate formula or the "&" to add the text together to create the Name&Code. Then use the sumif formula =Sumif(criteriarange,"name"&"code",sumrange)
    Then fix the references using $ in the appropriate places and drag the formula across all the cells. This is dynamic so if you want to change a name no problem.

    ...in fact you could do this straight into your "Dump Totals Here" sheet

    see attached

    P.S. If you want to have a master table (in sheet1) you could have a lookup to it in your Dump Totals... use the 'vlookup' or 'index' formula and the 'match' formula for a 2D lookup. e.g. =vlookup("James",A1:Z100,match("OT",A1:Z1,false),false)
    Attached Files Attached Files
    Last edited by incjourn; 02-25-2009 at 06:34 AM.
    Phil

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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