Word - Auto Number in Table

Did you know, you can add auto numbering within Table in MS Word?
You have no need to adjust numbering when you insert or delete row, auto numbering will manage it for you.

Let's try

1. Create a table, click at first top left cell and click at Numbering



2. Highlight the numbering cell and click at Format Painter.



3. Paste Format to next cell in row 2 and row 3.



4. You will get auto numbering for row 2 and row 3, the number is continuous.



5. Now click at row 3 and Insert Row Below it.



6. You will get new row with auto number



7. Now you try to insert row in between and remove some row, you will see the auto numbering will adjust the number for you.

:)

Reporting Services - RowNumber & RunningValue

In Reporting Services, if you want to display Record Number in your report you can use RowNumber Function.

From sample image below the first column (No.) is using RowNumber Function
Syntax
RowNumber(Scope)

Scope - Name of dataset, grouping, or data region



Specify Expression as following:-
= RowNumber("TravellingExpense")

* TravellingExpense is my dataset name



Another function is RunningValue

Syntax
RunningValue(Expression, Function, Scope)

Expression - The expression on which to perform the aggregation and cannot contain aggregate functions

Function - The name of the aggregate function to apply to the expression except RunningValue, RowNumber or Aggregate function

Scope - Name of dataset, grouping, or data region

Notice that I want running number of those different Initial Value (Same Initial will have same No.)



I can do that by right-click at Running Number cell -> select Expression...



Specify Expression as following:-
= RunningValue(Fields!Initial.Value, CountDistinct, "DetailIntercharge")

* DetailIntercharge is my dataset name



Further more, I do not want display duplicated Running Number.



Just right-click at Running Number cell again, select Properties



Check at Hide Duplicates and select Containing group or dataset to DetailIntercharge (your dataset)



That's all !!

Excel 2007 - Conditional Formatting

Did you know, you can create a condition to change formatting autometically in Excel?
I just know it for last 2 years. (Thx Mr. Wu fooshen to teach me)

Let's make use of useful feature from now.
I will give you a sample data of Loan Rate for Commercial Banks in Thailand.
I would like to highlight those Loan Rate that above average to red and below average to green.

1. select Cells you want.

2. Click at Conditional Formatting button



3. Choose Highlight Cells Rules -> Greater Than...



4. Select Cell value to compare with. I select average cell value



5. Define Format for those greater than average value, you can select pre-defined format or also create Custom Format for your own



6. Click OK, you will see those greater than average were highlight :)



7. Now is another way to add/edit/delete conditional formatting.

8. Go to Conditional Formatting -> Manage Rules...


9. You would see created rule here, now click New Rule...



10. Create another rule for less than average by select a Rule Type to Format only cell that contain -> Cell Value less than [average cell value]

11. Then click at Format... button to define format for those value less than the average.



12. You would see now there are 2 created rules.



13. Here is result, it is very easy to find which bank has Loan Rate below or above the average.



14. In another way, usually I use conditional formatting for tracking my team's job.
I create conditional formatting for each job's status.



15. Then Create List to let me select each status from Dropdownlist. (Create List in Excel)



16. When I change status for each issue, color or formatting will change for me autometically :)


enjoy Excel !!!

Excel 2007 - How to separate text to columns

When you open .csv file by Excel and that .csv file did not use comma as separater, you will get all text are in Cell A.

Here is how to separate those text to columns

1. Select Cell you want to separate text
2. Go to Data menu -> Click at Text to Columns button



3. Choose Delimited file type and click Next > button



4. Specify Delimiters or separator here and click Next > button



5. Select Data Format for each column, and click Finish button



6. Here they are!!!

SharePoint 2007 - Group by vs Order in custom list view

I would like to create a custom list view to display in tree view (allow expand and collapse) and also want to specify ordering of each item myself.

But... the ordering sequence does not follow the setting. Item will order by Group Name which is text ordering.

I try to create a calculated column to concatenate of sequence (number type) with title.
It does not work because of Text ordering 10 is less than 2 !! ( 1, 10, 100, 2, 3 ,4 ,5)

Let create a situation that I mention above

1. at First, create a custom list with structure below

2. Enter data for testing like this.

3. Create a view and configure as following:-

View Name: Tree View
Sorting: Sequence
Group by: Display Name

4. Choose Tree View, you will get display like this, see !! it's wrong ordering !!!

Here is solution for this

1. Go to List Setting to modify calculated column formula

2. Use below formula to add white space at front of sequence number

=REPT(" ", 3-LEN(Sequence)) & Sequence &". "&Title

3. Go to refresh custom list view

4. here you go !!

SharePoint - List of Formula for calculated column

When I create a calculated column in Custom List, I curious what formula can I use???

and I found the list here...

http://usa1021.wss.bcentral.com/_vti_bin/help/1033/sts/html/wsapusgm.htm

Excel 2003 Tips - Create List in Excel (Advance)

My last post is to create list in Excel also but it can use at one wooksheet.
This topic is to create only one list and can be used for every wooksheets in same Excel file.

Result you get is the same, you still get Dropdownlist like figure below.



Let's change step a bit for more advance

1. Go to your value listing, select source range by hi-light cell D2-D4 for example.


2. Name it as ListName by click at Name Box (top-left, at front of function box), type "ListName" into Name Box then click OK.

3. If you select other cell and you back to select the D2-D4 again, you will see "ListName" appear in Name box, that means you defined Naming successfully.

4. Modify Validation source to "ListName" by select Data -> Validation...,
Change data validation from selected range to "=ListName", then click OK.

Now you will get result same as basic way. Let's see the differrent.

5. Click at Sheet2, select entire column B and go to Data -> Validation...

6. Select Validation criteria Allow List and define Source to "=ListName", then click OK.
Now you can use value list anywhere in workbook.

7. To modify area of value listing, Go to Insert -> Name -> Define

8. Click at ListName and change the value range in Refers to:

Have fun!!

Excel 2003 Tips - Create List in Excel (Basic)

Normally there are 2 ways to create list in Excel that I use.
My first blog starting with the easy way :)

Here is what I want...
I can select a value from Dropdownlist only.

Ok, let's start on how to create above list step-by-step.

1. You have to prepare the value in Dropdownlist first. Just type all List values you have.



2. Select entire cell that you want to place Dropdownlist, my sample image use Column B as target cell. Then go to Data Menu, select Validation...

3. At Data Validation windows, select Validation criteria Allow List and define Source to the list values you prepared, then click OK.

4. Finished!!, you can do it :)

in this way, list can use only in the same sheet with data source.
Next post, I will describe how to create a list that can share to entire workbook.