Tech Notes Exchange

DATEDIF function

2011-11-21T19:24:00.002-05:00

The DATEDIF function can be used to calculate the number of days/moths/years between two dates in versions of Excel from 97 to 2007.

=DATEDIF(B9,C9,"m")/12

=DATEDIF(B9,TODAY(),"d")

=DATEDIF(B9,C9,"m")

It is interesting to note that DATEDIF is not listed with other functions under the formula tab in Excel 2007.

Folder and File Option : Missing Security Tab: Windows XP

2009-05-28T13:43:00.001-04:00

Problem: Right clicking on folder or file not showing security tab:

Go to Explorer -> tools -> folder options -> View->

navigate to the last check box in the list “USE SIMPLE FILE SHARING” .

Uncheck this checkbox and click OK.

Now you will be able to see the security tab in file properties.

Allow only numeric value in a textbox on a form

2007-04-13T11:57:00.000-04:00

Remove //** From the html codes:

This is the function in the header tag

function AllowNumeric(e) {
var iKeyCode = 0;
if (window.event)
iKeyCode = window.event.keyCode
else if (e)
iKeyCode = e.which;
if (iKeyCode > 47 && iKeyCode < 58)
return true
else
return false;

Here is the HTML for the textbox

//////(<)//**
INPUT onkeypress="javascript: return AllowNumeric(event);" id="txtAcctNo" style="WIDTH: 120px; HEIGHT: 20px"
type="text" maxLength="8" runat="server" size="14"
//**>///////

The VLOOKUP Function:Very useful and very simple

2006-08-16T13:01:00.000-04:00

The most useful and least used in common practice due to lack of knowledge is the VLOOKUP function of MS Excel.
Here I am presenting with the help of one example the common use of VLOOKUP function.
Let us first see the function:
With the VLOOKUP function, ypu can look for specified data in the first column of a table. The result is returned from the column number specified, if found.The formula for VLOOKUP is:

=vlookup(lookup_value,table_array,col_index_num,range_lookup)

Let us look at the example, I want to see if value in column A3 to A6 of table A exist in Table B.If yes, I want formula to return me column E's value.
The formula is:

=+VLOOKUP(A3,$E$3:$F$6,1,FALSE)

Now, here I am looking for A3(lookup_value) of table A in E3 to F6(table_array), remember: it will look into left most column,so in E3 to E6,and return me value from column 1(col_index_num) which is E3 to E6.Then comes the range_lookup which is False.The Reason for it being False is,the use of False as the optional range_lookup Argument. This directs to the VLOOKUP to find an exact match and is most often needed when looking for a text match. If this is omitted, or True, you will often get unwanted results when searching for text that is in an unsorted column of data. When True is used, or the range_lookup Argument is ignored, the data should be sorted (by the first column) in ascending order.

See the results in column A8 to A11.

Now, if I want to return the value from column F of Table B, all I have to do is to change column index to 2.

=+VLOOKUP(A3,$E$3:$F$6,2,FALSE)

See the results in column D8 to D11.

Whenever, match is not found, the formula will retorn an error i.e. #N/A.To avoid this, i write another formula:

=IF(ISERROR(+VLOOKUP(A3,$E$3:$F$6,1,FALSE))=TRUE,0,(+VLOOKUP(A3,$E$3:$F$6,1,FALSE)))

Meaning that if vlookup returns error i.e. #N/A, then return 0 else return vlookup value.
That is all to it.Hope you find it useful.

Export Custom Outlook Form Data to MS Access

2005-10-28T11:28:00.000-04:00

Dim appAccess
Dim nms
Dim strFolder
Dim fld
Dim strAccessPath
Dim rst
Dim dbe
Dim wks
Dim dbs
Dim itms
Dim itm

Sub CommandButton1_Click()

Set nms = Application.GetNamespace("MAPI")
strFolder = "fbtest folder"
Set fld = nms.Folders("Personal Folders").Folders(strFolder)

'Pick up path to Access database directory from Access SysCmd function
Set appAccess = CreateObject("Access.Application")
strAccessPath = appAccess.SysCmd(9)

'Get DAO version from DBEngine
strDBEngine = appAccess.Application.DBEngine.Version

'MsgBox "DBEngine version: " & strDBEngine

appAccess.Quit

If strDBEngine = "3.51" Then
'Office 97 DAO version
Set dbe = CreateObject("DAO.DBEngine.35")
strDBName = strAccessPath & "fbtestdb.mdb"
ElseIf strDBEngine = "3.6" Then
'Office 2000 DAO version
Set dbe = CreateObject("DAO.DBEngine.36")
strDBName = strAccessPath & "fbtestdb.mdb"

Else

MsgBox "Unknown Office version; canceling"

Exit Sub

End If
'MsgBox "DBName: " & strDBName
Set wks = dbe.Workspaces(0)
Set dbs = wks.OpenDatabase("S:\sameer\fbtestdb.mdb")

'Open Access table containing contact data
Set rst = dbs.OpenRecordset("fbtesttable")
'Set up reference to Outlook folder of items to export

Set itms = fld.Items
ItemCount = itms.Count
If ItemCount = 0 Then
MsgBox "No FB requests to export"

Exit Sub
Else

MsgBox ItemCount & " FB requests to export"

End If
'Set up reference to Outlook folder of items to export
Set itms = fld.Items
ItemCount = itms.Count

If ItemCount = 0 Then

MsgBox "No Fund Builder requests to export"

Exit Sub
Else

MsgBox ItemCount & " FB requests to export"

End If
For Each itm In itms

rst.AddNew

'Custom Outlook properties
rst.Request=itm.userproperties("001 Request")
rst.AccountNumber=itm.userproperties("002 Account Number") rst.ClientName=itm.userproperties("002 Client Name")

rst.Update

Next

rst.Close

MsgBox "All FB requests exported!"

End Sub

Export Data From Outlook Custom Form to Excel

2005-10-28T11:16:00.000-04:00

Sub CommandButton1_Click()
ExportToExcel()
End Sub

Sub ExportToExcel()

Dim appExcel
Dim olMAPI
Dim strTemplatePath
Dim strSheet
Dim Ifld
Dim MItem

Set olMAPI = GetObject("", "Outlook.Application").GetNameSpace("MAPI")
Set Ifld = olMAPI.Folders("Personal Folders").Folders("SamTest")

i=1

'Pick up Template path from the word options dialog
strTemplatePath="H:\"

'Debug.Print "Document folder: " & strTemplatePath

strSheet="SameerTest.xls"
strSheet=strTemplatePath & strSheet

'Set appExcel= New Excel.Application
Set appExcel=GetObject("", "Excel.Application")

appExcel.Workbooks.Open (strSheet)
Set wkb = appExcel.ActiveWorkBook
Set wks =wkb.Sheets(1)
wks.Activate
wks.Cells(1, 1)="Subject"
wks.Cells(1,2)="ClientName"
wks.Cells(1,3)="ClientAddress"
wks.Cells(1,4)="ClientAge"

appExcel.Application.Visible=TrueFor Each MItem In Ifld.Items

If Left(MItem.Subject, 12) = "Client Form" Then
i = i + 1

If MItem.Subject<>"" Then
wks.Cells(i,1).Value = MItem.Subject
End If

If MItem.UserProperties("010 ClientName").Value<>"" Then
wks.Cells(i,2).Value = MItem.UserProperties("010 ClientName").Value
End If

If MItem.UserProperties("020 ClientFirstName").Value<>"" Then
wks.Cells(i,3).Value = MItem.UserProperties("020 ClientAddress").Value
End If

If MItem.UserProperties("030 ClientInitial").Value<>"" Then
wks.Cells(i,4).Value = MItem.UserProperties("030 ClientAge").Value
End If End If

Next

Set MItem = Nothing
Set Ifld = Nothing
Set strTemplatePath = Nothing
Set strSheet = Nothing
Set olMAPI = Nothing
Set appExcel = Nothing

End Sub

Counting Commas or Special characters in a cell

2005-09-07T09:42:00.000-04:00

Suppose, your names seperated by "," is in cell A4.

Here is the formula to calculate number of commas in the cell A4:

=LEN(A4)-LEN(SUBSTITUTE(A4,",",""))

The first part: LEN(A4) calculates length of string with commas.

The Second part: LEN(SUBSTITUTE(A4,",","")) calculates length of string without commas.

The differnece of the two i.e. first part-second part will give you number of commas in your string.

This formula can come out handy in many calculations.

VB 6.0-Some Formatting Expression

2005-08-22T10:44:00.000-04:00

I find it very handy:

Formatting numbers with named formats Expression Result
Format(35988.3708, "general number") 35988.3708
Format(35988.3708, "currency") $35,988.37
Format(-35988.3708, "currency") ($35,988.37)
Format(35988.3708, "fixed") 35988.37
Format(1, "fixed") 1.00
Format(35988.3708, "standard") 35,988.37
Format(1, "standard") 1.00
Format(0.35988, "percent") 35.99%
Format(0, "Yes/No") No
Format(0.35988, "Yes/No") Yes
Format(0, "True/False") False
Format(342, "True/False") True
Format(0, "On/Off") Off
Format(-1, "On/Off") On

Formatting numbers with special characters Expression Result
Format(35988.3708, "00000.0") 35988.4
Format(35988.3708, "0000000.0") 0035988.4
Format(35988.3708, "00,000.00000") 35,988.37080)
Format(6.07, "0.###") 6.07
Format(6.07, "0.000##") 6.070
Format(143879, "#,###,###.00") 143,879.00

