| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect function with r1c1 style
=INDIRECT("'[Direct FTC Tracking 2009.xls]"&R2C2&"'!R81C3",FALSE)
This is the formula I am trying to use. What I am trying to accomplish is to return the value of Cell (R81C3) on variable Sheet (R2C2), in the workbook [Direct FTC Tracking 2009.xls] (Sheets are monthly and the reference of R2C2 is the current sheet date reference, which exactly matches the name of the sheets from the workbook [Direct FTC Tracking 2009.xls]) The formula returns #REF! Before I went to R1C1 style (for ease of VBA use) this formula worked like a charm and looked like this =INDIRECT("'[Direct FTC Tracking 2009.xls]"&$a$2&"'!$c$83",FALSE) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect function with r1c1 style
You need to check the R1C1 Reference Style check box for your formula to
work... INDIRECT has a flag to indicate the type of reference... but to build the string you have to use the A1 style unless you check the box mentioned above... Here is the navigation, just in case you don't know - To specify which notation format you want to use, follow these steps if you are using a version of Excel prior to Excel 2007: 1. Choose Options from the Tools menu. Excel displays the Options dialog box. 2. Make sure the General tab is selected. (Click here to see a related figure.) 3. If you want to use R1C1 format, select the R1C1 Reference Style check box; if you want to use A1 format (the default for Excel), clear the check box. 4. Click on OK. If you are using Excel 2007, follow these steps instead: 1. Click the Office button and then click on Excel Options. Excel displays the Excel Options dialog box. 2. At the left side of the dialog box, click Formulas. (Click here to see a related figure.) 3. If you want to use R1C1 format, select the R1C1 Reference Style check box; if you want to use A1 format (the default for Excel), clear the check box. 4. Click on OK. ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Zim" wrote: =INDIRECT("'[Direct FTC Tracking 2009.xls]"&R2C2&"'!R81C3",FALSE) This is the formula I am trying to use. What I am trying to accomplish is to return the value of Cell (R81C3) on variable Sheet (R2C2), in the workbook [Direct FTC Tracking 2009.xls] (Sheets are monthly and the reference of R2C2 is the current sheet date reference, which exactly matches the name of the sheets from the workbook [Direct FTC Tracking 2009.xls]) The formula returns #REF! Before I went to R1C1 style (for ease of VBA use) this formula worked like a charm and looked like this =INDIRECT("'[Direct FTC Tracking 2009.xls]"&$a$2&"'!$c$83",FALSE) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect function with r1c1 style
Maybe it's because you used R81C3 as opposed to $c$83. Row 81 is different from
row 83. Zim wrote: =INDIRECT("'[Direct FTC Tracking 2009.xls]"&R2C2&"'!R81C3",FALSE) This is the formula I am trying to use. What I am trying to accomplish is to return the value of Cell (R81C3) on variable Sheet (R2C2), in the workbook [Direct FTC Tracking 2009.xls] (Sheets are monthly and the reference of R2C2 is the current sheet date reference, which exactly matches the name of the sheets from the workbook [Direct FTC Tracking 2009.xls]) The formula returns #REF! Before I went to R1C1 style (for ease of VBA use) this formula worked like a charm and looked like this =INDIRECT("'[Direct FTC Tracking 2009.xls]"&$a$2&"'!$c$83",FALSE) -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect function with r1c1 style
This is done. Actually this is what cause my formula to malfunction. I
switched to the R1C1 (under tools, properties, general, check mark) because of the ease of working in VBA. When I did this, I lost this formula. I have 2 other similar to this, but they reference only in the same workbook. That is the only thing that I can see different. "Sheeloo" wrote: You need to check the R1C1 Reference Style check box for your formula to work... INDIRECT has a flag to indicate the type of reference... but to build the string you have to use the A1 style unless you check the box mentioned above... Here is the navigation, just in case you don't know - To specify which notation format you want to use, follow these steps if you are using a version of Excel prior to Excel 2007: 1. Choose Options from the Tools menu. Excel displays the Options dialog box. 2. Make sure the General tab is selected. (Click here to see a related figure.) 3. If you want to use R1C1 format, select the R1C1 Reference Style check box; if you want to use A1 format (the default for Excel), clear the check box. 4. Click on OK. If you are using Excel 2007, follow these steps instead: 1. Click the Office button and then click on Excel Options. Excel displays the Excel Options dialog box. 2. At the left side of the dialog box, click Formulas. (Click here to see a related figure.) 3. If you want to use R1C1 format, select the R1C1 Reference Style check box; if you want to use A1 format (the default for Excel), clear the check box. 4. Click on OK. ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Zim" wrote: =INDIRECT("'[Direct FTC Tracking 2009.xls]"&R2C2&"'!R81C3",FALSE) This is the formula I am trying to use. What I am trying to accomplish is to return the value of Cell (R81C3) on variable Sheet (R2C2), in the workbook [Direct FTC Tracking 2009.xls] (Sheets are monthly and the reference of R2C2 is the current sheet date reference, which exactly matches the name of the sheets from the workbook [Direct FTC Tracking 2009.xls]) The formula returns #REF! Before I went to R1C1 style (for ease of VBA use) this formula worked like a charm and looked like this =INDIRECT("'[Direct FTC Tracking 2009.xls]"&$a$2&"'!$c$83",FALSE) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
indirect function with r1c1 style
Sorry to waste your time. I closed all of the workbooks , reopened and
retyped the formula. Works now! Must have had a typo? Thanks for the help guys Zim "Zim" wrote: =INDIRECT("'[Direct FTC Tracking 2009.xls]"&R2C2&"'!R81C3",FALSE) This is the formula I am trying to use. What I am trying to accomplish is to return the value of Cell (R81C3) on variable Sheet (R2C2), in the workbook [Direct FTC Tracking 2009.xls] (Sheets are monthly and the reference of R2C2 is the current sheet date reference, which exactly matches the name of the sheets from the workbook [Direct FTC Tracking 2009.xls]) The formula returns #REF! Before I went to R1C1 style (for ease of VBA use) this formula worked like a charm and looked like this =INDIRECT("'[Direct FTC Tracking 2009.xls]"&$a$2&"'!$c$83",FALSE) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using INDIRECT & R1C1 Ref style | Excel Worksheet Functions | |||
| Sum Indirect Using R1C1 Style | Excel Worksheet Functions | |||
| How do I change sheet notation from R1C1 style to A1 style in XL 2 | Setting up and Configuration of Excel | |||
| can a1 reference style and r1c1 style be used in same formula? | Excel Worksheet Functions | |||
| R1C1 reference style | Excel Discussion (Misc queries) | |||
Linear Mode
