Array Formulas Across Sheets

Sep 12, 2012

Do array formulas work if the array is across sheets instead of across columns or rows? I'm getting a #Ref! error when I try to use an "across sheets" array.

I'm doing this:

=sum(if('Sheet 1:Sheet 2'!A1=1,'Sheet 1:Sheet 2'!A2,0))

With a CTRL + SHIFT + ENTER return.

View 5 Replies


ADVERTISEMENT

Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets

Dec 15, 2008

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

View 9 Replies View Related

Array Formulas : Two Conditional

Nov 28, 2008

Attached is the file where I want to derive Min Date of seq A and asset Type Char.

SeqAsset TypeStDateEndDate
AChar21-11-200828-11-2008
BEnv22-11-200829-11-2008
CProp30-10-200830-11-2008
AChar03-11-200801-12-2008
AChar04-11-200801-12-2008
AChar05-11-200801-12-2008
AChar06-11-200801-12-2008
AChar07-11-200801-12-2008

I want to find the Minimum Date of Seq "A" and of Asset type "Char". I used following Array Formulas but showing the correct answer 30-10-2008MIN(IF(A2:A9="A",C2:C9="Char"),(D2:D9)) But Istead of 03-11-2008 it's showing 30-10-2008 date of seq C and of asset type Prop

View 2 Replies View Related

About Array Formulas And How To Enter Them

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

Way To Speed Up Array Formulas

Apr 20, 2004

I have been lurking around for past month learning lots from MrExcel's wonderful web site. One of the many things I learned was how to improve my spreadsheets with Array Formulas, but today I ran into a problem on a new spreadsheet I'm building for work.

Here's the problem: I have 39 coworkers. For each coworker, I have 14 Array Formulas using SUMPRODUCT command with up to 5 "conditions", similar to this example:

{=SUMPRODUCT((user=$A$5)*(task=AO$3)*(DateChecked>0)*((Error="Error Removed")+(Error="Error Converted to an FYI")))}

Each condition such as "user" and "task" is a static named range of 5000 cells. This spreadsheet will hold one week's worth of my coworkers' work. This past week they have processed about 2500 items. To be safe, I doubled this number to determine the static named range size.

For each worker I have 56 columns (one for each possible task which a coworker can process).

So for each coworker, there will be 14 * 56 = 784 Array Formulas.

Currently my spreadsheet only has a single coworker defined, so I only have 784 Array Formulas, but it takes 35 seconds at 100% CPU Utilization when I press F9 (Calculate all formulas). Right now, I am running this on my Home PC (a 400 MHz PII PC with 256 Megs of RAM, OS is Win2000 at SP4 maint level and Excel 2002), but it is equally slow at work (1.7 GHz Celeron with 256 MB of RAM running Win2K SP4 and Excel 2K).

I haven't tested yet, but even if I assume a linear progression, with 39 coworkers I am thinking it is possible the amount of time for Excel to recalculate all the formulas will be 39 times longer than it is currently. This will be close to 22 minutes. That is a long time to wait! It will be even worse if my testing shows the amount of time Excel takes to evaluate the array formulas is exponential instead of linear...

...784*39 = 30,576 formulas...

View 9 Replies View Related

Array Formulas With 3 Critieria

Apr 18, 2006

I am having little luck creating an array to work with 3 critieria. Here is what I am trying to do: In the attached spreadsheet I am trying to show a total for each "Global Process" based on if "Reason Cancelled" and a date greater than or equal to March 1st, 2006. Here is what I currently have for the array formula: note: this is an Excel formula not VBA. I couldnt find the right code

=SUM(( 'Raw Data(Added_Cancelled)'!$D$2:$D114="Fulfillment")*('Raw Data(Added_Cancelled)'!$I$2:$I$150="Other")*('Raw Data(Added_Cancelled)'!$H$2:$H114>="2006-03-01 00:00:00"))

View 7 Replies View Related

Activating Multiple Array Formulas At Once...

May 14, 2009

Does anyone know how to activate a block of different array formulas at once??

Example:

