Marco, IFS and XLOOKUP but certainly happy to be wrong

[N0t Y0urs]

I am really struggling with this and while its just the first part it technically controls everything else.

Say I have 18 (I've only shown 12) rows and the data represented in these rows are dependant on a table (for want of a better word) The table contains 4 pieces of information which will determine the data reported on a different sheet. The information is Date (1-Apr-2024) then a Recurrence Value (in this example it is 2). Next I have Weekday (I haven't figured out how I need this at the moment but I only want weekdays reported). Finally I have 3rd value Recurrence as every 5 rows I want the row above to be shown 3 times.

Now to complicate this further the table I want to be able to change the initial recurrence value from 1 - 5, and the 5th row recurrence to be the initial recurrence plus 1.

Here is a sample of what it should look like.

Draft of Master.xlsx
	A	B	C	D
1		Date	Level	Starting Balance
2	1	Mon 01-04-2024
3	2	Mon 01-04-2024
4	3	Tue 02-04-2024
5	4	Tue 02-04-2024
6	5	Wed 03-04-2024
7		Wed 03-04-2024
8	6	Wed 03-04-2024
9	7	Thu 04-04-2024
10	8	Thu 04-04-2024
11	9	Fri 05-04-2024
12	10	Fri 05-04-2024
13		Fri 05-04-2024
14
Acc1

Cell Formulas
Range	Formula
B2	B2	=Tables!C3
B3,B5,B7:B8,B10,B12:B13	B3	=B2
B4	B4	=Tables!C3+1
B6	B6	=B4+1
B9,B11	B9	=B8+1

Here is the tables page so you can see what I have set there

Draft of Master.xlsx
	A	B	C	D
1
2		Table 1 - Date
3		Start Date	01-Apr-24
4		Recurrance	2
5		Weekday	1
6		5th Row Recurrance	3
7
Tables

Cell Formulas
Range	Formula
C5	C5	=WEEKDAY($C$3,2)
C6	C6	=C4+1

Any help would be greatly appreciated. I know that this spreadsheet is going to be really formula intensive so I am looking to try and reduce the redundancy and find the most effect solutions for what I am wanting to achieve.

 

[awoohaw]

Can you tell us what you want to see? Why are there blanks in A7 and A13?
what do you want to see when you select a recurrence of 4?
Are you trying to build a column with repeating dates (a specified number of times) that excludes weekends?

 

[N0t Y0urs]

Can you tell us what you want to see? Why are there blanks in A7 and A13?
what do you want to see when you select a recurrence of 4?
Are you trying to build a column with repeating dates (a specified number of times) that excludes weekends?

I suppose in simplest form yes I am wanting repeating dates that excludes weekends based on the repeat value.

So the concept is I want to be able to produce a data set that is broken into 5. Each 1 - 5 is a transaction and for forecasting purposes a day may only have 1 transaction or it will have up to 5 transactions. So every 6th row is a total and other assortments of calculations of the above 5 rows.

This is for a financial sheet and the 5 rows are grouped together so we know if we take a proportion of profit after 5 transactions or we have to wait for the next 5 to complete.

Since it’s midnight here I can take a screenshot that’s not to big to upload showing the bigger version of the sheet or if it’s too big I will use Dropbox or Google Drive to share the file in the morning.