Home

dropdown list

%3CLINGO-SUB%20id%3D%22lingo-sub-474630%22%20slang%3D%22en-US%22%3Edropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-474630%22%20slang%3D%22en-US%22%3E%3CP%3Ehi!%20how%20can%20i%20create%20a%20dropdown%20list%20in%20one%20excel%20document%20that%20uses%20data%2Ftable%20from%20another%20excel%20document%20(not%20woorksheet)%3F%26nbsp%3B%3C%2FP%3E%3CP%3Eeg.%20first%20document%20is%20the%20invoice%20and%20i%20would%20like%20to%20insert%20customer%20data%20from%20database%20that%20is%20in%20another%20document%20and%20is%20changing%20all%20the%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethx!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-474630%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-476536%22%20slang%3D%22en-US%22%3ERe%3A%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-476536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323876%22%20target%3D%22_blank%22%3E%40sdaji1973%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi!%20Here%20are%20two%20options.%20I%20am%20sure%20there%20are%20others.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20a%20recent%20version%20of%20Excel%20you%20can%20use%20Power%20Query%20(it%20is%20included%20in%20Excel)%20to%20grab%20the%20data%20you%20need%20from%20the%20other%20file%20and%20place%20it%20as%20a%20table%20on%20the%20invoice%20file.%20Then%20you%20can%20create%20your%20dropdown%20list%20with%20Data%20Validation%20referring%20to%20that%20table.%20When%20you%20create%20the%20query%2C%20you%20can%20set%20the%20properties%20to%20make%20it%20run%20every%20time%20the%20invoice%20file%20open%2C%20so%20that%20you%20get%20the%20most%20current%20version%20of%20the%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20you%20can%20create%20a%20sheet%20on%20the%20invoice%20file%20and%20put%20an%20array%20formula%20referring%20to%20the%20file%20where%20you%20have%20the%20client%20names.%20Array%20formulas%20as%20more%20difficult%20to%20break.%20In%20this%20case%2C%20you%20need%20to%20extend%20the%20formula%20to%20more%20cells%20than%20the%20ones%20you%20currently%20have%20to%20accommodate%20for%20new%20client%20names%20that%20might%20be%20added.%3C%2FP%3E%3CP%3EOpen%20the%20two%20workbooks.%3C%2FP%3E%3CP%3ESee%20pic%201%20for%20the%20formula%20to%20write%20on%20the%20invoice%20workbook.%20(just%20type%20%22%3D%22%20and%20then%20click%20the%20first%20cell%20with%20the%20first%20client%20name%20on%20the%20other%20workbook.%3C%2FP%3E%3CP%3EThen%2C%20INSTEAD%20of%20Enter%2C%20click%20CTRL%2BSHIFT%2BENTER%20so%20that%20the%20curly%20brackets%20appear.%3C%2FP%3E%3CP%3EThen%20extend%20the%20formula%20down%20as%20much%20as%20you%20need.%3C%2FP%3E%3CP%3EYou%20can%20close%20the%20client%20database.%3C%2FP%3E%3CP%3ENow%20you%20can%20create%20the%20data%20validation%20based%20on%20your%20list%20in%20the%20invoice%20new%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20works.%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20you%20need%20further%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-476902%22%20slang%3D%22en-US%22%3ERe%3A%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-476902%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F323876%22%20target%3D%22_blank%22%3E%40sdaji1973%3C%2FA%3E%3C%2FP%3E%3CP%3EHola%2C%20no%20se%20si%20entendi%20bien%20lo%20que%20necesitas%2C%20pero%20creo%20que%20queres%20por%20ejemplo%20ingresar%20un%20numero%20de%20cliente%20en%20la%20factura%20y%20que%20automaticamente%20te%20aparezcan%20los%20datos%20correspondientes%3F%20Estaria%20bien%20que%20subieras%20un%20ejemplo%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
sdaji1973
Occasional Visitor

hi! how can i create a dropdown list in one excel document that uses data/table from another excel document (not woorksheet)? 

eg. first document is the invoice and i would like to insert customer data from database that is in another document and is changing all the time.

 

thx! 

2 Replies

@sdaji1973 

Hi! Here are two options. I am sure there are others.

 

If you have a recent version of Excel you can use Power Query (it is included in Excel) to grab the data you need from the other file and place it as a table on the invoice file. Then you can create your dropdown list with Data Validation referring to that table. When you create the query, you can set the properties to make it run every time the invoice file open, so that you get the most current version of the data.

 

Alternatively, you can create a sheet on the invoice file and put an array formula referring to the file where you have the client names. Array formulas as more difficult to break. In this case, you need to extend the formula to more cells than the ones you currently have to accommodate for new client names that might be added.

Open the two workbooks.

See pic 1 for the formula to write on the invoice workbook. (just type "=" and then click the first cell with the first client name on the other workbook.

1.jpg

Then, INSTEAD of Enter, click CTRL+SHIFT+ENTER so that the curly brackets appear.

Then extend the formula down as much as you need.

You can close the client database.

Now you can create the data validation based on your list in the invoice new sheet.

 

I hope this works.

Let me know if you need further help.

 

 

 

 

@sdaji1973

Hola, no se si entendi bien lo que necesitas, pero creo que queres por ejemplo ingresar un numero de cliente en la factura y que automaticamente te aparezcan los datos correspondientes? Estaria bien que subieras un ejemplo 

Related Conversations