N7:Q80 has a total of 296 Array Cells. Each has a unique formula & I cannot just drag to fill these nor can I activate all at once.

In the future, I don't want to have to manually activate them w/F2, CTRL+SHIFT+ENTER.

btw, Why do I have to press F2? Is that only in Excel 2007? I googled pretty extensively & don't see an option how to only press CTRL+SHIFT+ENTER. It would be nice not to have to press F2 everytime.

View 14 Replies View Related

Array Formulas And Accounting Consolidation?

Nov 16, 2013

I would like to master the dreaded array formulas. Any Excel based accounting consolidation tool or other consolidation tool out there that I could adapt to consolidate group accounts on a monthly basis.

View 5 Replies View Related

Will Array Formulas Work In =AVERAGE()

Dec 28, 2007

This formula is returning 0.2578 when it should return 2.473

{=AVERAGE(('Data-Design'!E2:E6000)*('Data-Design'!Q2:Q6000=1))}

What am I not understanding?

I want the average of column E when column Q=1

View 9 Replies View Related

Add Array Formulas To Mutiple Ranges

Oct 3, 2007

I would like to set up VBA codes to generate a variable number of transition matrices. I would like to know how I can define dynamic ranges instead of coding each and every range. In the codes below, I have to define each range one by one instead of using a loop.

Sub TransitionMatrix()
Dim P1 As Range
Set P1 = Cells(2, 2).Resize(3, 3)
Dim P2 As Range
Set P2 = P1.Offset(5, 0).Resize(3, 3)
P2.Cells(0, 1) = "P2"
For i = 1 To 3
For j = 1 To 3
P2.FormulaArray = "=MMULT(" & P1.Address & "," & P1.Address & ")"
P2.BorderAround Weight:=xlMedium
Next j
Next i
Dim P3 As Range
Set P3 = P2.Offset(5, 0).Resize(3, 3)........................

View 3 Replies View Related

Re-Enter Mutiple Array Formulas

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

How To Speed Up Large Matrix Array Formulas

Dec 31, 2013

I am trying to calculate a matrix of array formulas that is roughly 365 x 137, or about 50,000 cells being calculated from a range of roughly 12,000 x 137. There are multiple if statements within the array formula, and then those 50,000 cells are referenced to another, but smaller matrix of array formulas (25 x 137). Each of these matrix sets is for one year, and there are three years that need calculated.

Problem: When calculating the 50,000 cells it takes roughly 2.5 hours to complete all calculations. So, for 3 years it will take roughly one work day of tying up my computer to just compile data that will then require several days of calculation/manipulation.

