Are there any way to add text and a function in the same
cell? For example Hello =sum(XX:XX)
Try something like
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"danne" <firstname.lastname@example.org> wrote in message
> Are there any way to add text and a function in the same
> cell? For example Hello =sum(XX:XX)
Sure, for example: ="Hello = "&SUM(A1:B1)
For more info, try MVP Debra Dalgleish's &...Macro or Formula needed to search data in cells
I am looking for a way to create a formula/macro to do the following:
My worksheet setup:
01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27
02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32
03 04 05 06 07 08 09 10 11 12
04 05 06 07 08 09 10 11 12 13
Each cell contains a 10 number sequence. The range A1:A30000 is my
randomly generated sequence. And C1:5000 is my database of archived
If I wish to check if the combination in cell A1 is anywhere in the
range C1:5000. I use the formula.
=IF(COU...Dynamic Bar of Pie Chart: Series1 Label & Value Issue
I have created a Bar of Pie chart and everything works great. I even have
named ranges on my data so that the bar portion automatically updates when
the data changes. However the Series1 Label on the pie itself does not
change to reflect the new total so that it is the sum of all the values in
the right column below. Does that make sense?
I have one series and my data looks like this:
The pie portion has Contracts & Other as labels with the totals of...Forwarding Using Mail Enabled Users
Are there any differences forwarding mail to an external recipient using
a mail enabled user vs a mailbox enabled user forwarding to a contact
aside from the fact that the mail enabled user doesn't have a mailbox?
Steve Fukumoto <email@example.com> typed:
> Are there any differences forwarding mail to an external recipient
> using a mail enabled user vs a mailbox enabled user forwarding to a
> contact aside from the fact that the mail enabled user doesn't have a
A mail-enabled user has an external...negative values on axes
this is my first mail here. well my x axes start from -2 and my Y axes data
start from -5, I change both to -5 (scale) they are shown (XY scatter dots)
as cross, like + sign. what I need:
how to change the graph punt the joint axes point from 0 to -5? so the
chart shws as usual not like a cross.
Change the Values axis crosses at to -5 for both axis.
This is an option on the Scale tab of the Format Axis dialog.
> hi there;
> this is my first mail here. well my x axes start from -2 and my Y axes data
> start from -5, I chang...How to export values from XY (Scatter) graf?
Is it possible to export each x and y value from XY (Scatter) graph?
...Hide rows with value "Hours"
I would like to toggle rows to hide/unhide based on the value "Hours"
in column B. I think filtering is an issue to some blank seperator
rows and merged cell headings. There are no merged cells involving
Column B. Thanks.
Public Sub ProcessData()
Const TEST_COLUMN As String = "B" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).row
For i = 1 To iLastRow
.Rows(i).Hidden = .Cells(i, TEST_COLUMN).Value = "Hours"
...Compare cells, update based on Ifs
I need to compare cells to the cell above them and to the right of
them. Based on the comparisons I'll need to update the original cell
with one of those adjoining cell values. After I finish with one
column then I need to repeat the procedure on the column to the LEFT
of the original column.
I know IF, THEN and ELSE statesments but I don't know VB for Microsoft
Range could be all 65,000+ rows on a workseet
Start on ColumnJ, Row2
If ColumnJ, Row2 is Null _
If ColumnK, Row 2 is Not Null _
If ColumnJ, Row 1 is Not Null _
ColumnJ, Row2 Value is ...If cell value is greater than another cell value, clear contents.
Okay, hopefully this is the last question today!
I've found the below formula in one of the posts here, but my brain has
completely failed on me and I'm not sure how to adapt it to my requirements.
I have a value which is entered by the user after a prompt, that populates
in cell G4 in a sheet called 'Lookup'. For arguments sake, the current value
I have another worksheet, 'Data', which has a column populated with dates
(say column B). I'd like a macro that looks in column B on the data sheet,
and clears out any dates which are gr...Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that
is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I
want those cells to remain blank.
That's just the way it is ..
Instead of : =Sheet2!A1
you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1)
Alternatively, we could suppress the display of extraneous zeros
in the entire sheet via:
Click Tools > Options > View tab > Uncheck "Zero values" > OK
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot&g...Date chart formula question?!?1
In a very old spread sheet i was using this formula
=IF(OR(AND($D7<G$6,$E7>F$6),AND(F$6>=$D7,F$6<=$E7)),".","") and it worked for
dates pre 2009. However, i now wish to use it for dates between 2010 - 2011.
Each column represent a week and the rows will indicate and project or task.
can anyone help?
There is nothing it that formula that will make it work for pre-2009 and not
for later years.
Perhaps you need to look at your data values?
If you have a problem, you could tell us what values are being fed into the
formula when it works...Use the same name on multiple sheets in same workbook
I am new to Excel 2002. In prior versions I could create sheet specific
names easily. With Excel 2002 every time I try to re use a name on another
sheet (same workbook) the name gets moved to the new sheet. I use several
workbooks (one book for sales, one for cost of goods, etc), with 12 sheets
named for each month of the year. Each sheet is a duplicate of the first.
As the year progresses I fill in the data at the end of each month. In the
past I used the same names(Gross.Sales for example) on each sheet in the
workbook. I then can build monthly summary reports using these names...IF Formula #6
Does anyone know a way of returning a picture instead of a value in an IF formula?
eg =IF(A1=JIM, <picture>, 0)
Any help very much appreciated!!
Cheers in advance!
do you need the pircote or would a hyperlink also suffice?. In this
case use something like
Jamie Bishop wrote:
> Does anyone know a way of returning a picture instead of a value in
> an IF formula?
> eg =IF(A1=JIM, <picture>, 0)
> Any help very much appreciated!!
> Cheers in advan...How do you ensure that null-valued elements are not serialized?
I am using complex types in order to support serialization/deserialization
of floating point numbers, since floating points can't be null.
I've seen how to suppress attributes that are "not specified", such as
having a float member called Value, and a bool member called ValueSpecified.
This instructs the XML Serializer to omit that attribute altogether if it
wasn't "Specified". But how can I tell it to omit the XML element
Here's the problem:
I deserialize an object that looks like this:
<name>Joe<...Error when send email from campaign response using template through workflow
There is an invalid argument error shown in workflow records if send email
from campaign response using template. Is it impossible to send email in
this way ?
...Multiple Cell Formats
Is it possible for a column to have more than one format, but for those
formats to be similar.
I would like to have a multi-format date column. The format can either be
Month-Year (Jan-99) or Year only (1999). (I would even like to have
something like Jan/Feb-99, but I can live without that.)
Any help would be appreciated.
With true Excel dates in column A e.g. 01 Feb 07, in column B
=A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07
=A1 Format>Cells>Number>Custom> yyyy will give 2007
or =TEXT(A1,"yy...Capture initial value of text box
Not sure why I can't seem to get this to work, but here's what I'm trying to
I have a text box, 'Actual_Due_Date', tied to a field in a table. In the
same table is a text box named 'Org_Due_Date'.
When the initial value is set in the 'Actual' field, I want to capture and
store it permanently in the 'Org' field.
I have two forms, a New Project form and an Edit Project form. My thought
was to just setup a simple macro in the New Project form in the 'Actual'
field that says After Update set the 'Org' field equal to the 'A...Invalid Argument Error when using 1/1/08 as a date
Not sure if this is a known issue - can anyone else create a record that
contains a date field with the value of 1/1/08 or 1/1/07, save it and then
open it again without getting an "Invalid Argument" error. I am able to
produce this error across different environments, different entities and
different date fields.
Would be interesting to hear more about this.
Just to let you know, I just created a task in CRM 4 and set the due date to
1/1/08 and it worked without any issues. This is a field displayed as a
Date/Time box, on not just Date. I don...Use of InstallShield
I have got InstallShield with VC++, and I would like to
use it to create a setup for various types of project,
e.g. a game, activeX control, etc. However the project
wizard only allows you to choose an .exe, and it makes you
choose between 'database application', 'finance
application', etc. What if my product isn't an .exe, say
it's an .ocx, or what if it isn't one of these predefined
types of program?
I've no idea on where to start with the blank project
option. I added my .ocx to the 'components' view and then
tried pressing Ctrl+F5, but a...Conditional formating using VBA
I have to format cells (I4:J37) if cells (I43:J76) are:
How do I write that in VBA? (I am new to VBA so any help would be appreciated)
I asume your users will enter a value in the range("I43:J76").
In that case you case use the Worksheet_change(Byval Target as Range
function like so:
Private Sub Worksheet_Change(ByVal Target As Range)
' Check if only 1 cells value is changed
If Target.Cells.Count > 1 Then Exit Sub
' Check if changed c...help with formula #24
$40.50 when S is $795.00 I need help figuring this formula out where am I
going wrong? I keep getting the wrong figures. This is the formula:
=IF(S118<=25, 0.0525*s118) + IF(AND(s118 >25, S118<=1000), 1.3125 +
0.0275*(S118 -25)) + IF(S118>1000, 28.125 + 0.015*(S118-1000))
Up to $25 will be charged 5.25% ,
5.25% of the initial $25.00 ($1.31), plus 2.75% of the remaining closing
value balance for $25.01 up to $1000,
over $1000.01 5.25% of the initial $25.00 ($1.31), plus 2.75% of the initial
$25.00 - $1,000.00 ($26.81), plus 1.50% of the remaining closing value
balance (...what function would I use here?
In rows 1 to 192 I have a letter in just one of the columns
I want to fill in column C so that it will have the letter that is in
column D thru I.
What is the function that I use? I tried =MAX(d2:i2) but that
doesn't work. I suppose the MAX function is for numbers only.
Is it a FIND function or something different?
Thanks in advance.
"In this country anyone can grow up to be President.
That's the risk you take."
You could put this formula in C2 (for example):
and then c...Is there an add-in that will lock the cells like later versions of Excel?
I'm using 97 and for 99.9% of everything I do I works fine except I can't
lock cell format so there can only be data entry. I would be nice if I
could do that.
Can you be more specific about what you want and don't want.
"Marc" <mcnr(N_O-S_P_A_M)@mindspring.com> wrote in message news:QThPf.1161$sL2.firstname.lastname@example.org...
> I'm using 97 and for 99.9% of everything I do I works fine except I can't
> lock cell format so there can only be data entry. I would be nice if I
> could do that.
...Word 2003: Possible to Use both Forms and Tracked Changes Protect
I've not been able to figure out a way to protect a document so that one
section is protected for filling in forms only, and another section is
protected in tracked changes mode. I want to create better contract
templates for use by our sales teams (thus the forms protection - they can
only fill in the gray fields and not alter other sections of the template),
while allowing our clients to made modifications to the terms in the
subsequent pages. I find Word protection features very limited. Hopefully
it's better in 2007. Thank you.
A document can only have one type ...Number of cells that have same values
Imagine there are two columns of cells, each row of two cells are
corresponding to each other. How to find out the number of rows where the two
corresponding cells have the same value?
Assume your Columns are A and B, and your rows go down to 20, Array enter
this formula (CTRL+SHIFT+ENTER):
Change the column and row references to fit your data.
"Lingyan Hu" wrote:
> Imagine there are two columns of cells, each row of two cells are
> corresponding to each other. How to find out the number of rows where the two