Script de folha cruzada no Excel

Question

É possível atualizar o número da linha na primeira folha das mesmas células no restante da pasta de trabalho?

Eu tenho uma folha de índice ( Index ) com links para 53 outras planilhas por semanas, Wk 1 thru Wk 53 . Preciso extrair dados de Index:A2 para Wk1:C19 , Index:A3 para Wk2:C19 , Index:A4 para Wk3:C19 , etc.

Eu tentei variações em Index!$A(wk1!sheet()) para retornar a referência da linha sem sucesso.

A única coisa que parece funcionar é escrever a referência absoluta em cada C19 em todos os 53 Wk folhas.

Existe uma maneira melhor de fazer isso?

    

Answer 1

Você precisa usar INDIRECT() para se referir a um local de célula em mudança e CELL ("nome de arquivo") para obter o nome da planilha para saber a semana / linha que a célula precisa consultar.

=INDIRECT("Index!A"
    &RIGHT(
        CELL("filename")
        ,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2)
    +1)

Vamos dividir isso:

1. Vamos identificar a planilha em que você está em cada semana. Vá para a célula C19 na sua guia Wk1. Coloque o código%. Esta etapa exige que você salve sua pasta de trabalho primeiro, caso contrário, não haverá nome de arquivo para o Excel reconhecer.

   Você deve receber algo assim:

   =CELL("filename")

2. Agora, precisamos descartar a maior parte disso para que mantenhamos apenas o bit relevante de informações, ou seja, o número da semana. Como o número da semana é sempre a última parte da string, podemos usar a função C:\Users\yourname\Documents\[yourworkbook.xlsx]Wk1 para pegá-la.

   Se você digitar apenas RIGHT() , receberá a semana número 1, mas isso só funcionará se você tiver menos de 10 semanas. Portanto, precisamos tornar a função flexível e descobrir quantos dígitos queremos manter. 1 ou 2?

   Podemos usar =RIGHT(CELL("filename"),1) para identificar o número total de caracteres em LEN() . Em seguida, podemos identificar o número de caracteres apenas no nome da planilha, subtraindo o comprimento da string até o CELL("filename") do comprimento total, assim:

   ]

   Isso lhe dá um valor de 3 ou 4, mas nós não queremos incluir os dois caracteres que compõem a parte "Wk" do nome da planilha, então vamos subtrair 2 para que também removamos "Wk":

   =LEN(CELL("filename"))-SEARCH("]",CELL("filename"))

   Então, adicionando isso à nossa função =LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2 , temos:

   RIGHT()

   A última coisa - na sua folha de Índice, a semana 1 começa na linha 2, então vamos adicionar 1 para garantir que a saída seja o número correto da linha:

   RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2))

3. RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2))+1 nos permite usar um cálculo em uma referência de célula. Adicionamos nosso número de linha à referência estática a "Index! A", concatenando os dois usando um INDIRECT() .

Colocando tudo junto, isso é:

=INDIRECT("Index!A"
    &RIGHT(
        CELL("filename")
        ,LEN(CELL("filename"))-SEARCH("]",CELL("filename"))-2)
    +1)