Excel References Between Sheets

Sometimes you want to make a calculation in one Excel sheet and display the result in another sheet. In a new tutorial in the kalmstrom.com Tips section I am showing how to make a formula refer to another sheet than the current one.

Exclamation mark
As an example in my tutorial I use a small ice cream factory with three kinds of ice creams. In an Excel workbook they have one tab for calculation of the selling price for each kind of ice cream. On another tab they have the costs for all ingredients of each type of ice cream.

When the costs of the ingredients for a certain batch are summarized and divided with the number of ice creams in that batch, they get the cost per ice cream. To see how much profit they will get with different prices, they need to get the cost per ice cream into the price calculation sheet.

This is actually very easy. When you create the formula and pick the cell for cost per ice cream in the Recepies sheet, Excel will automatically add not only the cell but also the name of the tab and an exclamation mark. The exclamation mark is the key!

Works with more
My example is very simple, but the principle is the same in more advanced use of references from outside the sheet: if you know how to manage it, Excel will help you with the formulas.

You can go to another workbook and pick a cell there to include in your formula, and you can refer to a range of cells or to a name. I will show this in a later tutorial about Lookup references.



kalmstrom.com Tips and pointers
The kalmstrom.com website has a much visited Tips section with tutorials on the Microsoft products we build on, like Excel, SharePoint and Outlook. They are not directly connected to the kalmstrom.com products but still useful to administrators of our applications.

Obviously many others also find these tutorial interesting, because the kalmstrom.com Tips section has a lot of visitors. And you are all welcome!

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Office 365 Trial Setup

Last week I wrote about Microsoft's decision to donate Office 365 to nonprofit organizations. This week I will come back to Office 365 and show how to set up a trial.

In the kalmstrom.com Tips section there is an article about how to set up an Office 365 trial. It has the demo below and also step by step instructions.

MS cloud platform
Office 365 is Microsoft's cloud platform. It builds on the Microsoft Office suite and gives hosted e-mail, storage, networking and collaboration and more. You subscribe to Office 356, just like you subcribe to the kalmstrom.com solutions.

The most important advantage of Office 365 is that the Office applications and other software like Exchange and SharePoint are possible to reach from everywhere over the internet. And users who have earlier worked with the Office desktop applcations will recognize the programs and can continue using them online without training.

All the kalmstrom.com applications work with the Office 365 versions of the products we build on – Outlook, SharePoint, Excel and Exchange.

Office 365 version
There are several versions of Office 365, and in my demo I set up a trial of the Enterprise E3 version, which is the one I prefer. I recommend you to study and compare the different options carefully and to select a version that is a little better than what you need right now. It is not always easy to upgrade to a more advanced version later.

Start with SharePoint
After entering all details and adding another user I finish my demo by setting up SharePoint. kalmstrom.com build on SharePoint, and many viewers of my tutorials are interested in SharePoint. I can see that on the number of visitors to my SharePoint-demos in the kalmstrom.com Tips section and on YouTube.

kalmstrom.com usage
In the kalmstrom.com team all members are using the Office 365 Outlook, Exchange and SharePoint, and it has worked well for us. We are a geographically distributed team, working in Sweden, India and Spain, so for us it is perfect to share mail and team site in the cloud.

To further enhance Office 365 we use our own products HelpDesk OSP for support mail and for our own improvement suggestions and Kanban Task Manager for our project and task management.

If you have been thinking about Office 365 but not tried it yet, I suggest that you do. You might not want to transfer everything from desktop to Office 365, but it surely is a good complement to other software.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

SharePoint In Depth

In addition to my work with the kalmstrom.com products I am often teaching on advanced IT courses. SharePoint In Depth is the umbrella term for some new classes I am looking forward to lead.

Inspiring meetings
 I am a Microsoft certifed Trainer, and I really like teaching. The in-class strategy discussions and the brain-storming around technical opportunities are both stimulating and enjoyable, and in the end they also have a positive impact on the kalmstrom.com products.

SharePoint demand
Most of my courses are organized by Cornerstone, Sweden's largest seller of professional IT-training, both for IT-pros and developers. They have experienced an increasing demand for SharePoint courses, so now there are six new courses called SharePoint In Depth. I have been asked to lead them together with my fellow SharePoint expert Stefan Smedius.

