Is there an "universal worksheet name", which I can use in formulas,
but independent from the actual displayed name?
My my first sheet named "Main" contains a table to change the names of
other sheets, e.g. "Berlin" ind B2, "Paris" in B3. Finally, in a last
"Summary" sheet I want to refer to the sheets with variable names.
(Another user might work with other towns.)
So, I could use INDIRECT(Main!B2&"!C13") to find out the
actual name of
a sheet from the table of the "Main" sheet. And I could use VBA to
change the tab name whenever the name is changed in B2.
BUT: If I insert a row or so in one of the sheets with variable name,
my refreing formula will not be changed automatically (in the above
example to INDIRECT(Main!B2&"!C14"). And I have to do this cumbersome
The 2nd solution would be to use not INDIRECT but straightly Berlin!C13
and if the name is changed in B2 on "Main", e.g. from "Berlin" to
"Madrid" I could use VBA not only to change the tab name, but also to
change the formulas in sheet "Summary" to Madrid!C13. Now also the
formula would change automatically if the users inserts a new row in
the "Madrid" sheet.
BUT: As I use many formulas this would be a bit slow.
If I open the sheet properties in the visual basic editor, each sheet
has not only the property "name", which is the name displayed on the
tab andthe one you can use in formulas. It also has s property "(name)"
with fixed values like "Sheet1", "Sheet2" etc. This -somehow fixed and
not displayed - name I would like to use in a refering formula.
Is this possible?
What is the exact notation?
Thank you for your kind help.