Dynamicly change spreadsheet tab names depending on cell value

Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 
for each day of the month)

These tabs are named - 1,2,3 e.t.c 
Is it possible to define rule that would ad "!" to day which is weekend day.

In other words is it possible to dynamicly change tab name depending from 
value of cell ?

If this is possbile how do i do this ?
0
Mikus (3)
8/13/2005 4:52:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
412 Views

Similar Articles

[PageSpeed] 57

Right-click on the spreadsheet tab, select View Code and paste this in:-

Private Sub Worksheet_Activate()
    ActiveSheet.Name = Range("A1").Value
End Sub

To test this I put the date in cell B2 and this formula into A1:-

=TEXT(DAY(B1),"d")&IF(OR(WEEKDAY(B1)=7,WEEKDAY(B1)=1),"!","")

"Mikus" wrote:

> Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 
> for each day of the month)
> 
> These tabs are named - 1,2,3 e.t.c 
> Is it possible to define rule that would ad "!" to day which is weekend day.
> 
> In other words is it possible to dynamicly change tab name depending from 
> value of cell ?
> 
> If this is possbile how do i do this ?
0
bigwheel926 (227)
8/13/2005 7:22:01 PM
Take a look here:

    http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html


In article <E00C3401-8BB2-4286-B0B7-C778831EC78A@microsoft.com>,
 "Mikus" <Mikus@discussions.microsoft.com> wrote:

> Is it possible to change tab names dynamicaly ? For example i have 31 tab (1 
> for each day of the month)
> 
> These tabs are named - 1,2,3 e.t.c 
> Is it possible to define rule that would ad "!" to day which is weekend day.
> 
> In other words is it possible to dynamicly change tab name depending from 
> value of cell ?
> 
> If this is possbile how do i do this ?
0
jemcgimpsey (6723)
8/13/2005 7:57:53 PM
Mikus Wrote: 
> Is it possible to change tab names dynamicaly ? For example i have 31
> tab (1
> for each day of the month)
> 
> These tabs are named - 1,2,3 e.t.c
> Is it possible to define rule that would ad "!" to day which is weekend
> day.
> 
> In other words is it possible to dynamicly change tab name depending
> from
> value of cell ?
> 
> If this is possbile how do i do this ?

You could add this routine to a module in the workbook:

Sub Auto_Open()
--For Each xSheet in ThisWorkbook.Worksheets
----nDay = Val(xSheet.Name) 'Use number in sheet tab to represent date
----dDate = DateSerial(Year(Now()),Month(Now()),nDay) 'Work out full
date
----tDay = Format(dDate,"ddd") 'Work out which day it is
----'If day is SAT or SUN, add exclamation point
----If Left(tDay,1)="S" Then
------xSheet.Name = Format(nDay,"0") & "!"
----Else
------xSheet.Name = Format(nDay,"0")
----End If
--Next xSheet
End Sub

This will add an exclamation point symbol to any sheet tab representing
a date which is a weekend day.

However, you could simply replace the 'If-Else-End If' section with:

----xSheet.Name = Format(dDate,"dd ddd")

And this will name your sheets:
'01 MON', '02 TUE', '03 WED', etc.

Hope this helps.
BizMark


-- 
BizMark
0
8/13/2005 10:40:26 PM
Reply:

Similar Artilces:

Names Drop down list
I have version 2002 SP-2 Outlook. Also, under tools/options/email options/advanced email options, I have "suggest names while completing" and "automatic name checking" checked. I was recently moved from outlook express to Outlook. Now when I write a letter, when I start writing a name, nothing happens. When I start writing an email address, then I get a suggestion drop down list. , How do I get names to do that,. I had no problem in Outlook Express. ...

Table Changes
Where is the documentation for the table changes for GP 9 It should be in the help menu. Gerald "Brian" wrote: > Where is the documentation for the table changes for GP 9 > I have yet to install it.. I am trying to make sure that I have everything covered do to the fact we have numerous outside apps that talk to the backend of GP and want to prevent any possible headaches Gerald wrote: > It should be in the help menu. > > Gerald > > "Brian" wrote: > >> Where is the documentation for the table changes for GP 9 >> Brian, What...

Duplicate entries in excel spreadsheet
I have lists of attendees to an annual event. I've taken the lists and combined them, and sorted them by first and last name alpha. I want to keep the names and corresponding collumns for those that attended my event more than once and dump the rest. Is there a way to sort them quickly as i have over 15,000 names. Cheers! I'd insert two new columns before the list, say A and B. In the first data row in B, say B2, I'd enter a formula that combines the first and last names to create a field the uniquely identifies each record: =C2&D2 and copy it down to all records. N...