Formatting numbers with embedded characters Expression Result
Format(45, "\[00\]") [45]
Format(642, "\£000.00") £642.00
Format(99, "00\¢") 99¢
Format(8, "#0\).") 8).

Formatting dates and time

Format(36715.5784, "general date") 7/8/00 1:52:54 PM
Format(36715.5784, "short date") 7/8/00
Format(36715.5784, "medium date") 08-Jul-00
Format(36715.5784, "long date") Saturday, July 08, 2000
Format(36715.5784, "short time") 13:52
Format(36715.5784, "medium time") 01:52 PM
Format(36715.5784, "long time") 1:52:54 PM
Format(36715.5784, "c") 7/8/00 1:52:54 PM
Format(36715.5784, "d") 8
Format(36715.5784, "dd") 08
Format(36715.5784, "ddd") Sat
Format(36715.5784, "dddd") Saturday
Format(36715.5784, "ddddd") 7/8/00
Format(36715.5784, "dddddd") Saturday, July 08, 2000
Format(36715.5784, "w") 7
Format(36715.5784, "ww") 28
Format(36715.5784, "m") 7
Format(36715.5784, "mm") 07
Format(36715.5784, "mmm") Jul
Format(36715.5784, "mmmm") July
Format(36715.5784, "q") 3
Format(36715.5784, "y") 190
Format(36715.5784, "yy") 00
Format(36715.5784, "yyyy") 2000
Format(36715.5784, "h") 13
Format(36715.5784, "hh") 13
Format(36715.5784, "n") 52
Format(36715.5784, "nn") 52
Format(36715.5784, "s") 54
Format(36715.5784, "ss") 54
Format(36715.5784, "ttttt") 1:52:54 PM
Format(36715.5784, "AM/PM") PM
Format(36715.5784, "am/pm") pm
Format(36715.5784, "A/P") P
Format(36715.5784, "a/p") p
Format(36715.5784, "AMPM") PM

