-------------------------------------------- On Tue, 1/10/17, Paul Schreiner <schreiner_p...@att.net> wrote:
Subject: Re: $$Excel-Macros$$ Index+Match+Indirect+Address formula To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Date: Tuesday, January 10, 2017, 3:59 PM OK, I think I've worked through your requirements...You have a folder with more than 100 workBOOKS.Each workbook is for a specific company (AAB, Infy, TATA, etc.)Each workbook has three workSHEETS (often called "Sheets" and are displayed as "tabs" across the bottom of the Excel window) for:Profit & loss A/CBalance SheetCash Flow Your goal is to have a workbook with a Summary sheet thatshows specific data like:Net salesNet ProfitShare CapitalCashfrom each of the 100+ workbooks----------------Your use of the =INDIRECT() function is nicely done.Many users have difficulty with the concept of using cell values to "indirectly" refer to other worksheets. However, in this case, I wouldn't recommend it. As you no doubt are aware, you cannot use this to refer to a worksheet that doesn't exist.in the same way, you CAN use this to refer to a worksheet in another workbook, but the workbook must be OPEN.That means that if your summary sheet refers to sheets in 100 workbooks, all of the workbooks would need to be open at the same time!Which would require quite a bit in system resources. this kind of thing can be easily done with VBA. I can help with this if you'd like.I've done it before with nearly 300 workbooks. Paul----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- On Tuesday, January 10, 2017 2:34 AM, amar takale <amartak...@gmail.com> wrote: Dear Friends There are more than 100 Excel Sheets (Company Name like AAB, Infy, TATA) in folder. Each Sheet there are 3 tab (Profit & loss A/C, Balalnce Sheet, Cash Flow from 10 years).I Required Separate one Output Excel sheet where I extract specific data (like Net sales, Net Profit, Share Capital, Cash) from Each sheet (like ABB,TATA) from each tab. I require excel formula like extract data from all sheets from each tab and then show in output sheet. From that way I would not required to open each sheet & link it. only open output sheet & see & update data automatically when that sheet update anyone. I think index match +indirect +address formula work for that. I create one output sheet (attached) through this formula but it work for only internal tab & I want formula extract data form external sheet from different tab in folder. Here I attached index match+indirect using formula sheet but it for only internal tab.in this formula required add address command for external sheet name source from that folder. Pls send any solution which is valuable (formula OR VBA) OR any good idea for that. Regards Amar -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. DESFasURAREA EVENIMENTELOR DE LA 23 AUGUST 1944. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at https://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
