can I find merged cells?
I'm trying to sort and get the message "merged cells must be the same size".
How can I 'find' the merged cells?
David, here is a macro by Dave Peterson that will do it
'macro looks for merged cells
'By Dave Peterson
Dim myCell As Range
Dim resp As Long
For Each myCell In ActiveSheet.UsedRange.Cells
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea(1).Address Then
resp = MsgBox(prompt:="found: " _
& myCell.MergeArea.Addre...Tab to specific cells
I am using excel 2002 and my boss 2003. I want to tab to specific cells in
my work sheet. Can anyone help me get started?
You could try the named range approach.
Select your *second* cell for data entry and then hold down <Ctrl> and
continue to select all the rest of the desired cells in order, ending with
selecting your *first* cell *last*.
While the cells are still selected, click in the name box and give this
range a short name such as "Entry1".
Now, when you're ready for data entry, simply click on the range name in the
name box. The focus for the range is th...Write formula for simple copy and paste to another cell
I have a worksheet that contains hundreds of columns. Each group of three are
related to each other (Cols A-C, D-F, etc...).
I have this formula that works well for columns A-C
=IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color
Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B$1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=COLUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B$2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COLUMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B$2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A...Text to speech particular cell
ive been trying for a while now to get the text to speech function to work
for a particular cell.
For example, i have a set of numbers and a total in D15, what i would like
is that if i change the numbers that contribute to that total, and the total
in D15 changes then excel will alert me saying "Change in Total".
ive tried and failed to get it notice the changed total and would really
appreciate some help
Thank You for any contributions
Let's put the message in a cell, say D16.
In a standard module insert the follow line:
Public OldVar As Variant
Insert the following...Copying cell contents to a list
First of all - apologies if this is in the wrong group. I wasn't sure if it
should be newusers (which I am) or programming (which I might need).
I have inherited a lottery syndicate at work after the previous organiser
retired. He kept an Excel 97 spreadsheet with 8 columns - A (Date), B to G
(Winning numbers) and H (Bonus number). This list has now grown to about 800
rows as each draw's numbers are added to the bottom of the list (I will be
removing about 600). I have adapted the spreadsheet to automatically check
our numbers against the winning numbers (using HLOOKUP) after I have ...PO with non-inventory item
I have a PO and receivable of a non-inventory item and it does not increase
the quantity for the inventory. How do I fix this issue so that the
receivable will show the quantity correctly? Thanks!
GP is designed not to track movements of non-inventory item, thus it will
not affect the quantity in your inventory. However, receipt transactions on
non-inventory will update your accounts payable when posted.
Hope this helps!
Dennis Araullo, MACS
Microsoft Certified Technology Specialist GP,CRM,AX
MCITP Installation and Configuration AX 2009
MCITP Database Ad...Numbers V Text
I want to print out a document but I don't want it all printed out in
numbers I want the text printed out.
Here is what I don't want it say 223, I want to say =SUM(C7:C11)/15
I am sure it can be done via "Options" but I can't work out how.
Toggle the view to Formula View - Ctrl + ` (that's the same key as the tilde
"James Ducat" <email@example.com> wrote in message
> I want to print out a document but I don't want it all printed out in
> numbers I wan...Place X in cell if criteria met`
Is there a formula to do this?
If cell B2 = pencils
Put an "X" in cell B7
If cell B2 = pens
Put an "X" in cell B8
If cell B2 = erasers
Put an "X" in cell B9
Thanks in advance
in cells B7 put
in Cell B8 put
In cell B9 put
> Is there a formula to do this?
> If cell B2 = pencils
> Put an "X" in cell B7
> If cell B2 = pens
...random number #2
how can i create a normally distributed random number
and a random number that has a beta distribution
i need these both to be integer outputs
thanks for the help
Use NORMDIST and BETADIST
> how can i create a normally distributed random number
> and a random number that has a beta distribution
> i need these both to be integer outputs
> thanks for the help
I think you meand NORMINV and BETAINV. See my reply under the OP's repeat of
It is generally better to reply to your original thread with fol...SMTP Server Port Number
I have a machine running Outlook 2K and with no user intervention the SMTP
Port Number will change within the Account Settings. When I change it back,
it will revert within about a 2 day period. Any help is greatly
What does it change to?
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. All
unsolicited mail sent to my personal account will be deleted without
After furious head scratching, Stephen Hartman asked:
| Hi all,
| I have a machine running Outlook 2K and with no user intervention the
| SMTP Port Nu...Custom number format with colors
I would like to format my numbers this way:
> 0 --> Green
= 0 --> Black
< 0 --> Red
How can I do that?
Go to Format->Conditional Formatting... set your specs and click the format
button to format the text the way you want. You will do a seperate one for
each color. Select the entire sheet first though (CTL-A)
Please rate when your question is answered to help us and others know what
> I would like to format my numbers this way:
> > 0 --> Green
> = ...Send Email With Cell Conent as Part of the Subject
Hyperlink is able to send email with cell content in the Subject line.
=HYPERLINK("mailto:Collections_Admin?subject=Overdue Balance " & "_"&
'Sheet1'!G15,"Email To Collection Agency")
Sheet1G15 has the data I want to show in the subject line.
Is it possible to do this with Macros? I am using Lotus Notes for email.
For lotus code see
Sending mail from Lotus Notes (XL-Dennis)
Regards Ron de Bruin
"igbe...Label a button from a cell content
Using Excel 97, is their a way to aquire the label for a button from
cell on a different worksheet? :confused:
Thanks in advance,
spyrule's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2548
View this thread: http://www.excelforum.com/showthread.php?threadid=38935
...inserting texts in cell based on conditions
I would very much appreciate if someone could help me
solving a problem, illustrated by the following example:
Column A B C
1 1 "LB"
2 1 "DK"
4 1 1 "LB/DK"
If there's a 1 in column A, the corresponding cell in
column C should get the text "LB" inserted into it.
If there's a 1 in column C, the corresponding cell in
column C should get the text "DK" inserted into it.
If both column A and B have ones in them, the
corresponding cell should get the...How do I Add and Subtract percentages in cells
Can anybody help I am trying to add and subtract percentages from cells
deducting tax from wages etc.
How do i go about it.As i am new to excel
Thanks in advance.
Check out this on-line tutorial for Excel.
Specifically the "Basic Math" section.
Gord Dibben Excel MVP
On Sun, 1 Aug 2004 23:06:56 +0100, "Ronnie" <firstname.lastname@example.org> wrote:
>Can anybody help I am trying to add and subtract percentages from cells
>deducting tax from wages etc.
>How do i ...I want to format a cell based on an adjacent cells value
I would like a cell automatically formatted in the same way a conditional
format works - but the format to be based on the value of an adjacent cell.
Any ideas as I cant work out how or if I can achieve this
Cumbo, Use Conditional Formatting, but change cell valve is to formula is
and use something like this, will change the cell if A1=25
=$A$1=25, so if you select B5 and put this in conditional formatting cell B5
will change when A1 = 25
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on an...calculation of cells
Periodically I open a work book and the calculation option
has been changed to manual and I cannot figure out why. It
seems that it would have to be done by a user and most of
my spreadsheets are only used by me.
Any ideas out there
Calculation, auto or manual, is set by the first workbook that's opened. It
is that way for any other workbooks opened in that instance of excel. Look
for a workbook you might have opened first that's been set to Manual and
saved that way. Go figure.
mvpearl omitthisword at verizon period net
------------------------------...returning vlookup values for blank cells
I have a spreadsheet that lists "soccer players" by name
down the first colunm and "time in game" across the top
and the position they play in array.
I then use vlookup for another spreedsheet by "position"
down the first column, time across the top and puts the
players name into the positions. All this works fine.
Since there are 5 more kids than positions, the orginal
spreedsheet has blanks when the kids are out of the game.
How do I use vlookup or other to extract the 5 sub'd out
kids at the bottom of the 2nd spreadsheet? It only
returns the nam...Counting cells based on color
I am looking for a "counting" formula that will count cells in a rang
based on their color, so that if I color a series of dates (to sho
'vacation) the number of cells (days) will be counted...to keep trac
Message posted from http://www.ExcelForum.com
There's no built-in functionality for that - it requires
a UDF (user-defined function). You'll find several
examples in the google archives:
MVP Chip Pearson also has some code:
http://www.cpearson.com...Multiple IF statements in one cell
Excel 2003 on XP.
I have a project control listing that has, in part of it, nine columns that
will hold the dates that certain stages are met, i.e. a date will be entered
as each stage is met and the following stages will be empty, until Stage 9 -
Project Complete/Signed Off.
Stages 1-9 each have a unique Stage Name/defintion.
I would like a following cell in the row to automatically show the Stage
Name for the latest date in the corresponding stage cell for that project.
I have a nested IF statement that works but as the project list is to get
very large I would like a neater way of doin...Cell formats, and time difference
some simple questions which I hope someone can help with...
I am using excel on a computer with "danish locale". This means that numbers
are displayed default like 1234,56. How do I change this to a format using a
decimal point (instead of a comma)?
How do I find the difference between to times? I have cells which just have
times (not dates), and I want to find the difference between two times in
hours. For example cells with 08:00 and 09:30 should give a difference of
Excel help gives an example like =TEXT(B2-A2,"h"), but this gives an
Pet...Automating transfer of data in cells
I have a time management spreadsheet with data stored
against work type and date. I need to transfer this data
into a similar but more comprehensive spreadsheet and
wonder whether it is possible to automate this task by
using the work types and dates in a macro (I have almost
10 months of data to transfer), along the lines of check
date, check worktype, where argument is true enter data
from cell. I think I need to use visual basic, but I
can't find out how in the help screens.
Any advice is much appreciated.
This is not difficult providing you keep your data in simple tables...Skip blank cells in diagrams
How do I exclude blank cells in diagrams. If I have an area of data and among
these data some is blank. How do I get excel to not display these data as '0'
but just to skip the cell.
You can include the function NA() in that field and the zero value for the
data won't be displayed.
"hlp" <email@example.com> wrote in message
> How do I exclude blank cells in diagrams. If I have an area of data and
> these data some is blank. How do I get excel to not display these data as
'0...minimum for casual cells but >0
I have big table with vendors, and I have to find the lowest price for each
item, but prices are like:
A B C D E F G H I
Item x 128 175 0 215
the problem is there are also columns with quantities which shouldn't be
I don't want to include columns e.g. B, D, F etc. AND find minimum price but
>0. so I can't use:
MIN(C1,E1,G1,I1...) because I'll get 0 as result.
I'm looking for the simplest solution.
Appreciate for any help.
Ctrl/shift/ente...Save spreadsheet using cell values as filename
I am trying to use a command button to save a spreadsheet using cell values
as the file name from the tab Master. For example I want to use cells B2
(Tony.Dungate) and F2 (2010.March) to create a file name of
Tony.Dungate.2010.March.xls. Essentialy I would need something like :
Can someone advise me on the correct code to do this. I have been trying to
use the following without success:
Const sRoot As String = "V:\Database Logs\"
.SaveAs Filename:=sRoot & _