indirect function with r1c1 style

[Zim]

=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)

 

[Sheeloo]

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.

 

[Dave Peterson]

Maybe it's because you used R81C3 as opposed to $c$83. Row 81 is different from
row 83.

=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)

 

[Zim]

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.

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...

=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)

 

[Zim]

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