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

 

[Domenic]

I haven't looked at your formula, however, assuming that the format is consistent, I'm guessing that this is probably what you're looking for...

=INDEX($B$3:$M$4,MATCH(Q2,$A$3:$A$4,0),MATCH(O2,$B$1:$M$1,0)+MATCH(P2,$B$2:$G$2,0)-1)

...where O2 contains 2008, P2 contains PHC, and Q2 contains Big Diary.

 

[ziad alsayed]

dear Domenic

thanks for your reply, please note that this not working neither what i need.

my file is consistent, in summary i have six sheets named ( PHC, Apapa,VI,Kano,Abuja, Ikeja). cell A1 in each sheet is named as per the sheet name. in columns and for each sheet i have the heading ( 2008,2009,2010,2011,2012) starting from B1 to F1.

in rows and for each sheet i have ( Big Diary,Small diary.......) starting from B2 to B7.

now i have the detailed sheet which i am trying to get all the data to it ( As i posted Previousely , note that i posted part of the data, so to the right i still have the data of 2010 till 2012).

what i am looking for is a smaller formula or a VBA code that will replace my formula.

hope you can assist.

 

[Domenic]

Try...

B3, copied across and down:

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

 

[ziad alsayed]

dear Domenic

thanks again for the assistance, i tried it works but i noticed wrong figures in row 6 and row 7 for all the years, which is not normal because data in rows 3, 4, 5 and 8 are accurate.

what could be the problem, please advise.

 

[Domenic]

On each sheet, does B2:B7 contain Big Diary, Small Diary..., or is it A2:A7?

 

[ziad alsayed]

dear Domenic

on each sheet i have from A2 to A7 ( big diary, small diary......)
and from B1 to F1 ia have the years (2008,2009......)
and in A1 i have the Sheet Name.

 

[Domenic]

The formula I offered should return the desired result...

 

[ziad alsayed]

thanks domenic, i will keep checking it to see what i am doing wrong.
please can you explain how the indirect formula works in this situation.

 

[Domenic]

As per Excel's help file...

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

For additional information, have a look at the INDIRECT function in the Excel help file.