copying a cell to another cell automatically

Hi,
I'm trying to set up my worksheet so that if the value in column E is
changed to 100%, the value in column A of that row automatically changes to
100, regardless of its original value. At the same time, I don't want to
lock down column A - the user can enter any value in here when E is not
100%. How do I go about doing this? Do I need to write a macro?
Thanks!
Amit


0
1/11/2005 6:06:57 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
506 Views

Similar Articles

[PageSpeed] 46

Hi,

You'll have to do this as a little macro. Go into the VB editor and add the 
following into the relevant Sheet.  

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 5 And Target = 100 Then
        Cells(Target.Row, 1) = 100
    End If
End Sub

You might want to check for ABS (target - 100) < some small number rather 
than equality. 

HTH, David Jessop

"Amit" wrote:

> Hi,
> I'm trying to set up my worksheet so that if the value in column E is
> changed to 100%, the value in column A of that row automatically changes to
> 100, regardless of its original value. At the same time, I don't want to
> lock down column A - the user can enter any value in here when E is not
> 100%. How do I go about doing this? Do I need to write a macro?
> Thanks!
> Amit
> 
> 
> 
0
1/12/2005 11:55:01 AM
Awesome, thanks!


"David Jessop" <DavidJessop@discussions.microsoft.com> wrote in message
news:49A9D8EE-18AA-4100-8233-2C8F52A02288@microsoft.com...
> Hi,
>
> You'll have to do this as a little macro. Go into the VB editor and add
the
> following into the relevant Sheet.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>     If Target.Column = 5 And Target = 100 Then
>         Cells(Target.Row, 1) = 100
>     End If
> End Sub
>
> You might want to check for ABS (target - 100) < some small number rather
> than equality.
>
> HTH, David Jessop
>
> "Amit" wrote:
>
> > Hi,
> > I'm trying to set up my worksheet so that if the value in column E is
> > changed to 100%, the value in column A of that row automatically changes
to
> > 100, regardless of its original value. At the same time, I don't want to
> > lock down column A - the user can enter any value in here when E is not
> > 100%. How do I go about doing this? Do I need to write a macro?
> > Thanks!
> > Amit
> >
> >
> >


0
1/12/2005 1:41:37 PM
Reply:

Similar Artilces:

Determine cells that drive conditional formatting?
Example: Cells A1:A4 have conditional formatting set up that states if they are equal to cell A10 they will be highlighted yellow. Is there a way to quickly see what cells drive conditional formatting? In other words, we can use Edit -> Go To Special to see which cells have conditional formatting applied; however, this doesn't show us that A10 is involved. Since there isn't a formula directly involved, we can't trace precedents/dependents. Can we only know that A10 is involved in the formatting of A1:A4 by selecting those cells and going into the conditional format...

Count "," in a cell
Dear expert, Is it possible to count "," in a cell please? I used this ... but does not work =COUNTIF(FO93,",") Say below ... can it be solved? 2,5,3 22,25,5 5,2,3,4 Try this… =LEN(FO93)-LEN(SUBSTITUTE(FO93,",","")) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Elton Law" wrote: > Dear expert, > > Is it possible to count "," in a cell please? > > I used this ... but does not work > > =COUNTIF(FO93,",&qu...

How to get info filled in automatically
I make a roster for my work with multiple items for each person. An example of what I am looking to do: In cell A1, I have a drop down menu with all employee's names. Cell B1 is for their ID number and cell C1 is for their radio ID #. I wanted to see if their was a way for when I select a name in the drop down menu of A1 for B1 and C1 to automatically fill with that employees' respective ID numbers? Hi Let's say you have a list of employees with their relevant numbers on Sheet2 in cells A2:Cxxx With your dropdown list in cell A2 of Sheet1, enter the followin...

moving data from one sheet to another
I have records in sheet1 as follows: A B C D E 1 NAME SEX SUBJECT COLLEGE WHETHER SELECTED 2 A MALE MATHS XAVIER YES 3 B FEMALE ENGLISH SEBASTIAN 4 C FEMALE MATHS PAULS 5 D MALE PHYSICS XAVIER YES 6 E MALE CHEMISTRY PAULS YES 7 F MALE PHYSICS SEBASTIAN YES 8 G FEMALE MATHS XAVIER YES 9 H MALE PHYSICS PAULS 10 I MALE MATHS SEBASTIAN Now, I want to scan entire data upto last cell of the range and move(cut and paste) those of the selected candidates, Sheet2 of the same book.After moving, the name o...

