Remove the external links from an Excel File.

Sometimes its so happens that when you open an Excel file, it asks if the External Data needs to be updated or not. But you may not have intentionally given any external links. It’s an irritation then. But How to get rid of it?

Most likely it could be because of a Formula or a Named Ranges, although there are other possibilities as well.

1. How to identify the problematic Formula.

Goto the menu "Edit > Find" ( or press ) and enter as below and click "Find All".



2. How to identify the problematic Named Range.
Look for a Named Range that is linked to an external Workbook.

I do it this way.

go to an empty portion of the worksheet.
go to the Menu "Insert >> Name >> Paste" and click "Paste List".
Now you can manually search or use the method 1 to identify.

For the full info, click here.

Excel Function :TABLE

Its been a while since I have done some posting. This option in excel is something which I thought would be very useful for many.

Its the TABLE Function in MS Excel. This is especially useful in the following scenarios.

Assume you have a very lengthy calculation to arrive at a value. And you want to see how one (or two) input variables affect the output. One way to do this is to have each calculation in adjacent columns and populate the rows with varying input value. Also, if you want two inputs to be changed, its even more difficult. Using TABLE, you can do this very easily.

I even remember that I had written a whole set of VBA codes to manage what TABLE could do!

Enough of introduction....



(A) How to do a 1D TABLE (one variable is changed).




Step 1: Basic Calculation

Setup your basic calculation with the variable value in separate cell(s).
In the Eg, Input is in [C2] and output is in [C3]


Step 2: Prepare the Table

Prepare the frame for the Table. Different possible values of the variable is arranged in a column ( assume [E3:E10] ). Immediate to the right of these values ( [F3:F10] ) will be the place for the results. Link the result cell of Step1 to the cell just above it [F2] (for eg, =C3).


Step 3: Populate the Table

Select the full table. ( [E2:F3] ).
Go to Data -> Table.
Select the input variable cell as Column Input Cell ([C2])
Select OK.

You are DONE.

(B) How to do a 2D TABLE (two variables are varied).





Step 1: Stays the same.

Step 2: Populate the possibilities of variable 1 in the 1st column and variable 2 as 1st row. The output cell should be linked to the Top-Left Corner.

Step 3: Select the two variable reference cells in the dialog. Click OK and you should be DONE.

If there is some mismatch, please repeat the step 3 with the reference cells interchanged.



Hope its useful.. Any questions?, let me know...

Treeview Structure in VB.Net

I am using Visual Basic 2008 Express Edition.

In one of my earlier posts, I had explained in detail about Treeview in Excel-VBA. Through this post, I would like to bring the VB.Net way of the control.

1) get the Control:

From the Toolbox (Common Controls tab), drag the treeview onto your form.
For convenience, I have named it - TV.

2) Build the Tree up:

You build tree by adding nodes as child of another Node (or as a child of the TV).

Special things to note for a Node.

You can specify an image for a Node.

You can attach an Object to each Node through the TAG property of the Node. This is something which I found really useful.

Node Object : TreeNode

Adding a node:

You can add a node to the TV.

'--- Start ---
Dim iNode As New TreeNode
Dim iObj as New MyObject

'Define iObj...

iNode.Name = "Name1"
iNode.Text = "Root Node 1"
iNode.Tag = iObj

TV.Nodes.Add(iNode)

'--- End ---


Or You can add a node to an existing Node.

'--- Start ---
Dim iNode As New TreeNode
Dim pNode as TreeNode
Dim iObj as New MyObject

'Define iObj...

pNode = TV.Nodes(0) 'Zero is the index of the Node at that Level.

iNode.Name = "Name2"
iNode.Text = "Child Node 1"
iNode.Tag = iObj

pNode.Nodes.Add(iNode)

'--- End ---



Removing a node:

TV.Nodes(0).Nodes(0).Remove()

or

iNode = TV.SelectedNode 'or Get the Node of the TV as an Object.
iNode.Remove()




Clearing all nodes:

TV.Nodes.Clear()





3. Usual properties of Nodes:

You can refer to a node with its Index.

'Gets or sets the Text
TV.Nodes(i).Text

