cell range not changing when refreshing linked data- sumproduct fo

The cell range is not updating correctly to reflect the last row in the data 
sheet that is being refreshed.  5878 is the correct number of rows and 5824 
is not.  I can find and replace in my formulas to correct the problem, but it 
does it each time the data is refreshed.  Any suggestions as to why?

 
=(SUMPRODUCT((Expense!$B$2:$B$5878=$C$2)*(Expense!$C$2:$C$5878=$C$3)*(Expense!$W$2:$W$5824=$A6)*(Expense!$M$2:$M$5878)))
0
Utf
2/25/2010 7:35:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
815 Views

Similar Articles

[PageSpeed] 46

I gave you the INDIRECT option y'day, but received no feedback from you in 
that thread. Looks like you're more interested in knowing why? One simple 
hunch, because some people "refresh" data by actually deleting 
cells/rows/cols, when they should be clearing cells/rows/cols with the DELETE 
key. Deleting/cutting/moving actions will destroy/mess up any downstream 
formulas pointing to the affected ranges.
-- 
Max
Singapore
--- 
"lharp21" wrote:
> The cell range is not updating correctly to reflect the last row in the data 
> sheet that is being refreshed.  5878 is the correct number of rows and 5824 
> is not.  I can find and replace in my formulas to correct the problem, but it 
> does it each time the data is refreshed.  Any suggestions as to why?
> 
>  
> =(SUMPRODUCT((Expense!$B$2:$B$5878=$C$2)*(Expense!$C$2:$C$5878=$C$3)*(Expense!$W$2:$W$5824=$A6)*(Expense!$M$2:$M$5878)))
0
Utf
2/25/2010 10:56:01 PM
Reply:

Similar Artilces:

Printer set changes when accessing GP from Terminal services
Hi, We are using GP8. My colleague had issues when she tries to print invoices from a GP client installation using terminal services. I help her to change the printer to a physical printer. But it changed back to a different one such as image writer. How the printer setting works for terminal services in GP? Gavin ------=_NextPart_0001_3FF3F32D Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Gavin Are you using Named Printers? Have a look at the follow post for information on how to set up Named Printers. http://blogs.msdn.com/developingfordynamicsgp/archive/20...

Change date from mm-dd-yyyy to MMM-YY in Access
How do I change a field which contains a date in a mm-dd- yyyy format into mmm-yy format. For Example: Ship Month = 03/01/2004 Convert "Ship Month" value to display "Mar-04" Hi Vince, Set the Format property of the field, and/or the control(s) that are displaying it, to "mmm-yy" On Tue, 20 Apr 2004 12:01:52 -0700, "Vince" <anonymous@discussions.microsoft.com> wrote: >How do I change a field which contains a date in a mm-dd- >yyyy format into mmm-yy format. > For Example: Ship Month = 03/01/2004 > Convert "Ship Month&qu...

How to journal receipts from printer as uncompressed data?
The default option is compressed. But Dynazip seems not support unicode interface. Any ideas? ...

Need invisible/shaded/highlighted cell
I'm thinking what I would like to do can *probably* be done in VBA, but I'd like to use the formula in the cell. If it can be done. I have three columns: DOW DATE BILL DTE --- -------- -------- Wed 02/01/06 02/03/06 Thu 02/02/06 02/06/06 Fri 02/03/06 02/07/06 Sat 02/04/06 02/06/06 Sun 02/05/06 02/07/06 Mon 02/06/06 02/08/06 Tue 02/07/06 02/09/06 Wed 02/08/06 02/10/06 . . . . The date col contains a formula to add 1 to the cell above. The day of week col uses the date from the date column. And the bill date col adds 4 days to the date col if it falls on a "Thu...

Transparent bitmap in FlexGrid cell?
I have a bitmap in the resource (IDB_PICTURE). I need to display it on a FlexGrid's cell with transparent background. Could someone show me how? This is the only function available to put a picture on a cell CFlexGrid::putref_CellPicture(LPDISPATCH) Thank you. ...

linking cells #3
I have a calendar created in Excel - each sheet is a new month that contains the following information: the last week of the previous month, the current month, and the first week of the next month. I would like to link the cells from the "overlapping" weeks, but I do not want the "0" to show up in the cells. Is there any way to prevent this? Thanks in advance! Paula Either choose not to show zero values through Tools>Options>View or trap the zero and turn to a blank-looking character. =IF(sheet1!A1="","",sheet1!A1) Gord Dibben Excel M...

