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?

 

[dave8]

You helped me this formula yesterday: =IF(INDEX(Sheet1!A:A,ROW())="","",INDEX(Sheet1!A:A,ROW()))


Is it possible to add something to this IF statement? I want to show if column 'Z' = "*" in Sheet1, then blank, " ")

 

[ZVI]

Like this?
=IF(INDEX(Sheet1!$Z:$Z,ROW())="*","", IF(INDEX(Sheet1!A:A,ROW())="","",INDEX(Sheet1!A:A,ROW())) )

 

[dave8]

PERFECT!! Thank you very much.

 

[ZVI]

Most welcome. Glad it helped