how do you get a formula to not update when rows are inserted
 

I am trying to insert values from another spreadsheet which I am doing by
linking to the other workbook (which will be open) as follows:

=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A12")="","",INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A12"))

I do not want the row ref's to change if I insert values on the other
workbook, hence the INDIRECT function (this works).

However, when I copy the formula down it needs to update to the new row as
follows:
=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A13")="","",INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A13"))

I need it to update as I am going to be copying it down about 1,000 lines!
And don't want to manually change.

Alternatively, is there another way of referencing these cells i.e. a named
range or something?

Thanks in advance for anyones help, he says hopefully.

Richard

how do you get a formula to not update when rows are inserted
 

You could use the ROW function to pull the row number from your current
sheet. Something like:

=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary!A"&ROW())="","",INDIRECT("'Project Timesheet
Directory.xls]Summary!A"&ROW()))

This works if your row numbers are the same in both sheets. If they are
different, then you can add or subtract the number needed from the ROW
function. So, for example, if your formula was in Row 10 of Sheet 1 and you
wanted to reference Row 12 of the Summary sheet, then ROW()+2.

HTH,
Elkar


"Richard" wrote:

I am trying to insert values from another spreadsheet which I am doing by
linking to the other workbook (which will be open) as follows:

=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A12")="","",INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A12"))

I do not want the row ref's to change if I insert values on the other
workbook, hence the INDIRECT function (this works).

However, when I copy the formula down it needs to update to the new row as
follows:
=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A13")="","",INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A13"))

I need it to update as I am going to be copying it down about 1,000 lines!
And don't want to manually change.

Alternatively, is there another way of referencing these cells i.e. a named
range or something?

Thanks in advance for anyones help, he says hopefully.

Richard

how do you get a formula to not update when rows are inserted
 

Thanks very much, worked well.

"Elkar" wrote:

You could use the ROW function to pull the row number from your current
sheet. Something like:

=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary!A"&ROW())="","",INDIRECT("'Project Timesheet
Directory.xls]Summary!A"&ROW()))

This works if your row numbers are the same in both sheets. If they are
different, then you can add or subtract the number needed from the ROW
function. So, for example, if your formula was in Row 10 of Sheet 1 and you
wanted to reference Row 12 of the Summary sheet, then ROW()+2.

HTH,
Elkar


"Richard" wrote:

I am trying to insert values from another spreadsheet which I am doing by
linking to the other workbook (which will be open) as follows:

=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A12")="","",INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A12"))

I do not want the row ref's to change if I insert values on the other
workbook, hence the INDIRECT function (this works).

However, when I copy the formula down it needs to update to the new row as
follows:
=IF(INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A13")="","",INDIRECT("'[Project Timesheet
Directory.xls]Summary'!A13"))

I need it to update as I am going to be copying it down about 1,000 lines!
And don't want to manually change.

Alternatively, is there another way of referencing these cells i.e. a named
range or something?

Thanks in advance for anyones help, he says hopefully.

Richard