How To Make Sheet Name Change With Cell Value

Oct 1, 2013

I am working on a Workbook for my wife to use at school. It's basically a grade-book that automatically fills out the report card for each kid. In column A I have a list of each student's name. I also have those hyper-linked to each sheet for easy access.

I would like to be able to change the names in column A on Sheet 1 and it also change the names on the corresponding sheet it is hyper-linked to. I have seem some similar post but I can't seem to get any of those formulas to work. I get an invalid sheet name error. Maybe I'm not inserting the code properly or it has something to do with my hyperlinks. This is driving me crazy not being able to figure this out. I just want to make it as simple and easy for her to use year to year.

View 6 Replies


ADVERTISEMENT

How To Make False Part Of IF-statement Not To Change A Cell

Sep 10, 2013

Perhaps a bit cryptic but here is where I'm looking for.

Cell A1 gets the value YES (or NO) assignment by the user.
Cell B1 has some calculated value let's say 4 based on the formula If(A1="YES";2+2)

Now If the user assigns "NO" to A1 the value of Cell B1 becomes FALSE (return value of second part of the If statement)

I do not want the value of B1 changed if A1=NO and to stay the same (4)

View 2 Replies View Related

Make One Cell Change Colour When Number Is Greater Or Smaller Than Another?

Jul 25, 2013

I am doing a spread sheet for to enter the times that i do in a rally.

So if i post a time of 00:49.3 and the target time is 03:54.3 i want the cell to turn red, or if the set time is less then the target time it should be green.

How can i do this in excel

View 5 Replies View Related

Event Change To Change The Sheet Name Based On A Cell Value

Jul 21, 2009

Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far

Sub ChangeName()
On Error GoTo errhandler
Sheets(1).Name = Sheets(1).range("d10")
Exit Sub
errhandler:
MsgBox "sheet name is already exists"
End Sub

View 9 Replies View Related

Change Sheet Tab Name When Cell Changes

May 17, 2008

Automatically Change Sheet Tab Name With Cell is listed below for convenience

$A$2 contains a formula...the code doesnt work on a formula. if i overwrite the formula, THEN the code works and the sheet tab is named to whatever i type in the cell

but how do i make it work with a formula? ...

View 9 Replies View Related

Make Macro To Bring Tables From Another Sheet To Cover Sheet?

Jul 8, 2014

I am trying to take the tables you will see in the dummy page on the second and third sheet and have them output onto the first sheet based on the parameters of the drop down menus. Is there a way to have a button on the first page that controls a macro to do this?

View 4 Replies View Related

Formula To Change Cell Colour :: By Value Of Cell In Another Sheet

Sep 30, 2008

I need to set a cell colour by the value of a cell in another sheet so i can't use conditional format. Is there a formula that allows the user to set a cell style, colour, font etc.

View 9 Replies View Related

Monitoring Sheet For Change In Cell?

Apr 8, 2014

If the user enters a numeric value in Range ("B" & Rows.Count).End(xlUp).Offset(1), I need the code to copy the formulas in Range ("A12,C12:V12") to Offset row If there is no user entry in Range ("B" & Rows.Count).End(xlUp).Offset(1) then nothing should happen.

This should be monitored in sheet at all times.