Question(s): Is there a way to speed this up to a reasonable amount of time (I'd even take 30 minutes at this point)? Or, is Excel simply the wrong tool to be using for this amount of data? Do I need a better machine to run these calculations? I am currently using a Dell XT3 with 2.5 Ghz i5 quad core processor.

View 3 Replies View Related

Superfluous Zeros In Array Formulas? (2007)

Jun 13, 2009

I often see array formulas written with "extra zeroes", like this: {=SUM(IF(let=1,IF(cost>5,cost,0),0)). Yet I was taught to write the same formula without the zeros, like this: {=SUM(IF(let=1,IF(cost>5,cost))).

My question are:

1) what do the zeroes mean or do?

2) what happens if I replace one or both of the zeros with a different value, like the number one?

View 4 Replies View Related

Stop Cut And Paste From Wrecking My Array Formulas

Feb 24, 2010

I have a worksheet "Expenses" with columns Month, Category, Expense, Amount. There are four categories and, obviously, 12 months.

In the workbook is another worksheet, "Totals" which lists the months across the top, and the categories down the left. Then I have array formulas in each cell, that look like this:

=SUM(IF(Expenses!$B$2:$B$990="July",IF(Expenses!$C$2:$C$990="Acquisition",Expenses!$E$2:$E$990,0),0) )

This works fine, until people start moving cells around in the Expenses sheet. Say they sort everything they've put in by month, and then they want to move July up to the top because it's the start of our fiscal year. So say they have six expenses for July and they cut and insert them in at B2. Suddenly the formula now says:

=SUM(IF(Expenses!$B$8:$B$990="July",IF(Expenses!$C$8:$C$990="Acquisition",Expenses!$E$8:$E$990,0),0) )

I thought by using absolute cell references instead of relative ones, I could avoid this problem, but that's apparently not the case. I tried changing the cell references in the formulas to relative ones but it still happens then too.

View 2 Replies View Related

Array Formulas With Separate Date Columns

Jun 5, 2014

I have some imported data that lists the date in separate columns.

i.e.

ColumnA ColumnB ColumnC
January.......10.........2013

How do I convert this to a date by using a formula? Later I want to use the date in an array formula to sort the list.

View 8 Replies View Related

Ctrl+Shift+Enter (CSE) Array Formulas In VBA

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

Entering Multi-cell Array Formulas

May 17, 2007

I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1".

Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...

View 4 Replies View Related

Array Formulas Causing Slow Calculations

Dec 24, 2007

I have an Excel file that has a certain amount of Array Formulas. It takes 30 seconds to calculate every time the user makes a change. Some users do not have the Status Bar showing, and they think that Excel is "locked up". I want to make a user form that will appear when the Stats Bar is "Calculating..." There are a lot of "progress indicator" tutorials on this site, but they all seem to relate to VBA code being inserted in between certain lines. That doesn't apply. My Excel file has no VBA. It just takes a long time to recalculate. I want a VBA form to appear, that will match the % progress shown on the Status Bar, whenever a change is made to the spreadsheet.

View 6 Replies View Related

Array & Lookup Formulas Slow To Calculate

Jan 3, 2008

I am looking for ideas on how to speed-up one workbook. I have a spreadsheet with hundreds of lookup formulas and array formulas which are very slow to recalculate (takes around 5 min). Good thing about it is that these formulas are located in a specific range and that I need to run them only once a day (in the morning). Since I need to refresh the rest of the spreadsheet frequently, I am looking for an idea on how to isolate this resource heavy areas.

One solution I've been thinking about is moving these complex and slow formulas to VBA and run them only when needed. Can anybody assist me with the best and easiest way of doing this. Ideally I would want to leave option to the user to later edit this formula. Does anybody have any experience with weather it is best to migrate complete formulas to VBA or maybe leave formulas in the spreadsheet and run them based on predefined flag (that I can switch on or off from the code)?

View 5 Replies View Related

Sheets Array To Include All Sheets Between First And Last

Jun 2, 2008

how do i create a sheetsarray to include all sheets between First and Last? I plan to loop through each sheet in this array to copy data to a summary page, as per [url]

View 9 Replies View Related

Array Formulas For Geometric Standard Deviation And Sharpe

Oct 17, 2005

Since I got such fantastic help with the last quesiton let me try one that
I've been beating my head against the wall on for two weeks now.

I'm calculating CAGR (Compound Annual Growth Rate) of an investment using
the following formula:

=((GEOMEAN(IF(Work!$A9:$A236<B2,Work!$DS$9:$DS$236))^12-1)*100)

A Column: Dates
D Column: Monthly returns in multiplier format

I want to do the same thing as this with GSD using this basic formula --
that is, only do the returns based on the IF statement:

=(EXP(STDEV(LN(Work!B9:B236)))^SQRT(12)-1)*100

And Sharpe with this formula:

=(AVERAGE(B49:B276)/STDEV(B49:B276))*SQRT(12)

Any help you can offer will certainly save me a ton of pain -- head's
beginning to hurt!

View 6 Replies View Related

ABS Function And Named Array Formulas To Find Closest Value?

Feb 5, 2012

I am really struggling with the following formula:

MATCH(MIN(ABS(Ann_TaylorRegularBust-B4)),ABS(Ann_TaylorRegularBust-B4),0)

I am trying to find the cell in the named row "Ann_TaylorRegularBust" that is closest to the input bust size in cell B4. This formula works when I use actual range instead of the named array.

View 5 Replies View Related

Enter Array Formulas To Range Macro Code

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

Calculate The Cells In Sheet Containing A Large Number Of Array Formulas

Oct 14, 2008

I I have a excel document which has a sheet containing a large number of array formulas.

I turned the automatic calculation off.

However, even when I press F9 to calculate the cells it just crunches, gets stuck at “Calculating 0%” and then crashes.

The majority of the array formulas are IF queries with multiple criteria.

Would it speed up the calculation if I didn’t use array formulas?

View 2 Replies View Related

Database Functions Vs. Array Formulas Vs. SUMPRODUCT Vs. Pivot Tables

Dec 13, 2006

All I am doing is counting text values in a table.

The table has 3 main columns(which are relevant to this thread anyway).

Shift - Area - Status

The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.

I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.

The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas

The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)

