Smaller Formula (Excel or VBA)

[ziad alsayed]

dear all

hope you can assist in making the below formula smaller either by excel or by giving a vba Solution.

appreciate any help

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		2008	2008	2008	2008	2008	2008	2009	2009	2009	2009	2009	2009
2		PHC	Apapa	VI	Kano	Abuja	Ikeja	PHC	Apapa	VI	Kano	Abuja	Ikeja
3	Big Diary	800	600	700	400	0	350	800	650	800	600	0	550
4	Small Diary	500	400	600	400	0	300	500	450	700	550	0	500
Detailed

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[ziad alsayed]

thanks for your kind advise and assistance.

 

[ziad alsayed]

dear Domenic

i am really sorry to disturb you again, i tried and still not working, if possible i can send you the file.

 

[Domenic]

See if this example helps...

PHC!A1:F3:
<TABLE style="WIDTH: 298pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=397><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 58pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=77>PHC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Big Diary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Small Diary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Data10</TD></TR></TBODY></TABLE>

Apapa!A1:F3:
<TABLE style="WIDTH: 302pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=403><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 62pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=83>Apapa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2010</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Big Diary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data13</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Small Diary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data19</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data20</TD></TR></TBODY></TABLE>

Detailed!A1:M4:
<TABLE style="WIDTH: 645pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=860><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 48pt" span=12 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 69pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=92></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2008</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2009</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2009</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>PHC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Apapa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>VI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Kano</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Abuja</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Ikeja</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>PHC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Apapa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>VI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Kano</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Abuja</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Ikeja</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Big Diary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Small Diary</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data16</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Data17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65></TD></TR></TBODY></TABLE>

Detailed!B3, copied across and down:

