Apr 24 2019 06:33 AM
Hi, first time post here so bear with me!
I am trying to create a worksheet that displays a list of shift types with names of staff allocated that shift over the period of a week.
I can do this for straightforward shifts but occasionally we have to ask staff to double up on shifts and the vlookup formula I am using wont find the double shifts.
The problem is that there are too many possible combinations of double shifts for me to list them all on the worksheet so I need a formula that can find any possible combination and display the staff member who has been allocated it?
Sample File attached.
Thanks
Apr 28 2019 05:19 AM
Apr 28 2019 02:59 PM
@othafa wrote:
I tried "*+"&range&"+*" but that didn't work.
I think you were within a whisker of getting it to work though. If you pad both the lookup value and the lookup array with leading and trailing "+"s, you will find the strings you are searching for but "+L+" is not present in "+LMP+". You will also locate it successfully within "+L+N+", just as you want.
p.s. I seem to have had different versions of the workbook. The attached uses a relative reference within the formula for 'shift' which allows the worksheet formulas to be entered without CSE.
Apr 28 2019 03:35 PM
I liked the reference to the Bill Jelen podcast. I have yet to get around to using measures and DAX so perhaps this will encourage me to seek out new problems where I can use it!
Apr 29 2019 04:18 AM
Apr 29 2019 04:54 AM
Oops! My fault.
I clearly changed the worksheet but uploaded it before saving!
The version I am now about to post picks up both the day and the shift code by relative referencing. Each cell requires a reference to be made to the 'shiftTable' but padded with "+". If this 'padding' were done in a worksheet formula this would require CSE but by hiding it away in the Named Formula the array operation happens by default.
I apologise for changing strategy mid-stream; it is not a good teaching style!
Apr 29 2019 07:04 AM