I use a pivottable to organise data in a list and then use th getpivotdata formula to pull the data into a customised report. I have run into a problem now where I try to copy the file and create new one for the present week. I change the source data however th getpivotdata formulas all now show ref! error. I've checked the formul and see nothing wrong. Can anyone help me? please copy your response t daveaswat@hotmail.co -- Maxxi ----------------------------------------------------------------------- Maxxin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=...

I attempt to copy a formula by scrolling down. The formula appears to copy correctly--increasing by one for each row. However, the value that appears in the cell remains the same--somehow ignoring the formula that shows up when you place the cursor in the cell. How do you get the actual formula to copy correctly? Has anyone else had this problem??? Thanks very much, Steve Make sure calculation is set to automatic: Tools>Options>Calculation tab>Automatic>OK -- Biff Microsoft Excel MVP "skluck" <skluck@discussions.microsoft.com> wrote in message news:...

I've got data from two different spreadsheets, but need the product cos from Data Range A to put next to the sales price of Data Range B. Data Range A Model Number Product Cost A 0.10 B 0.20 C 0.30 Data Range B Model Number Size Sales Price A 8x10 $1.00 B 5x7 $2.00 C 4x6 $3.00 What formula do I use if I want to get the SALES PRICE from Range B an put it right next to the Product Cost -- Message posted from http://www.ExcelForum.com Hi Try using the following using your proper filenam...

Hello everyone, Basic question: Is there a set quideline on when to use quotes in formulas and array formulas? -- Thank you... Elaine Hi! Can you be more specific? You must use quotes when referencing text values: =IF(A1="Red","yes it is","no it isn't") =SUMIF(A1:A10,"Ten",B1:B10) Do not use quotes when referencing numbers: =IF(A1=10,10,0) =SUMIF(A1:A10,10,B1:B10) Biff "Elaine" <Elaine@discussions.microsoft.com> wrote in message news:5879DD53-B927-401E-8304-3D158C51B232@microsoft.com... > Hello everyone, > > Ba...

Formula is merely an if if(B2 > 0,1,0) could also probably use countif nb: I can't use a summation here as some of my macro's use differen filters in combination with subtotal and sums alread -- shaw ----------------------------------------------------------------------- shawb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1420 View this thread: http://www.excelforum.com/showthread.php?threadid=26268 Hi if this formula is ion column C (cell C2) try: =IF(OFFSET(C2,0,-1)>0,1,0) or --(OFFSET(C2,0,-1)>0) But I'm quite sure you could use a ...

Hi all, I have price data in rows like this (each price takes up a cell): 5 5.43 6 6.25 6.50 8 15 9.25 9 8.75 8.50 8.50 8.50 8.50 13 I need to show trends via conditional formatting where on 3 or more consecutive days prices fluctuate EQUALLY by 0.25 or more each time, up or down... IE, with the above data, I would want to "flag" let's say, in red fill color, the sequence 6, 6.25, 6.50 as well as the group 9.25, 9, 8.75, 8.50, but NOT the group of 8.50, 8.50, 8.50. I have tried if/and/or statements again and again and come close on simpler tre...

I need some help: In this example I have a column of 5 cells B1:B5. I also have another cell C1. I would like to have cell C1 to display the most recent entry in the B Column. Start B1=Null C1=Null B2=Null B3=Null B4=Null B5=Null First Entry B1=4 C1=4 B2=Null B3=Null B4=Null B5=Null Second Entry B1=4 C1=7 B2=7 B3=Null B4=Null B5=Null Third Entry B1=4 C1=3 B2=7 B3=3 B4=Null B5=Null Fourth Entry B1=4 C1=0 B2=7 B3=3 B4=0 B5=Null Fifth Entry B1=4 A1=9 B2=7 B3=3 B4=0 B5=9 Is there a fo...

is it possible to add more items to the QUick Create menu? ...

For some reason, the when I insert a trend line into a graph, the formula comes up with one part of it in scientific format (y = 2750.2x2 - 64597x + 2E+06). I konw the last expression in the formula is $2,000,000 but this is rounded- and I need to know what the exact amount is. I have tried extending the formula box, but that doesn't work. Can anyone help me? Thanks in advance. See www.stfx.ca/people/bliengme/ExcelTips on the use of LINEST to get the values into cells - then you can have what ever format you need and there is no need to copy the values. -- Bernard Liengme www....