Various subjects
The In Depth courses aim to delve deeper into a particular topic, and they are intended for those who want to keep track of a certain functionality or subject, rather than getting a general knowledge from a given professional role.

These are the In Depth courses:

• Working with large amounts of data – how you can let the data be where they are but still present it in SharePoint in an attractive and user-friendly way
  
• InfoPath
• Content Management – build efficient information sharing
• Business Intelligence for SharePoint Enterprise
• Workflows with SharePoint Designer
• Search – build filtered searches, expand search for sources outside SharePoint, design search experience, introduce local metadata to filter

Tutorials
If you have followed my blog you will recognize the subjects in the list. In earlier articles I have introduced SharePoint tutorials published in the kalmstrom.com Tips section, and some of them have dealt with the same areas as the In Depth courses. Now the new courses will give me a welcome opportunity to dig deeper into these areas.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

SQL Server 2014 Supported By kalmstrom.com Solutions

Last week all kalmstrom.com products that have the SQL Server database option were tested for SQL Server 2014 CTP1, and our tester found no issues at all. I am happy to announce that they are fully compatible with next version of SQL Server.

Enhancements
A key feature of SQL Server 2014 is the In-Memory OLTP. This memory optimized OLTP database engine gives better performance, as it works with in-memory data instead of disk-based data. In-Memory OLTP highly improves transaction processing speeds and reduces latency.

In addition to this, the SQL Server 2014 has advanced features for Business Intelligence. As expected it is also more adapted to Microsoft's cloud platform Azure than earlier versions. Please refer to Microsoft for more info about features in different versions of SQL Server 2014.

kalmstrom.com database options The kalmstrom.com applications use a database or a SharePoint site for sharing and storage of data. For the database we give two options: Access or SQL Server. We only recommend the Access database for small business with few users and limited amounts of data. In other cases we advise users to upgrade to an SQL Server database, which gives much more possibilities and a huge storage capacity. The SQL Server is a database management system designed to have many hundreds of users accessing it at the same time. It has advanced database administration tools for scheduling tasks, configuring security accounts and roles, transferring data between other sources and much more.

Important testing
The kalmstrom.com Lead QA, Vijayant Rimza, keeps track of any new versions of the products used with our applications, and when a preview has been published he always tests our applications with it.

This way we will know about any problems long before the public version of the updated product is released, so that we can inform users and start solving the issues in a timely manner.

No issues
Now, when we have the Community Technology Preview of SQL Server 2014, Vijayant has been able to test those kalmstrom.com solutions that can use Outlook in combination with a database: Calendar Browser, for resource booking, Folder HelpDesk, Kanban Task Manager, TimeCard and our knowledgebase KBase.

The result was excellent, as the current versions of the applications are already compatible with SQL Server 2014. This means we can concentrate our development efforts on other enhancements, something we will be happy to do!

Excel Text Analysis and Manipulation - New kalmstrom.com Demo

I am continuing my series of Excel tutorials with a demo that might surprise some of you, as it is about text analysis and text manipulation.

Normally you think about word processing programs like Microsoft Word in connection with text, but Excel actually has some good features for text also.

Excel can never replace Word, but thanks to its calculation features Excel can do things that Word cannot manage.

Split up text in columns
The most obvious use for the Excel text functions is perhaps to split a text string so that words are separated in different columns. This can be done in Word to, by using the "Convert text to table" command, but in Excel you have more options – and best of all, in Excel the text becomes possible to filter.

Insert function
Most options for text manipulations are found in the Text category of the Insert function dialog, which is reached via the Insert function button.

 Filter for the Text category in the dialog that opens, and you will get many different possibilities. In my tutorial I am only showing the ones I use most, for example how to pick out words in the beginning or in the end of a cell.

I suggest that you watch it first, and if you want to learn more Microsoft has a page with explanations to Excel text functions. It was written for Excel 2007, but there are only minor changes in later versions. I used Excel 2013 for my demo.



kalmstrom.com Tips
In the Tips section of the kalmstrom.com website we have gathered a lot of tips and pointers on Excel, Outlook and SharePoint. Here you can find the tutorials from the kalmstrom.com YouTube channel with some extra information and step by step instructions.

There is also some additional articles that are not represented on YouTube. Please visit the site and browse around! I am sure you will find something useful.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

TimeCard Single For Outlook In New Design

