Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using INDIRECT Function and INDEX Function | Excel Discussion (Misc queries) | |||
INDIRECT function | Excel Discussion (Misc queries) | |||
Indirect function help please | Excel Worksheet Functions | |||
using the INDIRECT function | Excel Discussion (Misc queries) | |||
INDIRECT function inside AND function | Excel Worksheet Functions |