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

=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

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

=INDIRECT("A" & row())+INDIRECT("B" & row())

--
Regards,
Tom Ogilvy


"Mike" <Mike@discussions.microsoft.com> wrote in message
news:26FE289A-5C0A-49B6-AEF0-7F9F9D77575C@microsoft.com...
> 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

row(A2) is verbose if the formula will be entered in the second row. row()
will suffice.

--
Regards,
Tom Ogilvy

"Mike" <Mike@discussions.microsoft.com> wrote in message
news:8665FC99-8E53-49C6-9A30-31C56B1FB6A9@microsoft.com...
> 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