Help combing formula

[Liverlee]

Hi i have set up a calendar tracker

This formula works for recurring weekly appointments where 1 = Mon, 2= tue, wed = 3 etc etc

=IF(AB$12=$G12,1,IF(AB$12=$H12,2,IF(AB$12=$I12,3,IF(AB$12=$J12,4, IF(AB$12=$K12,5,"")))))

On row Ab1 I have week of month set up, i.e 1,2,3,4,5.

So i'd like to be able to set up the above formula where you can select week day (either 1 to 5) and then week of month ( either 1 to 5)

anyone have any ideas.

 

[Liverlee]

p.s i want to be able to select week of month in cells v to z

so if i put a 1 in cell v13 = 1st week of month, a 2 in cell w13 = 2nd week of month, a 3 in cell x13 =3rd week of month from ROW A1 etc etc

 

[jasonb75]

That makes somewhere between little sense and no sense.

Bear in mind that none of us can see your screen or read your mind, we can only work with what you give us.

In Post 1, the formula refers to row 12 only. In post 2 you refer to V13, W13, etc. Does this mean that it is spread over 2 rows, or should it be V12, W12, etc?

In Post 1, you mention 'ROW AB1'. AB1 is not a row. 1 is a ROW, AB is a column, AB1 is a cell. Incorrect descriptions will get you incorrect answers. You do the same with ROW A1 in post2.

At minimum, you will need to provide 2 or 3 accurate examples of where the week and day references are, and what the expected result of the formula should be.

 

[Liverlee]

That makes somewhere between little sense and no sense.

Bear in mind that none of us can see your screen or read your mind, we can only work with what you give us.

In Post 1, the formula refers to row 12 only. In post 2 you refer to V13, W13, etc. Does this mean that it is spread over 2 rows, or should it be V12, W12, etc?

In Post 1, you mention 'ROW AB1'. AB1 is not a row. 1 is a ROW, AB is a column, AB1 is a cell. Incorrect descriptions will get you incorrect answers. You do the same with ROW A1 in post2.

At minimum, you will need to provide 2 or 3 accurate examples of where the week and day references are, and what the expected result of the formula should be.

Ok i'll try again. So my spreadsheet looks something like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]row 10[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]row 11[/TD]
[TD]Start[/TD]
[TD]end [/TD]
[TD]mon[/TD]
[TD]tue[/TD]
[TD]wed[/TD]
[TD]thu[/TD]
[TD]fri[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD]T[/TD]
[TD]F[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD]T[/TD]
[TD]F[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]row 12[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


in column E and F i have start and end dates and a formula that works for that.

Calendar dates start from column AB runs until column IH on row 10.

So if i enter start date 29/08/2019 in column E and end date in Column F 30/08/2019 a cell is filled somewhere on row 12.

in columns G through to K i have 5 cells that when you type in 1, 2, 3, 4,5 will populate recurring daily/weekly events from column AB onwards and formula that works for that.

in column L to P i have 5 cells that when clicked on populate fortnightly recurring events and a formula that works for that.

And in the same vein i'm trying to populate monthly recurring events in column q through to z.

in cells q12 if you enter 1 - you have selected monday.
in cell r12 if you enter 2 - you have selected tue
in cell s12 if you enter 3 - you have selected wed
in cell t12 if you enter 4 - you have selected thur
in cell u12 if you enter 5 - you have selected fri.

in cells v12 if you enter 1 - you have selected 1st week of month
in cell w12 if you enter 2 - you have selected 2nd week of month
in cell x12 if you enter 3 - you have selected 3rd week of month
in cell y12 if you enter 4 - you have selected 4th week of month
in cell z12 if you enter 5 - you have selected 5th week of month

the week of month numbers can be found in row 1 start in column AB through IH says in each cell whether its week 1, 2, 3, 4, or 5.

I don't know if this is possible or if there are two many variables.

 

[jasonb75]

How do you define first week of the month?

Looking at Augst 2019, is the 1st (thursday) August week 1, or is it the last week of July?

 

[Liverlee]

Aug 27, 28, 29, 30, 31 & 1st Sept (which is a sun) is week 5.
Mon 2nd Sept is week 1.

Thanks for taking the time to answer my questions by the way.

 

[jasonb75]

So does that mean that week 1 always starts on the first monday of the month, or can it be the last monday of the previous month in some cases?

In the case of the latter, how do you decide which?

 

[Liverlee]

Hi Jason, to work out the the week of the month i used this formula. https://www.youtube.com/watch?v=4fFCDoCUnzw

Row 1 states from column ab onwards says whether it's week 1,2,3,4,5.

2nd sept = week 1
9 sept = week 2
16 sept =week 3
23 sept = week 4
30 sept = week 5
2nd oct = week 1

and so on and so forth

 

[Liverlee]

This is what it looks like

https://m.imgur.com/a/0afWJaD

 

[jasonb75]

I'm not going to watch a drawn out youtube video to find a formula that you could have posted to the forum.

I'm sure there must be a better way to achieve what you're trying to do (which I'm still trying to figure out).