Reference based on what day

[Jones54]

Hi all,

I have a formula which returns a specific shift target based on the criteria from another cell. The problem I am having is that the particular shift target changes on a Friday due to shorter shift times. I know I need to reference the date on the taskbar but not sure how to do this.

Any help would be very much appreciated.

Thanks

 

[jasonb75]

Using WEEKDAY(TODAY()) in a formula will give you the day of the week as a number from 1 (Sunday) to 7 (Saturday). You should be able to use this to determine the weekday and the relevant target figure.

Note that TODAY() is a volatile function which means that the formula and any dependent formulas will be recalculated every time calculation is triggered. With complex formulas or even a large number of simple formulas this can lead to significant lag during data entry.

 

[Jones54]

Hi Jasonb75,

thanks for the reply, where would I fit that into the following formula that I am currently using shown below:

=IFS(E9=$S$3,$T$3,E9=$S$4,$T$4,E9=$S$5,$T$5,E9=$S$6,$T$6,E9=$S$7,$T$7,E9=$S$8,$T$8,E9=$S$9,$T$9,E9=$S$10,$T$10,E9=$S$11,$T$11,E9=$S$12,$T$12,E9=$S$13,$T$13,E9=$S$14,$T$14,E9=$S$15,$T$15,E9=$S$16,$T$16,E9=$S$17,$T$17,E9=$S$18,$T$18,E9=$S$19,$T$19,E9=$S$20,$T$20,E9=$S$21,$T$21,E9=$S$22,$T$22,E9=$S$23,$T$23,TRUE,"")

Thanks

 

[jasonb75]

That whole formula could be simplified to

=IFERROR(VLOOKUP(E9,$S$3:$T$23,2,0),0)

But I can't say how you would incorporate WEEKDAY into that without knowing what E9, $S$3,$T$3, etc refer to. The additional functions need to be fitted to the data as well as the current formula.

 

[Jones54]

Hi jasonb75,

How do i add a file attachment so that you can see the route of the formulas?

 

[Dossfm0q]

Use XL2BB

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Jones54]

jasonb75.

In my example E9 refers to a cell which contains a dropdown list of different jobs. The formula I have then looks at a list present in column "S", when it match's the cell E9 with the match in column "S" to returns the value from column "T".

for example:

In cell E9 the following is picked

GLOW 2.0 - LOWER HOUSING SUB ASSY (TOPLINE)

A formula I then have in Cell I9 (Formula shown above) locates job picked in E9 in a list of jobs (in this case it locates GLOW 2.0 - LOWER HOUSING SUB ASSY (TOPLINE)) in column "S" and returns the correspondent shift target (in this case 2160) into cell "I9". This is ok when working Monday to Thursday, but as I explained earlier if its a Friday I want it still to look at column "S" to match the job picked but this time pick the shift target from Column "V", which in this case will then return the figure "1500" into cell "I9"

I hope this explains this better.

I couldn't add the add-on as highlighted by "Dossfm0q" otherwise I would have included the file.

 

[Fluff]

How about

=IFERROR(VLOOKUP(E9,$S$3:$V$23,IF(WEEKDAY(TODAY(),1)=6,4,2),0),0)

 

[Jones54]

Thanks Fluff, that seems to work a treat.

 

[Fluff]

Glad we could help & thanks for the feedback.