Can I have a table with a date and data entered, and later enter another date with different data in the same cells?

[Sedenya]

Noob question here!

I want to create a performance report, in which change in status is entered very week.
I could do separate rows for each week, but want to keep the table as slim as possible and still want to be able to track data over time.
So having just one row with a drop-down with pre-entered dates and then being able to enter the corresponding data for each date would be my goal. Is this possible to do?

Thanks very much for any suggestion!

Cheers,
Stefan

 

[6StringJazzer]

If you only have one row, how do you want to track data over time?

 

[psycoperl]

I have three methods that you can use.

1. "Data with Filter"
- You can have a sheet that has a Data Filter (Ribbon Data> Filter) that allows you to select which date record to show, the rest would be hidden.

2. VLookup with a search box
- You would need to use two sheets a Data Sheet and a Report/Summary sheet
- On the Report/Summary sheet you would have a data validation cell that gets a list of dates from the data sheet and then uses an dynamic array version of the Vlookup() to display the values. (To create a dynamic array formula press ctrl+shift+enter when you complete the formula)
- This version would allow the user to select previous dates to review other reports.

Book1
	A	B	C	D	E	F	G
1	Select Date	9/11/20
2
3		Leave Type	NOTE	D	H	M	LEAVE MINUTES
4		AL	FY20 SPEND	-1	0	0	26455
5
Summary Option 2

Cell Formulas
Range	Formula
B4:G4	B4	=VLOOKUP(B1,Data!A2:G200,{2,3,4,5,6,7},FALSE)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=Data!$A2:$A3000

3. Vlooklup with Max() date.
- This is similar to the option listed above as you need to use two sheets. The only difference is that instead of allowing a user to select a date from a drop down list, it automatically selects the maximum date value from the data list

Book1
	A	B	C	D	E	F	G
1
2	Date	Leave Type	NOTE	D	H	M	LEAVE MINUTES
3	Tuesday, August 31, 2021	AL	Increment +	2	0	35	29395
4
Summary Option 1

Cell Formulas
Range	Formula
A3:G3	A3	=VLOOKUP(MAX(Data!A2:A200),Data!A2:G200,{1,2,3,4,5,6,7},FALSE)
Dynamic array formulas.

Book1
	A	B	C	D	E	F	G
1	ANNUAL LEAVE Date	Leave Type	NOTE	D	H	M	LEAVE MINUTES
2	Monday, August 31, 2020	AL	BALANCE	69	6	55	29395
3	Wednesday, September 2, 2020	AL	FY20 SPEND	-1	0	0	28975
4	Thursday, September 3, 2020	AL	FY20 SPEND	-1	0	0	28555
5	Friday, September 4, 2020	AL	FY20 SPEND	-1	0	0	28135
6	Tuesday, September 8, 2020	AL	FY20 SPEND	-1	0	0	27715
39	Tuesday, August 31, 2021	AL	Increment +	2	0	35	29395
40		AL					29395
41		AL					29395
Data

Cell Formulas
Range	Formula
G2	G2	=(D2*7*60)+(E2*60)+F2
G3:G6,G39:G41	G3	=G2+IF(AND(NOT(ISBLANK(D3)),NOT(ISBLANK(E3)),NOT(ISBLANK(F3))),(D3*7*60)+(E3*60)+F3,0)