'Gets or sets the Name of the Node
TV.Nodes(i).Name

'say iNode = TV.Node(0).Nodes(2)

'Gets the Index of the Node at that Level.
iNode.Index

'Gets the No of children the has got.
iNode.Nodes.Count

'Gets the First Child Node and the Last Child Node
iNode.FirstNode
iNode.LastNode

etc....



more, later some time...

search and find all supressed features in proe

How to find all supressed objects in Proe (Pro Engineer) easily.. Yes, its easy.. no manual search is needed... Thanks to to ProE seach method..

Go to Edit -> Find and the rest is in the picture.



Select all the items in the left list, add all to the right, Click Close.

Now you can do what ever you want with it. Say delete all at once!

This is also applicable for the assembly models. Make sure that the check box "Include Submodel" is clicked. By default it will be checked anyway. Try and let me know.


NOTE:
We have used option in some other way as well. We normally rename the dimension symbol to some meaningful text and the same name will be coming in different parts when it represents the same data.

What if we want to change one such dimension in all parts. Use this option. Search for Dimension. In the attribute tab, put the dim name and it gives all such dims. Select all. Close the window. Now you can modify all such dimensions at once. Regenrate and you are done.



Hope this helps....

.

Proe Function / Relation to round the decimal places

Its strange that ProE does not have a ROUND function. At least I counldnt find it.

It has two funtions.
Ceil will always round it up.
Floor will always round it down.

I have used both to get the ROUND functionality.
here is the relation


X is the original value.
D is the number of decimal places.
X_New is the rounded value.

X = 1.334
D = 2

X_ = X * 10^D
XFLOOR_ = FLOOR(X,D) *10^D
IF X_ - XFLOOR_ < 0.5
X_NEW = FLOOR(X,D)
ELSE
X_NEW = CEIL(X,D)
ENDIF


try and let me know if it works.

How to use VLookUp in Microsoft Excel.

Tool : MS Excel.
Topic : The function - VLOOKUP.



This is a very useful function to extract data from the table.

Microsoft says..
"Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table."

The format.
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

where..
lookup_value = search key for the first column.
table_array = Table where the data is located.
col_index_num = is the column where the data has to be extracted from.
range_lookup = TRUE or omited then finds an approximate match. FALSE then finds an exact match and returns #N/A if there is no match.


Lets take an example. Let the data table be the following one.



From this table, you can find how are you feeling [ :) ] and how many days are left for the next weekend [ :)) ].
it will look something like this..



if you replace TUESDAY with another valve, the other two values will get extracted from the table.


How it works.
the formula for the cell C11 is this.
=VLOOKUP($C$10,$B$3:$D$7,2,0)


the formula for the cell C12 is this.
=VLOOKUP($C$10,$B$3:$D$7,3,0)

where,

C10 is the feed data or the data you want to search in the first column.
B3:D7 is the table shown in the first picture. (the source data)
next is column where you want the data from. Its 2 and 3 respectively,
next is 0 [ equal to false ]


Note (1):
You can ignore the $ symbols if its complicated.
$ sign is to lock the movement of the reference cell when the cell is copied to another one.

Note (2):
You can define a name for the table which can be used in functions.
Insert -> Name -> Define.
Put the name as "Data_Table"
Put the value as "B2:D7"

So the formulae will change as
=VLOOKUP($C$10,Data_Table,2,0)
=VLOOKUP($C$10,Data_Table,3,0)


Hope this is a good introduction for you to start exploring it further yourself.
put a comment if there is anything you want to let me know.

How to Start with VB Programming (VBA) in MS Excel

The possibilities of using VB (VBA to be precise) is almost endless and it’s not too complicated (to do simple stuff) if you put bit of an effort and with a small programming background.



Step 1. Security Settings

Ensure that the security setting is appropriate.
Go to TOOLS -> MACRO -> SECURITY
Select either MEDIUM or LOW. If you select MEDIUM, make sure that you select the option ENABLE MACROS when you open the excel with VBA.




Step 2. Set Excel ToolBars

In Excel window, it’s better to have the following ToolBars selected. (You can do that at VIEW -> TOOLBARS)

Visual Basic
Control ToolBox




Step 3. VB Editor

Press and you will see the VB Editor.
In that window, make sure that the following are visible. (In View Menu.)

Project Explorer
Properties Window




Step 4. Create a macro.

Enter 10 in Cell A1
Enter 20 in Cell A2
Enter 30 in Cell A10

Click the Cell B1.
Click RECORD MACRO (in Visual Basic Toolbar).
Click OK in the pop up window. Now it will start recoding your actions.
Enter the formula, =A1*10 to Cell B1
Click STOP RECORDING.
Recording is finished.

How to use the macro.

Click on cell B2. Click the RUN MACRO (in Visual Basic Toolbar). Select the macro you have recorded and click RUN. Click on cell B10 and run the same macro.

You can see that the same relation getting pasted in all selected cells.

Caution: This is an example where the macro was applicable for any selected cell. Not all macros are that direct. For most of the macros, you need to manually edit if you want that to use in different places.

To show that effect, you try this. Start recording a macro. Select a cell, apply a Fill Color. Stop recording. Try using the macro for other cells. It fails. That is because the selection is also getting recorded. Now we will see how to modify the macros.




Step 5. Edit the macro.

Click the RUM MACRO. Click EDIT.

Here you would be seeing the subs (functions) for each macro. In the sub for the first macro you would see the following command.

ActiveCell.FormulaR1C1 = "=RC[-1]*10"

Note: Ignore lines starting with "'". Those are comments and are not evaluated.

In the second Sub, you would see commands starting with following line or similar.

Range("B1").Select

After that you would see few lines starting with "WITH" and ending with "END WITH". We need that. That is what is setting the color. The number represents the color you have selected.

Copy the lines from "WITH" to "END WITH".
Put it in the first macro so that the sub will look like this.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded ---------- by -------
'

'
ActiveCell.FormulaR1C1 = "=RC[-1]*10"
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End Sub

Now close the VB editor.

In Excel delete the column B.
Click on Cell B1 and run the first Macro.
You should find that the Formula is placed and Cell is colored.





Step 6: Link the macro to a Button.

From the Control ToolBox, click and place a Button on the sheet. Once it is placed, you are in Design mode. Double click the button and place the following line in that Sub.
Call Macro1 'or with the correct name.

Close the VB Editor.
On the Visual Basic ToolBar, click EXIT DESIGN MODE.

Now verify it by clicking a cell and clicking the button.







Now you have a rough idea how it works. Play with The macro like changing color index. Say put a number in the cell D1. Modify the Sub so that the colorindex is taken from that cell like the following.
.ColorIndex = Range("D1")

Record more macros to understand how to code is doing things..

Have fun!!!

Basics of Excel functions and relations.

Through this post, I would like to introduce the Built in Functions of Microsoft Excel to a beginner. Let me put some excel functions that could be of use to those who what to make a start.

Before touching upon some of the functions, some introductions. The first thing that I would recommend for a beginner is to browse through the list of functions and its uses. At this stage, forget how it to be used, but keep an eye one what all you can do using functions. For that, go to INSERT -> FUNCTIONS. You will see the following dialog. Here you can either select ALL, or a specific category like TEXT. Now select a function and at the bottom, you will see what it will do. To find more detailed explanation, click HELP ON THIS FUNCTION.



Note: Click the cell with Function(s) and press F2 and you will be able to see the references in different colors.



Some simple functions:


1: Sum.
To find sum of a few numbers.
Eg: SUM(A1, D5, G1:G15)


2: Min.
Minimum of a list of numbers


3. Max.
Maximum of a list of numbers


Some more functions:


4: Concatenate:
Joins several strings into one single string.
Here each string can be a reference to another cell (like A5) or a manual one (like "Hello"). Note that the manual entries should be encapsulated in double-quotes.

NOTE: there is a simpler way to achieve this. just type & in between each string entry. So..
=Concatenate(A5," is the son of ", A10) is equivalent to
=A5&" is the son of "&A10


5: Left, Mid and Right
Left function allows you to get a specified number of characters from the left side.
Right function allows you to get a specified number of characters from the right side.
Mid function allows you to get a specified number of characters from a specified starting position.

Eg:
Assume A5 => "I am not a fool"
Assume A6 => "Joe"

Left(A5, 5) => "I am "
Left(A5, 5)&": "&A6 => "I am : Joe"

This following is an exercise for you.
Left(A5, 5)&Right(A5,6)


6: Substitute
Replaces an old text with a new one.

Assume you have to change a part number format.
What have is 75423.5638.6453 and what you want is 75423-5638-6453. (Replace all .s with -s)
=Substitute(75423.5638.6453, ".", "-") => 75423-5638-6453

Also,
=Substitute(75423.5638.6453, ".", "") => 7542356386453


7: Replace
Replaces a text with another one.
The difference from Substitute is that here you are specifying the positions of the text to be replaced.


8: Upper, Lower and Proper.
Changes the case of the string to the respective ones.

Upper(AbcD) => "ABCD"


Some Special Functions:


8: VLookUp
Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.



VLookUp(Value In Column 1, Range of the Table, The Column number of the data to be taken)
based the feed value, the row will be selected and the data from the correct column is extracted.

VLookUp(0.675, A1:C10, 3) => 250

Also..

A15 => 0.946
A16 => 2

VLookUp(A15, A1:C10, A16) => 2.17
is the viscosity for a density of 0.946.

NOTE: Alternately, you can use HLookUp to fix the column and get the data from row.



9: IF
Gets a value based on whether a condition is true or false.

If(Condition, command if condition is true, command if condition is false).

A1 => 10
A2 => First
A3 => Second

If(A1>5, A2, A3) => First
If(A1<=5, A2, A3) => Second


10: SumIf
Adds the cells based on a given criteria.



SumIf(Criteria Range, Criteria, Sum Range)
if Sum Range is not specified, Criteria Range is taken as Sum Range.

SumIf((A2:A5, ">160000", B2:B5) => 63,000
Add cells in column B if corresponding cell in Column A >160000.
SumIf((A2:A5, ">160000") => 900,000
Add cells in column A if corresponding cell in Column A >160000.

NOTE: A similar function COUNTIF counts based on condition.


11: ISBlank
Checks if a cell is blank or not.

A1 = ""
A2 = 10

IsBlank(A1) = TRUE
IsBlank(A2) = FALSE

If( IsBlank(A1), "", 100 / A1) => ""
If( IsBlank(A2), "", 100 / A2) => 10

What if you don't do that check!!!
100/A1 => #DIV/0!
100/A2 => 10.

See the first one returns an error, which you normally don't want to happen. So the check helps to prevent that.



Enough of the functions. You can browse the remaining yourself.
Couple of other related points also.





Relative motion of references.

This is a very interesting aspect of how you can use the functions effectively. And interestingly, less commonly used.
The normal references that you take move with the copying. I will explain.

let B2 is A1 + A2 + B1
If you copy B2 to B3, the relation will automatically be changed as A2+A3+B2.
If you copy B2 to C2, the relation will automatically be changed as B1+B2+C1.
This is the normal way it functions. If copy one cell to the right, all references move one cell to the right. If you copy 2 cells to the bottom, all references move 2 cells to the bottom as well.

You can control this movement. In the sense, you can restrict some or all the movement by putting a $symbol.

a reference that is A1 is free to move in both directions.
a reference that is $A$1 is prevented from moving in both directions.
a reference that is $A1 is allowed to move up or down but not right or left.
a reference that is A$1 is allowed to move right or left but not up or down.

I will take the original example again.
let B2 is $A$1 + $A2 + B$1

copy that to C2 and you will get this.
$A$1 + $A2 + C$1

copy that to B3 and you will get this.
$A$1 + $A3 + B$1

If Row-1 and Column-A are the heading elements, this relation will give you the sum of the heading cells + A1.

Note:
Pressing F4 repeatedly will cycle through each type.




I hope that this will allow you to jump start your excel learning.
Excel is a vast ocean. But don't let that bother you. Keep it simple, Make one step at a time and don't forget to make one new step each day. After sometime, you will be amused at what you are able to do.

Let me know if you have any comments / Qns.