Formula to Pull though data from first worksheet

Hi Friends

I have attached an example worksheet where I want to pull the data from Calculation tab to Store1 and Store2 tab. The yellow highlighted data needs to be pull through into Sheet "Store1" & "Store2".

I have large sets of data than this so just dumped the couple only. I am using OFFSET formula currently (as can be seen in the attached), however if there is any easy way to pull this data?

Thanks heaps for your assistance.

How many Stores and what is maximum number of Brands per store? Number of rows of data?

Try

"Store1"

in B1

=DATE(MID(B$1,3,4)+0,MONTH(INDEX(Calculation!$D$2:$O$2,,ROWS($1:1))&0),1)

in B2

=EOMONTH(B2,0)+1

copy down

in C2

=IFERROR(INDEX(Calculation!$D$4:$O$21,MATCH("Store1" &$A$1,Calculation!$A$4:$A$21&Calculation!$B$4:$B$21,0),ROWS($1:1)),"")

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

Copy down

NOTE: I added the "Store" to the row where the brand was so in "Calculations" Store2 is in A17.

The other Years have similar formulas, with the row increment as needed


in A16 (to check if more than one brand)

=IF(COUNTIF(Calculation!$A$4:$A$21,"store1")>1,INDEX(Calculation!$B$4:$B$21,SMALL(IF(Calculation!$A$4:$A$21="Store2",ROW(Calculation!$A$4:$A$21)-ROW($A$4)+1,""),2)),"")