The kalmstrom.com Outlook add-on for reporting of hours and expenses from the Outlook calendar is undergoing an extensive re-build. First to be released is the Single version.

Keep track of time
Self-employed people all over the world have since long been using TimeCard Single to keep track of how many hours they have worked on certain projects or for certain customers.

TimeCard Single creates an Access database where all the reported details can be found. You can also export  time reports to an Excel sheet and study how your time has been spent in statistics reports

New design
When current users upgrade to version 5 of TimeCard Single they will see the difference at once, as version 5 will have a new design. The TimeCard logo has also been updated, to comply with Outlook 2013.

TimeCard Single can be highly customized, which is very appreciated. But when all the different options are gathered in one screen, like in earlier versions,  the first impression might be overwhelming.

In version 5 the different kinds of configuration possibilities have been grouped in four screens. A main screen gives buttons to the different groups.

The new design with configuration groups and one main screen will hopefully make it easier for new users to get a grasp on how TimeCard Single works.

New architecture
The underlying architecture of TimeCard Single will be totally changed, and this makes it possible for us to add support for the 64-bit versions of both Outlook 2010 and 2013.

TimeCard has been on the market for thirteen years, and even if it has been updated many times we have not incorporated the latest and best technologies. Now we are doing that, so we can look forward to many more years with TimeCard and to many more versions.

Excel statistics
Version 5 of TimeCard Single will have a new statistics tool that I hope most users already know – Excel. That way new users will not have to learn a new tool for the TimeCard statistics.

A number of dfferent reports will be included, and custom Excel reports with data from the TimeCard Access database can also be created.

Still some work left
We are not yet ready to release version 5 of TimeCard Single, but the development is progressing well and we have begun updating the documentation. I will soon come back to tell you more and maybe show you the new slideshow.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Free Office 365 + Free kalmstrom.com Software = More To Good Causes

Bill Gates is one of the world's top donors to charity (I wish I could afford that!)  so I am not surprised that Microsoft has decided to give nonprofit organizations free licenses of the cloud platform Office 365. But I surely welcome the initiative!

Technology for Good
The free Office 365 is part of Microsoft's "Technology for Good" program, and SharePoint, Exchange, Outlook and Excel are among the included programs. Only the cloud platform is free, but the desktop version is licensed to nonprofits at less than a quarter of the normal price.

Who is eligible?
Microsoft donates Office 365 to organizations that are recognized as charitable in their respective countries. They must also operate on a not-for-profit basis and have a mission to benefit the local community. Educational institutions are not eligible, but schools and students are offered discounts on the Office 365 subscription fees.

The program is available in 41 countries, and Microsoft says it will be extended to 90 countries all over the world  by July 2014.

Free kalmstrom.com applications
I have actually done – and am still doing – something similar. When I begun buildning my company, I offered free licenses to schools and charities, and the feedback I got from these customers during the early days were valuable to the continued development of my Outlook add-ons.

Eventually, as the products were improved and word about them spread, the demand for my free applications became overwhelming. kalmstrom.com Buiness Solutions certainly don't have the same administration capacity as Microsoft!

Second application still free
We had to stop donating our products, but. instead we are giving schools and charities a 50 % discount on the subscription fees for the first kalmstrom.com Outlook or SharePoint solution they are using.

For Premium subscribers the second kalmstrom.com solution is still free, just as the third one, and we give full support and free upgrades also for the free product(s). This offer is valid for Premium Members of the kalmstrom.com Community, not only for the schools and charities, but those organizations can get extra good value for money by combining the 50 % discount and the free products offer.

Now the nonprofit Community Members can use Office 365 for free too. As they are already using discounted and free kalmstrom.com solutions only small parts of their budgets must go to software licenses – which means more money to charity. It feels good helping others do good!

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Excel Scenarios In Two Ways In New kalmstrom.com Tutorial

The kalmstrom.com Tips section has a new tutorial about how to show different scenarios in Excel. I recommend using the Scenario Manager to display various options.

Appropriate caption
Microsoft has put the caption "What-If-Analysis" on the button we should use for scenario calculations, and this is really to the point. When we create scenarios we are asking Excel "What will be the result if this or that happens?".

Identify cells that might change
To create various scenarios for your data you must identify what cells in a table have values that can vary in different scenarios. These are the cells you should use in the Scenario Manager.

