Hello All,
I have 96 cells with the following formula which causes slow excel perfomance.
I am looking for a way how to simplify it however i am not sure what would be the most CPU consuming part:
Code:
=SUMPRODUCT(GETPIVOTDATA("Sum of "&$A$7,'2014'!$A$12,"BookingDate",DATE(2013,10, 1)+7*INDIRECT("AUX!B1:B"&Departure!$B$3),"DepartureDate",DATE(2013,10,1)))+GETPIVOTDATA('2013'!$A$12,"DepartureDate['"&TEXT(B6, "mmm-yy")&"';Sum] 'Sum of "&$A$7&"'")
the red refers to the revenue, margin ... depending on the cell the formula is in
yellow refers to month
green is where i suppose the problem may be
basically i needed an dynamic array of sequential numbers depending on the user selected (cell B3) week
so the array would be {1, 2, ..., Cell B3}
the formula therefore call for cell values from B1 to B"week#" where are numbers from 0 to "week#"-1
now i have found out that it would be easier to refer to the row number rather than a cell value (which i assume will speed up the process)
I was however thinking about creating a public function that will return such array
This one works (tested with the above formula)
Code:
Function MYARRAY(x As Integer)
MYARRAY = Array(0, 1, 2, 3, 4)
End Function
now i need to recode that to dynamic one that will create an array output with sequential integer numbers from 0 till userinput
So far no success
Please help.
Thanks,
Kind regards,
Wries