The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)

In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g

Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned

I want my table to be as follows (as the SUMPRODUCT shows)

AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5

Shift Total

View 4 Replies View Related

Formulas To Other Sheets

Feb 28, 2007

I have a workbook with 4 sheets.

1st sheet is called "FORM"
2nd is called "BIDS"
3rd is called "RESULTS"
4th is called "BIDDERS"

Some of the data on the FORM moves to the BID and RESULT sheet. The cells on the BID sheet are moved to the RESULT sheet with a cell formulated to save the highest bid. On the BIDDERS sheet I have a list of Bidders and I use the Row numbers to match up with the column letters to place their bid in the cell. I need the formula on the Results page to give me the name of the highest bidder. Here is a sample of the workbook so far. you will see some data placed there for testing!

View 9 Replies View Related

Array Mode That Put It Back In The Letter, Number Display For Formulas/functions

Sep 25, 2009

My excel (2003) is stuck in array mode - is there any way to get it out of Array Mode and put it back in the letter:number display for formulas/functions? It's doing this in VBA too which is totally killing me.

View 2 Replies View Related

Pasting Formulas On Different Sheets

Dec 17, 2011

If I have a formula on Sheet2 which creates conclusions depending on data already existing on Sheet1 and then I need to paste the same formula on Sheet4 in order to get conclusions from Sheet3, then what can I do? Every time I am trying to paste the formula, it connects it with Sheet1 instead of Sheet3

In other words
Sheet1: Data
Sheet2: Extracted conclusions from Sheet1 data based on a formula
Sheet3: Data similarly organized as in Sheet1
Sheet4: How do I extract the same conclusions, but this time from Sheet3?

The formula on Sheet2 is:
=INDEX(Sheet1!$E$312:$E$5000;((COLUMNS($A4:B4)-1)*20)+1)

I would like to copy this formula from Sheet2, then paste it to Sheet4 and then to have it appearing as
=INDEX(Sheet3!$E$312:$E$5000;((COLUMNS($A4:B4)-1)*20)+1)

Is this possible?

View 7 Replies View Related

Formulas And Output On Separate Sheets `

Jul 4, 2007

What I want is one sheet that has all of the fomulas, then 4 other sheets that take those formulas and calculate the output based on the formulas and one column of input.

So essentially, I want to be able to change the formula once, and have it applied to all of the other sheets, resulting in a new output for each one.

View 4 Replies View Related

Data Validation Between Different Sheets Of Workbook With Formulas?

Jul 29, 2014

I am working on a resource management type workbook. In the first sheet,(Project Assignment) managers can enter staff, staff type and hours needed for the next three weeks. In a separate sheet,(Total Hrs per week) I have formulas set to total the hours entered from the first sheet for each resource - using the formula =SUMPRODUCT(--(staffassignments=$B4),--(Week1)) for each week, for each resource. That woks fine.

What I would like to do, if possible, is to present a type of data validation, dialog or popup when the resource's "total hrs per week" total = 40 hours. Different managers use the same resources, so one person may have time entered in multiple times for different projects in the Project Assignment sheet.

Is it possible to use the totaling formula (=SUMPRODUCT(--(staffassignments=$B4),--(Week1))) and an if statement in the data validation or conditional formatting to let managers know that the resource is fully utilized?

View 3 Replies View Related







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