Examples
A company might need to now how much they will save if their sales representatives stay at cheaper hotels when they are travelling. Or maybe they want to give them more luxury accomodations? How much would that cost?

In a private budget you can do the same. What if I reduce the amount I'm spending on clothes? How much will I have left then, at the end of the month?

In my demo I show an example with housing costs, the same as I used in the tutorial about Excel formulas that multiply and divide. What might change here is the interest rate and the taxes, so I select these cells to build a worse case scenario.

Save and edit
Scenarios can be saved, so that you can apply them later with a click of the mouse. You may of course also edit them when needed, and it is easy to add new scenarios to the Manager.

The copy method
In the demo below I am also showing another way to save scenarios: copy a worksheet and change the relevant figures. This method works if you only have two or three scenarios, but if you have more the Scenarios Manager is a smoother solution. And even if you start with only two scenarios you will probably want to add more later, and that is also a reason to use the Manager from start.

In the kalmstrom.com Tips section you can find more tips and pointers, each with an introductory text. Welcome to browse around among the other Excel demos!



By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

New kalmstrom.com Office For Expanding Team

The members of the kalmstrom.com team live and work in three countries: India, Spain and Sweden. The work group in India is expanding, so our Indian colleagues have moved into a bigger office.

Central location
The new kalmstrom.com office is situated in the well known Shalimar Corporate Centre in the very heart of Indore. This is highly appreciated by the team members, as Indore is a city with over 2 million inhabitants.

"Everything is near," says Jayant Rimza, kalmstrom.com Lead Developer and Director of the Indore office. "Railway and bus stations are close by, and there are plenty of parking places."


Security and prestige
Office Manager Vipul Dindulkar likes that the building is very secure and that the prime location is impressive to visitors and will benefit the recruitment of future employees.

Vipul is the one who has found the new office and organized the move, and he has done a good job. The move was so smooth that evaluators and users of the kalmstrom.com products didn't even notice it.

Inspiring surroundings
In the same building as the kalmstrom.com office there are several other companies of good reputation. The surrounding area has many IT companies and also institutions for higher education. So, our colleagues have moved not only to a bigger office but to a more stimulating environment as well.

"I really feel proud working here", says Jitu Patidar, Senoir Developer, while QA Engineer Ankita Kunchal claims she feels happy and gets "positive energy to work in such an environment."

Spacious office

The office itself is spacious and well kept and furnished. There is plenty of room for work stations and also a good sized lunch room, a pantry and a reception.

One of the rooms will be used for team sessions on development projects, and I am looking forward to using it for lectures and sessions when I visit Indore.

Better results
 In the image above you can see the QA team in their area of the new office. "This office will help us increase our productivity", predicts Lead QA Vijayant Rimza.

His workmate Neha Gupta Kaushal agrees and points out that the new office is silent. Even though it is situated in the city centre they are not disturbed by noise from outside the building.

Celebration dinner
As you understand the Indore team had reason to celebrate when the move to the new office was finished in August, so when everything was in order at the new place they went out to celebrate.


I have not yet visited the new office, so what I am telling you here comes from my chats with the Indian team members. But I share their joy and feel glad we can give our skilled and dedicated Indore staff this enhancement of  working conditions. They are certainly worth it!

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

A SharePoint List RSS Feed

An evaluator of Kanban Task Manager for SharePoint asked how to share the team tasks with others in the organization via an RSS feed.

We made a demo to explain how, and as we think more users can find this information useful it has now been published on the Kanban Task Manager for SharePoint Demonstrations page.

Same for all lists
Even if the tutorial shows how to create an RSS feed for the Kanban Task Manager task list, the principle is the same for all SharePoint lists. Therefore you might benefit from watching the demo even if you don't use the kalmstrom.com solution for task and project management.

Drag and drop tasks in SharePoint
Kanban Task Manager uses Outlook Tasks or SharePoint Tasks – or both, as the two versions can be combined.

On a virtual kanban board in Outlook or SharePoint, color coded tasks can be dragged and dropped between phases as the work process proceeds. This gives a good visualization of how projects and tasks are developing.

One subsite per team
Most organizations who use Kanban Task Manager for SharePoint prefer to activate the solution on one subsite per work group, so that each team has its own Kanban Task Manager.

On that site they will have the kanban board, the settings lists and everything else needed to use Kanban Task Manager. The site may of course also be used for other things that should be shared within the same team, like documents and calendars.