Format "w" returns day of week (1 = Sunday, 7 = Saturday)
Format "ww" returns week of year (1-53)
Format "y" returns day of year (1-366)
Format "h" returns hour of day as one or two digits...if necessary
Format "hh" returns hour of day as two digits...definitely
Above applies to "n"/"nn", and "s"/"ss" as well
Format "AMPM" uses settings from WIN.INI [intl] s1159=AM, s2359=PM
Try mixing and matching the format strings Expression Result
Format(36715.5784, "m-d-yy") 7-8-00
Format(36715.5784, "d-mmmm-y") 8-July-00
Format(36715.5784, "mmmm yyyy") July 2000
Format(36715.5784, "hh:mm a/p") 01:52 p
Format(36715.5784, "m/d/yy h:mm") 7/8/00 13:52

FormatDateTime
This new function works about the same as the regular Format function, but you're only allowed to
use one of 5 constants -
vbGeneralDate, vbLongDate, vbShortDate, vbLongTime, vbShortTime.

MS Excel: Working in multi-sheet environment

2005-04-17T09:10:00.000-04:00

When we are referencing one sheet data to another sheet in a formula or otherwise comparing multi sheets in a work book, it becomes tedious to move from sheet to sheet back and forth. Think of a situation where you have to refer data from three or four sheet.

