MS Excel:Protecting Cells

When a cell contains words or a formula that you don't want deleted by accident, use the protection feature. To protect cells, select the cells to protect. Right click, and choose Format Cells. Click the Protection tab and click Locked. Tools/Protection and choose Protect Worksheet. Now, if you try to change anything about that cell (formatting or the contents), you will get a message that the cell is protected. If you do need to change the cell, go to Tools/Protection and choose Unprotect Worksheet. After changes, remember to protect the worksheet again!

MS Excel:Prevent Duplicate entries within a range of cells

Suppose you want to prevent duplicate entries in the range of cells A1:A10:

Select cell A1.
Choose "Validation" from "Data" menu.
Choose "Settings" tab.
Choose "Custom" from "Allow" drop-down list.
In the Formula box, enter the following =NOT(OR(COUNTIF($A$1:$A$10,A1)>1))
Choose the "Error Alert" tab in order to set an alert style.
Set Style to Stop (the alert message with a Stop sign on it).
In the Title box, enter what you want to appear in the Error Alert's title bar (for example, "Duplicated Entry"); in the Error Message box, enter the message box text ("Please type an entry not entered previously," for example).
Choose "OK".
Back in the worksheet, select cell A1;
then, using the square AutoFill handle, drag to fill cells A2:A10.

MS Excel:Separating first name and last name

Cell A1 contains a name - Sameer Lal

The formula for extracting the first name is =LEFT(A1,FIND(" ",A1))

The formula for extracting the last name is =MID(A1,FIND(" ",A1)+1,LEN(A1))

This tip can be used for many other purposes and comes out very handy.

MS Excel:Setting Non-scrolling Rows and/or Columns

Position your cell pointer to indicate rows and/or columns to lock. When you give the command, rows above your cell pointer and columns to the left of your cell pointer will be frozen or set aside as separate scrolling areas.

Position your cell pointer to define the rows and columns to freeze.

Go to the Window menu and choose Split or Freeze Panes.

Return to the Window menu to Remove Split or Unfreeze Panes.

Split gives you areas that you can scroll within.If you've chosen Freeze Panes your cell pointer will move out of the frozen pane area if you attempt to move beyond it.

MS Excel:Displaying Cell Formulas Rather Than Results

To change Excel cells display for formulas to display the actual formula rather than the formula results.

Shortcout:
Keyboard shortcut Press Ctrl and ~(Tilde sign)

From Menu:
Tools Options View tab Window Options section, check Formulas.

MS Excel:Insert and Auto Sum

Insert an AutoSum Alt = (equal sign)

Excel will try to guess what cells you want to total up, either the cells immediately above or to the left - but you can change the range of cells to whatever you need.

MS Excel:Check For Numbers Stored In a Text Format

Excel can't perform calculations on cell values formatted as text, such as number values that you've imported as text from other databases into Excel. Excel 2002 includes a feature that alerts you to these cells and optionally converts them to numbers. To activate this feature, follow these steps:

1. Go to Tools Options.
2. On the Error Checking tab, select the Enable Background Error Checking and Number Stored As Text check boxes, and click OK.
Now, when Excel finds a number in a cell formatted as text, it will place a green error indicator in the upper left corner of the cell. To convert cells to a number format, select the cell, click the Error Checking Options button displayed to the left of it, and select Convert To Number.

MS Excel:Turn the Auto calculation- ON and OFF

You may temporarily want to turn off the automatic calculation option while working with large worksheets in Microsoft Excel. This allows you to enter all the formulas without having to wait for a recalculation. To do this:

• Open the Tools menu and click Options.
• In the Options dialog box, click the Calculations tab.
• Then, click on the radio button labeled Manual, and click OK to close the dialog box.
• If you want to check a calculation while you are working, simply press F9.
  

When you finish a large worksheet, don’t forget to turn automatic calculation back on.

MS Excel: Data Selection

Move entire columns: Highlight the column letters. Hold down the Shift key. Drag.

Move entire rows: Highlight the row numbers. Hold down the Shift key. Drag.

Data selection of multiple areas: Press the Control key while using the mouse to select multiple cells, data ranges, columns, rows, or worksheet tabs.

Modify the area of a selected range: If you use the mouse to highlight a range of too many or too few cells, use the Shift and Arrow keys to modify your selection.

