Using the =Sheet1! Formula Without Getting the #REF!

S

Swaroon

Active Member

Joined

Nov 18, 2005

Messages

288

Office Version

1. 365

• Nov 23, 2005

• #2

How about using

=IF(ISERROR(Sheet2!A1)=TRUE,"",+Sheet2!A1)

when you delete a row in sheet2,
the result should be a blank line in sheet1 as opposed to the #ref!

then use the filter function to tidy the report up ?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Nov 23, 2005

• #3

If you always want to reference the cell in the same row as the formula, use:

=INDEX(Sheet1!A:A,ROW())

 

Upvote 0

GWee

New Member

Joined

Nov 8, 2005

Messages

24

• Nov 23, 2005

• #4

Thanks for trying...but these don't work!

Still giving out #value errors and other problems

Thanks for trying!

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Nov 23, 2005

• #5

Re: Thanks for trying...but these don't work!

GWee said:

Still giving out #value errors and other problems

Thanks for trying!

Click to expand...

The formula I posted won't return a #REF! error. But it might return an error if the referenced cell contains an error.

 

Upvote 0

GWee

New Member

Joined

Nov 8, 2005

Messages

24

• Nov 23, 2005

• #6

Andrew, It does work but one problem!!

When you want to copy the formula down 800 rows by using the cross bars and dragging it, it won't add the next number up for the row number. It seems the parenthesis keep it from adding up.

Is there a way to do this for 800 rows?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Nov 23, 2005

• #7

Make sure Calculation is set to Automatic. Otherwise press F9 to calculate after you drag the formula down.

 

Upvote 0

GWee

New Member

Joined

Nov 8, 2005

Messages

24

• Nov 23, 2005

• #8

Andrew Poulsom said:

Make sure Calculation is set to Automatic. Otherwise press F9 to calculate after you drag the formula down.

Click to expand...

Still does not work!
It just copies the cell exactly without calculations..

It works for other formulas without the parenthesis!??

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,361

Office Version

1. 365

Platform

1. Windows

• Nov 23, 2005

• #9

What exactly do you mean by it doesn't do calculations, it should work, and the formula should look exactly the same in every cell because it references the row via ROW() within the INDEX function

but you might want to do this:

Code:

=IF(INDEX(Sheet1!A:A,ROW())="","",INDEX(Sheet1!A:A,ROW()))

 

Upvote 0

GWee

New Member

Joined

Nov 8, 2005

Messages

24

• Nov 23, 2005

• #10

You Got it to Work!!

Thanks Guys!

 

Upvote 0