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/C
- Balance Sheet
- Cash Flow
Your goal is to have a workbook with a Summary sheet thatshows specific data
like:
- Net sales
- Net Profit
- Share Capital
- Cash
from 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 Excelsheet 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 sheetsfrom each tab and then show in output sheet. From that way
I would not requiredto open each sheet & link it. only open output sheet & see
& updatedata automatically when that sheet update anyone. I think index match
+indirect +address formula work for that. I create one output sheet (attached)
through this formulabut it work for only internal tab & I want formula extract
data formexternal sheet from different tab in folder. Here I attached index
match+indirect using formula sheet butit for only internal tab.in this formula
required add address command forexternal sheet name source from that folder.
Pls send any solution which is valuable (formula OR VBA) ORany good idea for
that. RegardsAmar--
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.
