Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting rows, updating linked worksheet
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 a 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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting rows, updating linked worksheet
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Inserting rows, updating linked worksheet
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())) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows meeting criteria to another worksheet | Excel Worksheet Functions | |||
Updating, Deleting and inserting rows over two Excel Sheets | Excel Worksheet Functions | |||
inserting columns within certain rows only | Excel Discussion (Misc queries) | |||
Automatically inserting rows | Excel Worksheet Functions | |||
Insert rows | Excel Worksheet Functions |