Testing schedule based on frequency and date

Juddman79

Juddman79

New Member
Joined
Feb 17, 2020
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
Evening all,

I don't know if what I'm asking is possible but if it is I'm hoping somebody is able to help me. To be honest I have no idea where to start as my knowledge of coding excel is minimal.

Is it possible to create a schedule of test dates based on a pre-set value in a cell and a start date? I would like the row to auto populate based on the the date of the previous test completion date and the pre defined frequency (column G) for the specific test (column F). J5 is automatically filled based on the date in cell I6 and the frequency list in cell G5 (this would need to automatically change if the test type changes)and so on along the row for the ten tests. I've attached a screenshot and also a link to the sheet.

1701375501343.png



Thanks in advance.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Does something like this work?:

Or do you need to schedule the next test on working days from monday to friday?

Schedule.xlsx
DEFGHIJKLMNOP
3Test TypeFrequency [Days]Test 1Test 2Test 3Test 4Test 5Test 6Test 7Test 8Test 9Test 10
4Test A3Scheduled01/01/202405/01/202408/01/202412/01/202416/01/202421/01/202424/01/202427/01/202431/01/202403/02/2024
5Completed02/01/202405/01/202409/01/202413/01/202418/01/202421/01/202424/01/202428/01/202431/01/2024
6Result
7Test B5Scheduled01/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/202416/02/2024
8Completed02/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/2024
9Result
10Test D14Scheduled01/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/202407/05/2024
11Completed02/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/2024
12Result
13Test E30Scheduled01/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/202428/09/2024
14Completed02/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/2024
15Result
16
17
18Test TypeFrequency
19Test A3
20Test B5
21Test C10
22Test D14
23Test E30
Hoja1
Cell Formulas
RangeFormula
E4,E13,E10,E7E4=INDEX(TestFrequency,MATCH(D4,TestTypes,0))
H4:P4,H13:P13,H10:P10,H7:P7H4=IF(G5>0,G5+$E4,"")
Named Ranges
NameRefers ToCells
TestFrequency=Tests[Frequency]E13, E10, E7, E4
TestTypes=Tests[Test Type]E13, E10, E7, E4
Cells with Data Validation
CellAllowCriteria
D4List=TestTypes
D7List=TestTypes
D10List=TestTypes
D13List=TestTypes
 
Upvote 1
Solution
felixstraube said:
Does something like this work?:

Or do you need to schedule the next test on working days from monday to friday?

Schedule.xlsx
DEFGHIJKLMNOP
3Test TypeFrequency [Days]Test 1Test 2Test 3Test 4Test 5Test 6Test 7Test 8Test 9Test 10
4Test A3Scheduled01/01/202405/01/202408/01/202412/01/202416/01/202421/01/202424/01/202427/01/202431/01/202403/02/2024
5Completed02/01/202405/01/202409/01/202413/01/202418/01/202421/01/202424/01/202428/01/202431/01/2024
6Result
7Test B5Scheduled01/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/202416/02/2024
8Completed02/01/202407/01/202412/01/202417/01/202422/01/202427/01/202401/02/202406/02/202411/02/2024
9Result
10Test D14Scheduled01/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/202407/05/2024
11Completed02/01/202416/01/202430/01/202413/02/202427/02/202412/03/202426/03/202409/04/202423/04/2024
12Result
13Test E30Scheduled01/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/202428/09/2024
14Completed02/01/202401/02/202402/03/202401/04/202401/05/202431/05/202430/06/202430/07/202429/08/2024
15Result
16
17
18Test TypeFrequency
19Test A3
20Test B5
21Test C10
22Test D14
23Test E30
Hoja1
Cell Formulas
RangeFormula
E4,E13,E10,E7E4=INDEX(TestFrequency,MATCH(D4,TestTypes,0))
H4:P4,H13:P13,H10:P10,H7:P7H4=IF(G5>0,G5+$E4,"")
Named Ranges
NameRefers ToCells
TestFrequency=Tests[Frequency]E13, E10, E7, E4
TestTypes=Tests[Test Type]E13, E10, E7, E4
Cells with Data Validation
CellAllowCriteria
D4List=TestTypes
D7List=TestTypes
D10List=TestTypes
D13List=TestTypes
Click to expand...

Thanks for your response. A test can happen any day of the week, so no it doesn't need to be specifically a working day.

I did see an email about changing the sharing permissions of the file which I have done, but I don't know if you got a message saying this.

It does look like this will work.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Macro/VBA to add text from another cell into current cell when I type a certain value.
Replies
9
Views
744
mrmcmillanrevis
M
D
  • Solved
Overriding Conditional Formatting Color with Manual Entries of a different color
2
Replies
15
Views
3K
Joe4
Joe4
A
  • Question
How to enter a task start date based on the end date of a previous task. LONG
Replies
1
Views
379
abuchanan
A
D
  • Solved
DATE Formula - Find earliest posted date in range of dates given month and year
Replies
2
Views
895
Derick63
D
S
  • Question
calculations based on information from different cells
2
Replies
11
Views
609
jtakw
jtakw

Forum statistics

Threads
1,215,080
Messages
6,123,013
Members
449,093
Latest member
ikke

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