Match and show value based on date

B

buer4499

New Member
Joined
Jun 15, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking to show the amount of hours for Example 1, 2 and 3 in the Dashboard Sheet based on the Next Week's date (AC2) and the location in the Data Sheet A:A & B:B.

For example, on the Dashboard sheet, Example 1 would show 24070.

Example.xlsx
ABCDEFGHIJKLMNOP
1FY21FY21FY21FY21FY21FY21FY21FY21FY21
2Q4Q4Q4Q4Q4Q4Q4Q4Q4Q4Q4Q4Q4Q1
3202140202141202142202143202144202145202146202147202148202149202150202151202152202201
431/05/202107/06/202114/06/202121/06/202128/06/202105/07/202112/07/202119/07/202126/07/202102/08/202109/08/202116/08/202123/08/202130/08/2021
5      Current1234567
6      Current123456Locking
7
8Example 1English2637026370260002600026000240702407024070236902369021840218402184020000
9Example 1France26302630263034703470347034702800267026702670267022102550
10Example 1Germany35703570357035703570357034702870228022802280228022802100
11Example 1Sweden297297297297297297297297250250250250250250
12Example 1Netherlands361361361361361361361361370370340340340350
13Example 1Poland120120120120120120140140140140140140140140
14Example 1Denmark250250250250250250250250230230230230230250
15Example 1Spain500500500500500500480480300300300300300300
16Example 1Italy420420420420420420400400330300300300300300
17Example 1Russia26902690269026902690270027002700242024202420242024202400
18
19
20Example 2English6006001200180024002400240024002400
21Example 2France400400400400400600600
22Example 2Germany600600600600600800800
23
24
25Example 3English19091909190919091909190919091909
26Example 3France205205205205205205205205
27Example 3Germany2020202020202020
28Example 3Sweden2020202020202020
29Example 3Netherlands
30Example 3Poland
31Example 3Denmark
32Example 3Spain194194194194194194194194
33Example 3Italy294294294294294294294294
34Example 3Russia223223223223223223223223
Data
Cell Formulas
RangeFormula
C5:P5C5=IFS(WEEKNUM(C4)=(WEEKNUM(NOW())),"Current",WEEKNUM(C4)=(WEEKNUM(NOW()))+1,"1",WEEKNUM(C4)=(WEEKNUM(NOW()))+2,"2",WEEKNUM(C4)=(WEEKNUM(NOW()))+3,"3",WEEKNUM(C4)=(WEEKNUM(NOW()))+4,"4",WEEKNUM(C4)=(WEEKNUM(NOW()))+5,"5",WEEKNUM(C4)=(WEEKNUM(NOW()))+6,"6",WEEKNUM(C4)=(WEEKNUM(NOW()))+7,"7",WEEKNUM(C4)=(WEEKNUM(NOW()))+8,"8",WEEKNUM(C4)=(WEEKNUM(NOW()))+9,"Locking",WEEKNUM(C4)>(WEEKNUM(NOW()))+9,"",WEEKNUM(C4)<(WEEKNUM(NOW())),"")
C6:P6C6=IFS(WEEKNUM(C4)=(WEEKNUM(NOW())),"Current",WEEKNUM(C4)=(WEEKNUM(NOW()))+1,"1",WEEKNUM(C4)=(WEEKNUM(NOW()))+2,"2",WEEKNUM(C4)=(WEEKNUM(NOW()))+3,"3",WEEKNUM(C4)=(WEEKNUM(NOW()))+4,"4",WEEKNUM(C4)=(WEEKNUM(NOW()))+5,"5",WEEKNUM(C4)=(WEEKNUM(NOW()))+6,"6",WEEKNUM(C4)=(WEEKNUM(NOW()))+7,"Locking",WEEKNUM(C4)>(WEEKNUM(NOW()))+7,"",WEEKNUM(C4)<(WEEKNUM(NOW())),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K5:AM5Cell Valuecontains "8"textNO
K5:AM5Cell Valuecontains "7"textNO
K5:AM5Cell Valuecontains "Locking"textNO
K5:AM5Cell Valuecontains "6"textNO
K5:AM5Cell Valuecontains "5"textNO
K5:AM5Cell Valuecontains "4"textNO
K5:AM5Cell Valuecontains "3"textNO
K5:AM5Cell Valuecontains "2"textNO
K5:AM5Cell Valuecontains "1"textNO
K5:AM5Cell Valuecontains "Current"textNO
K6:AM7Cell Valuecontains "Locking"textNO
K6:AM7Cell Valuecontains "6"textNO
K6:AM7Cell Valuecontains "5"textNO
K6:AM7Cell Valuecontains "4"textNO
K6:AM7Cell Valuecontains "3"textNO
K6:AM7Cell Valuecontains "2"textNO
K6:AM7Cell Valuecontains "1"textNO
K6:AM7Cell Valuecontains "Current"textNO
C5:J5Cell Valuecontains "8"textNO
C5:J5Cell Valuecontains "7"textNO
C5:J5Cell Valuecontains "Locking"textNO
C5:J5Cell Valuecontains "6"textNO
C5:J5Cell Valuecontains "5"textNO
C5:J5Cell Valuecontains "4"textNO
C5:J5Cell Valuecontains "3"textNO
C5:J5Cell Valuecontains "2"textNO
C5:J5Cell Valuecontains "1"textNO
C5:J5Cell Valuecontains "Current"textNO
C6:J7Cell Valuecontains "Locking"textNO
C6:J7Cell Valuecontains "6"textNO
C6:J7Cell Valuecontains "5"textNO
C6:J7Cell Valuecontains "4"textNO
C6:J7Cell Valuecontains "3"textNO
C6:J7Cell Valuecontains "2"textNO
C6:J7Cell Valuecontains "1"textNO
C6:J7Cell Valuecontains "Current"textNO


Example.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Previous Month = Tactical for month previousTodays Date13/07/2021
2Proposed = TacticalNext Week19/07/2021
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21Avg DemandMonTueWedThuFriSatSunAvg DemandMonTueWedThuFriSatSunAvg DemandMonTueWedThuFriSatSun
22Previous MonthPrevious MonthPrevious Month
23Actual (12 weeks)Actual (12 weeks)Actual (12 weeks)
24Actual (4 weeks)Actual (4 weeks)Actual (4 weeks)
25ProposedProposedProposed
26Previous vs ProposedPrevious vs ProposedPrevious vs Proposed
27Hours ChangeHours ChangeHours Change
28
29HoursHoursHours
30Example 1Example 1Example 1
31Example 2Example 2Example 2
32Example 3Example 3Example 3
33
Dashboard
Cell Formulas
RangeFormula
AC1AC1=(TODAY())
AC2AC2=((AC1-WEEKDAY(AC1,2))+1)+7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C27:I27Other TypeIcon setNO
U26:AA27Other TypeIcon setNO
L26:R27Other TypeIcon setNO
C26:I26Other TypeIcon setNO
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You must log in or register to reply here.

Similar threads

V
  • Solved
Week Number
Replies
2
Views
101
vmjan02
V
R
  • Question
Pivot Table Slicer for Rows Field
Replies
0
Views
243
radonwilson
R
willow1985
  • Question
Assistance with Spreadsheet that is constantly crashing/over complicated formulas
Replies
1
Views
385
willow1985
willow1985
L
  • Question
Formula Help with Count of Week Numbers for every Each Month
2
Replies
19
Views
962
Armstrong_N14
A
L
  • Solved
Help with a Formula of IF greater than or Less than or with a Negative time sign
Replies
6
Views
514
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,657
Messages
6,120,773
Members
448,991
Latest member
Hanakoro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top