Program Sets > MS Excel

Putting Sheet names on a worksheet


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
looks complicated doesnt it. I found it and a function suggestion here:
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.


[0] Message Index

Go to full version