Formula's of YTD and MTD upon satisfying certain conditions

[Ganesh Vankadaru]

Hi,

I need a formula to compute the MTD, QTD and YTD figures.

In the excel file I have attached, there are 2 sheets.

One is Matrix and the other is Input sheet. Input sheet contains the item wise, location wise, date wise production details.

Now in the Matrix sheet I want to plot the production details with Item description on the columns and the location on the rows. Further, the user will have to select the month for which he needs the production details. Till here I was able to do it.

But what I need is "I want to attach one more field "Balance Type". So if I select March month and select balance type as Year To Date then the production matrix should give the production details from Jan to March. Is there a way to do it with out VBA?

If needed any new columns or pivots for convenience can be inserted. Fine tuning the data to ease the formula is also ok.

Thanks in Advance. This would save me a lot of time.

Regards
Ganesh Sai Kumar V

 

[NARAYANK991]

Hi ,

What is the limit on the number of items and number of locations ?

Narayan

 

[Ganesh Vankadaru]

Hi ,

What is the limit on the number of items and number of locations ?

Narayan

Thanks for looking into it.

The no.of locations would be 5-6 but the no.of items would be in 100's.

 

[NARAYANK991]

Hi ,

The requirement is very complex , and I doubt that formulae will be able to do the job.

Even with VBA , it is not going to be a straightforward application , and will take some time to develop.

Narayan

 

[bosco_yip]

Try,

Check as per attached file.

Regards
Bosco

 

[Ganesh Vankadaru]

Try,

Check as per attached file.

Regards
Bosco

Thank you so much. It worked. It's master class.