Return TEXT values from one table to another by given WEEK and YEAR

[freedox]

Hi Excel Gurus,

I have tryed to work this out for a some time already but no success... I hope someone would have solution

Ok, so I want that let's say IF I choose week 15 Year 2015, information from table: NewLaunches (in Data Entry Tab) will be feeded to the main Dashboard table "New Brand Additions".

In this example,according to Data Entry tab information should look like this:
Dashboard.jpg
DataEntry.jpg
Enrichment Reporting Schedule NEW v1.5.xlsx

This is the last piece I need to solve in this endless jigsaw...

[JohnTopley]

See attached. I had to remove your merged Cells (never use when needed for formulae!).

[freedox]

Speechless.... This is absolutely amazing! Cheers for that! No words to describe my thankfulness!

[JohnTopley]

FYI: it might be better if the WEEK number cell was simply a number rather than the current format of "WEEK nn". You could add the text "WEEK" in an adjoining cell.

I used the RIGHT function to get the number from the text: test it with a week < 10 to check it works OK.

[freedox]

Hi John,

I'm bit worried to mess it up other formulas if I change Week Number to a number. Unless we are talking about different things...

Also I have added all valid info to the sheet and it seems that new brand additions are not showing up information correctly.. Weeks 13, 14, 18, 22, 23, 24 are not apprearing in the table, only Weeks 15 and 17 showing up..

Enrichment Reporting Schedule NEW v1.6.xlsx

[JohnTopley]

See attached with extended ranges.

[JohnTopley]

Adjust formula as data starts at row 8 not 11 (my mistake)


=IFERROR(INDEX('Data Entry'!$M$8:$M$100,SMALL(IF((YEAR('Data Entry'!$K$8:$K$100)=YearNumber)*('Data Entry'!$P$8:$P$100=RIGHT(Dashboard!$C$6,2)+0)*('Data Entry'!$L$8:$L$100=Dashboard!D$34),ROW('Data Entry'!$M$8:$M$100)-7,""),ROWS($A$1:$A1))),"")

Enter with CTRL+SHIFT+ENTER

[freedox]

John, it works as a charm!!! Absolute gentlemen! Thanks for all of this!

[JohnTopley]

I would clear out the unwanted data in columns K to Q in "Data Entry" as they will cause error conditions