Referências dinâmicas a outras planilhas com Excel?
Se este post não responder a sua dúvida, por favor, deixe um comentário que eu certamente posso ajudá-lo pessoalmente.
Olá amigos,
Hoje me deparei com outra pergunta na internet e resolvi postar a resposta ao nossa amiga aqui no blog. A pergunta dele foi a seguinte:
Tenho varias planilhas de excel. Cada planilha um nome. Na planilha de controle quero deixar para o usuário escolher de qual planilha vai puxar. Exemplo:
Temos a planilha de 001 até 025. Na formula vou colocar ='C:\Teste\[001.xlsx]Plan1$A$1'
essa será a formula só que quero deixar o usuario escolher de onde ele quer puxar da 001 até a 025. Criei uma lista suspensa dando a opção mas não consigo deixar a formula principal mutável para poder escolher. Existe alguma saida?
A unica solução que achei foi mandar ele puxar todos do 001 a 025(ocultar essa planilha) e depois criar outra planilha deixando o usuario escolher e usando a formula Proc listando a primeira coluna como referencia.
Eu quero eliminar essa planilha total de intermediário para que assim não precise atualizar tudo que ele ta pedindo e apenas os numeros em questão. É claro que esses numeros serão selecionados em outro lugar.
Existe alguma saida?
quero algo tipo
='C:\Teste\[A1]Plan1$A$1'
onde eu possa escolher o arquivo final de onde vai puxar. è possivel?
Vamos usar uma simples macro em VBA?
Vá na guia Desenvolvedor (se esta guia não aparecer no seu Excel, vá em Arquivo > Opções > Personalizar Faixa de opções e marque a opção "Desenvolvedor" na segunda célula).
Em seguida clique em Visual Basic:
Na janela que se abre, de um duplo clique em sua planilha de controle:
Na janela que se abre, copie o código a seguir:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'quando usuário fizer alteração faça:
planilha = Range("A2").Text 'atribui a planilha escolhida a uma variável
Range("A1").Value = "='C:\Teste\[" & planilha & ".xlsx]Plan1'!R1C1" 'insere a variável na fórmula dinâmica
End Sub
Onde está A2 substitua pela célula da caixa suspensa, onde o usuário escolhe. Onde está A1 substitua pela célula da fórmula principal, que será dinâmica, mutável.
Feche a janela. Salve a planilha.
Toda vez que o usuário alterar a planilha de qual vai puxar, a fórmula é alterada automaticamente.
Feche a janela. Salve a planilha.
Toda vez que o usuário alterar a planilha de qual vai puxar, a fórmula é alterada automaticamente.
Não se esqueça de se increver acima ^ e receber as novidades do blog!!
Recomendo também o Curso Avançado de Excel!
Abraços
Albanir
Obg pela resposta fui eu que voce respondeu no yahoo respostas, te perguntar: a formula no final eh para ficar +/- assim
ResponderExcluir='C:\Acertos Diversos 22\[Nº 600002.xls]Fechamento'!$L$39
onde o mutavel vai ser o 600002 vai variar entre 600000 até 699999; tendo visto isso e considerando que o A1 vai puxar na minha planilha mestre ='C:\Acertos Diversos 22\[A.D. 22.xls]Plan1'!$A$1
só que considere o seguinte: http://prntscr.com/1haprw
cada celula puxa um item na planilha a linha de cima eh igual a debaixo nas formulas. O que muda? o vendedor que tem como codigos esses 600001,600002... e por ai vai a questao eh como fazer? Nao tenho nenhuma experiencia em macros
Opa Alessandro vamos resolver esse pequeno problema. Preciso tirar uma dúvida: na planilha que você mandou, o que são aqueles números lá em cima? (ex: 116, 11762, ...) Além disso, você me deu um exemplo da fórmula da coluna B, poderia me dar um exemplo da fórmula na coluna C?
ExcluirApenas aguardando a resposta para te dar a solução.
Grato
Albanir Neves
Claro os codigos 116,11762 sao os codigos do produto, essa planilha eh uma planilha de estoque, controle de estoque
ExcluirColuna B
='C:\Acertos Diversos 22\[Nº 600002.xls]Fechamento'!$L$39
Coluna C
='C:\Acertos Diversos 22\[Nº 600002.xls]Fechamento'!$L$40
Coluna D
='C:\Acertos Diversos 22\[Nº 600002.xls]Fechamento'!$L$41
soh vai mudar os numeros das linhas, é a unida coisa que muda, coluna para direita aumenta as linhas, e linha para baixo aumenta o numero da planilha q sera alterada, 600001,600002, 600003
Olá Alessandro,
ExcluirEstive estudando o seu caso e conclui que:
1) Primeiramente você deve mudar a extensão da sua planilha mestre de .xls para .xlsm. Você pode fazer isso por "Salvar como" a planilha, e escolher a opção "Pasta de trabalho habilitada para macro do Excel".
2) Baixe este exemplo:
https://docs.google.com/file/d/0B1C7k4cKJdc_MUh4elJYZkJkbUE/edit?usp=sharing
O que eu fiz? Criei a lista na coluna L de todos os números. Criei uma caixa suspensa na célula A3 para selecionar o número que desejar. Depois cliquei na guia Desenvolvedor > Visual Basic, seleciona a planilha principal e desenvolvi o código a seguir:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
planilha = Range("A3").Text
Range("b3,c3,d3,e3,f3,g3,h3,i3,j3").Value = "='C:\Acertos Diversos 22\[Nº " & planilha & ".xls]Fechamento'!$L$39"
End Sub
Teste você mesmo e veja se funciona.
No aguardo
Albanir Neves
Alessandro, a planilha funcionou? Estou curioso para saber.
ExcluirGrato
Albanir
boa tarde albanir
ResponderExcluirpreciso de uma ajuda sua +- no mesmo caso do alessandro, fiz umas planilhas aqui de vendas, como se fosse uma ficha de um cliente na loja e para cada venda efetuada salvo como a planilha mestre e renomeio ela com o nome do cliente; Minha dificuldade é a seguinte, preciso de uma planilha que me fale o total de vendas efetuadas e o total recebido. até consegui fazer porem quando eu renomeio ela com o nome do cliente esses dados se perdem, vc consegue pensar em alguma solução???
Poderia me enviar um modelo dessas planilhas?
ExcluirAbraços
Albanir
Você pode mandar um exemplo das planilhas pelo email albanirneves@gmail.com. Abraços. Albanir
ExcluirBoa noite Albanir, muito bem explicado esse seu tópico e a idéia de Print as imagens deixou bem mais explicado. Quero fazer um curso de Excel com VBA para me aprimorar e indicaram-me o ADV www.cursoadv.com.br, você conhece?
ResponderExcluirOlá Nathan, agradeço muito pelo comentário, desculpa não ter respondido antes. É o seguinte, eu não te recomendo este curso que você falou, mas é porque não o conheço, não sei a procedência, e não ouvi falar. eu te recomendo fortemente os cursos 24 horas, o link está abaixo, são cursos muito bons e didáticos. Acesse o curso exatamente com o link que te mando. E também o curso do hotmart de Excel Avançado. Espero ter ajudado, qualquer dúvida posta aí, ou envia um email para albanirneves@gmail.com. Abraços e sucesso!
Excluirhttp://www.cursos24horas.com.br/parceiro.asp?cod=promocao113089
http://hotmart.net.br/show.html?a=C364161A