Formula to extract data from sheet with drop down menu

%3CLINGO-SUB%20id%3D%22lingo-sub-2162083%22%20slang%3D%22en-US%22%3EFormula%20to%20extract%20data%20from%20sheet%20with%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2162083%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3ESo%20i%20have%20a%20sheet%20which%20includes%20A%20LOT%20of%20data%20for%20different%20items.%20The%20sheet%20is%20designed%20as%20such%20that%20it%20has%20drop%20down%20menu%20at%20top%2C%20and%20list%20contains%20about%2025%20items.%20Each%20item%20when%20selected%2C%20shows%20the%20data%20below%20in%20about%2019%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20i%20want%2C%20is%20to%20create%20another%20workbook%2C%20and%20link%20it%20to%20the%20master%20file%2C%20and%20it%20only%20populates%20data%20of%20like%20selective%20items%20from%20that%20list%20of%2025%2C%20and%20puts%20it%20in%20another%20format%20of%20my%20choosing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewrite%20now%20if%20i%20use%20Vlookup%2C%20or%20IF%20statement%2C%20the%20data%20does%20get%20extracted%20into%20another%20sheet%2C%20but%20as%20soon%20as%20i%20choose%20another%20item%20from%20dropdown%20of%20master%20sheet%2C%20the%20data%20in%20secondary%20sheet%20changes%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%3F%20i%20haven't%20been%20able%20to%20find%20solution%20anywhere%20on%20web%20for%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2162083%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2162103%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20extract%20data%20from%20sheet%20with%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2162103%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F977122%22%20target%3D%22_blank%22%3E%40EmmAay%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou'll%20need%20a%20VBA%20macro%20to%20copy%20the%20data%20to%20a%20new%20workbook%2C%20not%20formulas.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20help%20with%20that%2C%20could%20you%20attach%20a%20sample%20workbook%20without%20sensitive%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2162197%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20extract%20data%20from%20sheet%20with%20drop%20down%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2162197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AtO4cLU3LQyswGFUbHAPPKG_erW8%3Fe%3Dh1WwBY%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AtO4cLU3LQyswGFUbHAPPKG_erW8%3Fe%3Dh1WwBY%3C%2FA%3E%3C%2FP%3E%3CP%3Ehere%20is%20the%20link%2C%20the%20simplest%20form%20of%20the%20sheet%20i%20could%20make%20to%20give%20you%20the%20idea%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

So i have a sheet which includes A LOT of data for different items. The sheet is designed as such that it has drop down menu at top, and list contains about 25 items. Each item when selected, shows the data below in about 19 columns. 

 

What i want, is to create another workbook, and link it to the master file, and it only populates data of like selective items from that list of 25, and puts it in another format of my choosing.

 

write now if i use Vlookup, or IF statement, the data does get extracted into another sheet, but as soon as i choose another item from dropdown of master sheet, the data in secondary sheet changes as well.

 

Any help? i haven't been able to find solution anywhere on web for it.

6 Replies

@EmmAay 

You'll need a VBA macro to copy the data to a new workbook, not formulas.

If you want help with that, could you attach a sample workbook without sensitive data?

@Hans Vogelaar  https://1drv.ms/x/s!AtO4cLU3LQyswGFUbHAPPKG_erW8?e=h1WwBY

here is the link, the simplest form of the sheet i could make to give you the idea

@EmmAay 

Could you provide some examples of what the output should look like?

I have updated it in file, Sheet 3, sample of what output folders would look like

@EmmAay 

I'm lost now. I thought you wanted to select one item from the dropdown in cell C3 on Sheet1, and then copy some data.

But the output on Sheet3 lists data for 5 different items...

yess, thats the whole problem . it takes me hours to copy paste same cell data for different items into the different format.. and realllly need a solution to somehow automate it