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...Validate the format of a number
I need to determine that an entered serial number is valid. It must check
that it is 11 characters and follows the format as follows:
a letter, followed by a number, followed by 2 letters, followed by 6
numbers, and ending with a letter.
For example, the user enters D7PM234567B and the cell next to it would
indicate 'valid' or something similar.
If 87PM2345674 was entered, it was indicate 'invalid' next to it or
something similar to alert the user it is not in the correct format.
Thank you very much in advance.
=AND(LEN(A1)=11,CODE(...page numbering #2
I'm working on a landscape Excel document...the document
will become part of a booklet,and I need the page numbers
at the bottom of the page, but in portrait. Any help?
...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...XML formatting to HTML...beginner question
Hi, I apologize if I am posting this in the wrong forum. I am creating
a .net 2.0 website using vb.net. I have an XML file with the following
<lo number = '1'>
<Title>First Learning Objective. Click for more details</Title>
<details>These are the details for LO 1</details>
<lo number = '2'>
<Title>Second Learning Objective. Click for more details</Title>
<details>These are the details for LO 2</details>
<lo number = '3'>
<Title>Thir...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...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 I find an average number of specific words in a column
I am attempting to calculate a number of specific word occurrences. In
example, I have a column with yes in certain cells, and no in the others.
How can I display the total number of yes and no occurrences? I am fairly
new to Excel and know very little about coding in it.
you can use the countif function to return the numbers of "yes" and "no"
will count the number of "yes" answers in the range A1 to A100
will count the number of "no" answers in the same range
H...Add disclaimer to all email
Is there anyway to add a disclaimer to internal and external mail sent via
Outlook 2000/XP/2003 clients in an Exchange 2003 environment? All my
research has led me to 3rd party apps that do this but, of course, at a cost.
My CIO is asking me to find a "free" solution. But he requires both
internal and external mail to be stamped.
GFI Mail Essentials will do this if you download the 30 day trial version,
after the trial ends the Disclaimer portion wil still work. Otherwise you
can write an event sink,
http://www.ms...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...Maximum number of SMTP addresses per user
Hi we use an antispam filter that using ldap to determine if an email address
is valid before it reaches our exchange servers. This is very effective and
reduces the ammount of spam we receive. The system does not send bounce backs
if an address is not valid.
What I have been asked to do is to send find a way to send a message back to
the sender when someone emails an address of an employee that has left the
company to inform them to contact our customer services department.
The only way I can think of doing this is to create a mailbox that has
multiple smtp addresses on it that has an...Formatting with CONCATENATE
I would like to use the TODAY() function with a leading word, in this case
"Per ". The formula
="Per "&TODAY() produces "Per 38238" and I can't see any way to format the
"38238" to make today's date readable. It's easy to do it by using 2
columns, one for "Per" and one for the date, but is there any way to do this
in one column?
Try some variation of the following:
>I would like to use the TODAY() function with a leading
word, in...Conditional Formatting Text!!
I want to conditionally format some text in a spreadsheet using
I have column A1 with:
What I want to do is for all cells which contain "5a Be" "5b Be" 5c
Be" "4a Be" "4b be" and "4c Be" to be highlighted in Red - is there a
formula to put all of them into one formula - If("5a Be" or "5b Be)
"Kiran" <email@example.com> wrote in message
...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...Conditional Formating Help
I would love your help with a conditional formatting goal. I would
like to color a cell if it is part of a formula in another cell. Below
is an example:
A B C
1 12 13 =B1/A1 I then want A1 and B1 colored blue
2 12 13 =B1 I then want A1 and B1 colored blue
...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 (...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.
...Receipt with Serial Number
I need a receipt template that will print the serial number on it. I have
checked out Customer Source, and can't find one. Could someone send one if
they have it. If not, can you tell me how to modify an existing receipt to
show the serial number?
...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
> corres...How do I lock all cells in Excel except 2 which I need unlocked?
By default all cells in excel are protected or locked, select the cells you
want to unlock and go to format, cells, protection and uncheck locked, the
go to tools, protection, and protect sheet, enter a password if you want,
now only the cells that you unlocked can be edited. Be aware that this
protection is very easy to break, the code to do so can be found very easy,
but it will work for most people .
If you only need a few locked I would select them all first, Ctrl A, then
go to format, cells, protection and uncheck locked, then select the cells
you want to lock and go ...html fonts too big
Using Microsoft OfficeXP. Fonts are too big on websites
and Oulook where there is HTML. The fonts not only display
to large on the monitor, but are also printed out WAY TO
BIG! I am having to write this post on a separate computer
since the microsoft website cannot be displayed on the
computer in question. Leads me to think that the problem
is in internet explorer. I have checked Accessilibity
settings; the fonts settings under Edit in Internet
Explorer; Themes. Nothing has worked! I have uninstalled
and then installed OfficeXP. I'm up-to-date on the service
packs, too. Please he...Conditional Formatting
Apologies in advice for this being an easy one, but I seem to be having a
I have a conditional format in cell M17 which is a 'Use a formula to
determine which cells to format' =$M17<$K17 full red
This works fine.
I am then trying to copy this condition down several hundred lines, but it
copies it exactly as =$M17<$K17 rather than changing the row number each time
it is copied.
How do I copy it so that it changes the line number every time?
Take out the $ signs.
If my comments have helped please hit Yes.
"The Rook" wr...