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]

There is a small error but besides that it works like the greatest charm in the world!!!

After row 443 it seems to pull an error. I've attached the image. Please let me know what I'm doing wrong!!!

 

[kiyo052]

NEVERMIND!!!! WORKS GREAT!!! My mistake was that I put the year as 2013 and not 2014. Once I changed that and went back to dynamic name range everything worked like charm.


LADIES AND GENTLEMEN, THIS IS MARKMZZ 4th PROJECT WITH ME!!! YOU CAN COUNT ON THIS PERSON TO HELP YOU AND GO THE EXTRA MILE!!!! MARKMZZ, IN MY BOOK, IS AN EXCEL GURU!! THANK YOU MARKMZZ!!!

 

[markmzz]

NEVERMIND!!!! WORKS GREAT!!! My mistake was that I put the year as 2013 and not 2014. Once I changed that and went back to dynamic name range everything worked like charm.


LADIES AND GENTLEMEN, THIS IS MARKMZZ 4th PROJECT WITH ME!!! YOU CAN COUNT ON THIS PERSON TO HELP YOU AND GO THE EXTRA MILE!!!! MARKMZZ, IN MY BOOK, IS AN EXCEL GURU!! THANK YOU MARKMZZ!!!

Hi Kiyo052,

You're welcome and I'm glad that your problem was solved again.

And thank you for the nice words.

Markmzz

 

[kiyo052]

Markmzz,

I made a modification to the calendar and tracker. I tried to use your formulas from the thread but I keep pulling an Error. Could you please send me an updated version of the formula? I really appreciate it!

Here is the calendar:

And tracker:

This is what I'm using for the dynamic name range:

MyData: =Calendar!$C$2:INDEX(Calendar!$AG:$AG,MATCH("ZZZZZ",Calendar!$B:$B)-7)


Please let me know if you can help I really appreciate your knowledge and time. Thanks again!

 

[markmzz]

Try this:

Names

MyData - Refers To: =Calendar!$C$2:INDEX(Calendar!$AH:$AH,MATCH("ZZZZZ",Calendar!$B:$B))

MyHead - Refers To: =Calendar!$C$2:$D$2

MySlot - Refers To: =Calendar!$B$3:$B$10

MyWeek - Refers To: =COUNTA(Calendar!$C$1:$AK$1)

MyWeeks - Refers To: =COUNTA(Calendar!$B:$B)/MyWeek

Formulas

In A2

=IF(ROWS($A$2:$A2)>MyWeeks*COUNTA(MySlot)*MyWeek,"",IF(ROWS($A$2:$A2)=1,Calendar!$C$2,$A1+IF(MOD(ROWS($A$2:$A2)-1,COUNTA(MySlot)),0,1)))

In C2

=IF($A2="","","E"&MOD(ROWS($C$2:$C2)-1,COUNTA(MySlot))+1)


In B2

=IF($A2="","",INDEX(MyData,
SMALL(IF((MOD(ROW(MyData)-ROW(INDEX(MyData,1,1)),ROWS(MySlot))=0)*(MyData=$A2),ROW(MyData)-ROW(INDEX(MyData,1,1))+1),1)+RIGHT($C2,1),
SMALL(IF((MOD(ROW(MyData)-ROW(INDEX(MyData,1,1)),ROWS(MySlot))=0)*(MyData=$A2),COLUMN(MyData)-COLUMN(INDEX(MyData,1,1))+1),1)+ISNUMBER(MATCH(B$1,MyHead,0)))&"")

And copy to D2.

Markmzz

 

[markmzz]

A small modification in my last post:

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

=IF($A2="","",INDEX(IF(MyData="","",MyData),
SMALL(IF((MOD(ROW(MyData)-ROW(INDEX(MyData,1,1)),ROWS(MySlot))=0)*(MyData=$A2),ROW(MyData)-ROW(INDEX(MyData,1,1))+1),1)+RIGHT($C2,1),
SMALL(IF((MOD(ROW(MyData)-ROW(INDEX(MyData,1,1)),ROWS(MySlot))=0)*(MyData=$A2),COLUMN(MyData)-COLUMN(INDEX(MyData,1,1))+1),1)+ISNUMBER(MATCH(B$1,MyHead,0))))

And copy to D2.

Markmzz

 

[kiyo052]

I've copied it to D2 and it's showing the duplicate of B2. What part of the formula is suppose to chage for D2?

This is amazing by the way. I knew I could always count on you. Thank you again for all your help!

 

[markmzz]

I've copied it to D2 and it's showing the duplicate of B2. What part of the formula is suppose to chage for D2?

This is amazing by the way. I knew I could always count on you. Thank you again for all your help!

Here all is ok.

Did you created all the names?

Markmzz

 

[kiyo052]

I know what I did wrong. The name in D2 in the calendar has to be the same as the D2 in the tracker. I had the calendar say Name and the tracker say Buyer. Thank you so much for your help!!!! I really appreciate all that you've done for me!!

 

[markmzz]

I know what I did wrong. The name in D2 in the calendar has to be the same as the D2 in the tracker. I had the calendar say Name and the tracker say Buyer. Thank you so much for your help!!!! I really appreciate all that you've done for me!!

In the post #24 you used Name in D2 (sheet Calendar) and Name again in D1 (sheet Tracker).

Anyway, you are welcome and thanks for the feedback.

Markmzz