Wouldn't it be nice, if you can have all those three sheets of workbook open side by side?
Did you know that there is an easy way to do this and I always find it very handy while working on a multi-sheet workbook?

Open your workbook, sheet1.

Go to Window tab,
Click on New Window,
Select sheet 2 on this newly opened workbook where you will see on the top with the name: 2.

Again repeat the same steps and open sheet 3. Here with the name, you will see: 3.

Now, that you have the same workbook opened thrice with different sheets,

Go to window,
Click arrange,
Select Vertical

and here you go, three sheet of same workbook opened side by side, toggle around and enjoy working on multi-sheet workbook.

MS Excel: Close all Open Workbooks

2005-03-31T04:46:00.000-05:00

At times, when you have many workbook open on your desktop and wish to close all of them together,there is an easy way in MS Excel to do this. I could never understand the reason why this option is not on main menu and is kept hidden.
Follow this:

Press Shift key on the keyboard while clicking File on the menu;

And now you will see the option: Close All

Click on it and all the workbooks are closed unless, in some workbook(s), you have made changes and not saved them.Excel will prompt you to save changes and close the workbooks.

MS Excel: Not only sort data in a column but also in the rows

2005-03-30T21:22:00.000-05:00

Click a cell of the row you want to sort horizontally.

Select Sort from the Data menu.

In the Sort dialog box, click on the Options.

In the Sort Options dialog box, click on Sort Left to Right, then click OK.

Back in the Sort dialog box, go to the Sort By combo box and select a row to sort, and also select the direction (ascending or descending).

You can select additional rows by going to the boxes.

Click OK.
Yu Hooo!!! Excel sorts the row horizontally.

MS Excel: Get Summary Calculations on the Fly

2005-03-29T15:34:00.000-05:00

As a busy executive, you are presented with lots of data in MS Excel format. Though you know the formulas to calculate and find results, but it is just a waste of time to write formula to see what the sum of particular range is or which is the highest value or lowest value or may be the average.
While looking at the spreadsheet, you can do the following to improve your analysis capabilities or effectively respond to one off questions which keeps coming up during meetings:

1. From the menu option, select View, and check the status bar.

2. This action will start showing you status bar at the bottom of spreadsheet.

3. Select the range of data you wish to analyze, and you will see on the status bar, Sum= 100 or whatever is the sum of the range selected (By default, excel is set to show Sum).

4. Right click on status bar, and you will see options like Average, Count, Count Nums, Max, Min and Sum. Select any one and see the result on status bar.

MS Excel: Sum of a column with #DIV/0! Or any other error message in some cells

2005-03-28T11:29:00.001-05:00