text to fit in cell
How do I make the text fit in a cell. I want to be able to print the page with all of the words in the cell even if it has to make the cell larger, without going over into the next cell. How do I do this? Hi goto 'Format - Cells - alignment' and check 'Wrap text' -- Regards Frank Kabel Frankfurt, Germany DaveB wrote: > How do I make the text fit in a cell. I want to be able > to print the page with all of the words in the cell even > if it has to make the cell larger, without going over > into the next cell. How do I do this? Dave Format>Cells>Alignm...

automatic makefile generation
Is there any support for automatic makefile generation for vs.net projects ? Apart from makefile projects which is of no use. Ashish Hello Ashish, Thanks for posting in the group. The Export Makefile option was removed in VS.NET because of problems people had it making this very simple function work correctly. (The problems related to the handling of the Tools|Options|Directories settings for include path and library path - those settings weren't included in the exported makefile, so the makefile would only work correctly with a correctly configured command-line build environment....

1 Cell 2 Values?
Hello All, Is it possible in excel to have it return two values in one cell separated by a comma and spaces? For example I want it to count the number of times in a month the value was positive and the number of times a value was negative and in the cell I want it to show A1: +, - Is this possible? Thanks to all, Shhhh Shhhh wrote... >Is it possible in excel to have it return two values in one cell separated >by a comma and spaces? > >For example I want it to count the number of times in a month the value was >positive and the number of times a value was negative a...

Totaling cells from separate worksheets to master form
If I have 2 separate worksheets with individual cell values and want the total to show the sum of obth cells, how do I do it? Nelson Suppose those values are in F10 on one sheet and in G6 of the other sheet, then put this in the appropriate cell of your master sheet: =3DSheet1!F10 + Sheet2!G6 to add them both together. If your actual sheet names contain spaces you will need to include apostophes around the sheet name, like: =3D'First Sheet'!F10 + 'Second Sheet'!G6 Hope this helps. Pete On Apr 21, 10:31=A0pm, snake941 <snake...@discussions.micros...

can't copy text to new pub document
I am trying to copy entire pages from one Publisher 2007 doc to another. It will copy the text in some text boxes but not in others. I don't understand why this is happening. How do I get it to copy the entire page as is? I am using Ctrl A or Select All. Thank you. Oh, another thing to add: Even if I select only a single text box, it will not copy any text. "lindalou" wrote: > I am trying to copy entire pages from one Publisher 2007 doc to another. It > will copy the text in some text boxes but not in others. I don't understand > why this is happening. How d...

Format Excel cells
Anyone could help me to solve this problem : How to format the selected cells so that it allows other users to "paste values" only(number from 0 to 100) to the cells. It rejects "paste" function which put formula, format etc into the cells. Thanks a million ! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreade...

cells changing from number to date
I am putting a number in a cell, then tabbing to the next cell, the previous cell changed from a number to a date. How do I keep the number there? What are you inputting, a fraction?if so, format as # ??/?? -- HTH RP (remove nothere from the email address if mailing direct) "mwhite@srgmac.com" <mwhite@srgmac.com@discussions.microsoft.com> wrote in message news:E71CED6D-8370-44D1-A4BF-9A6B352205F3@microsoft.com... > I am putting a number in a cell, then tabbing to the next cell, the previous > cell changed from a number to a date. How do I keep the number there? ...

Problem
i all I am having a strange issue with my excell file since a while. This is the second year I am using it without problem. I start a workday with a new sheet so every morning I just copy (Edit/Move or Copy Sheet) the latest sheet, rename it and use it. All of a suddent when I perform this operation I have a prompt that says "a formula or sheet you want to move or copy contains the name 'aaa', which exists in the destination worksheet. Do you want to use this version of the name?" I would hit yes to proceed, then I would get over 50 other prompts with differ...

Empty cell in vlookup
hi, is there a way to have a vlookup see an empty cell and rather than filling it in with a 0 it could leave it blank? here's my vlookup formula =IF(A21="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)) Thank you in advance for your help :) =IF(A21="","",if(VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE)="","",VLOOKUP(A21,'PN Source Code'!$A$1:$H$25,8,FALSE))) -- Please click "yes" if this post helped you! Greatly appreciated Eva "confused" wrote: ...

How do I return to prior cell?
After I have traced a formula using Ctrl+[, what is the keyboard shortcut to return to the same cell where I traced the formula? Thank you! Press Ctrl+] Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "NYC18" wrote: > After I have traced a formula using Ctrl+[, what is the keyboard shortcut to > return to the same cell where I traced the formula? > > Thank you! Thank you, but I can only get Ctrl+] to work if all the referenced cells are within one worksheet. If I am writing formulas usi...

Linking Non-Contiguous cells
I am trying to link different rows of data in Excel to a Visio diagram as labels. In Excel I have a document that has a header row with a multiple rows of data following. Each picture on my Visio diagram corresponds to a different row on the excel worksheet. So to link a lable to each picture in Visio I need the first row(header row) and the corresponding data row. In all but one, these rows are not going to be next to each other. I've tried to highlight the header row and the data row and use copy, paste special in Visio and it pastes every row between them instead of just the...

cell value goes up by one count
,Hello Again Could i get some help with this. How does one get an activecell , that has a value of 1, that is copied from sheet1 to sheet2 so that the value changes from 1 to a 2 and so on depending on how many times I copy a sheet. I hope this makes sense Thanks in Advance Allan =sheet1!(a1). I think you are asking for this formula. It will change the value of current cell (doesn't matter on which sheet you are currently working) to value of Sheet 1's A1 cell. Means if Sheet 1 A1 = 100 and you are currently working on Sheet 2 F1, then this formula will show the F1 value ...

Outlook 2003: Copy Command Missing on Right-Click
In Outlook 2003, when I right click on an email message, only the move command is available; the copy command is missing! The only way I can copy an email message to another folder is to drag it with the right mouse button and then it comes up as an option. Is there a fix to this bug? Thanks... ...

sorting two columns of merged cells
I have two columns of merged cells. two cells in each column are merged in each row: ie A1 and B1 are merged into one cell, a2 and B2, etc. The next column is the same; C1 and D1 are merged, C2 and D2, etc. Is there any way I can sort these columns? I need a descending sort by col A and B. Im using Excel 2003 Hope this is clear. Jim Don't merge cells. Look in the archives of this group for countless reasons why not; you've just found one of them. -- David Biddulph "bigjim" <bigjim@discussions.microsoft.com> wrote in message news:...

Move 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 009601098-9 ...

Using cell value in VBA sub
I'm using the following sub Sub FindRow() Dim rngFound As Range Set rngFound = Range("A:A").Cells.Find(What:="test", _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry ""test"" was not found" Else rngFound.EntireRow.Select End If End Sub My problem is that if I excange Test with a1 - wanting to look the value in cell a1 I always get the first blank a-cel...

automatically assign value to query
Hello, Ive got a database where staff login just using their name, and the login button has a macro assigned as follows Condition : Not IsNull([cboCurrentEmployee]) Action : SetTempVar Arguments : CurrentUserID, [cboCurrentEmployee] Certain contacts in my database are assigned to a member of staff and call sheets are set up using a query that requests that member of staffs name. Is there a way of automatically setting the criteria of the query to only show clients that are assigned to the member of staff that has logged in? Thanks, please let me know if i need to explain th...

Does Excel support mixed formatting of a string in a formula cell, yet?
Excel is one of the most spectacular apps ever created. But one feature I really need is mixed formatting of a string whose cell is a formula. By mixed formatting I mean, you know, making selected (as opposed to all) character(s) of the string bold, italic, a different color, sub- or superscripted, etc. In Excel 2002 you cannot do that in a formula cell (right?). In edit mode, the user would, say, press F9 to display the calc'ed string, selectively format it as usual, then cancel edit. Excel would save the formatting as a separate mask. What could be easier? The user would ...

Going to a page depending on a combobox or a number in a cell
Hello everyone, I've a problem and been searhing for so long and still need help. I want to use a Combo Box as a shortcut list that will choose a sheet to go. For example; When I choose "Poems" form the Combo Box list, I want Excel to open the sheet "Poems". Alternativley; If there is a number in cell A1 as "1" than go to (or jump to) "Sheet1", if the number is "2" than go to "Sheet2" , if the number is "3" than go to sheet "Poems" etc. etc. How can I make this? Really need help.. Kindly regards....

Export range of cells as high resolution image
If I try to cut & paste into a graphics program (I tried Photoshop and Paint) I get a low resolution image (96 dpi), suitable for viewing on screen, but not for printing. How can I get an hi-res image, in gif or (better) EPS format? Thanks in advance, Andrea Zadig wrote... > If I try to cut & paste into a graphics program (I tried Photoshop and > Paint) I get a low resolution image (96 dpi), suitable for viewing on > screen, but not for printing. > > How can I get an hi-res image, in gif or (better) EPS format? > > Thanks in advance, Hi Andrea, I have two m...

Automatic Registration Button Problems
I am using the code below for a button in a form. It searches the Leaders table to find all leaders who are directors. Then registers all of these leaders for whichever event the button is in. However, when I press the button, it does something that I don't understand. Right now I have two events in my test database that I could register for. Both of them have about 12 people registered for them. I typed each of these registrants in. When I create a third event to test my new nifty button, it seems like it takes the first two events and doubles their registrations and register no on...