=IFERROR(VLOOKUP($A3,INDIRECT("'"&B$2&"'!A2:F3"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")

Adjust the references/ranges, accordingly. Does this help?

 

[ziad alsayed]

dear Domenic

i tried it, with only 2 rows it will work , if i increase the rows and adjsut the ranges ,it will not work , please check the below

PHC!A1:F7

Excel Workbook
	A	B	C	D	E	F
1	PHC	2008	2009	2010	2011	2012
2	Big Diary	data1	data2	data3	data4	data5
3	Small Diary	data6	data7	data8	data9	data10
4	Table Pad	Beta1	Beta2	Beta3	Beta4	Beta5
5	Table Calender	Beta6	Beta7	Beta8	Beta9	Beta10
6	Greeting Cards	Beta11	Beta12	Beta13	Beta14	Beta15
7	Wine Sets	Beta16	Beta17	Beta18	Beta19	Beta20
PHC

Excel 2010

Apapa!A1:F7

Excel Workbook
	A	B	C	D	E	F
1	Apapa	2008	2009	2010	2011	2012
2	Big Diary	data11	data12	data13	data14	data15
3	Small Diary	data16	data17	data18	data19	data20
4	Table Pad	Beta21	beta22	Beta23	Beta24	Beta25
5	Table Calender	Beta26	Beta27	Beta28	Beta29	Beta30
6	Greeting Cards	Beta31	Beta32	Beta33	Beta34	Beta35
7	Wine Sets	Beta36	Beta37	Beta38	Beta39	Beta40
Apapa

Excel 2010

detailed!A1:M8

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		2008	2008	2008	2008	2008	2009	2009	2009	2009	2009	2009	2009
2		PHC	Apapa	VI	Kano	Abuja	Ikeja	PHC	Apapa	VI	Kano	Abuja	Ikeja
3	Big Diary	data1	data11					data2	data12
4	Small Diary	data6	data16					data7	data17
5	Table Pad	Beta1	Beta21					Beta2	beta22
6	Table Calender	data6	data16					data7	data17
7	Greeting Cards	data1	data11					data2	data12
8	Wine Sets	Beta16	Beta36					Beta17	Beta37
Detailed

Excel 2010

Cell Formulas
Range	Formula
B3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")
B4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")
B5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")
B6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")
B7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")
B8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")
C3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&C$2&"'!A2:F7"),MATCH(C$1,INDIRECT("'"&C$2&"'!A1:F1"),0)),"")
C4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&C$2&"'!A2:F7"),MATCH(C$1,INDIRECT("'"&C$2&"'!A1:F1"),0)),"")
C5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&C$2&"'!A2:F7"),MATCH(C$1,INDIRECT("'"&C$2&"'!A1:F1"),0)),"")
C6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&C$2&"'!A2:F7"),MATCH(C$1,INDIRECT("'"&C$2&"'!A1:F1"),0)),"")
C7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&C$2&"'!A2:F7"),MATCH(C$1,INDIRECT("'"&C$2&"'!A1:F1"),0)),"")
C8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&C$2&"'!A2:F7"),MATCH(C$1,INDIRECT("'"&C$2&"'!A1:F1"),0)),"")
D3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&D$2&"'!A2:F7"),MATCH(D$1,INDIRECT("'"&D$2&"'!A1:F1"),0)),"")
D4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&D$2&"'!A2:F7"),MATCH(D$1,INDIRECT("'"&D$2&"'!A1:F1"),0)),"")
D5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&D$2&"'!A2:F7"),MATCH(D$1,INDIRECT("'"&D$2&"'!A1:F1"),0)),"")
D6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&D$2&"'!A2:F7"),MATCH(D$1,INDIRECT("'"&D$2&"'!A1:F1"),0)),"")
D7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&D$2&"'!A2:F7"),MATCH(D$1,INDIRECT("'"&D$2&"'!A1:F1"),0)),"")
D8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&D$2&"'!A2:F7"),MATCH(D$1,INDIRECT("'"&D$2&"'!A1:F1"),0)),"")
E3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&E$2&"'!A2:F7"),MATCH(E$1,INDIRECT("'"&E$2&"'!A1:F1"),0)),"")
E4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&E$2&"'!A2:F7"),MATCH(E$1,INDIRECT("'"&E$2&"'!A1:F1"),0)),"")
E5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&E$2&"'!A2:F7"),MATCH(E$1,INDIRECT("'"&E$2&"'!A1:F1"),0)),"")
E6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&E$2&"'!A2:F7"),MATCH(E$1,INDIRECT("'"&E$2&"'!A1:F1"),0)),"")
E7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&E$2&"'!A2:F7"),MATCH(E$1,INDIRECT("'"&E$2&"'!A1:F1"),0)),"")
E8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&E$2&"'!A2:F7"),MATCH(E$1,INDIRECT("'"&E$2&"'!A1:F1"),0)),"")
F3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&F$2&"'!A2:F7"),MATCH(F$1,INDIRECT("'"&F$2&"'!A1:F1"),0)),"")
F4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&F$2&"'!A2:F7"),MATCH(F$1,INDIRECT("'"&F$2&"'!A1:F1"),0)),"")
F5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&F$2&"'!A2:F7"),MATCH(F$1,INDIRECT("'"&F$2&"'!A1:F1"),0)),"")
F6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&F$2&"'!A2:F7"),MATCH(F$1,INDIRECT("'"&F$2&"'!A1:F1"),0)),"")
F7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&F$2&"'!A2:F7"),MATCH(F$1,INDIRECT("'"&F$2&"'!A1:F1"),0)),"")
F8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&F$2&"'!A2:F7"),MATCH(F$1,INDIRECT("'"&F$2&"'!A1:F1"),0)),"")
G3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&G$2&"'!A2:F7"),MATCH(G$1,INDIRECT("'"&G$2&"'!A1:F1"),0)),"")
G4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&G$2&"'!A2:F7"),MATCH(G$1,INDIRECT("'"&G$2&"'!A1:F1"),0)),"")
G5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&G$2&"'!A2:F7"),MATCH(G$1,INDIRECT("'"&G$2&"'!A1:F1"),0)),"")
G6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&G$2&"'!A2:F7"),MATCH(G$1,INDIRECT("'"&G$2&"'!A1:F1"),0)),"")
G7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&G$2&"'!A2:F7"),MATCH(G$1,INDIRECT("'"&G$2&"'!A1:F1"),0)),"")
G8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&G$2&"'!A2:F7"),MATCH(G$1,INDIRECT("'"&G$2&"'!A1:F1"),0)),"")
H3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&H$2&"'!A2:F7"),MATCH(H$1,INDIRECT("'"&H$2&"'!A1:F1"),0)),"")
H4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&H$2&"'!A2:F7"),MATCH(H$1,INDIRECT("'"&H$2&"'!A1:F1"),0)),"")
H5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&H$2&"'!A2:F7"),MATCH(H$1,INDIRECT("'"&H$2&"'!A1:F1"),0)),"")
H6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&H$2&"'!A2:F7"),MATCH(H$1,INDIRECT("'"&H$2&"'!A1:F1"),0)),"")
H7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&H$2&"'!A2:F7"),MATCH(H$1,INDIRECT("'"&H$2&"'!A1:F1"),0)),"")
H8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&H$2&"'!A2:F7"),MATCH(H$1,INDIRECT("'"&H$2&"'!A1:F1"),0)),"")
I3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&I$2&"'!A2:F7"),MATCH(I$1,INDIRECT("'"&I$2&"'!A1:F1"),0)),"")
I4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&I$2&"'!A2:F7"),MATCH(I$1,INDIRECT("'"&I$2&"'!A1:F1"),0)),"")
I5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&I$2&"'!A2:F7"),MATCH(I$1,INDIRECT("'"&I$2&"'!A1:F1"),0)),"")
I6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&I$2&"'!A2:F7"),MATCH(I$1,INDIRECT("'"&I$2&"'!A1:F1"),0)),"")
I7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&I$2&"'!A2:F7"),MATCH(I$1,INDIRECT("'"&I$2&"'!A1:F1"),0)),"")
I8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&I$2&"'!A2:F7"),MATCH(I$1,INDIRECT("'"&I$2&"'!A1:F1"),0)),"")
J3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&J$2&"'!A2:F7"),MATCH(J$1,INDIRECT("'"&J$2&"'!A1:F1"),0)),"")
J4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&J$2&"'!A2:F7"),MATCH(J$1,INDIRECT("'"&J$2&"'!A1:F1"),0)),"")
J5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&J$2&"'!A2:F7"),MATCH(J$1,INDIRECT("'"&J$2&"'!A1:F1"),0)),"")
J6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&J$2&"'!A2:F7"),MATCH(J$1,INDIRECT("'"&J$2&"'!A1:F1"),0)),"")
J7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&J$2&"'!A2:F7"),MATCH(J$1,INDIRECT("'"&J$2&"'!A1:F1"),0)),"")
J8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&J$2&"'!A2:F7"),MATCH(J$1,INDIRECT("'"&J$2&"'!A1:F1"),0)),"")
K3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&K$2&"'!A2:F7"),MATCH(K$1,INDIRECT("'"&K$2&"'!A1:F1"),0)),"")
K4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&K$2&"'!A2:F7"),MATCH(K$1,INDIRECT("'"&K$2&"'!A1:F1"),0)),"")
K5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&K$2&"'!A2:F7"),MATCH(K$1,INDIRECT("'"&K$2&"'!A1:F1"),0)),"")
K6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&K$2&"'!A2:F7"),MATCH(K$1,INDIRECT("'"&K$2&"'!A1:F1"),0)),"")
K7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&K$2&"'!A2:F7"),MATCH(K$1,INDIRECT("'"&K$2&"'!A1:F1"),0)),"")
K8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&K$2&"'!A2:F7"),MATCH(K$1,INDIRECT("'"&K$2&"'!A1:F1"),0)),"")
L3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&L$2&"'!A2:F7"),MATCH(L$1,INDIRECT("'"&L$2&"'!A1:F1"),0)),"")
L4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&L$2&"'!A2:F7"),MATCH(L$1,INDIRECT("'"&L$2&"'!A1:F1"),0)),"")
L5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&L$2&"'!A2:F7"),MATCH(L$1,INDIRECT("'"&L$2&"'!A1:F1"),0)),"")
L6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&L$2&"'!A2:F7"),MATCH(L$1,INDIRECT("'"&L$2&"'!A1:F1"),0)),"")
L7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&L$2&"'!A2:F7"),MATCH(L$1,INDIRECT("'"&L$2&"'!A1:F1"),0)),"")
L8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&L$2&"'!A2:F7"),MATCH(L$1,INDIRECT("'"&L$2&"'!A1:F1"),0)),"")
M3	=IFERROR(VLOOKUP($A3,INDIRECT("'"&M$2&"'!A2:F7"),MATCH(M$1,INDIRECT("'"&M$2&"'!A1:F1"),0)),"")
M4	=IFERROR(VLOOKUP($A4,INDIRECT("'"&M$2&"'!A2:F7"),MATCH(M$1,INDIRECT("'"&M$2&"'!A1:F1"),0)),"")
M5	=IFERROR(VLOOKUP($A5,INDIRECT("'"&M$2&"'!A2:F7"),MATCH(M$1,INDIRECT("'"&M$2&"'!A1:F1"),0)),"")
M6	=IFERROR(VLOOKUP($A6,INDIRECT("'"&M$2&"'!A2:F7"),MATCH(M$1,INDIRECT("'"&M$2&"'!A1:F1"),0)),"")
M7	=IFERROR(VLOOKUP($A7,INDIRECT("'"&M$2&"'!A2:F7"),MATCH(M$1,INDIRECT("'"&M$2&"'!A1:F1"),0)),"")
M8	=IFERROR(VLOOKUP($A8,INDIRECT("'"&M$2&"'!A2:F7"),MATCH(M$1,INDIRECT("'"&M$2&"'!A1:F1"),0)),"")

 

[ziad alsayed]

dear domenic

thanks for the assistance. it is giving an approximate match and not and excact match. by adding the false as last argument for the Vlookup it will work.

below is the formula

=IFERROR(VLOOKUP($A3,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0),FALSE),"")

thanks again for all time and effort you added is sorting out my problem.

 

[Domenic]

Sorry about that. I forgot to include the last argument for VLOOKUP. I'm glad you were able to sort it out. Thanks for the feedback.