Using Excel, how do I replace cells containing blanks with nulls?
I plan to use an Excel spreadsheetand cut and paste data into a database. I
need to replace blank cells in the spreadsheet with nulls. How can I
Select the range, press F5, special and Blanks, press enter, now type
whatever you want and press ctrl + enter
> I plan to use an Excel spreadsheetand cut and paste data into a database. I
> need to replace blank cells in the spreadsheet with nulls. How can I
> accomplish this?
If I understand you correctly, Excel doesn't support the concept of the NULL
data ...Sort window for Custom Sort gets bigger each use
Our company recently rolled out Office 2007 Standard. One user uses Excel
quite a lot. She does the Custom Sort action a lot (under the Home
tab>Sort&Filter button). She noticed that the window that pops up actually
increases in size each time she clicks the Custom Sort button. When I try it
on my pc, I can resize the Sort window, and have it remember whatever size I
put it at each time, whether I close and restart Excel, or just doing another
Sort action. For her, it increases exponentially. The process goes like this:
1. Click Custom Sort
2. Click Cancel to close Sort window.
...strip characters out of a field
Is it possible to write a formula to do the following:
and strip out the time to make the field look like
We need to know if this is a text field or a date field.
If it's a text field, it's simply = left(A2,9)
If it's a date field, then = int(a2)
or just format, cells, select "dd-mmm-yy"
will probably work.
"IT" <email@example.com> wrote in message
> Is it possible to write a formula to do the following:
&g...The field 'MyTable.MyField' cannot contain a Null value because the Required property for this field is set to true.
I have a form in my database used for entering new data into a table.
Some of the fields in my table are required, others are not. If I
start entering a new record, using the form, but only fill in some of
the fields, and then click a button, to, for example, open another
form (i.e. I have decided that I do not want to add this new record
now), I will get the following error message:
The field 'MyTable.MyField' cannot contain a Null value because the
Required property for this field is set to true. Enter a value in this
What I want is for the record not to be added unl...Finding transaction with unassigned category
I know this will seem small, but it causes me to be concerned. I have a
current month spending report that has a message at the top of the report
You have 1 transaction, for $0.02, that doesn't have an assigned category.
Assign a category.
When I click on Assign a Category, there are no details in the report that
it generates. I have narrowed it down to have occurred in December 2005, but
I don't know how to find this entry. Can you please help me?
Maybe. Let's find out. Try going into Tools|Find and Replace and doing an
advanced search for amou...How do I delete the line between 2 cells and merge them into one .
Please someone respond I have two cells thatsay the same thing and I can't
figure out how to get rid of the line inbetween them
Not sure what your needs are.
Two cells side by side?
Two cells one above the other?
Two cells far apart?
What is a "line"?
A row or column?
Gord Dibben Excel MVP
On Mon, 11 Apr 2005 10:43:05 -0700, "Eric" <Eric @discussions.microsoft.com>
>Please someone respond I have two cells thatsay the same thing and I can't
>figure out how to get rid of the line inbetween them
Just guessing what you want here:
...accounting format for zero show 0.00 in one cell "-" in another
I have formatted cells with the accounting format with no symbol. Two cells
that compute to zero show differently. One shows 0.00 and the other shows a
"-" dash. I have rechecked all of the cells that are included in the formula
for each and I cannot find any inconsistencies; all are formatted the same.
I realize that this seems petty but for the life of me I cannot determine why
the same formatting shows two different designations for zero. I prefer the
Can anyone suggest what I can look at to try and resolve this?
If the cells contain fo...Is there any way to zero out WSUS db, we are not using it.
SBS 2003 R2
We have not used and we will not use WSUS. Is there any way we can
zero out its huge db file to release space?
Which version are you using R2 WSUS 3.0?
in WSUS 3.0 you can use the clean up tool
But It's been too long since I've even used R2 (WSUS 2.0)
There is the option to uninstall it?
However I think WSUS is great, and if you use WSUS 3.0 I think you will like
Russell Grover - SBITS.Biz [SBS-MVP]
Microsoft Gold Certified Partner
Microsoft Certified Small Business Specialist
24hr SBS Remote Support - http://www.SB...Using SQL server as a backend
I'm currently experiencing problems with concurrent users on access and have
decided to use SQL server as the backend and then Access as the front end.
Can anyone reccomend any sites where I can gather some info on how to get
started with doing this as I have no experience with SQL server.
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"Dale Fye" <dale....Excel validation using multiple conditions
I am faced with a situation where i need some help. I a
having a worksheet which calculates the number of leave accumulated fo
when we choose a particular option for the employee, say "xx" whe
he/she works overtime, right now i have set up a formula to calculat
the number of times this occurs.
I need help to further enhance this functionality. Based on the date o
which the "xx" is entered into a particular employee's row, i need t
create another column which validates the particular date and check
whether more than a month has elapsed from the date...why don't some lib files can use in Unicode mode
why dn't some lib files can use in Unicode mode, though those lib files work
well in Debug mode
i got errors like that
error LNK2001: unresolved external symbol "public: int __thiscall CProtocol
i included some lib files into project in Debug Unicode mode, but it like
that i dn't include it.
This is a very vague question in many ways.
If I write a DLL or static library, and it has functions that take string arguments, and I
want people to be able to use it for both ANSI and Unicode apps, then I have to create
*two* libraries, one library compiled in ANSI mode...Dates between cells in one column and another
Hi can any one help me. I need to find out which dates within one column are
less than 12 weeks apart from dates in another column.
Column A 12/03/2009 14/03/2009
Column B 14/04/2009 16/06/2009
I have two columns with 2000 records in and need to identify these records
Try something like this...
Microsoft Excel MVP
"excelitous" <firstname.lastname@example.org> wrote in message
> Hi can any one help ...passing custom field from lead to account on conversion
We are using CRM 3.0. We have a custom text field in our lead form/entity
which we would like copied to a similar custom field in the account
form/entity when a Lead is converted into an Account, Contact, Opportunity.
Would someone be able to explain how to do this?
You need to add a mapping on the relationship between Lead and the other
objects. This is done through the customization. Open the lead, then got o
the relationships tab, find the one tied to the right objet and edit it.
The mappings are on the relationship.
MVP - Microsoft CRM
"DMcL&q...Using Countif with And
I am trying to count E15:E659 that contains "VM" if the corresponding row
under H is greater than 0.
You can only use COUNTIF with a single condition. Use this instead:
You can't use wildcards here, hence the need for the ISNUMBER/SEARCH
combination. Note also that you can't use full-column references with
SP (unless you have Excel 2007).
Hope this helps.
On Dec 17, 5:37=A0pm, Mike <M...@discussions.microsoft.com> wrote:
> I am trying to count E15:E659 that contains "...Using Outlook to create/read mailing lists
I recently installed Outlook in my office and would like it to do some
automated tasks. Specifically, automated email to my employees based
on a spreadsheet or database. If I have an individuals name on a
spreadsheet, is it possible to link Outlook to that spreadsheet
(let's say in Excel) and create a mailing list? I'm using Outlook
'03 and my employees are already on a list in Outlook.
Any help would be appreciated.
Yes to do this type of automation, you can use the addin
Thanks for the response. Could you be a bit more specific. I'm an
what do yo...Limit fields showing in Advanced Find
Anyone know of a way to cause an unused field to not be shown in the
list of fields you can filter on when doing an advanced find?
Also, CustomerTypeCode shows up on the list when doing a find on
Company but not on Contact, even though it's a valid field on both -
Any idea how to add that back?
As well as i know the fields in list will appear as long as they are at
the attributes of entities. If you remove them from their then you
would not be able to find them in the list.
I just have the same problem with Contact.Customertypecode not available in
Did you re...manipulating data from Find dialog in web browser ActiveX control.
I would like to know how can I get access from MFC to the text that was
entered in the Find dialog of the Microsoft web browser ActiveX control.
Documentation is pointing to IHTMLOptionsHolder interface but I cannot get
reference to it (I am getting E_NOINTARFACE) from any of the available
interfaces in my code. Note, I do not have problem to query other standard
interfaces exposed by ActiveX control (ex.: IHTMLDocument2,
IOleCommandTarget, and IDispatch).
I would appreciate any help regarding this matter.
...I'm unable to paste from one document to another
I can cut and paste fine within a document but cannot copy from one document
and paste into another.
Do you have 2 separate instances of Excel open?
If so, then the Paste options that show will not be the normal list that
you see when you right click.
Open both files in the same instance of Excel and you will be fine.
> I can cut and paste fine within a document but cannot copy from one document
> and paste into another.
Sounds like you have your two workbooks open in separate instances of Excel,
which will not see each o...sumif only seeing one row
Tried to find an answer but haven't found one yet. :(
I have two sheets. On Sheet 1, I have a Demo Code (1, 2, 3, etc.) in A9:A17
and a total quantity column in column W. This is where my sumif would go.
On Sheet 2, I have the Demo Code going across from D5:L5. Below that, I have
quantities listed for each demo code going to different people. The columns
are then totaled below in row 113. I want to show the quantity for each demo
code in the Total Quantity column on Sheet 1.
In my total quantity column on Sheet 1, I have the following formula:
=SUMIF('Sheet 2'!$D...Edit Filter Criteria in Custom View
I have created a custom view in the contacts section. I need to filter out
all companies with the name ABC.
I have 120 ABC branches in the database. If I edit the filter criteria in
this view, MS CRM will only return the first 100 records. Is there a way to
return the extra 20 records or another way to get this data?
Thanks in advance.
look on the bottom right of the results view and you will see the word Page
and an error on the right. Clicking this will display the other 20.
assuming you mean filter in rather than filter out :-)
Microsoft CRM MVP
http://ww...How to find out if public folder replication is complete
This is a multi-part message in MIME format.
I'm replicating public folders from one server to another and plan to =
shut down original server after it's done.
My questions are
1. How do I know that all public folders are replicated? Any tool for =
that except for clicking on each folder individually and verifiing it?
2. How do I test this on client machines? Do I shut down original =
server? Or dismount public store? Or stop "mess...pasting excel charts into a ppt template using vba code
I have a process i am trying to automate using VBA. I have a workbook
in excel with numerous worksheets. What I need to do is paste them into
a template powerpoint presentation. There is one blank slide in the
template, so i would also need to write in vba for a new slide to be
created for each worksheet. another tricky thing is that 2 worksheets
correspond to 1 slide. So worksheets with an 'a' extension in the name
go on the left, worksheets with a 'b' extension go on the left. i am a
little new at using vba across application, so any advice would be
appreciated. T...How do you automatically record a revision date
Whenever a worksheet is revised I need to automatically record a new revision date. The next time the worksheet is edited a new date automatically replaces the old date.
Thanks for the help,
And that's a bad thing?? I don't get the picture. Ideally
what you want is a column of revision dates?? It seems
that what you want to do is to show the most recent
revision date if it's recorded in only one place. What
good is an old revision date. As you can see I just don't
>Whenever a worksheet is revised I need to a...How can I maintain the web page formatting when using autorepulish feature
I have a dynamically updated Excel spreadsheet that is
included in a PowerPoint presentation. To try to make the
spreadsheet "look like" the page formatting of the
PowerPoint (colors, titles, etc.) I used the "publish as
web page" option for saving the spreadsheet. I can then
go in and edit the web page to use the colors, background
image, titles, fonts, etc. to match the PowerPoint slides
but as soon as I update the spreadsheet and republish
(autorepublish) it, I loose all of my page formatting.
It seems like there should be a "target" link on the web
...Okay, I can't find this anywhere else
I have a form to enter data for an invoice, how do I tell the form/query to
automatically list every item in the table so that values can be entered.
From what I can tell of the query, a zero value must be entered for the item
to show up on the report properly. Is this possible? Can I have the items
Lori A. Pong
On Thu, 9 Aug 2007 12:48:10 -0700, Lori <email@example.com> wrote:
>I have a form to enter data for an invoice, how do I tell the form/query to
>automatically list every item in the table so that values can be entered.