Dynamic reference to a sheet

Hi!

Enter this formula in A3 and copy down as needed:

=INDIRECT("'"&A$1&" ("&A$2&")'!A"&ROW(1:1))

Biff

"xisque" <xisque@discussions.microsoft.com> wrote in message
news:D5C4297E-C3AF-4484-8842-A7FC3780FDDF@microsoft.com...
>A have the following sheet :
>
> A1 | Day
> A2 | 1
> A3 |- (cel a1 from sheet Day (1) -
> A4 |- (cel a2 from sheet Day (1) -
> A5 |- (cel a3 from sheet Day (1) -
> A6 |- (cel a4 from sheet Day (1) -
> A7 |- (cel a5 from sheet Day (1) -
> A8 |- (cel a6 from sheet Day (1) -
>
>
> And I have 60 days, I want to make a formula that automaticaly reference
> the
> correct sheet according the number above Day . The name of the sheet will
> be
> something like Day (n), where n is the number in the cel A2.
>
> Something like cel A3 :
>
> ='Day (value of cel A2)'!A1
>

That was tuff, I tried with indirect, but my excel is in PT-BR, so I spend
some time trying to "translate" that formula to my language.

=INDIRETO("'"&A$1&" ("&A$2&")'!A"&LIN(1:1))

Thanks, that was the answer. Do you know if there is a dictionary to
translate excel functions to my language ?



"Biff" escreveu:

> Hi!
>
> Enter this formula in A3 and copy down as needed:
>
> =INDIRECT("'"&A$1&" ("&A$2&")'!A"&ROW(1:1))
>
> Biff
>
> "xisque" <xisque@discussions.microsoft.com> wrote in message
> news:D5C4297E-C3AF-4484-8842-A7FC3780FDDF@microsoft.com...
> >A have the following sheet :
> >
> > A1 | Day
> > A2 | 1
> > A3 |- (cel a1 from sheet Day (1) -
> > A4 |- (cel a2 from sheet Day (1) -
> > A5 |- (cel a3 from sheet Day (1) -
> > A6 |- (cel a4 from sheet Day (1) -
> > A7 |- (cel a5 from sheet Day (1) -
> > A8 |- (cel a6 from sheet Day (1) -
> >
> >
> > And I have 60 days, I want to make a formula that automaticaly reference
> > the
> > correct sheet according the number above Day . The name of the sheet will
> > be
> > something like Day (n), where n is the number in the cel A2.
> >
> > Something like cel A3 :
> >
> > ='Day (value of cel A2)'!A1
> >
>
>
>

>Thanks, that was the answer. Do you know if there is a dictionary to
>translate excel functions to my language ?

Sorry, I have no idea.

Thanks for the feedback!

Biff

"xisque" <xisque@discussions.microsoft.com> wrote in message
news:16D7656F-D23A-4F08-8E82-0B6E603D99BD@microsoft.com...
> That was tuff, I tried with indirect, but my excel is in PT-BR, so I spend
> some time trying to "translate" that formula to my language.
>
> =INDIRETO("'"&A$1&" ("&A$2&")'!A"&LIN(1:1))
>
> Thanks, that was the answer. Do you know if there is a dictionary to
> translate excel functions to my language ?
>
>
>
> "Biff" escreveu:
>
>> Hi!
>>
>> Enter this formula in A3 and copy down as needed:
>>
>> =INDIRECT("'"&A$1&" ("&A$2&")'!A"&ROW(1:1))
>>
>> Biff
>>
>> "xisque" <xisque@discussions.microsoft.com> wrote in message
>> news:D5C4297E-C3AF-4484-8842-A7FC3780FDDF@microsoft.com...
>> >A have the following sheet :
>> >
>> > A1 | Day
>> > A2 | 1
>> > A3 |- (cel a1 from sheet Day (1) -
>> > A4 |- (cel a2 from sheet Day (1) -
>> > A5 |- (cel a3 from sheet Day (1) -
>> > A6 |- (cel a4 from sheet Day (1) -
>> > A7 |- (cel a5 from sheet Day (1) -
>> > A8 |- (cel a6 from sheet Day (1) -
>> >
>> >
>> > And I have 60 days, I want to make a formula that automaticaly
>> > reference
>> > the
>> > correct sheet according the number above Day . The name of the sheet
>> > will
>> > be
>> > something like Day (n), where n is the number in the cel A2.
>> >
>> > Something like cel A3 :
>> >
>> > ='Day (value of cel A2)'!A1
>> >
>>
>>
>>

xisque wrote...
....
>Thanks, that was the answer. Do you know if there is a dictionary to
>translate excel functions to my language ?
....

There may be such a dictionary, but you could do this with VBA. If you
name an unlocked cell XYZ, you could use a macro like

Sub x()
Dim f0 As String, f1 As String

f1 = InputBox(Prompt:="Enter formula", Title:="Formula Translation")

If f1 <> "FALSE" Then
f0 = Names("XYZ").RefersToRange.Formula
Names("XYZ").RefersToRange.Formula = f1
MsgBox Prompt:=Names("XYZ").RefersToRange.FormulaLocal, _
Title:="Formula Translation"
Names("XYZ").RefersToRange.Formula = f0
End If

End Sub

A have the following sheet :

A1 | Day
A2 | 1
A3 |- (cel a1 from sheet Day (1) -
A4 |- (cel a2 from sheet Day (1) -
A5 |- (cel a3 from sheet Day (1) -
A6 |- (cel a4 from sheet Day (1) -
A7 |- (cel a5 from sheet Day (1) -
A8 |- (cel a6 from sheet Day (1) -


And I have 60 days, I want to make a formula that automaticaly reference the
correct sheet according the number above Day . The name of the sheet will be
something like Day (n), where n is the number in the cel A2.

Something like cel A3 :

='Day (value of cel A2)'!A1

Hi!

Enter this formula in A3 and copy down as needed:

=INDIRECT("'"&A$1&" ("&A$2&")'!A"&ROW(1:1))

Biff

"xisque" <xisque@discussions.microsoft.com> wrote in message
news:D5C4297E-C3AF-4484-8842-A7FC3780FDDF@microsoft.com...
>A have the following sheet :
>
> A1 | Day
> A2 | 1
> A3 |- (cel a1 from sheet Day (1) -
> A4 |- (cel a2 from sheet Day (1) -
> A5 |- (cel a3 from sheet Day (1) -
> A6 |- (cel a4 from sheet Day (1) -
> A7 |- (cel a5 from sheet Day (1) -
> A8 |- (cel a6 from sheet Day (1) -
>
>
> And I have 60 days, I want to make a formula that automaticaly reference
> the
> correct sheet according the number above Day . The name of the sheet will
> be
> something like Day (n), where n is the number in the cel A2.
>
> Something like cel A3 :
>
> ='Day (value of cel A2)'!A1
>

That was tuff, I tried with indirect, but my excel is in PT-BR, so I spend
some time trying to "translate" that formula to my language.

=INDIRETO("'"&A$1&" ("&A$2&")'!A"&LIN(1:1))

Thanks, that was the answer. Do you know if there is a dictionary to
translate excel functions to my language ?



"Biff" escreveu:

> Hi!
>
> Enter this formula in A3 and copy down as needed:
>
> =INDIRECT("'"&A$1&" ("&A$2&")'!A"&ROW(1:1))
>
> Biff
>
> "xisque" <xisque@discussions.microsoft.com> wrote in message
> news:D5C4297E-C3AF-4484-8842-A7FC3780FDDF@microsoft.com...
> >A have the following sheet :
> >
> > A1 | Day
> > A2 | 1
> > A3 |- (cel a1 from sheet Day (1) -
> > A4 |- (cel a2 from sheet Day (1) -
> > A5 |- (cel a3 from sheet Day (1) -
> > A6 |- (cel a4 from sheet Day (1) -
> > A7 |- (cel a5 from sheet Day (1) -
> > A8 |- (cel a6 from sheet Day (1) -
> >
> >
> > And I have 60 days, I want to make a formula that automaticaly reference
> > the
> > correct sheet according the number above Day . The name of the sheet will
> > be
> > something like Day (n), where n is the number in the cel A2.
> >
> > Something like cel A3 :
> >
> > ='Day (value of cel A2)'!A1
> >
>
>
>

>Thanks, that was the answer. Do you know if there is a dictionary to
>translate excel functions to my language ?

Sorry, I have no idea.

Thanks for the feedback!

Biff

"xisque" <xisque@discussions.microsoft.com> wrote in message
news:16D7656F-D23A-4F08-8E82-0B6E603D99BD@microsoft.com...
> That was tuff, I tried with indirect, but my excel is in PT-BR, so I spend
> some time trying to "translate" that formula to my language.
>
> =INDIRETO("'"&A$1&" ("&A$2&")'!A"&LIN(1:1))
>
> Thanks, that was the answer. Do you know if there is a dictionary to
> translate excel functions to my language ?
>
>
>
> "Biff" escreveu:
>
>> Hi!
>>
>> Enter this formula in A3 and copy down as needed:
>>
>> =INDIRECT("'"&A$1&" ("&A$2&")'!A"&ROW(1:1))
>>
>> Biff
>>
>> "xisque" <xisque@discussions.microsoft.com> wrote in message
>> news:D5C4297E-C3AF-4484-8842-A7FC3780FDDF@microsoft.com...
>> >A have the following sheet :
>> >
>> > A1 | Day
>> > A2 | 1
>> > A3 |- (cel a1 from sheet Day (1) -
>> > A4 |- (cel a2 from sheet Day (1) -
>> > A5 |- (cel a3 from sheet Day (1) -
>> > A6 |- (cel a4 from sheet Day (1) -
>> > A7 |- (cel a5 from sheet Day (1) -
>> > A8 |- (cel a6 from sheet Day (1) -
>> >
>> >
>> > And I have 60 days, I want to make a formula that automaticaly
>> > reference
>> > the
>> > correct sheet according the number above Day . The name of the sheet
>> > will
>> > be
>> > something like Day (n), where n is the number in the cel A2.
>> >
>> > Something like cel A3 :
>> >
>> > ='Day (value of cel A2)'!A1
>> >
>>
>>
>>

xisque wrote...
....
>Thanks, that was the answer. Do you know if there is a dictionary to
>translate excel functions to my language ?
....

There may be such a dictionary, but you could do this with VBA. If you
name an unlocked cell XYZ, you could use a macro like

Sub x()
Dim f0 As String, f1 As String

f1 = InputBox(Prompt:="Enter formula", Title:="Formula Translation")

If f1 <> "FALSE" Then
f0 = Names("XYZ").RefersToRange.Formula
Names("XYZ").RefersToRange.Formula = f1
MsgBox Prompt:=Names("XYZ").RefersToRange.FormulaLocal, _
Title:="Formula Translation"
Names("XYZ").RefersToRange.Formula = f0
End If

End Sub