I am new to using option groups. I will have several of them that will be a value of 1-5 and want to total them up in a report. Do I need a field for each option group? Are there field names that can be used to make it easier to total, ie grp1, grp2, etc.? Thanks, Les You've told us "how", but not "what". "How" depends on "what"! If you have data with values of 1-5, and "several of them", is there a chance you are working with something like a rating scale, or a survey? If so, you may benefit (*and get more...

OFFSET(C6,COUNT(C7:C33),0) I am using this formula as part of a spreadsheet I am using for a social golf club, it has 20 sheets, and this formula keeps track of how many games an individual plays. It works well except for the fact if a player misses a game the result (in C35 shows 0, as per the formula) eg: c7 1 c8 2 c9 3 c10 4 and the result in c35 is 4 ( as I want) but if c7 1 c8 2 c9 (no input,blank) c10 3 the result in c35 is 0 not 3 So what I need is to change the ,0) bit but I am not sure what with or how to replace it so that it ignores a blank cell ......... Thanks for any help Ke...

I just switched this past weekend. From the universe of the bizarre: -Money takes over 1 minute to load. -It frequently said that it had updates to install, but never installed anything. I finally determined that when Boinc/Setiathome was running, M05 would get little CPU time. As soon as I disabled Boinc, Money loads instantly and the update download occurred as soon as the notice displayed. -Converting transactions from Quicken went fairly well except for transaction that had categories in common with money and those same categories had subcategories in Quicken. In this case no...

I need to do a formula as follows. If the amount in column A is < $1500 calculate it by .034. If the amount is > $1500 then they get .00 of only the amount over %1500. Know how to do IF statements but can't seem to figure out how to calculate only on the amount under a certain value and not on the amount over that value. So results should be $1500*.034 = $51.00 00-1700 *.00 = $00.00 Total Commission $51.00 Help!!!! Thanks if you can do this.:confused: :rolleyes: -- Sharon D. ------------------------------------------------------------------------ ...

IF I HAVE THE OPTION TO PUT ANY OF THE FOLLOWING NAME IN CELL A1 TRACTOR ( REFERS TO $80) BOBCAT( REFERS TO $50) LABOR (REFERS TO $28) AND I HAVE THE FORMULA =SUM(A1*B2) IN CELL C1 HOW CAN I REWRITE THIS FROMULA TO DISTINGUISH BETWEEN TRACTOR, BOBCAT AND LABOR? PLEASE HELP ME =((A1="TRACTOR")*80+(A1="BOBCAT")*50+(A1="LABOR")*28)*B1 -- HTH Bob Phillips "ELISA@RELIABLECONTRACTORS.BIZ" <ELISARELIABLECONTRACTORSBIZ@discussions.microsoft.com> wrote in message news:1185FF6C-3C98-4AA8-907E-AA3001C207E0@microsoft.com... > IF I HAVE THE OP...

A B 1 112233 Expired 2 113344 Valid 3 116655 Valid 4 117799 N/A This Formula is to calculate the number of employee in the column. =COUNTIF(A1:A4,"*"). Can I make it =COUNTIF(A1:A4,"*")- N/A. I mean I want it the formula to calculat the number of employee but if the Column B has the work N/A. include it in the calculation. Try =COUNTA(A1:A10)-COUNTIF(B1:B10,"N/A") 'If you want to count only if there is a numeric value in colA then you may try =SUMPRODUCT((ISNUMBER(A1:A100))*(B1:B100<>"N/A")) -- Jacob (MVP - Ex...

I am linking between two sheets and the link formula is showing up instead the answer. It could be a few things... #1. The cell is formatted as text. Format|cells|General (or some number variation--just not Text) then hit F2 followed by enter to re-enter the formula #2. You're looking at formulas Tools|options|View tab\ Make sure Formulas isn't checked #3. Maybe a mistake in your formula. Make sure that there is no space before the initial = (equal sign) BalancedSolutions wrote: > > I am linking between two sheets and the link formula is showi...

Hi, I'm trying to figure out how to add one more layer of complexity to my formula, but it's not working. Any assistance would be appreciated. My spreadsheet has several expense sections, each with a "subtotal". I'm trying to lookup the subtotal lines and sum for the column in which this formula lies. Here's the simplified version: =SUMIF (Range1, "Subtotal", Range2) This formula works great, but people keep typing the word "Subtotal" differently. So I'd like to replace that string with: IF(NOT(ISERROR(FIND(&...

