If your source data starts in row 3 down,
make a small arithmetic adjustment to the earlier extract expression
In the new sheet,
In B2: =IF(x!B30,ROW(),"")
Copy B2 across by 28 cols, fill down as far as required
Then in AK2, use this instead:
=IF(ROWS($1:1)COUNT(B:B),"",INDEX(x!$A:$A,SMALL(B :B,ROWS($1:1))+1))
Copy AK2 across by 28 cols, fill down to the same extent, to return the
required results, all neatly packed at the top.
[ ROWS($1:1))+1 replaces ROWS($1:1) within the SMALL part ]
The above should work ok. Please press the YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Dom" wrote:
Hi Max,
This seems to be working but bring back the wrong values because
my data doesn't start in the columns said. My "fruits" starts in A3 and
"Months" Headings start in B2. What would the formula be? Thanks for your
help with this.