http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab Ask Excel and Charting Questions, Muse about Visualizations, Learn and Share - Discussion Forums - Chandoo.org en-US Wed, 19 Jun 2013 08:11:35 +0000 http://bbpress.org/?v=1.0.1 q http://chandoo.org/forums/search.php http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22757 Mon, 09 Apr 2012 14:21:41 +0000 SirJB7 22757@http://chandoo.org/forums/ <p>Hi, tmc.planning!<br /> Glad it solved your issue. Welcome back whenever needed or wanted.<br /> Regards! </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22733 Sun, 08 Apr 2012 16:25:26 +0000 Steffen19 22733@http://chandoo.org/forums/ <p>The Indirect/Address function totally is enough for me, I don´t need to get into VBA just for this project. I just tested it in another workbook and it works! Thanks for taking the time and helping me out. </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22684 Sat, 07 Apr 2012 01:10:08 +0000 SirJB7 22684@http://chandoo.org/forums/ <p>Hi, Steffen19!</p> <p>As a humble advise, don't mess with something you can handle. Just choose the manual alternative, and with a little of time, a little of effort and a lot of dedication, begin searching on how to automatize it. First define named ranges, then check if they're OK, try changing (expanding) them. Then enter into the VBA editor, study its structure, see what changes when you open or close a workbook, and go on with inserting VBA code.<br /> Despite of this, with the indications of previous posts you should be able to incorporate and eventually modify the provided code. If not, well, Chandoo's courses of VBA may lead you painlessly into this world.</p> <p>You can start here:<br /> <a href="http://chandoo.org/wp/excel-vba/" rel="nofollow">http://chandoo.org/wp/excel-vba/</a><br /> then contact Chandoo himself here:<br /> <a href="http://chandoo.org/wp/about/" rel="nofollow">http://chandoo.org/wp/about/</a></p> <p>Regards! </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22678 Fri, 06 Apr 2012 20:12:19 +0000 Steffen19 22678@http://chandoo.org/forums/ <p>HI SirJB7,</p> <p>thanks for all the work and effort you put in to help me. I´ll be looking at your workbook this weekend when I have some free time and let you know if I have more questions. RIght now, I have no clue where you put that VBA code, but I try to find it out myself before asking here.</p> <p>Thanks again</p> <p>Steffen </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22677 Fri, 06 Apr 2012 18:39:53 +0000 SirJB7 22677@http://chandoo.org/forums/ <p>@Montrey<br /> Hi!<br /> Agendas, maps, ...<br /> :)<br /> Regards! </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22666 Fri, 06 Apr 2012 16:48:20 +0000 Montrey 22666@http://chandoo.org/forums/ <p>I never knew about the Address formula! It has a lot of potential! Wewt. Anymore applications where Address is good for? </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22628 Fri, 06 Apr 2012 02:33:09 +0000 SirJB7 22628@http://chandoo.org/forums/ <p>Hi, Steffen19!</p> <p>Workbook structure:<br /> - a master sheet by default named "Master" but the name might be changed as descripted in the previous post<br /> - N sheets for countries</p> <p>Country worksheet structure:<br /> - Columns A, B, C &amp; D with similar contents, having in row 1 the data wanted to be displayed in master sheet (so I assume no titles!)<br /> - no named ranges<br /> - no alteration respect of today's content</p> <p>Master worksheet structure:<br /> - Column A, list of countries for retrieving data in next columns: drop-down list with source in column F<br /> - Columns B, C, D &amp; E equivalent to A:D of countries (in the uploaded file I set only B:D, copy whole D column to E)<br /> - Column F, list of sheet names except that defined in the code for constant "ksMaster"<br /> - one named range, SheetList, referring to =Master!$F:$F, scope workbook</p> <p>Unique formula:<br /> Master worksheet, columns B:E, as follows:<br /> =SI(ESBLANCO($A2);"No";INDIRECTO(DIRECCION(1;COLUMNA()-1;4;1;$A2))) -----&gt; in english: =IF(ISBLANK($A2),"No",INDIRECT(ADDRESS(1,COLUMN()-1,4,1,$A2)))</p> <p>Hope it helps you.</p> <p>Regards!</p> <p>EDIT: column D just copied into E </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22627 Fri, 06 Apr 2012 02:20:06 +0000 SirJB7 22627@http://chandoo.org/forums/ <p>Hi, Steffen19!</p> <p>The sheet list is updated automatically when you open the workbook, and independently of the sheet name (except for "Master") if you define the same named range as in the uploaded file: SheetList referring to =Master!$F:$F.</p> <p>Download it again, I just moved the code from the Workbook_Open event to the Worksheet_Activate event for sheet "Master", so each time you come back to this sheet (no matter what you've been doing, if adding or deleting sheets or just anything) the list will be updated.</p> <p>Note that within the VBA code, in the line:<br /> Const ksMaster = "Master"<br /> you can change the name of master sheet if you need to.</p> <p>Regards! </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22624 Fri, 06 Apr 2012 01:30:48 +0000 Steffen19 22624@http://chandoo.org/forums/ <p>Hi SirJB7,</p> <p>Thanks again for your help, but could you do me a favor and explain to me how you set that up step by step? Also I manually added a Hoja4 in your sheet and it doesn´t show up in the drop down menu (as you mentioned) however I can manually input it and it works. I can very well live with that. My point is, right now I have absolutely no clue how you set the Master up, so could you explain the formulas you used to me?</p> <p>Thank you for your work and help.</p> <p>Steffen </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22623 Fri, 06 Apr 2012 01:10:10 +0000 SirJB7 22623@http://chandoo.org/forums/ <p>Hi, Steffen19!</p> <p>Here's the link for the VBA option:<br /> <a href="http://dl.dropbox.com/u/60558749/1%20Workbook%2C%20100s%20of%20tabs%2C%20having%20overview%20in%20one%20Master%20tab%20%28for%20Steffen19%20at%20chandoo.org%29.xlsm" rel="nofollow">http://dl.dropbox.com/u/60558749/1%20Workbook%2C%20100s%20of%20tabs%2C%20having%20overview%20in%20one%20Master%20tab%20%28for%20Steffen19%20at%20chandoo.org%29.xlsm</a></p> <p>Regards! </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22622 Fri, 06 Apr 2012 00:36:39 +0000 SirJB7 22622@http://chandoo.org/forums/ <p>Hi, Steffen19!<br /> Download again the file, it's updated.<br /> And for the drop-down listbox, I should say that:<br /> a) you firstly intended to enter manually the country, so you can remove data validation form cells in column A (Data tab, Validation, Configuration, Validation Criteria, Allow/Let set to Any Value and press Ok) and go on inputting data manually<br /> b) the alternative of a list can be created by three methods:<br /> 1) pointing to a range where all the possible tab values were entered (you'll have to keep it updated)<br /> 2) entering a list in the data validation list option separated by semi-colon (you'll have to keep it updated too and copy to all cells in column A, I just used it for the example)<br /> 3) using VBA code to automatically generate or update the list (out of the scope)</p> <p>So as far as I can see, you have two reasonable options:<br /> - enter manually the country<br /> - getting involved with VBA</p> <p>For beginners I'd recommend the first one: simple, easy and displays errors immediately with the #¡REF! value.<br /> For intermediate or advanced user, I'd recommend the second one: not so simple, easy, user friendly and don't allow errors.</p> <p>Regards! </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22621 Fri, 06 Apr 2012 00:15:45 +0000 Steffen19 22621@http://chandoo.org/forums/ <p>Thank you very much for your reply, however that´s not what I´m looking for. Let me explain it in a different way. Let´s assume I have 3 tabs in my workbook: Germany1, France1, USA1. All tabs are built in the same way, since I basically just copy an empty one whenever a new investment needs to be monitored. Of course, the cashflows and so on are all different in every country. What´s the same is that the ROI is always displayed in "C1", the net profit in "D1" and the turnover in "E1". SO once you go to Germany1 and look at D1, you´ll find the net profit for Germany. If you go to France1 and look at D1, you find the net profit for France.</p> <p>Now I want to create a Master tab where I can monitor all the countries at the same time (the investments per country are logically still monitored in its respective tabs). I would like to format a table in the master tab with headers from A1 (country) to B1 (ROI) to C1 (Net profit) to D1 (turnover).</p> <p>The next step would be to fill in "Germany1" in A2, go to the Germany1 tab, copy Germany1!C1 and paste it in Mastertab!B2 to have the ROI being displayed and so on...</p> <p>What I would like to have now is that Germany1!C1, Germany1!D1 and Germany1!E1 automatically go into Mastertab!B2, Mastertab!C2 and Mastertab!D2 once I fill in Germany1 in Mastertab!A2. The same goes for the other countries.</p> <p>Also I´d like to add, that the number of tabs is not limited as I will have to continually open new tabs to monitor new countries.</p> <p>I hope that helps a little bit. Thanks again in advance. </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22617 Thu, 05 Apr 2012 23:07:56 +0000 SirJB7 22617@http://chandoo.org/forums/ <p>Hi, Steffen19!</p> <p>Give a look at this file:<br /> <a href="http://dl.dropbox.com/u/60558749/1%20Workbook%2C%20100s%20of%20tabs%2C%20having%20overview%20in%20one%20Master%20tab%20%28for%20Steffen19%20at%20chandoo.org%29.xlsx" rel="nofollow">http://dl.dropbox.com/u/60558749/1%20Workbook%2C%20100s%20of%20tabs%2C%20having%20overview%20in%20one%20Master%20tab%20%28for%20Steffen19%20at%20chandoo.org%29.xlsx</a></p> <p>It's simple to reference cells in other tabs (sheets), in the example there are 3 tabs and a drop-down box to select them, and the same formula displays the data required.</p> <p>If you want to retrieve all the cells in each tab to the master sheet, it's Ok, because INDEX function can refer to each one as with ROW() and COLUMN() parameters.<br /> But if you want to retrieve certain cells only, then the thing changes and it might be very difficult to define selection criteria depending on how you define them. Otherwise you should get involved with VBA code and then enter the rules within the code.</p> <p>Regards! </p> http://chandoo.org/forums/topic/1-workbook-100s-of-tabs-having-overview-in-one-master-tab#post-22614 Thu, 05 Apr 2012 22:16:34 +0000 Steffen19 22614@http://chandoo.org/forums/ <p>Hi guys,</p> <p>the last time I asked for help you were able to come up with a solution quickly, thank you very much again, so I´m counting on you another time. The problem this time is, I have no clue whether the solution I´m looking for is even possible in Excel.</p> <p>Like mentioned in the title, I have 1 big workbook with a lot of tabs where I´m basically monitoring the development of different investments. For every single investment, there´s 1 tab in the workbook. Every investment-tab is built up in exactly the same way, with all the data I have to put in being formatted as a table.</p> <p>Since I´m measuring a lot of data, I want to bring it all together in one Master tab, so I can easily sort which investments have the highest ROI, where the most money has been risked and how the returns have been developed over the last 30 entries and so on. I hope you understand what I mean. </p> <p>The easiest way to solve this problem would be by doing it the old fashioned copy/paste way, however since there will be a lot of tabs, this will cost me quite some time and in addition there´s also a high risk of me screwing up and making some mistakes.</p> <p>What I want to know is, is there a way for me to write a formula for the Master tab that basically pulls out all the information out of an "investment-tab" so that the only thing I have to do is simply type in the name of an investement tab (e.g. Germany1) and all the relevant data I specified before (ROI Germany1, Net Profit Germany1 ...) shows up in the Master tab?</p> <p>I hope that wasn´t too confusing and I´m looking forward to discussing my idea further in case you´re interested. As I mentioned before, I have no idea whether this is possible.</p> <p>Thanks in advance. I´m looking forward to your replies.</p> <p>Steffen </p>