Function and Data in a Cell

I was wondering if it is possible to enter data into a cell on the worksheet and there also being a function in that cell to

For Example cell A1 I enter data of '50' and the function automatically adds '100' leaving the data displayed in cell A1 as'150

Any help much appreciated
0
anonymous (74722)
2/4/2004 1:31:04 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
601 Views

Similar Articles

[PageSpeed] 25

Sarah

Not without resorting to VBA.

Well, there is way "after the fact".  Enter your number then enter 100 in
another cell.  Copy it and Paste Special>Add>OK>Esc to the original cell.

If you had a great many of these cells, that may be the easiest.  You can
select all the cells before Paste Special>Add step.

The code below pasted into a worksheet module will add 100 to any number you
enter in Column A.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      If Target.Count > 1 Then Exit Sub
       If Not IsNumeric(Target.Value) Then Exit Sub
     Application.EnableEvents = False
     With Target
         .Value = .Value + 100
     Application.EnableEvents = True
     End With
End Sub

Copy and right-click on the sheet tab and "View Code".  Paste in there.

Gord Dibben Excel MVP

On Tue, 3 Feb 2004 17:31:04 -0800, "Sarah"
<anonymous@discussions.microsoft.com> wrote:

>I was wondering if it is possible to enter data into a cell on the worksheet and there also being a function in that cell too
>
>For Example cell A1 I enter data of '50' and the function automatically adds '100' leaving the data displayed in cell A1 as'150'
>
>Any help much appreciated

0
Gord
2/4/2004 2:01:35 AM
Ooops!

Change "Target.Count" to "Target.Column"

Gord

On Tue, 03 Feb 2004 18:01:35 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>Sarah
>
>Not without resorting to VBA.
>
>Well, there is way "after the fact".  Enter your number then enter 100 in
>another cell.  Copy it and Paste Special>Add>OK>Esc to the original cell.
>
>If you had a great many of these cells, that may be the easiest.  You can
>select all the cells before Paste Special>Add step.
>
>The code below pasted into a worksheet module will add 100 to any number you
>enter in Column A.
>
>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>      If Target.Count > 1 Then Exit Sub
>       If Not IsNumeric(Target.Value) Then Exit Sub
>     Application.EnableEvents = False
>     With Target
>         .Value = .Value + 100
>     Application.EnableEvents = True
>     End With
>End Sub
>
>Copy and right-click on the sheet tab and "View Code".  Paste in there.
>
>Gord Dibben Excel MVP
>
>On Tue, 3 Feb 2004 17:31:04 -0800, "Sarah"
><anonymous@discussions.microsoft.com> wrote:
>
>>I was wondering if it is possible to enter data into a cell on the worksheet and there also being a function in that cell too
>>
>>For Example cell A1 I enter data of '50' and the function automatically adds '100' leaving the data displayed in cell A1 as'150'
>>
>>Any help much appreciated

0
Gord
2/4/2004 2:04:49 AM
Ooops!

Change "Target.Count" to "Target.Column"

Gord

On Tue, 03 Feb 2004 18:01:35 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>Sarah
>
>Not without resorting to VBA.
>
>Well, there is way "after the fact".  Enter your number then enter 100 in
>another cell.  Copy it and Paste Special>Add>OK>Esc to the original cell.
>
>If you had a great many of these cells, that may be the easiest.  You can
>select all the cells before Paste Special>Add step.
>
>The code below pasted into a worksheet module will add 100 to any number you
>enter in Column A.
>
>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>      If Target.Count > 1 Then Exit Sub
>       If Not IsNumeric(Target.Value) Then Exit Sub
>     Application.EnableEvents = False
>     With Target
>         .Value = .Value + 100
>     Application.EnableEvents = True
>     End With
>End Sub
>
>Copy and right-click on the sheet tab and "View Code".  Paste in there.
>
>Gord Dibben Excel MVP
>
>On Tue, 3 Feb 2004 17:31:04 -0800, "Sarah"
><anonymous@discussions.microsoft.com> wrote:
>
>>I was wondering if it is possible to enter data into a cell on the worksheet and there also being a function in that cell too
>>
>>For Example cell A1 I enter data of '50' and the function automatically adds '100' leaving the data displayed in cell A1 as'150'
>>
>>Any help much appreciated

