• 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.

Index(Match issues

Jamie Wagler

Jamie Wagler

Member
Hello,

On my first page I have a shop calendar in weekly buckets.
PeriodWeek of
113-Jan-14
220-Jan-14
327-Jan-14
403-Feb-14
510-Feb-14
617-Feb-14
724-Feb-14

On the next I have a list of stat holidays
HolidayWhen
New Year's Day1-Jan-14
Family Day10-Feb-14
Good Friday18-Apr-14
Victoria Day19-May-14
Canada Day1-Jul-14
Labour Day1-Sep-14
Thanksgiving Day13-Oct-14
Remembrance Day11-Nov-14
Christmas Day25-Dec-14

I would like to populate the week with the holiday that falls within the week. Any suggestions?
 
Hi Jamie,

I would suggest you upload a sample of your spreadsheet so that we can see exactly what you are trying to achieve as the title and question isn't making sense to me :(
 
Hi, Jamie Wagler!

Give a look at the uploaded file. It has 3 tables, one for the week periods, other for the weekly calendar itself and another one for the holidays.

The formula for 2nd tab is:
=SI.ERROR(INDICE(Tabla3[Holiday];COINCIDIR(Tabla1[@[Week of]]+COLUMNA()-4;Tabla3[When];0));"") -----> in english: =IFERROR(INDEX(Tabla3[Holiday],MATCH(Tabla1[@[Week of]]+COLUMN()-4,Tabla3[When],0)),"")

Just advise if any issue.

Regards!
 

Attachments

  • Index(Match issues (for Jamie Wagler at chandoo.org).xlsx
    14.9 KB · Views: 10
Hi, Jamie Wagler!
And what about my blind shot? If it works do you think you can manage to handle the adaptation to your recently uploaded file? I hope so... so as to work less :)
Regards!
 
SirJB7,

I will see what I can do. This is a small portion of a much larger MRP project. Your model is a great example, and I will see if I can adjust it, but I have perimeters that I must work within.

Thanks!
 
SirJB7 said:
Hi, Jamie Wagler!
And what about my blind shot? If it works do you think you can manage to handle the adaptation to your recently uploaded file? I hope so... so as to work less :)
Regards!
Click to expand...

I have a macro which allows me to roll the vertical schedule up and down. I am trying to capture the holiday and have it roll along with the week. As I am only displaying a 26 week forecast but need to option to look ahead and behind.
 
Hi, Jamie Wagler!

Checking your file against mine, the column structure is exactly the same! 2 columns (A:B) for the periods, a blank column (C), 7 columns for the calendar (D:J), a blank column, 2 columns for the holidays (L:M). The only difference is the structured table notation vs. the range notation that you should use, but you can define dynamic named ranges that will be closer to my suggestion.

Just advise if further help is required, including what can and what can't be done: structured tables, dynamic ranged names, simple ranges, ...

Regards!
 
@NARAYANK991
Hi!
Checking your file I tried to set up more than 1 holiday in the same week (copying column D across) but I didn't succeed changing the formula. My best attempt is changing this:
MIN(FILA(Holidays_List))+1
by this:
MIN(FILA(Holidays_List))+COLUMNA()-3
but I get displayed holidays that aren't of that week.
Any clue?
Regards!
 
When I go to post the equation it is populating with the Jan 01 date and not the holiday... any suggestions?
 

Attachments

  • Question (1).xlsx
    18.8 KB · Views: 1
@SirJB7

I tried your solution and it wasn't able to populate my sheet as expected. Where am I getting turned around?
 

Attachments

  • Question.xlsx
    19.7 KB · Views: 1
Hi Jamie ,

Can you check your file ?

Hi Pablo ,

Replacing MIN by SMALL(....,1) , SMALL(....,2) ,... should do the job ; I have included Boxing Day as one of the holidays on the list.

Narayan
 

Attachments

  • Question _1.xlsx
    19.4 KB · Views: 5
Hi, Jamie Wagler!

Give a look at the uploaded file. It's your Question.xlsx file with 2 worksheets:
Sheet1, with fixed ranges addreses, so you'll have to adjust them whenever you add a holiday.
Sheet2, with dynamic named ranges, add or remove holidays and they get adjusted automatically.

Formula for D3 cell:
Sheet1: =SI.ERROR(INDICE($L$3:$L$11;COINCIDIR($B3+COLUMNA()-4;$M$3:$M$11;0));"") -----> in english: =IFERROR(INDEX($L$3:$L$11,MATCH($B3+COLUMN()-4,$M$3:$M$11,0)),"")
Sheet2: =SI.ERROR(INDICE(HolidayList;COINCIDIR($B3+COLUMNA()-4;WhenList;0));"") -----> in english: =IFERROR(INDEX(HolidayList,MATCH($B3+COLUMN()-4,WhenList,0)),"")

Regards!
 

Attachments

  • Index(Match issues - Question (for Jamie Wagler at chandoo.org).xlsx
    30.4 KB · Views: 9
Hi, Jamie Wagler!
SirJB7 said:
It's your Question.xlsx file with 2 worksheets:
Sheet1, with fixed ranges addreses, so you'll have to adjust them whenever you add a holiday.
Sheet2, with dynamic named ranges, add or remove holidays and they get adjusted automatically.
Click to expand...
Just check Sheet1.
Regards!
 
Hi Jamie ,

How would it be if we go through the entire setup together , and you can confirm why you are facing a problem ?

The file has 2 named ranges , which can be static or dynamic ; if they are defined to be dynamic , they will reflect addition / deletion of data , otherwise each time you make changes to the data , you will have to take care to redefine them in the Name Manager.

1. Holidays_List refers to =Sheet1!$M$3:INDEX(Sheet1!$M:$M,COUNTA(Sheet1!$M:$M)+1)

2. Annual_Holidays refers to =Sheet1!$L$3:INDEX(Sheet1!$L:$L,ROWS(Holidays_List)+2)

If you have problems making these dynamic , change the definitions to :

Holidays_List : =Sheet1!$M$3:$M$12

Annual_Holidays : =Sheet1!$L$3:$L$12

Once you have done this , the formula in the first output cell , say D3 would be :

=IFERROR(INDEX(Annual_Holidays,MIN(IF(ISNA(MATCH(Holidays_List,ROW(INDEX($B:$B,$B3):INDEX($B:$B,$B3+7-1)),0)),999,ROW(Holidays_List)-MIN(ROW(Holidays_List))+1))),"")

If you do not want to use named ranges , just replace the named ranges in the above formula by their static address references ; try this :

=IFERROR(INDEX(Sheet1!$L$3:$L$12,MIN(IF(ISNA(MATCH(Sheet1!$M$3:$M$12,ROW(INDEX($B:$B,$B3):INDEX($B:$B,$B3+7-1)),0)),999,ROW(Sheet1!$M$3:$M$12)-MIN(ROW(Sheet1!$M$3:$M$12))+1))),"")

Narayan
 
OK, I just tried to apply your equation and when I copy it down the 26 week period it populates weeks 11-26 with New Years. See the attached file.
 

Attachments

  • Holiday Error.xlsx
    15.4 KB · Views: 2
Hi Jamie ,

See your file now ; the mistakes were :

1. I had not mentioned explicitly that the formulae need to be array entered , using CTRL SHIFT ENTER.

2. Replacing entire column references , as in changing from $B:$B to $B$3:$B$65536 , meant that the starting point for the array became 3 instead of 1 ; this needed to be adjusted.

Narayan
 

Attachments

  • Holiday Error.xlsx
    15.2 KB · Views: 5
You must log in or register to reply here.
Back
Top