What is the cell data reference for 5th row and 7th column called
Can someone help me with this question? I need to get the answer for my homework. Thanks for your help. I'm guessing, but might it be G5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shy-di@verizon.net" <Shy-di@verizon.net@discussions.microsoft.com> wrote in message news:A0979494-228B-435E-98F7-6BD26C900266@microsoft.com... > Can someone help me with this question? I nee...

tab between sheets
Any way to select a different sheet without using the mouse -- Message posted from http://www.ExcelForum.com Hi CTRL-PGUP and CTRL-PGDOWN -- Regards Frank Kabel Frankfurt, Germany > Any way to select a different sheet without using the mouse? > > > --- > Message posted from http://www.ExcelForum.com/ In Excel Go to Help, type in keyboard shortcuts This will give you tons of information "laneman >" <<laneman.14pfas@excelforum-nospam.com> wrote in message news:laneman.14pfas@excelforum-nospam.com... : Any way to select a different sheet witho...

Select a random value from a column of values
Hi, I have never programmed Excel before, but do use it a fair amount, including use of built-in formulae. I would like to be able to select a random value among a group of values - is this possible? So for example, in a spreadsheet I have in column A a list of names from cell 1 to cell 20. then I want to pick a name from random from that list. How would I do? Any input would be appreciated. -- Paul paulastya@gmail.com PS I do not have visual Studio/VB installed on my machine. someone else will give you a simpler formula, but, if not, the following will work: =INDIRECT("A"&a...

The value become wrong when get value by using AppleScript while other worksheet is active.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I just want to send a bug report. Summary: The value become wrong when get value by using AppleScript while other worksheet is active. Description: 1. Create an AppleScript file with below script. tell application "Microsoft Excel" activate set OriginalWorkbook to workbook "Workbook1" set OriginalData to string value of column 1 of row 1 of worksheet 1 of OriginalWorkbook end tell 2. Launch Excel 2008 and create a Workbook called "Workbook1". 3. Enter time data "11...

Outlook Contact Names are Reversed
I have been using Outlook 2002 for two years. (I have used earlier versions for at least six additional years.) Now, for some unknown reason, Outlook reverses the order of names for new Contacts that I enter. For example, when I enter "John Smith" as my contact's name, Outlook lists "John, Smith" under File as. When I open the Check Full Name window, it lists "Smith" as the First Name and "John" as the Last Name. If I enter"John" as the First Name and "Smith" as the Last Name in Check Full Name, "Smith John" is list...

Contact Full Name Doesn't sort properly
I recently changed from "First Last" to "Last, First" in the system settings. I updated the ContactBase and the SysUser tables to take of of the existing records. I noticed when I type in a quick find search on the contacts it doesn't sort properly. It sorts by the last name, then the first name then about 1/2 way down, I notice there are some records out of alphabetical order. I double checked the Quick Find Query and the sort column is set to the full name. Not sure how/why it's not truly sorting on full name (yes, they are all active records) When you...

Cell Contains Data.
How can I tell a cell that IF a cell contains data (numbers, text, whatever) do x or y. How can I identify data?? I don't want to tell the function to look for a specific number or text, just data, just something in the cell... =IF(A1<>"","Data","No Data") "GEM" <GEM@discussions.microsoft.com> wrote in message news:07916799-F312-4E89-A926-59356B74ED89@microsoft.com... > How can I tell a cell that IF a cell contains data (numbers, text, > whatever) > do x or y. How can I identify data?? I don't want to tell the function ...

Active cell highlight
Hi, I would like to know if there was a way to have an active cell always in yellow or any color. Example: If the active cell is moved around, the active cell will be always in yellow. Thank you in advance maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm E wrote: > > Hi, > > I would like to know if there was a way to have an active cell always in > yellow or any color. > > Example: If the active cell is moved around, the active cell will be always > in yellow. > > Thank you in advance -- Dave Pet...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

Excel2000: Is it possible to use named ranges as chart's series ranges
Hi I have a table, with a row added weekly, and a chart based on those data. But chart must display data from last 6 week only, i.e. chart's data series must be defined dynamically. Of course I can do it, using some hidden dummy sheet, where data for last 6 week are automatically collected, and which serves as source table for chart. But is there a way to avoid this - p.e. by using dynamic named ranges? I tried to enter range names into Values field for Chart Series, but I got 'The formula you typed contains an error' message. Is there some trick for it, or it is hopeless? OK, ...

copy value in cell above to cell below for a whole column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Can anyone help? Ctrl+D will copydown the value of the top cell to the highlighted cells below. "tarthur" wrote: > I have a spreadsheet that was saved from a report. this report only lists > the employee name once. There is a way to copy th...

How can I remove the previous lock cells and keep the new ones.
Could you please help me?... I locked and protected the cells A1:H20 in the sheet 1 two months ago. However, yesterday I wanted to unlock and unprotect these cells and locked and protected the cells A21:H40 instead. However, for some reason the cells A1:H20 are still lock and protect. How can I remove the previous lock cells (A1:H20) and keep the new cells (A21`:H40) only. Thanks. Maperalia In EXCEL 2007 take the following actions to unlock cells A1 to H20:- 1. Home / Cells group / Format / Unprotect Sheet / enter the password to Unprotect Sheet / OK / highlight cells A1 t...

ribbon--removing tabs
Hi, I used the following code to remove all tabs from the ribbon. It removed all tabs except "Acrobat". How can I remove this tab? <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="true"> </ribbon> </customUI> Karen Updates to Adobe Acrobat seem to install themselves whether they're wanted or not. Open Access. Use the Office Button in the upper left to get to the Access Options. Select the "Add-Ins". Select "Manage: COM Add-In" a...

Problem with a value in a variable
Hello, I'm trying to work with the events log : cmdlet "get-eventlog" I would like To test if I get some news events since two hours. This is my simple script : -------------------------------------------- $datedujour=$(get-date).AddHours(-2) $journal="Application" $listevents=get-eventlog $journal -Newest 1000 | where { ($_.timewritten -ge $datedujour) -and $_.entryType -match "warning|error"} $countevents=@($listevents).count IF ($countevents -gt 0) {$evtcheck="YES"} ------------------------------------------- But i ha...

Value shows but should not print
Hi all! I was wondering if there's any way to format a cell such that its value shows normally but when the worksheet is printed, that cell's value is not printed out. Any help on this would be greatly appreciated! -- uberathlete ------------------------------------------------------------------------ uberathlete's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28388 View this thread: http://www.excelforum.com/showthread.php?threadid=480622 check out this from Ron De Bruin See http://www.rondebruin.nl/print.htm#Hide good luck -- swatsp0p -----...

formulas changing when emailed
At work, I have created a spreadsheet that has links to workbooks in a user drive..called the "U: drive." When I transfer this excel project to others via email, I make sure that the other workbooks also have the same name in their U: drive as well. However, whenever I email it... I have to change many of the formulas at their computers bc the references in my formulas change to a really long string. I have tried using "CNTL+H" to replace the "=" to a "+" but it doesn't work for me bc I have "+" "=" elsewhere in my formulas....

Adding data from multiple cell and linking to a new worksheet
I have data in a sheet with names and amounts due. Like A 500 B 300 A 600 B 300 C 400 C 200 There is a second sheet named summary. I want the totals of each person to appear in the summary sheet. These should be linked cells and any change in data in sheet 1 should be updtaed automatically Create a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Rashmi" <Rashmi@discussions.microsoft.com> wrote in message news:56DC0F3A-8D60-4FE2-B778-3A07790EFE8A@microsoft.com... >I have data in a sheet wi...

Choose highest value in a row
How do I tell excel to choose the highest value in a range of cells? A B C D E Highest 1 3 2 7 5 7 help please. =max(a1:e1) will return the highest value. sohum.shah@gmail.com wrote: > > How do I tell excel to choose the highest value in a range of cells? > > A B C D E Highest > 1 3 2 7 5 7 > > help please. -- Dave Peterson =MAX(A1:E1) returns 7 in your example. Gord Dibben MS Excel MVP On 23 Oct 2006 10:47:09 -0700, sohum.shah@gmail.com wrote: >How do I tell excel to choose the highest valu...

Move cells down to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7...

using data from another cell
I would like to type a name in one cell(1) then check in list (different column) if the same name is there, and if it is there get a value of another cell locate in the same row where. Can some one help me? Create your second list and use VLOOKUP as follows: Name is typed in: A1 List of names and values in: C1:D10 =VLOOKUP(A1,$C$1:$D$10,2,FALSE) "leo" <reani1996@hotmail.com> wrote in message news:OiO5fCTWDHA.2268@TK2MSFTNGP11.phx.gbl... > I would like to type a name in one cell(1) then check in list (different > column) if the same name is there, and if it is t...

Delete table values automatically
Hi All, I want to create a routine that will clean all records from a specific table automatically once a week (if possible) or each time that the MDB opens up. Any thoughts? Thanks a lot in advance Hi, the code to empty a table is simple Public Sub EmptyTable() Dim strSQL as String Dim db as DAO.Database Set db = dbengine(0)(0) strSQL = "DELETE FROM TableName" db.Execute strSQL, dbFailOnError Set db = Nothing Exit Sub the above code goes in a standard module when your database opens, you call the routine EmptyTable Jeanette Cunningham "Lp12" <Lp12@discussion...

Display Time based on another field value
Im trying to insert the current time in a field based on another fields value which has 3 different values from a list box the value in this field is text. How do I go about write the formula? SFCNS Shaun Are you working in MS Access, the relational database? Are you trying to do this in a form (good) or directly in a table (bad)? Are you saying the "other" field has multiple values or just one? More info, please... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Menti...