| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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/ |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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/ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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/ |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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/ |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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/ |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Address in Formula | Excel Discussion (Misc queries) | |||
| Use Cell Address Lookup in formula | Excel Worksheet Functions | |||
| cell address from formula | Excel Discussion (Misc queries) | |||
| How do I use cell contents as an address in a formula | Excel Worksheet Functions | |||
| how do I write a formula: if (cell address) is less than X add Y | Excel Discussion (Misc queries) | |||
Linear Mode