My friend recently requested me to suggest some quick fix for sum of a column which has few cells containing #DIV/0! Or any other error message. Doing the sum function produces error because sum neither ignores those cells nor understands it.

My process to deal with this problem is:

1.Insert a column next to the column you are trying to sum up.
2.In my example, the first cell is A3, I inserted column B.
3.In Cell B3, I wrote the formula “=IF (ISERROR (A3), 0,A3)”
4.This formula looks for the cell value and if it is an error, converts it to 0.
5.Then I copied this formula in B4 to B6 since my values were in up to cell B6, you could copy it till last cell value.
6.Select Cells B3 to B6, copy and paste special-values only.
7.Now you can sum this column and you are done.

Example:
Formulas:

Values:

MS Excel and VBA: Transposing Data

2005-03-26T11:38:00.000-05:00

This tip is submitted by Anupam Srivastava:

Transposing Data from Column to Row with a feature to start a New Row for each New Record which is identified by an empty Row:

The original dataset was stored in the same column followed by an empty row before each new group entry. The task was to bring each dataset into row format to make data analysis job easier. This is particularly useful for comparing information.
This Macro scans through the column, copies each item and paste it into different columns in the same row and begins a new row for each empty row it finds. The datasets are differentiated by empty row before new dataset begins.
To maintain consistency with the generated table, it is wise to make sure that each new dataset has the same number of data entities (items).

For users with knowledge of DB, if we consider each group to be an attribute with x number of entities, then each new attribute that you add to the original data should consist x entities to make sure that the final table gets populated in a uniform fashion.

For this Macro it is assumed that each dataset consists of 7 entities

Sub DataTranspose()

‘ Declare variable to denote column and row assignment

' Row Variable
Dim i As Integer
' Column Variable
Dim j As Integer
i = 2
j = 1

' Sheet has 218 rows of data

For i = 1 To 218
Range("A" & i).Select
Selection.Copy
Range("a" & j).Select
ActiveSheet.Paste

' Increment Row Counter to read next row
i = i + 1
Range("A" & i).Select
Selection.Copy

