Link formulas to Worksheet names

  • Thread starter Thread starter Landyman
  • Start date Start date
L

Landyman

Is it possible to link formulas into worksheet names. I do weekly
reporting where the tab name is a date. What I want to do is run a
formula from the tab name.
I have used the cell "filename" function, and this works fine, but then
I run into difficulties when I move copies of the sheet to other books
as I include 4 workbook sheets into a new workbook, and they obviously
cannot have the same name. I can modify my cell "filename" function to
cope, but was wondering if there was an easier way.

I tried Walkenbach Sheetname function, but couldnt get this to run.

CHeers
 
I'm guessing you're using a formula like this:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
to obtain the worksheet name.

And since you said the worksheet name is a date, I'm gonna guess that you
formatted the date (=worksheetname) as mm-dd-yyyy

When you copied that worksheet to a new workbook that already had a worksheet by
that same name, that copied sheet took a name like:
mm-dd-yyyy (2)

So couldn't you use a formula that would only look at the first 10 characters of
the sheet name:

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

or

=left(a1,10)
if A1 was the cell that held the worksheet name.

(Depending on how the worksheet was named, you may have to adjust that 10.)

If this doesn't work, you may want to be more specific:
What are the worksheet names that cause trouble?
Why John Walkenbach's UDF didn't work for you.
 
Back
Top