Spy Journal Excel Tips

Wednesday, June 29, 2005

Sharing Excel Workbooks using FTP

Basically, to share the files like in a LAN environment, you need a LAN network.However, when the distances between bases is away, it costs a huge for the installation of LAN net work. So generally, a software for sharing files would be used. But there is a way to do this without extra cost if you have your web space (Intranet or Internet). You can upload Excel workbook and you can share the files using FTP location folder. In this page, I would introduce you how to make a FTP Location folder and how to use it to share files. No VBA code necessary.

I have noticed some issues with sharing workbooks on a LAN. The most obvious one is the version control issue; that is whose changes are saved when these may conflict?

However some other issues I have found in the last couple of weeks include the inability to edit comments while in shared mode, even if there are no other users using the file, and also the inability to edit pictures other objects while it is in shared mode. My solution initially was to unshare the workbook, and then make the changes, which then worked. However this seems like a pretty sillay way to solve it.
Does anybody out there know any other way to make these types of changes without having to remove sharing?

Also if you have a go at Colo's shared workbook or are able to create your own share using FTP then please write your findings and thoughts in the comments here, or email me if you are on the email list.

Monday, June 27, 2005

Comments in Excel

Comments can be added into excel that can be rolled over (hold mouse over the cell) to view. However there is a quite a lot more that you can do with comments than simply type information in them.
You can resize the comment by dragging the handles
You can format the text in it by selecting he text and then using Format | Comment or the buttons on the tool bar.
You can make comments always show, or only show on roll over. Right click a cell with comments and choose Show / Hide Comments.
Additionally you can add pictures into the comments. Charles Maxson posts on how to do this.

Wednesday, June 22, 2005

Using the CELL function in Excel

The CELL function can be used for filename identification on a spreadsheet by using the syntax =CELL("filename") This is easily entered on a sheet by hitting the fx (function) button on the toolbar and typing filename in the info type box and hitting enter.

However this may not always show the file name for the current file on which the formula is entered.
There is a reason for this found in the help files and I have added a solution.

The correct syntax is CELL(info_type,reference).
The reference field is the cell that you want information about. If omitted, information specified in info_type is returned for the last cell that was changed.
If you change a cell in a different file, then go to the file you have the formula in and print it, then it will display as the other file name, which is obviously incorrect.

The solution is to enter A1 in the reference field like this =CELL("filename",A1)

This is a good method for people using Excel versions prior to 2003 to display the full path of the file. In Excel 2003 there is an option in the print settings to put the path and file name into the footer or header in the Page Print settings. However in versions previous there was no path option.

Monday, June 20, 2005

Shading and Borders in Excel

I have said it before and I will say it again, presentation is the most important thing about spreadsheets, other than auditing for correctness in the results created.
The use of colour, borders and shading can turn a drab report into a glittering example of spreadsheeting and win you accolades from your boss and clients.

Here a few ideas that I use.

Front pages, header pages and menus.
I always use a single background colour for the entire sheet and a contrasting bold font colour. Usually I will put dark colour text on a lighter background as this is both easier to read and prints better in black and white if necessary.
To make the menu options, or selections easier to see and use I will make the columns wide, the rows wide and center the text vertically and horizontally and wrap the text in the cells. I often use a contrasting thick border around the cells with text in them, using the same colour as the font, or maybe a darker or lighter shade of the same colour. If I put macro buttons into the page I will contrast these colours also.
The main point is to create a visually aesthetically appealing page with contrasting colours and easy to read text and instructions.

Data entry and calculation pages.
Where the user is required to select options from drop downs or enter data into cells I will create this page in as visually appealing way as possible without sacrificing fucntionality of formula construction.
I always create a standard data entry format, drop down format, and formula and "dont touch" areas. Where possible these calculation and "don't touch" areas are on hidden sheets, or on their own sheet. If possible these are also locked for further security.

The main two things to remember are:

What will it look like when it is printed? and
Will someone else other than me be able to intuitively operate it?

Friday, June 17, 2005

This is an excellent example of a CSE formula one from Exceltip.com (Formula entered with CTRL SHIFT ENTER)

Problem:
Range A2:C10 contains the login and logout times of various ID's.
Each ID could log in and out a number of times a day.
How could we find the first time a specific ID logged in and the last time that same ID logged out?

