Copying a formulae down a column that includes an INDIRECT

[Guest]

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]

=indirect("B"&row())

if that's the formula in C1.

 

[Guest]

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]

maybe...

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

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?

 

[Guest]

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

 

[Guest]

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

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

HTH
JG

 

[Guest]

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

 

[Dave Peterson]

It would go in C1 replacing the other suggestion.

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

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

 

[Guest]

Can you post the formula you tried?

JG

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

 

[Guest]

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)

 

[Guest]

Hi,
Fomula using my format
=IF(AND(INDIRECT("A"&ROW())>0,INDIRECT(CHAR(63+COLUMN())&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