This problem has a bit of history, but I will cut it down to the bare
bones. I have a workbook that starts with three sheets called Semester
1, Semester Template, and Totals. I have a macro on Totals that creates
a sheet for the next semester before Semester Template, in this case it
would be Semester 2. Then, the macro inserts a number of columns equal
to the number of Mondays in the semester. For instance for Fall 2005,
there are 27 weeks from August 1-January 31 (I need our semesters to
overlap for the project, so Fall ends in January.) so there are 27
columns between A and the last column headed as Total. This Total
column for the semester sheet has an INDIRECT formula for summing that
particular row from B:[Total column - 1]. These totals work great. The
last the thing macro does is create a column on Totals to reference the
semester Total column from the new semester sheet that was created. In
the appropriate cell, I need to reference the corresponding category's
Total on the semester sheet. I have attempted to do this a number of
ways, but all to no avail. The code I have should work in my limited
understanding but it doesn't. Any help would be immensely appreciated.

ActiveCell.Value = "=INDIRECT(ADDRESS(4," & NewSemLastCol & ",,," &
NewSem &")"

The address I am referencing with the above code contains this formula:

=SUM(INDIRECT("B4:"&ADDRESS(ROW(),COLUMN()-1)))

NewSemLastCol is an integer variable that is equal to the number of
Mondays in the semester plus two, which yields that last column number
on the semester sheet. NewSem is a variable containing the name of the
sheet for the newly added semester. NewSem works for sure because I
have used it in another reference on the Totals sheet.

Sheets("Totals").Activate
Range("A1").Select
ActiveCell.Offset(0, LastSheet - 1).EntireColumn.Insert
ActiveCell.Offset(0, LastSheet - 1).Value = "='" & NewSem & "'!A2"
ActiveCell.Offset(0, LastSheet - 1).Columns.AutoFit

NewSem!A2 is the term and year, i.e. Fall 2005, Spring 2006, etc. on
the semester sheet. The code above puts the term and year as the column
heading in the Totals sheet's column for that semester's totals. The
only difference between NewSem!A2 and the cell I am trying to reference
is that A2 has a string while the cell I want to reference has the
SUM(INDIRECT()) formula from above. When I enter the cell reference in
Excel as

='Semester 2'!AC4 (which is what the reference works out to with the
NewSemLastCol and NewSem variables)

the total value from that cell shows up nicely in my Totals sheet. I
just cannot for the life of me get it to work in VBA. Thanks in advance
for your help. If I need to post more info/data, I will be happy to do
so.