Copying a formula to new workbook
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
what: = (equal sign)
with: $$$$$= (some unique string)
Another option ...Formulas in Spread sheet quit working???
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
Is Tools/Options/Calculation set to Automatic??
> I have a database in a spreadsheet tha...Formulas to (a) pull month, year and (b) sum within date range?
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
-- 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...excel series charts
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
samit700's Profile: http://www.excelforum.com/memb...Formula help *
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($...Excel Charts #19
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?!!
...Saving chart templates
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...Excel formulas #9
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
2)What grade/percentage dertines the pass grade
>I need a formula to compute a final grade for ...= if formula needed
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
in you example is the cell you want updated "Q1"?
If so than it should read
> 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
In that formula yo...charting performance speed
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
Am I missing something please?
Your impressions match my own. Excel 2007 with SP1 installed is slightly
better in some situations.
Jon Peltier, Microsoft Excel MVP
Tutorials ...Simple If, Then formula for excel
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
So there you have it, really simple I just have no idea what to do. So i...Can you combine data from 2 worksheets into 1 chart?
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?
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom So...Is there an easy way to denote significance on charts
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...Overlapping 2 charts (XL2003)
I have a 5 row 3 column sheet in A1:C5. Here are the 15 values (first
3 are column headings):
type day amount
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...Excel Formula #8
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
=COUNT(H5:M16) - COUNTIF(H5:M16,">89.99") - COUNTIF(H5:M16,"<79.99")
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Barb" <...Finding data for a chart in word
> 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.
After inserting a chart in a word document I cannot seem to get back to the
excel worksheet update data on the chart.
<TITLE>Finding data for a chart in word<...Perspective setting won't save in a 3-D column chart
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
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...User Defined Function vs. Named formula
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.
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...Obtaining series of data that adds up to a particular large figure
I have a large figure and series of other small figures on differen
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...XY Scatter Chart Not Showing X Axis
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 Peltier, Microsoft ...Formatting Linked Charts Now An Issue
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...How do I create a formula to copy and paste between workbooks?
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
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:
then c...Data Bar / Progress Meter / Bar Chart
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
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?
You can insert the microsoft progress b...Help with formula #14
Here is the formula
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")
> Here is the formula
> 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
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
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...