Inserting rows, updating linked worksheet

[Patti]

Hi.

I have a workbook set up with a sheet called "Tracker", with columns
set up as:

center# firstname lastname centername city state zip
country
1 John Doe Research Inc Anycity NY
01234 United States
2 Bob Smith Insight Corp Anytown NJ
56791 United States

All information is set up as straight text.

Now I have a protected worksheet linked to it, that pulls the
information into a different format (I have several different sheets
like these):

center # Name Address
1 John Doe Research Inc
Anycity, NY 01234
United States
2 Bob Smith Insight Corp
Anytown, NJ 56791
United States


The formulas for these columns are:

center # is:
=T(Tracker!A2)

Name is:
=T(Tracker!B2&" ")& T(Tracker!C2)

Address is:
=T(Tracker!D2)&CHAR(10)& T(Tracker!E2&" ")& T(Tracker!F2&" ")&
T(Tracker!G2)&CHAR(10)&T(Tracker!H2)

I've copied these formulas down the sheet for 1000 rows.

When I copy and paste information columns of information into the
Tracker and press F9, the linked sheet updates beautifully!

The problem I have is when it is necessary to update the tracker by
inserting a row. When a new row is inserted in the tracker, the linked
sheet simply ignores the new row and adjusts, such that where it was:

Tracker!A2
Tracker!A3
Tracker!A4

After insertion it is:

Tracker!A2
Tracker!A4
Tracker!A5

What I want after insertion is:

Tracker!A2
Tracker!A3
Tracker!A4
Tracker!A5

I've tried to mess around with absolute formulas instead of the
relative ones I'm using, but it did not help either.

I dont want the linked sheet formulas to shift. I want each row to
always point to the same row, even if the data shifts on the Tracker
sheet.

If you are still with me, thanks for reading this all the way through!

 

[John Michl]

Try using a formula that bases the the row from the Tracker sheet on
the row that the formula is in. For instance, if the formula below is
in row 12 of some sheet, the result of the formula would actually be
=Tracker!A12. If necessary add or subtract an offset number such as
&ROW()+2) if you need to adjust it. This should get you started.

=INDIRECT("Tracker!A"&ROW())

- John

 

[Patti]

Dear John,

Thank you so much for your very helpful reply.

I added a T function to the formula, and copied it down the sheet. It
worked beautifully!

=T(INDIRECT("Tracker!A"&ROW()))