Microsoft Excel 2007 - Lista suspensa que captura várias colunas de outra planilha

Question

Eu tenho vasculhado a internet por isso, mas parece que não consigo encontrar uma solução.

Digamos que eu tenha 2 folhas: "Itens" e "Inventário".

Dentro de "Items", eu tenho 3 colunas:

• Nome do item
• Tipo de item
• Custo do item

Dentro de "Inventário", tenho a mesma coluna acima. No entanto, em Nome do item na planilha "Inventário", tenho uma lista suspensa que lista todos os itens encontrados em Nome do item em "Itens".

O que eu quero que aconteça é que em "Inventário", se eu selecionar um item da lista suspensa ... eu quero não apenas o Nome do Item a ser mostrado ... mas o Tipo de Item e o Custo do Item em " O inventário "também deve ser preenchido com todos os dados em" Itens "para o item correspondente que acabei de escolher.

Isso é possível?

    

Answer 1

• Sim, usando o VLOOKUP. Explicação retirada de aqui , cortesia de bigmyk2k em resposta a uma pergunta semelhante.

The work here is not done in the dropdown (which is easy to make), the work is done in each column you want to populate.

For each cell, write a VLOOKUP which finds the desired value in a table, based on the value in the drop-down cell. In the example above, you will either want to name the ranges of parent data (the information on a separate spreadsheet that you are populating from) or adjust as necessary. For this example, lets assume that we have named the parent data: Parent_Data

A1 B1 C1 D1 Full Name ID Addr1 Addr2

For instance, in the example above, if your drop down for FULLNAME is in cell A1, and you are creating the VLOOKUP for Addr Line 2 would be,

=VLOOKUP(A1,Parent_Data,4,True)

This would find the exact match for your full name, go to the third column to the right of that, and return that value.

To keep it neat, you will want to embed the vlookup in an IF statement, so that if there is no result, it doesn't show anything. That would look something like this:

=IF(ISNA(VLOOKUP(A1,Parent_Data,4,True)),"",VLOOKUP(A1,Parent_Data,4,True))

Instruções adicionais de esclarecimento:

For this explanation, I have to start with the fact that excel works either down and right, or up and left. In this case, we are in the "Down and Right" realm.

In order for the function to work, the value that you are searching with (Fullname in A1) also has to be in the leftmost column of the parent data you are searching. This is because Excel is going to search Down the first column until it finds that value, and then Right across the row until it comes to the column you specify.

Assuming that Fullname is in the A column, Excel will return the value in column D because we have told it to look in the fourth column of the row in which we find the matching value. This is the "4" in the formula. For the cell that you want to return AddressLine1, you would enter the exact same formula, but with a 3, instead of a 4.

"False" directs the VLOOKUP function to return a value for the query only if it finds an exact match. "True" will return results for the first close match. To be totally honest, I haven't figured out what Excel thinks is close in a text string...

• Aqui é um tutorial em vídeo do VLOOKUP no YouTube para instruções adicionais.