Workbook & worksheet name formulas

Is there a formula that obtains the workbook name that I can put in cel
A1?

Also,

Is there a formula that obtains the worksheet name that I can put i
cell A2?

Thanks so very much.  mikebur

--
mikebur
-----------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=48298

0
11/7/2005 11:20:14 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
256 Views

Similar Articles

[PageSpeed] 10

Workbook need to be saved

=SUBSTITUTE(MID(CELL("filename",B1),FIND("[",CELL("filename",B1))+1,255),"]"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32),"")


for sheet


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



for path/name/sheet

=CELL("Filename",A1)

-- 
Regards,

Peo Sjoblom

(No private emails please)


"mikeburg" <mikeburg.1y5grm_1131405901.2118@excelforum-nospam.com> wrote in 
message news:mikeburg.1y5grm_1131405901.2118@excelforum-nospam.com...
>
> Is there a formula that obtains the workbook name that I can put in cell
> A1?
>
> Also,
>
> Is there a formula that obtains the worksheet name that I can put in
> cell A2?
>
> Thanks so very much.  mikeburg
>
>
> -- 
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=24581
> View this thread: http://www.excelforum.com/showthread.php?threadid=482983
> 

0
terre081 (3244)
11/7/2005 11:38:05 PM
See Bob Phillips' site for this.

http://www.xldynamic.com/source/xld.xlFAQ0002.html


Gord Dibben Excel MVP

On Mon, 7 Nov 2005 17:20:14 -0600, mikeburg
<mikeburg.1y5grm_1131405901.2118@excelforum-nospam.com> wrote:

>
>Is there a formula that obtains the workbook name that I can put in cell
>A1?
>
>Also,
>
>Is there a formula that obtains the worksheet name that I can put in
>cell A2?
>
>Thanks so very much.  mikeburg

0
Gord
11/7/2005 11:41:25 PM
Peo Sjoblom's vba works great!

One last request.

What formula would you recommend to show only the workbook name 
worksheet name in a cell (A3 or whatever) separated only by a hyphen 
.  For example for a workbook named Payroll with a sheet named Rates:

Payroll-Rates

Thanks a million.  mikebur

--
mikebur
-----------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=48298

0
11/8/2005 2:37:07 PM
=formula1&" - "&formula2

where formula1 is the workbook name formula and formula 2 the worksheet name
formula


-- 

Regards,

Peo Sjoblom

"mikeburg" <mikeburg.1y6n4n_1131460802.2276@excelforum-nospam.com> wrote in
message news:mikeburg.1y6n4n_1131460802.2276@excelforum-nospam.com...
>
> Peo Sjoblom's vba works great!
>
> One last request.
>
> What formula would you recommend to show only the workbook name &
> worksheet name in a cell (A3 or whatever) separated only by a hyphen -
>   For example for a workbook named Payroll with a sheet named Rates:
>
> Payroll-Rates
>
> Thanks a million.  mikeburg
>
>
> -- 
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24581
> View this thread: http://www.excelforum.com/showthread.php?threadid=482983
>


0
terre081 (3244)
11/8/2005 2:47:20 PM
Reply:

Similar Artilces:

Pivot table formulas #2
I'm using Excel 2007. When I add a formula to a pivot table my filters no longer work, the table is doubling/tripling up on on row lables and it takes for every to calculate a simple formula. What am I doing wrong? Thanks ...

how do i arrange column A (last name) in alphabetical order? t.
my phone book is in excel. how can i alphabetize by last name (column A)? thanks Click on any one last name. Then, Look for the Sort Ascending Button on the Standard Toolbar. It looks like a Blue A above a Red Z. Click that button once. --or-- Click on any cell in the phone list (such as a last name). Go to Data | Sort. Make sure it is selecting the Last Name Column in the Sort by position. Click Okay. tj "hershy" wrote: > my phone book is in excel. how can i alphabetize by last name (column A)? > > thanks I don't trust excel to guess the range to be sorted. I...

CFtpFileFind::FindNextFile() can't get the correct file name.
Once I want to get file from HP-UX by FTP. I use MFC CFtpFileFind to find the files under a specified directory in HP-UX, but the return value of CFtpFileFind::GetFileName () is not the correct file name, it includes time (sometimes and date) ahead, such as: 19:30 Mydemofile001.txt 19:35 Mydemofile002.txt but the real file name existed are only Mydemofile001.txt and Mydemofile002.txt. Is there any problem in the low level encapsulated by MFC for FTP list files? Could anyone give me some advice? Thank you. ...

