ExcelElite

Just another knowledge sharing place

  • Join 1 other follower

  • Read Counter

    • 300 hits

Validate Excel Cell Input

Posted by sqlnow on June 3, 2010

You are doing many critical calculations in a single excel spreadsheet in our daily work, but what if you entered invalid data into the sheet? All the formulas entered will through some error. So can you really validate user inputs in excel?

Yes, you can!! This post demonstrates how to restrict incorrect inputs into a particular column. Here I will demonstrate how to validate a column where you can enter only Numeric values. Just follow these simple steps:

Press ALT + F11 key combination to open Visual Basic Editor >> double click on the Sheet you want to validate from the Project Window. Paste the below VBA code and save the spreadsheet and you are done with the requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
 'do nothing if multiple cells edited
 If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
With Target
If .Row > 1 Then
If IsNumeric(Cells(.Row, 1)) = False Then
MsgBox "Not a valid integer value", vbInformation
End If
End If
End With
End Sub

Isn’t it interesting?

I am not allowed to attach excel files in my posts or else I could have provided you the sample macro for this. Please leave your valuable comment here.

Posted in Code Corner, Data Works | Tagged: , , , , , | Leave a Comment »

Saving Excel Spreadsheet Data in XML

Posted by sqlnow on June 3, 2010

This post demonstrates how to convert excel spreadsheet data into XML. Why do you want to convert the excel data to XML? Well we have our data in excel spreadsheet and we want to show them into flash or our web.  As we have the data with us it will be unwise to reenter the data into XML manually. This post will help you to do that in excel.

Let’s discuss with the detailed steps. Below is the data in excel spreadsheet as in Figure – 1, which you want to convert into XML.

Figure-1

Figure – 1

 

Step-1: Create a XML schema first as in Figure – 2

 Figure – 2

 

Step-2: Once you created the schema just save it and import it into Excel as bellow:

Open a new excel spreadsheet è then go to Developer Tab è in the XML section click on Source (This will open up a XML Source Window on the right side of Excel application) è click on XML Maps è Add è Choose the XML schema file you created above èOK.

This will look something like Figure – 3 below:

Figure-3

Figure – 3

 

Step-3: Once this is done you just drag and drop the parent node to the spreadsheet. Then copy the data and paste it into the mapped sheet as in Figure – 4

Figure – 4

 

Step-4: Finally save the sheet as a “XML Data” file as in Figure – 5

 

Figure – 5

Now you can use the XML file created with all the data in XML format.

Please send your comments.

Posted in Data Works, General | Tagged: , , , , | Leave a Comment »

Smart Excel Keyboard Shortcuts

Posted by sqlnow on June 1, 2010

Here are some frequently used keyboard shortcuts to speedup the daily work.

Key Combination Purpose
SHIFT+F11 or ALT+SHIFT+F1 To Insert a new worksheet
CTRL+SHIFT+PLUS SIGN To Insert blank cells
F11 or ALT+F1 To Create a chart that uses the current range
ALT+DOWN ARROW To Display the AutoComplete list
CTRL+; (semicolon) To Enter the current date
CTRL+SHIFT+: (colon) To Enter the current time
CTRL+ENTER To Fill the selected cell range with the current entry
SHIFT+F5 To Display the Find dialog box
SHIFT+F4 To Repeat the last Find action
F5 To Display the Go To dialog box
CTRL+1 To Display the Format Cells dialog box

 

Work smart !!! enjoy the day…

Please leave your valuable comments.

Posted in General | Tagged: , , , , | 1 Comment »

My First Post

Posted by sqlnow on June 1, 2010

Just to test things…

Need your valuable comments all the time…

Thank You

Posted in Uncategorized | Leave a Comment »

Hello world!

Posted by sqlnow on June 1, 2010

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Posted in Uncategorized | 1 Comment »

 
Follow

Get every new post delivered to your Inbox.