Formula without cell address

[Billy Rogers]

Is is possible t write a formula that doesn't use a cell address but rather a
relative location ( I cant have a specific cell letter in the formula)

ex. a formula adds the two cells to the left of the cell with the formula

What I'm trying to do is add the formula to a query that gets pasted into
excel. Then i manually type a formula in the last column. I can't hard
code a cell reference because each month there is an extra column so the
formula gets moged over one column each month.
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Bernie Deitrick]

Use a formula like this, where K11 is the cell _with_ the formula

=OFFSET(K11,0,-2)+OFFSET(K11,0,-1)

which will return I11+J11
When you insert a column to the left of K11, the formula will become

=OFFSET(L11,0,-2+OFFSET(L11,0,-1)
and will return J11+K11

 

[Gary''s Student]

Tools > Options... > General and check the R1C1 reference checkbox
then use:
=RC[-1]+RC[-2]

 

[Billy Rogers]

thanks for the help, but I can't have any absolute cell addressess since this
is going to be pasted into a different cell each month. (the data I need will
be positioned relatively to it though)

I need some other way of refering to the cell itself that the formula is in.
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Billy Rogers]

I need somehting like this without having to change the settings. The
spreadsheet is created automatically and is filled with data from SQL Server.
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

Tools > Options... > General and check the R1C1 reference checkbox
then use:
=RC[-1]+RC[-2]
--
Gary''s Student - gsnu200786

Is is possible t write a formula that doesn't use a cell address but rather a
relative location ( I cant have a specific cell letter in the formula)

ex. a formula adds the two cells to the left of the cell with the formula

What I'm trying to do is add the formula to a query that gets pasted into
excel. Then i manually type a formula in the last column. I can't hard
code a cell reference because each month there is an extra column so the
formula gets moged over one column each month.
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Bernie Deitrick]

Billy,

This will work for any cell:
=INDEX($1:$65536,ROW(),COLUMN()-2)+INDEX($1:$65536,ROW(),COLUMN()-1)

HTH,
Bernie
MS Excel MVP

 

[Billy Rogers]

awesome! Thanks Bernie
--
"Just because you don''t know how to do something doesn''t mean it can''t be
done"

Billy Rogers

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/