I have 2 separate sheets: "Analysis" and "RawData". I am using the
Analysis page for various formulas that interpret the RawData.

This is the basic layout of the RawData sheet:

A B C D
1 Acct 1 Acct 2 Acct 3
2 Day 1 balance balance balance
3 Day 2 balance balance balance
4 Day 3 balance balance balance

I am looking at bank account balances on specific days. There are
roughly 100 accounts. Since their positions are subject to change, I
want to use dynamic formulas to analyze the data.

My Analysis page will have the account numbers in Row 1, just as they
are on the RawData page. However, they are not necessarily in the
same order, and they are subject to moving around.

Assuming that "Acct 1" is in cell B1 on the Analysis page, I want to
reference that account number and pull the average of all days for
that account from the RawData page.

Since I know the location of Acct 1, I could very simply just use
AVERAGE('RawData'!$B:$B). However, I will not know the column of
every account.

Thanks for your help.

Kevin