#REF Error

jfalcone1

Board Regular
Joined
Jul 13, 2007
Messages
55
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? :eek:


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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top