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.

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.

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.

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

Convert date to text in excel

If you have a date in a cell, when you try to use it as it is for example in concatenation, the date gets converted to a number and does not retain the date format.

In order to retain the date format and still be used as text -- the formula that can be used is TExt(datecell, "dd-mm-yyyy")

Adding a calculated field in pivot table

The following are the steps to add a calculated field inside a pivot table in Excel

• Click on a cell inside a pivot table.
• Go the PivotTable Tools -- Options -- Click on Formula --Calculated Field
• Add the formula for the calculated field
• Click ok. This will add the calculated field to the Pivot table as well.

Add a line break in an Excel cell

Sometimes you might have a need to add a line break (press enter so that a new paragraph is created). To do this the key combination is Alt + Enter

Making speedometers in Excel

I was wanting to create a speedometer gauge in excel and started researching on the internet. Here are the links that I came with...

Interesting article titled Make Excel Charts interactive with sliders, gauge, dials and more by Ashish Mohta

There are several add-ins available that can be used to create speedometers in excel. The one that I liked the most was at Mrexcel.com

The link that I most found useful was this -- Hack together a Gauge Chart in Excel without sweat

Problems connecting to SSAS 2005 from Excel 2003

Today I was trying to connect to SSAS 2005 cube from Excel 2003 and I was not able to do so using the exisitng OLAP 8.0.

I had to download the folowing to resolve the problem.

1. Microsoft Core XML Services (MSXML) 6.0 SP1
2. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider

I have followed the article on Microsoft downloads http://support.microsoft.com/kb/940167

and that solved my problem !

Excel to Word mailmerge problem

Today there was this small thing that took my time. A date field in excel was formatted correctly but when the field was used in the mailmerge with Word. The format in the excel was not being reflected in word.

So I used the following steps to correct this in Word.

Step 1. Right click on the field name
Step 2. Click on the ‘Toggle Field Codes’ option For eg., { MERGEFIELD “AFinish” }
Step 3. Edit the field to include \@ “dd-MM-yyyy” e.g. { MERGEFIELD “AFinish” \@ “dd-MM-yyyy” }
Step 4. Right click and click update field.


You can see the date format as 17-08-2008

Adding lines on a stacked column chart

Today I had a need to do a graph in Excel where it need to display a stacked column chart and also a line in the same graph.

The data for this is as follows for three years

2008 2009 2010
12000 14000 16000
1000 1000 1000
170000 180000 200000

I wanted a stacked column with the first two rows and a line with the last row. But when I chose the normal 2d column chart, it was showing all three rows as one stacked column.

To get the desired results, I followed the following steps

First, include all three rows in the series part of the chart data and choose the 2d stacked column chart

Next, right-click on the colored part of the bar that corresponds to the year and choose "Chart Type". Then select the first Line graph. Click Ok

Finally, right click on the graph and choose chart options and tick the 'Value Y Axis' tick box under the secondary axis lable of the 'Axes' tab

You should now have the first two rows displayed as a stacked column chart and the third row displayed as a line graph above your columns.

Opening Excel from an email in Outlook

I came across this problem today where Excel by default opens a s a 'Read Only' when opened from an email.

This is because the macros of the sender of the email are set to 'High'. This option automatcially sends the excel attachement in the email as 'Read Only' file making it difficult for the user to make changes.

This problem can be overcome by setting the Macro Security to Low in the Senders outlook. To do this, go to Tools -- Macro -- Security -- Choose Low and save the setting.

Creating a single line graph in Excel

Today I was asked a question from my friend as to how to add a single line graph in excel. That is how to display a line graph between price and quantity in excel with price in X-axis and quantity in Y-axis. When you select line graph in excel, that gives two line graphs one for quantity and one for price.


To arrive at a single line graph here is the procedure to follow. Enter the data into two coulmns of price and quantity as shown. Then select the two columns and click on chart wizard.




Select XY(Scatter) for Chart type. And the first one without any lines for the sub chart as shown. Click once on "Press and Hold to View Sample" to check whether the X-axis and Y-axis values look as what you are expecting. Then click next and change the X-axis and Y-axis values appropriately for both the series.


Then click on Finish. You get a range of dots which can then be connected by a line. To do this, click on chart and then on a "Add a trend line". Choose automatic and your line graph is ready.