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
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
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
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.
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.
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.
Thanks Sambo kid,
That solved the issue.
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.
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.
=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)))
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.
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.
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.
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?
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.
Thanks for all your hard work.
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?
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks