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 ....... ............

Type	Un-Audited
Date Begin	4-1-2000
Date End	6-30-2000
Description	Amount (Rs. million)
Net Sales	17282.7
Other Income	155.3
Total Income	17438
Expenditure	-13705.3
Interest	-1031.1
Gross Profit	2701.6
Depreciation	-1121.5
Profit before Tax	1580.1
Tax	-91.2
Extraordinary Items	-474.9
Net Profit	1014
Equity Capital	3679.7
Result Type

<tbody>
</tbody>

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

<tbody>
</tbody>

 

[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 !!!