Sorting with Cell References

Hi!

I created the attached workbook to serve as a template for encoding responses to a survey. It will be distributed to multiple users. The workbook contains six spreadsheets that refer to each other using a simple formula (=Stratification!N5). Because of this, I cannot seem to be able to sort or delete rows as the formula moves with the row instead of remaining static. (Information from the spreadsheet entitled Phil PEN is fed into the spreadsheet entitled Stratification. There is a formula in column N of the the Stratification tab, the response to which is fed back into column J of Phil PEN tab. If I wanted to sort the data in the Phil PEN tab alphabetically the formula in column J moves with the row, which no longer references the correct data) Is there a way to work around this?

Thank you!

How would I deselect the column? I have information in columns A to CD. Columns BR to CD also references information in other sheets. Thank you!

There are 16 columns where the formulas would have to be restored. F, H, J, and BR to CD. I sorted and restored the formulas manually as I don't know how to manipulate the code AlphaFrog posted and it seemed to work. The recorded macro is below. There are instances where the formula is overridden though, which is lost when I manually restored the formula by dragging the first cell down.

Please Login or Register  to view this content.

Originally Posted by kvsrinivasamurthy

Do you mean after sorting also in Column J Row 5 formula should be
==Stratification!N5
and further.
If it so , in J5
=INDIRECT("Stratification!N"&ROW())
then drag down.
Even if you sort the data including J column, Data in column J will not change.

That is what I meant and the formula you provided seemed to have worked! How would I change the below formula to include the indirect function as well? When I sorted, the 'Hypertension Confirmation'! portion continued to refer to row 10 when it should refer to row 5. Thank you!!

=IF('Hypertension Confirmation'!AL10="Confirm","Suspected",IF(AND('Hypertension Confirmation'!AL10>=140,AQ5="YES"),"Confirmed Yes",IF(AND('Hypertension Confirmation'!AL10<140,AQ5="YES"),"Confirmed No",IF(AQ5="Yes","Suspected",IF(AQ5="No","No","")))))

Formula for F5, then drag down

Please Login or Register  to view this content.

Since the cells are validated , it may not be possible to edit formula.
So, Remove the data validation. Apply the formula in the column. Then redo the validation.