Changing the language in OWA
One of our users reports that when he connects to the internet and accesses his email using Outlook Web Access, he gets the French version of OWA and not the english. (The menus and commands are all in French). How can we change this to English? It's controlled by the default language configured in Internet Explorer; Control Panel-> Internet Options-> tab General-> button Languages... -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tip of the month: -Create your own fully customized ...

a little help with finding and associating cells
ok so here is my dilemma, I need a formula that will look at a cell value on one work sheet, check for the same value on another work sheet in a defined column, and insert a coresponding value from another column...... is that even remotely possible??? any help would be greatly appreciated.... -- rcarrollct ------------------------------------------------------------------------ rcarrollct's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24931 View this thread: http://www.excelforum.com/showthread.php?threadid=384671 It's called VLOOKUP. http://www.offi...

"Diagonal" cells
Hi. I'm doing a tricky poor-man's-Illustrator project with Excel 2007. I'm trying to create the impression of a large cell that's been split in half diagonally (with a diagonal ascending from bottom left to top right). I've created a 2 x 2 grid of cells and put a diagonal border through the lower left and top right cells and removed the vertical, internal borders. Looks great so far. In addition, I'd like to place text in the upper left and lower right cells. Ideally, this text would be wider than the upper left or lower right cell and would flow over into the adja...

insert symbols in text cell
i would to insert "-" to the text cell format, how to do? example: 123456 change to 123-456 -- SelinaT ------------------------------------------------------------------------ SelinaT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33675 View this thread: http://www.excelforum.com/showthread.php?threadid=534451 Assuming the data posted is representative (6 digits, with dash to be inserted in centre) and running in A1 down Try in B1: =LEFT(A1,3)&"-"&RIGHT(A1,3) Copy down Then copy col B, and overwrite col A with Paste special &g...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

minimum cell value
Hi, I am trying to set up a cell so it has a minimum value. The current formula is: =a1/a2*5 which gives the answer 0 but i need to show a minimum value of 1 is this possible? Hi, Doug, Try: =Max(a1/a2*5,1) --- Regards, Norman "Doug Bell" <Doug Bell@discussions.microsoft.com> wrote in message news:8766BC4D-D3CD-4B4F-AF7B-FD145DE56205@microsoft.com... > Hi, > > I am trying to set up a cell so it has a minimum value. > > The current formula is: =a1/a2*5 which gives the answer 0 but i need to > show > a minimum value of 1 is this possible? > ...

Changing Domain Name
We have mix W2k & W2k3 DCs and one E2k2 in a W2k3 member server. We now xxx.yyy.zzz.ca.us as our domain name. Now we have approval to change it to xxx.yyyyyyyy.gov format. What is the most efficient way to do it? We need to retain the old domain name with new domain name for a period of time to give our users enough time top inform their friends, other senders,etc. Thank you. -- JoeCL LACO-CAO In news:B8BD42A2-428A-4156-BE94-4CCAE8FF1E8E@microsoft.com, JoeCL <JoeCL@discussions.microsoft.com> typed: > We have mix W2k & W2k3 DCs and one E2k2 in a W2k3 member server. ...

How to change the format?
I have some data from a SQL database I have put into an excel spreadsheet, this has produced four columns of data. In the coloumn B is a reference number and in coloumn C is the name the field and in coloumn D is the data. Now there can be upto 12 rows of information for the same ref. number with different field names and their relevant data. I need to do is to reformat the page so that the so that the field names in coloumn C go across the page, but I don't have a clue on how to do this, any suggestions? Tim Hi Tim, Ito sounds like you want to want to transpose your data. For e...

format to calculation cell
I have a spreadsheet which has a weight column. This is exported as 25kg, 1000kg, 10x2kg from another program. I am trying to use this weight column in a calculation. I have inserted a new column and copied the information over and then used the find/replace to remove the KG to use in the calc cell. This works fine for the 25 / 1000 etc, but the 10x2 will not caculate. Is there a way that when i paste the cells that i can get it to change to 20 automatically. ( i presume that the x will need to be a * ?) thanks. -- mdma --------------------------------------------------------------...

I can not see all contents in one cell. Help me pls!
Please help me! By default when I write some text in cell and the text is longer then cell's width, I can not see his contents in next cells. But in one excel file when I sellect all cells in worksheet and then draging them it can no longer seen all contents in one cell. What I should to do? Thank you in advance! If you have wraptext turned on (format|cells|alignment tab), then the cell will expand its rowheight to show you all the data. (unless you use merged cells or have adjusted the rowheight manually). If you have wraptext turned off, then the text will overflow to the cell to ...

