Solved: Variable Data with Date

[tlchan]

Hi there,

I have to prepare monthly sales performance review report showing monhtly data from Jan to Dec across the row for eah product. However I need to recalculate manually the proportionate budget sales from yearly target for specific month in order show the level of achievement in last column for the reporting month.

Format of the report as below:


Performance Report for July 2008
***************************
Product Jan Feb Mar Apr May Jun Jul Aug Sep Cul Prop Yrly
total target target
Produt A 45 63 75 85 90 120 T Y XX


The level of achievement in last column = Y/T X 100

Is there any function or VBA to automate the value Y correspond with month (in red) based on value XX for the corresponding month.
:bug:

Thank you

[mdmackillop]

I'm not totally clear on your data postions etc, but the principle should be clear. All data in yellow cells is entered as 1/month/year. The presentation uses Custom Formatting.

[Bob Phillips]

=SUM(B8:INDEX(B8:J8,MONTH(TODAY())))/Y*100

[tlchan]

Just to clarify what I'm looking for is to calculate the proportionate target for N5 and N6 for the reporting period whenever the reporting period changes under F2.


Attached the sample workbook for clarification.

[Bob Phillips]

=$O5*MATCH(DATEVALUE("01-"&$F$2&"-"&$G$2),$B$4:$L$4,0)/12

[mdmackillop]

You can use the yellow cells as the data validation list.

[tlchan]

Thanks for all Excel genius to solve my problem promptly. Without your assistance I have to spend lot time in manual calculate every month.


Thank you

[mdmackillop]

Hi Tichan
For the benefit of others, can you please post your final solution?
Regards
MD

[tlchan]

Thanks. Your function works fine for me but function provide by XLD seem show show "0".

[Bob Phillips]

Mine won't work for 2 reasons. You stuck in in O9 and changed teh O5 to O9, hence a circular reference, and you changed the headings from true dates to month names, I had catered for your true dates.

[mdmackillop]

My bad,
I changed his text to true dates for my first solution, and back to text 'cos the search cell was changed.

[Bob Phillips]

Yeah I know you did MD, but they should have looked at your solution, and saw what it did. Then trying to apply my solution to your workbook, doesn't work, so where do you look first? The last thing changed of course. It needs a bit of self-help here, it is pointless just getting a response and not even trying to understand it.