return sheet name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robin - 19 Sep 2006 04:26 GMT

How can I open a file with multiple sheets and have a formula or code return
the names of the sheets so i can perform calculations on varying sheets with
out manually having to input them into my formulas.

Example: i open a file created by anouther user that has 7 or 8 sheets with
different names depending on the data in the sheets. I want to be able for my
formulas to adjust to those names with having to input them in manually.

Reply to this Message

Carim - 19 Sep 2006 07:15 GMT

Hi Robin,

Following formula does the job ...

=MID(CELL("filename",A1), FIND("]", CELL("filename",A1))+ 1, 255)

HTH
Cheers
Carim

Reply to this Message

Stopher - 19 Sep 2006 07:16 GMT

> How can I open a file with multiple sheets and have a formula or code return
> the names of the sheets so i can perform calculations on varying sheets with
[quoted text clipped - 3 lines]
> different names depending on the data in the sheets. I want to be able for my
> formulas to adjust to those names with having to input them in manually.

My guess is that the sheets you are doing the calcs on have standard
names, and that this is true for each file.

If that is true then you could create a macro that uses the standard
names of the sheets, regardless of the worksheet name. Or to make it
more transportable you could create an Add-In with functions that
reference specific sheets in any workbook.

So

Function ABC()
ABC = [Insert Macro or formula here]
End function

And then any sheet you type =ABC it will do the calc.

This is a very simple no specific example, and also assumes that your
files are in an exact format, or you will have to start introducing
named ranges and calling them in your formula as well.

Reply to this Message