• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help multiple monthly sheet planner calendar

R

richboom

New Member
hi, i really need help
i m using chandoo calender with event planner..

i created each month plan in 12 separate sheet : Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov,Dec
each sheet contain 7 column : date, time, event, location, program, add on, remark

in the yearly tab, if i click on any date, it will auto show the daily event at right side memo.. let say i click 25dec, it will show those events created in sheet dec with date 25. display in 5 column :event,location,time,program,add on

can u help me in the formula.. as i use indirect/index, but it only show sheet Jan, but not other sheet month

tq tq tq
 
Post your file example - it will make it easier for people to assist. Otherwise we have to *try* to duplicate your worksheet and it's not always easy to do so from a description ....

Santa
 
Hi,

Perhaps you need an INDIRECT in cell AA8:

=IFERROR(INDEX(INDIRECT(TEXT($AC$3,"mmm")&"!D7:D27"),SMALL(IF(selectedCell=INT(INDIRECT(TEXT($AC$3,"mmm")&"!A7:A27")),MATCH(ROW(Jan!$A$7:$A$27),ROW(Jan!$A$7:$A$27)),""),ROW(A1)),COLUMN(A1)),"")

{array formula}

Regards,
 
hi khalid
thank you thank you thank you.. it works

how about the rest .. MATCH(ROW(Jan!$A$7:$A$27),ROW(Jan!$A$7:$A$27))..

do i need to change it to each Sheet! ? or just leave it to Jan! sheet ??
 
Hi,

Since it is generating an array of:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21}

You don't even need Jan!
MATCH(ROW($A$7:$A$27),ROW($A$7:$A$27)) will serve the purpose.


However, I just realize you can remove the MATCH function to make it more shorter, the Red highlighted will also generate the same array:

=IFERROR(INDEX(INDIRECT(TEXT($AC$3,"mmm")&"!D7:D27"),SMALL(IF(selectedCell=INT(INDIRECT(TEXT($AC$3,"mmm")&"!A7:A27")),ROW($A$7:$A$27)-6,""),ROW(A1)),COLUMN(A1)),"")

Regards,
 
hi khalid

i need one more help please...
is it possible to add in one more criteria, if selectedCell equal to date, and if column AA7 equal to "month sheet" column C3:C27, the rest is same.

thank you once again..
 
richboom said:
if selectedCell equal to date, and if column AA7 equal to "month sheet" column C3:C27
Click to expand...

Good day @richboom

May be this:

=IFERROR(INDEX(INDIRECT(TEXT($AC$3,"mmm")&"!D7:D27"),SMALL(IF(selectedCell=INT(INDIRECT(TEXT($AC$3,"mmm")&"!A7:A27"))*($AA$7= INDIRECT(TEXT($AC$3,"mmm")&"!C7:C27")),ROW($A$7:$A$27)-6,""),ROW(A1)),COLUMN(A1)),"")

Not tested...

Regards,
 
You must log in or register to reply here.
Back
Top