software

Excel - I would like to refer to the sheet tab name on the spreadsheet...I
know you can use the simple (tab) command in the header, but what would be
the quot;formulaquot; you would put in a cell to refer to the sheet tab name...ie
={tab}?

Use the following formula:

=MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,A1)) 1,32)

The file must have been saved to disk for this to work.--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;donaldDquot; gt; wrote in message
...
gt; Excel - I would like to refer to the sheet tab name on the
gt; spreadsheet...I
gt; know you can use the simple (tab) command in the header, but
gt; what would be
gt; the quot;formulaquot; you would put in a cell to refer to the sheet tab
gt; name...ie
gt; ={tab}?
That did it...Awesome....

quot;Chip Pearsonquot; wrote:

gt; Use the following formula:
gt;
gt; =MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,A1)) 1,32)
gt;
gt; The file must have been saved to disk for this to work.
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt;
gt; quot;donaldDquot; gt; wrote in message
gt; ...
gt; gt; Excel - I would like to refer to the sheet tab name on the
gt; gt; spreadsheet...I
gt; gt; know you can use the simple (tab) command in the header, but
gt; gt; what would be
gt; gt; the quot;formulaquot; you would put in a cell to refer to the sheet tab
gt; gt; name...ie
gt; gt; ={tab}?
gt;
gt;
gt;

This works, but cuts off the rest of the filename and xls extention.

I want the cell to contain (example): ftx07715s-1/31/05 as the filename
contains a date.

Thanks.

quot;donaldDquot; wrote:

gt; That did it...Awesome....
gt;
gt; quot;Chip Pearsonquot; wrote:
gt;
gt; gt; Use the following formula:
gt; gt;
gt; gt; =MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,A1)) 1,32)
gt; gt;
gt; gt; The file must have been saved to disk for this to work.
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Cordially,
gt; gt; Chip Pearson
gt; gt; Microsoft MVP - Excel
gt; gt; Pearson Software Consulting, LLC
gt; gt; www.cpearson.com
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;donaldDquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Excel - I would like to refer to the sheet tab name on the
gt; gt; gt; spreadsheet...I
gt; gt; gt; know you can use the simple (tab) command in the header, but
gt; gt; gt; what would be
gt; gt; gt; the quot;formulaquot; you would put in a cell to refer to the sheet tab
gt; gt; gt; name...ie
gt; gt; gt; ={tab}?
gt; gt;
gt; gt;
gt; gt;

This does work but would you explain this formula to me. Is there a website
that will breakdown the formula to explain it to me?

quot;Chip Pearsonquot; wrote:

gt; Use the following formula:
gt;
gt; =MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,A1)) 1,32)
gt;
gt; The file must have been saved to disk for this to work.
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt;
gt; quot;donaldDquot; gt; wrote in message
gt; ...
gt; gt; Excel - I would like to refer to the sheet tab name on the
gt; gt; spreadsheet...I
gt; gt; know you can use the simple (tab) command in the header, but
gt; gt; what would be
gt; gt; the quot;formulaquot; you would put in a cell to refer to the sheet tab
gt; gt; name...ie
gt; gt; ={tab}?
gt;
gt;
gt;

Hi

=CELL(quot;Filenamequot;,A1)
will return something like
C:\Data\[Sales.xls]Sheet1

FIND(quot;]quot;,CELL(quot;filenamequot;,A1))
will return the position of the ] within the string (position 19)

MID(the number returned from above 1,32)
will return that portion of the string starting 1 character after the
position of the ], (20) and return the next 32 characters
i.e. Sheet1

The number of characters to pick up could be set at any arbitrary number
that is likely to be greater than the length of the sheet name.
--
Regards
Roger Govier
quot;esparzaonequot; gt; wrote in message
...
gt; This does work but would you explain this formula to me. Is there a
gt; website
gt; that will breakdown the formula to explain it to me?
gt;
gt; quot;Chip Pearsonquot; wrote:
gt;
gt;gt; Use the following formula:
gt;gt;
gt;gt; =MID(CELL(quot;filenamequot;,A1),FIND(quot;]quot;,CELL(quot;filenamequot;,A1)) 1,32)
gt;gt;
gt;gt; The file must have been saved to disk for this to work.
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Cordially,
gt;gt; Chip Pearson
gt;gt; Microsoft MVP - Excel
gt;gt; Pearson Software Consulting, LLC
gt;gt; www.cpearson.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;donaldDquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Excel - I would like to refer to the sheet tab name on the
gt;gt; gt; spreadsheet...I
gt;gt; gt; know you can use the simple (tab) command in the header, but
gt;gt; gt; what would be
gt;gt; gt; the quot;formulaquot; you would put in a cell to refer to the sheet tab
gt;gt; gt; name...ie
gt;gt; gt; ={tab}?
gt;gt;
gt;gt;
gt;gt;