iPad changes or challenges society.
How does the ipad changes or challenges organizing in a governmental, collective, money-saving way? In appendix, how does the ipad rat to the 3 disciplines of Unparalleled, Sociology, and Anthropology? Thanks. -- Ronentike "Ronentike" <Ronentike.60a91a0@officefrustration.com> wrote in message news:Ronentike.60a91a0@officefrustration.com... > > How does the ipad changes or challenges organizing in a governmental, > collective, money-saving way? > > In appendix, how does the ipad rat to the 3 disciplines of Unparalleled, > Sociol...

Entourage future only Recurring Event change
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: imap Is it possible to alter only the future occurrences of a recurring event? <br> Let's say a recurring event is going to have a different name or meeting place from the next session forward. Is it possible to edit only future events? I don't want to change the past editions of this event. I don't want to delete the past sessions, either. It seems to be an all or nothing proposition for recurring events, which is not desirable. I'm pretty sure Outlook had this option....

Formatting cell for state abbreviations
I am unable to format cell to accept ME, the abbreviation for Maine. It continues to revert to a lowercase "E" as in "Me" while other states such as NH, VA and LA maintain their uppercase second letter. What am I doing wrong? Thank you -- LovesArt247 This is a AutoCorrect item for people who mistype the word "Me" (as in me myself) Tools | Autocorrect; locate and remove this entry -- remember it will effect all Office apps OR: after Excel gives you Me, use CTRL+Z to undo best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme rem...

Excel 2003, when I click on a particular cell it gets deselected
While using Excel 2003, when I click on a particular cell, within 30 seconds, that particular cell gets deselected followed by the workbook. So each time I need to click either the cell or workbook to enter data in that particular sheet. There could be event code that is being fired. Does this happen if you open the workbook with macros disabled? HTH, Bernie MS Excel MVP "towinwin" <towinwin@discussions.microsoft.com> wrote in message news:B7B87AFB-27AC-4235-9AE3-5E7A46310AF8@microsoft.com... > While using Excel 2003, when I click on a particular cell, within 30 se...

Mistake of changing decimal into whole numbers
Earlier in the year we rec'd a spreadsheet for prices. The format was 2.30, 1.05 etc. to make it simpler, someone change it to 230, 105 and the decimals was assumed after the 1st digit. Now the customer wants it back to 2.30 vs 230 but the format has been permanently changed. Is there any hope here of changing it back? Try this: Put 0.01 in a blank cell Copy that cell Select the range of number you want to move the decimal point on. Edit>Paste Special Check: Multiply and Values Click [OK] (If that doesn't do what you want....Edit>Undo) Does that help? *********** Reg...

how do I highlite text within a cell (specific characters)
I am trying to high light specific characters within a cell. (similiar to the way text can be high lighted in word. Can this be done in Excell. I dont want to change the colour of the cell or the colour of the text - but do want to high light specific parts of the text with in a cell Just select that part of the text and format as per your requirement by going to cells > format > font. Mangesh "tim" <tim@discussions.microsoft.com> wrote in message news:A9781298-1301-4FE1-9901-9D1C03504504@microsoft.com... > I am trying to high light specific characters within a ...

Downloading Statements / Changing Target Account
I previously had two accounts within my bank (Checking and Savings). My wife and I created a joint account and closed my savings account. Money was previously set up to download statements to both of my accounts. I have changed the status of the Savings account to closed and was hoping that when I was to download the file from the bank it would see the account closed and ask for a new account thus creating a new link. This does not happen it still enters the info into the old account. Is there anyway of linking to the new joint account? or do I have to delete the old savings account...

Changing font from lowercase to uppercase easily
In Word I know that we can change the font of an entire document from lowercase to uppercase without having to re-type the document...is that possible in Excel? You can use a formula and a help column =UPPER(A1) or for multiple changes use VBA http://www.mvps.org/dmcritchie/excel/proper.htm scroll down halfway until you see the UPPER macro Regards, Peo Sjoblom "formhelp" wrote: > In Word I know that we can change the font of an entire document from > lowercase to uppercase without having to re-type the document...is that > possible in Excel? Create a macro na...

Cell colors
In Format Cells (Excel 2008), there are 40 colors in the top five rows and 16 more colors in the bottom two rows. I see how to change the colors in the top rows (Preferences > Colors), but how do you change the ones in the bottom rows? Charles ...