| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can INDIRECT hold only the column value constant?
Can INDIRECT hold only the column value constant?
In a previous post I'm was trying to create a formula that will return the value of a cell based on its column position in the spreadsheet even if I insert another column to the left of it. Example Formula in Cell C2 is: =A2+B2. I insert a new column A. This moves everything one column right and the formula in cell D2 is now: =B2+C2. What I'd like is a formula that references the row/column position in the spreadsheet regardless of if columns are inserted or deleted so that after inserting a new column A my formula is the value of the new information in A2 & B2. I'd like the formula that moved to D2 to still give me the value for: =A2+B2 Thanks to Peo Sjoblom who suggested =INDIRECT("A2")+INDIRECT("B2") This worked. NEW QUESTION: I would now like be able to hold the column value constant but allow the row to adjust to the destination row so I can paste the formula down the column. Example: Formula in cell C2 is now =INDIRECT("A2")+INDIRECT("B2") I insert a new column A. No problem. Formula in cell D2 is now =INDIRECT("A2")+INDIRECT("B2"). However now Id like to revise this formula so I can copy it into cell D3 and get the value of =INDIRECT("A3")+INDIRECT("B3"). Any ideas? Thanks! -- Cheers, MIke |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can INDIRECT hold only the column value constant?
=INDIRECT("A" & ROW(B2))+INDIRECT("B" & ROW(A2))
"Mike" wrote: Can INDIRECT hold only the column value constant? In a previous post I'm was trying to create a formula that will return the value of a cell based on its column position in the spreadsheet even if I insert another column to the left of it. Example Formula in Cell C2 is: =A2+B2. I insert a new column A. This moves everything one column right and the formula in cell D2 is now: =B2+C2. What I'd like is a formula that references the row/column position in the spreadsheet regardless of if columns are inserted or deleted so that after inserting a new column A my formula is the value of the new information in A2 & B2. I'd like the formula that moved to D2 to still give me the value for: =A2+B2 Thanks to Peo Sjoblom who suggested =INDIRECT("A2")+INDIRECT("B2") This worked. NEW QUESTION: I would now like be able to hold the column value constant but allow the row to adjust to the destination row so I can paste the formula down the column. Example: Formula in cell C2 is now =INDIRECT("A2")+INDIRECT("B2") I insert a new column A. No problem. Formula in cell D2 is now =INDIRECT("A2")+INDIRECT("B2"). However now Id like to revise this formula so I can copy it into cell D3 and get the value of =INDIRECT("A3")+INDIRECT("B3"). Any ideas? Thanks! -- Cheers, MIke |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can INDIRECT hold only the column value constant?
Thanks. That worked.
"K Dales" wrote: =INDIRECT("A" & ROW(B2))+INDIRECT("B" & ROW(A2)) "Mike" wrote: Can INDIRECT hold only the column value constant? In a previous post I'm was trying to create a formula that will return the value of a cell based on its column position in the spreadsheet even if I insert another column to the left of it. Example Formula in Cell C2 is: =A2+B2. I insert a new column A. This moves everything one column right and the formula in cell D2 is now: =B2+C2. What I'd like is a formula that references the row/column position in the spreadsheet regardless of if columns are inserted or deleted so that after inserting a new column A my formula is the value of the new information in A2 & B2. I'd like the formula that moved to D2 to still give me the value for: =A2+B2 Thanks to Peo Sjoblom who suggested =INDIRECT("A2")+INDIRECT("B2") This worked. NEW QUESTION: I would now like be able to hold the column value constant but allow the row to adjust to the destination row so I can paste the formula down the column. Example: Formula in cell C2 is now =INDIRECT("A2")+INDIRECT("B2") I insert a new column A. No problem. Formula in cell D2 is now =INDIRECT("A2")+INDIRECT("B2"). However now Id like to revise this formula so I can copy it into cell D3 and get the value of =INDIRECT("A3")+INDIRECT("B3"). Any ideas? Thanks! -- Cheers, MIke |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can INDIRECT hold only the column value constant?
=INDIRECT("A" & row())+INDIRECT("B" & row())
-- Regards, Tom Ogilvy "Mike" wrote in message ... Can INDIRECT hold only the column value constant? In a previous post I'm was trying to create a formula that will return the value of a cell based on its column position in the spreadsheet even if I insert another column to the left of it. Example Formula in Cell C2 is: =A2+B2. I insert a new column A. This moves everything one column right and the formula in cell D2 is now: =B2+C2. What I'd like is a formula that references the row/column position in the spreadsheet regardless of if columns are inserted or deleted so that after inserting a new column A my formula is the value of the new information in A2 & B2. I'd like the formula that moved to D2 to still give me the value for: =A2+B2 Thanks to Peo Sjoblom who suggested =INDIRECT("A2")+INDIRECT("B2") This worked. NEW QUESTION: I would now like be able to hold the column value constant but allow the row to adjust to the destination row so I can paste the formula down the column. Example: Formula in cell C2 is now =INDIRECT("A2")+INDIRECT("B2") I insert a new column A. No problem. Formula in cell D2 is now =INDIRECT("A2")+INDIRECT("B2"). However now I'd like to revise this formula so I can copy it into cell D3 and get the value of =INDIRECT("A3")+INDIRECT("B3"). Any ideas? Thanks! -- Cheers, MIke |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can INDIRECT hold only the column value constant?
row(A2) is verbose if the formula will be entered in the second row. row()
will suffice. -- Regards, Tom Ogilvy "Mike" wrote in message ... Thanks. That worked. "K Dales" wrote: =INDIRECT("A" & ROW(B2))+INDIRECT("B" & ROW(A2)) "Mike" wrote: Can INDIRECT hold only the column value constant? In a previous post I'm was trying to create a formula that will return the value of a cell based on its column position in the spreadsheet even if I insert another column to the left of it. Example Formula in Cell C2 is: =A2+B2. I insert a new column A. This moves everything one column right and the formula in cell D2 is now: =B2+C2. What I'd like is a formula that references the row/column position in the spreadsheet regardless of if columns are inserted or deleted so that after inserting a new column A my formula is the value of the new information in A2 & B2. I'd like the formula that moved to D2 to still give me the value for: =A2+B2 Thanks to Peo Sjoblom who suggested =INDIRECT("A2")+INDIRECT("B2") This worked. NEW QUESTION: I would now like be able to hold the column value constant but allow the row to adjust to the destination row so I can paste the formula down the column. Example: Formula in cell C2 is now =INDIRECT("A2")+INDIRECT("B2") I insert a new column A. No problem. Formula in cell D2 is now =INDIRECT("A2")+INDIRECT("B2"). However now I'd like to revise this formula so I can copy it into cell D3 and get the value of =INDIRECT("A3")+INDIRECT("B3"). Any ideas? Thanks! -- Cheers, MIke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I change the tab but keep the row and column constant when | Excel Worksheet Functions | |||
| Keeping the column constant in a formula | Excel Discussion (Misc queries) | |||
| ARRAY?, CONSTANT?, INDIRECT?, CONSTITUATE? | Excel Worksheet Functions | |||
| Multiplying a column of data by a constant | Excel Discussion (Misc queries) | |||
| making a column of formuls constant | Excel Worksheet Functions | |||
Linear Mode
