Help with Lookup and insert

[GKS]

Hi all.
I am pretty new to Excel so I may have the subject line wrong. I have a worksheet that contains contract numbers for different organisations. for example number 000111 is for one organisation 000112 for another etc. but under the organisation # they may have 5 totally seperate contaract so the organisation is 000111 but the contracts would be 111U601, 111U602, 111U603 and so on, so this organisation has 5 lines of data from column A to AG. On a seperate worksheet how can I express for excel to find these 5 lines of date and copy them into a new worksheet? I was thinking of finding them my their organaisation number 000111 or their contracts numbers (111U601 etc) or by the name of the organisation, which would be the easiest? I ask instead of simply copying and pasting because this data gets updated once a week is a total of over 900 rows, for 15 organisations and I wanted to create 15 seperate organisation tabs all linked to the original 900 row worksheet and for each tab to self update when the main worksheet is updated one a week. can any one help please? If I have not explained myself properly I apologise. If you need more info then please private message me, thanks in advance for any help

[PeterB]

You might want to try this example out and expand it to your needs:

Your main (Main!) sheet has something like this -

A B
1 Organisation Ref Contract No.
2 000111 111U601
3 000112 112U601
4 000111 111U602
5 000111 111U603
6 000112 112U602
7 000113 113U601
8 000114 114U601
9 000115 115U601
10 000116 116U601
11 000111 111U604
12 000113 113U602
13 000114 114U602
14 000115 115U602
15 000111 111U605

Your page for Organisation 000111 :

Cell A1 = 'Organisation
Cell B1 = '000111

Cell E2 = 'Position from top of Lookup Table used in columns A & B
Cell F2 = 'Number of rows from top of Lookup Table used in Row 3
Cell G2 = 'Area for Lookup Table used in columns A & B
Cell H2 = 'Area for Lookup Table used in column E

Cell A4 =IF(B4="","",VLOOKUP($B$1,INDIRECT($G4),COLUMN(),FALSE))
Cell B4 =VLOOKUP($B$1,INDIRECT($G4),COLUMN(),FALSE)
Cell E4 =MATCH($B$1,INDIRECT(H4),0)
Cell F4 =2
Cell G4 ="Main!$A$"&F4&":$B$15"
Cell H4 ="Main!$A$"&F4&":$A$15"

Cell A5 =IF(B5="","",VLOOKUP($B$1,INDIRECT($G5),COLUMN(),FALSE))
Cell B5 =IF(OR(VLOOKUP($B$1,INDIRECT($G5),COLUMN(),FALSE)=B4,B4=""),"",VLOOKUP($B$1,INDIRECT($G5),COLUMN(),FALSE))
Cell E5 =MATCH($B$1,INDIRECT(H5),0)
Cell F5 =SUM(E4:F4)
Cell G5 ="Main!$A$"&F5&":$B$15"
Cell H5 ="Main!$A$"&F5&":$A$15"

Copy Row 5 down as far as you need/want to go.

Columns E-H can be in any unused columns you have, but adjust the equations accordingly. If you don't want these columns to show hide them by grouping them or using white text on white background, or whatever other method you like to use.

Equations edited to overcome problems not evident until more comprehensive test done. Also, column headings added to explain use of columns E-H.