' Transposing Row Entry to Column
Range("B" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("C" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("D" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("E" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("F" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("G" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("H" & j).Select
ActiveSheet.Paste
i = i + 1
Range("A" & i).Select
Selection.Copy
Range("I" & j).Select
ActiveSheet.Paste
i = i + 1
j = j + 1
Next

End Sub

----------------------------------------------------------------------------------
Anupam Srivastava is B.Sc. (Computer Engineering) from Queen’s University, Kingston, Canada.

MS Excel: Always show full menu

2005-03-25T11:14:00.000-05:00

Turn off the frequently used menu option and always use full menu option during your learning process of MS Excel and remember, learning is a continuous process and you are always learning. So make the full menu option as a regular option for you.
MS Excel only shows you frequently used menu under each tab and then, if you wish to use something other than shown in the menu, you have to go to that expand sign to see full menu:

This is frustrating, especially when you are learning. How are you suppose to know, what is hidden under that magic expand sign. I always prefer to keep full menu on.
To do this, it is a very simple two step process:

Step 1: Go to Tools, find out Customize and click:

Now, you will see something like this:

Step 2: Click on Options tab and select, always show full menus, close and you are done.

Now, every time you click on a menu, you will see full menu. Remember, by doing this on MS Excel, your all other MS Office programs like MS Words etc. will also show full menu items and it is not possible to avoid this. But who cares, it is always good to keep full menu in view irrespective of program you are using.

MS Excel : The Leading Zeros

2005-03-24T08:45:00.000-05:00

MS Excel keeps removing that leading zero from your number?
Excel automatically sees the data as a number and leading zeros have no value in numbers, so it gets rid of them.
It is helpful when you're actually dealing with numbers, but irritating when it's a Item number or Employee number.
There is a quick way to get Excel to leave your zeros alone. Justchange the cell format to text. There are couple of ways to make this change—
1. Highlight the cells to change.
Then go to the Format menu, Cells choice. (Ctrl + 1 works too.)
On the General tab choose Text from the Categories list.
Click OK.
The leading zeros are there.
2.If you only have a few cells to change you could just type an apostrophe before entering the data (ie. instead of typing 01122 in the cell type '011122).
When you hit the Enter key the change is made instantly, for that cell only, and again your zero is there.

Outlook SaveAs Type Constants

2005-03-21T08:32:00.000-05:00

Constant Value
olTXT 0
olRTF 1
olTemplate 2
olMSG 3
olDoc 4
olHTML 5
olVCard 6
olVCal 7

SQL db to MS Access-How to

2005-01-17T13:49:00.000-05:00

Go To SQL Enterprise Manager
>>> Select Server
>>>Open DB
>>>>Tools
>>> Export.. and it will guide you through from there.Don't remember, you probably have to disconnect as well.
Try disconnecting and then, don't forget to connect back. :)

MS Excel:Quickly Navigate Multi-Sheet Workbooks

2004-12-18T02:21:00.000-05:00

Right-click the the arrow buttons at the bottom left-hand side of the screen (the sheet tab navigation tool). You'll get a clickable menu of all the sheets in the workbook.

MS Excel:Search for an Asterisk, Question Mark, or Tilde

2004-12-17T02:12:00.000-05:00

Since these characters have special meanings in Excel, you will need to precede them with a tilde to be able to search for them successfully.
So, to find an asterisk, Edit>Find, and then type ~* in the Find dialog box;
to find a tilde, type ~~;
to find a question mark, type ~?.

MS Excel:Change Text Case

2004-12-16T03:08:00.000-05:00

Excel provides three handy functions to change the case of text.
UPPER: converts all text to uppercase
LOWER: converts all text to lowercase
PROPER: converts text to The First Letter Of Each Word Is Capitalized.
If cell A1 contains the text: sameer lal, the following formula will produce Sameer Lal.
=PROPER(A1)
You may want to then select the cell or the range, Copy and then Edit>Paste Special (then choose Values) to change the contents of the cells from functions into the values produced by the functions.

MS Excel: Formula Error and What It refers To

2004-12-15T05:30:00.000-05:00

When you get an error in your formula cell, you’ll need to determine what caused the error and fix it.
Common Formula Errors You’ll See in EXCEL
What Appears in the Cell and What happened? :

#DIV/0!
Appears when the formula calls for division by a cell that either contains the value 0 or, as is more often the case, is empty. Division by zero is a no-no.

#NAME?
Appears when the formula refers to a range name that doesn't exist in the worksheet. This error value appears when you type the wrong range name or fail to enclose in quotation marks some text used in the formula, causing Excel to think that the text refers to a range name.

#NULL!
Appears most often when you insert a space (where you should have used a comma) to separate cell references used as arguments for functions.

#NUM!
Appears when Excel encounters a problem with a number in the formula, such as the wrong type of argument in an Excel function or a calculation that produces a number too large or too small to be represented in the worksheet.

#REF!
Appears when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over the cells referred to in a formula.

#VALUE!
Appears when you use the wrong type of argument or operator in a function, or when you call for a mathematical operation that refers to cells that contain text entries.

MS Excel:Remembering Cell Names

2004-12-14T01:08:00.000-05:00

When you're doing a formula and can't remember the range names that you need, you can:

Press [F3] - (a list of range name appear)

DOUBLE CLICK the range name that you want to include in formula.

MS Excel: Quick Click Movement

2004-12-13T06:11:00.000-05:00

If you Want to find out where a column ends or where the next blank cell is in the column.

Select a cell:
Double-click the bottom edge of the selected cell,
(Take cursor to botton edge and when it becomes + sign made of four arrows)
and you're taken to the last filled cell below the selected cell in the current column.
This trick works for rows nd column and in all four directions.
So, for example, if you want to find the last filled cell or the next empty cell in the current row:

Select a cell:
Double-click the right edge of the cell.

MS Excel:Range Names Used In Formulas

2004-12-12T05:02:00.000-05:00

Named ranges let you calculate the total for all cells in a range with a formula such as

SUM(Value) instead of SUM(E2:E20)
For example, enter the name Quantity for Column C and Price for column D and value in column E.(highlight the column from C2:C20 and then type the name in the Range name text area at the left of the fx symbol and hit Enter).
Then enter the formula =Quantity*Price in column E cells.
Now, as you fill in numbers in the Quantities and Price columns, Excel automatically calculates Values in the third column.

MS Excel: Avoid error displays in formulas

2004-12-11T05:28:00.000-05:00

Sometimes a formula may return an error message
but you may want to avoid the messages.
You can do so by using an IF() function to check for an error.
The formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),"",A1/B1)

MS Excel: Indirect Function

2004-12-10T06:15:00.000-05:00

Excel's INDIRECT function accepts a text string as an argument,and then evaluates the text string to arrive at a cell or range reference.Assume that cells B2,C2,D2 on our Master worksheet hold the student's name.
The following formula utilizes the INDIRECT function to create the range reference used by the SUM function(Cell B2 to B6 carry individual marks for each subjecton different sheets named after students referred in B2 to D2):

=SUM(INDIRECT(B2&"!$B$2:$B$6"))

Note that the ampersand operator to join the student name with the cell reference (expressed as text).Refer the example below:The cell B2 contains the text Sam,the SUM function returns the sum of the range Sam!B1:B6.

MS Excel: Copy formula shortcut

2004-12-09T06:27:00.000-05:00

If you have some formula in cell A3, go to cell A4 and press Ctrl+D and the formula will be copied there in cell A4.Similarly, cover cell A3 to A15, and press Ctrl+D, the formula will be copied from A4 to A15

.Same thing you can perform in right cell to the cell of formula by pressing Ctrl+R, in our case, B3.

This tip is contributed by Pramod Jain

MS Excel:Cell Counting Techniques

2004-12-08T11:34:00.000-05:00

Excel provides many ways to count cells in a range that meet various criteria:

The DCOUNT function. The data must be set up in a table, and a separate criterion range is required.
The COUNT function. Simply counts the number of cells in a range that contain a number.
The COUNTA function. Counts the number of non-empty cells in a range.
The COUNTBLANK function. Counts the number of empty cells in a range.
The COUNTIF function. Very flexible, but often not quite flexible enough.

MS Excel:Calculate the day of the year and days remaining

2004-12-07T05:22:00.000-05:00

If you've ever had to figure out which of the year's 365 days a particular date falls on, or how many days remain in the year, you've probably found that Excel lacks functions to perform the calculation. But you can create formulas to do the job.
The formula below returns the day of the year for a date in cell A1:

=A1-DATE(YEAR(A1),1,0)

Note: Excel automatically formats the cell as a date, so change the number format to another option (like General).
To calculate the number of days remaining in the year (assuming that the date is in cell A1), use the following formula:

=DATE(YEAR(A1),12,31)-A1

MS Excel:Count AutoFiltered Rows

2004-12-06T06:02:00.000-05:00

AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering criteria, Excel shows the record count on the status bar--but this value disappears when the sheet is calculated.
To display a permanent count of the visible rows in an AutoFiltered list, create a formula using Excel's SUBTOTAL function. The first argument for SUBTOTAL specifies the type of operation (an argument of 2 displays a count of the visible cells in a range).The formula in cell which will display visible rows is:

=SUBTOTAL(2,A6:A3000)

The formula counts the number of visible cells in the range (minus the header row). Apply different filtering criteria, and the formula updates to show the new count. The SUBTOTAL function only works for AutoFiltering and outlining. If you hide rows manually, it won't return the correct result.

MS Excel:Calculating a conditional average

2004-12-05T06:02:00.000-05:00

In the real world, a simple average often isn't adequate for your needs.
For example, an instructor might calculate student grades by averaging a series of test scores but omitting the two lowest scores. Or you might want to compute an average that ignores both the highest and lowest values.
In cases such as these, the AVERAGE function won't do, so you must create a more complex formula. The following Excel formula computes the average of the values contained in a range named "scores," but excludes the highest and lowest values:

=(SUM(scores)-MIN(scores)-MAX(scores))/(COUNT(scores)-2)

Here's an example that calculates an average excluding the two lowest scores:

=(SUM(scores)-MIN(scores)-SMALL(scores,2))/(COUNT(scores)-2)

MS Excel: Word Count

2004-12-04T03:51:00.000-05:00

Here is VBA Macro to count number of words in Excel spreadsheet as MS Excel does not have built in word counter like MS Word:

Sub NumberOfWords()

Dim NumberOfWord As Long
Dim RangeArea As Range
Dim Str As String
Dim Num As Long

For Each RangeArea In ActiveSheet.UsedRange.Cells
Str = Application.WorksheetFunction.Trim(RangeArea.Text)
Num = 0
If Str <> "" Then
Num = Len(Str) - Len(Replace(Str, " ", "")) + 1
End If

NumberOfWord = NumberOfWord + Num

Next RangeArea

MsgBox NumberOfWord

End Sub

MS Excel: Delete Those Unwanted Sheets

2004-12-03T09:59:00.000-05:00

Deleting the whole sheet, Excel makes it quick and painless:
Right-click on the sheet tab. Select Delete from the shortcut menu.

MS Excel: Add a Row or Column Easily

2004-12-02T04:51:00.000-05:00

In Excel, the easiest way to insert or delete entire rows or columns is to use keyboard shortcuts: [Ctrl]+ and [Ctrl]-.
Drag across the row numbers or column letters in the worksheet frame to select the number of rows or columns you want to insert.
Then, you press [Ctrl]+ (use the plus sign on the numeric keypad) to insert entire rows or columns at that spot. You press [Ctrl]- to delete entire rows or columns.

MS Excel:Copying Format

2004-12-01T05:50:00.000-05:00

Sometimes you'll want to copy only a range's formatting. The easiest way to do this is to use the Format Painter button (it's the one that looks like a paint brush on the Standard toolbar). To do so, first select the cell or range whose formatting you want to copy, then click the Format Painter button. When the mouse pointer changes to a paint brush, select the range to which you want to copy those formats. When you release the mouse button, Excel will paste the formats to that range and the mouse pointer will return to normal.
To copy formatting to several nonadjacent cells or ranges, repeat the same steps, only double-click the Format Painter button. This way, the mouse pointer will remain a paint brush until you press [Esc]

MS Excel:Protecting Cells

2004-11-30T21:43:00.000-05:00

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

2004-11-29T23:24:00.000-05:00

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

2004-11-28T03:56:00.000-05:00

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

2004-11-27T04:19:00.000-05:00

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

2004-11-26T09:21:00.000-05:00

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

2004-11-25T06:51:00.000-05:00

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

2004-11-24T08:38:00.000-05:00

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

2004-11-23T16:50:00.000-05:00

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

2004-11-22T10:46:00.000-05:00

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

2004-11-21T14:40:00.000-05:00

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

2004-11-20T08:31:00.000-05:00

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

2004-11-19T09:22:00.000-05:00

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

2004-11-18T13:20:00.000-05:00

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

2004-11-17T08:40:00.000-05:00

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

2004-11-16T09:17:00.000-05:00

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

2004-11-15T09:10:00.000-05:00

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

2004-11-14T13:12:00.000-05:00

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

2004-11-13T12:58:00.000-05:00

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

2004-11-12T10:27:00.000-05:00

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

2004-11-12T10:02:00.000-05:00

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

2004-11-12T09:03:00.000-05:00

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.