I have done this before, but it has been several years and I cannot recall
exactly how I did it. I am now trying to do this in Excel 2002.

I have Sheets (exported to Excel from another application) for each month
(named Jan 2006, Feb 2006, Mar 2006, etc). On another sheet, I have Columns
for each month (Jan, Feb, Mar, etc) for a side by side comparison. I need a
formula that references the column heading to determine which sheet to get
the data from. I.e. - ='Aug 2006'!B4 is the formula for the Aug 2006 column
row 4, but in the next column (Jul 2006) it should be ='Jul 2006'!B4.

I have used CONCATENATE (&) to create a formula that will will evaluate and
return the cell contents as indicated above. The following is close, but the
end result displays the formula I want evaluated.

="='"&B1&"'!"B4 yields ='Aug 2006'!B4 displayed (rather than the contents of
cell B4 from the sheet named Aug 2006). I am hoping to avoid the need to
find/replace in each column after copying the formula to the right by simply
having the formula refer to the column heading which corresponds to the name
of the sheet from which to retrieve the data. I know the concatenation is
creating the correct formula. How do I get Excel to evaluate the resulting
formula?

Thanks for any help.