Calendar Tracker Formula Help

[kiyo052]

Excel 2010

So I don't know how to explain this without having you download the worksheet. SORRY!!

If you download the worksheet, I was wondering if you could guide me to a formula where sheet 1 would equal sheet 2 without having to manually input sheet 2 = sheet 1 for each row. It's practically a chore wheel/ cooking wheel/ tracker.

Thanks to all those who help!!!!

https://sites.google.com/site/upload988/Calendar Tracker Example.xlsx?attredirects=0&d=1

 

[kiyo052]

It's going to be the same format. On the Calendar tab, I would be adding rows and changing the dates.

It's not meant to look like a real calendar, just something to continually add to.


Hope that helps!!!

 

[markmzz]

It's going to be the same format. On the Calendar tab, I would be adding rows and changing the dates.

It's not meant to look like a real calendar, just something to continually add to.


Hope that helps!!!

Could you post the calendar for October/2013?

Markmzz

 

[kiyo052]

Here is October,

As you can see the calendar format doesn't change. Every month I'm just going to keep that format. Please let me know if I need to provide anything else and I will do as soon as I can. Thank you for your help!!!!!

 

[markmzz]

Maybe this (for Calendar 10/2013):

Layout (Tracker-10)

Slot	Date	Room	Name
E1	29/09/2013	Kitchen	Lisa
E2	29/09/2013	Living Room	Bart
E3	29/09/2013	2nd Floor	Peter
E4	29/09/2013	Bathroom	Chris
E5	29/09/2013	Basement	Megan
E6	29/09/2013	Attic	Brian
E1	30/09/2013
E2	30/09/2013
E3	30/09/2013
E4	30/09/2013
E5	30/09/2013
E6	30/09/2013
E1	01/10/2013
E2	01/10/2013
E3	01/10/2013
E4	01/10/2013
E5	01/10/2013
E6	01/10/2013
******	***********	**********	*********

<colgroup><col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <tbody>
</tbody>

Formulas

In A2 - use only Enter to enter the formula 

=IF(B2="","","E"&MOD(ROWS(A$2:A2)-1,6)+1)

And copy down.

In B2 - use only Enter to enter the formula

=IF(A1="Slot",'Calendar-10'!$B$2,IF(A1="E6",IF(ROWS(B$2:B2)<=COUNTA('Calendar-10'!$A:$A)*7,B1+1,""),B1))

And copy down.

In C2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(B2="","",OFFSET('Calendar-10'!$A$2,
SMALL(IF('Calendar-10'!$B$2:$AF$34=$B2,ROW('Calendar-10'!$B$2:$AF$34)-ROW('Calendar-10'!$B$2)+1),1)+RIGHT($A2,1)-1,
SMALL(IF('Calendar-10'!$B$2:$AF$34=$B2,COLUMN('Calendar-10'!$B$2:$AF$34)-COLUMN('Calendar-10'!$B$2)+1),1)+COLUMNS($C2:C2)-1)&"")

And copy to D2 and then copy the range C2:D2 down.

Markmzz

 

[markmzz]

A small modification in my last formulas:

In A2 - use only Enter to enter the formula 

=IF(ROWS(A$2:A2)>COUNTA('Calendar-10'!$A:$A)*7,"","E"&MOD(ROWS(A$2:A2)-1,6)+1)

And copy down.

In B2 - use only Enter to enter the formula

=IF(A1="Slot",'Calendar-10'!$B$2,IF(A1="E6",IF(A2="","",B1+1),B1))

And copy down.

In C2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(B2="","",OFFSET('Calendar-10'!$A$2,
SMALL(IF('Calendar-10'!$B$2:$AF$34=$B2,ROW('Calendar-10'!$B$2:$AF$34)-ROW('Calendar-10'!$B$2)+1),1)+RIGHT($A2,1)-1,
SMALL(IF('Calendar-10'!$B$2:$AF$34=$B2,COLUMN('Calendar-10'!$B$2:$AF$34)-COLUMN('Calendar-10'!$B$2)+1),1)+COLUMNS($C2:C2)-1)&"")