0
Gord
2/4/2004 2:06:49 AM
Reply:

Similar Artilces:

Mounting old Data Stores
Hi. Due to problems with Win2003 SBS, I did a reinstall of Win2003 SBS sp1 from original CD-s. Before 'deleting' the old Windows, I made an ASR tape backup of the running server. It seams the ASR backup backed up only drive C: with all relevant data. As other data were on drive D: (Exchange data files, SQL data files,...), I backed up the D: drive too to a separate tape in a separate ntbackup session. Before the backup I used Exmerge to export all users mailboxes. Later I imported those mailboxes in a new Exchange installation. Unfortunately the user forgot to told me, they hav...

Plotting 1 curve from 2 separate x-axis data
I have stock portfolio info for January thru June, which plots easily. After portfolio changes, I have another plot for July thru December. Plots easily. These data are on 2 separate worksheets. Now I would like to plot a single curve for the year. The first 6 months is easy. How can I plot a continuous curve for the whole year without having to insert the first 6 months worth on the last 6 months worksheet? Not sure I explained this very well, but --- Dave The easiest way is to have both sets of data on one (a new?) worksheet. Then you can plot as with one or two series. Copy a...

Import external data-text files but placed in the next column rather than next empty row
I want to import text files in to one worksheet. The text files are sent daily and all the same format. However the text file names change buy an incremental number. Is it possible to create a macro that imports these text files, ignoring their exact filename but from the same source directory into one worksheet. Can you use wild cards for the name e.g. "********.txt"? At the moment I can manually do this, by selecting the cell below the last record then using the process "Get external Data" and following the wizard which works fine. As soon as I try to create a m...

Frx: linked data worksheet summing error
We use linked data worksheets for some of our reporting. The column format displays both current period and YTD. The YTD column is not calculating the correct amounts from the worksheet. For example, if we were in period 12, it would sum the YTD column as follows. period 1: add 12x period 2: add 11x period 3: add 10x period 4: add 9x ....and so forth period 12: add 1x Obviously, this gives us a huge error in the ytd column. Anyone have an idea why this is occurring? We are using the /cpo format for the linked worksheet. Thanks ...

Help! Selecting data according to date range
I'm attempting to setup a worksheet for reminding employees to rene their licenses. I have input the data where the data range is from ro 3 - 84 (this could increase or decrease with hiring/firing, etc.). Th columns range from A - K with column H being *date*. I would like to start a new sheet (sheet2) in this workbook with th range of months in a year. In each monthly section it would search th data range in sheet 1 and return the records with the correspondin dates for that month. For example: If a employee's license expires i January, the entire record for that employee would...

Changing values in a row based on a cell in the row.
Hi, I have a little matrix 5R x 6C. All cells within the matrix have data validation in them to restrict the input to "1" or "0". It is OK to have mutiple selections of "1's" in the same row, except if the user happen to select a "1" for the sixth or last cell in the row. If that happens I would like the other five cells in that row to have a value of "0". Something like A B C D E F 1 1 0 0 1 1 0 ok 2 1 0 1 0 0 1 Not ok 3 0 0 0 0 0 1 ok 4 5 -- Casey --------------------------------------...

