A formula to put the sheet’s name into a cell
=RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1)))
The CELL() function with the “filename” argument will return the full path of the workbook plus the sheet name. The workbook will be enclosed in brackets []. This formula finds the right bracket (]) and returns everything after that.
Note that if the workbook has not been saved, there is no path, and this formula returns a #Value error.
Thanks, I will be using this formula in my project.
As an alternative, this has two fewer function calls:
=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255)
255 is just a big number. Could be anything >=31.
Can I use this formula somehow to grab the sheet name of a different excel file and place it in a cell of the current excel file?
You could use something like this to do that for you.
=MID(CELL(“filename”,[Book1.xls]Sheet1!$A$1),FIND(“]”,CELL(“filename”,[Book1.xls]Sheet1!$A$1))+1,255)
The cell references for the CELL() function have been changed for cells on the sheet that you are interested in in another workbook. This only works if the other workbook is open though otherwise it returns the #N/A error.
OH,thanks john.
I dont think that will work for me. I have an autocad created excel file. AutoCAD assigns random name to the excel sheets, but the file name is always the same. I need to grab the random name and put it in a different excel file. I have not been able to find anyone who can do it so far.
John,
Your formula works perfect, it just sucks that it wont work unless the file is open.
thanks again!
Obtain sheet names from a closed workbook:
http://www.google.co.uk/groups?selm=u8CED2k0EHA.1408%40TK2MSFTNGP10.phx.gbl
http://www.google.co.uk/groups?selm=ugWMa0PvAHA.1820%40tkmsftngp04
http://www.google.co.uk/groups?threadm=%23sTElpyoCHA.2564%40TK2MSFTNGP10
I want to enter “sheet2? into A1 of sheet1. Then I want to sum A1:A5 of sheet2.
the formula would be =SUM(sheet2!A1:A5)
I would like to pull the sheet name from A1 of sheet1. The reason for this is because I have data relating to all 10 provinces in one workbook and I want to write a formula that will pull the province name from a cell in Sheet11 to correspond to the sheet name that I want to sum up.
I would like to write a formula like this
=SUM(“A1?!A1:A5)
where A1 will return sheet2
I hope this makes sense.
Is this possible.
Matt,
Using the INDIRECT function like this:
=SUM(INDIRECT(“‘”&A1&”‘!A1:A5?))
Juan
Thanks so much that will work excellent.
I am trying to place the sheet name into a cell in another sheet of the same workbook.
I want to use the conditional sum function on sheet 2, but refer to a data range on sheet 1. I have successfully used the conditional sum wizard to enter three conditions, placing the formula on sheet 1. However, I need to put these totals on sheet 2, separate from the data on sheet 1.
=SUM(IF($C$2:$C$11=”X”,IF($L$2:$L$11=”HVAC”,IF($I$2:$I$11=”C”,$J$2:$J$11,0),0),0))
How do I add a reference to Sheet 1 in this formula?
I have answered my own question. When asked for the data range, I clicked to Sheet 1 and selected the data range. I actually had tried manually adding the reference to “Sheet1!” but it did not work until I went back into the Conditional Sum Wizard and highlighted the cell range.
Why am I unable to edit my formula after using Conditional Sum Wizard? I tried the above edit and it didn’t work. Then I tried to change the reference to one column (change from C1:C10 to D1:D10), but when I do that it is invalid and I must start all over using the Wizard.
I am using Excel 2000. Do the newer versions resolve that problem?
Hi Robin –
The conditional sum wizard produces an array formula. Notice that it has {curly braces} around it in the formula bar? When you create or edit an array formula, you have to hole CTRL+SHIFT while pressing Enter, in order to produce a valid array formula.
– Jon
Thank you so much, Jon! I can’t tell you how much this has helped me!
I am working in the same array formulas and have another question. I have a Conditional Sum formula and need one of my conditions to say this: “If C2:C17 is not blank (it will contain a number), add the numbers.” What I need to know is how to express the condition “not blank.”
Thanks very much!
Robin
re “put sheet name into a cell” (Excel XP)
ie. =RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1))) and similar.
The above does not appear to work well when there are multiple sheets in a workbook. eg. I use the formula to display the sheetname on about 12 sheets. Unfortunately, at any one point in time, all the same sheetname is displayed on all sheets. The formula appears to display the sheetname of the sheet with the most recently “recalculated” cell value.
Simply switching from one sheet the next does not change the sheetname displayed. The displayed name only changes to the currently open sheet if:
+ Press F9 to recalculate a value (/values) on the ‘active’ worksheet
+ or say enter data or a formula and press enter; ie. this appears to make the “open” sheet the “active” sheet.
David,
Are you sure you have a cell reference after “filename”? eg. CELL(“filename”) vs. CELL(“filename”,A1) — with the cell anchor you shouldn’t have to recalculate in order to see it.
Matt
This will work in a cell to get the sheetname of a refference (and does not require the file to be saved) Note however you cannot use it on the sheet you are getting the name of… Excel automatically gives local address and then doesn’t have the sheetname.. :P
=MID((CELL(“Address”,’Sheet You Want The Name of’!$A$1)),(FIND(“]”,CELL(“Address”,Sheet You Want The Name of’!$A$1),1)+1),(FIND(“!”,CELL(“Address”,’Sheet You Want The Name of’!$A$1),2)-1)-(FIND(“]”,CELL(“Address”,’Sheet You Want The Name of!$A$1),1)+1))
Still Requires Recalculation.
Hope this helps someone…
This is all facinating, but I was looking for something the other way round.
How do I set a worksheet name to the value of a Cell.
ie I have a cell H1 containing
“January 2008?
and I want sheet1 to be labelled this programmatically
which would save me manually changing all 7 worksheets every month
Derek: You need VBA for that. See
http://www.dailydoseofexcel.com/archives/2005/01/04/naming-a-sheet-based-on-a-cell/
excel 2002
I want to use the name of the ACTIVEworksheet into the chart label or textbook.
I already can get the worksheet name into a cell.
NOTE. I name each tab by date and then want this date on the chart which I print out.
THANKS !!!
adding in the reference to A1 (ie CELL(“filename”,A1) rather than simply CELL(“filename”) did the trick for me !! :-D
I am using formula for Filename in one cell. When another file is opened, the filename of this new file gets updated in the earlier file. For example File named “123.xlsx” is open, the cell A1 shows “123?. When the file named “xyz.xlsx” is opened, cell A1 in file 123.xlsx shows “xyz”
How to fix this.
Thanks in advance
Vinod,
Add a cell reference (the 2nd parameter to the CELL function).