For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson

Thanks Dave, much appreciated - this seems to work as long as I do not insert
any columns in the spreadsheet prior to column B - is there a way I can
always make it refer to the same column, regardless of whether the column
changes letter as columns are inserted or deleted in the future?

"Dave Peterson" wrote:

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson

maybe...

in C1:
=offset(c1,0,-1)



A.Webb wrote:

Thanks Dave, much appreciated - this seems to work as long as I do not insert
any columns in the spreadsheet prior to column B - is there a way I can
always make it refer to the same column, regardless of whether the column
changes letter as columns are inserted or deleted in the future?

"Dave Peterson" wrote:

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson


--

Dave Peterson

Sorry, my Excel skills are only average !! where would this go in the
formaule you used as the exmaple in the previous response
=indirect("B"&row()) - where the "b" is ??

"Dave Peterson" wrote:

maybe...

in C1:
=offset(c1,0,-1)



A.Webb wrote:

Thanks Dave, much appreciated - this seems to work as long as I do not insert
any columns in the spreadsheet prior to column B - is there a way I can
always make it refer to the same column, regardless of whether the column
changes letter as columns are inserted or deleted in the future?

"Dave Peterson" wrote:

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson


--

Dave Peterson

It would go in C1 replacing the other suggestion.

It just says to return the value from the cell to the left.

A.Webb wrote:

Sorry, my Excel skills are only average !! where would this go in the
formaule you used as the exmaple in the previous response
=indirect("B"&row()) - where the "b" is ??

"Dave Peterson" wrote:

maybe...

in C1:
=offset(c1,0,-1)



A.Webb wrote:

Thanks Dave, much appreciated - this seems to work as long as I do not insert
any columns in the spreadsheet prior to column B - is there a way I can
always make it refer to the same column, regardless of whether the column
changes letter as columns are inserted or deleted in the future?

"Dave Peterson" wrote:

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

If columns are only inserted before B and not after then mayby:

=INDIRECT(CHAR(63+COLUMN())&ROW())

HTH
JG



"A.Webb" wrote:

Thanks Dave, much appreciated - this seems to work as long as I do not insert
any columns in the spreadsheet prior to column B - is there a way I can
always make it refer to the same column, regardless of whether the column
changes letter as columns are inserted or deleted in the future?

"Dave Peterson" wrote:

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson

Thanks, sorry I am still having no luck making this work if the data in
column B moves to column C after I insert a new column after column A ????

"pinmaster" wrote:

If columns are only inserted before B and not after then mayby:

=INDIRECT(CHAR(63+COLUMN())&ROW())

HTH
JG



"A.Webb" wrote:

Thanks Dave, much appreciated - this seems to work as long as I do not insert
any columns in the spreadsheet prior to column B - is there a way I can
always make it refer to the same column, regardless of whether the column
changes letter as columns are inserted or deleted in the future?

"Dave Peterson" wrote:

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson

Can you post the formula you tried?

JG



"A.Webb" wrote:

Thanks, sorry I am still having no luck making this work if the data in
column B moves to column C after I insert a new column after column A ????

"pinmaster" wrote:

If columns are only inserted before B and not after then mayby:

=INDIRECT(CHAR(63+COLUMN())&ROW())

HTH
JG



"A.Webb" wrote:

Thanks Dave, much appreciated - this seems to work as long as I do not insert
any columns in the spreadsheet prior to column B - is there a way I can
always make it refer to the same column, regardless of whether the column
changes letter as columns are inserted or deleted in the future?

"Dave Peterson" wrote:

=indirect("B"&row())

if that's the formula in C1.

A.Webb wrote:

For example, in C1 I have a long formulae that includes multiple INDIRECT
references to B1 which is where I input a data figure - column B has data
that is dragged and dropped between cells up and down that column (hence the
reason for including the indirect) column C provides the answer. After
setting up a formulae in C1 how can I copy this down column C and make the
indirect move with it - currently the indirect always stays refering to cell
B1 ????

--

Dave Peterson