Program Sets > MS Excel

Putting Sheet names on a worksheet

(1/1)

Stephen:
I recently completed my school orders for new toners etc, unfortunately I do this for the entire school but I do it on a spreadsheet across a number of worksheets.
 
I accidently wiped the name off the top of each sheet where I had it as a heading and of course did want to have to type it in again for all the subjects, as I had named the worksheets each subjects name (apart from History - key word - I named it History) I thought there must be an easier way and there is!!
 
As long as you have saved your file you can use the equation
 
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
 
looks complicated doesnt it. I found it and a function suggestion here: http://www.mrexcel.com/forum/showthread.php?t=32203
 
Basically Cell("Filename",A1) would return the [filename]worksheetname the rest just seperates worksheetname out of the rest of the text.
 
Note I had about 12 worksheets highlighted and wanted to put the name into all 12 at once, After I pasted the equation I had to re-edit all the A1 bits or else you get a ref error.
 
So my final line looked like this
 
=CONCATENATE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))," Supplies June 2009")
 
Which would say something like: ICT Supplies June 2009, I choose to use concatenate rather than the usual & because I got an error with & for some reason.
 
Just got it working with
 
=(RIGHT(CELL("filename",A19),LEN(CELL("filename",A19))-FIND("]",CELL("filename",A19)))&" Supplies June 2009")
 
Go figure.

Navigation

[0] Message Index