Share the kanban board
When all the Kanban Task Manager SharePoint subsites have the same top navigation, managers can easily go from one subsite to another to supervise the work.

However, in some cases managers prefer other ways of viewing the tasks, or maybe other members of the organization need to read the tasks of one or more workgroups. Then you can use an RSS feed.

The KTM Tasks list
The visualized tasks are displayed on the kanban board page, and that is where team members work with them. The kanban board page gets the tasks from a standard SharePoint list, KTM Tasks, and this is the list you should map with the RSS reader.

HelpDesk OSP
What I have advised above is valid for all task lists, so if you have connected Outlook to a SharePoint Task list with HelpDesk OSP and want to share that list with an RSS feed you can follow the same process: allow RSS feeds in the site collection as well as on the site, set text over 256 characters to be truncated and allow the RSS feed.

Outlook default
Outlook is the default RSS reader for SharePoint, and in the demo I show the 2013 versions of both. Outlook and SharePoint must have the same version number for the RSS feed to work, so if you have a SharePoint 2010 site you must map the feed to an Outlook 2010 installation.

Should you prefer another RSS reader, make sure it supports feeds from SharePoint. If it does, it will work with the your SharePoint lists also.

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

New Obama Effect After Stockholm Visit

Last week the US president visited Sweden, and I wrote an article about Barack Obama's interest for our energy inventions. I also mentioned that the Swedish capital was cleaner than in many years, but I could not imagine the extensive effects this would give in Sweden after Mr. Obama had left the country.

30 % less pollution
During Barach Obama's visit to Stockholm parts of the capital was blocked off for cars and buses. That gave eye-opening results, because the  streets of Stockholm's inner city had an average of 30 % less emissions of nitric oxides than usual, according to the City Commissioner for Environment and Traffic.

Enthusiasm in social media
Stockholm inhabitants were happy to be able to walk around freely in the inner city, and many gave voice to their enthusiasm in social media and suggested more days without traffic or with just commercial traffic. "Can we make Obama stay, to keep our city clean?" was one comment.

4 of 7 positive
The leading Swedish newspaper Dagens Nyheter asked representatives for the seven political parties about their opinions on a day without traffic in Stockholm. Four of them were positive, even if several pointed out that more most be done to solve our environment problems.

The Green Party representative said that the Obama visit showed what a fantastic city Stockholm can be. They don't only want a day without traffic but also a walking area right through the inner city once a week. And the Left Party even wants the city centre to be permanently closed of from all traffic but the commercial.

European Mobility Week
Many other cities all over the world already have a traffic free day on 22 September, when they take part in the European Mobility Week campaign organized by the European Commission. It culminates with a day when certain areas in the participating cities are set aside solely for pedestrians, cyclists and public transport.

The idea has spread far beyond Europe, and in 2012 no less than 2158 cities from 39 countries registered for the campaign. I have experienced the day in Malaga, and it was fantastic to get rid of the heavy traffic – if so only for a day!

This year I will be in Stockholm on the 22 September. I wonder if I will be able to stroll along the beautiful watersides, listening to seagulls instead of car engines and breathing clean air?

By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions

Calculate and Compare Costs In Excel - New kalmstrom.com Tip

Excel is an excellent program to use if you need to calculate de costs of different alternatives. Excel formulas make it easy to see how figures change when new options are selected, and with the Excel charts you can visualize the figures and compare them more easily. I am showing this in a new demo about Excel calculations in the kalmstrom.com Tips section.

Flooring costs
In my example I calculate costs for different floor materials in an imaginary house renovation. The house has thirteen rooms of different sizes, and there are five floor materials of different costs to choose among.  In my Excel table I can see how costs varies depending on floor material, for the room as well as for the whole building.

Many tips
In my tutorial I show how do create the formulas needed, and in the process I also explain other useful things about Excel, such as

• Set number format
  
• Create a multiply formula
  
• Name an array of cells
  
• Create a vertical lookup formula
  
• Use the AutoSum button
• Copy format
• Create a data validation rule

Pivot chart
Finally I create a pivot chart where I can see more clearly how any changes I make in the tables affect the proportions of the flooring costs for the different materials.


By Peter Kalmstrom
CEO and Systems Designer
kalmstrom.com Business Solutions