Formula Recipe:Random order list

To transform a given list of item in random order, the following formula could be used:
RAND - for creating random number
ROW - used with RAND to generate random number
LARGE - To match out the list from ranking.
VLOOKUP - used with LARGE for locate exact position of item

Assume the list of Target is located in column B


Step1: add formula in A2:
=INT(RAND()*1000)+ROW()/10000
the "ROW()/10000" is used to prevent duplicated random number.Even the rand() result is 0, the default ranking would be row number. The 10000 is the possible number of items in the list.

Step 2: add formula in D2:
=LARGE(A$2:A$21,ROW()-1)

To generate sorted list of rank
Step 3: add formula in E2
=VLOOKUP(D2,A$1:B$21,2,FALSE)
Based on the sorted list of rank, look for the sorted list of targets.

Step 4: extend the formula in A2,D2 and E2 to number items by copy and paste

Finish!

Excel 2010: Print Preview Full Screen (Old Print Preview)

For Users

From menu controls, the new print preview mode has completely replaced the old print preview.
The new print preview mode offer a streamlined "quick printing" in one screen:
Selecting printer, number of copies, paper orientation,... All in One

However, convenience control "margin manual tuning" (as below) is missing from the new mode.

Actually, the old print preview is still not removed.
You can add it to the quick access bar, or any ribbon (new ribbon customization feature in Office 2010)

The below steps demonstrate how to release the old print preview (Print Preview Full Screen) method to the quick access bar

Step 1:Click on the more 

Step 2:

Finish, Click this button to launch the old print preview screen:

---

For Programmers

If your existing application are accessing the old preview box.

you can still access the old preview mode. It's also not inside the 2010 object model change list:

ActiveWindow.SelectedSheets.PrintPreview

Microsoft Certified Application Specialist(MCAS) credential change

According to wendyj, the Microsoft Certified Application Specialist (MCAS) will be rebranded to Microsoft Office Specialist (MOS) at June/2010.

It seems that the new brand (MCAS) like MCITP doesn't heard by many employers.
Many of the employer's still only knows about MCSE but not MCITP (We can find it out from the recruit).


Besides, there are far too more MOSs than MCASs in the world.

Anyway, there will have new logos for the MOS.
About the old MCAS certificates or cards with MCAS logo, you may use them for lighting up the BBQ stove. :)

Office 2010 Code (VBA) Compatability Inspector

To check whether existing VBA codes supported by Office 2010 or not,
instead of re-compiling the VBA codes, Microsoft now offering a new inspector for you.

Gray has written a short guide about on how to use the inspector.
If your macros are password protected, remember to unlock it before launching the inspector.

However, this tools is still in beta. I have encountered serveral false alarm about "Range" objects.

As the tools are in beta, you can send any feedback to the development team.
There is also a sweeptakes on bug reporting.

MCAS Practice Test Excel (70-602) Suggested Solutions

(Last Updated 2010-02-28)
Belows are the suggested solution of the practice test.
The steps below is not the only answer.
There may have other ways or even shortcut leading to the same result.

Part I Q1-Q10 Part II Q11-Q20 Part III Q21-Q25

Question 1.

1. Remove the duplicated entry of Vendor Name(Column B) from the range "A3:K159".
Solution:

1. Highlight the range "A3:K159".
2. Data Ribbon > Data Tools Group > Remove Duplicates
3. Make sure that ONLY the box of "Vendor Name" (or Column B) is checked. (Uncheck all others) and then click on OK.

2. Name the cell range "B4:B107": Vendors.

1. Highlight the range "B4:B107".
2. Formulas Ribbon > Defined Name Group > Define name.
3. Enter "Vendor" in the Name box and then click on OK.