Locking a reference from one worksheet to another, in the same book(without moving when a row is add

[chad]

Hello,

I am having trouble locking a reference from one worksheet to another, in the same book (without moving when a row is added).

I want to be able to input data on the first page and it automatically populate the second page in a different order.

My problem occurs when I insert a row on the first page, the second page doesnt note the change. If I re-drag the reference formulas down each time, it will change... but I would prefer to not have to do that.

An example, with sheet names (1st page and only page that has data ever input) SchedC and 2nd page (automatically populates in a different order) Catalog would be perfect.

Thank you!

EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[Bernie Deitrick]

Chad,

One way to lock a reference is to use INDIRECT

=INDIRECT("'Sheet Name'!A1")

You can get additional cells by using

=OFFSET(INDIRECT("'Sheet Name'!A1"),COLUMN(A1)-1,ROW(A1)-1,1,1)

and copying that down and across...

HTH,
Bernie
MS Excel MVP