non-functioning multiple Array formulas in a workbook

I have a workbook with used for forecasting. I use a template sheet for each
forecast (all forecast sheets -54- are the same and contain sheet-level
names).

On each sheet I have a formula that counts the number of chargeable
engagements (where the chargeable engagement is indicated by a "C" in the
preceding column) and places the results in a cell with concatenated text.
The 'chargeable designation' column is a named range.

I can get the formula to work on only two of the worksheets. It can be any
two sheets, but it will only work on two sheets maximum. I have tried a
number of work arounds with no success. I sure this is a straight-forward
problem, but I'm unclear where to start. I have a feeling the named range is
creating the problem, but referencing the range itself does not work either.

example: (this is an excel spreadsheet, clearly, I'm not an designer)

    |  A                 | B                    |C                    D
E
-- |-------------- |--------------- |-----------------------------------
1  |  Chg Status    | Engagement    |
2  |  "C"               | eBay               |
3  | "C"                | Yahoo            |
4  | "C"                | Google            |
5  |  "A"               |  Admin time     |
6  |  "H"               |  Holiday time   |
7  |                      |                        |

(is array formula entered with Shift+Control+Enter)
{=CONCATENATE("Chargeable Client Count = ", COUNTIF(Chg.Type,"C"))}

Where the range in column A is named Chg.Type

I've tried changing the named range (chg.Type) to the direct reference
(A2:A6), but all I get in either case is the formula text displayed in the
cell

{=CONCATENATE("Chargeable Client Count = ", COUNTIF(A2:A7,"C"))}

I have a number of other COUNTIF functions in the worksheet that work fine.
It's the array formulas that are not functioning correctly. The workbook is
currently about 7megs in size.

Any assistance would be greatly appreciated.

thom
hunter1sf@yahoo.com



0
thom1904 (1)
12/5/2003 9:06:25 PM
excel 39879 articles. 2 followers. Follow

1 Replies
891 Views

Similar Articles

[PageSpeed] 52

First, I don't see a need to use ctrl-shift-enter on this formula.

Second, try formatting the cell as General and then hitting F2 and then Enter.

(It sounds like the cell was formatted as text before you typed in your
formula.)

Thom wrote:
> 
> I have a workbook with used for forecasting. I use a template sheet for each
> forecast (all forecast sheets -54- are the same and contain sheet-level
> names).
> 
> On each sheet I have a formula that counts the number of chargeable
> engagements (where the chargeable engagement is indicated by a "C" in the
> preceding column) and places the results in a cell with concatenated text.
> The 'chargeable designation' column is a named range.
> 
> I can get the formula to work on only two of the worksheets. It can be any
> two sheets, but it will only work on two sheets maximum. I have tried a
> number of work arounds with no success. I sure this is a straight-forward
> problem, but I'm unclear where to start. I have a feeling the named range is
> creating the problem, but referencing the range itself does not work either.
> 
> example: (this is an excel spreadsheet, clearly, I'm not an designer)
> 
>     |  A                 | B                    |C                    D
> E
> -- |-------------- |--------------- |-----------------------------------
> 1  |  Chg Status    | Engagement    |
> 2  |  "C"               | eBay               |
> 3  | "C"                | Yahoo            |
> 4  | "C"                | Google            |
> 5  |  "A"               |  Admin time     |
> 6  |  "H"               |  Holiday time   |
> 7  |                      |                        |
> 
> (is array formula entered with Shift+Control+Enter)
> {=CONCATENATE("Chargeable Client Count = ", COUNTIF(Chg.Type,"C"))}
> 
> Where the range in column A is named Chg.Type
> 
> I've tried changing the named range (chg.Type) to the direct reference
> (A2:A6), but all I get in either case is the formula text displayed in the
> cell
> 
> {=CONCATENATE("Chargeable Client Count = ", COUNTIF(A2:A7,"C"))}
> 
> I have a number of other COUNTIF functions in the worksheet that work fine.
> It's the array formulas that are not functioning correctly. The workbook is
> currently about 7megs in size.
> 
> Any assistance would be greatly appreciated.
> 
> thom
> hunter1sf@yahoo.com

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/6/2003 12:47:21 AM
Reply:

Similar Artilces:

copy a row of data to a column in another workbook?
I am trying to get the data i enter into my charity subscription workbook, to be automatically updated in my gift aid register (workbook) so that i do not have to input twice. The data is entered into the subs register in rows and the gift aid register is entered in columns. Any ideas would be appreciated. hi, =('H:\CodeStuff\[ConnectPaths.xls]Sheet1'!D4) a formula like this in your gift aid register wb will draw the data over from your charity subscription wb. I don't know your file paths so you will have to change it to fit your set up. in the gift aid register wb, put the ...

Creating a field to search and enter non duplicate data
Hello all. I have a team of 15 pople that all work with reference numbers, on occasion a number has to be sent to a different work group. I've created a spreadsheet for them to enter the numbers into, but we have been duplicating numbers. I want to create a field to enter th number into, have the field cross reference the exsting numbers, and if it does not find a match, enter the number and sort the list. I know how to create a custom macro that will accomplish what I want to do, but I'd prefer a static formula if it's possible. A formula can not do waht y...

Create Multiple Hyperlinks in Excel
I am trying to link two worksheets using hyperlinks. The first worksheet ( Worksheet1) has a field CompanyCode and contains sales information . The second worksheet (Worksheet2) has the same field CompanyCode and all the company address details. The two worksheets are in exactly the same order. I would like to create a hyperlink in Worksheet1 linking the CompanyCode to Worksheet2 and then copy all the hyperlinks by dragging . Is this possible, or is there another simple way of doing this. (It has to be simple - I am not too good on macros or VB!) Many thanks ...

workbook
i have twelve worsheets running on for each month of the year. how can i extract two or three colums from each one, and put them in a chart. thanks Monty, One option would be to create a 13th "master" worksheet to contain the data for your chart. You can create cell references to all 12 sheets from the master sheet. In other words, the master sheet contains a summary range that is made up of simple formulas that refer to the data in the other 12 sheets. Build the chart based on that summary range. ---- Regards, John Mansfield http://www.pdbook.com "Monty" wro...

Using the DLookup function
I have a table that includes a field StartDate. Another table (HOLIDAY) holds dates announced as Holidays. I am trying to use the Lookup function to see if my StartDate matches a HolidayDate. I have tried If Forms!frmAssignment!StartDate = DLookup("HolDate", "HOLIDAY", "HolDate = " & Me.StartDate) Then......... Unfortunately, it doesn't run!! (The datatypes throughout are ShortDate). I've tried the same using the # sign as part of the last section, but still no joy. Would appreciate any help. Thank you. Andrew The correct syntax shoul...

issue with Workday Function
From: amran.majid@gmail.com Newsgroups: microsoft.public.excel Subject: issue with Workday Function - not calculating workdays correctly! Date: Thu, 05 Oct 2006 12:23:03 -0000 hi, trying to use the workday function to calculate the number of workdays from a specific date. i am trying to calculate the 11th working day for each month. it works for October, but not november and december 2006. A1 = 01/10/2006 B1 = 02/11/2006 C1 = 03/12/2006 A2 = WORKDAY(A1,11,Holidays) returns a value of 16/10/2006 which is correct. B2 = WORKDAY(A1,11,Holidays) returns a value of 16/11/2006 which is incorrec...

how to get same sent items on multiple pc's (exchange)
Hi there! I run Outlook 2003 and use two different PCs (home and work) to access my mail which are both set up as microsoft exchange. It is a webmail account at my university. I love how it works, except that I have this one problem: When I send a mail from home, it does not appear in the sent items at work and vice versa. I have been trying to figure it out for a while now, but to no avail. I think I m missing something simple... Thanks for reading my post. Any help would be much appreciated! Pieka You have not set the default delivery location your Exchange account then ...

non
...

Query a non-MS/non-Active Directory LDAP Server
Hi All, Does anyone know how to query an LDAP server from Access VBA that is not a Microsoft / Active Directory (AD) server? I have found a lot of stuff about how to query AD and have succeeded in doing so. But that does not seem to transfer to the non-MS LDAP server. It gives the error "Automation error" "There is no such object on the server." Thanks, Clifford Bass -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201004/1 Clifford Is there a chance that there...

Excel Function not Evaluating to a Result
Hi, A colleague of mine has a spreadsheet containing a number of formulas which reference cells in another sheet within the same workbook, so the cells contain for example ='Reference Data'!A3 He populates the sheet using VBA code inserting the functions into the appropriate cells, and on initial creation this works fine and all the cells show the correct result. i.e. If the Reference Data sheet at cell A3 contains "Fred Bloggs" then "Fred Bloggs" is displayed in the cell. However, if he edits the cell function to change say the !A3 to !A4, th...

Formula For Multiple Rows
In Excel 2009 I need to multiply rows 1 through 55 by $0.35. I don't need any amounts of the rows multiplied by that, just the number of rows (55 x $.035). How can I add that to a cell with a formula in it to add certain cells of these rows for a total? Thanks. ?? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "satrntgr" <satrntgr@discussions.microsoft.com> wrote in message news:9638900A-4E2C-4D52-AC7A-AD9BA24CCA14@microsoft.com... > In Excel 2009 I need to multiply rows 1 through 55 by $0.35. I don't need >...

Copying a formula while leaving one cell fixed
Hi there, I forgot how to leave one cell fixed while copying a formula down a column. For example, "=RC[-3]*R[-45]C[-5]" should read "=RC[-3]*R[-46]C[-5]" I recall using a Dollar sign ("$") to hardwire a cell in a formula. My formula should then read "=RC[-3]*$R9$C3" However, this doesn't seem to work. Thank you for your help. You can use the $ to fix the row or column -- but only if you're using A1 reference style. If you're using R1C1 reference style, then this: =RC3*R[-45]C5 would take the value in colum...

Basic IF function help
i am new to excel and trying to use the IF function. i am trying to figure out how to get excel to only display the result of the formula is a number. in some cases, the formula has "0" in numerator so i get "Div/0" answer. in that case, i want the result to display just a blank. any help will be appreciated. thanks. my current formula is =IF((4*R3/G2)xxxxxx,(4*R3/G2),("N/A")) One way: =IF(G2=0,"",IF(4*R3/G2)xxxxx,4*R3/G2,"N/A")) In article <1168958132.311542.122240@m58g2000cwm.googlegroups.com>, "gtd068a" <IKhan11@...

Non Delivery Strangeness
I rebooted Exchange 2003 SP2 and received the following NonDelivery email on 1/3/2006 for an email sent on12/15/2005. I and other users received a few of these and the server seems to perform normally for the 100's of other emails that are sent and received daily. Thanks, Dan Foxley ---------------------- Your message did not reach some or all of the intended recipients. Subject: Purchasing From Your Site Sent: 12/19/2005 2:05 PM The following recipient(s) could not be reached: smartservice@smartwool.com on 1/3/2006 10:17 AM The e-mail system was unable to deliver the message, but d...

Can I set up a formula to lookup a value for a condition
I am trying to set up a formula for a vendor list. Each vendor has its own individual number in column C.Some of vendors are grouped together. If vendor is not grouped Column D has same number as of C. If Vendor is grouped, first vendor number in Column C is assigned to all the group members. I need to set up a formula so that col. E will look for vendor number in col. D and add data for one group. Please help. Below is an example of my data list Vendor name vendor number grouping number Purchases Sales Profit abcd 251 251 $$$ ...

using percentile in an array formula
Hi, I am trying to find the value of AF at the 25% percentile of AW in the following array formula. =IF(PERCENTILE(Exit_Date!$AW$184:$AW$228,0.25),Exit_Date!$AF$184:$AF$228) I am getting a result but not the answer I am expecting. Any ideas? Bruce You might try =INDEX(AF$184:$AF$228,MATCH(PERCENTILE($AW$184:$AW$228,0.25),AW$184:$AW$228)) if your data values in AW are in ascending order, but in that situation you could also use =INDEX(AF184:AF228,1+0.25*ROWS(AW184:AW228)) -- David Biddulph "Bruce" <Bruce@discussions.microsoft.com> wrote in message ...

Run Query using Multiple Parameters
Hi - thanks in advance for help. I have this query that will calcuate my positions on any day that enter. SELECT StockTransactions.DealCode, StockTransactions.Stock, Sum(StockTransactions.Shares) AS SumOfShares FROM StockTransactions WHERE StockTransactions.TradeDate<[As Of] GROUP BY StockTransactions.DealCode, StockTransactions.Stock HAVING Sum(StockTransactions.Shares)>0; However, I'd like to have the query run for every day between two dates that I enter. So, if I input start date as Jan 1 and end date as Jan 15 it will run the above query repeatdly using Jan 1 as [As Of], Ja...

Excel Basic function #2
Domenic Thanks, works now Cheer -- Lesoth ----------------------------------------------------------------------- Lesotho's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1582 View this thread: http://www.excelforum.com/showthread.php?threadid=27315 ...

Method of calling a function where function name is stored in a variable or control
Is there a way to call a function (or sub for that matter) where the function/sub name is stored in a variable? For instance lets say I have a table that stores some function names. On a form I have a list box that displays the records in that table. For this example lets say they all accomplish similar things and all require the same numbef of arguments and none of them return a value. If Call were the right method to accomplis this it might look sometihng like this Dim lngArgument as Long lngArgument = 1234 Call Me.lstBox, lngArgument In this case Call would run the function...

Re-post:Formula Finesse Needed: COUNTIF or DCOUNT or Something bet
Hello, I might have posted my original question in the wrong area initially. The answer i recieved from the General Excel forum was way beyond my ken. Please see the post below and the answer I recieved. If someone could explain how the answer works or send an alternative answer that would be great. "Sean Timmons" wrote: > OK, make sure your months are actual numbers.. Jan would be, ay, 1/1 > formatted as mmm. > > in A2, > =SUMPRODUCT(--(ISERROR(SEARCH($A2,Sheet2!$A$2:$A$500))=FALSE),--(month(Sheet2!$B$2:$B$500)=month(B$1))) > > should get it > &g...

NON TAXABLE Item Setup, What TAX CODE to assign
What is the proper way to set up a non taxable item like a coupon or labor? I was assigning the <not assigned> (RMS dbase Tax ID 0) (But I just read said to delete the <not assigned> tax code. to prevent a runtime error (we have not had yet)) Do I need to setup a Non tax tax code? and add all non tax items to it? PS I have a < > (blank)(RMS Dbase tax id 3) tax code that seems to need to be deleted, RMS assigned it tax code 3. There are currently no items assigned to the < > blank tax id I can not find any guidance in the RMS Help. It seems MS ex...

NDR from non-existent email address
Any user that sends a meeting request to the user in question gets the following responce. Your message did not reach some or all of the intended recipients. I changed the info to the innocent. Subject: test Sent: 8/16/2005 9:01 AM The following recipient(s) could not be reached: Gay, Esse M. on 8/16/2005 9:02 AM The recipient name is not recognized The MTS-ID of the original message is: c=US;a= ;p=XXX;l=XXXXXXX-XXXXXXX MSEXCH:MSExchangeMTA:EXCHANGE:XXXXXX This only happens with meeting invites, regular email goes through just fine. It is jus...

AVERAGE function returns #DIV/0! error
I used AVERAGE function in my spreasheet and received #DIV/0! error from it. Is there a safe proof way to omit the invalid values out of the average calculation. Try this. I assumed your range was A1:A10, change this to fit your data. Enter this formula by using CTRL+SHIFT+ENTER as it is an array formula: =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10,"")) -- Regards, Dave "KhaVu" wrote: > I used AVERAGE function in my spreasheet and received #DIV/0! error from it. > Is there a safe proof way to omit the invalid values out of the average > calculation. =IF(COU...

Non-cash, Non-regular Transactions
Money 2003 SE V11 I have a need to set up transactions, such as Mileage, to keep track of non-cash activities. I currently handle it by setting up Bills & Deposits split into two transactions totalling $0.00. The first is the Category/Subcategory I want to track and the second is a Category/(Non-Cash Offset Subcategory) that I filter out of all reports. This works ok for me. Example: (meaning 23 miles traveled) Mileage:Medical/Dental 23.00 Mileage:Offset Non-cash Transaction -23.00 However, the transaction is not regularly occurring. Yet we are required to ...

Using multiple Back-end servers
I've installed Front-end and back end exchange 2003 servers. Now we have to host another domain and old back-end it's full. I've so installed another beck-end server where hosting new mailboxes. Which I've to set to Master? Where put public folders and have to set replica? How set RSUS policy for new domain and new mailboxes in another back-end server? Thanks The additional backend should make no difference. The Routing Master and RUS can stay the same. As for Public Folders you could point the new server to the old server or create a replicas. Probably the best approac...