Moving array formulas
I have the following formular that is working. However, it needs to be the same for 6 rows but then moving the references accordingly. B needs to be increased by 6 every 6 rows. H & F need to be increased by 1 every 6 rows (see below for how it needs to be) ie Rows A1 to A6 {=if($B$2=H3,F3)} Rows A7 to A12 {=if($B$8=H4,F4)} Rows A13 to A18 {=if($B$14=H5,F5)} How can I acheive this? One way .. Put in A1: =IF(INDIRECT("B"&(INT((ROW(A1)+5)/6)-1)*6+2)=INDIRECT("H"&INT((ROW(A1)+5)/6) +2),INDIRECT("F"&INT((ROW(A1)+5)/6)+2),"") (j...

Displaying the date a workbook was modified
How doe I insert the date on which the workbook was last saved into a worksheet? -- Bodene ------------------------------------------------------------------------ Bodene's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24924 View this thread: http://www.excelforum.com/showthread.php?threadid=384824 This code should log the save date and time to cell A1 of the active sheet in the book. Range("A1").ActiveWorkbook.BuiltinDocumentProperties(12).Value How and where you use the code depends if you need the refreshing of the data to be automatic or ...

New workbook, same window..
This is a daft one, but I can't find a solution anywhere. Everytime I open a workbook it opens in the same window as the last one. There was a time, in the dim and distant past when each time I opened a workbook, it opened in a new seperate window. Tiling windows annoys me, I want two copies of excel running independently. I just can't figure it out. I know its obvious and stupid, but please help! Cheers, Ben On Fri, 12 Sep 2003 03:52:58 -0700, "Bungers" <bungers@mailinator.com> wrote: >This is a daft one, but I can't find a solution anywhere. > &g...

Formula in 07
Need a formula. Column 1 (type) Column 2 (wk) a 77 b 77 c 78 d 80 e 80 blank 81 I would like the formula to come back for every week and tell me the number of types that are in that week. Another words in this case there are qty 2 types in wk 77. In week 81 there are 0. In wk 78 there is 1 and week 80 there is 2. Can anybody help? Thanks. Assume that you are having the Values i...

Linking Workbooks #3
I'm not sure if this is an excel function or an access function. (I don't know Access - however I'm fairly well versed at Excel). My company has a separate Excel spreadsheet with data for each client. We'd like to continually update the data in the client specific spreadsheet and have a separate worksheet that regularly pulls the data from all clients/all worksheets that we may then reference, sort etc. How would we go about establishing this? Thanks. -- Cathy Hi Cathy Maybe the Merge Add-in or code from my site will help you See the "Copy/Paste/Merge example...

Account name not synchronising offline in SFO
I wonder if anyone can help. We have installed MS CRM 1.2 and Sales for Outlook. The offline synchonisation seems to work correctly except that the account name is not mapping to the company name in the outlook contacts when going offline. This field is left blank. It does seem to create a new "user-defined field" in the Outlook contact called Parent Account. Is there any way to automatically synchonise this with the normal company field? Thanks David Try synchronize data from all business units under Offline Configuration. Also, ensure that the user's security role has a...

One link breaks every time I open a workbook...
I have 3 workbooks tied together with links. One is a source only, the other two push and pull data. One of the sources, Payroll Master, on opening updates all links fine to the source only workbook but not to the third workbook (which is in the parent directory). Every time I open it it prompts me to show it where the Payroll Entry is. Doesn't matter if I hit cancel and do Edit Links, I can change it there, but will still lose it upon closing (the link appears in the box and looks ok, just won't update...) Try going Edit->Links. Break the link and re-establish it? That m...

Excel formula riddle
I have a spreadsheet containing 150 workorders that were inspected based on 20 questions(columns). Each question has a specific numerical value and the only 3 possible answers are: PASS FAIL or N/A. I am trying to give points (lets say 30) for a pass or N/A, and 0 points for fail. Resulting in a final score displayed at the end of the row for each work order based on the 20 questions results. Any suggestions are very welcomed! -- egeorge4 ------------------------------------------------------------------------ egeorge4's Profile: http://www.excelforum.com/member.php?action=getinfo&...

Names across worksheets
I am defining groups of cells as a name by using these steps, "Insert", "Name", "Define". This works great when I'm on the one worksheet, though I find a brief problem when I try to span across worksheets. I've taken 6 cells from one worksheet and one from another. If I do a normal sum, the answer is 1795. If I do a sum on the defined name, it comes to 3595.5. I thought for some odd reason that the result was doubled, but it's more than doubled (only just). If I simply do =(defined name), it comes out to the value of the final cell (on the s...

workbook to workbook
In a cell in workbook no.1 I have this eneterd No.8-CRP-B1, in a cell in workbook no.2 in A7 i want to link this cells so it read the same and will update when updated. so I opened bolth notebooks whe into workbook no.2 and cell A7 entered = and then highlighted the cel in workbook no.1 for some reason I get the response #value why is this when I do this with other cells I have no problem? Thanks for the help -- MIKE0 ----------------------------------------------------------------------- MIKE0W's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2146 View this thr...

