Create Hyperlink Cells in Excel

 

COL. DESCR Formula   A URL Common URL portion applicable to all [hyperlink] cells http://lbpdmesm01/esm/ticketdetails/sr_ticketdetail.cfm?RequestID= B QueryParam   hd-id-xxxxxx C Hyperlink Created from D Column =HYPERLINK(D1,B1) B1 specifies URL name: HD-ID-001392797 D Full URL as Text =CONCATENATE(A1,B1)

VBScript – Encrypt Decrypt with Key

   1:  

   2: 'Simple encryption/decryption script

   3: ' 

   4: 'This script is a sample encryption/decryption algorithm. 

   5: 'The algorithm uses a encryption key and a random seed. 

   6: 'If you change the seed or the key, you will get a different encrypted string. 

   7: 'You need to use the same key and seed for encryption and decryption. 

   8:  

   9: '******************************************************************************

  10: '** Script:         encrypt-decrypt.vbs

  11: '** Version:      1.0

  12: '** Created:      20-1-2009 22:28

  13: '** Author:         Adriaan Westra

  14: '** E-mail:         

  15: '** http://www.westphil.nl/systemadministration/vbscript/index.php?name=encrypt

  16: '** Purpose / Comments:

  17: '**               Demonstrate a simple encryption algorithm.

  18: '**

  19: '** Doel / Commentaar:

  20: '**               Demoscript voor een simpel encryptie algoritme.

  21: '**

  22: '**

  23: '** Changelog :

  24: '** date / time            : 

  25: '** 20-1-2009 22:28  :   Initial version

  26: '**

  27: '******************************************************************************

  28: '******************************************************************************

  29: '**   Constants for opening files

  30: '**   Constanten voor het openen van files

  31: Const ForWriting = 2 

  32: Const ForAppending = 8

  33: Dim strEncrypted

  34: Dim strKey

  35: Dim intSeed

  36: '******************************************************************************

  37: '** This is the key that will be use for en/decrypting the text

  38: strKey = "This is a very long key"

  39: '******************************************************************************

  40: '** Tis is the text that is going to be encrypted

  41: strEncrypt = "This text will be encrypted."

  42: '******************************************************************************

  43: '** This is the seed that is used for randpmizing the en/decryption

  44: intSeed = 6

  45: '******************************************************************************

  46: '** Call the Encrypt function to encrypt the text

  47: strEncryptedText = Encrypt(strEncrypt, strKey, intSeed)

  48: '******************************************************************************

  49: '** Store the encrypted text in a file for later use

  50: Set objFSO = CreateObject("Scripting.FileSystemObject")

  51: strFile = "encrypted.txt"

  52: Set objTxTFile = objFSO.OpenTextFile(strFile, ForWriting, True)

  53: objTxTFile.Write strEncryptedText

  54: objTxTFile.Close

  55: '******************************************************************************

  56: '** Get the encrypted text from file

  57: Set objTxtFile = objFSO.OpenTextFile(strFile)

  58:     If Not objTxtFile.AtEndOfStream Then

  59:       strDecrypt = objTxtFile.ReadAll

  60:    End If

  61: objTxTFile.Close

  62: '******************************************************************************

  63: '** Output the encrypted text to screen

  64: wscript.echo "Encrypted text : " & strDecrypt

  65:  

  66: '******************************************************************************

  67: '** Output the decrypted text to screen

  68: wscript.echo "Decrypted text : " & Decrypt(strDecrypt, strKey, intSeed)

  69:  

  70: wscript.quit

  71: '******************************************************************************

  72: '** Function:     String2Asc

  73: '** Version:      1.0

  74: '** Created:      20-1-2009 22:35

  75: '** Author:       Adriaan Westra

  76: '** E-mail:         

  77: '**

  78: '** Purpose / Comments:

  79: '**

  80: '**      Transform a string in to an array with ascii values

  81: '**

  82: '** Change Log :

  83: '**

  84: '** 20-1-2009 22:36 : Initial Version

  85: '**

  86: '** Arguments :  

  87: '**

  88: '**   strIn   :   string to be converted

  89: '**

  90: '** Returns   :

  91: '**

  92: '**   an array with ascii values

  93: '**         

  94: '******************************************************************************

  95: Function String2Asc( strIn)

  96:   arrResult = Array()

  97:   ReDim arrResult( CInt( Len( strIn ) ) )

  98:   For intI = 0 to Len(strIn) - 1

  99:       arrResult( intI ) = Asc( Mid( strIn,intI + 1 ,1 ) )

 100:   Next

 101:   String2Asc = arrResult

 102: End Function

 103: '******************************************************************************

 104: '** Function:     Encrypt

 105: '** Version:      1.0

 106: '** Created:      20-1-2009 22:35

 107: '** Author:       Adriaan Westra

 108: '** E-mail:         

 109: '**

 110: '** Purpose / Comments:

 111: '**

 112: '**      Encrypt a string in to an encrypted string

 113: '**

 114: '** Change Log :

 115: '**

 116: '** 20-1-2009 22:36 : Initial Version

 117: '**

 118: '** Arguments :  

 119: '**

 120: '**   strEncrypt   :   string to be encrypted

 121: '**   strKey       :   string used as encryption key

 122: '**   intSeed      :   integer to make the encryption random

 123: '**

 124: '** Returns   :

 125: '**

 126: '**   an encrypted string

 127: '**         

 128: '******************************************************************************

 129: Function Encrypt( strEncrypt, strKey, intSeed)

 130:   Rnd(-1)

 131:   Randomize intSeed

 132:   intRnd =  Int( ( Len(strKey) - 1 + 1 ) * Rnd + 1 )

 133:   

 134:   arrEncrypt = String2Asc(strEncrypt)

 135:   arrKey = String2Asc(strKey)

 136:   

 137:   For intI = 0 to UBound( arrEncrypt ) - 1

 138:       

 139:       intPointer = intI + intRnd

 140:       If intPointer > UBound(arrKey) Then

 141:          intPointer = intPointer -  ((UBound(arrKey) + 1 ) * Int(intPointer / (UBound(arrKey) + 1)))

 142:       End If

 143:       

 144:       intCalc = arrEncrypt(intI) + arrKey(intPointer)

 145:       

 146:       If intCalc > 256 Then

 147:           intCalc = intCalc - 256 

 148:       End If

 149:       strEncrypted = strEncrypted & Chr(intCalc)

 150:   Next

 151:   encrypt = strEncrypted

 152: End Function

 153: '******************************************************************************

 154: '** Function:     Decrypt

 155: '** Version:      1.0

 156: '** Created:      20-1-2009 22:35

 157: '** Author:       Adriaan Westra

 158: '** E-mail:         

 159: '**

 160: '** Purpose / Comments:

 161: '**

 162: '**      Decrypt an encrypted string

 163: '**

 164: '** Change Log :

 165: '**

 166: '** 20-1-2009 22:36 : Initial Version

 167: '**

 168: '** Arguments :  

 169: '**

 170: '**   strDecrypt   :   string to be Decrypted

 171: '**   strKey       :   string used as encryption key

 172: '**   intSeed      :   integer used to make the encryption random

 173: '**

 174: '** Returns   :

 175: '**

 176: '**   A Decrypted string

 177: '**         

 178: '******************************************************************************

 179: Function Decrypt( strDecrypt, strKey, intSeed)

 180:   Rnd(-1)

 181:   Randomize intSeed

 182:   intRnd =  Int( ( Len(strKey) - 1 + 1 ) * Rnd + 1 )

 183:   

 184:   arrDecrypt = String2Asc(strDecrypt)

 185:   arrKey = String2Asc(strKey)

 186:     

 187:   For intI = 0 to UBound( arrDecrypt ) - 1

 188:       

 189:       intPointer = intI + intRnd

 190:       If intPointer > UBound(arrKey) Then

 191:          intPointer = intPointer -  ((UBound(arrKey) + 1 ) * Int(intPointer / (UBound(arrKey) + 1)))

 192:       End If

 193:       

 194:       intCalc = arrDecrypt(intI) - arrKey(intPointer)

 195:       

 196:       If intCalc < 0 Then

 197:           intCalc = intCalc + 256 

 198:       End If

 199:       strDecrypted = strDecrypted & Chr(intCalc)

 200:   Next

 201:   Decrypt = strDecrypted

 202: End Function

