# Formula Must Be Confirmed With CTRL+SHIFT+ENTER Not Just ENTER

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

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

View 3 RepliesADVERTISEMENT

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?

i am trying to modify and existing array formula

=VLOOKUP(R2,TRIM(Codes!$C$3:$D$283),2,FALSE)

but ctrl shift enter does not seem to work. has anybody else encountered this problem?

FYI auto calculate is on, lookup value and table array are all formatted the same. as i said, the formula works but i need it updated for one extra row.

I recently upgraded my computer. I noticed I can no longer enter array formulas. When I tried to enter an array formula using CTRL+Shift+Enter, nothing happens. I don't get any error, just nothing happens.

If I do the same exact thing on my old computer, it works - formula is converted and I see the braces {} added as part of my formula. Am I missing a macro or add-in? I'm using Excel 2002 - same version on my old computer. Is there another way to generate an array formula besides using CTRL+Shift+Enter?

I have a ton of cells that contain formulas with arrays. How can I update these without:

clicking the cell

clicking the formula bar

then hitting CTRL+SHIFT+ENTER

I have a couple of array formulas that were expertly suggested on this very forum. They function spectacularly, but now I need to incorporate them into my code.

The catch is that I am not sure yet if I will a WorksheetFunction solution, or more the likes of the actual "live" formula, e.g., "=SUM(A:Z)"

In either case, I am not sure what the equivalent of CSE is in VBA, or whether the braces/curly brackets can be manually inserted in the live formula.

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].....

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.

For Excel 2007, does this key function only work after typing in a formula into a cell? I tried pasting the formula and am unable to get the {}.

Is there some setting in preferences to allow this?

If ur using a formula that uses Control Shift Enter to activate it in Excel,

How do u use that formula in VBA? ....

I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.

View 8 Replies View Relatedknow the keybaord shortcut to select a range? Indeed, how can i trick excel to use CTRL SHIFT Down in a range with blank data and to select the last data in the range?

View 1 Replies View RelatedI was thinking of how the Worksheet_Change(ByVal Target As Range) macro can be used to paste values instead of formula when the user uses Ctrl-C and Ctrl-V. Instead of assigning a macro to a command button for user to activate.

Suppose I have a worksheet where the range ("D7:D56") is where I would want the user to paste his values in it.

I have to factor in the possibility that these values have formulas attached to them, and that the user is not tech-savvy enough to know about the "paste special --> values" functionality of excel, and chooses to use the Ctrl-C, Ctrl-V method instead.

What then appears are ####### which might alarm the user.

Neither would we want the user to press a button (which we can assign the xlpastevalues macro as an alternative) as that adds an extra step for the user. There is also a chance the user might not use the button, or overlooked it.

Is it possible to use the Worksheet_Change(ByVal Target As Range) macro, or sth similar, to paste values instead of formula when user uses Ctrl-C and Ctrl-V?

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 RelatedI 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]?

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 RelatedI 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:

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 RelatedIf 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 RelatedIs 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?

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

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.

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.

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 RelatedI 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.

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.

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!

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] ...........

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?

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))

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