I have a sheet where I have a lot of items listed in column A. I then have a helper column that changes between FALSE and TRUE if the items in that list switches from one item to another. That Works very fine with the following formula people on the nice forum so fine gave me:
=TRUE in celle DW2
And the following in DW3 and down
I now have the problem, that in another sheet I would like have the same feature, but on this sheet it is mandatory that you SELECT the Whole sheet, press copy and then INSERT it as values, because the array-formulas that this sheet contains is so ressource heavy, that it is not possible to Work with it with the formulas still "active". But doing that copy + paste as values of cause ruins that helper columns data as well.
Is there any other way to achieve the same feature after this copy paste actions has taken place? I have thought of different solutions, but none seem to Work. I tried referencing the column A values to another sheet and put the helper column in that sheet, but that didnt Work, because the conditional formatting is referencing based on the helper columns position. I also thought it would be easy if I could somehow Lock the helper column in the sheet where the copy paste action takes place, but that doesnt seem to Work either.
Can the above formula somehow be put into the conditional formatting itself? Then it surely wouldnt be affected by the copy paste actions done.
edited by moderator: Reformatted to fit text box