Cell Reference changes

dave8

Active Member
Joined
Jul 8, 2007
Messages
267
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?
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

dave8

Active Member
Joined
Jul 8, 2007
Messages
267
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Untested

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

dave8

Active Member
Joined
Jul 8, 2007
Messages
267
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Sorry

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

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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
 
Last edited:

dave8

Active Member
Joined
Jul 8, 2007
Messages
267
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Ok, let’s assume that values of Sheet1!A1:B3 are as follows:
Excel Workbook
AB
1110
2220
3330
4
Sheet1


Then in Sheet2!A1:B3 we have result of your formula but with using of INDEX:
Excel Workbook
AB
1110
2220
3330
4
 
 
Sheet2


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

Excel Workbook
AB
1220
2330
3
Sheet1


Excel Workbook
AB
1220
2330
3
 
 
4
 
 
Sheet2


HTH
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,392
Messages
5,444,204
Members
405,273
Latest member
cswshaun

This Week's Hot Topics

Top