Go to entered date in Excel for Mac 2016

[renpilot]

Hello all experts,

I have tried some VBA solutions I found but they don't work (or maybe I am doing something wrong). I read that Excel for Mac is not as advanced, so maybe that is the reason.

I am trying to make Excel for Mac 2016 go to a date that I enter in A2. The date can be in rows b4:nc4 or b11:nc11 or b18:nc18 as I am displaying the days for three years in these rows. The entered dates in the three rows are dd/mm/yyyy but are displayed as dd/mmm to save space. The entered date is in format dd/mm/yy.

I would like this functionality as it is easier to navigate all these dates when an input has to be made in a cell below the specific date. Column A is frozen so the identifiers will always be visible when scrolling through to any given date.

Hope you have ideas

 

[renpilot]

Sorry to bother you again, but the curser doesn't jump to the cell with the date, but the cell just below. E.g. it does't jump to a cell in range b4:nc4 but to d4:nc4.

Any ideas?

 

[renpilot]

Another thing to consider is to organize your data differently. I don't know what you actual data in rows A, B, C, D, E are but you could try this:
To create a table with these columns:

Date	A	B	C	D	E
01/01/2024	10	5	9	3	5
02/01/2024	3	6	8	10	3
03/01/2024	3	7	7	1	7
04/01/2024	5	3	10	2	4
05/01/2024	8	8	5	9	2
06/01/2024	2	1	8	3	2
07/01/2024	6	4	6	6	5
08/01/2024	3	8	1	10	9
09/01/2024	7	8	8	4	3
10/01/2024	4	6	1	3	9
11/01/2024	5	9	1	9	3

With dates from 01/01/2024 to 31/12/2026, and in the A - E columns you data.

Then you could filter the table by date for example

View attachment 103145

And you'll get this:

View attachment 103146

You also can do lookups. Enter a date in one cell and get values from columns A to E.

Here is a (hopefully) working example: DataAsTable.zip

Let me know what you think.

I don't think that will work as I need it as a calendar showing occupied dates over any of the years...

 

[felixstraube]

Sorry to bother you again, but the curser doesn't jump to the cell with the date, but the cell just below. E.g. it does't jump to a cell in range b4:nc4 but to d4:nc4.

Any ideas?

It does that because the cells below, has the same date just another format.
You could fix it by transforming the formula below the date, for example in cell B5 to this:

=TEXT(B4, "ddd")

And copy across. Do the same for row 13. In row 21 it wont be necessary because it isn't in the dataset considered.

And how about this other example: DataAsTable2.zip

Here you have you data in Sheet1 and in Sheet2 you can enter a date and it populates the columns to the left with the next dates. And if you need to show more dates, just select columna I and copy to the right. No need to click a hyperlink, just input the date.
If you need to show the data of the other years, that can be done also if you need.

DataAsTable2.xlsx
	B	C	D	E	F	G	H	I
2		Enter date
3		02/01/2024	03/01/2024	04/01/2024	05/01/2024	06/01/2024	07/01/2024	08/01/2024
4		mar	mié	jue	vie	sáb	dom	lun
5	A	3	3	5	8	2	6	3
6	B	6	7	3	8	1	4	8
7	C	8	7	10	5	8	6	1
8	D	10	1	2	9	3	6	10
9	E	3	7	4	2	2	5	9
Hoja2

Cell Formulas
Range	Formula
D3:I3	D3	=C3+1
C4:I4	C4	=TEXT(C3,"ddd")
C5:I9	C5	=INDEX(Tabla1,MATCH(C$3,Tabla1[[Date]:[Date]],0),MATCH($B5,Hoja1!$A$1:$F$1,0))

 

[felixstraube]

Something like this:

DataAsTable2.xlsx
	B	C	D	E	F	G	H	I
2		Enter date
3	2024	02/01/2024	03/01/2024	04/01/2024	05/01/2024	06/01/2024	07/01/2024	08/01/2024
4		mar	mié	jue	vie	sáb	dom	lun
5	A	3	3	5	8	2	6	3
6	B	6	7	3	8	1	4	8
7	C	8	7	10	5	8	6	1
8	D	10	1	2	9	3	6	10
9	E	3	7	4	2	2	5	9
10
11	2025	02/01/2025	03/01/2025	04/01/2025	05/01/2025	06/01/2025	07/01/2025	08/01/2025
12		jue	vie	sáb	dom	lun	mar	mié
13	A	2	9	2	1	4	2	3
14	B	10	3	4	2	1	6	5
15	C	4	10	3	1	7	7	10
16	D	5	10	3	1	10	2	1
17	E	8	8	10	9	3	4	8
18
19	2026	02/01/2026	03/01/2026	04/01/2026	05/01/2026	06/01/2026	07/01/2026	08/01/2026
20		vie	sáb	dom	lun	mar	mié	jue
21	A	3	1	4	3	2	4	3
22	B	1	8	7	8	4	10	7
23	C	9	7	2	9	2	5	3
24	D	6	8	2	5	6	6	2
25	E	4	3	7	8	4	8	2
Hoja2

Cell Formulas
Range	Formula
B3,B19,B11	B3	=YEAR(C3)
D3:I3,D19:I19,D11:I11	D3	=C3+1
C4:I4,C20:I20,C12:I12	C4	=TEXT(C3,"ddd")
C5:I9	C5	=INDEX(Tabla1,MATCH(C$3,Tabla1[[Date]:[Date]],0),MATCH($B5,Hoja1!$A$1:$F$1,0))
C11	C11	=DATE(2025,MONTH(C3),DAY(C3))
C13:I17	C13	=INDEX(Tabla1,MATCH(C$11,Tabla1[[Date]:[Date]],0),MATCH($B13,Hoja1!$A$1:$F$1,0))
C19	C19	=DATE(2026,MONTH(C3),DAY(C3))
C21:I25	C21	=INDEX(Tabla1,MATCH(C$19,Tabla1[[Date]:[Date]],0),MATCH($B21,Hoja1!$A$1:$F$1,0))

 

[renpilot]

Ok thanks that is advanced. What I need is an overview of a full year with indications whether a date is occupied by A or B or C or D or E. I looked online for a sort of booking calendar, but they all cost a good bit of money. So I tried myself...
The one I liked best displayed a full year and then colour coded triangles in the upper corner on each date corresponding to occupied or not. Don't know if it can be done with 6 conditions (not occupied, A, B, C, D, E). I will have a look at your suggestion above.
Have attached a screenshot of a colour coded one giving an easy overview through the colours so a year is not 365 cells in a row but rather kind of a matrix. I suspect this is heavy VBA coding so that's why I tried my own amateur layout