software

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(quot;Bquot;amp;row())

if that's the formula in C1.

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

quot;Dave Petersonquot; wrote:

gt; =indirect(quot;Bquot;amp;row())
gt;
gt; if that's the formula in C1.
gt;
gt; A.Webb wrote:
gt; gt;
gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; B1 ????
gt;
gt; --
gt;
gt; Dave Peterson
gt;

maybe...

in C1:
=offset(c1,0,-1)
A.Webb wrote:
gt;
gt; Thanks Dave, much appreciated - this seems to work as long as I do not insert
gt; any columns in the spreadsheet prior to column B - is there a way I can
gt; always make it refer to the same column, regardless of whether the column
gt; changes letter as columns are inserted or deleted in the future?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; =indirect(quot;Bquot;amp;row())
gt; gt;
gt; gt; if that's the formula in C1.
gt; gt;
gt; gt; A.Webb wrote:
gt; gt; gt;
gt; gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; gt; B1 ????
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

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(quot;Bquot;amp;row()) - where the quot;bquot; is ??

quot;Dave Petersonquot; wrote:

gt; maybe...
gt;
gt; in C1:
gt; =offset(c1,0,-1)
gt;
gt;
gt;
gt; A.Webb wrote:
gt; gt;
gt; gt; Thanks Dave, much appreciated - this seems to work as long as I do not insert
gt; gt; any columns in the spreadsheet prior to column B - is there a way I can
gt; gt; always make it refer to the same column, regardless of whether the column
gt; gt; changes letter as columns are inserted or deleted in the future?
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; =indirect(quot;Bquot;amp;row())
gt; gt; gt;
gt; gt; gt; if that's the formula in C1.
gt; gt; gt;
gt; gt; gt; A.Webb wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; gt; gt; B1 ????
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

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

=INDIRECT(CHAR(63 COLUMN())amp;ROW())

HTH
JG
quot;A.Webbquot; wrote:

gt; Thanks Dave, much appreciated - this seems to work as long as I do not insert
gt; any columns in the spreadsheet prior to column B - is there a way I can
gt; always make it refer to the same column, regardless of whether the column
gt; changes letter as columns are inserted or deleted in the future?
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; =indirect(quot;Bquot;amp;row())
gt; gt;
gt; gt; if that's the formula in C1.
gt; gt;
gt; gt; A.Webb wrote:
gt; gt; gt;
gt; gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; gt; B1 ????
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

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

quot;pinmasterquot; wrote:

gt; If columns are only inserted before B and not after then mayby:
gt;
gt; =INDIRECT(CHAR(63 COLUMN())amp;ROW())
gt;
gt; HTH
gt; JG
gt;
gt;
gt;
gt; quot;A.Webbquot; wrote:
gt;
gt; gt; Thanks Dave, much appreciated - this seems to work as long as I do not insert
gt; gt; any columns in the spreadsheet prior to column B - is there a way I can
gt; gt; always make it refer to the same column, regardless of whether the column
gt; gt; changes letter as columns are inserted or deleted in the future?
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; =indirect(quot;Bquot;amp;row())
gt; gt; gt;
gt; gt; gt; if that's the formula in C1.
gt; gt; gt;
gt; gt; gt; A.Webb wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; gt; gt; B1 ????
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;

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:
gt;
gt; Sorry, my Excel skills are only average !! where would this go in the
gt; formaule you used as the exmaple in the previous response
gt; =indirect(quot;Bquot;amp;row()) - where the quot;bquot; is ??
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; maybe...
gt; gt;
gt; gt; in C1:
gt; gt; =offset(c1,0,-1)
gt; gt;
gt; gt;
gt; gt;
gt; gt; A.Webb wrote:
gt; gt; gt;
gt; gt; gt; Thanks Dave, much appreciated - this seems to work as long as I do not insert
gt; gt; gt; any columns in the spreadsheet prior to column B - is there a way I can
gt; gt; gt; always make it refer to the same column, regardless of whether the column
gt; gt; gt; changes letter as columns are inserted or deleted in the future?
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =indirect(quot;Bquot;amp;row())
gt; gt; gt; gt;
gt; gt; gt; gt; if that's the formula in C1.
gt; gt; gt; gt;
gt; gt; gt; gt; A.Webb wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; gt; gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; gt; gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; gt; gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; gt; gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; gt; gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; gt; gt; gt; B1 ????
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