To select a single column: Put the cursor in the column then control-spacebar, or click on the column letter.

To select a single row: Put the cursor in the row then shift-spacebar, or click on the number.

To select the entire worksheet: Control A or click on the blank cell in upper left corner.

To select a range: Click the cell in the upper left corner. Hold down the shift key. Click the opposite corner.

To select multiple ranges: Press Control then repeat the above procedure in another area.

To select all worksheets: Right click on any tab and choose Select All Sheets.

MS Excel: Which formula to use

As you can see the same result is derived from all the three formulas shown below, it is for you to decide which one you feel most comfortable with.
The sumproduct has it's own limitation of being used to return only number but for numbers, I find it most suitable.
Before writing formulas, always break it parts, see the results of that part and then merge parts together.

MS Access(or MS Excel):Mathematical order of operations

5 + 3 * 4 = ?
Answer is 17 or 32
It is 17.
Remember this order:
B E D M A S
B = ( ) Brackets
E = 2raise to power 3 Exponents=8
D = / Division
M = * Multiplication
A = + Addition
S = - Subtraction

MS Excel:INDEX and MATCH to Look up Data

Create the following table with information in cells B3:D7 and this table contains salary information in cells D3:D7:

Say, you want to look up the salary of a person by using the person's name. To do this, use a combination of the INDEX and MATCH functions as under:

=INDEX($B$3:$D$7, MATCH("Lal",$B$3:$B$7,),3)

This formula uses cells B3:D7 and looks up Lal's salary in the third column. The formula returns 4000.

MS Excel: Magic Sumproduct Formula

You can count or sum a particular column based on any number of criteria from other columns without ever bothering to use array function.
Here in this example, I have used both, Sumproduct and Array formula Sum to show the results from both.
Essentially, in A8 and A13 , we are finding out sum of quanity which are greater than 20 with "Sam" in Area "a" and in A10 and A15 , we are finding out count of records of quanity which are greater than 20 with "Sam" in Area "a" both by SumProduct formula and array formula of Sum(where you have to enter Ctrl+Shift+Enter to enter the formula):


You can perform many calculations using this magic Sumproduct formula without ever bothering to go for array formula.

MS Excel: Entering a line break within a cell

Just a quick note, If you want to enter a line break within a cell, type in the first line then
Press ALT+ENTER
Type in the second line. Do the same again if you have more lines to enter.
Press ENTER when you are done typing.

MS Excel: Find out overdue days

The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. This function makes it very easy to calculate overdue days.
To try this:
In a worksheet, type the due date in cell A1, using slashes to separate day, month, and year.

In cell A2, type =DATEDIF(A1,TODAY(),"d")
and press ENTER.

The overdue period (in days) will be displayed in cell A2.You can replace "d" with "m" or "y" to find out period in months or years.Same way, you can replace Today() with a date field to find difference between those two fields like, invoice date and payment date.

MS Excel: Record Counting Based on Multiple conditions

Use of Array formula to count the number of records in excel that meet a set of conditions.Remeber, that writing array formula is same as any other formula except that you press Ctrl+Shift+Enter.
For example:
The worksheet carries three columns, A, B and C.A is for name of student, B for marks and C for gender(M/F).
Your sheet looks like
A ------B -----C
Mary -- 60 -- F
Sam -- 70 -- M
Tim -- 65 -- M
Rudy -- 80 -- F
Jack -- 75 -- M
For this, you want to see Number of Males getting more than 70.

The formula will be(Write this in the cell you want the result):

=SUM(IF($B$2:$B$6> =70, IF($C$2:$C$6="M",1,0)))
Press CTRL+SHIFT+ ENTER.

The result in this case is 2.

DateTime Format and .NET

The FormatDateTime() function is a powerful functions and is very easy to use.
The format function is:

FormatDateTime(date, format)

You could use:

Format DateTime(Now,”M/dd/yyyy”)

Or any other similar formatting argument.But Microsoft provides lot of predefined VBScript functions designed to reduce coding time:

When specifying the format argument, you can either type the Visual Basic constant name (name in left column), or the constant's corresponding value (0 - 4, from the middle column). They do the same thing, it's just less typing if you use the value.

