Indirect Function
If AA1 = 1, the below would refer to the sheet 'Summary by Unit & Class 1'
row 44 column 2.
=OFFSET(INDIRECT("'Summary by Unit & Class "&$AA$1&"'!A1"),
ROW(C45)-1,COLUMN(B47))
--
Jacob
"Mike H." wrote:
I have a lot of formulas in a sheet that look like this:
=INDIRECT("'" &"Summary by Unit & Class "&$AA$1 &"'!" &
columnletter(COLUMN(B47)) & ROW(C45)-1)
When I open the file, oftentimes these formulas have #Value in them. I just
hit F9 and it goes away. The calculation mode is automatic and I don't want
end users having to refresh to get rid of the errors in cells. Ideas?
Note: Columnletter is a UDF:
Function ColumnLetter(ByVal colNum As Long) As String
Do
ColumnLetter = Chr$(65 + (colNum - 1) Mod 26) & ColumnLetter
colNum = (colNum - 1) \ 26
Loop While colNum 0
End Function
|