Is there any way to copy a formula from one workbook to another without bringing over the reference to the originating workbook. If you're just bringing one formula, you can copy from the formula bar and paste into the formula bar. If you're doing lots then you can change the formulas to text, copy|paste, and then convert those text strings back to formulas (in both workbooks): select the range edit|replace what: = (equal sign) with: $$$$$= (some unique string) replace all copy|paste edit|replace what: $$$$$= with: = replace all (both workbooks) === Another option ...

I have a database in a spreadsheet that keeps track of stats for a couple of teams I am on. The formulas have quit working now. Every single formula has quit working since I last worked on the sheet last week. Even simple adding of columns. I can enter the information in the columns and then go to the cell with the formula and open and hit enter and it refreshes the cell and starts working. Any Ideas???? This is very frustrating. Thanks for any help. Richie Richie, Is Tools/Options/Calculation set to Automatic?? John Richie wrote: > I have a database in a spreadsheet tha...

I have a list of dates in column A, and numbers down cols B-E. Off to the right, I need a table that lists the month/year and the totals for each of the columns. It needs to be a separate table, rather than a pivot table, so it can be copied-and-pasted by others. What I _think_ I need is: -- a formula in H2:H10 (maybe more) that will look down the full range of dates (call it A2:A100) and put one month/year per row >> H2 = 02/09 H3 = 03/09 H4 = 04/09 etc. -- a formula in I2 that will sum all entries in B2:B100 with the month and year of H2 I think I could get the secon...

Hi, I am trying to create a line chart in excel with two series. Conside the following data: np time1 time2 1 1000 100 2 900 90 3 850 80 4 700 70 5 600 60 6 400 30 7 300 10 8 270 5 The first series is plotted correctly but the second series i completely wrong. I was wondering if someone else has encountered suc a problem with two series charts. Can anyone suggest a solution to thi problem. thanks, sami -- samit70 ----------------------------------------------------------------------- samit700's Profile: http://www.excelforum.com/memb...