Solution:
For each of the ID's in range A2:A10, enter two Array Formulas.
To find the first login time (Column B) enter the following formula:
{=1/MAX((A14=$A$2:$A$10)*($B$2:$B$10<>0)*(1/$B$2:$B$10))}
To find the last logout time (Column C) enter the following formula:
{=MAX(($A$2:$A$10=A14)*($C$2:$C$10))}
(Don't forget to hit CTRL-SHIFT-ENTER when edioting or wthis formula)

ID______Login Time______Logout Time
1 ______02:40___________03:10
2 ______00:15___________03:20
1 ______06:20___________09:30
3 ______09:14___________11:05
4 ______11:00___________19:30
2 ______04:05___________06:55
3 ______12:08___________17:17
1 ______10:00___________16:20
2 ______08:12___________12:33

ID______First Login Time______Last Logout Time
1 ______2:40__________________16:20
2 ______0:15__________________12:33
3 ______9:14__________________17:17
4 ______11:00_________________19:30

Thursday, June 16, 2005

Separating First and Last names without using formulas

I used this to help a client do exactly this the very next day after reading it on Exceltip.com

To separate first name and last names:

1. Select Column A or the range of cells containing the list of names.
2. From the Data menu, select Text to Columns.
3. In Step 1 of 3, select Delimited.
4. In Step 2 of 3, select the Space checkbox.
5. In Step 3 of 3, in the Destination box, select cell B1, and then click Finish.

It worked beautifully!

Tuesday, June 14, 2005

Sum all item prices except one

Todays Tip from Exceltip.com

Problem:
Columns A:B contains clothing items and their matching price.
How could we create a formula that will sum the prices in column B of all the items except the jacket?

Solution:

Use the SUMIF function as follows:
=SUMIF(A2:A6,""<>Jacket"",B2:B6)

Items____Prices
Shoes____100
T-Shirt__20
Jeans____50
Jacket___150
Socks____5

Result 175

Note: This method can be used with the less than < and greater than > keys also for numeric exclusions.

Saturday, June 11, 2005

Follow up to Footy Tipping Competition Ranking problem.

From Jon Peltier

You could cheat, and add a helper column with a formula like this:

=E2+row()/1000

This way, duplicate integer values are different out in the decimal digits, and you eliminate ties.

From Mark
Rank when not in order.xls

This was a good problem.

There is a much simpler solution, not requiring RANK, when the items have been sorted first : IF(G2=G1,F1,F1+1) - refer to column F in the attached workbook.

The formula when the items are not sorted was a bit harder, and required a bit of lateral thinking. I think you will like this solution - refer to column A.

The solution, not surprisingly, requires a CSE formula. We assume that we are ranking from highest number to lowest. (CSE requires CTRL-SHIFT-ENTER when editing or writing the formula.

=SUM(IF(RANGE>B1,COUNTIF(RANGE,RANGE)/COUNTIF(RANGE,RANGE)^2))+1

What the formula does is for each time any other number is larger than the number in the current row, it counts the number of occurrences, but then (and here's the maths coming in), it divides this number by its square. The formula then sums all these numbers and adds one.

Why it works is that if a larger number is found, say, 3 times, on each occurrences of that specific larger number the formula is adding 3/3^2, that is 1/3 - so the 3 occurrences add to 1. Similarly, if there are 4 occurrences of a specific higher number you are adding four 1/4, again equalling one.

The result is that the formula calculates the number of unique numbers that are higher than the number in the current row. The 1 is added to get the actual number ranking you need.

Thanks guys for your input.

Friday, June 10, 2005

Using Conditional Formatting in Excel

Conditional formatting can be used to great effect in spreadsheets to compare results to expectations and visually illustrate variances.

For example a spreadsheet of dates (eg birthdates). Let say you had a list of dates in column B and you wanted to see visually in the sheet whether or not a date was before or after today. All dates that have passed so far this year could be light green and all dates including today that have not come yet can be bright yellow.
A conditional format is the way to do this. In fact we need two.
Select the first date (in cell B1 in this example) and then click Format | Conditional Formatting.
In the first condition select Formula and then type this formula =B1< TODAY() and then select patterns and light green.
Now click Add to get the second condition and type this formula =B1>= TODAY() and then select patterns and bright yellow.
Now Copy and Paste Special As Formats over the rest of the cells. They should now all be either light green or bright yellow depending on the date in relation to today.

Note: Conditional Formats override existing formats in a cell if their conditions return True.

Tuesday, June 07, 2005

Footy Tipping Rankings in Excel

A subscriber asked me today how to manage the rankings for their footy tipping comp in Excel. I suggested RANK as the function to use. However it was not quite so simple as that.

For example if you have 3 people on equal first place then they are all ranked 1 and the next place is ranked 4th. In this case they wanted to show the following places as second and third etc regardless of what the actual ranking was.
After some discussion with Mark (the footy tipping guru) I came up with the following formula - illustrated below.
=IF(RANK(E2,$E$2:$E$14)>RANK(E1,$E$2:$E$14),F1+1,F1)

This worked for every cell except the first where I used the formula =RANK(E2,E2:E14).
If I wanted to be real smart I would add in an IF statement testing for the first cell in the list as follows:
=IF(ISNUMBER(E1)=FALSE,RANK(E2,$E$2:$E$14), IF(RANK(E2,$E$2:$E$14)>RANK(E1,$E$2:$E$14),F1+1,F1))

Thursday, June 02, 2005

Macro Security

Most installations of Excel by default set the security level for macros on High. This means most macros will not run. When opening an Excel workbook with macros in it a message will pop up indicating that the macros have been disabled.
When this occurs you need to reduce the setting to either Medium or Low. Do this by clicking Tools | Macro | Security. Choose the Security Level you need and click OK. If you had already opened a file and need to use the macros then you will need to close and reopen it.
Once this setting has been set on your PC it will remain set for that user until you change it again.

Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Jethro Management (c) 2004 unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission.

Unless otherwise expressly stated, all original material of whatever nature created by Tim Miller and included in this weblog and any related pages, including the weblog's archives, is licensed under a Creative Commons License.