populate list based on condition; formula

[endlessboundless]

Hello all, hope someone can help here.

In one tab called MAIN I have a list of many many projects in column A under PROJECTS. In columns B through E I have allocations of that project, so imagine 4 divisions (NORTH, WEST, SOUTH, EAST) and the project is allocated to each division differently.

Now I have a tab for each column (NORTH, WEST, SOUTH, EAST).

I would like to populate column A of each of the tabs above based on whether the project is allocated to it or not.

For example:
Operation Build; allocated to NORTH at 50 and SOUTH at 50
Operation Demolition; allocated to EAST at 100
Project Thunder; allocated to NORTH at 100

So in NORTH, i would have under PROJECTS a list of OPERATION BUILD and PROJECT THUNDER

in EAST i would have under PROJECTS a list of only OPERATION DEMOLITION

and so on...

Hope someone can help here...
TYIA!!!!!

 

[Akashwani]

Hi,

Is this something like you require?...

Excel Workbook
	A	B	C	D	E
1	PROJECTS	NORTH	SOUTH	EAST	WEST
2	Cat	50	50	*	*
3	Dog	*	*	50	50
4	Cow	100	*	*	*
5	Bull	*	100	*	*
6	Pig	*	*	100	*
7	Horse	*	*	*	100
8	Rat	100	*	*	*
9	Mouse	*	100	*	*
10	Moose	*	*	*	100
11	Donkey	*	100	*	*
12	Tiger	*	*	100	*
13	Lion	*	*	*	100
14	Puma	100	*	*	*
15	Zebra	*	50	25	25
MAIN

Excel Workbook
	A	B
1	",CELL("filename",A1))+1,256)]NORTH	4
2	PROJECTS	*
3	Cat	*
4	Cow	*
5	Rat	*
6	Puma	*
NORTH

Excel Workbook
	A	B
1	",CELL("filename",A1))+1,256)]SOUTH	5
2	PROJECTS	*
3	Cat	*
4	Bull	*
5	Mouse	*
6	Donkey	*
7	Zebra	*
SOUTH

Excel Workbook
	A	B
1	",CELL("filename",A1))+1,256)]EAST	4
2	PROJECTS	*
3	Dog	*
4	Pig	*
5	Tiger	*
6	Zebra	*
EAST

Excel Workbook
	A	B
1	",CELL("filename",A1))+1,256)]WEST	5
2	PROJECTS	*
3	Dog	*
4	Horse	*
5	Moose	*
6	Lion	*
7	Zebra	*
WEST

The formulas in A3 of sheets NORTH-EAST need to be entered using ctrl shift enter NOT enter. You can then copy them down as far as you require. You will need to change the cell references to suit yours.

Good luck.

Ak