how to reference a matrix?

[effebruno]

Hi all,
I have an Excel Sheet with a long list with some data, like this:

CODE MONTH VAL1 VAL2 ....
----------------------------------------
xxx 01 100 150 ...
xxx ....
xxx 12 200 120 ...
yyy 01 150 220 ...
...

so that I have n blocks of 12 rows each, representing some money values for one year for n persons (identified by the code value). The list is ordered by the 1st column and then by the 2nd one.

I wish to create a summary Sheet with the annual data only for a particular code the user have to type in a cell. I thought to identify the starting row with a vlookup() function but I can't find a way to reference an entire matrix starting at row x.

Could you give me some advice? Please tell me if you want me to explain better...

Thank you again,
Filippo

[TMS]

You could use MATCH to get the first row number for each block of data and then increment it for subsequent rows.

Regards, TMS

[effebruno]

Hi, thank you for the reply.

I thought something like

a) selecting the entire (12 rows x n columns) block in the summary sheet
b) entering one formula using somewhere a corresponding block in the list sheet
c) pressing Shift+Ctrl+Enter to confirm it as a matrix formula

That is, I would like not to copy and paste formulas all over the page, but use a matrix instead.

I made it!
Something like:

=INDIRECT("i"&MATCH(code;list_1st_col,0)&":k"&MATCH(code,list_1st_col,0)+12)

assuming the list block is on columns i:k, and code and list_1st_col are named ranges. To make that simple, I considered only 1 sheet.

btw, thank you again!

Filippo

[TMS]

See the attached example

Regards, TMS