Auto populate monthly and quarterly data

[Excel Dumbo]

Hello Dear friends,

Please help me to auto populate monthly and quarterly data data from
table of monthly figures. Please see attached.

Basically just by by updating the current month in B2 cell, data shoul
auto matically populate from the table shown
Say if I type July in B2 cell, it should automatically populate th
values from the table on the right for the month of July

Similarly for the Quarter.


Appreciate your attentio

+-------------------------------------------------------------------
|Filename: Auto Populate by current month.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=448
+-------------------------------------------------------------------

 

[Spencer101]

Hi Excel Dumbo
Basically i have Been able to figure out how to make it look for th
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 wha
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 Quarterl
calculation using this method. the only thing i can think is to have
separate cells with the months you want added up and then make a ver
long formula that searched each one and adds them. ill keep working t
find a solution

Regards
chris

One way to get the quarterly totals would be to use hidden helpe
columns. If that's an option, let me know and i'll mock a version up.


If the example workbook is the exact layout you have then it can easil
be done using hlookup.

S

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Excel Dumbo]

Thanks a lot Omen for the detialed explaintion. Works well and made m
life easy. Hats off to you


Hello Spencer, thank you very much for pointing me to the righ
direction for the Quarterly data . If you could mock up a version fo
me, that would be extremely great.example workbook is in exact layou
and unfortunately I am using Excel 2003 version.

Awaiting your solution to the quarterly problem

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

Thanks a lot Omen for the detialed explaintion. Works well and made m
life easy. Hats off to you


Hello Spencer, thank you very much for pointing me to the righ
direction for the Quarterly data . If you could mock up a version fo
me, that would be extremely great.example workbook is in exact layou
and unfortunately I am using Excel 2003 version.

Awaiting your solution to the quarterly problem.

A couple of questions:

1) Do you have the analysis toolpak addin installed on your machine?
Easy way to tell is format two cells (A1 & B1) in a new workbook a
DD/MM/YYYY dates). In A1 type today's date and in B1 typ
=EOMONTH(A1,0) and press enter. If the date shown in B1 is the last da
of the month, then you have the toolpak installed and that makes lif
easy. If you get an error then you don't have it installed.

2) What happens when the date selected gets to November for example?
Should it just say Nov & Dec or should it be Nov, Dec and Jan of th
following year

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Cimjet]

Hi
Your file is at this link: http://cjoint.com/?BGexpx8kguO
You will find some notes on the sheet
HTH
Cimjet

 

[Cimjet]

Hi Again
This one I installed Data Validation list so you don't need to type, just
select.
Your choice.
http://cjoint.com/?BGex1w7ypfW

HTH
Cimjet