Fetch corresponding data value

[maikeyjohn]

Hi i need to fetch corresponding data value from a data set which is spread across the sheet
horizontally as well vertically. i need the net sales and net profit figures to fetched to a different worksheet/book. i have quarterly data horizontally and yearly data vertically. Below is a table in a horizontal manner. need your help to do it to automate or less-time-consuming

output required as:
period 6-30-2000 9-30-2000
net sales ........ ............
net profit ....... ............

[TABLE="width: 275"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Un-Audited[/TD]
[/TR]
[TR]
[TD]Date Begin[/TD]
[TD]4-1-2000[/TD]
[/TR]
[TR]
[TD]Date End[/TD]
[TD]6-30-2000[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Amount (Rs. million)[/TD]
[/TR]
[TR]
[TD]Net Sales[/TD]
[TD]17282.7[/TD]
[/TR]
[TR]
[TD]Other Income[/TD]
[TD]155.3[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[TD]17438[/TD]
[/TR]
[TR]
[TD]Expenditure[/TD]
[TD]-13705.3[/TD]
[/TR]
[TR]
[TD]Interest[/TD]
[TD]-1031.1[/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD]2701.6[/TD]
[/TR]
[TR]
[TD]Depreciation[/TD]
[TD]-1121.5[/TD]
[/TR]
[TR]
[TD]Profit before Tax[/TD]
[TD]1580.1[/TD]
[/TR]
[TR]
[TD]Tax[/TD]
[TD]-91.2[/TD]
[/TR]
[TR]
[TD]Extraordinary Items[/TD]
[TD]-474.9[/TD]
[/TR]
[TR]
[TD]Net Profit[/TD]
[TD]1014[/TD]
[/TR]
[TR]
[TD]Equity Capital[/TD]
[TD]3679.7[/TD]
[/TR]
[TR]
[TD]Result Type[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 275"]
<tbody>[TR]
[TD][TABLE="width: 275"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Un-Audited[/TD]
[/TR]
[TR]
[TD]Date Begin[/TD]
[TD]4-1-2001[/TD]
[/TR]
[TR]
[TD]Date End[/TD]
[TD]6-30-2001[/TD]
[/TR]
[TR]
[TD]Description[/TD]
[TD]Amount (Rs. million)[/TD]
[/TR]
[TR]
[TD]Gross Sales[/TD]
[TD]16114.1[/TD]
[/TR]
[TR]
[TD]Excise Duty[/TD]
[TD]-2067.7[/TD]
[/TR]
[TR]
[TD]Net Sales[/TD]
[TD]14046.4[/TD]
[/TR]
[TR]
[TD]Other Income[/TD]
[TD]58.9[/TD]
[/TR]
[TR]
[TD]Total Income[/TD]
[TD]14105.3[/TD]
[/TR]
[TR]
[TD]Expenditure[/TD]
[TD]-10947.6[/TD]
[/TR]
[TR]
[TD]Operating Profit[/TD]
[TD]3157.7[/TD]
[/TR]
[TR]
[TD]Interest[/TD]
[TD]-1145.7[/TD]
[/TR]
[TR]
[TD]Interest(Net)[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Gross Profit[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]Depreciation[/TD]
[TD]-1356.4[/TD]
[/TR]
[TR]
[TD]Profit before Tax[/TD]
[TD]655.6[/TD]
[/TR]
[TR]
[TD]Tax[/TD]
[TD]-2.7[/TD]
[/TR]
[TR]
[TD]Profit after Tax[/TD]
[TD]652.9[/TD]
[/TR]
[TR]
[TD]Extraordinary Items[/TD]
[TD]-447.6[/TD]
[/TR]
[TR]
[TD]Net Profit[/TD]
[TD]205.3[/TD]
[/TR]
[TR]
[TD]Equity Capital[/TD]
[TD]3679.7[/TD]
[/TR]
[TR]
[TD]Forex earnings/Exports[/TD]
[TD]1212.7[/TD]
[/TR]
[TR]
[TD]Basic And Diluted EPS after Extraordinary item[/TD]
[TD]0.5[/TD]
[/TR]
[TR]
[TD]Nos. of Shares - Non Promoters[/TD]
[TD]27,13,38,622.00[/TD]
[/TR]
[TR]
[TD]Percent of Shares - Non Promoters[/TD]
[TD]73.78[/TD]
[/TR]
[TR]
[TD]Result Type[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]Notes[/TD]
[TD]Notes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

What do you have to the right of the data you posted?

 

[maikeyjohn]

it has same respective values but of different quarters namely, 9-30-2000 , 12-31-2000 and 3-31-2001
same for other data sets as well

quarterly data horizontally and yearly data vertically

 

[Andrew Poulsom]

Formula in H2 copied down and across:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Type	Un-Audited	Un-Audited	Un-Audited	Un-Audited		Period	06-30-2000	09-30-2001
2	Date Begin	04-01-2000	07-01-2000	10-01-2000	01-01-2001		Net Sales	10000	7000
3	Date End	06-30-2000	09-30-2000	12-31-2000	03-31-2001		Net Profit	1000	300
4	Net Sales	10000	11000	12000	13000
5	Net Profit	1000	1100	1200	1300
6
7	Type	Un-Audited	Un-Audited	Un-Audited	Un-Audited
8	Date Begin	04-01-2001	07-01-2001	10-01-2001	01-01-2002
9	Date End	06-30-2001	09-30-2001	12-31-2001	03-31-2002
10	Net Sales	6000	7000	8000	9000
11	Net Profit	200	300	400	500
Sheet1

Cell Formulas
Range	Formula
H2	=INDEX(INDEX($B:$E,MATCH(H$1,INDEX($B:$E,0,MONTH(H$1-92)/3),FALSE),MONTH(H$1-92)/3):INDEX($B$1000:$E$1000,MONTH(H$1-92)/3),MATCH($G2,INDEX($A:$A,MATCH(H$1,INDEX($B:$E,0,MONTH(H$1-92)/3),FALSE)):$A$1000,FALSE))

 

[maikeyjohn]

Thank you mr andrew its works as smooth as silk !!!

merci !!!