I want a formula to use data from a fixed location. For example I am using a
table B2 to I4.
During the week new value are added to the right of this table and then the
table is shifted to the left. So a value in I4 becomes G4 etc. I want the
formula to continue using I4 despite the fact it has been shifted but the
formula automatically adjusts to read G4.
IN SHORT Is there anyway to fix the formula to read a fixed physical
location and not track the position of the cells?
I know how to fix the value when the formula is moved(using $), but not how
to fix the source for the formula.
Anyone know a way
Thanks
Pete

=INDIRECT("I4")

Jerry

Pete w wrote:

I want a formula to use data from a fixed location. For example I am using a
table B2 to I4.
During the week new value are added to the right of this table and then the
table is shifted to the left. So a value in I4 becomes G4 etc. I want the
formula to continue using I4 despite the fact it has been shifted but the
formula automatically adjusts to read G4.
IN SHORT Is there anyway to fix the formula to read a fixed physical
location and not track the position of the cells?
I know how to fix the value when the formula is moved(using $), but not how
to fix the source for the formula.
Anyone know a way
Thanks
Pete

You can use the OFFSET() function.

If the cell that references I4 is M40, this formula will ALWAYS reference I4

=OFFSET(M40,-36,-4)

alternatively,

=OFFSET(A1,3,8)

will also ALWAYS refer to I4


"Pete w" wrote:

I want a formula to use data from a fixed location. For example I am using a
table B2 to I4.
During the week new value are added to the right of this table and then the
table is shifted to the left. So a value in I4 becomes G4 etc. I want the
formula to continue using I4 despite the fact it has been shifted but the
formula automatically adjusts to read G4.
IN SHORT Is there anyway to fix the formula to read a fixed physical
location and not track the position of the cells?
I know how to fix the value when the formula is moved(using $), but not how
to fix the source for the formula.
Anyone know a way
Thanks
Pete

Duke Carey wrote:

You can use the OFFSET() function.

If the cell that references I4 is M40, this formula will ALWAYS reference I4

=OFFSET(M40,-36,-4)


Inserting/deleting rows/columns between I4 and M40 will change the
referenced cell's address.


alternatively,

=OFFSET(A1,3,8)

will also ALWAYS refer to I4


Inserting/deleting rows/columns between A1 and I4 will change the
referenced cell's address.

AFAIK =INDIRECT("I4") is the only way to ensure that you are always
pointing to I4 regardless of what happens to the worksheet.

Jerry

Jerry W. Lewis wrote:
|| Duke Carey wrote:
||
||| You can use the OFFSET() function.
|||
||| If the cell that references I4 is M40, this formula will ALWAYS
||| reference I4
|||
||| =OFFSET(M40,-36,-4)
||
||
|| Inserting/deleting rows/columns between I4 and M40 will change the
|| referenced cell's address.
||
||
||| alternatively,
|||
||| =OFFSET(A1,3,8)
|||
||| will also ALWAYS refer to I4
||
||
|| Inserting/deleting rows/columns between A1 and I4 will change the
|| referenced cell's address.
||
|| AFAIK =INDIRECT("I4") is the only way to ensure that you are always
|| pointing to I4 regardless of what happens to the worksheet.
||
|| Jerry

Why not just use $I$4 ?

--
Interim Systems and Management Accounting
Gordon Burgess-Parker
Director
www.gbpcomputing.co.uk

Gordon wrote:


Why not just use $I$4 ?

If =$I$4 is in A1 and you delete column H, then the formula in A1 will
now read $H$4, where the OP wanted it to refer to the new I4 cell.

Jerry

Thanks a lot Jerry. That was exactly what I was after. I forgot to write
back and thank you, but I corrected the sheet and it works perfectly. It was
for a big company and all the employees are used to adding this weeks sales
to the end of the tables and then deleting the first row so that the sales
charts are always the same length. I was making a table at the bottom which
shows various statistics but like I said when the first row is deleted it
changes the locations and the whole thing gets messed up. Not any more.
Cheers
Pete


"Jerry W. Lewis" wrote:

Gordon wrote:


Why not just use $I$4 ?

If =$I$4 is in A1 and you delete column H, then the formula in A1 will
now read $H$4, where the OP wanted it to refer to the new I4 cell.

Jerry