Chart from separate workbooks
Hello! I`m new to this group so greetings for everybody :). My questions (MS Excel 2007): There are 10 separate files, each containing a similar worksheet. There is an eleventh, cumulative file, containing a chart from these ten files. The number of files grows constantly. I would like the charts in the cumulative file to change, based on the newly created files. How to do this in a possibly automatic manner? For example: 5 persons are evaluated each month with a 10-question test and get a grade (from 1 to 5)for each question. In the cumulative file there is a chart depicting the mea...

paste formulas between workbooks without workbook link
Hello, I want to copy and paste several cells, each containing formulas, from one workbook to another. The formulas contain references to several different sheets and I want the paste to contain the formulas exactly as they are in the originating workbook. When I paste these cells into the destination workbook, however, the formulas have inserted in them links to the originating workbook file name. The only way I can see to solve this problem is to individually select each cell, copy its formula from the edit formula field and then paste it into the edit formula field in the dest...

formula to count matching days between two date ranges (for large list of names)
I have two sheets. Sheet 1 and sheet 2. Sheet1: has a list of Social Security Numbers, followed by a date Range (i.e., start date field and end date field). The date ranges are discrete (i.e., do not overlap) with each other for any given name. SHEET 1 Name A Start Date A End Date SSN1 1/1/09 1/5/09 SSN2 4/1/09 4/15/09 SSN1 3/2/10 3/15/10 SSN3 ETC... Sheet 2 has the same information (i.e., multiple records with SSNs, followed by date ranges Name B Start Date B End Date RESULT NEEDED SSN1 1/1/09 1/31/09 5 SSN2 4...

my colors change in my workbook when I open another workbook
I downloaded a template for Excel to my computer. When I use the template, the colors in other workbooks change. After I reboot, it all goes back to normal, until I open the template again. ...

Print and page settings of workbook
I am tired of resetting page attributes on similar worksheets within a workbook. Seems like one should be able to copy print settings (margins, footers, headings, font, etc. etc.) from one worksheet to another with one click like the format brush. What am i missing? Must I write a print macro to do this on each and every multiple set of worksheets? Try selecting the multiple sheets first, then setting the attributes. Just remember to ungroup them before you make changes that *don't* apply to all. In article <OVHh#XvdHHA.984@TK2MSFTNGP04.phx.gbl>, David Greenberg <...

Multiple row insertion on various worksheets
Need to be able to add row to Sheet1, and at the same time add a row to Sheet2 at a certain off sett row. Then if possible populate both new row with what is in the row above in the same way as would occur using the autofill pull down. Thanks Shaun If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett M...

Create a user-defined cell to display shape's layer name
How can I reference a shape's own layer name to display as a field? Specifically, I want to create a text box with a custom formula or user-defined field that display's that shape's one and only layer name. I can display the layerMember index by using "=LayerMember", but can't figure out the name. Please no VB/VBA. Thanks. The 1st thing to do might be just making custom propery on the page. Then please open the shapesheet of the page, add rows to the Custom Preperty Section, (or Shape Data in Visio 2007). The number of rows are as same as the number of layers. An...

how to delete the same columns across a workbook
how to delete the same columns across a workbook Hi, Hold down the CTRL key and click each sheet tab to group them. On the visible sheet delete the column abd it will be deleted on all grouped sheets. Click a sheet tab to un-group sheets. Mike On 5 Jan, 19:39, RPM <R...@discussions.microsoft.com> wrote: > how to delete the same columns across a workbook ...

removing reference to other workbook
hi i have a excel wookbook which contains references to other workbook. whenever i open this wookbook it asks me for updating the link to the other workbook. i want to remove these references to other workbook but am not able to figure out which cell is refering to the other workbook. i also have lot of references in the same workbook. i dont want the internal references to be removed. can anyone help me to accomplish this task. any help is highly appretiated. thankx in advance. pradyumna Hi try the following add-in to find/delete links: http://www.bmsltd.ie/DLCount/DLCount.asp?file=FindL...

Income Tax using US Tax System Excel WorkBook
Hello, I am looking for Excel Template to calculate Tax using US tax brackets. Can anyone please post a link for it. I have looked up cannot find it on web. Someone got it? Please post a link or webaddress if you got any idea. Thanks. Here's a couple of possibilities (?) http://www.dotxls.com/free-templates/ http://www.dotxls.com/free-templates/165/free-2007-excel-tax-forms-for-april-2008-filing "BetaDocuments" wrote: > Hello, I am looking for Excel Template to calculate Tax using US tax > brackets. Can anyone please post a link for it. I have looked up > ca...

queries & formulas
Hello, How would I write a formula that uses information from a different line in the query? For example, I have 4 fields (a,b,c,tot): a b c tot 10 250 0 =[b] 20 250 50 =[b] + [c] 30 250 100 =[b] + (what c is equal to in the above line) I've tried to make my formula look something like this: IIF([a]=10;[b]; IFF([a]=20;[b]+[c];IFF([a]=30;[b]+[c]+([c]=([a]=20));0))) By using this, I am missing some of my calcuations and, in this example, I would be short 50. I have many lines, and the [tot] should be the result of the above line plus [c]....

referencing multiple worksheets in macro
So I kind of found the answer to my question in a post from a couple years ago but am still confused so if anyone could help I'd appreciate it...I'm trying to protect the sheet while still being able to use the subtotal function. I have several worksheets I want to do this for and this is the post from before with the code but I can't figure out where to put the names of my worksheets ("01","02","03") and what I need to customize in the code for my file. Any suggestions? Option Explicit Sub auto_open() dim wks as worksheet ...