Cell Reference changes

[dave8]

I have a worksheet (Sheet1) with data in the first column....sixth column. In sheet2, each cell of the first column...sixth column points to the corresponding data in Sheet1.

The formula in sheet2 looks like this:

=IF(sheet1!A1="","",sheet1A1)

Here's the problem: If I delete the first row in Sheet1, the formula in Sheet2 will have an error: =IF(sheet1!#REF!="","",sheet1#REF!)


How to correct this so that if I delete the row(s) in Sheet1, the references in Sheet2 will still point to the corresponding data in Sheet1?

 

[ziad alsayed]

try the below


=IFERROR(IF(Sheet1!#REF!="","",Sheet1!#REF!),"")

hope this can help.

 

[dave8]

Thank you but thats not what I meant. I want to be able to keep the formula so that when the row in Sheet1 gets deleted, Sheet2 will point to the same row.

 

[VoG]

Untested

=IF(sheet1!A1="","",INDIRECT("'"sheet1'!A1")

 

[dave8]

I'm getting an error with this formula:


=IF(sheet1!A1="","",INDIRECT("'"sheet1'!A1") <----- Excel formula error

I also tried:

=IF(sheet1!A1="","",INDIRECT(sheet1!A1) <------ #REF error

 

[VoG]

Sorry

=IF(Sheet1!A1="","",INDIRECT("'sheet1'!A1"))

 

[ZVI]

Hi Dave,
1) Does your formula =IF(Sheet1!A1="","",Sheet1!A1) the same as =Sheet1!A1 ?
2) To fix the reference use the formula in the same row of Sheet2:
=INDEX(Sheet1!A:A,ROW())
It's not volatile therefore it saves the calculation time.
Regards

 

[dave8]

I have no formula in Sheet1. The formula is in Sheet2 which points to the data in Sheet1.

I just tried entering your formula in Sheet2: =INDEX(Sheet1!A:A,ROW())

I get a #VALUE! error

 

[ZVI]

Ok, let’s assume that values of Sheet1!A1:B3 are as follows:

Excel Workbook
	A	B
1	1	10
2	2	20
3	3	30
4
Sheet1

Then in Sheet2!A1:B3 we have result of your formula but with using of INDEX:

Excel Workbook
	A	B
1	1	10
2	2	20
3	3	30
4
Sheet2

========
After deleting of the 1st row in Sheet1

Excel Workbook
	A	B
1	2	20
2	3	30
3
Sheet1

Excel Workbook
	A	B
1	2	20
2	3	30
3
4
Sheet2

HTH

 

[dave8]

Yes, this is exactly what I'm looking for. Thank you very much.