Copying cross workbook nested IF formulas

[lancetre]

Hi. Got what is hopefully an easy problem -

Have created a nested IF formula that references multiple worksheets within the workbook and from external workbooks. All this is working fine.

The problem arises when I try to copy the formula - the references within the workbook update to subsequent rows - which is what I want - but the external references remain absolute.

For example, if the reference is
='[Transition timeline.xlsx]Progress'!$J$23

When I copy the formula down the column, I want the reference to update to $J$24, $J$25, $J$26 etc

Is there a way to have these update to subsequent rows?

Don't want to have update manually as there are about 1200 rows

 

[jtakw]

Hi,

If you want the Row to update, remove the absolute reference ($) for the row:

='[Transition timeline.xlsx]Progress'!$J23

If you want the Column to update, remove the absolute reference ($) for the column:

='[Transition timeline.xlsx]Progress'!J$23

 

[lancetre]

Hi,

If you want the Row to update, remove the absolute reference ($) for the row:

='[Transition timeline.xlsx]Progress'!$J23

If you want the Column to update, remove the absolute reference ($) for the column:

='[Transition timeline.xlsx]Progress'!J$23

Perfect - thanks.
Didn't want to touch them as they were imposed by system - very much appreciated

 

[jtakw]

You're welcome, welcome to the forum.