Link formulas to specific cell values in a row?

[j4m1n]

I'm hoping someone can help. I have a list of unpaid invoices that I retrieve a few times a week. The raw data is pasted on one sheet (invoice data) and data from a select few columns copied to another sheet with a very simple ='invoice data'A1 formula etc. That data is then manipulated in its current position but there is no absolute link to the specific invoice data so any formulas are just to the row the invoice data is currently in. Problem is when new raw data is pasted the row position of the invoices will change as old ones go and new are added but the entries relative to a specific invoice (expected days payment is delayed, likely paid value etc) will now align with a different invoice number. Any ideas how could I overcome this?

 

[jasonb75]

There is no way of locking the formulas down to the data when you paste over it. The formulas are linked to the cells, not what is in them.

In order to try and keep the information that you want after you paste the new data, you will need some fixed values or criteria in the formula sheet (something that doesn't rely on the current formulas) that can be used to identify the records that the formulas bring in.

Beyond that, the only option that might work would be to copy and pastespecial > values (this removes the formulas from the cells and just leaves the results in there).