Is there a way to set 2 cells equal, while allowing either one to be manually changed? So if I put in value of 10 for Cell1, Cell1 and and Cell2 will both be equal to 10. Then if I change Cell2 to a value of 20, both cells will be equal to 20.
View 5 RepliesHow can I get a cell to change color if a user were to modify or change the value of the cell contents?
View 4 Replies View RelatedI need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
I made a sheet to control some reports, and it works like this: you click a button ("Insert"), it opens a userform, you type everything there (date, serial number, client, city) and it place that info on the first row available. Then there's a "Clear Last Entry" button, and that's it.
I would like to protect the whole sheet and only allow users to insert new entries via the "Insert" button. How can I do this? If I protect normally Excel won't allow the VBA code to write on cells.
Iam making some test report tamplet`s, in that when I select the material from drop down (ref enclosed xls file) all spec will be displayed. ·I want the sheet to be protected at the same time few cells to be allowed for data entry (cells marked with blue)· If sheet can`t be protected I want to hide the formulas and spec details used in it.
View 2 Replies View RelatedI am trying to setup a number of cells with a validation list allowing "Yes" or "". I would like the cell value to equal 1 when "Yes" is selected. Is this possible without VBA?
The reason for this is because I want to set up conditional formatting with the tick, cross, exclamation mark icon set (With a tick appearing for "Yes"), but this conditional formatting only works with numerical values.
why I must update all cells, with formulas, manually after I do something with the workbook. I'll try to make an example.
If I make a macro that enters =sum("F1:F10") it says #NAME? when its done, but if I dubbleclick it and press enter it calculates the cell like its supposed to. I've tried to press alt + ctrl + F9, but that doesnt work.
Is there a command to dubbelclick the entire workbook and then press enter if you know what I mean?
I have a sheet where I have a column of cells where I manually enter in a date. the format is d-mmm-yy. Before the end of the year rolled around I was able to enter in say only 12-31 and it would automatically change that to 12-dec-09, now when I do that it spits out 12-dec-10. It's not a big deal to type in the extra -09 to get it right but was wondering if there was a quick/easy fix to get it to know that I don't want a date in the future?
All dates I type in will be in the recent past...never more than 3 months or so old and never later than today's date.
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
On an excel document that was originally formatted as a number and then locked, somehow users have changed the formatting to a date. I think it is an accident, but I can't figure out how they are doing it. I have several data files on a server where several offices have access to them to input data and somehow formatting gets changed sometimes, even when locked.
View 3 Replies View Relatedhow to alter this Private Sub for my code to execute automatically once all 3 cells have changed. At the moment it executes as soon as any of the 3 cells change.
Here's the Private and Public Sub codes:
I'm after some vb code that will only allow the range - PRODUCT_Selections!A2:C370 to calculate, when something is changed in the range - Input!T11:V500
I would like all other calculations in the workbook to continue as normal.
Column-A starts out empty.A user changes cell B5 and navigates to another cell (whether by clicking, tabbing, or enter-key, it doesn't matter).An "X" is placed in Column-A for that row.Preferably the user would then be taken to the cell they were navigating to (whether by mouse-click, tabbing, or the enter-key).This happens every time a change is made to a cell.I have no idea how to code for this.
I'm not sure what constraints I might have for exceptions. For instance, can it handle multiple row being pasted into or cleared at once, etc. ( Deleting columns will not be allowed in this instance.)
------------------------------------
ADDENDUM:
I continued my search and found the instructions at [url]
My ultimate goal in putting an "X" in Column-A is to identify rows with changes, so they can then be copied into an "archive" worksheet. I'm thinking this VBA version of track changes would be much better than what I was earlier thinking.
I have a workbook with two worksheets, Data and Archives. I want to let a user make changes to data in the "Data" worksheet. Then when they save or press a button all rows with edits will be copied into the "Archives" worksheet.
Appending date/time and username stamps in the trailing columns would be very handy too.
How do I protect a select range of cells from being changed? There may be some formulas in certain cells, which produce data based on other worksheet cells. These cells are not for user data input, but for data output only. I don't want end users to acidently delete the formulas in cetain range of cells, by entering a value in the cell, which then will write over the formula.
View 5 Replies View RelatedI've managed to piece together a VBA code that works perfectly for my needs. The problem is that the sheet it is on gets adjusted a lot, and I only need the code to run when certain cells are changed (rather than each time any cell is changed).
Here is the code I have now:
[Code] .....
Put simply, I need this code to run only when a cell in Range "TValueDeal" is changed. Is making this adjustment as simple as adding a couple lines of code, or is there a better way to write the entire thing?
Colouring calendar entries based on a date match... VBA to colour a cell on each line based on date match
The code works fine if you change the date in the cell and press enter however my date changes are triggered via a Vlookup which the code doesn't recognise has changed so doesn't trigger the macro.
Is there any way to amend the VBA code shown below so that it triggers the macro when dates are changed in column I via Vlookup?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Target.EntireRow.Interior.ColorIndex = xlNone
[Code] ......
Is there a way to cause a particular VB function I write to execute any time a value in a particular range of cells is changed?
I thought I had it when I thought I could do a sum formula, range on the cells to be checked, then call the function as a side effect of this sum. It was then that I realized I didn't know how to call VB from within an Excel cell formula.
1. Is there a way to call VB (say, sheet1.myFunction())?
2. Is there a better way to do what I want than to try to hack a side effect into a formula in a cell that depends on a sum (which "watches" for the changes for me.)
The cells would have text in them, not numbers, so using the sum-based formula would be iffy at runtime to me at best. What I really want to do is run a function on the cell value as a string. There are a lot of such cells (thousands) so ideally I would only need one external cell to activate the function (which scans all the cells I am concerned with) rather than have to paste a custom, relative formula into thousands of cells manually to have each one watch one cell, if you know what I mean.
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).
Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.
I really do not think the actual formulas matters at this time...
Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.
I am using Excel 2007, and here is my scenario. I have a spreadsheet with a different sheet for each month of the year. I want certain cells in a month's sheet to mirror or be equal to the same cell in the previous sheet so that if I change the value of a cell in April, the same cell will automatically be changed in May.
An example is that I have a cell with the value of "Comcast". If I want to change that value in April to "Directv", I want it to automatically change in May, too.
Now I know that I can individually program a cell to be equal to a cell in another sheet and it will do exactly what I want. The problem is that I have about 60 cells in each sheet that I want to mirror the same cells in the previous sheet, and I don't want the entire sheet to mirror the previous one. So with 11 months/sheets with cells mirroring the previous sheet, individually programming 660 cells and switching between sheets to do that would take a very long time.
So my question is this. Is there any way to select multiple cells and have them all mirror the same cells on a different sheet without my having to program each cell individually?
I have two sets of data to compare. One is real world data, the other is forecasted data. I have real daily data for every day over about 3 months. The predictions though, there may be 4-6 predictions for ever one real daily value.
What I'd like to do is have a function that looks at Column A's date (real daily value) and Column C's date (predicted data) and if Column C equals Column A, then divide corresponding data in Column B by value in Column D.
A1:A6 have numeric values 1 to 6.
I want the conditional format values to change Based on the value in Cell B2.
If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.
IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.
I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.
Say I have data in one tab that is cells
A9, B9, C9, D9, etc.
On the next tab, I want the cell in D5 to equal A9, D6 to equal B9, D7 to C9, etc.
I can't seem to lock the cell correctly to do this. If I use =A$9 in cell D5 and then copy down in cell D6 I'll get A$9 also, but if I use $A9, in D6 I'll get $A10.
I am confusing of using the equal function in vba, I need to have some cells between sheet1 and sheet to be equal but i cannot just put = sign in the sheet as it may be deleted. Can I have it as VBA code? how to do that and where to put this code, in what event?
View 3 Replies View RelatedI have a workbook that has two worksheets. One worksheet is an input worksheet. A user will select a date from a drop-down list and type in the events that occurred on that date in 8 adjacent cells. The user selects a button that advances date and clears form. On sheet 2, whatever was typed into sheet one is saved via VLOOKUP formula. However, if I try to change something retroactively and select a previous date from the drop-down list, it clears everything in sheet 2 that was typed for any selected day.
View 1 Replies View RelatedIs there any way to make one cell on a separate sheet appear EXACTLY the same, including formatting and values?
As in this for example:
Pieces of Paper (Blue)
I would like to keep the formatting for (Blue) , color and bold, but I have not figured out how to do this.
I have a spreadsheet with Golf Stats on it, and on my latest import, the lookup stopped working. I am no longer able to look up any of the golfers names.I have attached a portion of the spreadsheet as an example.Lookup Failure.xlsx
Before the data was pulled in 'last name','first name' but is now 'first','last'... so that explains why I have the lookup column in Column A, because it used to convert the last,first format to first,last but that is besides the point. What my real problem is not being able to lookup Column D. As you can see in Cell L4, I tried to test if K4=D4 (Both say Nicolas Colsaerts but Excel does not say they are equal).
I am trying to hide rows if cetain cells in that row equal zero using a button on the page. Each cell has lookup formulas that will return a value. If coulmn B,F & I have a value of zero I want to hide the row. As of right now I am using the following code but I keep getting the following error message runtime error '13': type mismatch
View 4 Replies View RelatedColumns L, M and N are always fully populated.
Columns D, E and F may have some missing values, along with H, I and J.
Lets just concentrate on D.
I need to create a formula where I get the anser in C1 This is an average of L only where D has a value.
Can you make a TextBox equal the value of two Cells without first summing the two cells in the workbook?
TextBox1.value = Range.("B1").Value + Range("B2").Value ???
How can I select all rows where certain cells are equal to zero?
(i.e. in Column A
1 Bob
2 Joe
3 Smith
4 0
5 0
6 0
I want to select rows 4, 5 & 6.
I've used Go To>Special:Errors to write a macro to hide all rows with errors, but I also want to hide all rows that have zero values