I`ve done som research and think the code must be entered as a Private Sub Worksheet_SelectionChange(ByVal Target As Range) sub?

View 13 Replies View Related

Activate Cell On Sheet Change

Jan 6, 2014

When I select a specific sheet I would like cell A1 to be selected using VBA.

View 2 Replies View Related

Activate Corresponding Cell With Sheet Change

Feb 18, 2008

I have workbook with two sheets "Path A" and "Path B". Whenever I activate worksheet "Path B", I want to activate the cell in "Path B " which was the active cell in sheet "path A". For example, if cell R1 was active in path A when Path B was activated, R1 should be made the activecell of Path B. Tried the follwing code... not working.

Private Sub Worksheet_Activate()
Dim r As Integer, c As Integer
r = Worksheets("Path A").ActiveCell.Row
c = Worksheets("Path A").ActiveCell.Column
Worksheets("PathB").Cells(r, c).Activate
End Sub

View 9 Replies View Related

Make A Global Change To Many References

Aug 13, 2009

I have a large excel matrix that contains many references to a Word document. The path to that document includes /ref Doc1/ in every reference. I want to change that to /ref Doc1 rev1/. Is there a 'replace' tool to do that?

View 2 Replies View Related

Macro To Change Sheet Name When Cell Value Is Changed?

Jun 21, 2012

I would like to create a macro that will automatically change the name of the sheet any time a value is entered into cell a1. For example, in cell A1 of sheet1 I would input "Hello", and then the tab for sheet1 would be automatically renamed to "Hello".

Here's the twist - the workbook will have multiple sheets, and I want all tabs to reflect the value of a specific cell (a1) in each sheet.

For example:

value in sheet1, cell a1 = "Yes" - corresponding tab name would change to "Yes"
value in sheet2, cell a1 = "No" - corresponding tab name would change to "No"
value in sheet3, cell a1 = "Maybe" - corresponding tab name would change to "Maybe"
etc...

The "tab change" cell would always be cell a1 in each sheet.

View 2 Replies View Related

Change Sheet Name In Cell And Pass Through Formula

Mar 23, 2007

I can't use code for this problem as I need to give this to someone who doesn't know VBA. They will need to use it in several different reports, so I can't produce something in VBA very easily. I have three worksheets, Summary, Year1 and Year2.

I want to display either Year1 or Year2 data in the Summary sheet depending on what the user enters in a cell in the Summary sheet. For example, if the user enters "Year1" in cell E2 then I want to point to a cell in the Year1 spreadsheet.
I tried using formula: =E2 & "!B3" but this doesn't work. Is there another solution? I have attached an example.

View 6 Replies View Related

Multiple Pivots With The Same Row - How Do I Make Them Change At The Same Time

Oct 22, 2009

I'm using Excel 2003 for this project.

For example: -
I have 3 pivot tables with the same row 'Media' (TV, Neswpaper, Magazine) and each has count as the data field (number of people who read/watch) then each pivot has a different column, Region, age, ***.

If I change the row of the first pivot to only show TV, how do i code it so pivot 2 and 3 only show TV also? I may have upto 20 pivots inthe end.

I found the below code to do this for page fields but can't adapt to rows -

HTML Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim ptTable As PivotTable, ptItem As PivotItem, vFields As Variant, lngField As Long
On Error GoTo ExitPoint
vFields = Array("filed1", "field2", "field3")

Application.EnableEvents = False
For lngField = LBound(vFields) To UBound(vFields) Step 1
For Each ptTable In ActiveSheet.PivotTables
If ptTable <> Target Then
With ptTable.PivotFields(vFields(lngField))
.CurrentPage = Target.PivotFields(vFields(lngField)).CurrentPage.Value
End With
End If
Next ptTable
Next lngField
ExitPoint:
Application.EnableEvents = True
End Sub

View 14 Replies View Related

Change Comment Box Shape And Make It Default

Jul 1, 2013

i changed the shape of "Comment" box with edit shape. However when i right click the updated shape and click on Set Autoshape Default it does nothing. When I insert a new comment it is back to old shape.

View 2 Replies View Related

Make Calendar That Can Change Year In Text Box?

Jun 27, 2014

I' using 2007 I'm trying to make a calendar that you can change the year in a text box e.g. 2014 - 2015 and it will change the date and days in the calender rather than having to do it manually.

Is there a way to make this formula refer to a cell rather than having the specific year in there?

=TEXT(WEEKDAY(DATE(2014,1,15),1),"aaa")

View 2 Replies View Related

How To Make Same Formula Change In Multiple Places

Jul 7, 2014

I want to change the references in a VLOOKUP formula that is in 20 columns that are not all next to each other. The current formula is:

=VLOOKUP($AF5,' COUNTY ROLES '!$B$4:$D$69,3,0)
and I want the new formula to be
=VLOOKUP($AF5,' COUNTY ROLES '!$B:$D,3,0).

Is there a way to do this without having to change it in the first cell of each column and then copy/paste it all the way down each column?

View 1 Replies View Related

Inserting New Row On Sheet 1 - Macro To Make New Row Appear On Sheet 2?

Jul 24, 2013

In my database I am often inserting a new row in between two rows of data and then entering information across 8 cells. I have to manually enter all of the same information on sheet 2, again between two rows that already have data. Is there a macro or formula that would work so that I don't have to enter it twice?

View 5 Replies View Related

Identify The Cell Address Of Last Change Made In Sheet Using VBA?

Aug 13, 2012

I am using an event macro to trigger a set of calculations. In the spreadsheet, users have a choice of 3 input cells to work out an answer; these 3 cells work out the same cost from different angles depending on the variables available to the user.

Users only ever need to fill in one of the three cells to work out the answer as the event macro I designed, should (upon input by the user), work out the remaining two input cells using logical arguments.

The macro and calculation work fine, except in the event of users amending a value in an input cell previously updated; in other words, if all 3 cells contain a value and 1 of them is being changed by a user.

The issue: I need the event macro to recognise the location of the last change made by the user in order to determine which 2 of the 3 cells need to recalculate. However, users will invariably press enter or tab etc... upon making changes and this has caused me issues so far.

What I have tried already:

1) running another event macro "on selection change" to record elsewhere in the model all cell selections as and when the user interacts with the model

EFFECT: it made the "on change" event macro very slow and clunky; so I need to avoid
2) declaring a range as set r = activecell.address

EFFECT: this did not work as the active cell's address would in fact be the address on the last cell; e.g. the one selected further to pressing enter (often the one directly below the cell recently changed)

A solution to add to the existing "on change" event macro that identifies the last edited cell

View 8 Replies View Related

Change Color Of All Cells On Sheet Based Off Of One Cell Value

May 11, 2009

I want to change the fill color of all cells on my sheet, based on the value of one specific cell. In my sheet, I am using cell F1 as the trigger for the change. If the word Blue is in the cell, I want the background color of all cells to be Blue. Likewise for Red and Yellow as well. I don't believe conditional formatting can get this done, as all but the one cell (for this) will be empty.

View 2 Replies View Related

Change File Path In Formulas For Every Cell In A Sheet

Oct 9, 2009

I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.

e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like.

View 13 Replies View Related

Copy Entire Row To New Sheet Based On Cell Change

Feb 10, 2013

I am trying to copy an entire row to another tab based on when a cell changes. The column where the change will come from in colum N. I am using this code based on what I have read on this board, but cannot seem to get it to work correctly.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LC As Integer, iCol As Integer, Found As Range
iCol = 14 'column containing K
LC = Cells.Find(what:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
If Target.Column = iCol Then
Select Case Target.Value

[code]....

In the end what I would like to do is everytime there is a change in column N, the macro copies the information from that row into the other tab. I would like the information to overwrite anything that is alraedy in that tab as well. So if someone accidentally putc in a C instead of a K, it will not keep that information in the wrong tab.

View 7 Replies View Related

Insert Today Date But Make Sure It Doesn't Change

Mar 19, 2014

I have a button and when I press it, it shows a tick so the user knows when it's been clicked I also want it to insert the date it was clicked but obviously if I use the =TODAY() formula it would change everyday. I need to keep it the same after each day.

View 3 Replies View Related

Text Copy To Another Sheet Based On Change Of Actual Cell

Jul 20, 2013

I have this code on my sheet1:

VB:
Private Sub Worksheet_change(ByVal Target As Range) Dim KeyCells As Range
Set KeyCells = Range("K:K")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
If ActiveCell.Value = ActiveCell.Offset(0, -6).Value Then
ActiveCell.Offset(0, 1).Value = ((ActiveCell.Offset(0, -4).Value) * (ActiveCell.Offset(0, -5).Value)
End If
End Sub

Now I would like to add another code: When I will change value in actual cell (sheet1) then copy value from cell A1 (sheet1) to the first free cell in column A (sheet2). I still have problem with error that I am out of range if I tried to copy it to sheet2.

View 2 Replies View Related

Copy Sheets And Change Sheet Name And Cell As Matching Date?

Feb 1, 2014

I have a template with a sheet dated the 1st of the month "Feb 1" Cell 2 also had the same date.

I'd like a VBA to copy this sheet, change the sheet names to Feb 2, Feb 3, etc, and also change cell 2 to the same name...

I've seen similar code to copy sheets and change name, but can't figure out with the date...

A pop up asking for the number of new sheets would also be useful.

ExcelForumSample.xls

View 8 Replies View Related

Copy Formula To Another Sheet And Simultaneously Change Reference Cell?

Apr 29, 2014

I have an excel workbook that has many spreadsheets (each one sheet has a client name)I have another excel workbook that has these client names on one sheet (on a list) and next to every name I have a number (i.e total turnover of the year).

In the first workbook (where every client has his sheet (tab named after the client) I want to have a cell that equals to the sum of some cells on the other workbook, that refer to the specific client

(it looks like this ='[comissions NF 10-14.xls]comissions 14(auto)'!$J$81+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$197+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$313+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$429)

I want this sum to be added to every sheet of this workbook. each sheet refers to a client, so $J$81, $J$197 etc must be changed for the correct cell that refers to the name of the client. The tab names are alphabetical and so s the list.

Is there any way to do it, without re-entering the formula to each one?

View 1 Replies View Related

Make Chart That Will Constantly Change As Different Selections Made By Person?

Jun 18, 2014

I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.

Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. What I mean as you open the example sheet and start messing around with the data validation boxes.

The problem I am encountering now, is creating a chart that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.

Here is what I am looking for in the graph:

1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.

2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.

3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.

View 2 Replies View Related

Make Copies Of A Spreadsheet Within A Book - Dates Change Automatically?

Nov 27, 2012

If you look at the file I attached you will see the tabs at the bottom are months of the year. I know how copy a sheet but every time I make a new month tab I have to retype the month in the top left corner of the table, in the tab, and other places. This gets really repetitive when I have to do this for literally around 20 other spreadsheets like this.

What I'd like is a way to create new month tabs that automatically rename the month and year in chronological order on the sheet and tab if possible. 2 months it's because I'm constantly having to tweak spreadsheets for my boss. It'd be pointless to go ahead and make a template because then I'd have to go back and change the code for every month. So I just create tabs and edit code month by month.

View 4 Replies View Related

Make Worksheet Calculate Event When Range Of Cells Change?

Feb 15, 2014

I have the following code that I would like to trigger when cells B26:U26 change to something other then 0 due to a formula? How was I make this happen?

[Code] .....

View 4 Replies View Related

Change Cell Color Based On Cells Information Appearing On Another Sheet

Jan 6, 2014

I am trying to create a formula that will change the cell color in a range of cells in the row. I want the color to change based on the information in a particular cell appearing on a different sheet. What sheet the information is on determines the color the cells change to.

Example: I have 4 sheets I am working with. We will call them A; B; C; and D. I would like the color of cells A5-I5 on sheet D to change to red when the information in cell A5 from Sheet D shows up in any cell in column A on sheet A. If the information from cell A5 sheet D appears in any cell in column A from Sheet B then the color will be yellow, etc.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved