+ Reply to Thread
Results 1 to 11 of 11

Index/match sort issue

  1. #1
    Registered User
    Join Date
    10-26-2014
    Location
    Leavenworth, WA
    MS-Off Ver
    2010
    Posts
    82

    Index/match sort issue

    Hi
    I have reviewed the posts involving the index/match and sort difficulties.
    I have tried the forum suggestions, but it's not working for my spreadsheet which has to be sorted continually.
    I have attached the spreadsheet for review.
    The formula in question is in COLUMN M, highlighted in pink.
    It is referencing another page that is only giving back a base salary, that is tab is BUILDCONTRACTS.
    I didn't include that tab because it has way too much sensitive information.
    In COL D of the attached spreadsheet I deleted sensitive name information, so there's just a number there due to referencing issues.
    This is the formula
    =INDEX(BuildContracts!$F$8:$F$173,MATCH(BuildAssignments!E79,BuildContracts!$A$8:$A$173,0))/210*16*0.4+(3000*0.4)

    Thank you so much,
    Jules
    Last edited by julesmctavish; 06-01-2016 at 05:44 PM. Reason: Added extra spreadsheet

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    881

    Re: Index/match sort issue

    Jules,

    Your formula has missed out a few apostrophes. Try the attached:

    =INDEX('Build Contracts.xls'!$F$8:$F$173,MATCH(E79,'Build Contracts.xls'!$A$8:$A$173,0))/210*16*0.4+(3000*0.4)

    (You don't need "BuildAssignments!" for E79, because the formula is on that page already)

    Hope this helps

    Ochimus
    Last edited by Ochimus; 05-31-2016 at 08:52 PM.

  3. #3
    Registered User
    Join Date
    10-26-2014
    Location
    Leavenworth, WA
    MS-Off Ver
    2010
    Posts
    82

    Re: Index/match sort issue

    When I input the formula with the ' I get a message that says I need to file values. I've never gotten that message before.
    So, it won't allow me to even input it in the spreadsheet.
    I tried just putting F:F and A:A, so when I sorted no matter if the cell referenced E79 or E144 or whatever it would located the number I needed....and it won't do that.
    Will the apostrophe make make it so it will reference the correct number no matter what when I sort?

  4. #4
    Registered User
    Join Date
    10-26-2014
    Location
    Leavenworth, WA
    MS-Off Ver
    2010
    Posts
    82

    Re: Index/match sort issue

    This formula still doesn't work and I don't know why.
    Cell E79, in the first example is great when I first enter my info, however, when I sort, E79 becomes something else and I get and#NA reference.
    How can I keep the original data?

  5. #5
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    881

    Re: Index/match sort issue

    Jules,

    Not sure what you mean by "original" data?

    If you can explain which column you "sort", and what should appear in Col M when you do, then hopefully we can sort this out.

    Because whether you use Index/Match or VLOOKUP, the only reason your formulae should generate a "N/A" in Col M is if there is no match in Build Contracts A8:A173 for whatever is in Col E of the Build Assignments sheet, or there is no value in Build Contracts F8:F173 against the row.

    The first problem is that in your attachment most of column M has no formula.

    Some are a fixed number (e.g. M14 is 616) so will not change if you resort other columns.

    Some are multiplying a fixed number (e.g. M43 is 1211*1.03), and again will not change if you resort other columns

    Others are matching a value from a fixed range in Col F of the "BuildContracts' sheet, so they will change if you resort Col E.

    But you then divide the number by widely different formulae:
    M62 divides it by 180*6
    M79 divides it by 210*16*0.4+(3000*0.4)
    M80 divides it by 210*16*0.15+(3000*0.15)
    M113 divides it by 210*16+3000


    Ochimus
    Last edited by Ochimus; 06-01-2016 at 05:38 PM.

  6. #6
    Registered User
    Join Date
    10-26-2014
    Location
    Leavenworth, WA
    MS-Off Ver
    2010
    Posts
    82

    Re: Index/match sort issue

    Hi Ochimus;
    So, the ones that are highlighted in pink in column M with the odd dividers and multipliers were the ones that were giving me trouble.
    The original spreadsheet about 60 tabs. They all interconnect somehow.
    The Build Contracts tab has a base salary, say, 105,000.
    210=DAYS TOTAL IN CONTRACT
    16=EXTRA DAYS
    .4=40%
    3000=extra supplemental pay (some get the whole amount, others is based on a percentage
    What should appear in column M is the calculation.
    I would send you another attachment with the BUILD CONTRACT info...but I am not seeing where it will let me attach another spreadsheet.

  7. #7
    Registered User
    Join Date
    10-26-2014
    Location
    Leavenworth, WA
    MS-Off Ver
    2010
    Posts
    82

    Re: Index/match sort issue

    I added the BuildContracts to the original post.
    THANK YOU for looking at this with me.

  8. #8
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    881

    Re: Index/match sort issue

    Still not really clear on this.

    1. What are the Column headings in the Build Contracts sheet?

    2. Col E on the BuildAssignment sheet contains 325 "unique" serial numbers.
    Why is your formula looking for only 165 numbers in the Build Contracts sheet? (Your formula says it searches rows 8 - 173 )?
    Why are there only 11 in your Build Contracts atttachment?
    Those 11 serials are in rows 2 - 13, which means your formula is not looking at anything in rows 2 - 7, and will not find any data in rows 14 - 173, because they are blank on your attachment.

    3. What column on the BuildAssignment sheet do you use to "sort" the data?

    4. Does the name in Col D of the Build Assignments determine whether Col M gets the entire supplemental pay, or just a certain percentage?

    Ochimus
    Last edited by Ochimus; 06-02-2016 at 08:57 AM.

  9. #9
    Registered User
    Join Date
    10-26-2014
    Location
    Leavenworth, WA
    MS-Off Ver
    2010
    Posts
    82

    Re: Index/match sort issue

    Hi Ochimus;
    This spreadsheet is twenty years old and was built way beyond my capabilities. The orignal spreadsheet is huge.
    1. The column headings in Build Contracts tab correlate to the multiple tabs in some way or fashion.

    2. I am only trying to pull 11 people's base salary from the build contracts page because not every employee in the organization gets the supplemental pay. There are hundreds of employees. Eleven employees work for different departments, different accounting codes, get different percentages of the BASE PAY from the BUILD CONTRACTS page. This page then feeds to BUILD ASSIGNMENTS and breaks the Base into smaller pieces based on which account code the salary is expensed.

    3. There is a button at the top, a macro that sorts the data and verifies that the formulas are working.

    4. The Serial on Build Assignments is what it should look for in Contracts. It's the unique identifier.

    I tried to send a cut down version of the spreadsheet, but even after I trimmed it down, it was too large.

    What I ended up doing was referencing the actual cell in the Build Contracts, so my formula was =BuildContracts!$F$52/210*16*.40+(3000.40)
    This keeps the formula in the Build Assignments tab from giving an error.
    What I had hoped to do was to use the formula in other applications as well as this instance because of the constant references between sheets. VLOOKUPS don't seem to work either with a sort in this instance.
    I am wondering if the spreadsheet is just too big? It works great with the index formula UNTIL I sort. I was thinking of trying to put the name serial number in " ", but that didn't work either.

    Thank you for taking the time to look at this.

    Jules

  10. #10
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    881

    Re: Index/match sort issue

    Jules,

    Appreciate the much clearer explanation.

    Hopefully the attached sheet will show the answer to your problem.

    "Build Contract" sheet

    Col A contains eleven serial numbers from your list.

    Cols B - E show their different Base Salaries (as I'm not clear whether everyone has the same in your file or they are individual?), and up to three allowances. You can see some get all the allowances, some get two, and some get only one.

    Cols G - K then break down the percentages of their pay between four Cost Codes.

    "Build Assignments" sheet:

    Col F contains 327 Serials. To "mimic" your sort issue, the order they appear changes every time you highlight any cell on the sheet and press "Copy". (Uses an Index/Link to the Random Generator sheet)

    If the Serial matches one of your eleven, then the Base Salary will appear in Col M under the following formula starting in row 3 and copied down:

    =IFERROR(INDEX('Build Contracts'!$B$3:$B$13,MATCH(E3,'Build Contracts'!$A$3:$A$13,0)),"")

    (If the serial in Col E does not match any of the values in A3 - A13 in the Build Contracts sheet, then Col M stays blank,
    If it does match, then find the matching value in B3 - B13 of the Build Contracts sheet and copy it into Col M.)

    If there is a match, it then adds the Allowances into Cols N - P under this formula, starting in row 3 and copied across and down:

    =IFERROR(INDEX('Build Contracts'!$C$3:$C$13,MATCH(E3,'Build Contracts'!$A$3:$A$13,0))*M3,"")

    (If the serial in Col E does not match any of the values in B3 - B13 in the Build Contracts sheet, then Col N stays blank,
    If it does match, then divide the salary in Col M by the value from Col C of the Build Contracts sheet.
    If the allowance does not apply to that serial, the cell stays blank.)

    Cols R - V then break down the Base Salary + any allowances between the four Cost Centres by the values you set in the Build Contract sheet using the following formula copied across and down:

    =IFERROR(INDEX('Build Contracts'!$G$3:$G$13,MATCH(E3,'Build Contracts'!$A$3:$A$13,0))/100*SUM(M3:P3),"")

    (If the serial in Col E does not match any of the values in B3 - B13 in the Build Contracts sheet, then Col R stays blank,
    If it does match, then multiply the combined salary and allowances in Cols M - P by whatever percentage you had in Col G of the Build Contracts sheet.

    Hope you use that and "adapt" it to your actual filepaths and ranges.

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 06-02-2016 at 03:19 PM.

  11. #11
    Registered User
    Join Date
    10-26-2014
    Location
    Leavenworth, WA
    MS-Off Ver
    2010
    Posts
    82

    Re: Index/match sort issue

    WOW....that was amazing.
    I am actually going to print this off and use it for other areas of the spreadsheet too.
    That was a lot of work.
    THANK YOU SO MUCH!!

+ 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. Issue: Only returning 1st match on Index/Match
    By tbr2891 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2014, 11:54 AM
  2. [SOLVED] index match issue
    By mamig in forum Excel General
    Replies: 2
    Last Post: 09-15-2014, 03:03 PM
  3. [SOLVED] Match Issue - Consolidate Columns of Data With Match/Index/etc?
    By excelsior123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-03-2014, 05:14 PM
  4. index match issue
    By jw01 in forum Excel General
    Replies: 2
    Last Post: 04-25-2012, 12:10 AM
  5. index match (issue)
    By step_one in forum Excel General
    Replies: 5
    Last Post: 05-16-2011, 02:58 PM
  6. index, match issue
    By step_one in forum Excel General
    Replies: 3
    Last Post: 05-03-2011, 03:40 PM
  7. index..match - issue
    By step_one in forum Excel General
    Replies: 15
    Last Post: 05-03-2011, 08:25 AM

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