In the attached (stolen from
ArviLaanemets) there's a yellow cell E2 which currently has the formula pointing to cell B18. This is for your starting cell adjustment.
The numbers in the first row from columns F to J are the numbers of results you want in the columns below. For you to adjust.
The formulae which do the work are in cells F13 to J13. Depending on your version of Excel, you may have to array-enter these formulae (commit to the sheet using
Ctrl+Shift+Enter rather than the more usual
Enter), what's more you may have to select, for example, cells F13:F40 while array-entering that formula to cater for the greatest number of results you might want to see in that column; this might result in several
#N/A errors in the cell which you could hide with:
Code:
=IFERROR(OFFSET(INDIRECT(MID(FORMULATEXT($E$2),2,99)),SUM($E$1:E$1),0,F$1),"")
The current versions of Excel don't need all that, just the entry of the formula into F13 alone.
Whichever way you have to do it, you can copy across.
I've used
MID(FORMULATEXT($E$2),2,99)
to get the address of a cell from the formula; you might find it easier to type in B18 into that cell without the preceding
= sign and change the formula in F13 to a more straightforward:
Code:
=OFFSET(INDIRECT($E$2),SUM($E$1:E$1),0,F$1)