Office 2007 I am trying to display in a cell the last value in a selected column. Numbers are added daily to this column but I need this specific cell to show the last entry at all times - any ideas or help would be appreciated Thanks in advance Bryan Sun, 10 Feb 2008 20:53:28 +0900 from JohnWA <j_ustforfun@hotmail>: > Office 2007 I am trying to display in a cell the last value in a selected > column. Numbers are added daily to this column but I need this specific cell > to show the last entry at all times - any ideas or help would be appreciated =OFFSET($A$1,COUNTA($...

I am making a chart with weird X-values. Instead of single, whole numbers, I need each value to be as follows: 1-2, 3-5, 6-10, 11-15, etc. Please help me!! My chart keeps going to whole numbers instead and its been driving me crazy for days! How can I fix this problem?!! ...

I have created some charts and saved them as templates (crtx). I can apply them to existing chart and, subject to some occasional odd behaviour (plotting agains the ''wrong' axis) it works ok. Not a huge timesaver but you take what you can get. However, if i 'Insert' a new chart using any of three templates I have created - and then attempt to add data to it the workbook is corrupted when I try to save and ALL charts and Range names disappear. I am using the name manager from peltiertech but that has always worked before and even if i avoid using it the pr...

I need a formula to compute a final grade for a class when there are assignments missing. I also need to figure out what my target grade is using goalseek?? I need an if formula that displays TRUE if you are currently passing your class and FALSE if you are not. Any help would be appreciated. To help you we would probably need a bit more information from you - 1)how will you calculate the final grade? (EG Average of Assignments 1-10) 2)What grade/percentage dertines the pass grade Kind regards Paul Falla >-----Original Message----- >I need a formula to compute a final grade for ...

good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i cant get it right. i have tried various variations of the formula.any help is appreciated. thx wyn -- wynb in you example is the cell you want updated "Q1"? If so than it should read =IF(Q80="closed",NOW()) -- Kevin "WYN" wrote: > good day all, i am trying to get this formual ie: =if(q80="closed",q1=now())i > cant get it right. i have tried various variations of the formula.any help is > appreciated. thx wyn > -- > wynb In that formula yo...

So am I correct in finding that the charting preformance speed of 2007 is an absolute dog? Trying to chart 16 datasets with 2055 data pointrs each on an x-y chart, with the colours of some of the lines adjusted to keep them in four "matching sets". I have to wait for nearly a minute for a redraw when you flick back a tab. Can't type any headers without taking a break for a coffee. Am I missing something please? Your impressions match my own. Excel 2007 with SP1 installed is slightly better in some situations. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials ...

Hi all, So I am fairly weak when it comes to Excel formulas, so I'm sure what I require is really simple, I just have no idea how to formulate it. Basically, I have a column of values, some of which are negative values and some are positive, but they are changing from month to month (i.e. accruals and prepayments). I need to add up all the negative values from the column in a new cell, and all the positive values in another. So it will be something like If amount>0 then add to cell, else add to other cell. So there you have it, really simple I just have no idea what to do. So i...

My son is working on a science project. He had to track the temperature and precipitation levels in Daytona Beach for 9 years. I put the data in 2 worksheets, one for temperature and one for precipitation. He now has to make charts for the data. We've done the 2 separate charts but now we need to make 1 chart showing all the data. Is there a way to take the data from the 2 separate worksheets and put it into 1 chart? http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom So...

The only way I know how to add significance to charts, such as an asterisk above a bar chart, is by adding a text box. Any adjustments to the chart require rearranging the text box. Adding significance to charts is quite common and I can't believe Excel would not have an easier option for this. Another option is to apply shapes to hidden series. For example, if the shape were an asterisk you could then control the position of the asterisk based on your source data. The asterisk could always appear over the bar even if the bar moved when the source data changed. -- John Mansfie...

I have a 5 row 3 column sheet in A1:C5. Here are the 15 values (first 3 are column headings): type day amount A,1,10 A,2,11 A,3,12 B,3,7 I'm aiming to have a chart with 1,2,3 on x axis, and 3 blue values: 10 units tall over the 1; 11 over the 2; 12 over the 3. Then I want a red 7 over the x value 3. So I want to see both 12 and 7 above the 3 on the x axis, either overlapping or side by side. I created a chart by highlighting C2:C4 and hitting F11. I can name that series as A. So far so good. Now I want to overlay B data, so under Source Data I add a series named B with values =sheet1!c5...

I am trying to format spreadsheet. Need 3 counts. 1st count is for 90% and above =COUNTIF(H5:M16,">89.99"). Second count is for Under 80% =COUNTIF (H5:M16,"<79.99") . Can anyone help me with a formula for 3rd count, between 80% and 89.98? I can't seem to formulate it. Thanks in advance Hi Barb, Try =COUNT(H5:M16) - COUNTIF(H5:M16,">89.99") - COUNTIF(H5:M16,"<79.99") -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Barb" <...

> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3325082853_2433946 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit After inserting a chart in a word document I cannot seem to get back to the excel worksheet update data on the chart. Advice please. Thank you. Joe --B_3325082853_2433946 Content-type: text/html; charset="US-ASCII" Content-transfer-encoding: quoted-printable <HTML> <HEAD> <TITLE>Finding data for a chart in word<...

I have a 3-D column chart that has the perspective setting as 20, the elevation as 24 degrees and the rotation as 198 degrees. I change the settings, close the dialog box and click save. Yet the perspective setting resets itself to 15 every time I open the file. I am using Excel 2007. Any suggestions? Thanks. Hi Kathi, I have been able to duplicate your problem, I suggest this is a bug, although there might be some reason for this behavior, I can't think of any. It's probably not worth it but I suppose you could attach a macro to the Open_Workbook event and reset it to...

I have used Named Formulas and am now learning about User Defined Functions. One thing I found with named formulas is that the naming conventions around global/local selections. Reading about UDFs makes me think they might be a better option for a lot of my work. Any insight as to When to use one over the other will be appreciated. Thanks Robert IMO they are different beasts. A named formula would be used to shorten formulae by including a name rather than the sub-formula (especially where it is repeated), and to improve readability/maintainability, as the name would/should be meanin...

I have a large figure and series of other small figures on differen rows. How do I get the different sets of small figures that exactly or almos add up to the large figure:confused ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You can try to use solver under tools>solver (if it is not there select it from under tools>add-ins and keep the office/excel cd handy and follow instructions) Put the series of small figures in a range, select that range and click in the n...

I'm trying to create a quadrant analysis. I have the four quadrants as stacked columns and the real data as a XY Scatter. The x axis for the XY Scatter is not showing. The real data series are marked as the secondary axis. Does anyone know how to make the X axis appear? Usually Excel gives you both secondary axes when you add an XY series to a column chart. Go to Chart menu > Options > Axes, and check the box for the secondary X axis. If you're using Excel 2007, it's found on the middle of the three charting tabs, under Axis. - Jon ------- Jon Peltier, Microsoft ...

We just got upgraded to Micro. 2003 and let me tell you it's not pretty. I previously had a lengthy Word doc with a bunch of charts linked from an Excel worksheet I had. Since the upgrade I had to re-link every chart because the links no longer work, why is this?????? Also after I cut and paste special as a link each chart I can no longer freely move the chart around or even right click to get properties and select the size I want the chart to show. I paste it in the right column of a word doc table so I need to size it to fit into the column and I can no longer do this. This i...

I would be grateful for any help with this. I am trying to scan one column on a worksheet for a name e.g. Smith and then to copy all the information on the same row into another workbook. There would be many rows with the name Smith in that column and would like ALL of these rows to be copied into the other workbook. I saw the information posted by Bob Phillips on 10 Jan about programming excel to do this but I do not understand how the formula would actually look. Thanks a lot. Easiest way is to put an autofilter on the top of the row heading: Data>Filter>Autofilter: then c...

Hi, Could anyone suggest a method of having a text field (or similar) have the behaviour of a data bar in excel 2007. I have a field that displays a percentage and would like to present the information to the user in a bar graph / data bar / progress meter style appearance. The value is only ever updated when the form is first loaded, ie. the values will not require updating/refreshing. The data comes from a query. It would be really great if it could change to yellow at 75% and red at 90% as some sort of conditional format as well? Thankyou. You can insert the microsoft progress b...

Here is the formula =InventoryOrdered!D3-COUNTIF(InventorySold!B:B,"10-132500") What I want it to do is every time I enter numbers in cell D3 to CA3 in Inventory Ordered I want it to add those numbers, then subtract Inventory Sold countif function. Not quite sure, but... =sum(inventoryordered!D3:CA3) - COUNTIF(InventorySold!B:B,"10-132500") Crowraine wrote: > > Here is the formula > > =InventoryOrdered!D3-COUNTIF(InventorySold!B:B,"10-132500") > > What I want it to do is every time I enter numbers in cell D3 to CA3 in > Inventory O...

How could i higlight different intervals in the charts. e.g. I have on the horizental axis time from 0 hrs to 24 hrs. and on the vertical axis body temprature. I have three different subjects data on this chart. I would like to higlight the area where those subjects were sleeping, but I could't get the solution in MS Excel. Does anyone helps me on this issue Hi, You could add another series plotted as column. Plotting the maximum value when shading is required. You will need to set the maximum Y axis value so the columns can reach the top of the plot area. Format the columns via t...