Have a Month Drop down return a number value

[jak82]

Hi,

I have a drop down list defined in a sheet called 'List Box' which lists all the months Jan-Dec.

I am referencing it in the main sheet as part of cell. I am wanting to return a number for the value selected for example 'March' returns a value of 03 when referenced in a function.

This is the code I am currently using as a function

=-GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear", 'P&L'!C$5&"","trans_period",'P&L'!C$4&"","account_code",'P&L'!$C10&"")

So I am wanting 'P&L'!C$5&"" to pull the March value as 03

Many Thanks

 

[Jonmo1]

OK, so 'P&L'!C$5 contains your dropdown that returns Jan Feb Mar etc...
And you want to conver that to 01 02 03 etc..

Do you really need the leading 0 as a TEXT string "03", or would just the actual Number 3 ?

This will get the Number 3
=MONTH(1&'P&L'!C$5)

 

[jak82]

Thanks Jonmo, Thanks for the help,

Does that return the value as a number or string. I have tried entering the formula you mentioned it does not like the '=' at the start al I have tested with December which would be 12.

The heading on the pivot table contains two digits so I presume it must need the double digit returned.

Thanks

 

[Jonmo1]

Does that return the value as a number or string.

This will get the Number 3
=MONTH(1&'P&L'!C$5)

Sorry, I didn't actually put it in your formula, what I posted was just example that would stand alone as it's own formula in a cell.

If it needs to be 2 digit Text string, it would be
=TEXT(1&'P&L'!C$5,"mm")

To impliment that in your original formula, try
=-GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear",TEXT(1&'P&L'!C$5,"mm"),"trans_period",'P&L'!C$4&"","account_code",'P&L'!$C10&"")

 

[jak82]

Sorry, I didn't actually put it in your formula, what I posted was just example that would stand alone as it's own formula in a cell.

If it needs to be 2 digit Text string, it would be
=TEXT(1&'P&L'!C$5,"mm")

To impliment that in your original formula, try
=-GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear",TEXT(1&'P&L'!C$5,"mm"),"trans_period",'P&L'!C$4&"","account_code",'P&L'!$C10&"")

Thanks, unfortunalty it returned #ref. I did try it in a cell on its own and it return the correct value ie August returned 08, seems its when it is in the formulae that it is having an issue.

Thanks

 

[Jonmo1]

I did try it in a cell on its own and it return the correct value ie August returned 08

That would indicate the problem lies elsewhere, not with the month string

Does this work on it's own
=-GETPIVOTDATA("journal_amount",'Pivot Table'!$A$3,"nlyear","08","trans_period",'P&L'!C$4&"","account_code",'P&L'!$C10&"")

 

[jak82]

Nope

 

[Jonmo1]

What are journal_amount nlyear trans_period and account_code ?
Are those Named Ranges, why are there quote marks around them?

Try removing those quote marks.

 

[jak82]

Ok, sorry my mistake I was putting the month into the year, it should be were trans period is, ill swap therm about see if it works.

 

[jak82]

What are journal_amount nlyear trans_period and account_code ?
Are those Named Ranges, why are there quote marks around them?

Try removing those quote marks.

Thanks very much it works!!, why did we put the 1 as the value, what does that specify it as?

Thanks again.