I need to convert name 3 part name into Last, First MiddleNameorMI
I have a column of names, most of which are FirstName LastName in format.
Some, however are First MiddleName (or MI) Last. I need to convert these
values into LastName Firstname MI. I have a formula that gets close, but
doesn't account for the possible presence of a Middle Name or initial.
For ex, data could be Jill Smith, or Jo Ann Smith. I need to make Smith,
Jill and Smith, Jo Ann.
Seems like I need to go backwards thru the string with a Search function to
find the 2nd space for this to work, but can't. Or should I try it by parsing
consecutive columns with incremental chan...Excel crashes when entering numbers and formulas into cells
Operating System: Mac OS X 10.6 (Snow Leopard)
This only happens with one file...I have tried switching users, bringing it over to Sun Office and then bringing it back, bringing it to 2004 and back, reinstalling 2008, etc. It works fine with Windows 2003. <br><br>I have also copied and moved tabs to a new spreadsheet and the problem still occurs after a few hours of productivity. <br><br>I think the file is corrupted as other spreadsheets work fine. Any thoughts on how to fix? <br><br>Thanks much! Andy <br><br&...How to assign a value as a Named Area
I would like to write a VBA to make some copies automatically. The range is
to be selected based on a cell's value.
For example :
if A1 = "General Admin" Then print area named "GA"
if A1 = "Sales Admin" Then print area named "SA"
My VBA is :
Dim x As String
If ActiveCell.Value = "General Management" Then
x = "GA"
ElseIf ActiveCell.Value = "Sales Admin." Then
x = "SA"
ElseIf ActiveCell.Value = "Beijing" Then
x = "BJ"
ElseIf ActiveCel...Replace Cell Address in A Long Formula
From time to time, I need to do some change in some very long formula. For example:
=TRIM(IF(ISERROR(FIND(" ",B46,FIND(" ",B46,1)+2)),MID(B46,FIND(" ",B46,1)+1,LEN(B46)),MID(B46,FIND(" ",B46,FIND(" ",B46)+2),LEN(B46))))
I want to replace all the "B46" with "F18". Is there a easier way? Thanks.
One quick and dirty way is to Edit>Replace B46 with F18.
"plumstone" <email@example.com> skrev i meddelandet
> From...worksheets gone
I've been doing some mail merge, and suddenly with no apparent reason my
Excel worksheets are not showing up. When I click on Excel icon, one part
of a sheet will come up (like beginning w/ D89). The other two worksheet
tabs don't show up at all on the bottom like usual. Please help me asap. I
am dying here.
...text in cell looks different when printed
text in cell looks ok on screen. when printed, text is
moved around, out of alignment from the way the cell
looked when viewing the screen.
When keying in information into a cell, how do you get
the text to skip a line? when I press enter key, the
pointer moves to the next cell.
I would check your printer driver for your first problem. How does it look
on Print Preview?
For your second question, use Alt Enter and this will insert a carriage
return into the cell.
"thomas brown" <firstname.lastname@example.org> wrote in message
news:35c101c42944$...Dynamic Series Name
I have a table that is set up using "Data-Filter-AutoFilter" so that the user
can click a drop down arrow in the header for column 1 (the x-values), select
a value, and the table displays only those records.
A chart is connected to the table.
In the chart I want the series name to be the user's choice of the dropdown
value. For example, say the x-value choices are "apples", "oranges", or
"lemons". If the user chooses "oranges", then that becomes the series name in
Can someone come up with a way to do this. I have thus far...Formula to lookup named reference based on value
I cant' figure out what the exact words that I'm looking for and if I
did I would most likely find the answer.
I've got a spreadsheet that has a series of numbers in say column A
and it has the numbers 1,2,3,4,5
Further on in the spreadsheet I have named values like CA.1, CA.2, CA.
What I want to do is in the formula go:
=(X1/SUM(X:X,X,X:X)*CA.2) (for the named range CA.2)
Is it possible to have something that goes....
=(X1/SUM(X:X,X,X:X)*"CA"&B4) where B4 = 2 Thus the outcome would be
Kind...How do I expand the number of characters allowed in an Excel cell.
The default for maximum number of characters in a single Excel cell is 1024.
If you enter more than that all you see is astricks (********). If you
select the cell where the astericks show, you can read the information in the
formula bar above. However, it does not print out well.
I am importing web data from a Web Based Application into Excel and require
a column for comments which normally falls below the default maximum. On
those occassions it does not, it is a pain.
I have run into this problem for quite a while and have finally decided to
put it out there for the more advan...Returning every 7th row of a column
I have a row of 1836 consecutive dates in a column and I want a new column
with only every 7th row's date (the date beginning each week). How do I get
Excel to do this?
Assuming the date are in Col A try this formula dragged down
Now this assumes the data start in row 1. If it's another row then adjust
the last 1 in the formula to that row
When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
"bondjel" wr...Run a Hyperlink from another cell
I have a master spreadsheet and a cutdown spreadsheet. I have a hyperlink to
a file in the master spreadsheet which I want to link to on the cutdown
At the moment if I click on the hyperlink on the cutdown spreadsheet it
opens the master and goes to the hyperlink cell reference. I want to open
the file from the original hyperlink when I click on the cell in the cutdown
I hope somebody can help.
...4000 defined names
My excel sheet shows some 4000 defined names which i never created.
Though I can easily delete these unwanted names using the utitily
available at http://www.bmsltd.ie/mvp/, I would like to know, if
someone as any idea, as to how these names got created in the first
These names refer to various servers and computers. This file did
travel to various computers but for sure no name was defined on those
Thanks in advance for your help
Did you possibly convert the workbook to web pages.
David McRitchie, Microsoft MVP - Excel [site changed ...create a rewrite rule for the domain name?
I've a scenario where I receive an email to a distribution list on my
exchange 2003 server from a paging service - skytel.com - that is
distributed to local support reps as well as needs to go back out to another
page at the same skytel.com paging service.
skytel.com sees the message coming from skytel.com and doesnt accept it.
Is there a way for me to set up a scenario where I change who the orginal
message is coming from or change the domainname when sending back out to
skytel.com so it will accept it?
I looked at trying to set up a rule on a mailbox but they all forwar...Insert a space in all blank cells of the first column
For some specific reasons I would like to enter a space in all blank
cells of the first column of my spreadsheet called sheet 1.
How is this done by using VBA?
Help is much appreciated. Thank you very much in advance. Regards,
First, this is usually a mistake to do.
It'll mess up formulas like:
=if(a1="","it looks empty","it doesn't look empty")
If you have other formulas that use these empty cells--like:
and you're seeing 0's where you don't want them, try modifying your formul...How to reverse the column name and row name?
In the original setting of excel, the column name starts from "A", "B", "C",
.....so on and the row name starts from "1", "2", "3".... so on. Is it
possible to change the setting to the column name starts from "1", "2", "3"
and the row name starts from "A", "B", "C" ?
Many thanks in advance !
The simple answer is no.
You can change column headings to numbers by adopting the R1C1 notation
Tools>Options>General and select the R1C1 style.
You could use Row 1 to displ...Public Constant not visible in worksheet?
Office / Excel 2003 SP3
XP Pro SP3
I'm writing some code that I'd like to be available to every worksheet
in the workbook, and for a variety of reasons I'm trying to avoid using
PERSONAL.XLS and/or addins. [The object of the exercise is to allow any
user within the organization to be able to utilize the macros in this
workbook as long as (s)he has a standard Office installation.]
I've written sub procedures in a standard module in the workbook /
project that are called from a Worksheet_Changed event .... the code
works as expected.
My problem is when I attempted to rep...Conditional formating an entire row vs. only one cell
I have 400 rows of data. There are four columns that contain any one of five
different key letters (R, NR, O, U, NA). What I want to do is that everytime
a letter such as NR shows up in any of the four columns, the entire row has a
conditional format applied. When I do "Cell Is", the conditional format is
only applied to the one cell that meets the condition even if I have the
entire row(s) highlighted when I create the conditional format. I tried to
find similar posts to help but have not been successful. Using Excel 2003.
Highlight all your data (assuming you have start...how to skip the blank cells
i copied some cells including blank, but after checking the skip blank
button when i try to paste the blank cells are also copied.
Select the copied cells including the blank...
....Select Edit then Go to (ctrl G)
Click the special botton...select BLANKS click OK
the performed delete command
>i copied some cells including blank, but after checking
the skip blank
>button when i try to paste the blank cells are also
...Hide rows if cells = 0.00
I am trying to hide rows if any cell in a columm = 0.00. Example: If any cell
in column D = 0.00 than hide the row containing that cell.
Highlight your data, then from the menu bar select Data > Filter >
Autofilter. Select the drop-down in column D and choose Custom. Use the
drop-downs to set your selection to read 'does not equal' '0.00', then click
OK. Those rows with 0.00 in column D should now be filtered out (they're
still in your data, just not visible).
> I am trying to hide rows if any cell in a columm = 0.00. Exa...Protect a worksheet
I have worksheet protection setup on a excel worksheet.
I want to know if there is a way to prevent users from highlighting the page
and creating their own worksheet so they can make changes.
As of right now, the worksheet prevents any changes but people have been
highlighting, copying and pasting to a new worksheet so they can make
Is there a way to prevent this?
"Jaz" <email@example.com> wrote in message
>I have worksheet protection setup on a excel worksheet.
> I want to know if there i...Counting cells basesd on multiple criteria
Ok so i have two sheets
In SHEET 1 column A i have a list of about 1000 different alarms.
Then In SHEET 2 i have a log for 30 days with the alarms that occured in
those thirty days. The log takes up 35465 thousand rows. Column D has the
name of the alarm and Column C has if the alarm was an IN or OUT alarm.
What i want to do is in SHEET 1 column 2 display how many times each of
these 1000 alarms occured in the last 30 days but only when it was an IN
I cant use something that refrences the name of the alarm i need a formula
that can actually refrence the cell because it would be ...Sending an entire worksheet via a hyperlink / button
I have a worksheet with many drop down menus, etc, and i want people to
be able to fill it in, and then send it on ..... but you know how lazy
people are .... if they have to save it, then send it, they will gripe
Is there a way to just have a hyperlink style button on the sheet that
once the guys have filled it out, it just sends the sheet to me?
Suggest looking at Ron de Bruin's website
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org..._T() return LPCTSTR?
_T("Mutex") return LPCTSTR?
Yes, string literals will be const.
Nish [VC++ MVP]
"kathy" <firstname.lastname@example.org> wrote in message
> _T("Mutex") return LPCTSTR?
"kathy" <email@example.com> wrote in message
> _T("Mutex") return LPCTSTR?
"Mutex" is an array of 6 chars (the final one being '\0' )
L"Mutex" is an array of 6 wchar_t 's
In a unic...sum value of nonblank discontinuous cells
I'm trying to figure out in what way I can sum up the currency values of
select cells in a row based upon alternate select cells having a value that
is not blank. I'm trying to create a worksheet that will allow our group to
order items and provide totals. So, if they do not enter anything into that
particular column the adjacent one will stay blank. However, if they enter a
quantity in column a then column b will provide a total. I've gotten that
far. What I want to do now is to read those specific quantity columns and if
they have a number (isnumber) then to add those...Biztalk 2002, eConnet 8.0 how to return the Error number/string using the eConnect Wrapper?
Does anyone use orchestration designer with the eConnect wrapper for version
8.0? if so how do you return the error number/string in the form of an
email? Currently I have wrote a lot of functions in the pre/post routines
to help me out.