FORMULAS AND WORKSHEETS

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
An absolute reference does not move when you copy and paste it. A relative one does.

The "absolute" is shown by a "$". For example:

$a$1 - this is absolute. when you copy it it will not change
$a1 - this has an absolute column. When you copy it down, the row number will change. When you copy accross, the column will stay as "A"
a$1 - this has an absolute row. When you copy it down, the row number will not change. When you copy accross, the column will go to B, C, etc
a1 - relative. Both the column and row will change.

Type the formula:
=$a$1 into cell B1. Then copy and paste it down and accross. Then do it with the other formulas and see how they change.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,459
In VBA the same issues apply, for example:
range("A1")
is an absolute reference. It is always cell A1.

activecell.
is a relative reference. It depends which cell has been activated.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,018
Messages
5,569,646
Members
412,285
Latest member
Daibear
Top