Mtd - qtd - ytd

**qualtay** · 07-14-2011, 06:59 PM

Hi All!

I am putting together a worksheet that pulls from our data warehouse and gives me X data per month (dollars & headcount).

I want to be able to open the file each month, change the fiscal month and have it trigger all the changes. I have already set the file (which is connected to our systems) to update system data, but am stuck on the analysis. I currently am pulling a pivot of data laid out like this

Rows - Organizations where $$s are charged too

Columns - Months

Data - Total dollars by month by organization

I am hoping to establish some automated analysis by which once i change the month to say June, i have columns of MTD / QTD / YTD which will pull the month (June), QTD (Apr - Jun), and YTD (Sept - June). Sorry for the long winded question. Hopefully someone out there has an idea!

Thanks

Matt

**Roger Govier** · 07-14-2011, 07:45 PM

Hi Matt

Welcome to the forum.
Can you upload a sample of your data and PT as it would make it easier to understand.

**qualtay** · 07-14-2011, 08:15 PM

Thanks Roger!

I updated a test case of my data (actual data changed for company confidentiality).

The fiscal calendar is Oct - Sept, so in this case of June, it would be the end of Q3 in our calendar.

**Roger Govier** · 07-15-2011, 06:03 AM

Originally Posted by qualtay

Thanks Roger!
The fiscal calendar is Oct - Sept, so in this case of June, it would be the end of Q3 in our calendar.

Hi
Take a look at the attached file - I think it doe what you want.
I put a small table of months on sheet 2, and then use some Vlookup and Index/Match formulae in R1 and S1 of Sheet1, which are triggered by the month dropdown in cell Q1

I formatted R1 and S1 with a custom format of ;;; so the values don't show.

R1   =VLOOKUP(Q1,Sheet2!A2:C13,2,0)
S1   =INDEX(Sheet2!C2:C13,MATCH(Sheet1!R1,Sheet2!B2:B13,0))

R4   =INDEX($B4:$M4,$R$1)
S4   =SUM(INDEX($B4:$M4,$S$1):INDEX($B4:$M4,$R$1))
T4   =SUM($B4:INDEX($B4:$M4,$R$1))

**qualtay** · 07-15-2011, 11:49 AM

Originally Posted by Roger Govier

Hi
Take a look at the attached file - I think it doe what you want.
I put a small table of months on sheet 2, and then use some Vlookup and Index/Match formulae in R1 and S1 of Sheet1, which are triggered by the month dropdown in cell Q1

I formatted R1 and S1 with a custom format of ;;; so the values don't show.

R1   =VLOOKUP(Q1,Sheet2!A2:C13,2,0)
S1   =INDEX(Sheet2!C2:C13,MATCH(Sheet1!R1,Sheet2!B2:B13,0))

R4   =INDEX($B4:$M4,$R$1)
S4   =SUM(INDEX($B4:$M4,$S$1):INDEX($B4:$M4,$R$1))
T4   =SUM($B4:INDEX($B4:$M4,$R$1))

Hey Roger,

This is a great idea!! Simple to setup to it seems. Look forward to applying this to the real data!!

Much appreciated...!!

Regards

Matt