Returns the reference specified by a text string. References are immediately
evaluated to display their contents. Use INDIRECT when you want to change the
reference to a cell within a formula without changing the formula itself.

Here is the general way to make the sheet name (in your case a number) a
variable:

Say we have the formula: =Sheet2!A1
but we want to use a cell reference rather than the hard-coded Sheet2.
Use instead:
=INDIRECT(B2 & "!A1") and put Sheet2 in cell B2

If your sheetname has spaces then
=INDIRECT("'" & B2 & "'!A1")

If this post helps click Yes
---------------
Jacob Skaria


"Ric" wrote:

Thank you Shane, could you please tell me what is the INDIRECT function and
how i can use?
thank you again...
"Shane Devenshire" wrote:

If the other files are going to be open you should look at the INDIRECT
function.

FYI - replace =+ with just =
and replace " " with ""

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ric" wrote:

I have this formula:
=+IF(VLOOKUP($C1,'C:\Documents and Settings\ricardo\My
Documents\DPR-Ajax\[2005-01.xlsx]Data Presence
Report.rdl'!$B$5:$J$89,4,0)=0," ",VLOOKUP($C1,'C:\Documents and
Settings\ricardo\My Documents\DPR-Ajax\[2005-01.xlsx]Data Presence
Report.rdl'!$B$5:$J$89,4,0)),
and i want to use this formula along of a worksheet, but i need to change
the table_array creating a new sweries like:
=+IF(VLOOKUP($C1,'C:\Documents and Settings\ricardo\My
Documents\DPR-Ajax\[2005-02.xlsx]Data Presence
Report.rdl'!$B$5:$J$89,4,0)=0," ",VLOOKUP($C1,'C:\Documents and
Settings\ricardo\My Documents\DPR-Ajax\[2005-02.xlsx]Data Presence
Report.rdl'!$B$5:$J$89,4,0)),
then, in each new colum change the table_array name, increasing as
2005-03,2005-04,2005-05, etc.
Thank you.