Don't want cell references to update to another worksheet.

[Mrmusick]

Hi,  I have worksheet B that copies cell data from worksheet A.  Every time I insert a row in sheet A, sheet B is updated and then the data from the newly inserted row in sheet A doesn't show up in sheet B.  How can I make some sort of an absolute cell reference in sheet B that won't update when the row is inserted in sheet A?  

Thanks.

[#2]

Hi @Mrmusick 

can you please attach sample file? 

Regards, Faraz Shaikh

[#3]

@Faraz Shaikh Thanks for looking into this.   I attached the sheet.  Maybe another way of looking at it is to insert a row in the 'contact history' sheet whenever I add a new contact in 'person data'. 

The problem is that when I add a new person (row) in 'person data' , I need to manually insert a row and update the 'contact history' sheet to include that person.   How can I make this happen automatically?

Thanks for your help.

[#4]

In contact history, cell A2, try:

=INDEX('Person Data'!$F$2:$F$68,ROWS(A$2:A2))

Then, copy/paste special - formula down your column.

[#5]

@JMB17 Thanks.    That works for the cells where data is being directly copied over.   

But somehow I would like to insert the row all across the 'contact history' sheet.    Is it possible to insert a row on the second sheet when a row is inserted on the first sheet?

Thanks alot.

[#6]

@Mrmusick 

I see what you mean. The remaining fields are inputs and won't match the correct family.

For a formula solution, you would probably need to set up a column on the contact log for the family ID. The user could input the family ID and then you could use a vlookup formula to pull in the adult/children names from your names table.

Otherwise, it will require vba. I would name the table containing the family names as well as the adult and children columns so vba can easily find those ranges. Also, I added a gray border at the bottom as a visual cue that all rows must be inserted at or above that border row. When the workbook opens, it would need to capture the number of rows of the table. Then, set up a worksheet change event handler to compare the current row count to the saved row count and determine if rows were inserted. Then, insert rows at the same location in the contact worksheet and link the adult/children cells back to the name table.

If it were me, I would first look to use ID numbers and lookup formulas. But, I attached a file using vba to insert the rows if you want to look at it. If the user selects a non-contiguous area when inserting rows, then it's not as straightforward to insert the rows in the contact worksheet because excel apparently starts at the bottom and passes one area at a time (so, there is a row offset calculation to handle that situation).