Back to Excel Newsletter Archives
EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS
Relative Named Ranges Download Workbook With Data
The most common type of range name is an Absolute Range Name . An absolute range name refers to a specific cell or range as an absolute address like $A$1. This is because when you create a named range, you are usually creating a range to refer to a specific part of a worksheet.
A relative named range is a named range that refers to a range that is relative to the position of the cell pointer at the time they are created. This makes them perfect for formulas that are required to be moved around a worksheet and are easy to create. When you create a relative named range it is VERY important that you have the position of the cell pointer in the correct cell before naming.
Let’s say we have a spreadsheet and we want to create relative names for it so that we can create a running Total.
Column A contains invoice numbers and is called Inv. No. Column B contains Vendors names, and is headed Vendors, Column C is called Debit and column D is called Credit. In column E we want to place our formula. This column is titled Total. Assume there are a mixture of numbers and blank cells in columns C and D.
The first thing that we need to do is go to Insert>Name>Define. Under Names in Workbook type in Vendor. Now press the Tab key three times to jump to the Refers to: area of the dialog. In this area type =$B:$B, (absolute) then select Add, then Close.
We need to also add another three named ranges:
Go to Insert>Name>Define and add the following absolute named ranges:
Now we need to create relative names for our rows in order for our formula to work. Select cell E2
Go to Insert>Name>Define and under Names in Workbook type in This, then jump to the Refers to box and type in the relative range: =2:2
We need to add one more relative named range:
Click Add, then Close.
Because we had the cell pointer firstly in row 2, This will always refer to the current row. Prior will always refer to the row above.
Now to write the formula.
In cell E2, type in the following formula:
=Debit-Credit+IF(Vendor=Prior Vendor, Prior Total)
Hit Enter, then with your fill handle , copy down the page.
See alsoExcel Named Ranges for many more types of named ranges
See Also Links Below if Not Familiar With AutoFilter in VBA
AutoFilter by Date & Time With VBA
Sub ByDateTimeGreater()Dim dDate As Date, dTime As DateDim strDate As StringDim lDate As Long, dblTime As DoubleDim dDateTime As Double dDate = DateSerial(2006, 9, 12) lDate = dDate dTime = TimeSerial(12, 8, 0) dblTime = dTime dDateTime = lDate + dblTime Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTimeEnd SubSub ByCellDateTimeGreater()Dim dDate As Date, dTime As DateDim strDate As StringDim lDate As Long, dblTime As DoubleDim dDateTime As DoubleDim rDate As Range, rTime As RangeSet rDate = Sheet1.Range("G1") 'Cell housing date & time If Not IsDate(rDate) Then 'Check if valid MsgBox "Non valid date and time in Sheet1 G1)" Exit Sub End If dDate = DateSerial(Year(rDate), Month(rDate), Day(rDate)) lDate = dDate Set rTime = rDate dTime = TimeSerial(Hour(rTime), Minute(rTime), Second(rTime)) dblTime = dTime dDateTime = lDate + dblTime Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTimeEnd SubSub ByDateTimeEqual()Dim dDate As Date, dTime As DateDim strDate As StringDim lDate As Long, dblTime As DoubleDim dDateTime As Double dDate = DateSerial(2006, 9, 12) lDate = dDate dTime = TimeSerial(12, 8, 0) dblTime = dTime dDateTime = lDate + dblTime '= to does not work for non US date systems. So as with dates, we use 'Greater than or Equal to and Less than or Equal to the SAME date & time. Range("A1").AutoFilter Field:=1, Criteria1:=">=" & dDateTime, Operator:= _ xlAnd, Criteria2:="<=" & dDateTimeEnd Sub
Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only
$145.00. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!
Software Categories Search Software
Excel Add-ins || Excel Training || Excel Templates || Employee Scheduling Software || Excel Password Recovery and Access & Word etc|| Excel Recovery and Access & Word etc || Financial Software || Financial Calculators || Conversion Software || Construction Estimating Software || Drilling Software || Real Estate Investment Software || Time Management Software || Database Software || Neural Network Software || Trading Software || Charting Software || Windows & Internet Software || Barcodes Fonts, ActiveX, Labels and DLL's || Code Printing Software || Outlook Add-ins
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.