Search This Blog

Tuesday, 31 July 2012

CREATE DYNAMIC DATA VALIDATION LIST


Say your data is in the range A2:A6 and you want to create a data validation list using this data  in C1. It is easy to do. You will go to Data-- Data Validation--List--Select Range--Press OK and you are done.


But sometimes we want our data validation list to get updated automatically when we add entries in our data.
For this we need to create a dynamic named range for our data. To do this go to Formula Tab--Click on Define Name. Below window will appear.


Enter name whatever name you want in the NAME field (I have named it as MyRange) and enter the formula as shown in the figure in refers to field and press OK

Formula :  =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

Select cell C1(where you want to create data validation list) and Go to Data Tab--Click on Data Validation. Below window will appear


Select List in the combobox under Allow option and enter the name (=MyRange) that you have given in the previous step and press OK.

Now whenever you add an entry in you data, your list will automatically get updated. I have add few names and my list got updated. See image below


Is this post helpful to you?

Please post your valuable comment. Thanks!


Monday, 30 July 2012

HOW TO INSERT CAMERA IN QUICK ACCESS TOOLBAR

Below are the steps

1. Click on MS Office button
2. Click on Excel Option
3. Go to Customize
4. Select All Commands
5. Click on Camera and then Add
6. Click OK
(Click to Enlarge)


CONCATENATE YOUR TEXT WITH FORMAT USING EXCEL


STEPS:

1. Enter your text in A1,B1 and C1
2. Adjust column width so that combined width of A1:C1 is equal to the width of E1
3. Select range A1:C1
4. Click on the Camera Tool


5. Select E1 and done.

Now whenever you change the format of any cell (A1,B1 or C1), format of the text in E1 will change automatically.

To know how to insert Camera in Quick Access Toolbar. Click on
How to insert Camera


Is this post helpful to you? Please post your valuable comment.



CREATE IN-CELL BAR CHART IN EXCEL 2007 USING FORMULA- TYPE 1



Formula in F2 to F6:

=REPT("█",A2)&CHAR(10)&REPT("█",B2)&CHAR(10)&REPT("█",C2)&CHAR(10)&REPT("█",D2)&CHAR(10)&REPT("█",E2)

Formatting:

1. Change font size to 3.
2. Wrap Text
3. Go to Format Cells and click on Alignment Tab and Orientation to 90 Degree
4. Select the font color.

AND YOUR INCELL BAR GRAPH IS READY


To see Type 2, Click on
http://excelvbatipsforbeginners.blogspot.in/2012/08/create-in-cell-bar-chart-in-excel-2007.html

Did it help you? Please post your valuable comment. Thanks!!

CREATE AN INDEX FOR YOUR WORKBOOK

Paste the below code in the module and run it


Sub create_index()
Dim i As Integer
Dim newsheet
Dim j As Integer

Set newsheet = Worksheets.Add(before:=Sheets(1))
 
   With newsheet
        .Name = "Index"

            With Range("A1:H1")
                 .Merge
                 .Value = "INDEX"
                 .HorizontalAlignment = xlCenter
                 .VerticalAlignment = xlCenter
                 .Font.Bold = True
                 .Font.Color = vbRed
                 .Font.Size = 13
            End With

j = 2
 
   For i = 2 To ThisWorkbook.Sheets.Count
          .Range("A" & j) = Sheets(i).Name
          .Range("A" & j).Select
          .Hyperlinks.Add Anchor:=Selection, _
            Address:="", SubAddress:="" & Sheets(i).Name & "!A1"

j = j + 1
 
   Next

ActiveWindow.DisplayGridlines = False

    End With

End Sub


This code will insert a sheet in workbook named "Index". This sheet will have list of name of all sheets with hyperlink. You can move to any sheet directly by clicking on it's name.

Thursday, 26 July 2012

DELETE ALL SHAPES IN YOUR WORKSHEET


Paste below code in module and run it

Sub delete_shapes()

Dim i As Integer

For i = ActiveSheet.Shapes.Count To 1 Step -1
ActiveSheet.Shapes(i).Delete
Next
End Sub

Monday, 23 July 2012

CREATE DATA VALIDATION LIST WITH UNIQUE VALUES FROM A LIST OF DUPLICATES

Paste the below code in module and run it.


Option Explicit

Sub data_validation_with_unique_values()
Dim clctn As New Collection
Dim arr As Variant
Dim i As Integer
Dim distinct() As String

'Fill values from your range into array
arr = Application.Transpose(Sheet1.Cells(1, 1).CurrentRegion.Resize(, 1).Value)

'Create a list of unique
On Error Resume Next
For i = LBound(arr) To UBound(arr)
clctn.Add arr(i), arr(i)
Next i
On Error GoTo 0

ReDim distinct(1 To clctn.Count)
For i = 1 To clctn.Count
distinct(i) = clctn(i)
Next

'Paste unique values in column E
Sheet1.Cells(1, 5).Resize(clctn.Count).Value = Application.Transpose(distinct)

'Give a name range to your list in column E
Range("E1:E" & Range("E65536").End(xlUp).Row).Name = "Myrange"

'Create data validation list in active cell using named range
ActiveCell.Validation.Add xlValidateList, xlValidAlertStop, xlBetween, "=Myrange"

End Sub

Note: Change the highlighted part as per your requirement.

Sunday, 22 July 2012

QUICK TIP TO REMOVE "0" FROM ACTIVE WORKSHEET

Click on Office Button



Select Excel Options



Go to Advanced and deselect the checkbox "Show a zero in cells that have zero value as shown in the below figure.(Click on the image to enlarge it)



It will remove all the zero from your worksheet.

Friday, 20 July 2012

CLOSE USERFORM ON ESCAPE KEY


Put a commandbutton on your userform and write the below code on command button Click event.


Private Sub CommandButton1_Click()
Unload Me
End Sub

Then set cancel to true in its property. See image below(Click on the image to enlarge it)


Now when you run userform and press escape key on your keyboard userfrom will be closed.

PASTE NON BLANK CELLS ONLY USING EXCEL



Array Formula in B1:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$1:$A$10<>"",ROW($A$1:$A$10)),ROWS($B$1:B1)))))      

Press Ctrl+Shift+Enter and drag it down to B10.

Tip:   This is also useful when you create a data validation list from a range with blank cells in it. In that case you can create a new range with non-blank cells using above formula and then use this new range for data validation.


Tuesday, 17 July 2012

MERGE TEXT BY VBA WITHOUT LOOSING DATA


Select the cells need to be merged and run the below code in the module.


Sub Merge_without_loosing_data()
Dim OutputText As String
Dim cell As Range
Const delim = " "

On Error Resume Next
For Each cell In Selection
OutputText = OutputText & cell.Value & delim
Next cell

With Selection
     .Clear
     .Cells(1).Value = OutputText
     .Merge
     .HorizontalAlignment = xlCenter
     .VerticalAlignment = xlCenter
     .WrapText = True
End With
End Sub

SPLIT TEXT BY VBA


Select the cell that you want to split, copy the below code and run it in module.


Sub split_text()
Dim splitval As Variant
Dim totalval As Long

splitval = Split(ActiveCell.Value, Chr(10))
totalval = UBound(splitval)
Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), Cells(ActiveCell.Row _
, ActiveCell.Column + 1 + totalval)).Value = splitval
End Sub



Friday, 13 July 2012

COUNT "6" WORKING DAYS IN A WEEK

Write 07/08/2012 in A1 and 07/14/2012 in B1

If you use NETWORKDAYS function to count the working days between these two dates, it will give you "5". But what if your office works six days in a week. In that case use the following formula to count six working days in a week.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<7))



Thursday, 12 July 2012

SORT DATA ON BACKGROUND COLOUR BY VBA


Copy below code and run it in the module


Sub sort_data_on_backgroud_color()
Sheets("Sheet1").Select
Range("B1") = "ColorIndex"
For i = 2 To ActiveSheet.Range("A65536").End(xlUp).Row
ActiveSheet.Range("B" & i).Value = ActiveSheet.Range("A" & i).Interior.ColorIndex
Next
ActiveSheet.Range("A1:B" & Range("A1").End(xlDown).Row).Sort key1:=ActiveSheet.Range("B:B"), order1:=xlAscending, Header:=xlYes
Columns("B:B").ClearContents
End Sub



QUICK TIP: CONVERT THE NUMBER INTO PERCENTILE



If we want to add "%" in each cell in a column, we generally select home tab, then go to % and it converts the number in the following format.

1000%
3300%
3700%
1200%
1100%
4200%
2400%
2400%

So, this is not a right way. The quick way to do this is


Write 100 in a cell & copy itSelect the data which you want to format (In this case A1:A8)
Right click and go to Paste Special
Select divide
Press OK
Go to Home tab and select Percent Style
Your data will change into the format shown in column B in the above image




FIND TOP FIVE VALUES FROM A LIST OF DUPLICATE VALUES


Generally if we use Large function to find out top 5 (any number) values from a list of duplicates it will give
67,65,65,40,40. But if you want to find out the large unique values use below mentioned formula.


Select range from B2:B6 and enter the following formula in the cell B2

=TRANSPOSE(LARGE(IF(FREQUENCY(A2:A11,A2:A11)>0,A2:A11,""),{1,2,3,4,5}))

Press Ctrl+Shift+Enter

GANTT CHART BY VBA



Paste the below code in the module:


Sub gantt_chart()
Dim lastrow, lastcol As Long

lastrow = Range("A65536").End(xlUp).Row
lastcol = Range("A:A").End(xlToRight).Column

Dim i, j As Integer

For j = 5 To lastcol
For i = 2 To lastrow

If Cells(1, j).Value >= Cells(i, 3).Value And Cells(1, j).Value <= Cells(i, 4).Value Then
Cells(i, j).Value = 1
Cells(i, j).Interior.Color = vbRed
Cells(i, j).NumberFormat = ";;;"
Else
Cells(i, j).Value = 0
Cells(i, j).NumberFormat = ";;;"
End If
Next
Next
End Sub