Excel Absolute Reference?
Solved/Closed
Related:
- Excel Absolute Reference?
- Excel marksheet - Guide
- Number to words in excel - Guide
- Excel apk for pc - Download - Spreadsheets
- Kernel for excel - Download - Backup and recovery
- Excel date format dd.mm.yyyy - 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")
