Dynamic reference to a sheet
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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