• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

1 Workbook, 100s of tabs, having overview in one Master tab

S

Steffen19

New Member
Hi guys,


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.


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.


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.


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.


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?


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.


Thanks in advance. I´m looking forward to your replies.


Steffen
 
Hi, Steffen19!


Give a look at this file:

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


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.


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.

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.


Regards!
 
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.


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).


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...


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.


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.


I hope that helps a little bit. Thanks again in advance.
 
Hi, Steffen19!

Download again the file, it's updated.

And for the drop-down listbox, I should say that:

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

b) the alternative of a list can be created by three methods:

1) pointing to a range where all the possible tab values were entered (you'll have to keep it updated)

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)

3) using VBA code to automatically generate or update the list (out of the scope)


So as far as I can see, you have two reasonable options:

- enter manually the country

- getting involved with VBA


For beginners I'd recommend the first one: simple, easy and displays errors immediately with the #¡REF! value.

For intermediate or advanced user, I'd recommend the second one: not so simple, easy, user friendly and don't allow errors.


Regards!
 
Hi, Steffen19!


Here's the link for the VBA option:

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


Regards!
 
Hi SirJB7,


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?


Thank you for your work and help.


Steffen
 
Hi, Steffen19!


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.


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.


Note that within the VBA code, in the line:

Const ksMaster = "Master"

you can change the name of master sheet if you need to.


Regards!
 
Hi, Steffen19!


Workbook structure:

- a master sheet by default named "Master" but the name might be changed as descripted in the previous post

- N sheets for countries


Country worksheet structure:

- Columns A, B, C & D with similar contents, having in row 1 the data wanted to be displayed in master sheet (so I assume no titles!)

- no named ranges

- no alteration respect of today's content


Master worksheet structure:

- Column A, list of countries for retrieving data in next columns: drop-down list with source in column F

- Columns B, C, D & E equivalent to A:D of countries (in the uploaded file I set only B:D, copy whole D column to E)

- Column F, list of sheet names except that defined in the code for constant "ksMaster"

- one named range, SheetList, referring to =Master!$F:$F, scope workbook


Unique formula:

Master worksheet, columns B:E, as follows:

=SI(ESBLANCO($A2);"No";INDIRECTO(DIRECCION(1;COLUMNA()-1;4;1;$A2))) -----> in english: =IF(ISBLANK($A2),"No",INDIRECT(ADDRESS(1,COLUMN()-1,4,1,$A2)))


Hope it helps you.


Regards!


EDIT: column D just copied into E
 
I never knew about the Address formula! It has a lot of potential! Wewt. Anymore applications where Address is good for?
 
HI SirJB7,


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.


Thanks again


Steffen
 
Hi, Steffen19!


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.

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.


You can start here:

http://chandoo.org/wp/excel-vba/

then contact Chandoo himself here:

http://chandoo.org/wp/about/


Regards!
 
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.
 
Hi, tmc.planning!

Glad it solved your issue. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top