Shortcut to Last Worksheet used

The Excel Help Desk received the following request for help....Is it possible to define any shortcut (key sequence) in order to be able to quickly switch between last used sheets which are not next to each other - preferably using only left hand

Read More »

Lookup for Product Effort Timesheet

For this problem we received a copy of the workbook. We then used a formula to count the number of 'Product' occurences for each staff member. That count was then transferred to a staff member summary worksheet to report the total counts for all 'Products' across the team. To simplify the formula and the structure of the workbook we also recommended to record all the staff time into one worksheet for the year, rather then a separate worksheet for each month

Read More »

Calculate SUMIF for Activity Log

For this problem we received a copy of the workbook. We then used a formula to calculate the number of hours between A and B for the activity. Lastly we created a formula to SUM all the activities for the daily log.

Read More »

Text not Converting to Date

For this problem we received a copy of the workbook. We then used a formula to convert the original birth date text into a "DateValue" and finally format those cells as dates to create a column with the actual Birth Dates.

Read More »

Worksheet Custom Sort

For this problem we received a copy of the workbook. We then used a formula to extract the last two digits of the text string. From there we sorted the worksheet by the extracted digits to acheive the result. See the post for screen shots of the un-sorted list, the formula used and then sorted list.

Read More »

Find Duplicates in Multiple Worksheets

What is the best way to find duplicates in a workbook with over 40 worksheets? This workbook has over 5,000 names separated into different groups (worksheets). We use this list as a basis for invitation lists. We usually have over 100 names duplicated and are trying to find the best way to highlight the duplicates - we have tried many different methods in the past, but none that finds all the dupes.

Read More »

Refer to Sheet Name in Cell Formula

A reference to another sheet always includes the sheet name (Sheet1), an exclamation (!), and the cell reference (I6). In this case we want the sheet name to be a reference from another cell in the worksheet and that whole reference to then be calculated. In each case the end of the reference is to the same cell "$I$6" which is the location of the Password in each worksheet. Then in front of that reference we want to insert the worksheet name. We need to get a combined string with the worksheet name and the cell reference, to acheive this we can use concatenation.

Read More »

Annualized Standard Deviation

Annualized Standard Deviation - I have quarterly performance returns for a portfolio and I believe I got the annual standard deviation for 2006, 2007, 2008, 2009, and 2010 using Excel. The next thing I need to do is get an 5 Year Annualized Standard deviation number and I have no idea how to do it. Any help would be appreciated.

Read More »