# Excel Absolute Reference?

Solved/Closed
Related:

- Excel Absolute Reference?
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Excel marksheet - Guide
- How to take screenshot in excel - Guide

## 3 responses

rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766

Aug 11, 2010 at 07:23 AM

Aug 11, 2010 at 07:23 AM

Yes it is because that when you drag down, it means row needs to be changed (default behaviour) but when you drag across columns, then it means columns would change.

You have to use a combination of formula to do

=INDIRECT("A" & COLUMN()-2)

column() function gives the index of the column where the formula is written, then it subtract 2 from that number. So if you write this in C1, you would get

column() = 3

column()-2 = 1

then indirect basically just uses "A" & 1 = A1 cell address to give you the value

You have to use a combination of formula to do

=INDIRECT("A" & COLUMN()-2)

column() function gives the index of the column where the formula is written, then it subtract 2 from that number. So if you write this in C1, you would get

column() = 3

column()-2 = 1

then indirect basically just uses "A" & 1 = A1 cell address to give you the value

Thanks for the formula combination.

What if I were to have the values on different worksheets?

For example, cells A1:A5 (5,10,15,20,25) would be on Sheet 1.

On Sheet 2, I want to input the formula combination for cells C1:G1.

How could that work, is it possible?

What if I were to have the values on different worksheets?

For example, cells A1:A5 (5,10,15,20,25) would be on Sheet 1.

On Sheet 2, I want to input the formula combination for cells C1:G1.

How could that work, is it possible?

rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766

Aug 11, 2010 at 10:33 AM

Aug 11, 2010 at 10:33 AM

Indirect should still work. In this case you string the sheet name too as was done for column

=INDIRECT("Sheet1!A" & COLUMN()-2)

If you look at the help for indirect, you would see all it does is instead of

usual

=A1

it takes an string and then transform that to address.

=INDIRECT("A1")

=INDIRECT("Sheet1!A" & COLUMN()-2)

If you look at the help for indirect, you would see all it does is instead of

usual

=A1

it takes an string and then transform that to address.

=INDIRECT("A1")