# Enter Value In A Cell Without Overriding Formula?

Feb 25, 2014
I need to write a formula that calculates after a value is entered. As in I want the cell to show 12% of whatever value is entered. The form needs to look as it does so I considered inserting a column and shrinking it down so it isn't visible, however this would be a last resort. I am building a spreadsheet for others to use and excel is not something they readily understand.

I am thinking something like:

if(0,0),ifx,(x*12%)

x being the entered value. Is this possible? I am hoping for some out of the box thoughts.

View 3 Replies
ADVERTISEMENT
Aug 15, 2008

This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear.

View 3 Replies
View Related
Apr 3, 2009

This site provides a partial answer to my problem- http://www.j-walk.com/ss/excel/tips/tip98.htm

That link worked for me but there's a problem with it, it does not work if a table has different types of validations.

View 5 Replies
View Related
May 1, 2013

I need to take a figure that appears in a cell, take off 20% vat from it and then calculate 16% off what is left, that is the figure I need in the new cell ? Show me exactly how to write that formular into the new cell?

View 3 Replies
View Related
Feb 8, 2010

I have attached a sample of the spreadsheet. The cell/cells in question are f32:f36 on the first tab.

Question #1 how this formula works or what it does.

Question #2 Is when I enter the required information in cells a32:e32 it enters an N/A in cell f32. When I than click on cell F32 and than click on the formula bar and hit enter the information populates.

View 2 Replies
View Related
Feb 2, 2007

I have an Excel 2003 application that automatically opens and closes a lot of workbooks. When done with a workbook, I close it with: ActiveWorkbook.Close SaveChanges:=False

On most users' machines, this works well. On one user's machine, the procedure is still asking: " Do you want to save the changes you made to 'filename'? "

at each workbook close. Considering that a typical run might inspect over 2,000 workbooks, this is a problem for the user. Is there a setting that can override "SaveChanges:=False"?. If so, where would I find it, and how would I programmatically turn it on/off?

View 2 Replies
View Related
Mar 12, 2007

Is there a way to add data into a cell that already had a formula? Lets say in cell A1 we have a formula like "=A1*A2". Would I be able to some how enter a value into cell A1 without changing the formula?

Example: enter value "5" into A2 and value "10" into A1 which would result in A1 value being "50" after value is entered.

I know that this creates a circular error but is there any other way to accomplish this?

View 10 Replies
View Related
May 14, 2013

I know I can put an IF formular into B1 =if(A1="sat",D/O,0) but obviously if people enter data to this cell i.e time they worked the formula goes is there anyway I can put a formular into C1 but have the result entered into B1?

A1 sat

B1 D/O

C1

View 7 Replies
View Related
Jan 19, 2008

How can a formula perform the equivalent of the keyboard alt enter in a cell?

ie Make =A1&A2 where A1 and A2 are cells containing text become

a1text

a2text

in the new cell.

View 9 Replies
View Related
Mar 27, 2009

Is it possible to have a formula in a cell which can be overwritten by manually entering in a number, but if the manually entered number is deleted, the formula remains in the cell.

View 9 Replies
View Related
Dec 20, 2006

I am trying to make a very simple macro, that when it is run, it inserts a formula into the selected cell. But I am having problems with quotation marks etc... As it doesn't understand the 3rd and beyond quotation marks.

Sub Macro()

ActiveCell.FormulaR1C1 = "=IF(A1<3000,"Small", "Large")"

End Sub

I'm sure there may is a way of coding it in VBA properly, but I plan to use this method for a range of basic formulas. The idea is this is saved in the Personal Macro Book so I can start a column of formulas by running a Macro rather than typing it in (the example above is a simplified formula, the actual one is fairly long) - then it can just be dragged down on every Workbook I need it in.

View 4 Replies
View Related
Sep 8, 2007

Automatically copy formula to next cell when i enter something in perticular cell ....

View 11 Replies
View Related
Sep 9, 2007

I want to copy formula from previous row to next cell when i enter something in perticular cell.

i.e

--Colomn A --- Colomn B -- --------Colomn C

1 01-09-07 ----- John ----------=vlookup(b2,$s$1:$t$10,2,false)

2 01-09-07 ----- Smith -------- =vlookup(b3,$s$1:$t$10,2,false)

3

4

5

Now if i enter date in cell A3 then cell C3 should be automatically filled/copy formula as celll C2. and so on......

then if i enter data to A4 then cell C4 should be automatically filled/copy formula from cell C3.

I have also attached example file.

View 9 Replies
View Related
Nov 3, 2008

The formula =SUM(E5:E39)-SUM(E45+F41) is what I'm using to enter my total man hours for the week (E5:E39) and subtracting my overtime(E45) and vacation hours.(F41). I have to enter OT and vacation on the discription cells to get the numbers to enter into the proper cells. I would like to use a formula to take the total manhours over 40 and enter them into the overtime cell.

View 9 Replies
View Related
Jul 3, 2014

I am having a terrible time with Excel today. The version I'm using is Excel 2010.

I just want a simple division formula in a cell, formatted to accounting. This should be really easy, but it isn't reacting the normal way.

The simple division is =13588/12

The output in accounting format should be 1,132.33.

Every time I enter this formula into a cell that is pre-formatted to accounting, the result is 1132 1/3. It also erases my formula and replaces it with the value.

View 7 Replies
View Related
Nov 4, 2005

trying to eliminate error messages needed for math formula. With "0" in cells

