+ Reply to Thread
Results 1 to 17 of 17

Spreadsheet not selecting correct data

  1. #1
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Spreadsheet not selecting correct data

    I have created a spreadsheet that you can select data using a drop down date box - it works fine for the first 4 dates then does not find the data after that.

    I have included the file.


    Many thanks


    David
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    from what I can see it looks like you are matching assessment sheet C3 against input table B20 through B133 but the date in the fourth spot 2/9/2019 (or 9/2/2019 for England) is no where in those cells.

    Or maybe it is 9/2 as it appears in my US version and 2/9 in yours? I get them mixed up, but the same issue still exists.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Re: Spreadsheet not selecting correct data

    Hi Sambo kid

    I have now changed where the formula looks see below:

    =IF(INDEX('Input Table'!$C$7:$DQ$150,MATCH('Assessment sheet'!$C$3,'Input Table'!$B$7:$B$134,0),MATCH('Assessment sheet'!$B8,'Input Table'!$C$6:$DQ$6,0))=0,"",INDEX('Input Table'!$C$21:$DQ$26,MATCH('Assessment sheet'!$C$3,'Input Table'!$B$21:$B$26,0),MATCH('Assessment sheet'!$B8,'Input Table'!$C$6:$DQ$6,0)))

    Now its finding data in the correct positions but still #N/A error: I have attached a screenshot.
    Attached Images Attached Images

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    ok, what is the second portion supposed to return?
    this portion... =INDEX('Input Table'!$C$7:$DQ$150,MATCH('Assessment sheet'!$C$3,'Input Table'!$B$7:$B$150,0),MATCH('Assessment sheet'!$B5,'Input Table'!$C$6:$DQ$6,0))
    works, but this section is returning the #N/A...
    INDEX('Input Table'!$C$21:$DQ$26,MATCH('Assessment sheet'!$C$3,'Input Table'!$B$21:$B$26,0),MATCH('Assessment sheet'!$B8,'Input Table'!$C$6:$DQ$6,0))
    usually people put a copy of the first index/match in the second section of an if/then stmt.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    I think this is what you are looking for...
    =IF(INDEX('Input Table'!$C$7:$DQ$150,MATCH('Assessment sheet'!$C$3,'Input Table'!$B$7:$B$134,0),MATCH('Assessment sheet'!$B5,'Input Table'!$C$6:$DQ$6,0))=0,"",INDEX('Input Table'!$C$7:$DQ$150,MATCH('Assessment sheet'!$C$3,'Input Table'!$B$7:$B$134,0),MATCH('Assessment sheet'!$B5,'Input Table'!$C$6:$DQ$6,0)))
    it appears to return the results you want for the other dates I tried. See what you think.

  6. #6
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Re: Spreadsheet not selecting correct data

    Thanks Sambo kid,

    That solved the issue.

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    Great, glad that solved it for you. Don't forget to mark the post as solved using the thread tools dropdown at the top of the post. And if you are so inclined clicking on * Add Reputation below one of my posts is always appreciated.

  8. #8
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Re: Spreadsheet not selecting correct data

    Hi Sambo Kid,

    I have made some alterations but I can not get it to make a double search for both position and date, using the input table - file included.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Re: Spreadsheet not selecting correct data

    =IF(INDEX('Input Table'!$D$12:$EH$242,MATCH('WTD 17 WK RULE & ETI CODE'!$J$3,'Input Table'!$C$12:$C$226,0),MATCH('WTD 17 WK RULE & ETI CODE'!$B23,'Input Table'!$D$6:$EH$6,0))=0,"",INDEX('Input Table'!$D$12:$EH$242,MATCH('WTD 17 WK RULE & ETI CODE'!$J$3,'Input Table'!$C$12:$C$226,0),MATCH('WTD 17 WK RULE & ETI CODE'!$B23,'Input Table'!$D$6:$EH$6,0)))

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    I tried a three match function, date, position and day but it kept failing. I do know that Position 1 had an extra space at the end of it in your WTD 17 sheet but after I removed that so they would match between sheets I still couldn't get it to match. I got the two factor match but not the three. I'll give it another try tomorrow if someone else doesn't come along and solve it in the mean time.
    BTW, I'd get rid of the merged cells in the WTD sheet between rows 3 and 4. Merged cells mess up formulas.

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    I think I finally got it to work for you.
    Changes you have to make: in rows 3/4 of the WTD 17 WK tab, get rid of the merged cells so that the start dates are only in row 3.
    Second, in C2 of the same tab, get rid of the trailing space after the 1 in Position 1 so it will match Position 1 in the input table.
    Now, I limited the ranges to just the dates of 8/5/2019 in the formula AND the input table but you can change that to the whole range in the input table tab.
    So here is the formula - but this one is limited to a specific set that I tested so I know it works...
    =IFERROR(INDEX('Input Table'!$D$122:$J$131,MATCH(1,(C$2='Input Table'!$A$12:$A$131)*(C$3='Input Table'!$C$122:$C$131)),MATCH($B5,'Input Table'!$D$6:$J$6,0)),"")
    It is an array formula so once you put it in the cell, to activate it you need to hit ctrl + shift + enter so that the curly braces {} appear at both ends, YOU CANNOT add them yourself (in case you are not familiar with array formulas).

    Below is the formula with the entire range for the table. I haven't tested it to the extent of the entire Input Table tab but below is that formula.
    =IFERROR(INDEX('Input Table'!$D$12:$EH$131,MATCH(1,(C$2='Input Table'!$A$12:$A$131)*(C$3='Input Table'!$C$12:$C$131)),MATCH($B5,'Input Table'!$D$6:$EH$6,0)),"")
    see if it works.

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    now that I'm looking at it again, the problem with the structure of your workbook is that in the input sheet you added another date column every eighth column so that will confound the formula since you want additional dates. Maybe if you just tried to match on two conditions such as the position and the day of the week 1 or day of the week 2 making the dates irrelevant it could work, or maybe extend your dates that are in col K below the dates in col C (same thing with the dates in col S, put them below those in col C).

    So the first formula I gave you in post #11 I know works, the second covering the ranges out to col EH I don't think will work because it will be looking for the dates in col C that are now in col K and then in col S etc. If your data was more vertical instead of horizontal the formula would cover it all.

  13. #13
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Re: Spreadsheet not selecting correct data

    Thanks for all your help, Sambo Kid - I think that I am expecting too much from the formulas any suggestions for making the data more vertical?

  14. #14
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    I'll have to play with the structure and get back to you on that. Maybe someone else will have some input in the mean time.

  15. #15
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Re: Spreadsheet not selecting correct data

    Thanks for all your hard work.

  16. #16
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Spreadsheet not selecting correct data

    Not a problem, it isn't what I'm being paid to do (but shh, don't tell my employer).

    BTW, does the 17 week period have some significance?
    In the input table do the numbers in col B have a significance?

  17. #17
    Registered User
    Join Date
    07-18-2019
    Location
    Lancaster, England
    MS-Off Ver
    Office 2016
    Posts
    14

    Re: Spreadsheet not selecting correct data

    Yes 17 Week period is very significant due to having to calculate a rolling 17 week work hours and column b in the input table is just for my reference. Hope this helps

+ 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. [SOLVED] Selecting Correct Data from file
    By simpauw in forum Excel General
    Replies: 3
    Last Post: 03-08-2019, 11:34 AM
  2. Replies: 4
    Last Post: 01-06-2016, 05:38 PM
  3. Trouble selecting correct data for bubble charts.
    By Mc837 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-07-2014, 04:57 PM
  4. [SOLVED] Problems selecting correct data after filter is in place
    By DarthWire in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-19-2013, 06:18 AM
  5. ADOdb not selecting correct data within Date Range
    By mvgoggans in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2012, 03:50 PM
  6. Combobox on Userform not selecting correct data
    By frostie77 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2010, 05:15 PM
  7. Replies: 2
    Last Post: 02-02-2006, 06:20 PM

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