Join two formulas
I have this formula:
And I only want this to be true if:
I don't think it is as easy as joining two together, but I tried:
I also tried:
=SUMPRODUCT(--(YEAR('2003-2...how to highlight a cell so I know which point on the chart it is?
Hi, I am curious to see if anybody could create a visual relationship between
a point on the chart and the data cell. Let's say if we have 1000 points on
the chart, when I point to one of them--it will show the values--but I still
have no idea where the point actually comes from. Or on the other hand, when
I try to hightlight some cells using formats, the best thing is to see the
points on the chart also being highlighted in some way.
Thanks for any hints there.
I am not sure whether I can help you here. If you select a data point and
you move it Excel pops up a Goal Seek...Password protecting hidden cells
I have a client that has inventory on a single excel file on a office
network. They want to be able to hide some cells from some users and not from
others. They have mentioned they have group-level security setup on their
network, so this could be used. What I am trying to do is hide some
columns/rows and then password protecting the sheet, and also tried to
password protect the range as well, but it does not seem to work. I have to
unprotect the sheet to hide/unhide the column, then I have to remember to
protect it again after. This can obvious lead to human error where the user ...How do I add a formula to a range of cells
1) You can type a formula into a cell and then drag the that cell's fill
handle (solid square in lower right corner) to fill other cells with the
formula. You need to know about absolute and relative referencing - see Help
I the cell is part of a vertical table, double clicking the fill handle is
quicker than dragging it.
2) You can select a range of cell, type the formula and finish off with
CTRL+ENTER. This fills the selected range with the same formula - with
appropriate cell reference changes.
Microsoft Excel MVP
...macro error in cell reference in the AUTOSAVE.XLA
I have a user who is using Excel 2000 that when he starts
up Excel it comes up saying that there is a macro error
in cell reference in the AUTOSAVE.XLA.
Will replacing this file resolve this or is it caused by
Lots of people are using autosave.xla in xl2k without problems.
It shouldn't hurt to delete that file and reinstall it from the distribution CD.
Dion Pauls wrote:
> I have a user who is using Excel 2000 that when he starts
> up Excel it comes up saying that there is a macro error
> in cell reference in the AUTOSAVE.XLA.
>...Tightening up range in a Line Chart
This appears pretty basic, but I can not find a solution.
I am comparing two sets of similar data which are between 800 and
1600. The Line Chart I have created allows for data on the vetical
axis from 0 to 1800.
The differences in the data would be more efficiently demonstrated if
the range of the vertical axis were tightened to match the range of
Is there a way of doing this?
I am working in Excel 2007.
Format Axis/ Scale and choose your preferred min & max values instead of the
automatically chosen ones.
"Dano" <db_murray@y...WLM Junk Mail Filtering by Subject Line content
In the old web based HOTMAIL, we could filter junk mail by examining Subject
Line content (all, start or portion). I like the WLM both desktop and
webbased versions, but I am unable to filter my junk mail by examing subject
Does this option still exist, if so how do I get to it?
Thank you in advance for your assistance.
WLM is only a desktop version.
Windows Live Hotmail(WLHm) is the web UI for Hotmail type accounts(Hotmail.com, Live.com, Msn.com)
A Junk Mail setting to filter by subject is not an option in WLM or WLHm.
One does have the o...Playing Vedio clip in excel cell
Do anybody know how to play a vedio clip in a excell cell.....
don't think this is really possible. And as in your other thead: why do you
want to do such a thing with Excel?
"Jagz" <email@example.com> schrieb im Newsbeitrag
> Do anybody know how to play a vedio clip in a excell cell.....
...why have my spreadsheets stopped calculating formulas?
The sheet calculates only those cells that I retype the formula for. If I
change the values again it will not recalculate until I retype the formula
Most likely, the calculation mode has been set to manual instead
of automatic. Go to the Tools menu, choose Options, then the
Calculation tab. There, check the Automatic check box.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"dfbaskwill" <firstname.lastname@example.org> wrote in
> Th...getting a y-value from a graph knowing the corresponding x-value
if you have a graph with three points plotted [(x1,y1),(x2,y2),(x3,y3)],
can excel find y4 if you know the corresponding x4?
lsu-i-like's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14317
View this thread: http://www.excelforum.com/showthread.php?threadid=392733
You could use FORECAST() or TREND() to predict the y-value assuming that
the points can be approximated by a straight line. You could use
FORECAST() to predict the y-value assuming that the points can be
ap...Need Count Formula Help
Hello, I'm trying to write a count formula. Column A has just names, many
with "New Hire" as a placeholder for upcoming heads added throughout 2005.
Column B indicates the hire date --- only the "New Hire" labels will be in
the 2005 year. Then I have twelve columns representing the months for 2005. I
can't figure out how to write the formula, to appear at the bottom of each
month's column, to count the total dates that fall within January 2005,
within February 2005, within March 2005, etc. Any help is much appreciated.
Thanks in advance!
Nevermin...How do I move data without changing an associated formula?
I have created an Excel worksheet which I update with new values each day
(golf scores). I keep the last 10 scores but want an average of the last 5
scores. How can I move the data one column to the left and not change the
averaging formula so that when I post the new score it still uses the
original formula (sum of G5 to K5 devided by 5).
If you put a "$" before any cell reference, it will "freeze" the formula
value. So, if your formula references cell G5, you can use $G5 to freeze
the column or G$5 to freeze the row or $G$5 to freeze the entire cell
...pasting linked cell from Exc and maintaining formatting no matter
I am linking a data cell from Excel into Word.
I am using the paste special, paste link: unformatted text
I make adjustments to the font and font size in Word. I goto Excel, and I
modify the text in the cell and go back to Word and it changes.
How can I get the formatting to stay the same, no matter what?
In the WOrd document, press Alt+F9 to display the field codes and add the \*
charformat switch inside the closing }, replacing \* mergeformat if it is
there. Now apply the desired formatting to the L of Link then press Alt+F9
again to toggle off the display of the field co...COPYing the content of a cell to a graph as Title
I need to copy the content of a Sheet1 cell (just a name
to a graph (in the same xls file) as its title.
Ctrl+C does not retain the info as I go back to the graph
sheet to PASTE it on the title.
When I write a macro, the code displays an error that
says the procedure can't be performed.
Help, please. Thanks.
Click on the Current title of the chart. In the formula bar type
Once you have the = in the formula bar, switch to the sheet that contains the cell you want to link to, then press enter
If you don't currently have a title, enter it by going to Chart|Chart Options. ...how do I draw a dotted line
I wish to draw a dotted or dashed line in excel, how do I do it
If your drawing toolbar isn't visable, click View>Toolbars and select
Drawing from the list.
Select the line icon and draw your line where you want it, then right click
on the line and select Format Autoshape, in the Colors and Lines tab you can
adjust how your line looks.
Hope this helps,
"blank flange" wrote:
> I wish to draw a dotted or dashed line in excel, how do I do it
I have the following data I want to chart:
8 12 16 36 64 100
Mtl Cost $15,207 $19,079 $22,950 $42,308 $69,408 $104,252
Engr Cost $16,640 $24,960 $33,280 $74,880 $133,120 $208,000
When I created the chart the upper bounds of the y axis is $350,000, and the
Engr Cost series, although states it $208,000, it's above the $300,000.
What am I doing wrong.
Hover your mouse above each axis in turn.
Right-Click and select Format Axis.
Go to the Scale page, and uncheck the auto boxes.
Put in the values you feel are appropriate (Max, Min etc.) and click OK.
If the visual effect is not w...Formula Too Long #2
I am not sure what you are talking about with creating a
name for the link. Could you please define a little
try creating a name for the link
>"Todd" <email@example.com> wrote in message
>> Is there anyway to expand the amount of characters that
>> formula will allow? I am trying to link 14 different
>> spreadsheets to one and I keep getting the formula is
>> long error. Any help will be appreciated. Thanks in
If you are simply addi...Format X-Axis with Base Unit = Hour(s)
I dealing with hourly data I need to graphically present
the data in hourly intervals. MS Excel only allows for
Base unit selections of Day(s), Months(s) and Year(s).
Is there a solution available to plot hourly indexed
values without the graphical scaling on hourly intervals?
[This followup was posted to microsoft.public.excel.charting with an
email copy to Joe.
Please use the newsgroup for further discussion.]
The easy out is to use a XY Scatter chart.
The more complicated solution is to use a XY Scatter chart with an
additional (dummy) series that represents the values you want to s...how do I UN-concatenate cells
I have a string of numbers 00633.84010.12740.0000.10101 that I need to break
out into the separate components.
Is the string of numbers always 5 numbers, a dot, 5 numbers, a dot,
etc? If it's *always* in that format you can use a MID() function. If
the separate components are between dots but irregularly spaced it
If it's the easy scenario, and supposing your string is in cell A1, try
If you try Data > Text to Columns and follow the options, this
may be what you are after.
juli...Can a formula determine if a cell is a particular colour?
I have an Excel 97 spreadsheet with heaps of rows in it, some of which
have red rather than black text.
Is it possible to have a formula that can show me the rows that are
red so I can then sort on that column.
For example, a formula something like:
Thanks a lot for any help.
No worksheet formula, but you could create a simple UDF. You could try this
technique that evolved out of previous threads between
Harlan Grove and I. Create a function to get the colorindex of cells, namely
Function ColorIndex(rng As Range, Optional font As Boolean = Fals...Shortening SUMPRODUCT formulas
This is a re-post, in a sense, of a question I posted last week to this
I work at a college and use SUMPRODUCT formulas to total up all kinds of
stuff. For example, to calculate the number of people majoring in a
particular area (in this case, Social Sciences and Arts), I use the
=SUMPRODUCT(((Major="African/African Am Studies")+(Major="Ancient
Studies")+(Major=&q...Retrieve last non zero value in a range of cells
How do I extract the last non zero value in a range of 5 Cells for
From the example above I would require to choose the value 98 from the
Range declared. Some weeks it is possible for all the values to be >0
so therefore I would need the Value in E1 other weeks it may be B1
Would it be possible to return the value I require into Cell G1 in
the example above?
Entered in G1
Gord Dibben MS Excel MVP
On Wed, 8 Jun 2011 11:55:19 -0700 (PDT), Pete <firstname.lastname@example.org> wrote:
>...Copying matched criteria plus relevant columns to new worksheet
I want to use code in a macro to search for criteria in a worksheet which
allows the matched data to be copied (along with other relevant columns) to
another worksheet in the same file. Would this involve some sort of loop?
Any help apreciated.
Thanks in advance,
there are several ways to do it.
if you want to copy a hundred cells for three columns every time something
in row 3 is matched
somehting like the following could be used.
tar=1 ' target column in sheet 2
for c = 1 to 34 step 3 ' this will be the look up column in sheet 1
if sheets("Sheet1&quo...what is the format for an input cell?
I have Office - Student and Teacher Edition 2003
I am having trouble making a table ...
I try to make a Data Table and try to
define $E$3:$G$16 as the range in the row input cell area so I can make
this a one-input data table
and then I enter C$9$ for the column cell input area
a pop up pops up saying that the input (row) cell reference is invalid
I hope this not a bug that can only be fixed if you have the full version of
it should be $C$9
Pumaman <Pumaman@discussions.microsoft.com> wrote in message
news:D68327C9-5237-4353-8474-890677696F9...Macro to clean empty cells
As I have had some great help from this group before, here is another
Am looking to clean up some sheets in various workbooks in Excel 2007
Anybody able to help me out with a macro that will look for empty cells and
then clear them out of all formats or hidden characters etc. that they may
have but cant be seen.
Will only need to run it on individual named sheets rather than on whole