works good "anyone"

View 9 Replies
View Related
May 22, 2009

I have following Array formula in Cells N1:N3.

HTML =SUM(IF(O$4:O$1000="Total I/O Points",N$4:N$1000,0))

Each formula has different text in "".

There are a few macros in this workbook that delete and/or insert rows.

When that happens, the formulas listed above get modified with different references. How do I restore the formulas back to the same range reference [O$4:O$1000]?

View 2 Replies
View Related
Oct 28, 2009

I have an excel sheet wherein I want to enter the following formula into cell G11 when aparticular Macro is run by the user. I am using the following command but it is showing some error.

View 4 Replies
View Related
Nov 15, 2009

I am trying to write a macro which will get values from column B and C and print the result on column D using a simple function like the one before:

D2 = "text" & B2 & "text2" & C2 & "text3"

I need this to be done in the macro, so that when I click the button, it will automatically create column D. Column B is formula and column C is constants.

I tried something like the one below:

View 9 Replies
View Related
Oct 9, 2012

In Q3 I have a formula which determines the "next" date from today. In P3 I need to enter a formula which will return the value of the range (P6:P37) which is in the same row but different column as the value calculated in Q3.

View 1 Replies
View Related
Mar 3, 2014

If the value in the Discount Y/N column is equal to Y, the formula should check if the value in the Tenure (Yrs) column is less than 5.

View 3 Replies
View Related
Jul 31, 2009

I have the formula:

=HYPERLINK("[Book1.xlsm]'Sheet1!A1","To Reachback")

I may however change the name of Book1, therefore rendering the hyperlink formula useless. Is there a way to enter something in place of Book1 that will return the current name of the workbook?

If there isn't, I will have to create a VBA program to update the hyperlinks, and will therefore have additional questions about that shortly.

View 9 Replies
View Related
May 21, 2014

I have a huge (for a newbie ) spreadsheet where every item is associated with several key words. There about 500 key words, all in the same column, and I have to build a table identifying the total frequency of each key word. Basically, the first column of the table I've created lists all the possible key words, and the second one is all the COUNTIF formulae, each one being associated with its corresponding key word. The formula I need to use is this one :

=COUNTIF($D$2:$D$8486;"corresponding key word from column 1")

The formula works well and my table looks fine, but the task of copying and pasting 500 key words into each occurrence of the formula is pretty daunting! is there an automatic way to enter each key word into the corresponding formula without having to do it manually? Otherwise, is there a more direct way or another formula that would give me the information I need? Surely there's a more efficient way to do this, but I just don't know how!

View 1 Replies
View Related
May 26, 2009

VBA automation -

Excel added the formula - but they all return "Not Found". After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar. In VBA I tried to use the:

objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....

The Excel Formula bar for the cell is just empty (blank)

Using only the Formula property:

objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....

The correct formula is created in each cell, but. Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets.

In Excel - my vba code successfully constructs these formulas: In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.

View 5 Replies
View Related
Dec 8, 2011

I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN “Q” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.

I want the Macro to do the following THREE THINGS:

1. IF the Value in COLUMN L is “0” THEN enter “n/a” into COLUMN “Q”

2. IF the Value in COLUMN L is “2” THEN enter “n/a” into COLUMN “Q”

3. IF the Value in COLUMN L is “1” THEN use a formula that looks like this:

= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a “2” in COLUMN L and then MULTIPLY that Result against those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN “Q”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.

Here is an example of how the formula will work:

I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are “Times” and “PM” and contain a “2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN “Q” IS 10.395.

Here the DATA SET:

Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T

Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ

This is what is happening in Column Q Explanation of Formula

Row 2

Times

Cont

2

28 28 n/a

n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".

Row 3

Times

Cont

1

404 1194 413.4740369

O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:

1. The "Org" value of "Times" in Column C

2. The "S Function" value "Cont" in Column H

3. The "P ID" value "2" in Column L

[Code] ...........

View 9 Replies
View Related
Sep 24, 2007

I have a simple formula in cell A2

=A1

But it will not calculate. I have gone to Tools - Options - Calculation to see if calculation is set to Manual but it is Automatic. Is there another setting or have I exceeded some limit?

View 9 Replies
View Related
Jun 9, 2009

I'd like to be able to add 10% to column D and have the prices in A, B, C change accordingly. Is there a formula for this?

A B C D

2009 Distributor CASE Price2010 Distributor CASE Price2010 Distributor EACH PriceVariance from 2009 price135.00148.000.0592108.25100.000.040062.8875.006.250066.6096.008.0000

PS: Right now I have it set up working the opposite way, the prices are entered and my formula tells the user by what % the price has changed. The user wants to be able to tell the formula what % is desired and have the prices change accordingly.

The formula I am using for the way column currently calculates is:

=IF((G2=0),"",(((G2-F2)/F2)*1))

View 9 Replies
View Related
Jun 1, 2014

I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column <> 1 Then Exit Sub

Application.EnableEvents = False

[Code].....

View 4 Replies
View Related
Jan 28, 2014

Is it possible to enter a formula using two summarized values within a pivot table?

For example, if the original column was "X" and within the pivot table i'm using "Sum of X" and then another for "Average of Y".

Can I then divide "Sum of X" by "Average of Y" or any other similar calculations between 2 summarized fields?

View 1 Replies
View Related
Jan 4, 2009

This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet.

Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?

View 4 Replies
View Related