VBScript – Chart directories by size

   1: 'This script lists a directory and creates a pie chart in Excel with direcories > 10 MB. 

   2: 'Create excel pie chart.

   3: 'http://www.westphil.nl/systemadministration/vbscript/index.php?name=dirspace

   4:  

   5: 'The directorie name is an argument on the command line. 

   6: '

   7: 'To start the script type: 

   8: 'cscript dirspace.vbs c:\

   9:  

  10:  

  11: Dim objFS

  12: Dim objFolder

  13: Dim objXL

  14: Dim objSubFolder

  15: Dim objRng

  16: Dim intLine

  17:  

  18: Const xl3DPie = -4102

  19: Const xlColumns = 2

  20: Const xlLocationAsNewSheet = 1

  21: Const xlColorIndexNone = -4142

  22: Const xlLineSttyleNone = -4142

  23: Const xlDataLabelsShowValue = 2

  24: Const xlDataLabelsShowPercent = 3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

  25: Const xlDescending = 2

  26: Const xlTopToBottom = 1

  27: Const xlBottom = -4107

  28: Const xlGuess = 0

  29:  

  30: On Error Resume Next

  31:       

  32: Set objFs = WScript.CreateObject("Scripting.FileSystemObject")

  33:  

  34: '** get directory name from the commandline

  35: Set objFolder = objFS.GetFolder(Wscript.Arguments(0))

  36: If objFolder Is Nothing Then 

  37:    MsgBox"Please enter a directory to list.", vbInformation, Wscript.Scriptname

  38:    Wscript.Quit

  39: End if

  40: '** start excel

  41: Set objXl = Wscript.CreateObject("Excel.application")

  42: If objXl Is Nothing Then 

  43:    MsgBox"Excel cannot be started.", vbInformation, Wscript.Scriptname

  44:    Wscript.Quit

  45: End if

  46:  

  47: With objXL

  48:     .visible= TRUE  '** make excel visible

  49:     .workbooks.add  '** add new workbook

  50:    

  51:    for each objSubFolder in objFolder.SubFolders '** loop through directories

  52:        

  53:        

  54:        If Round((ObjSubFolder.Size / 1048576),2) > 10 Then  '** if directory > 10mb 

  55:            intLine = intLine + 1

  56:            .activeSheet.Cells(intLine,1).Value = ObjSubFolder.Name

  57:            .activeSheet.Cells(intLine,2).Value = Round((ObjSubFolder.Size / 1048576),0)

  58:        End If

  59:    Next

  60:    .activeSheet.UsedRange.Columns(2).Cells.NumberFormat = "#,##0"

  61:    Set objRng = objxl.Range("A:B")

  62:    objrng.sort Objxl.activeSheet.Columns("B"), xlDescending

  63:    objRng.Select

  64:    '** create pie chart

  65:    .charts.Add         

  66:    With .activeChart

  67:          .ChartType = xl3DPie

  68:          .setSourceDate objXL.Sheets("Sheet1").UsedRange, xlColumns

  69:          .SeriesCollection(1).XValues = "=Sheet1!C1"

  70:          .location xlLocationAsNewSheet

  71:          .HasTitle = TRUE

  72:          .ChartTitle.Characters.Text = "Content of " & ObjFolder.Path

  73:          If objXL.Version = "10.0" Then

  74:             .HasLegend = False

  75:             .ApplyDataLabels,,,,,True,true,False

  76:          Else

  77:             .HasLegend = True

  78:             .Legend.Select

  79:              .Selection.Position = xlBottom

  80:             .ApplyDataLabels xlDataLabelsShowValue, false, true

  81:          End If

  82:          .plotArea.Interior.ColorIndex = xlColorIndexNone

  83:          .PlotArea.Border.LineStyule = xlLineStyleNone

  84:          .Legend.position = xlBottom

  85:       End With

  86: End With

EXCEL – CALCULATE QUARTER FROM DATE

FORMULA:

=IF(MONTH(F6) < 4, 4,IF(MONTH(F6) < 7, 1, IF(MONTH(F6) < 10, 2, IF(MONTH(F6) <= 12,3))))

DD Data Val List From other Workbook By Contextures Blog

 

Data Validation List From Different Workbook

In Excel, you can create a drop down Data Validation list, using a list from another workbook as the source.

However, for the data validation to work, the workbook that contains the list must be open, in the same instance of Excel. Users will have to open both the workbooks — the one with the drop down lists, and the workbook with the original source list.

The following instructions are for Excel 2007. There are similar instructions to create a data validation list from a different workbook in Excel 2003.

Create the Source List

In this example, you have a workbook named DataValWb.xlsx, which contains a list of customer names. This list is in a range named CustName. (For instructions on creating a named range, refer to Naming Ranges.)

Create a Named Reference to the Source List

1. Open the workbook that contains the source list — DataValWb.xlsx in this example.
2. Open the workbook in which you wish to use the list in Data Validation, or create a new workbook.
3. On the Ribbon, click the Formulas tab, then click Define Name.
   • 
4. Type a name for the List, e.g. MyCustList
5. From the Scope drop down, select Workbook
6. In the refers to box, you’ll type a reference to the named range in the source workbook:
   • Type an equal sign, then the source workbook name and extension
   • Next, type an exclamation mark, followed by the range name in the source workbook.
   • In this example, the completed reference is:
     =DataValWb.xlsx!CustName
   • NOTE: Type an apostrophe (‘) at the start and end of the referenced workbook name, if it contains a space character. For example:
     =’DataVal May10.xlsx’!CustName
