======================================================================
Microsoft Sites:
Excel, Word, Powerpoint course Index:
http://office.microsoft.com/en-in/training/results.aspx?qu=excel%20training&ex=4&page=2&queryid=3a38f69d-9b0e-421c-a387-0b22d549c6c1
Excel shortcuts:(This is very informative)
http://office.microsoft.com/en-in/excel-help/introduction-0-52-RZ102673162.aspx?CTT=1§ion=2
http://office.microsoft.com/en-in/excel-help/excel-2010-keyboard-shortcuts-i-ctrl-key-shortcuts-RZ102673162.aspx?section=5
Know Excel 2007
http://office.microsoft.com/en-in/training/get-to-know-excel-2007-create-your-first-workbook-RZ010076674.aspx?CTT=1
Excel link for learning basic formulas with examples like vlookup etc.,:
http://office.microsoft.com/en-in/excel-help/examples-of-commonly-used-formulas-HP005200127.aspx
http://www.baycongroup.com/el0.htm
Some Basic Macro examples like macro for deleting duplicate rows-- writing macros for uppercase, lowercase text etc.,
Normal Excel blogs for excel tips:
http://spreadsheetpage.com/index.php/site/tips
http://spreadsheets.about.com/od/excelformulas/u/advanced_topics_user_path.htm
Simple Macros
http://msdn.microsoft.com/en-us/library/office/ee264224(v=office.12).aspx - - Good one
http://www.excelvbamacros.com/
VBA Examples:
http://msdn.microsoft.com/en-us/office/hh360994.aspx
http://msdn.microsoft.com/en-us/library/ee814737.aspx -- Very good
http://msdn.microsoft.com/en-us/library/dd721892.aspx
VBA Basics - http://www.datapigtechnologies.com/ExcelMain.htm
Excellent! But few examples
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=315
http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=302
http://www.your-save-time-and-improve-quality-technologies-online-resource.com/free-vba-tutorials.html
VBA Tutorial: http://www.excelfunctions.net/Excel-VBA-Tutorial.html
http://quickvba.blogspot.in/
http://www.excelvbamacros.com/p/contact-us.html
http://excelvbatutor.com/vba_chp7.htm
Other sites:
http://www.internet4classrooms.com/technology_tutorials/advanced_excel_topics_charts_technology_tutorials.htm
http://www.excelforum.com/excel-general/
http://spreadsheetpage.com/index.php/tips
http://excelpoweruser.wordpress.com/2012/11/06/comment-formatting/
Practicing with data bases / Examples can download data
http://office.microsoft.com/en-in/templates/desktop-student-database-TC001225355.aspx
http://office.microsoft.com/en-in/templates/?CTT=97
Yahoo answers links for macros and vba basic examples
http://answers.yahoo.com/question/index?qid=20080314122402AA5Xkbg
VBA Access
http://www.functionx.com/vbaccess/index.htm
http://www.functionx.com/access/applications/ceilinn.htm
==============================================
Other Examples:
1. Index, Match
2.Max, Offset
Data Validation use list column for country, region, city each depending on other like country india, if you select india then in the next column it should display states, you select one state say andhra pradesh, then in the third column you should be able to select district all these column lists are interdependent on each other.
| A | B | C | D | E | F | G | H | |
| 1 | color\model | Model1 | Model2 | Model3 | Model4 | Arguments | ||
| 2 | Brown | 5 | 4 | 3 | 2 | model | color | |
| 3 | Red | 0 | 2 | 8 | 9 | Model2 | blue | |
| 4 | Green | 10 | 15 | 17 | 20 | |||
| 5 | Blue | 8 | 9 | 10 | 11 | |||
| 6 | Rainbow | 1 | 8 | 40 | 80 | |||
| 7 | ||||||||
| 8 | 4 | =MATCH(H3,A2:A6,0) | ||||||
| 9 | 2 | =MATCH(G3,B1:E1,0) | ||||||
| 10 | 9 | =INDEX(A1:E6,(MATCH(H3,A2:A6,0)+1),(MATCH(G3,B1:E1,0)+1)) | ||||||
| 11 | 9 | =INDEX(A1:E6,5,3) Row=5, col=3 equivalent to above | ||||||
| 12 | 9 | =INDEX(A1:E6,MATCH("Blue",A1:A6,0),MATCH("Model2",A1:E1,0)) equivalent to above | ||||||
Source: http://dmcritchie.mvps.org/excel/vlookup.htm
A | B | C | D | E | |
| 1 | Color Name | Tint Formula | Cost | ||
| 2 | 8422 Hampestead | B1 C4 F1 | $12.25 | ||
| 3 | 8427 Wishing Star | M2 T8 | $ 6.35 | ||
| 4 | 8433 Ashford | C7 F12 S8 | $ 8.75 | ||
| 5 | 8449 Modernist | I6 S5 T4 | $ 9.25 | ||
| 6 | 8460 Bridal Wreath | AXN1 D12 E6 | $ 9.45 | ||
| 7 | Most Expensive: | $12.25 | 8422 Hampestead | ||
| 8 | |||||
| 9 | CELL | Formula | |||
| 10 | C7 | =MAX(C2:C6) | |||
| 11 | D7 | =OFFSET(A1,MATCH(C7,C2:C6,0),0) | |||
| 12 | |||||
| 13 | you can rewrite formulas as | ||||
| 14 | C7 | =MAX(C$2:OFFSET(C7,-1,0)) | |||
| 15 | D7 | =OFFSET($A$1,MATCH(C7,C$2:OFFSET(C7,-1,0),0),0) | |||
Source: http://dmcritchie.mvps.org/excel/vlookup.htm
| A | B | C | D | E | F | G | H | |
| 1 | color\model | Model1 | Model2 | Model3 | Model4 | Arguments | ||
| 2 | Brown | 5 | 4 | 3 | 2 | model | color | |
| 3 | Red | 0 | 2 | 8 | 9 | Model2 | blue | |
| 4 | Green | 10 | 15 | 17 | 20 | |||
| 5 | Blue | 8 | 9 | 10 | 11 | |||
| 6 | Rainbow | 1 | 8 | 40 | 80 | |||
| 7 | ||||||||
| 8 | 4 | =MATCH(H3,A2:A6,0) | ||||||
| 9 | 2 | =MATCH(G3,B1:E1,0) | ||||||
| 10 | 9 | =INDEX(A1:E6,(MATCH(H3,A2:A6,0)+1),(MATCH(G3,B1:E1,0)+1)) | ||||||
| 11 | 9 | =INDEX(A1:E6,5,3) Row=5, col=3 equivalent to above | ||||||
| 12 | 9 | =INDEX(A1:E6,MATCH("Blue",A1:A6,0),MATCH("Model2",A1:E1,0)) equivalent to above | ||||||
| A | B | C | D | E | |
| 1 | Cat1 | SubCat1 | Data1 | Data2 | Data3 |
| 2 | 1 | 15 | 1.2 | 4.5 | 6 |
| 3 | 1 | 18 | 2.2 | 6 | 8.2 |
| 4 | 1 | 22 | 3.1 | 7.1 | 9 |
| 5 | 2 | 15 | 3 | 5 | 6 |
| 6 | 2 | 18 | 3.1 | 5.1 | 6.1 |
| 7 | 2 | 22 | 3.2 | 5.4 | 6.2 |
| 8 | 3 | 15 | 4 | 4 | 4 |
| 9 | 3 | 18 | 5 | 5 | 5 |
| 10 | 3 | 22 | 6 | 6 | 6 |
=INDEX(C2:C10,MATCH(1,(A2:A10=G1)*(B2:B10=H1),0))
array entered with ctrl + shift & enter, assuming we are retrieving from column C, where G1 holds one lookup and H1 another.. Or hardcoded assume we want 3 from A and 15 from B
=INDEX(C2:C10,MATCH(1,(A2:A10=3)*(B2:B10=15),0))
would return 4
To find two different values:
| A | B | C | D | E | |
| 1 | Book Id | Location | Date In | Date Out | |
| 2 | 23 | 1 | 02/12/2001 | 04/22/2001 | |
| 3 | 45 | 5 | 04/17/2001 | 04/19/2001 | Location Mismatch |
| 4 | 57 | 6 | 03/18/2000 | 03/19/2000 | |
| 5 | 59 | 7 | 05/12/2001 |
| A | B | C | D | |
| 1 | Book Id | Location | Date Purchased | |
| 2 | 23 | 1 | 02/12/2000 | |
| 3 | 45 | 2 | 04/17/2001 | Location Mismatch |
| 4 | 56 | 5 | 04/20/2000 | Missing ID 56 |
| 5 | 57 | 6 | 03/18/1999 | |
| 6 | 59 | 7 | 05/12/2000 |
Formula in D2 of Sheet M.Set1: (use fill-handle to fill down)
=IF(ISERROR(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)),"Missing ID " & A2,IF(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)<> B2,"Location Mismatch",""))
=IF(ISERROR(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)),"Missing ID " & A2,IF(VLOOKUP(A2,M.Set2!$A$2:$C$200,2,FALSE)<> B2,"Location Mismatch",""))
Formula in E2 of Sheet M.Set2: (same as previous formula just for a different sheet)
=IF(ISERROR(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)),"Missing ID " &A2,IF(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)<>B2,"Location Mismatch",""))
=IF(ISERROR(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)),"Missing ID " &A2,IF(VLOOKUP(A2,M.Set1!$A$2:$C$200,2,FALSE)<>B2,"Location Mismatch",""))
Unless you modify your data structure, VLOOKUP will not work as No1 and
Patrick have indicated.
To maintain the current structure, use INDEX(MATCH()).
For example, to use name in SheetA!B1 as the look_up value for the
inspection of data in range SheetB!B1:B10 and return the associated value
from SheetB!A1:A10, use in SheetA the formula
=INDEX(SheetB!A1:A10,MATCH(SheetA!B1,SheetB!B1:B10,0))
inspection of data in range SheetB!B1:B10 and return the associated value
from SheetB!A1:A10, use in SheetA the formula
=INDEX(SheetB!A1:A10,MATCH(SheetA!B1,SheetB!B1:B10,0))
| A | B | C | D | E | F | G | H | I | |
| 1 | Plant-Name | Prod. Name | Jan | Feb | March | April | |||
| 2 | Plant-A | Prod A | 5 | 12 | 8 | 22 | Given: | ||
| 3 | Plant-A | Prod B | 6 | 15 | 15 | 18 | Plant | Plant-A | |
| 4 | Plant-A | Prod C | 8 | 66 | 42 | 45 | Prod. | Prod B | |
| 5 | Plant-B | Prod A | 2 | 45 | 12 | 16 | Month | March | |
| 6 | Plant-B | Prod B | 4 | 22 | 6 | 45 | |||
| 7 | Plant-B | Prod C | 6 | 15 | 2 | 52 | Find value | (see formula) |
Bob Ulmas, solved this problem 2001-05-30 in worksheet.functions as follows:If “Plant Name” is in cell A2, then this formula will work
Array-enter:
=INDEX(data,MATCH(A10&A11,A3:A8&B3:B8,0),MATCH(A12,2:2,0))
Where A10 contains variable plant (Plant A) and A11 contains variable
product (Prod B) and A12 contains the variable month (March)
array-enter is Ctrl/shift/enter
|
|
=SUM(SUM(OFFSET(A1,SMALL(IF(ISNUMBER(A2:A100),ROW(A2:A100)),{1,2,3})-1,)))
Recall, array formulas are entered by simultaneously pressing the Ctrl+Shift+Enter keys, not just Enter. The curly braces are not entered by you; Excel places them automatically when the array formula is properly applied.
Source: http://www.atlaspm.com/toms-tutorials-for-excel/toms-tutorials-for-excel-summing-the-first-nth-numbers-in-a-mixed-list/
Pivot table - Good example
http://www.pivot-table.com/
Removing Duplicate Rows in one column using Macros:
Sub DelDups_OneList()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through.
iListCount = Sheets("Sheet1").Range("A1:A100").Rows.Count
Sheets("Sheet1").Range("A1").Select
' Loop until end of records.
Do Until ActiveCell = ""
' Loop through records.
For iCtr = 1 To iListCount
' Don't compare against yourself.
' To specify a different column, change 1 to the column number.
If ActiveCell.Row <> Sheets("Sheet1").Cells(iCtr, 1).Row Then
' Do comparison of next record.
If ActiveCell.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet1").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
End If
Next iCtr
' Go to next record.
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub
Removing Duplicate Rows in two columns using macros
Sample 2: Compare two lists and delete duplicate items
The following sample macro compares one (master) list against another list, and deletes duplicate items in the second list that are also in the master list. The first list is on Sheet1 in the range A1:A10. The second list is on Sheet2 in the range A1:A100. To use the macro, select either sheet, and then run the macro.
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer
' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False
' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count
' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
' Loop through all records in the second list.
For iCtr = 1 To iListCount
' Do comparison of next record.
' To specify a different column, change 1 to the column number.
If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
' If match is true then delete row.
Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
' Increment counter to account for deleted row.
iCtr = iCtr + 1
End If
Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End
good site:
Tom ultris site:
http://www.atlaspm.com/?s=row&x=0&y=0
