# Alternative To CTRL-SHIFT-ENTER Array Formula

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
ADVERTISEMENT
Jun 24, 2009

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.

View 9 Replies
View Related
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
Feb 21, 2008

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.

View 9 Replies
View Related
Mar 4, 2009

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?

View 9 Replies
View Related
Apr 10, 2008

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

View 13 Replies
View Related
Dec 12, 2008

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 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
May 26, 2014

know 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 Related
Jun 10, 2013

I 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?

View 6 Replies
View Related
Sep 18, 2012

complex formulas in Excel but managed to work out and create the following formula;

{=IFERROR(INDEX('owssvr(1)'!$AE:$AE,MATCH($A9&AK$1,'owssvr(1)'!$C:$C&'owssvr(1)'!$E:$E,0)),"")}

Basically I want to return a call quality score if the name and week match my criteria.

The above formula works perfectly, however, it is very slow and the sheet takes ages to update.

Is there a quicker and more dynamic way to achieve the same result?

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
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
Dec 11, 2008

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?

View 2 Replies
View Related
Apr 21, 2006

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

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

View 8 Replies
View Related
Jan 27, 2010

The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data:

K28 = Last month balance

Column E = Type of expenditure

Column D = Values

Column B = Payments

K23 = Currency conversion rate

View 2 Replies
View Related
Feb 21, 2010

Sheet2ABCDE1Start Date 01/01/201005/01/2010Holiday211/01/2010Holiday3Days1445the date to be back to work 19/01/2010Excel 2010

Worksheet FormulasCellFormulaB5=WORKDAY.INTL(B1,B3,16,D1:D2)

but how i can get the same result with Xl 2003 or 2007

View 9 Replies
View Related
Jul 14, 2008

I have got several columns of data with subtotals, months, total unit per month, total sales per month, Example

jan feb

total unit total value total unit total value

60 £600 35 £180

SOLUTION

dateunitsale01-Jan6060001-Feb3518001-Mar335801-Apr911220

I am having to use = sign (=A10 --) transfer the information to solution, is there a formula that I use instead. I have try $A10,A$10 to ancher the column?. ideal I need formula that read alternative columns for date, unit and sales- that I can copy down?

View 9 Replies
View Related
Mar 18, 2009

I have the followings in my spreadsheet.

a) Column A: 900 serial nos of Work Orders. Each is unique.

b) Column B: Parties responsible for each Work Order no. Certain parties will have more than 1 serial no in Column A.

Note: Column A & B are the main reference.

c) Column C, D & E: List of parties from three different locations performing works base on serial nos in Column A.

I would like a list of serial nos performed by parties in Column C, D & E base on Column A. I can't use the autofilter or sort function on each individual parties as this is very time consuming and moreover the info keeps changing ever 2-3 days. What I require is a simple summary list where I just need to paste the info from Column C, D & E into a special table and it would list down the serial nos from Column A. I had tried the INDEX & MATCH functions but it won't work as I have some parties performing more than 1 serial number. Would it be possible to create a table to perform the said task. These data entry and retrieval has taken minimum 4 hours of my time everyday and it takes longer when certain lists are revised.

View 9 Replies
View Related
Dec 1, 2009

I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.

For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

View 3 Replies
View Related
Apr 11, 2008

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double

Dim shour As Integer

Dim smin As Integer

Dim ehour As Integer

Dim emin As Integer

Dim stod As String

Dim etod As String

pday = 8

pnight = 12

shour = Hour(st)

smin = Minute(st) + shour * 60

If (shour >= 8 & shour < 20) Then

stod = "day"

Else

stod = "night"

End If

ehour = Hour(en)

emin = Minute(en) + ehour * 60

If (ehour >= 8 & ehour < 20) Then.................

View 8 Replies
View Related
May 17, 2014

I'm trying to add an enter space to text with the combination of a loop and an array, but I cannot find the right combination so that the final result appears as so:

ArrayItem

Desired Text

or via excel

=NamedRange&"

Desired Text"

Here is the code for the loop, trying to add the appendage to the first item after the if.

[Code] .....

View 1 Replies
View Related
Oct 14, 2005

I typed in the word array into Excel Help and found this item

"About array formulas and how to enter them"

I am trying to duplicate the first example and cannot

Ex: =Average(if(C5:C14="Europe",D5:D14))

I tried something similar to this (diff cell ref) and I get #VALUE

Do I need to check off an addin or something?

View 9 Replies
View Related
Jan 26, 2007

An OzGrid member kindly provided this formula from another post I made here:

http://www.ozgrid.com/forum/showthre...837#post326837

= SUMPRODUCT((--(WEEKDAY(Database!$E$7:$E$3973,2)=(ROW()-3))*1)*(Database!$C$7:$C$3973= 'Pattern Analysis'!$A$4)*(Database!$F$7:$F$3973='Pattern Analysis'!C$3))

My problem is that the =SUMPRODUCT is slowing my sheet down considerably and I was wondering if any of you formula whizzes can convert it to a less processor hungry alternative such as DCOUNTA (if that's possible) or suggest a better method.

View 2 Replies
View Related
Jan 16, 2008

I've created a very large spreadsheet (4096 calculations) and I'm using array formulas for a large number of cells, which leads me to my current predicament. All the formulas are written in, but I haven't done the necessary ctrl+shift+enter after finishing all of them (there is only slight variation in each calculation so I produced them in an iterative manner) and I was wondering if there was a way besides selecting each cell individually - pressing F2 - Then pressing CTRL + SHIFT + ENTER to make all my formulas array formulas.

View 5 Replies
View Related
Apr 23, 2008

I'm trying to enter a series of formulas referencing the first cell of each row.

With Range("A40")

Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).FormulaR1C1 = "= COUNTIF(Details!R2C2:R65536C2,RC1)"

Range(.Cells(1, 1), .End(xlDown)).Offset(0, 2).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC[-2])*('Details'!R2C11:R65536C11=RC1))"

Range(.Cells(1, 1), .End(xlDown)).Offset(0, 4).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"

Range(.Cells(1, 1), .End(xlDown)).Offset(0, 5).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-7))"

Range(.Cells(1, 1), .End(xlDown)).Offset(0, 7).FormulaArray = "=SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"

Range(.Cells(1, 1), .End(xlDown)).Offset(0, 8).FormulaArray = "=RC[-1]-SUM((Details!R2C2:R65536C2=RC1)*(Details!R2C11:R65536C11=RC1)*(Details!R2C4:R65536C4>TODAY()-30))"

End With

While this code works for the first formula, the following 4 are arrays, and for some reason, will only reference the first A40 cell.

View 9 Replies
View Related
Jan 24, 2014

I need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.

i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.

I've attached a sample workbook to show.

MDT Report December 201322(FILEminimizer).xlsx

View 2 Replies
View Related
Mar 8, 2014

I have the formula but it will always change when i shift my cell down

=MAX($G$21:G35)

When I shift my cell G21 down, it will change to below

=MAX($G$22:G35)

The problem is that I do not want the formula to change. How can I do it ?

View 5 Replies
View Related
Mar 10, 2009

How can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??

View 4 Replies
View Related
Apr 7, 2009

Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

View 6 Replies
View Related