Index & Match Help for date lookup

[sjgidman82]

Hi Any help would be appreciated

I am trying to get the dates on this sheet to auto fill with a formula when I create a new weeks sheet

1.png

When I create a sheet the week number is automatically added based on the name of the sheet
e.g Sheet named WK 01 will display 01 in the Week cell

The year will be a manual input

what I would like is based on we know the week,year, day is for the dates to be worked out from a table on a different sheet (Ignore Numbers Row this will be entered manually each week)

2.png

I have attached the workbook so you can see what i mean

[Glenn Kennedy]

Confused.

What do you want to see? Where do you want to see it?

Why have you posted a cut-down sheet with no room for experimenting with formulae?

[sjgidman82]

I need the the formula on the date row (Row 5) above each day and not sure what you mean about cut down sheet, i've only hidden columns and row not being used

[shukla.ankur281190]

tRY

d3=SUBSTITUTE(MID(CELL("filename",B1),SEARCH("]",CELL("filename",B1))+1, SEARCH("]",CELL("filename",B1))-SEARCH("[",CELL("filename",B1))-1),"WK ","")+0

d5=INDEX('Date & Week'!$D$2:$D$736,SMALL(IF($D$3='Date & Week'!$A$2:$A$736,IF('Date & Week'!$B$2:$B$736=$D$4,ROW('Date & Week'!$A$2:$A$736)-ROW('Date & Week'!$A$2)+1)),1))

Change above part with 2,3,4,5,6,7

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[Glenn Kennedy]

1. Reverse the order of the table in Date & week (ascending).

2. Demerge the date cells in Wk 01 row 5.

3. Use this formula in D5:

=IF(MOD((COLUMNS($D:D)-1),3)=0,INDEX(Date_Week[[Date]:[Date]],MATCH(1,INDEX((Date_Week[[Week]:[Week]]=$D$3+0)*(Date_Week[[Year]:[Year]]=$D$4),0),0)+INT((COLUMNS($D:D)-1)/3)),"")

drag across.

4. In D6: =TEXT(D5,"dddd") copied across.

5. if you REALLY want to you can now individually remerge the sets of 3 cells in row 5. I despise merged cells.... so have left you to do that.

I cannot seem to be able to upload your file again. I'll try again later.

[sjgidman82]

Thank you shukla.ankur281190 that was great he only thing i had to change was with the aecond formula, it gave me fridays date when put in d5 so i just counted backwards starting with 7 down to 1

[Glenn Kennedy]

Glad you got sorted!! there's something weird with your file. Over here, it bloated to 2.55 Mb and I can't reduce the filesize back to normal. It keeps adding in 1,000,000 rows on the first sheet.

[sjgidman82]

Thanks for your help also Glenn i have tried your way too and got both working, i too hate merged cells but i am trying to make this sheet as easy and tidy as possible for others to use. Not sure why it bloated that big even with adding in the rest of the 52 tabs for the weeks its only a few hundred kb here

Glad you got sorted!! there's something weird with your file. Over here, it bloated to 2.55 Mb and I can't reduce the filesize back to normal. It keeps adding in 1,000,000 rows on the first sheet.