Sql Server Error 3154

I created a new database today and was wanting to restore this new database from another exisitng database using the restore option available in the wizard.

But I got the 3154 error as below.

So I had to restore to the restore database command in the SSMS. So here is the first option I tried

restore database databasename
from disk 'filepath.bak'
with replace

but this gave an error because the database and log files were in use for the exisitng database and had to use the with move command as follows.

restore database databasename
from disk 'filepath.bak'
with move data file to 'file path',
move log file to 'file path',
replace



The database was restored successfully.

Workbook is larger than the maximum workbook size

I use sharepoint 2010 to display the data analysis tool I have created in excel 2010 to manage the Microsoft licenses as well as to utilise the excel 2010 capabilities of slicers and dicers. This has been going pretty smoothly until yesterday when I stumbled with this error below.

 

"Workbook is larger than the maximum workbook size"

 

So I had to go through the Excel web services properties on the sharepoint server and increase the maximum file size.

 

 

Here are the steps that I followed.

 

 

• Logged into the Central Administration of the Sharepoint on the relevant server.
• Clicked on Application Management
• Clicked on Manage Service Applications
• Cicked on Excel Service Application which is a hyperlink in blue
• Clicked on Trusted File Locations and clicked on the relevant link as shown below

• Under the file size properties I have changed from 10 to 15 as shown below. Once this is done the page needs to be reloaded and the excel web part works beautifully.

 

Scroll Lock problems in Excel

Yesterday I had this problem in moving between the cells within excel. This problem usually occurs if the scroll lock is on. I tried to find the scroll lock button on my keyboard but it was not there. So I got the idea of using the On screen keyboard function to diable the scroll lock.

So I clciked on Start button -- All Programs -- Accessories -- Ease of Access --On Screen keyboard.

The following screen appears and I click on the scroll lock as shown below.



This same funcationlity can be used when we use remote desktop to access the keyboard of the remote computer.

Remove the compatibility mode from excel files

As you all know Excel 2007 has lot more features compared to Excel 2003. The extension with which the excel files are saved alos differs based on the version of excel.

For excel 2003 the file is saved as .xls and for Excel 2007 the file is saved as .xlsx

The other day I realised that when I opened a file in Excel 2007 all the features of Excel 2007 are not available for use.

So when I tried to investigate what was happening I found that the file was saved as .xls. So I saved the file as excel 2007 workbook and tried to use all the features.

I was not able to use all the features until I reopened the file in excel 2007.

I thought I would mention this tip here for everyone's benefit.

Resolving #NUM error in excel

I was using excel to display data from a cube. I tried to do a caluclated cell in excel to display number of months between two dates. Here is the formula I used to do this.

=Datedif(H1,G5,"m")

But I received an error -- #NUM

Then I again looked at the dates entered. To make sure that the datedif function works you need to give the earlier date as the first parameter and the later date as the second parameter to avoid the #NUM error. You also need to ensure that the dates are in the correct format otherwise you will receive a #VALUE error.

So to avoid the #NUM error I changed the formula to

=Datedif(G5,H1,"m"). This retunrs the difference between the two dates in months.

Multiply and Divide a group of cells in Excel

I just now realised that it has been more than a month since I have posted anything on my blog. So here I am with the most recent tip I learnt in Excel.

If you have a column of cells that need to be divided by 1000 for example. Here are the steps that you need to follow.

• Type 1000 in a cell lets say A2



• Copy the cell A2 using Ctrl+C



• Select the cells in the column where you would like to let us say multiply the values by 1000.



• Right click and select Paste Special



• Click on Multiply as shown below.

You can do the same for adding or subtracting a specific value from a group of cells.

This tip is particularly useful if you want to show the data that is in millions but want to reduce the number of digits by dividing them by 1000.

Hope this heps.

Add months to date in excell

Yesterday I had this requirement to add number of months (m) to a cell that contains start date to estimate the end date. In 2003 I had to use a complex formula like below where A1 is the cell that contians the start date.

=DATE(YEAR(A1),MONTH(A1)+m,DAY(A1))

Well in Excel 2007 it is even more easy to add months with the Edate function like below.

=Edate(A1, m).

Using Countif in Excel

I was using excel today and I wanted to count all the cells in a range only when there is a value greater than 2. So I started hunting for a funciton in excel and found the countif function.

Countif(range,criteria)

But when I started using it like this -- Countif(A1:A100,>2) it didn't work. It was coming up with an error.

So I tried Countif(A1:A100,">2"). This worked. So if we include the criteria in quotes then the formula worked.

I also noticed a sumif function that also works in a similar way. It populates the sum of all the cells in the range specified that satisfy the criteria given.

Conditional formatting using multiple conditions - Excel 2007

Today I had a requirement to colour code in Excel based on a few conditions. At first I thouhgt I could use thre to four rules in the conditional formatting to achieve this result but when I did that the results that I was getting was not very satisfactory. So I thought I would try the And function of excel in conjunction with the conditional formatting and it worked wonders.

I wanted the entire row to be highlighted in a certain colour based on three conditions.

Here are the steps I followed to achieve what I was after.

• Select all the data that you want to colour code.
• Click on Conditional Formating on the Home Tab
• Choose Manage Rules as shown below
  

• From the dialog that appears click New Rule.
• Add the multiple conditions using the And function in the formula box
• The most important thing you need to do when you are adding the formula to highlight rows is to remove the $ sign that gets assigned when you choose the cell for entering a formula with a mouse.
• Choose the colour by clicking the format button and click ok
• Click Ok again to come out of the Manage Rules window.

