How to create and edit multiple tabs simultaneously

**brandnew22** · 08-14-2013, 09:13 AM

I am trying to make an entire workbook consisting of a profile for multiple entities at the same time. I want the information for each entity to be in a different tab. The example I used is schools, and I have a list of schools, locations, programs, and cost. Say I want each school to have its own tab, and I want each tab to consist of the school I am interested in and a comparison of how it stacks up against other schools. Instead of doing this 100 times for each school, how could I replicate the tab so that I can do 100 school comparisons while doing one?

Any help is welcome!!

**XOR LX** · 08-14-2013, 09:17 AM

Hi,

If you're wanting to perform, as you say, comparisons between the data for each school, you're far better off keeping all the information in one tab. Is there a particular reason that each school should have its own individual tab?

Regards

**Pete_UK** · 08-14-2013, 09:21 AM

What is it that you want to compare? If it is Program, say, then you could apply autofilter to that column and see all schools that offer that program. If it is Location then, again, you could apply a filter and see all schools with that location, and so on for other fields. I'm not sure how separating the data into different worksheets will allow you to carry out a comparison.

Hope this helps.

Pete

**brandnew22** · 08-14-2013, 09:25 AM

I think the idea is that the data in each tab will basically be its own individual report. So rather than filter on what we want to look at, we will just go to each tab where the data is already there. Plus it will enable you to create charts and other visuals is my guess.

**XOR LX** · 08-14-2013, 09:47 AM

You can still create the charts, etc. from the data all in one tab. Trust me, unless it's absolutely necessary to split the data into separate tabs, you really are far better off keeping it all in one.

Regards

**Pete_UK** · 08-14-2013, 10:07 AM

I agree with XOR XL (although I think he meant "UNnecessary"), but if you really want to go ahead then the attached file shows how you can do it using 3 basic formulae. I've used column A in Sheet1 as a helper column (in blue), with this formula in A4:

Formula:

Please Login or Register  to view this content.

This is copied down beyond your data (to row 60 in the example) to give you a unique reference for each record.

In each of the subsidiary sheets I have put the school name in cell A1, and also used column A as another helper with this formula in A3:

Formula:

Please Login or Register  to view this content.

This will give the row number of the first record that matches A_1, but when it is copied down it will match on A_2, A_3 etc.

Then I have this formula in B3 of those subsidiary sheets:

Formula:

Please Login or Register  to view this content.

which can then be copied across to column E to retrieve the appropriate data for record 1.

Then the formulae in row 3 can be copied down as far as you need (to row 20 in the example - the hyphens show how far).

NOTE that as the subsidiary sheets are identical except for the school name in A1, then if you need other sheets you can just copy the sheet for A and rename it and change the entry in A1 of the new sheet - I've done this for school H, which was missing on your example.

Note also that the file is dynamic - if you add new records to Sheet 1 they will be reflected on the appropriate subsidiary sheet, and if you edit any data this will change automatically. You just need to ensure that the formulae are copied down far enough to accommodate all records.

Hope this helps.

Pete

**brandnew22** · 08-14-2013, 10:33 AM

I really appreciate it. That should be good, Pete. I understand that doing it all in one tab would be the easier way, but for our purposes we will end up making separate tables and trying to find: x% is above, x% is below school A, and do the same for school B, C, D, etc. Any tips with that would be appreciate too. Specifically, I know how to select multiple tabs and edit them simultaneously, but will the formulas apply to all the right rows? e.g., if A has 10 rows but B has 25, if I make edits to A will it apply to all 25 rows for B? Or will it only apply for 10 rows for B?

**brandnew22** · 08-14-2013, 10:41 AM

Also-- in that example worksheet, how do you name the tab according to what is in cell A1??

**Pete_UK** · 08-14-2013, 10:54 AM

Originally Posted by brandnew22

but will the formulas apply to all the right rows?

I think it is better to make all the subsidiary sheets the same (except for the entry in A1), so if you need 50 rows of formulae in one sheet, then make all of them have 50 rows. If you group the sheets together then you can copy the formula from row 20 down as far as you need it and it will apply to all the grouped sheets (that is how I set it up in the first place) - remember to Ungroup them afterwards. As long as you do not edit A1 while the sheets are grouped then it will be fine.

No formula can set the sheet name to be the same as a cell.

Hope this helps.

Pete

How to create and edit multiple tabs simultaneously

LinkBack

Thread Tools

Rate This Thread

Display

How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Re: How to create and edit multiple tabs simultaneously

Thread Information

Users Browsing this Thread

Similar Threads

Edit multiple excel files simultaneously

IS there a way to create multiple tabs WITHIN a spreadsheet?

How to create multiple charts for the same data on multiple worksheet simultaneously?

[SOLVED] Create multiple sheet tabs from multiple cells.

[SOLVED] How can I create multiple rows of worksheet tabs in a workbook?

Bookmarks

Bookmarks

Posting Permissions