The following CSE function gives a value error
=TRANSPOSE(OFFSET(Data!$A$1,(ROW()-1)*9,0,9,1))
but if I move the section (ROW()-1)*9 out to its own cell and then reference that cell in the CSE function
=TRANSPOSE(OFFSET(Data!$A$1,K6,0,L2,1))
it works fine.
Is there a way to include the row argument in the CSE function?
You don't specify the cell in which the first formula appears - if we assume (using your example references) that it's say B6 on a sheet other than Data then
The above unlike the TRANSPOSE approach is not an Array and can be confirmed with ENTER as normal, also given avoidance of OFFSET it is also non-volatile.Code:B6: =INDEX(Data!$A:$A,COLUMNS($B6:B6)+(ROWS(B$1:B6)-1)*9) copied across to J6 B6:J6 copied up/down as required
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks