Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
How do I reference values from 200 worksheets onto a summary sheet | Excel Discussion (Misc queries) | |||
How do I reference non-contiguous columns in another sheet and th. | Excel Discussion (Misc queries) | |||
Need formula for sheet & cell reference | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |