For ordinary person sometime to understand pivot table is very difficult but with video below it will be easy. This video actually for my own reference to refresh my knowledge because I seldom use this function.
Sorry, no post since January 2013! Too busy and eye problem caused by computer I think.
This morning my friend asked? He got image in excel sheet but he wanted to hide when rows or columns affected is hide. After he tried several time the image still there. Actually the solution is under image property itself, when we select image, right click and choose “size and properties” and again under “properties” tab we have selection as below.
- Move and size with cells
- Move but don’t size with cells
- Don’t move or size with cells
These 3 selection is the setting what we can do or don’t to your selected image? In this case we have to select (1) as option.
Under the same tab “properties” we have another selection as below for image.
- Print Object
If selection (1) is unchecked your image disappeared when print but not in excel sheet. For option (2) this locked function available when excel sheet is protected otherwise remain unchanged.
Actually this code is for my own reference as a template for future to make my life easier because many people ask me to create summary sheet after processing certain sheets. For example we need to extract or sort specific data from current sheet and place sorted data at new summary sheet.
Option Explicit Sub CreateSummaryList() Dim IntSht As Integer Dim ShtSummary As Worksheet Dim ShtActive As Worksheet Dim LngRow As Long, LngLstRow As Long Dim i As Integer 'To check either user work with summary sheet or not? Yes then exit If UCase(ActiveSheet.Name) = "SUMMARY" Then MsgBox "Sorry! You are trying to work with summary sheet." Exit Sub 'To check active sheet empty or not? Empty then exit ElseIf WorksheetFunction.CountA(Cells) = 0 Then MsgBox "Sorry! Active Sheet is empty." Exit Sub End If i = 1 'Starting Row for summary Set ShtActive = ActiveSheet 'Current sheet 'To check existance of summary sheet and delete For IntSht = Sheets.Count To 1 Step -1 If Sheets(IntSht).Name = "Summary" Then Application.DisplayAlerts = False Sheets(IntSht).Delete Application.DisplayAlerts = True End If Next 'To create new summary sheet Set ShtSummary = Sheets.Add(After:=ActiveWorkbook.Sheets(Sheets.Count)) ShtSummary.Name = "Summary" 'To work with active sheet and place new data into summary ShtActive.Activate LngLstRow = ShtActive.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For LngRow = 1 To LngLstRow If ShtActive.Range("A" & LngRow) <> "" Then ShtSummary.Range("A" & i) = ShtActive.Range("A" & LngRow) 'More code goes here i = i + 1 End If Next 'To work with Summary sheet ShtSummary.Activate LngLstRow = ShtSummary.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For LngRow = 1 To LngLstRow If ShtSummary.Range("A" & LngRow) <> "" Then 'Your code goes here End If Next Range("A1").Select End Sub
The above code will:
- Confirm current sheet is not summary sheet? Summary then exit
- Confirm current sheet empty or not? Empty then exit
- Loop all sheet – if found summary sheet then delete (Previous summary sheet)
- Create new summary at last sheet
- Start working with active sheet
- Start working with Summary sheet.
In Microsoft Excel when have hyperlink normally the text in cell become blue and when we move our cursor on top, it will display the link address which is hidden inside. Therefore some time necessary to get Text to Display and also the hyperlink
address via code or Visual Basic for Applications. Below example will do the job for you. Please try by inserting this code into module, run and feedback to me the result, please don’t forget to put hyperlink into cell A1.
Option Explicit Sub GetHyperlinkAndText() Dim GetText As String Dim GetAddress As String On Error GoTo line1 GetText = Range("A1").Hyperlinks(1).TextToDisplay GetAddress = Range("A1").Hyperlinks(1).Address MsgBox "Your anchor Text = " & GetText & Chr(10) & "Your link = " & GetAddress End line1: MsgBox "Sorry! Link not found." End Sub
If you have any question or project please e-mail to me, I will reply and create
Actually in Microsoft Excel we have these 2 function index and match when combined it become very powerful formula even better than vlookup function. To understand this formula may be difficult at the first place but when we use to it sooner or later it become normal. This tutorial actually for me to remember how it work but if you feel useful too then much better. When I try to use vlookup function but fail because data position at reverse side, that why I’m trying to look for other alternative. So, I found this function when Google for solution.
To make the scenario more simple and easy to understand. Let try example below:
- In Column B3 to B8 – Just numbering 1,2,3,….
- In Column C3 to C8 we have fruit name
- In Column D3 to D8 we have quantity
This quantity keep on changing. What we want here is, the Maximum fruit that having the highest quantity. Forget about Visual Basic for Application just use formula. Let say we want the result in F3. Try this formula and insert into Cell F3:
The explanation refer below picture.
This definition I get from help for Index function the equation as below:
Array is a range of cells or an array constant.
- If array contains only one row or column, the corresponding row_num or column_num argument is optional.
- If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
Row_num selects the row in array from which to return a value. If row_num is omitted, column_num is required.
Column_num selects the column in array from which to return a value. If column_num is omitted, row_num is required.
and for Match equation as below:
Lookup_value is the value you use to find the value you want in a table.
- Lookup_value is the value you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.
- Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
- If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE.
- If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
- If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
- If match_type is omitted, it is assumed to be 1.