Using indirect to refer to a row

[laurafv]

Hello,

I have a list of employees (one person per row). The columns are attendance to events (each attendance has a different number), and I would like to create a table that sumarises the attendance ranked chronologically.

To do so, I would need that in the formula below instead of the red part (now referring to row 7) there is a link to another cell that I have set so that it automatically finds the row corresponding to an employee. Would an indirect formula work? how? I tried with no success...

=match(C5,'v2'!7:7,0)


Thanks in advance,
Laura

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Jonmo1]

Try

=MATCH(C5,INDEX('v2'!A:IV,anothercell,0),0)

Another cell is a cell containing the 7

Change IV to XFD if in XL2007+
Or better, change IV to the last occupied column of the v2 sheet.

 

[Oeldere]

not an answer to your question, but have you tried pivot table for that kind of work?

 

[laurafv]

Thanks! tomorrow I will try at the office and let you know if it works
I dont want to use a pivot table because the dataset is not in the right format (each column is an event), but maybe I will have to investigate if this is a better option...

 

[laurafv]

thanks, it works you saved my project !

Try

=MATCH(C5,INDEX('v2'!A:IV,anothercell,0),0)

Another cell is a cell containing the 7

Change IV to XFD if in XL2007+
Or better, change IV to the last occupied column of the v2 sheet.

 

[Jonmo1]

You're welcome.