Auto populate monthly and quarterly data

E

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
+-------------------------------------------------------------------
 
S

Spencer101

omen666blue;1603322 said:
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

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
E

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

+-------------------------------------------------------------------
+-------------------------------------------------------------------
 
S

Spencer101

Excel said:
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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top