Excel Buscar valor de célula em planilhas externas

[ppeterk]

Amigos, boa tarde!
 

Tenho uma planilha BASE que todo mês eu preencho.

 

Gostaria de uma fórmula ou VBA que fizesse semelhante a um PROCV, puxando a informação automaticamente da planilha de cada setor (6º Andar, 5º Andar e 4º Andar), salvas em pastas diferentes.

 

Em anexo: planilha BASE + 3 planilhas de exemplo que eu gostaria de puxar. A fórmula será feira nas células da Coluna C e deve considerar o diretório/endereço da pasta que a planilha do gerente está salva (Coluna D)

 

Obrigado.

BASE.xlsx 5º Andar.xlsx 4º Andar.xlsx 6º Andar.xlsx

[Midori]

@ppeterk Talvez o recurso que combina múltiplos arquivos ajude. Em Obter Dados > De Arquivo > Da Pasta. Selecione a pasta onde estão apenas os arquivos "Andar" e depois Combinar e Carregar. A tabela fica assim,

 

 

Dessa forma é só atualizar a tabela para buscar os dados atualizados de qualquer arquivo. Se um novo arquivo no mesmo padrão for criado na pasta, os registros serão atualizados na tabela. Para puxar dados mais específicos você pode criar outra tabela na planilha para buscar nessa.

[ppeterk]

@Midori , obrigado pela resposta. Nesse caso, essa opção não seria viável. Cada planilha está em diretório diferente. Coloquei todas em uma pasta de exemplo. 

 

Eu realmente preciso de uma fórmula que fosse em cada diretório, puxando as informações pra coluna C. É possível? Previamente, a pessoa teria que preencher o caminho de onde está cada planilha.

[ppeterk]

@Midori , a grosso modo, seria a seguinte ideia:

 

[Midori]

16 horas atrás, ppeterk disse:

Nesse caso, essa opção não seria viável. Cada planilha está em diretório diferente. Coloquei todas em uma pasta de exemplo. 

A importação pode ser feita individualmente. Em vez de escolher "Da Pasta" pode ser "Da Pasta de Trabalho". Em Carregar Para > Importar Dados selecione Apenas Criar Conexão. Vai ficar assim,

 

 

Depois é só juntar tudo numa tabela, se clicar com o direito sobre uma tabela verá a opção. Aqui é Append,

 

 

Com fórmula dá para passar o diretório a partir de uma célula com indireto, mas se fechar a planilha vai dar erro na fórmula. Com macro é possível fazer uma busca nos diretórios para abrir a planilha e atualizar os dados na ativação de algum evento (botão ou change talvez).

 

[ppeterk]

@Midori Perfeito, Midori. Agradeço muito o retorno. Nesse caso, poderia me ajudar com a macro, por gentileza? Seria interessante um botão para a pessoa clicar.

 

 

[Midori]

@ppeterk A macro vai pegar o caminho do arquivo na coluna 4 e aplicar o filtro para Setor e Lider. O retorno da função será o valor da nota da primeira filtrada.

 

Sub MacroBuscaNota()
    Const COL_SETOR As Integer = 1
    Const COL_LIDER As Integer = 2
    Const COL_NOTA  As Integer = 3
    Const COL_DIR   As Integer = 4
    Dim Base        As Range
    Dim Arquivo     As Workbook
    Dim Linha       As Long
    Dim Nota        As Double
    
    Set Base = ThisWorkbook.ActiveSheet.[A1].CurrentRegion
    
    For Linha = 1 To Base.Rows.Count
        If Dir(Base(Linha, COL_DIR).Value) <> "" Then
            Set Arquivo = Workbooks.Open(Filename:=Base(Linha, COL_DIR).Value)
            
            Nota = BuscaNota( _
                Arquivo.ActiveSheet.[A1].CurrentRegion, _
                Base(Linha, COL_SETOR).Value, _
                Base(Linha, COL_LIDER).Value)
                         
            If Nota >= 0 Then
                Base(Linha, COL_NOTA).Value = Nota
            End If
                         
            Call Arquivo.Close(False)
        End If
    Next Linha
End Sub

 

[Midori]

Faltou a função.

 

Function BuscaNota(Tabela As Range, ByVal Setor As String, ByVal Lider As String) As Double
    Const COL_SETOR As Integer = 1
    Const COL_LIDER As Integer = 2
    Const COL_NOTA  As Integer = 3
    Dim Area        As Range
    
    If Tabela.Worksheet.AutoFilterMode = True Then
        Call Tabela.Rows(1).AutoFilter
    End If
    
    Call Tabela.Rows(1).AutoFilter(Field:=COL_SETOR, Criteria1:=Setor)
    Call Tabela.Rows(1).AutoFilter(Field:=COL_LIDER, Criteria1:=Lider)
   
    Set Area = Tabela.SpecialCells(xlCellTypeVisible)
    
    If Area.Areas.Count > 1 Then
        BuscaNota = Area.Areas(2)(COL_NOTA).Value
    ElseIf Area.Rows.Count > 1 Then
        BuscaNota = Area(2, COL_NOTA).Value
    Else
        BuscaNota = -1
    End If
End Function

 

[ppeterk]

@Midori , boa tarde! Fico muito feliz por me ajudar nesse caso. Poderia só me explicar como eu uso os parâmetros? Tentei dessa forma:

 

 

2º dúvida: O caminho do arquivo na coluna "Diretório" está escrita corretamente? "Downloads\6º Andar"

 

Obrigado.

[Midori]

A função deve ser usada no ambiente VBA para ser chamada por outro procedimento como fiz em MacroBuscaNota.

 

Sobre o diretório, nessa coluna considerei o caminho completo do arquivo, p.ex: C:\Downloads\Arquivos\6º Andar.xlsx.

[ppeterk]

@Midori, perfeitamente. Deu muito certo. Agradeço imensamente.

 

Uma última dúvida: Caso a informação que eu esteja querendo buscar não seja um número e sim uma palavra, o que eu deveria alterar na macro ?

 

[Midori]

@ppeterk Na função é só alterar o tipo de Double para String e remover o Else onde tem BuscaNota = -1. E na Sub o teste (If Nota >= 0) também pode ser removido.