Smaller Formula (Excel or VBA)

ziad alsayed

Well-known Member
Joined
Jul 17, 2010
Messages
665
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
ABCDEFGHIJKLM
1200820082008200820082008200920092009200920092009
2PHCApapaVIKanoAbujaIkejaPHCApapaVIKanoAbujaIkeja
3Big Diary80060070040003508006508006000550
4Small Diary50040060040003005004507005500500
Detailed
Excel 2007
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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.
 
Upvote 0
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.
 
Upvote 0
Try...

B3, copied across and down:

=IFERROR(VLOOKUP($A3,INDIRECT("'"&B$2&"'!A2:F7"),MATCH(B$1,INDIRECT("'"&B$2&"'!A1:F1"),0)),"")
 
Upvote 0
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.
 
Upvote 0
On each sheet, does B2:B7 contain Big Diary, Small Diary..., or is it A2:A7?
 
Upvote 0
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.
 
Upvote 0
The formula I offered should return the desired result...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top