Como posso transformar uma coluna calculada em uma coluna de dados em uma tabela do Excel?

Question

Eu tenho uma grande tabela do Excel (44 colunas, 1000 + linhas), mas uma das colunas foi acidentalmente transformada em uma coluna calculada (Veja aqui ). O resultado é que, agora, quando os usuários tentam adicionar novas linhas à tabela (arrastando a alça do canto inferior direito para baixo), ela preenche inadequadamente as novas linhas dessa coluna com essa fórmula, resultando em erros de planilha.

Eu tentei limpar o conteúdo de cada célula nesta coluna que eu possa encontrar com a fórmula, mas ainda a trata como uma coluna calculada, em vez de normal.

Como posso desligar isso? Eu posso empregar o VBA, se necessário, no entanto, não posso me livrar de colunas calculadas ou de preenchimento automático para toda a tabela porque usamos extensivamente.

Além disso, como posso evitar que os usuários façam isso acidentalmente novamente no futuro? Eles só devem inserir dados nas primeiras 10 colunas, não em fórmulas, mas naturalmente elas nem sempre seguem as instruções ...

    

Answer 1

A maneira mais rápida que encontrei de remover um grande número de fórmulas é selecionar a coluna, copiá-la e depois colá-la usando a função Paste Values .

Para evitar que os usuários repetissem isso novamente, a única maneira que eu sei é usar os recursos Protect Sheet e Protect Workbook . Se eles podem ser adaptados ao seu cenário em particular, isso realmente depende do que você está tentando proteger e do que você ainda precisa permitir que os usuários façam. Geralmente, no entanto, você pode limitar os usuários a editar apenas determinados intervalos, colunas ou linhas bloqueando todas as outras células. Existem opções para permitir que os usuários ainda insiram novas linhas, se desejado. Infelizmente, não conheço uma maneira de bloquear as planilhas para que os usuários possam inserir dados, mas não possam adicionar fórmulas.

Você pode usar Validação de dados para adicionar uma mensagem de entrada lembrando os usuários de não inserirem fórmulas. Não os interromperia, mas ao menos os lembraria constantemente. A Validação de dados também pode ser usada para forçá-los a inserir um tipo de dados específico, mas não interromperia uma fórmula que fornecesse o tipo de dados correto como resultado.

ATUALIZAÇÃO:

Você pode rejeitar explicitamente fórmulas no Excel usando o evento Worksheet_Change, assim:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("C:C"), Range(Target.Address)) Is Nothing Then
        If Range(Target.Address).HasFormula Then
            Range(Target.Address).Value = ""
            MsgBox "You may not enter formulas on this sheet!"
        End If
    End If
End Sub

Neste exemplo, o intervalo verificado para fórmulas é a totalidade da coluna C. Você pode definir qualquer intervalo que deseja verificar e, em seguida, apagar a fórmula, alterá-la para outra coisa, avisar o usuário, etc.

    

Answer 2

TechRepublic tem:

Function IdentifyFormulaCellsUsingCF(rng As Range) As Boolean  
IdentifyFormulaCellsUsingCF = rng.HasFormula
End Function

que pode ser usado para realçar (digamos, amarelo) e formatar (digamos, "NÃO USE FÓRMULA") células com fórmulas.

Não impede a inserção de fórmulas, mas fornece um aviso mais imediato do que a mensagem Localizar > Fórmulas.