Right, here we go - it is good that you are using Excel 2007 because I can shorten the formulas by using IFERROR instead of the longer Excel 2003 version.
At first sight this may look complicated but it is not - it is just that there is a lot of descriptive text. I suggest that you Copy/Paste the formulas to prevent typing errors.
I recommend that you try this on a COPY of your workbook and not the original.
First of all, it is assumed that each row of data will have a value in Column A (Region) - this column will be used to calculate the number of rows of data.
1. No change is required to your 'Master' sheet. For this example I will assume that your Master sheet is called "Master Sheet" - you will then see that the name in the formulas has to be enclosed within single quotes because there is a space in the name.
2. Create a new worksheet and give it the name "Control Sheet". This sheet name has no significance other than it is meaningful. I use such a sheet in most of my workbooks but here we are only going to use two cells on the sheet. It is not really needed (you could change the formulas instead) but I have included it to make it easier to adapt the workbook for other uses. In Cell A1 enter the name "Key Column" and colour the cell green and make the font bold; in Cell A2 enter the value 1 and colour the cell yellow; put a border round those two cells. The only reason for the formatting here is cosmetic, making it stand out. This next bit is important. Select cell A2 and give it the name "KeyColumn" (without any spaces) - you do this by entering the name in the "Name Box" that you see above the Column A and B headings - after typing that name, make sure that you press "Enter". The reason for this will become clear when you enter the formulas - the value indicates the "key" column which in this case is Column A (the Region column).
3. The 'filtered' worksheets will use Column A as a "helper" column - this can later be hidden. Create a new worksheet and give it the name "POB". Copy the column headers from the Master sheet to worksheet POB but starting in Column B. So A1 to S1 on the Master sheet will become B1 to T1 on worksheet POB.
4. Enter the following formula in Cell A1 of worksheet POB:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
The cell should now contain the name of the worksheet.
5. Enter the following formula in Cell A2 of worksheet POB:
=IFERROR(MATCH($A$1,OFFSET('Master Sheet'!$A$2,0,KeyColumn-1,COUNTA('Master Sheet'!$A:$A),1),0),"")
6. Enter the following formula in Cell A3 of worksheet POB:
=IFERROR(MATCH($A$1,OFFSET('Master Sheet'!$A$2,A2,KeyColumn-1,COUNTA('Master Sheet'!$A:$A)-A2,1),0)+A2,"")
7. Copy-down the formula that is in Cell A3 as far as you need to. This would normally be at least the same number of rows of data on the Master sheet.
8. Enter the following formula in Cell B2 of worksheet POB:
=IFERROR(INDEX(OFFSET('Master Sheet'!$A$2,0,0,COUNTA('Master Sheet'!$A:$A)-1,COUNTA('Master Sheet'!$1:$1)),$A2,COLUMN()-1),"")
and then copy-down this formula as far as the formulas were in Step 7. Also copy-across the formulas to Column T.
9. Now to create the other worksheets: Copy worksheet POB by holding down the Ctrl key, left mouse-clicking the TAB and dragging to the right. You will now have a new worksheet named "POB (2)" - rename this "POG" and you will see that the worksheet will now show the data for that region. It is as simple as that.
Additional information:
The formulas in Column A calculate the offset from row 2 where the required data is to be found.
The formulas in Columns B to T use Column A to locate the required columns.
You need to watch out when printing the filtered worksheets - make sure that you set the Print Area so that you do not print the rows with formulas but having no data.
If you need to filter on a different column, change the column number on the Control Sheet and rename your filter worksheets accordingly.
There is one 'issue' to be aware of:
If your Master sheet has blank cells, this will appear on the filtered worksheets as '0' (zero). This is an unfortunate feature of using INDEX across worksheets.
There are a couple of "solutions" for this. One is to apply a Custom Format to the cells to hide zeros (only works if you do not have some cells that must show zeros):
The Custom format is: General;General;
or something similar.
The other solution is to change the formula, entered in Step 8 to the following:
=IFERROR(IF(LEN(INDEX(OFFSET('Master Sheet'!$A$2,0,0,COUNTA('Master Sheet'!$A:$A)-1,COUNTA('Master Sheet'!$1:$1)),$A2,COLUMN()-1))=0,"",INDEX(OFFSET('Master Sheet'!$A$2,0,0,COUNTA('Master Sheet'!$A:$A)-1,COUNTA('Master Sheet'!$1:$1)),$A2,COLUMN()-1)),"")
Those zeros would have been returned for cells with a zero-length and this formula checks for that and returns a null-string.
Don't forget to hide Column A.
If you have any problems or questions, just update this thread.