7. Click OK

Create the Dropdown List

1. Select the cells in which data validation will be set.
2. On the Ribbon, click the Data tab, then click Data Validation
3. In the Allow box, choose List
4. In the Source box, type an equal sign, then the list name, e.g.: =MyCustList
5. Click OK

Use the Data Validation List

To use the data validation drop down lists, both workbooks must be open.

1. Open both workbooks — the one that contains the drop down lists, and the workbook that contains the original source list.
2. Select an item from the data validation drop down list.

Watch the Video

To see the steps for creating the named ranges, and the drop down lists, you can watch this short video.

You can also watch the video on YouTube: Excel Drop Downs From List in Different Workbook

 

Data Validation List From Different Workbook « Contextures Blog

Protected: Asynchronous Programming

This post is password protected. To view it please enter your password below:

Password:

Excel – Quickly find protected cells By TechRepublic

By Susan Harkins

October 27, 2010, 12:00 AM PDT
See also: Use Excel’s Find feature to find and select cells

Takeaway: Use conditional formatting to format protected or unprotected cells.

Once you complete a spreadsheet you plan to distribute, you probably protect cells that you don’t want altered. Doing so keeps you in control. However, later when you make changes to the sheet, you might have trouble remembering which cells are protected and which aren’t. Fortunately, using conditional formatting, you can quickly identify protected (and thereby non-protected) cells.

To identify protected cells in Excel 2003 do the following in an unprotected sheet:

1. Select the range you want to check—usually that’s your entire spreadsheet area.
   You can select the entire sheet by press [Ctrl]+A or
   clicking the Sheet Selector (the gray intersecting cell between the column and row headers).
2. From the Format menu, choose Conditional Formatting.
3. From the Condition 1 dropdown, choose Formula Is.
4. Enter the following formula: =CELL(”Protect”,A1)=1
   To format unprotected cells, use the formula
   =CELL(”Protect”,A1)=0 
   (To avoid curly quotes, paste as text only)
5. Click Format and apply an appropriate format, say, a back color.
6. Click OK. The following figure shows conditional formatting for displaying an orange back color.

Click Ok. The protected cells—that’s almost all of the cells in this sheet—are orange.

In Excel 2007 and 2010, select the entire unprotected sheet and do the following:

1. Click Home | Conditional Formatting | New Rule.
2. From the Select A Rule Type list, select Use A Formula To Determine Which Cells To Format.
3. Repeat steps 4 through 6 from above.

Quickly find protected cells in Excel | TechRepublic