Search TeachExcel.com
TeachExcel.com
TE
Teach Excel MS Office Tutorials Excel Consulting Services Excel Forum
Excel Video Tutorials Excel Tips Free Excel Macros Excel Help Resources Contact TeachExcel
Video Tutorials
  • Free Macros
  • Excel Help Directory
  • Excel 2007 Resources
  • Keyboard Shortcuts
  • Excel Forum
  • Contact/About

Tutorials: Formulas



Featured Tutorial
Dynamically Update Drop-Dowm Menus in Excel
Featured Macro
Delete Empty Rows in Excel

Absolute and Relative Cell References
   - & INDIRECT() Function Introduction

Video | Similar Helpful Excel Resources

Bookmark and Share

This is a video tutorial for Microsoft Excel which shows you how to make absolute and relative cell references in excel. Absolute cell references mean that when you enter a formula into one cell, and then copy and move that formula, the cell references within that formula will still point to the cell which the original formula points. If the references are relative and you move a formula from one cell to another, the cell references within that formula will change by the same number of rows and columns shifted from the original position of the formula. This may seem a little confusing, but the video will clear this up with examples.

The INDIRECT() function is a way to make a sort of 'super' absolute cell reference. If you use this function, the cell reference will always point to where it is specified within the formula and will never change if you move the formula, no matter what. There are many other more advanced applications of the INDIRECT() function, but this tutorial only illustrates the simple application of making absolute cell references.
   Topics Covered
Absolute Cell References and what they are
Relative Cell References and what they are.
INDIRECT() Function & Absolute Cell References
   Difficulty:         Easy
   Video:               Yes



Got a Question? Ask it Here in the Forum.



Similar Helpful Excel Resources

Indirect - Row Absolute:column Relative - Excel

View Content
I am just learning about the indirect function and have a question.

I get that INDIRECT("G38:BE1000") is the same $G$38:$BE$1000 but what if I want G$38:BE$1000. How is this done with the indirect function?

Using A Relative Or Absolute? Cell In Formula - Excel

View Content
Hey,

I'm working on my first real macro - so I'm a greenhorn. I've spent a few hours trying to research a solution, but most threads are too technical for me so I'm hoping someone can really help me out.

I am trying to use a formula that references a cell that changes day to day from when I use the macro. I need to use a formula which grabs from a cell in a non concrete location. Let me try and show you what I mean.

From I4:I10, I need a formula like this:
Code:

ActiveCell.FormulaR1C1 = "=RC[-3]*365/R17C[-6]*10000"


The part in red is the cell I have trouble referencing. In other words, I am trying to reference a cell in a separate range, but that cell changes rows. This is probably really vague, so I'm sorry.

Is there a way to name a cell in a range? Because the cell I'm looking for is always D2 in the range I created. But the cells with the formula are not in the range, so I'm having trouble referencing that particular cell. I appreciate any help that is offered, but please keep in mind my knowledge is extremely limited - especially with various functions, etc.

Relative/absolute Cell References - Excel

View Content
I have a formula, like for instance = A1*A3

I like to copy this, relatively, that is updating it to =A2*A4 and so on.

The data is supposed to be in A1, A3 or A2, A4, but if the user moves the
data from A1 to B1 the fomula is updated to =B1*A3.

Is there someway I can prevent that? So no matter where the user moves the
data, the formula will remain unchanged?

Jan




Absolute And Relative Cell Reference? - Excel

View Content
I cant figure this thing out fully, I feel so clueless. I'll attach my try at it. Does anyone have any pointers or tips for me?

Company needs a new copy machine. Rent costs are = 17.5 cents / each.
Number of copies = atleast 200 000. (The company agrees to buy at least that number)
Buying the unit costs 150 000 SEK. The bank wants a 6.5% interest rate and
after 5 years, the unit needs to be fully paid for.
Maintenance and operation is estimated to cost 6 cents / copy.
In both cases, the actual paper costs will be the same for both options.
---------------------------------------------------------------------------------------------------
What is the total cost after 5 years, which is cheapest?
How many copies / year, before it pays to buy the machine instead of renting?
Which maximum price / copy, can you pay to the leasing company in case you want to have equal cost to lease / purchase if you copy 200,000 copies / year. (Other values ​​are equal)

Absolute And Relative Figures In One Cell - Excel

View Content
Hi guys,

I' ve got a little problem concerning the output of a result;
it's very simple but I don't get it!

I want to output an absolute difference and a relative difference in one cell;
p.e.

A2: 120
A3: 150

result in A4: 30 (+25%)

Thank you for your replys in advance

Absolute Vs. Relative Cell References - Excel

View Content
I have a relatively simple Excel sheet where a user will paste in 20 fields
of information from another application. Then to the right are 10 formula
fields that need to be copied down. Record count will change each time the
template is used. Is there a way to have a macro determine how far to copy
down and then do the copy down?

I wanted to do an xlDown to bottom of pasted area, then tell it to go over
one field, mark with an "x" to spot the bottom, then do a copydown to the
"x". Problem I am having is telling Excel to go over absolute 1 field
regardless of row location. Macro keeps recording actual cell address not
relative movement.

Can anyone help?

c-


Relative/absolute Formula Writing In To A Cell. - Excel

View Content
Hi,

In the below example z= 2.

If I put,

ActiveCell.Formula = "= " & Cells(z, "d").Address

Then in Cell H8, I get the formula as -- = $D$2 --

How do I modify my formula such that I get the value as -- = D2 --

Actually I dont want hard-coded/fixed referencing.

Thanks a lot,
Hari
India




Relative (vs. Absolute) Cell References With Macros - Excel

View Content
Can somebody help me with editing this simple macro. I would like to perform
the following task on any cell in my spreadsheet.

=3+(Cell address one row up)[Enter key][Down Arrow]

You'd think this is pretty simple. But I can't get the macro to execute on
any cell other than the one in which I created the macro. Pretty useless.

Also, how do you record "Right Arrow", "Left Arrow", Down Arrow", and "Up
Arrow" into macros? Would seem to be pretty basic, but I sure can't figure it
out.

Thanks a lot.

C. S. Weiller


Hyperlink Relative And Absolute Cell Copying - Excel

View Content
Hi all,

I'm asking the wider community of expert knowledge to help me crack a problem that a novice like me can't work out even after trawling the interwebby.

I have a workbook with two sheets. The first sheet has a lot of number data, the second has descriptions etc.

I want to hyperlink from a cell on sheet 1 (B2) that links to the info on sheet 2 (C2). So far so good, I've done this using the standard hyperlink and the formula hyperlink way and editing text display with ease.

What I can't work out is how to copy the hyperlink so that the row cell reference changes in context so that sheet1 B4 links to sheet2 C4 or B5 to C5 without editing every cell: the hyperlink/formula stays the same whether I copy+paste or drag paste.

Help would be appreciated in layman's terms. Thanks in advance.

S

Using Tab To Change Absolute, Relative Cell Addresses. - Excel

View Content
Dear Excel Expert

At my former company the excel program was set up so that the TAB key would cycle through the various Cell Addressing notation.

A1
$A1
A$1
$A$1

On my current computer the tab key goes to the next cell ... I want the former behavior. How do I set it up?

thanks Excel Expert.

Contact | Privacy Policy | Disclaimer
Copyright© 2012 TeachExcel.com