Back to Excel Newsletter Archives
WHAT'S NEW AT OZGRID |HOT SPECIALS | EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS WHAT'S NEW AT OZGRID
As of this month Raina has Start ed working full-time in the business, specifically focusing on building up the consultancy and programming side of the business. So if you have a project that you would like to implement, but are unsure as to how to go about it, or you would simply like some Excel consultancy advice, please contact [email protected] and/or visit this page
Our developers are skilled in all areas, like engineering, accounting, financial, budgeting and management and are have all been regular contributors to the Forum for the last few years. All work that is undertaken and all correspondence during the project is overseen by Raina, ensuring your project is well taken care of. To our clients this means the owners (Raina and Dave) see all work and ensure you are given the best
HOT SPECIALS 1) Over $139.00 of FREE Software .
2) Excel Dashboard Kit For Reports & Charts .
3)Take Excel to the next level . Award winning data visualization software
that lets you transform your dull spreadsheets into interactive and
visually stunning dashboards, calculators, presentation graphics and more.
With one click you can export your work to PowerPoint, html, email or even
4) 40% Off: Speed Up My PC, Windows Back-up & Windows Tasks .
Excel Data Tables
Download Data Table example workbook
Data Tables are a range of cells that are used for testing and analysing outcomes on a large scale.
A Data Table will show you how by changing certain values in your formulas you can affect the result of your formula. Data Tables can store the results of many different scenarios for you in one table, so that you can analyse them to select which scenario is your best option. The results are then written into a table form in your Workbook in a location specified by you. Data Tables are written as array formulas , which therefore allows them to perform multiple calculations in a single location.
There are two types of Data Tables, One-variable data tables and two-variable data tables.
One-variable data tables
The first thing you must do is to create a base or test model and tell your Data Table which formulas from your base model you want to test. This is easily done from inside the Data Table by placing a formula to reference the formula in the base model.
Here is an example:
Lets say that we wish to purchase a new tractor for work on our family farm. We need to know that if interest rates fluctuate we can still afford to pay for the tractor. So we need to know what our loan repayments will be, what our total repayments will be and how much interest we are paying.
You should now see the results of the calculations given the values entered in D4:D9 that would appear in cells B11, B13, and B14 of your base model pasted into the table.
Two-variable Data Tables
You can use a two-variable Data Table to gauge the effect on one formula by changing the value of two input cells within the one table. With this type of table, you can nominate two series of data that can be placed back into the original model into two different input cells. Using the example above, this means that you could nominate a series of interest rates to place in the original interest rate cell (B5) and a series of loan terms to place in the Term of Loan cell (B7). When creating a two-variable table, one series is entered into the first column of the table and the other into the first row of the table. As when creating a one-variable table, the formula reference that we want to test needs to be placed into the blank cell at the top of the first column of the table. The attached workbook shows an example of a Two-variable Data Table.
A couple of rules for Data Tables:
Download Data Table example workbook
Create a Hyperlinked List of Excel Workbook File Names
With the use of Excel VBA Macro code we can create a list of Hyperlinked Excel Workbook names on any Excel Worksheet. The code below can be used on any version of Excel, 2000 or higher. The only changes needed are to the file paths used. The part of the code that reads: .Filename = "Book*.xls" has been commented out but can be uncommented if you wish to restrict the list to that of specific Excel Workbook name. Note also the code 'as is' restricts the list to only Excel Workbooks. However, this can be any of the File types listed below;
MsoFileType can be one of these MsoFileType constants
Ensure the active Worksheet at the time of running the code is clean so as to not over write existing data.
Sub HyperlinkXLSFiles()Dim lCount As LongApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseApplication.EnableEvents = FalseOn Error Resume Next With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "C:\MyDocuments\Testings" .FileType = msoFileTypeExcelWorkbooks ' .Filename = "Book*.xls" If .Execute > 0 Then 'Workbooks in folder For lCount = 1 To .FoundFiles.Count 'Loop through all. ActiveSheet.Hyperlinks.Add Anchor:=Cells(lCount, 1), Address:= _ .FoundFiles(lCount), TextToDisplay:= _ Replace(.FoundFiles(lCount), "C:\MyDocuments\Testings\", "") Next lCount End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = TrueEnd Sub
Loop Through a Folder of Excel Workbooks
With the use of Excel VBA Macro code we can loop through a Folder on ones Hard-drive and work on all Excel Workbooks within it. The code below can be used on any version of Excel, 2000 or higher. The only changes needed are to the file paths used. The part of the code that reads: .Filename = "Book*.xls" has been commented out but can be uncommented if you wish to restrict the list to that of specific Excel Workbook name. Note also the code 'as is' restricts the list to only Excel Workbooks. However, this can be any of the File types listed below;
Sub RunCodeOnAllXLSFiles()Dim lCount As LongDim wbResults As WorkbookDim wbCodeBook As WorkbookApplication.ScreenUpdating = FalseApplication.DisplayAlerts = FalseApplication.EnableEvents = FalseOn Error Resume NextSet wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "C:\MyDocuments\TestResults" .FileType = msoFileTypeExcelWorkbooks '.Filename = "Book*.xls" If .Execute > 0 Then 'Workbooks in folder For lCount = 1 To .FoundFiles.Count 'Loop through all. 'Open Workbook x and Set a Workbook variable to it Set wbResults = Workbooks.Open(.FoundFiles(lCount)) 'DO YOUR CODE HERE wbResults.Close SaveChanges:=True Next lCount End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = TrueEnd Sub
ADVERTISEMENTSArtificial neural network software for stock markets! EXCEL TEMPLATES SPECIALS DATABASE SOFTWARE