Formula without cell address
 

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/

Formula without cell address
 

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

--
HTH,
Bernie
MS Excel MVP


"Billy Rogers" wrote in message
...
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/

Formula without cell address
 

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


"Billy Rogers" wrote:

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/

Formula without cell address
 

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/


"Bernie Deitrick" wrote:

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

--
HTH,
Bernie
MS Excel MVP


"Billy Rogers" wrote in message
...
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/

Formula without cell address
 

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/


"Gary''s Student" wrote:

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


"Billy Rogers" wrote:

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/

Formula without cell address
 

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" wrote in message
...
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/


"Bernie Deitrick" wrote:

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

--
HTH,
Bernie
MS Excel MVP


"Billy Rogers" wrote in message
...
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/

Formula without cell address
 

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/


"Bernie Deitrick" wrote:

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" wrote in message
...
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/


"Bernie Deitrick" wrote:

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

--
HTH,
Bernie
MS Excel MVP


"Billy Rogers" wrote in message
...
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/