open view

[JOEE1979]

Merry Christmas to all.

I have a sheet that is about 200 pages long.
When I open the page, instead of it starting at the top and I have to scroll down,
Is it possible to have the table start where "L2" and column "A" would match?

schedule 4 tracker.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		Schedule 4 Tracker										2-Jan-24
3		Unit	Oil	WMA	Other	Other2	Scheduled Date	Start Date	End Date	Expired Date	Actual duration (days)	Notes
4	1-Jan-24	BUS-2432					1-Jan-24			31-Jan-24
5		BUS-2437					1-Jan-24			31-Jan-24
6		BUS-2438					1-Jan-24			31-Jan-24
7		BUS-2439					1-Jan-24			31-Jan-24
8		BUS-2441					1-Jan-24			31-Jan-24
9		BUS-2442					1-Jan-24			31-Jan-24
10		BUS-2445					1-Jan-24			31-Jan-24
11		BUS-2446					1-Jan-24			31-Jan-24
12		BUS-2447					1-Jan-24			31-Jan-24
13		BUS-2448					1-Jan-24			31-Jan-24
14		BUS-2449					1-Jan-24			31-Jan-24
15		BUS-2450					1-Jan-24			31-Jan-24
16		BUS-2451					1-Jan-24			31-Jan-24
17		BUS-2452					1-Jan-24			31-Jan-24
18		BUS-2453					1-Jan-24			31-Jan-24
19	2-Jan-24	BUS-2454					2-Jan-24			1-Feb-24
20		BUS-2455					2-Jan-24			1-Feb-24
21		BUS-2456					2-Jan-24			1-Feb-24
22		BUS-2457					2-Jan-24			1-Feb-24
23		BUS-2458					2-Jan-24			1-Feb-24
24		BUS-2459					2-Jan-24			1-Feb-24
25		BUS-2460					2-Jan-24			1-Feb-24
26		BUS-2461					2-Jan-24			1-Feb-24
27		BUS-2462					2-Jan-24			1-Feb-24
28		BUS-2463					2-Jan-24			1-Feb-24
29		BUS-2464					2-Jan-24			1-Feb-24
30		BUS-2465					2-Jan-24			1-Feb-24
31		BUS-2466					2-Jan-24			1-Feb-24
32		BUS-2467					2-Jan-24			1-Feb-24
33		BUS-2468					2-Jan-24			1-Feb-24
Master

Cell Formulas
Range	Formula
J4:J33	J4	=[@[Scheduled Date]]+30
K4:K33	K4	=IF(IF(COUNTA(Master!$H4,Master!$I4)<>2,"",DAYS360(Master!$H4,Master!$I4,FALSE))=0,"SAME DAY",(IF(COUNTA(Master!$H4,Master!$I4)<>2,"",DAYS360(Master!$H4,Master!$I4,FALSE))))
G4,G19	G4	=A4
G5,G20	G5	=A4
G6,G21	G6	=A4
G7,G22	G7	=A4
G8,G23	G8	=A4
G9,G24	G9	=A4
G10,G25	G10	=A4
G11,G26	G11	=A4
G12,G27	G12	=A4
G13,G28	G13	=A4
G14,G29	G14	=A4
G15,G30	G15	=A4
G16,G31	G16	=A4
G17,G32	G17	=A4
G18,G33	G18	=A4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C32:F45	Other Type	Icon set		NO
C18:F31	Other Type	Icon set		NO
C4:F17	Other Type	Icon set		NO
A4:A5493	Expression	=($L$2=A4)	text	NO
B4:B5493	Expression	=AND($L$2=G4, (ISBLANK(I4)))	text	NO
B4:B5493	Expression	=AND($L$2>G4, (ISBLANK(I4)))	text	NO
B4:B5493	Expression	=AND($L$2>G4, NOT(ISBLANK(I4)))	text	NO
K4:K5493	Expression	=(ABS((K4-#REF!))/#REF!)>FlagPercent	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B3	Any value
F3	Any value
G3	Any value
H3	Any value
I3:J3	Any value
K3	Any value
L3	Any value
A1	Any value

 

[rollis13]

If you need to scroll to the date very time you open your file then you could add this macro to your ThisWorkBook module.

Option Explicit
Private Sub Workbook_Open()
    Sheets("Master").Range("A" & Cells.Find(What:=Sheets("Master").Range("L2"), SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues).Row).Select
End Sub

 

[rollis13]

Sorry: the above code should've been:

Option Explicit
Private Sub Workbook_Open()
    Sheets("Master").Activate
    Range("A" & Cells.Find(What:=Range("L2"), SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row).Select
End Sub

-----
Or, if you need to scroll very time you move to the sheet "Master" then use only this other macro to be pasted in the "Master" module:

Option Explicit
Private Sub Worksheet_Activate()
    ActiveSheet.Range("A" & Cells.Find(What:=ActiveSheet.Range("L2"), SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row).Select
End Sub

 

[JOEE1979]

Is there a way to use it without marcos?

 

[rollis13]

As far as I know, you can't do without a macro.