Indirect Function
 

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

Indirect Function
 

First thing I would do is get rid of the ColumnLetter UDF.

=INDIRECT("'Summary by Unit & Class "&$AA$1&"'!R"
&row(c45)-1&"C"&column(B47),false)

To see if that helps.

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

--

Dave Peterson

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

Indirect Function
 

correction.

If AA1 = 1, the below would refer to the sheet 'Summary by Unit & Class 1'
row 45 column 2

=OFFSET(INDIRECT("'Summary by Unit & Class "&$AA$1&"'!A1"),
ROW(C45)-1,COLUMN(B47)-1)


--
Jacob


"Jacob Skaria" wrote:

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

Indirect Function
 

Do I have to have the excel options set to R1C1-type formulas to get this to
work? I can't seem to get your formula to return anything but #REF!. Ideas?

"Dave Peterson" wrote:

First thing I would do is get rid of the ColumnLetter UDF.

=INDIRECT("'Summary by Unit & Class "&$AA$1&"'!R"
&row(c45)-1&"C"&column(B47),false)

To see if that helps.

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

--

Dave Peterson
.

Indirect Function
 

=indirect() has a second parm that indicates whether your string is in A1
reference style or R1C1 reference style.

I'm guessing that the value in AA1 creates a worksheet name that doesn't exist.

Mike H. wrote:

Do I have to have the excel options set to R1C1-type formulas to get this to
work? I can't seem to get your formula to return anything but #REF!. Ideas?

"Dave Peterson" wrote:

First thing I would do is get rid of the ColumnLetter UDF.

=INDIRECT("'Summary by Unit & Class "&$AA$1&"'!R"
&row(c45)-1&"C"&column(B47),false)

To see if that helps.

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

--

Dave Peterson
.


--

Dave Peterson