what is the best option

%3CLINGO-SUB%20id%3D%22lingo-sub-2836146%22%20slang%3D%22en-US%22%3Ewhat%20is%20the%20best%20option%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836146%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Spreadsheet.%20there%20is%20one%20column%20in%20the%20spreadsheet%20that%20have%20about%20200%20row%20each%20containing%20and%20unique%20number.%20i%20need%20to%20match%20this%20number%20to%20another%20workbook%20with%2012%20sheet%2C%20each%20sheet%20containing%20column%20a%20that%20will%20contain%20some%20of%20the%20unique%20number%20that%20is%20found%20in%20the%20original%20spreadsheet.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20i%20was%20looking%20for%20the%20number%20in%20the%20workbook%20with%2012%20sheet%2C%20if%20would%20just%20do%20a%20find%20and%20choose%20the%20entire%20workbook%20and%20press%20find%20all.%20How%20exactly%20can%20i%20do%20it%20across%20workbook%20where%20ne%20of%20the%20workbook%20also%20contain%2012%20sheet%20where%20the%20information%20can%20come%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eoption%20regarding%20formulas%20or%20VBA%20will%20be%20helpful%20as%20i%20have%20to%20run%20this%20search%20one%20every%20month%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%20for%20your%20thoughts%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2836146%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836271%22%20slang%3D%22en-US%22%3ERe%3A%20what%20is%20the%20best%20option%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128296%22%20target%3D%22_blank%22%3E%40eoliams%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20thought%20regarding%20%3CEM%3E%3CSTRONG%3Eanother%3C%2FSTRONG%3E%3C%2FEM%3E%20option--if%20you're%20willing%20to%20consider%20it%20(and%20able%20to%20do%20it)%3A%20convert%20those%2012%20sheets%20into%20a%20single%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20they're%20all%20something%20like--my%20guess--monthly%20data%20concerning%20something%20(sales%2C%20production%20runs%2C%20etc).....there%20is%20little%20reason%20why%20they%20need%20to%20be%20separate%20sheets%3B%20just%20add%20a%20column%20that%20represents%20the%20month.%20That%20kind%20of%20reorganization%2Fconsolidation%20of%20the%20database%20makes%20it%20far%20more%20amenable%20to%20the%20Excel%20functions%20that%20can%20lookup%20or%20retrieve%20information%20from%20a%20database.%20We%20often%2C%20as%20humans%2C%20think%20separating%20the%20data%20into%20monthly%20(or%20regional%2C%20or%20whatever)%20sub-sheets%20is%20useful%3B%20but%20actually%20it%20interferes%20with%20exactly%20the%20kind%20of%20thing%20you%20appear%20to%20be%20wanting%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20is%20that%20a%20possibility%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2836200%22%20slang%3D%22en-US%22%3ERe%3A%20what%20is%20the%20best%20option%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2836200%22%20slang%3D%22en-US%22%3EForgot%20to%20mention%20-%20Currently%20i%20can%20get%20what%20i%20need%20by%20running%20a%20nested%20IFERROR%20formula%20with%20an%20IF%20formula%2012%20times%20for%20it%20to%20look%20into%20every%20sheet.%20but%20that%20seems%20such%20a%20long%20process%20-%3CBR%20%2F%3E%3CBR%20%2F%3Esurely%20there%20must%20be%20something%20easier%20out%20there%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi guys

 

I have an Spreadsheet. there is one column in the spreadsheet that have about 200 row each containing and unique number. i need to match this number to another workbook with 12 sheet, each sheet containing column a that will contain some of the unique number that is found in the original spreadsheet. 

 

if i was looking for the number in the workbook with 12 sheet, if would just do a find and choose the entire workbook and press find all. How exactly can i do it across workbook where ne of the workbook also contain 12 sheet where the information can come from.

 

option regarding formulas or VBA will be helpful as i have to run this search one every month

 

thanks for your thoughts

2 Replies
Forgot to mention - Currently i can get what i need by running a nested IFERROR formula with an IF formula 12 times for it to look into every sheet. but that seems such a long process -

surely there must be something easier out there

@eoliams 

 

Here's a thought regarding another option--if you're willing to consider it (and able to do it): convert those 12 sheets into a single sheet.

 

Assuming they're all something like--my guess--monthly data concerning something (sales, production runs, etc).....there is little reason why they need to be separate sheets; just add a column that represents the month. That kind of reorganization/consolidation of the database makes it far more amenable to the Excel functions that can lookup or retrieve information from a database. We often, as humans, think separating the data into monthly (or regional, or whatever) sub-sheets is useful; but actually it interferes with exactly the kind of thing you appear to be wanting to do.

 

So is that a possibility?