Alternative to CTRL-SHIFT-ENTER Array Formula (follows previous post)

Hi Folks,

With much thanks to the experienced forum contributors, I thought I had solved most of my spreadsheet problems....however, one problem still remains regarding the use of an array reference - the solution was suggested by "donkeyote" in a previous post:

http://www.excelforum.com/excel-gene...ious-post.html

This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet.

Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?

Once again, many thanks to those who find the time to respond.

Given you know the following:

a) last time entered (row 3)
b) step between entries (defined in B2:B5 for respective columns)

You can in theory work in reverse:

O6: =INDEX($D$1:$D$51,(O$3+6)-($B$2*(COUNT(E$6:E$51)-1)),1)

When you copy the above to Q6, S6 & U6 you must alter $B$2 to be $B$3, $B$4, $B$5 respectively.

That should do it I think.

(You could make O6 such that it can be copied without adaptation but more hassle than worth really given you have only 4 cells in which the above resides)

I'm afraid I don't understand what you mean -- the +6 value equates to the Row number of time 0... it should never require alteration unless you move the position of the first time instance. Technically however the formula is flawed if your time values (D) increment by anything other than 1 and/or do not commence from 0. Given that fact the following is safer:

O6: =INDEX($D$1:$D$51,MATCH(O$3,$D$6:$D$51,0)+ROW($D$5)-($B$2*(COUNT(E$6:E$51)-1)),1)
Q6: =INDEX($D$1:$D$51,MATCH(Q$3,$D$6:$D$51,0)+ROW($D$5)-($B$3*(COUNT(G$6:G$51)-1)),1)
S6: =INDEX($D$1:$D$51,MATCH(S$3,$D$6:$D$51,0)+ROW($D$5)-($B$4*(COUNT(I$6:I$51)-1)),1)
U6: =INDEX($D$1:$D$51,MATCH(U$3,$D$6:$D$51,0)+ROW($D$5)-($B$5*(COUNT(K$6:K$51)-1)),1)

The above approach is based on the premise that the values in E,G,I & K are stepped in accordance with the value as indicated in B2:B5 respectively... ie if B2 is 3 (for Col E) you won't have anything other than 2 blank rows between values, were the value of B2 4 you wouldn't have anything other than 3 blank rows between values... were that not to hold true I'm afraid I fail to see the point of B2:B5 values.

Perhaps if you could post up an example using the above whereby the results are in error that would help me ascertain the problem ?

Thanks