Pass Down System Using Only Today's Date

[Justinian]

I am trying to create a pass down system where anyone can enter important daily events on a single log.

I want to take only today's information, shown in the table below, and have it placed on a separate tab so whenever the sheet is opened on a new day, only that day's issues appear:

12/06/22	Tower 48	Laundry room	Painting project
12/06/22	Block 31	Laundry room	Painting project
12/06/22	Tower 37	A/B/C	Hot water shutdown
12/07/22	Tower 41	G/H/J	Hot water shutdown

I can do this using table or pivots but some users are not that experienced and I would like Excel to only show today's issues when the log is opened.

Can anyone help?

 

[Joe4]

Since you have Excel 365, why not make use of Excel's new Filter function?

FILTER function - Microsoft Support

How to use the FILTER function in Excel to filter a range of data based on criteria you define.

support.microsoft.com

As your condition, just put your date column =TODAY().
So, depending on your range and sheet names, it might look something like:

=FILTER(Sheet1!A2:D5,Sheet1!A2:A5=TODAY())

 

[Justinian]

I get a spill error when using FILTER plus the source data may not have three rows tomorrow (like it did today). It could be 10 or none.

 

[Joe4]

You ONLY need to put the formula in the very first cell of your destination range.
It will automatically spill over to the other columns and however many rows it needs.

 

[Justinian]

People add to this spreadsheet everyday so tomorrow, A2:A5 will be 12/6/22 instead of 12/7/22. This sheet will grow everyday when people add to it so the data set needs to allow for that.

 

[Justinian]

Date	Building	Unit(s)	Issue
#SPILL!

 

[Joe4]

People add to this spreadsheet everyday so tomorrow, A2:A5 will be 12/6/22 instead of 12/7/22. This sheet will grow everyday when people add to it so the data set needs to allow for that.

Then just make the range bigger than it will ever get, i.e.

=FILTER(Sheet1!A2:D5000,Sheet1!A2:A5000=TODAY())

If 5000 isn't big enough, then choose some bigger row number that you will never hit.

Date isn't an issue, as TODAY() will always dynamically return the current date.

 

[Justinian]

Still not working.

Sample Pass Down Log.xlsm
	A	B	C	D
4	Date	Building	Unit(s)	Issue
5	#SPILL!
6
7
8
Dashboard

Cell Formulas
Range	Formula
A5	A5	=FILTER('Source Data'!A2:G2000,'Source Data'!A2:A2000=TODAY())
A8	A8	=IF(B8=FALSE,"",VLOOKUP($D$1,'Source Data'!A:G,1,FALSE))

 

[Joe4]

Still not working.

Sample Pass Down Log.xlsm
	A	B	C	D
4	Date	Building	Unit(s)	Issue
5	#SPILL!
6
7
8
Dashboard

Cell Formulas
Range	Formula
A5	A5	=FILTER('Source Data'!A2:G2000,'Source Data'!A2:A2000=TODAY())
A8	A8	=IF(B8=FALSE,"",VLOOKUP($D$1,'Source Data'!A:G,1,FALSE))

Why is there a formula in cell A8?
Get rid of that!

 

[Justinian]

I deleted that:

Sample Pass Down Log.xlsm
	A	B	C	D
4	Date	Building	Unit(s)	Issue
5	#SPILL!
6
7
8
9
10
Dashboard

Cell Formulas
Range	Formula
A5	A5	=FILTER('Source Data'!A2:G2000,'Source Data'!A2:A2000=TODAY())
A8	A8	=IF(B8=FALSE,"",VLOOKUP($D$1,'Source Data'!A:G,1,FALSE))