PDA

View Full Version : Excel 2003: Linking worksheet tab value to a cell



johcar
26-08-2007, 12:19 PM
I use Excel to do my invoices. Because they're usually for the same client for a number of months, I usually just do a "save as" to create a new one. The Invoice Number is one of the cells in the spreadsheet. I then manually change the worksheet tab name to reflect the new invoice number.

Problem is I sometimes forget to increment the Invoice Number that is displayed in the cell in the spreadsheet.

Does anyone know how (or even if it's possible) to link the cell containing the invoice number to the worksheet tab? That way I could just update the worksheet tab and that would update the cell (or vice versa - if the worksheet tab was linked to the cell, I could see whether the invoice number was the same as a previous one)...

Thanks in advance for suggestions...

andrew93
26-08-2007, 09:50 PM
Hi

The following formula entered into a cell will return the worksheet tab name:


=RIGHT(CELL("Filename",A1), LEN(CELL("Filename",A1)) - SEARCH("]",CELL("Filename",A1)))

HTH, Andrew

johcar
27-08-2007, 07:39 AM
Absolutely pefect for my needs, exactly the sort of formula I was looking for - thanks andrew93 :thumbs: :thumbs: :thumbs:

Greg
27-08-2007, 08:51 AM
I empathise with your problem as I used to have almost the exact same issues. Glad you got it sorted.

(I sorted my problem by training myself to remember to update the cell)

johcar
27-08-2007, 09:25 AM
I empathise with your problem as I used to have almost the exact same issues. Glad you got it sorted.

(I sorted my problem by training myself to remember to update the cell)Training sounds too hard a solution - I have far too few brain cells left to worry them with learning something as 'easy' as that!! :D :D :D

This formula is GREAT! I can even see other uses for it other than my invoicing....

RogerRamjet
27-08-2007, 11:04 AM
There is an add-in at http://xcell05.free.fr/english/ called Morefunc.xll which includes the function Sheetname() which will return the name of the tab.

As well as simplifying the formula, the add-in has many other functions which you may find useful.

Cheers.

johcar
27-08-2007, 02:07 PM
Great link, thanks RogerRamjet. :thumbs:

Nice to see you are still saving the world with your Proton Energy Pills which give you the strength of twenty atom bombs for a period of twenty seconds.... :thumbs: :D

RogerRamjet
27-08-2007, 02:24 PM
The Proton Energy Pills ran out about 10 years ago.

I'm on Viagra now. Bloody useless for saving the world but they keep me standing upright :thumbs: