Hi there!

I have formulas like this in a worksheet:

=SUMPRODUCT(--(Sheet2!$L$1:$L462=$B3),--(Sheet2!$AO$1:$AO462<20))
=C3/(COUNTIF(Sheet2!$L$1:$L462,$B3))

When the columns on "Sheet2" change, this wouldn't be very "modular" or
friendly.

I was wondering if I can incorporate something like INDEX or MATCH in these
formulas. Here's some detail:

"Sheet2!$L$1" refers to the "EmployeeName" column. This column always has
the header in row 1, but the column can be different every time. Can this
be something like "MATCH(xxxxx)"? (I'm not too familiar with these formulas.)

The ":$L462" can also be different - This is the end of the column. It's
never more than 6000 rows, but it does change every time.

(Just ignore the $B3 in the formulas - It's a reference point for the user
to change which data they want to count/sum/etc.)

If I knew how to change those two formulas by adding something like MATCH or
INDEX(MATCH), I think I can manipulate the rest of my formulas accordingly,
but I'm not sure how to use them in a format like this.

Any ideas?

Thank you!

Ronny Hamida