Can you post the formula you tried?

JG
quot;A.Webbquot; wrote:

gt; Thanks, sorry I am still having no luck making this work if the data in
gt; column B moves to column C after I insert a new column after column A ????
gt;
gt; quot;pinmasterquot; wrote:
gt;
gt; gt; If columns are only inserted before B and not after then mayby:
gt; gt;
gt; gt; =INDIRECT(CHAR(63 COLUMN())amp;ROW())
gt; gt;
gt; gt; HTH
gt; gt; JG
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;A.Webbquot; wrote:
gt; gt;
gt; gt; gt; Thanks Dave, much appreciated - this seems to work as long as I do not insert
gt; gt; gt; any columns in the spreadsheet prior to column B - is there a way I can
gt; gt; gt; always make it refer to the same column, regardless of whether the column
gt; gt; gt; changes letter as columns are inserted or deleted in the future?
gt; gt; gt;
gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; =indirect(quot;Bquot;amp;row())
gt; gt; gt; gt;
gt; gt; gt; gt; if that's the formula in C1.
gt; gt; gt; gt;
gt; gt; gt; gt; A.Webb wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; gt; gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; gt; gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; gt; gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; gt; gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; gt; gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; gt; gt; gt; B1 ????
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt;

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 quot;aquot; and quot;bquot;
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(quot;aquot;amp;ROW())gt;0,INDIRECT(quot;bquot;amp;ROW())gt; 0),SUM(Data!Y3-INDIRECT(quot;aquot;amp;ROW()) INDIRECT(quot;bquot;amp;ROW()) Data!Y4),0 )

quot;pinmasterquot; wrote:

gt; Can you post the formula you tried?
gt;
gt; JG
gt;
gt;
gt;
gt; quot;A.Webbquot; wrote:
gt;
gt; gt; Thanks, sorry I am still having no luck making this work if the data in
gt; gt; column B moves to column C after I insert a new column after column A ????
gt; gt;
gt; gt; quot;pinmasterquot; wrote:
gt; gt;
gt; gt; gt; If columns are only inserted before B and not after then mayby:
gt; gt; gt;
gt; gt; gt; =INDIRECT(CHAR(63 COLUMN())amp;ROW())
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt; JG
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;A.Webbquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Thanks Dave, much appreciated - this seems to work as long as I do not insert
gt; gt; gt; gt; any columns in the spreadsheet prior to column B - is there a way I can
gt; gt; gt; gt; always make it refer to the same column, regardless of whether the column
gt; gt; gt; gt; changes letter as columns are inserted or deleted in the future?
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Dave Petersonquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; =indirect(quot;Bquot;amp;row())
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; if that's the formula in C1.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; A.Webb wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; For example, in C1 I have a long formulae that includes multiple INDIRECT
gt; gt; gt; gt; gt; gt; references to B1 which is where I input a data figure - column B has data
gt; gt; gt; gt; gt; gt; that is dragged and dropped between cells up and down that column (hence the
gt; gt; gt; gt; gt; gt; reason for including the indirect) column C provides the answer. After
gt; gt; gt; gt; gt; gt; setting up a formulae in C1 how can I copy this down column C and make the
gt; gt; gt; gt; gt; gt; indirect move with it - currently the indirect always stays refering to cell
gt; gt; gt; gt; gt; gt; B1 ????
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Dave Peterson
gt; gt; gt; gt; gt;