And copy to D2 and then copy the range C2:D2 down.

Markmzz

 

[kiyo052]

Markmzz,

THIS LOOKS AMAZING!!!! It works great!!! I just have ONE small edit. I'm adding a row so instead of all of them having E6, I'm adding so there will be E7. Could you adjust the formula for me since I have no idea what to edit. Thanks again!!!! Also sorry for such a late response, gone for vacation. Thanks again for all your help!!!!!

 

[kiyo052]

I've added the image so you can see what I'm talking about. Thanks again for all your help!!!!

 

[markmzz]

Markmzz,

THIS LOOKS AMAZING!!!! It works great!!! I just have ONE small edit. I'm adding a row so instead of all of them having E6, I'm adding so there will be E7. Could you adjust the formula for me since I have no idea what to edit. Thanks again!!!! Also sorry for such a late response, gone for vacation. Thanks again for all your help!!!!!

Maybe this (for Calendar 10/2013 and the modification are in red):

In A2 - use only Enter to enter the formula 

=IF(ROWS(A$2:A2)>COUNTA('Calendar-10'!$A:$A)*7,"","E"&MOD(ROWS(A$2:A2)-1,[COLOR="#FF0000"]7[/COLOR])+1)

And copy down.

In B2 - use only Enter to enter the formula

=IF(A1="Slot",'Calendar-10'!$B$2,IF(A1="[COLOR="#FF0000"]E7[/COLOR]",IF(A2="","",B1+1),B1))

And copy down.

In C2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(B2="","",OFFSET('Calendar-10'!$A$2,
SMALL(IF('Calendar-10'!$B$2:$AF$[COLOR="#FF0000"]38[/COLOR]=$B2,ROW('Calendar-10'!$B$2:$AF$[COLOR="#FF0000"]38[/COLOR])-ROW('Calendar-10'!$B$2)+1),1)+RIGHT($A2,1)-1,
SMALL(IF('Calendar-10'!$B$2:$AF$[COLOR="#FF0000"]38[/COLOR]=$B2,COLUMN('Calendar-10'!$B$2:$AF$[COLOR="#FF0000"]38[/COLOR])-COLUMN('Calendar-10'!$B$2)+1),1)+COLUMNS($C2:C2)-1)&"")

And copy to D2 and then copy the range C2:D2 down.

Markmzz

 

[kiyo052]

This works great!!

I have one last request if it is possible: please let me know if you don't know and I will try to find another way. So here it is:

Everything on the formula looks great, my question is: is it possible to make the calendar a dynamic name range? So instead of always having to manually change $AF$38 to wherever the next month ends, I want to just input a dynamic name range where no matter how many weeks I continue to add, it will update automatically.

Please let me know if I need to be clearer.

Thanks so much for your help Markmzz, I trust and count on you so much!

 

[markmzz]

This works great!!

I have one last request if it is possible: please let me know if you don't know and I will try to find another way. So here it is:

Everything on the formula looks great, my question is: is it possible to make the calendar a dynamic name range? So instead of always having to manually change $AF$38 to wherever the next month ends, I want to just input a dynamic name range where no matter how many weeks I continue to add, it will update automatically.

Please let me know if I need to be clearer.

Thanks so much for your help Markmzz, I trust and count on you so much!

Try this:

Create this name:

MyData - Refers to: ='Calendar-10'!$B$2:INDEX('Calendar-10'!$AF:$AF,MATCH("ZZZZZ",'Calendar-10'!$A:$A)-7)

And use this formula:

In C2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IF(B2="","",OFFSET('Calendar-10'!$A$2,
SMALL(IF(MyData=$B2,ROW(MyData)-ROW(INDEX(MyData,1,1))+1),1)+RIGHT($A2,1)-1,
SMALL(IF(MyData=$B2,COLUMN(MyData)-COLUMN(INDEX(MyData,1,1))+1),1)+COLUMNS($C2:C2)-1)&"")

And copy to D2 and then copy the range C2:D2 down.

Markmzz