I need to determine a value that is predicated upon the selections of about 6 or so drop down menus. It'd be General Text within drop-downs but final value is an Accountant price Basically if someone selects Option #1 out of Cells A-G's drop down menus, then the value of H1 = a cell on another page that fits the category of 1- 1-1-1-1-1 from the drop down menus. I don't know how to do this. I imagine it's possible but have no clue. Your help is greatly appreciated. From what you say, the meat of this problem lies in the layout/format of the data you have in this "...

If I have a value of $10 and add 10% by dividing by 0.90, I get $11.11 which I would like to round up to the nearest $0.5, i.e. $11.15. Does anybody know the formula and can you use it on a result which is also a formula? Thanks, Lee "Lee" wrote: > If I have a value of $10 and add 10% by dividing by 0.90, > I get $11.11 which I would like to round up to the > nearest $0.5, i.e. $11.15. From your example, I presume you mean "nearest $0.05" (nickel). > Does anybody know the formula and can you use it on a > result which is also a formula? If you truly ...

Hi, all, I'll post this in the SQL Server area as well. I'm creating a new VM with 2008R2, SharePoint 2010, and Office 2010 beta/Gemini. Is there a specific order I should install these in? Many thanks.... 1)OS 2)Office 3)SQL Server 4)Sharepoint Thanks very much!!! "Kamlesh" <user@msgroups.net/> wrote in message news:O1fuLkblKHA.3128@TK2MSFTNGP02.phx.gbl... > 1)OS 2)Office 3)SQL Server 4)Sharepoint > > --- > frmsrcurl: > http://msgroups.net/microsoft.public.sharepoint.general/Install-order-for-SharePoint-2010-SQL-Ser...

When typing a formula, I cannot see the result. I have tried ctrl + ~, however this just expands all columns. Thi only seems to happen on some of my worksheets. Any suggestions are grealtly appreciate. I have also tried searchin for an similar question, but could not find one. Thanks!!! Shan -- Message posted from http://www.ExcelForum.com Shane Maybe the cells were formatted as text when you typed the formula? Andy. "Shane >" <<Shane.zxdn9@excelforum-nospam.com> wrote in message news:Shane.zxdn9@excelforum-nospam.com... > When typing a formula, I canno...

aack.. I went to 2007.. so far, ok- one I can't solve probelem. I have a cell with conditional formatting.. in part it reads =VALUE(RIGHT(FE2,4)) < 1-FE$84 time was (2003) if I selected that cell, copied-- went to another cell, paste special, formats then the 'fe' '2' 'FE' but NOT the '84' parts above would change relative to the new location so if I went one cell down, one cell right I'd get =VALUE(RIGHT(FF3,4)) < 1-FF$84 now when I do this same paste special, I'm getting all original values-- as if they all had $ in front of them ...

Is there an easier way to delete Financial Series Inventory Batches that we don't want posted to the GL? We don't want to have to open each batch ID and click delete for each of these batches that we need removed. Is there a 'mass delete' functionality that we could use? Thanks. New VBA form can be easily developed to cover this requirment, i don't think you can manage this issue in Great Plains forms. Regards, Mohammad Daoud "Banner" wrote: > Is there an easier way to delete Financial Series Inventory Batches that we > don't want posted to ...

I keep getting this message when installing MS CRM 1.2. Anyone else had this issue? ------=_NextPart_0001_31256612 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Brian, This error can mean a few different things. Below are a couple of KB articles that discuss the error message: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;861915 https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;870105 Thank you, Tami J. Lemar Microsoft Business Solutions Support This posting is provided �AS IS� with no warranties, and confers no rights. ------...

I am using Excel 2003 on a Win 7 machine. I am a stamp-collector and I am preparing lists of stamps I have to be able to take with me to the stamp club as spreadsheets rather than carrying 10 pounds of catalogs. My catalog identifies Parcel Post stamps by adding the capital letter "Q" before the number, i.e. Q1, Q2, etc. When I insert Q1 in cell A1 and Q11 in cell A2 and then right-drag and "fill series" for ten columns, the second row is shown accurately as Q11 to Q20 but row 1 shown as Q1, Q2, Q3, Q4, Q1, Q2, Q3, Q4, Q1, Q2 as if the software is reading the Qs a...