Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

=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

Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

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

Copying a formulae down a column that includes an INDIRECT
 

This is the current 'end' formulae that takes variable data from colums A and
B - it works okay unless I insert a new row - how can I make the "a" and "b"
references equal to that column even if the column letter changes ? Sorry my
advanced excel is not very good, any help appreciated. Thanks,

=IF(AND(INDIRECT("a"&ROW())0,INDIRECT("b"&ROW()) 0),SUM(Data!Y3-INDIRECT("a"&ROW())+INDIRECT("b"&ROW())+Data!Y4),0 )

"pinmaster" wrote:

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

Copying a formulae down a column that includes an INDIRECT
 

Hi,
Fomula using my format:
=IF(AND(INDIRECT("A"&ROW())0,INDIRECT(CHAR(63+COL UMN())&ROW())0),Data!Y3-INDIRECT("A"&ROW())+INDIRECT(CHAR(63+COLUMN())&ROW ())+Data!Y4,0)

Formula using Dave's format:
=IF(AND(INDIRECT("A"&ROW())0,OFFSET(C1,0,-1)0),Data!Y3-INDIRECT("A"&ROW())+OFFSET(C1,0,-1)+Data!Y4,0)

that is, assuming Column A will not change, if you intend to insert columns
prior to column A then you will need another aproach.

One way might be to select row 1 and inserting another row, making the
current row 1 row 2. Then in A1 put =CHAR(64+COLUMN()), and the formula would
be:
=IF(AND(INDIRECT($A$1&ROW())0,OFFSET(C1,0,-1)0),Data!Y3-INDIRECT($A$1&ROW())+OFFSET(C1,0,-1)+Data!Y4,0)
you could then hide row 1 or changing the font to white to make it invisible.

HTH
JG


"A.Webb" wrote:

This is the current 'end' formulae that takes variable data from colums A and
B - it works okay unless I insert a new row - how can I make the "a" and "b"
references equal to that column even if the column letter changes ? Sorry my
advanced excel is not very good, any help appreciated. Thanks,

=IF(AND(INDIRECT("a"&ROW())0,INDIRECT("b"&ROW()) 0),SUM(Data!Y3-INDIRECT("a"&ROW())+INDIRECT("b"&ROW())+Data!Y4),0 )

"pinmaster" wrote:

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