#REF Error

[jfalcone1]

Hey all,

I have two worksheets that directly link a range of cells on both sheets, ie: Output!a1 = Database!a1.

In this case, the Database sheet contains data that I want to manipulate by deleting entries that are defined in rows. however, when I delete one of the source rows I get a #REF error on the Output sheet.



Is there any way to avoid this so the entries will automatically shift down a row if a row has been deleted?


So if i were to delete row two on the source page and the data from row three moves down, that would, in turn, cause the row three data on the output page to move down one row as well.

 

[Andrew Poulsom]

Try:

=INDEX(Database!A:A,ROW(A1))

 

[jfalcone1]

I need to clarify that these references are not a verbatim copy, they mis and match cells within the same row. Is there a way to manipulate the INDEX function to work on an individual cell basis?

 

[Richard Schollar]

The second argument to INDEX (ie the ROW(A1) in Andrew's formula) is the index number of the row of the given range (A:A) to return. So if you want to manipulate this directly you can use:

=INDEX(Database!A:A,1)

=INDEX(Database!A:A,10)

=INDEX(Database!A:A,1000)

 

[jfalcone1]

Is there a way to do this without involving the entire row o arrays, but just individual cells? I cant quite get this to work in my instance.

 

[Richard Schollar]

I'm sorry I don't follow you - which bit isn't working for you? The formulas that Andrew and I posted only return a result for a single cell.

 

[jfalcone1]

yep i was being foolish, got it


thanks for your help.

 

[jfalcone1]

NEVERMIND, i still get a #Ref when i delete a source row.

 

[jfalcone1]

NEVERMIND, i still get a #Ref when i delete a source row.

 

[Andrew Poulsom]

What was the original formula that returns #REF! when you delete a source row?