I find using numbers very fast and useful. Like;
Format DateTime(Now,2)

Server Variables and .NET

The server variables collection can be very helpful when you are developing applications in .NET. The proper syntax to refer to a server variable in your code is Request.ServerVariables("VariableName") such as to:( Don't forget to declare variable such as; Dim IP as String and then:)

• Retrieves the user IP Address

IP = Request.ServerVariables ("REMOTE_ADDR")

• Retrieves the remote host IP Address

dnsIP = Request.ServerVariables("remote_host")

• Retrieves the page domain name

serverName = Request.ServerVariables("server_name")

• Retrieves the referer url

referer = request.servervariables("http_referer")

• Gets the browser type

agent = Request.ServerVariables("http_user_agent")

• Retrieves page url

Url=request.servervariables("URL")

• Retrieves request mehtod .. get or post

method=request.servervariables("Request_Method")

• Retrieves server port

serverPort=request.servervariables("server_port")

• Retrieves current page

scriptName=request.servervariables("script_name")

• Retrieves server software

serverSoftware=request.servervariables("server_software")

Consult the Microsoft Documentation for a full description of all the Server Variables.This handy list is a for commonly used server variables including referred above:

• ALL_HTTP
• AUTH_TYPE
• CONTENT_LENGTH
• CONTENT_TYPE
• GATEWAY_INTERFACE
• HTTP_ACCEPT
• HTTP_ACCEPT_LANGUAGE
• HTTP_CONNECTION
• HTTP_USER_AGENT
• HTTP_COOKIE
• HTTP_HOST
• HTTP_COOKIE
• HTTP_REFERER
• LOGON_ACCEPT_ENCODING
• PATH_INFO
• PATH_TRANSLATED
• QUERY_STRING
• REMOTE_ADDR
• REMOTE_IDENT
• REMOTE_HOST
• REQUEST_METHOD
• SCRIPT_MAP
• SCRIPT_NAME
• SERVER_NAME
• SERVER_PORT
• SERVER_PORT_SECURE
• SERVER_PROTOCOL
• SERVER_SOFTWARE
• URL

Various Articles on .NET

Just thought to list my articles published on .NET, may be, you would find it useful for reference:

Using DateTime Picker With Null Support with MS Access and ASP.Net


Selecting, Confirming and Deleting Multiple DataGrid items in ASP.NET


CodeSnip: The Basics of MS Access with ASP.NET


CodeSnip: Bidirectional Sorting of a DataGrid in VB.NET

.NET and MS Access Query

I was developing a project in .NET with MS Access as backend database where a report was generated to show if the shipment was done within 24 hrs of order,between 24 and 48 hrs and more than 48 hrs. Usually, stores operations remain closed on weekends and thus all orders received over weekend were shipped on Monday.To give effect of weekend closure in the report, I wrote a query in MS Access:

SELECT tblshipment.*, IIf(([D1]=2 And [D2]=6),((DateDiff("h",[orderdate],[shipmentdate]))-48),IIf(([D1]=2 And [D2]=7),((DateDiff("h",[orderdate],[shipmentdate]))-24),DateDiff("h",[orderdate],[shipmentdate]))) AS DD, Format([shipmentdate],"w") AS D1, Format([orderdate],"w") AS D2, DateDiff("h",[orderdate],[shipmentdate]) AS DDxFROM tblshipment;

By this, I am first finding out hours difference in order date and shipment date and then, looking for, if order date was Friday and shipment date is Monday, then reduce 48 hrs from time difference or if order date was Saturday and shipment date is Monday, then reduce 24 hrs from time difference, to give effect for weekend closure.

MS Excel: Finding unique entries in a column

Today, I came accross a situation where one column in MS Excel had account numbers with other column showing amount charged.Certain account numbers were charged for number of times during the period and we wanted to find out, how many accounts were charged during this period as number.This was part of bigger formula but finding the number of unique account was accomplished by creating a column and writing the following formula:
Assume Account Numbers are in column A and we created column F for this formula:

=IF(COUNTIF($A$1:A2,A2)>1,0,COUNTIF($A$1:A2,A2)) and copying it till the last account number row.

This gave value 1 for each unique account and 0 for repeatition.Just addition of this column provided me by number of unique accounts.