Referencing Values from columns on Master sheet and skipping blank values

[sjbfan]

Hey I'm wondering what formula I can use to get these two sheets below from a master sheet. I would like to be able to type out values in either the In or the Out columns on my master sheet (3rd picture) and have them automatically be printed out in my In and my Out sheet along with their corresponding dates. Thanks!

 

[Toadstool]

Hi SJBFan,

Does this help?

SJBFan.xlsx
	A	B	C
1	Date	In	Out
2	2021-06-02	1
3	2021-06-03		2
4	2021-06-04		3
5	2021-06-05	4
6	2021-06-06	5
7	2021-06-07	6
8	2021-06-08		7
9	2021-06-09		8
10
Master

Cell Formulas
Range	Formula
A3:A9	A3	=A2+1

SJBFan.xlsx
	A	B
1	Date	In
2	2021-06-02	1
3	2021-06-05	4
4	2021-06-06	5
5	2021-06-07	6
6
In

Cell Formulas
Range	Formula
A2:B6	A2	=IFERROR(INDEX(Master!A$2:A$9999,AGGREGATE(15,6,ROW(Master!$B$2:$B$9999)-ROW(Master!$B$1)/(Master!$B$2:$B$9999<>""),ROW()-ROW($A$1))),"")

SJBFan.xlsx
	A	B
1	Date	Out
2	2021-06-03	2
3	2021-06-04	3
4	2021-06-08	7
5	2021-06-09	8
6
Out

Cell Formulas
Range	Formula
A2:A6	A2	=IFERROR(INDEX(Master!A$2:A$9999,AGGREGATE(15,6,ROW(Master!$B$2:$B$9999)-ROW(Master!$B$1)/(Master!$C$2:$C$9999<>""),ROW()-ROW($A$1))),"")
B2:B6	B2	=IFERROR(INDEX(Master!C$2:C$9999,AGGREGATE(15,6,ROW(Master!$B$2:$B$9999)-ROW(Master!$B$1)/(Master!$C$2:$C$9999<>""),ROW()-ROW($A$1))),"")

 

[sjbfan]

Hey thanks so much that did the job perfectly!

 

[Toadstool]

Hey thanks so much that did the job perfectly!

You're welcome!
Thanks for the feedback.

 

[Peter_SSs]

Since you have Microsoft 365 you can actually do this with a single formula in a single cell in each of the In/Out sheets. No need to copy the formula anywhere as the results will automatically 'spill' into the required number of rows and columns.

sjbfan.xlsm
	A	B	C
1	Date	In	Out
2	2/06/2021	1
3	3/06/2021		2
4	4/06/2021		3
5	5/06/2021	4
6	6/06/2021	5
7	7/06/2021	6
8	8/06/2021		7
9	9/06/2021		8
10
Master

sjbfan.xlsm
	A	B
1	Date	In
2	2/06/2021	1
3	5/06/2021	4
4	6/06/2021	5
5	7/06/2021	6
6
In

Cell Formulas
Range	Formula
A1:B5	A1	=FILTER(Master!A1:B100,Master!B1:B100<>"","")
Dynamic array formulas.

sjbfan.xlsm
	A	B
1	Date	Out
2	3/06/2021	2
3	4/06/2021	3
4	8/06/2021	7
5	9/06/2021	8
6
Out

Cell Formulas
Range	Formula
A1:B5	A1	=LET(bigr,Master!A1:C100,smallr,INDEX(bigr,SEQUENCE(ROWS(bigr)),{1,3}),FILTER(smallr,INDEX(smallr,0,2)<>""))
Dynamic array formulas.

If you don't happen to have the LET function, then the formula in A1 of the Out sheet would be

=FILTER(INDEX(Master!A1:C100,SEQUENCE(ROWS(Master!A1:C100)),{1,3}),INDEX(INDEX(Master!A1:C100,SEQUENCE(ROWS(Master!A1:C100)),{1,3}),0,2)<>"")

 

[Peter_SSs]

Actually, much simpler for the Out sheet:

sjbfan.xlsm
	A	B
1	Date	Out
2	3/06/2021	2
3	4/06/2021	3
4	8/06/2021	7
5	9/06/2021	8
6
Out

Cell Formulas
Range	Formula
A1:B5	A1	=LET(f,FILTER(Master!A1:C100,{1,0,1}),FILTER(f,INDEX(f,0,2)<>""))
Dynamic array formulas.

Without LET:

=FILTER(FILTER(Master!A1:C100,{1,0,1}),INDEX(FILTER(Master!A1:C100,{1,0,1}),0,2)<>"")