Quote:
Originally Posted by Excel Dumbo
Hello Dear friends,
Please help me to auto populate monthly and quarterly data data from a table of monthly figures. Please see attached.
Basically just by by updating the current month in B2 cell, data should auto matically populate from the table shown
Say if I type July in B2 cell, it should automatically populate the values from the table on the right for the month of July
Similarly for the Quarter.
Appreciate your attention
|
Hi Excel Dumbo
Basically i have Been able to figure out how to make it look for the month specified and match this to the relevant column.
to do this you need to use a INDEX and MATCH commands
and example of the formula is here
=INDEX($E$1:$P$29,3,MATCH($B$2,$E$1:$P$1,0))
this works like so.
('is all the cells your pulling data from'
,'The row Number your pulling the data from (so 3-29 Depending on what cell the formula is in)'
,MATCH('the input cell (were JUL Is input))'
,'All the column Headers to search (so all the months)
,'Match type ,0 for exact match))
I hope this makes sense to you.
i have added a zipped complete copy that i have made up for you.
sadly i have no clue how you would go around inputting a Quarterly calculation using this method. the only thing i can think is to have 3 separate cells with the months you want added up and then make a very long formula that searched each one and adds them. ill keep working to find a solution :)
Regards
chris