How do I get a formula to use data from a fixed location(NOT vicev

P

Pete w

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
 
D

Duke Carey

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
 
J

Jerry W. Lewis

Duke said:
You can use the OFFSET() function.

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

=OFFSET(M40,-36,-4)
Click to expand...


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
Click to expand...


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
 
G

Gordon

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 ?
 
J

Jerry W. Lewis

Gordon said:
Why not just use $I$4 ?
Click to expand...

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
 
P

Pete Wadley

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
 
Top