As you can see below I have added two rules and it is giving me the result that I want.

Print formuals from Excel 2007

Yesterday I had a requirement to compare formulas between two sheets. So I wanted to print the formulas and compare. This is a basic feature but I straight away did not know how to do it. So I started investigating the ribbon in Excel 2007.
After about 2-3 minutes Voila I found this.

Under Formulas Tab -- There was Show Formulas button in the Formulas Auditing Group as shown below. This button works as a toggle for showing and hiding the formulas.

Copy Paste problem in excel 2007

Today one of my colleagues was working in excel. He had a dataset that in one sheet which was filtered. He wanted to copy those records that were filtered into a new worksheet. But when he copied and pasted the cells into another sheet the cells that were pasted contained all the rows. What he wanted to copy was only the filtered rows. This becomes a problem sometimes in Excel 2007

To overcome his paste problem I have suggested the following steps

Go to the Editing group in the Home tab
Click on the "Find and Select" button
Click on the "Go to Special" from the list
And click on "Visible cells only " radio button and click ok

Now you can copy and paste only filtered rows in excel.

Another quick turn around is open the workbook in another computer where the filtered cells copy paste works and do the first copy and paste and close the worksheet.
Now if you open the spreadsheet on the first computer the filtered cells copy and paste works !

When my joystick started giving in...........

The other day I had about 20 excel workbooks to work with in a single day to complete some outstanding work in a tight timeframe. In each one of the excel workbook I was working on, there were about 20-25 worksheets. I use a joystick instead of normal mouse, and we didn't have any spare joysticks. My joystick thought that I have had enough of this and started giving in to my moves. I didn't want to increase my RSI by uisng a normal mouse. Instead I thought I will use some excel key board short cuts to work in these excel files.

Here are the shortcuts that I thought will come handy to everyone.

• CTRL + PgUp -- To switch to the right hand side of the sheets
• CTRL + PgDn -- To switch to the left hand side of the sheets
• F4 -- To repeat last action in Excel (This works only in Excel, I wish it worked in word as well!)
• CTRL + --> -- To move to the last populated cell on the right hand side
• CTRL + <-- -- To move to the last populated cell on the left hand side
• CTRL + down arrow -- To move to the last populated cell in the downward direction
• CTRL + --> -- To move to the last populated cell in the upward direction.
• If you hold down the SHIFT key as well for the above four commands the cells are selected.
• CTRL + Spacebar -- To select an entire column in a worksheet
• SHIFT+ Spacebar -- To select an entire row in a worksheet

All the above shortcuts helped me in doing my job quickly that day in spite of having a problem with my joystick.

Count number of months between two dates in excel

The formula I used for this is

datediff(cell1,cell2,"m").

This will give you the number of months.

Shortcut key to display Macro dialogue

In excel the shortcut key to display the macro dialog box is Alt+F8. This can then be used to create, run, edit, or delete a macro.

I had a requirement to import an excel 2007 spreadsheet into a database as a table in sql server 2005. In Excel 2007 the drivers required for exporting have changed when compared to the previous versions. With Excel 2007 you use the driver named 'Microsoft Access 12.0 database engine OL DB provider'

Following are the steps to import data into sql server 2005 from Excel.

• Right click on the database from sql server management studio and choose tasks -- import data
• Choose the data source as the driver name specified above ('Microsoft Access 12.0 database engine OL DB provider')
• Click on properties button and click on All tab
• Double click on the data source line and give the file name with the exact path in the property value field. Click ok
• Double click on the Extended properties line and enter Excel12.0 in the property value field. Click ok twice.
• Click next through the import wizard and preview the data and click finish.

Your data is imported into the sql server database.

Disable report filter selection in a Pivot Table in Excel

I had to work on disabling a field (report filter) on a Pivot Table in Excel 2007. I googled a lot on this and there was no straight forward answer. Hence thought of sharing my solution with anyone who wants to do the same thing

Here is the code that I have used to achieve this

Right click on the sheet where there is a pivot table and click on View Code
Add the following code

Option Explicit
Private Sub Workbook_Open()DisableSectorSelectionEnd Sub
Sub DisableSectorSelection() Dim pt As PivotTable On Error Resume NextSet pt = Selection.PivotTableIf Not pt Is Nothing Then pt.PivotFields("Sector").EnableItemSelection = False
End Sub

Save and reopen the file.
The filter should not be disbaled on the worksheet.

The following features cannot be saved in macro-free workbooks

The following features cannot be saved in macro-free workbooks

VB Project

To save a file with these features, click no, and then choose a macro -
enabled file type in the file type list.

To continue saving as a macro freee workbook, click yes.

That was the error I was getting today when I wanted to write a macro in Excel 2007.

In order to make an Excel 2007 workbook macro enabled, you need to save the workbook as .xlsm rather than .xlsx That solves the problem of the macros not being enabled.

Password protect a workbook in Excel 2007

If you want to password protect your workbook in Excel 2007 the following are the steps that need to be followed.

• Click on the Office button on the top left hand corner of the workbook
• click on prepare
• choose restrict permission
• choose a pasword
• you may be asked to reenter the password

That's it your workbook is password protected.

Disable accessing a hidden sheet in Excel workbook

I had a requirement today to not display the hidden worksheets in an excel workbook when you try to unhide sheets. Here are the steps I have taken to achieve this.

• Open the VBE (alt-f11)
• Go to the sheet that you want to disable in the unhide option
• View properties window
• Change Visible property to 2 -XlSheetVeryHidden.
• Save and close

Test the unhide option