asked on

format as currency

Experts,

I have a pivot table with a text box referring to a cell outside of the pivot table with the following:
="Available Now " & GETPIVOTDATA("Available",$A$1)

I need to format it as currency and no decimals.

I thought I could do something like this:
="Available Now " & format(GETPIVOTDATA("Available",$A$1),"Currency") but it returns an error.

any ideas? thanks...

zorvek (Kevin Jones)

Try:

="Available Now " & Format(GETPIVOTDATA("Available",$A$1),"$#,##0.00")

Kevin

pdvsa

ASKER

pdvsa

ASKER

I need the G1 to be formatted as currency.

zorvek (Kevin Jones)

The GETPIVOTDATA might be a string.

Try:

="Available Now " & Format(CDbl(GETPIVOTDATA("Available",$A$1)),"Currency")

Kevin

zorvek (Kevin Jones)

Correction:

="Available Now " & Format(Value(GETPIVOTDATA("Available",$A$1)),"Currency")

Kevin

pdvsa

ASKER

Kevin: it gave me a #Name. I am thinking it has something to do with the "Available".

What do you think now?

pdvsa

ASKER

both ways did..

zorvek (Kevin Jones)

Are we in VBA or a formula?

If a formula then:

="Available Now " & TEXT(VALUE(GETPIVOTDATA("Available",$A$1)),"Currency")

If VBA then:

X="Available Now " & Format(Value(GETPIVOTDATA("Available",$A$1)),"Currency")

Kevin

pdvsa

ASKER

Kevin: I dont know what is wrong but with the TEXT it is giving a #value.

Maybe you can see what I am referring to. I think that if you see it then it will more clear as to a solution.

thank you

Please see attachement cell G1.
Export-db-FacAvailMnth.xlsx

pdvsa

ASKER

disregard the second pivot chart below the first...

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pdvsa

ASKER

you are good. thank you sir..