How Check some cells have been updated
Hi All I want to keep trace some of cells in Row have been updated, the one of column update as today and time ? Does formula can handle this ? eg. A11 or B11 or C11 Updated, the D11 = today + time moonhkt Put this code in your worksheet module. To do this, right click the sheet tab at the bottom of Excel, click View Code, then paste code below into the sheet module. This code will put a time stamp in Col. D if any data is changed in columns A, B, or C. Give it a try. Hope this helps! If so, let me know, click "YES" below. Private Sub Worksheet_Change(ByVa...

Formula needed: count if cell CONTAINS certain text
What formula do I need to count the cells that CONTAIN a certain text (<> equal!) e.g. A1 = John A2 = Pieter A3 = William count cells that CONTAIN "i": 2 (A2 and A3) What (set of) formula should I use? Excel Help doesn't give me the answer. Wim On 7 Dec 2005 04:28:36 -0800, "Wim" <eisingspam@iafrica.com> wrote: >What formula do I need to count the cells that CONTAIN a certain text >(<> equal!) >e.g. >A1 = John >A2 = Pieter >A3 = William > >count cells that CONTAIN "i": 2 (A2 and A3) > >What (set of) formula...

VLOOKUP formula appears in the cell I need to see the result in
I am using the VLOOKUP function but when I complete the formula, the formula just appears in the cell I need the result to show up in. Can anyone help? Hi it is possible that the cell has been formatted to text prior to the entry of the formula: click on the cell, choose format / cells - number tab and click on general and click ok. then you might need to press F2 then F9 and enter to get it to work or tools / options / view tab, untick formulas or ensure that your VLOOKUP formula has no space before the =VLOOKUP(........ -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm...

about inserting data
hi, i am new to this newsgroup. I have little knwldge in Excel. How to create tables in Excel file. and also i want to execute the quries. How can i do that. thanks, regards, koti ...

distributing each characters on a cell
how can i distribute the letter in a cell to another cells? example: A1=HELP then get each character B1=H C1=E D1=L E1=P In B1: =MID($A$1,COLUMN(A1),1) Copy to C1:E1 -- Kind regards, Niek Otten Microsoft MVP - Excel "jjuan" <jp4the_clan@yahoo.com> wrote in message news:OFyGM%23$9GHA.4464@TK2MSFTNGP02.phx.gbl... | how can i distribute the letter in a cell to another cells? | example: | | A1=HELP | | then get each character | | B1=H | C1=E | D1=L | E1=P | | | And if you have lots to do in that column, you can select that column data|text to columns Fixed width dr...

Need help with data validation format
I'm trying to make this format mandatory when someone enters his/her data in a cell. The format would be this : "### ###". Therefor, if the worksheet user doesn't enter a chain like this one : "113 244", the data just won't enter. And yes, the space beetween the 3 first and last numbers has to be there. I can't seem to find anything in the Data / Validation menu that enables me to do this :confused:. Any help would be much appreciated. Thanks in advance. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~...

searching data
Is there a way in Excel to search multiple worksheets to collect "hits" of a particular entry? I have a 5-sheet workbook with about 2000 customer names, and I want to see if I have duplicates or repeats over all 5 sheets. >-----Original Message----- >Is there a way in Excel to search multiple worksheets to >collect "hits" of a particular entry? I have a 5-sheet >workbook with about 2000 customer names, and I want to see >if I have duplicates or repeats over all 5 sheets. >. > This feature is available in Excel XP & 2003 -- in the Fin...

Not able to Paste cells
I have a workbook with multiple sheets. I need to copy several cells from one sheet and paste on other sheets. I copy, then when I go to the other sheet, there is no paste option available. The file and sheets are not protected. The cells are not merged. Paste option is available for copy and paste within a sheet. I am trying to copy and paste values not formulas or hyperlinks. Has anyone seen this before and any idea why this is occurring? Thanks LL The paste option should be available if you are doing what you say. Exactly what else are you doing between the time you copy and t...

Addition on "text" cells
Hello everyone, I wonder if this is possible, cell A1 contains the text "P0001", cell A2 contains "P0021", A3 contains "P0041" and so on until "P0581". The values are separated by 20 units. If the cell is numeric, I could just make the formula on A2 as "+A1+20" and copy it down. This will give me the result from cell A2 21, 41, etc. Is there a way to make a formula or something so that I can have "P0001", then "P0021" and so on, on a single column. -- ledzepe -----------------------------------------------------------...

export public folder data?
Am in the process of performing an Alternate server recovery of an Exchange 2003 Information Store. The reason being that on the current server all the email has dissappeared from the public folder mail folders...its a very strange situation indeed. Hoping that by restoring an earlier backup to a different machine we can see if the emails will return. If they do, is there a way I could export the contents of these folders back into the current info store? I gather that Exmerge will only work on Mailboxes and not Public Folders... Many thanks in advance, Neil On Fri, 19 Aug 2005 08:58...

How to select data series to format? (alternative needed)
Is there an alternate way to select a data series to format in an XY Scatter chart? The only way I know of is to move the cursor close to the series line and right-click. But if the chart includes many data series and the lines are close together, it is difficult, if not "impossible", to select the desired line. I am looking for a method that presents me with a list of all the data series, and I select the one I want to "right click" (i.e. open the menu that include format, add trendline and clear, among others). Or something else that would not be so difficult to use. ...

SetFieldReqLevel function
Sorry ones again with right question. Does anybody know, where the JS function SetFieldReqLevel and other functions are documented? Thanks Radek Check Client Programming guide->Client side scripting->Field Types, Properties and Methods in the SDK Help. HTH, Niths "Radek Novak" wrote: > Sorry ones again with right question. > > Does anybody know, where the JS function SetFieldReqLevel and other > functions > are documented? > > Thanks > > Radek > > > ...

Data Not Displaying
I am having a weird instance with my report not showing a couple of data. This report is based on a table and has 7 groups. The weird instance is on group 1, where some of the data appeared but not all. For example, here are my data : ColA ColB ColC ColD .... So far, I have detected that in ColC, the data does not show in all rows. Ex.: I have 13 rows all together, but in rows 3 and 6, Col C is blank. I checked the data in the table and they're all there. It is just not showing up on the report. I checked the Conditional Formating for this textbox, but there i...

Pivot table returns "Problems obtainig data"
Office 2000 Prof. SR_1 with SP4. Created a Form in Access to display data from one "My table" using the Form "Excel Pivot Table". Exported "My table" and this Form to others databases. Works OK in all computers. In one computer is giving the following error message when trying to REFRESH de data: EXCEL Problems Obtainig data. Even when I tried to create it a new Form in that database using the same imported table, gives the same error message. ...

Selectively protect cell data
I would like to , when a cell is empty, be able to enter data, but, once data is entered have the cell protected. Is there any way to do this. I'm a novice. thanks for any help. Ken Ken, here is one way, Private Sub Worksheet_Change(ByVal Target As Range) '******unlock all cells in the range first********* Dim MyRange As Range Const Password = "123" '**Change password here** Set MyRange = Intersect(Range("A1:B10"), Target) '**change range here** If Not MyRange Is Nothing Then Unprotect Password:=Password MyRange.Lock...

Selecting a cell according to the cell value
Hello All VB expert Need help with a Macro. I would like to be able to select a cell according to a variable in a cell. For example: Cell A1 “31/04/2010”. Cell A3 = 31/05/2010 Cell A4 = 30/06/2010 Cell A5 = 31/07/2010 Cell A6 = 31/04/2010 In this case when I run the Marco, it will select Cell A6 because it is the same value to Cell A1. And if now Cell A1 = 31/05/2010, it will select Cell A3 because it is the same value to Cell A1 If you need more information or my explanation is not clear please let me know. Thank you very much for your help. Harn Se...

Autofill cells based on formula ?
I am using Excel 97. I have two columns. The user enters their numbers into column A and column B. When the user enters their numbers in Column A and B then goes to the next row, Column C is automatically filled based on a formula. Can this be done? If I understand correctly, yes, values in A1 and B1 can easily return a result in C1. You need to be more specitic in what you are trying to achieve. "Rich" <richmarin@earthlink.net> wrote in message news:46e4ef1a.0311161755.19caad43@posting.google.com... > I am using Excel 97. > > I have two columns. The user enters the...

Text data formula
I need some help, I'm very new to excel and I'm having a hard time making my spreedsheet work like I want it to...This is what i'm doing, I have Three columns, column A is labeled site with drop downs (Daksh, Tampa, Texas),column B has drop downs (Full verifications,Streamline Verifications, column C is labeled completed and I put a 1 everytime I complete a test. I then have the total of streamline verifications added up and inputed into a column D cell, and full goes into column e cell. What I want is to have all the Daksh entries that are full verifications go into colum...

Trying to post data from two companies in FRx
To explain this, I need to give a little background about our company. Our fiscal year is from 7/1 to 7/1. Our company was bought by another and transferred hands on 5/1/04 of this year. With the sale of the company, we created in Great Plains an identical company under the new ownership with the same accounts and use the same type of reports as we previously did. What I'm trying to do is pull data from the old company and the new company and print them into the same report. However, I'm still getting information from the new company only when I print reports. In the tr...