If you cannot read HTML in your email please go here: http://www.ozgrid.com/News/Archive.htm
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!To no longer receive our newsletter, send an NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .Microsoft and Microsoft Excel are registered trademarks ofMicrosoft Corporation Read this issue and past issuesonline here :
**PLEASE DO NOT REPLY TO THIS ADDRESS **
All Excel Templates Normally an 80% saving at $299.00. November Super Special $127.00! Best Value on the WWW More Hot Specials!
We Recommend SmartDraw for Flowcharts
|SmartDraw is the quick and easy way to draw quality flowcharts, org charts, web graphics, and business presentations. You can try SmartDraw free for 30 days and see why it was voted "Best Business Program" two years in a row. For business charts and diagrams- ISO 9000 Flowcharts, Floor Plans, Circuit Diagrams, Flow Charts, Org Charts, VisualScript XML, Floor Plans, Business Forms, Network Diagrams, Circuit Diagrams, Engineering Diagrams, Flyers, Maps, Timelines, Clip Art, and Web Graphics and MUCH MORE|
Excel on the WWW
What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks
That's it for this month, enjoy the newsletter and remember tocheck out any past issues here
As most of you are by now aware we have been very busy writing the upcoming Book for O'Reilly Press; 100 Excel Hacks. We are now just past the half-way mark and myself and my wife Raina are still talking!
In addition to writing the book I have been searching the Web for good products that I think will be of use to our site visitors. I have added too many to list here so I would suggest going here:http://www.ozgrid.com/Services/ExternalAddIns.htm As an added bonus to ALL newsletter members not only will you get a free copy of our33 Workbook Downloads when making any purchase, you will also get a free copy of Formula Reference Changer & Sheet Index Creator valued normally at $25.00
Why Buy From OzGrid ?
Free Excel, Word, Access, PowerPoint and E-mail Support
OurOzGrid Forum continues to grow in popularity each day. If you haven't already given yourself and/or your company totally FREE 24/7 support,simply register here ALL forum members have the opportunity to earn some extra money.Simplygo here to find out how Where else do you have the opportunity to earn $$ while answering, or asking question?
Create spreadsheets like a pro. Subscribe to Working Smarter with Microsoft Excel Affiliate SiteBarcode, Fonts, ActiveX, DLL's, Labels and more!
One common problem often encountered when developing and/or modifying spreadsheets is that there are often times when you may like to reference a single Cell such as Cell A1 and then copy this reference across columns to the right. Naturally, this results in the Formula reference changing to =B1, =C1, =D1 etc. However, this is not the result you want. Wouldn’t it be nice if Excel had a function or option that allowed us to determine whether our Cell references should increment by rows or columns regardless of if we copy across or down. Unfortunately, there is no such option in Excel, and I haven no idea of whether there is any intention of putting one in. However, we can do this by using a combination of the INDIRECT Function with the ADDRESS Function nested within.
Perhaps the best way to explain how to create the Function needed is to use a logical example. In the Cells A1:A10 enter the numbers 1 – 10 in numerical order. Go to Cell D1 and in this Cell enter:
As soon as you enter this you should have the number 1 showing in Cell D1. This is because our Formula is actually referencing Cell A1.
If you now copy this Formula across some columns to the right, you will notice that Cell E1 will contain 2, F1 will contain 3 etc. In other words, although we are copying across columns, our Formula reference is incrementing by rows.
I find this method very useful especially when I have a spreadsheet which may have headings going down rows in one particular column and I wish to create a dynamic reference to these row headings across columns.
While this is a fairly straight forward process if we are only referencing a single Cell, there are often times when you may need to do something along the same lines except the range will not be a single Cell, it will be a range of Cells and that range of Cells is being used in the argument for a function.
Lets use the all popular SUM function to demonstrate what I mean.
Assume you have been given a huge long list of numbers and your job is to not sum all of the numbers, but to sum the column of numbers in a running total fashion like this; =SUM($A$1:$A$2), =SUM($A$1:$A$3), =SUM($A$1:$A$4). The problem comes about because the results need to be dynamic and they also need to span across 100 columns on row 1 only and not down 100 rows in another column (as would often be the case).
Naturally, we could manually type such functions into each individual Cell, but as you can imagine this would take a lot of unnecessary time. Here is how we can do it using the same principle as we did when referencing a single Cell.
Again, fill the range A1:A100 with the numbers 1 – 100 in their numeric order
Go to Cell D1 and enter this Formula:=SUM(INDIRECT(ADDRESS(1,1)&”:”&ADDRESS(COLUMN()-2,1)))
This will then give you the result of 3, which is the sum of the Cells A1:A2. Copy this Formula across to Cell E1 and you will get the result of 6, which is the sum of A1:A3. Copy to Cell F1 and you will get the result of 10, which is the sum of Cells A1:A4.
It is the use of the volatile function COLUMN that causes our last Cell reference to increment by 1 each time we copy it across to a new column. This is because the COLUMN Function always returns the column number (not letter) of the Cell that houses it.
Excel, Word, Access Password Recovery ||Corrupt Excel, Word, Access File Recovery
|GO TO Excel Add-ins, Training and Templates ... Business and Charting Software ...|
|Excel Templates||Business Software Categories|
|Microsoft Excel Add-ins||Financial Calculators|
|Downloadable Excel and Excel VBA Training||Charting Tools and Software|
|Bundled Add-ins and Software Savings||Trading Software & Add-ins|
|Free Excel and VBA Support||Financial Add-ins and Software|
|Excel and VBA books||Construction and Estimating Excel Add-ins|
|Excel Password Recovery||Corrupt Excel, Word, Access File Recovery|
Are You Into Excel and Excel VBA?Our 4 Most Popular Bundled Savings
If you have ever had to work in a Workbook that has many Worksheets, you will know how painful it can be to locate a specific Worksheet. An index sheet that we could easily access from any Worksheet would make navigation much easier. Here are two ways to create one.
The first way simply entails inserting a Worksheet, calling it Index or something similar and on this Worksheet entering a list of all your Worksheet names and then creating a hyperlink for each of these names to the desired Worksheet, that is; right click>Hyperlink. While this method is probably sufficient in some instances, you will note that if and when you add/delete Worksheets your index is no longer valid. We can overcome this by placing some VBA code into the Private Module of the Worksheet like this:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
.Range("A1").Name = "Start " & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
"Index", TextToDisplay:="Back to Index"
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", SubAddress:= _
"Start " & wSheet.Index, TextToDisplay:=wSheet.Name
To insert this code into your Workbook, it must reside within the Private Module for the Sheet Object.
It is important to note that the code above, written as is, names Range A1 on each sheet Start followed by a number that represents the Sheet Index Number. This ensures that Range A1 on each sheet has a different name. If Range A1 on your Worksheet already has a name, you should consider changing all occurrences of .Range("A1") to a suitable range address.
The other method that can be used, which is probably a lot more user-friendly, is to manipulate the pop-up menu displayed when you right click on any Cell in any Worksheet. To this we can add our own menu item which when clicked will show the standard Workbook Tabs Command Bar. If you are not familiar with this Command Bar, you can see it by right-clicking on your Sheet Scroll Tab arrows on the bottom left of any Worksheet.
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim cCont As CommandBarButton
On Error Resume Next
On Error GoTo 0
Set cCont = Application.CommandBars("Cell").Controls.Add _
cCont.Caption = "Sheet Index"
cCont.OnAction = "IndexCode"
To insert the code as shown above:
Until next month, keep Excelling!
|Microsoft Excel Add-ins||Chart Tools & Add-ins||Excel on the WWW|
|Excel Training & Tutoring||Trading Software & Add-ins||Financial Calculators|
|Excel - Word Templates||Financial Add-ins & Software||Downloadable Training|
|Business Software||Excel Converters | Barcodes, Fonts||Current Software Specials|
|Windows & Internet Software||Construction & Estimating||OzGrid Add-in Collection|
Office Ready Professional 3.0 |High Impact e-Mail 2.0 |Office-Ready Business Plans |Office-Ready Marketing Plans |e-Marketing Suite |Office Policy Manual |Ultimate Everyday Calculator |Ultimate Financial Calculator |Ultimate Marketing Calculator |Template Zone Home Page |Office Ready Stuff It |Ultimate Loan Calculator
You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :