LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 260
Default Formula Syntax

Dim IndxNumber as Long
IndxNumber = ComboBox1.ListIndex + 1

With Worksheets("Reps Current").PivotTables("PivotTable1")
..CalculatedFields.Add "testfieldname", "= (Total28- 'On
Time28')/ Total28"
..PivotFields("testfieldname").Orientation = xlDataField
..PivotFields("Sum of testfieldname").Caption = "Percent
Overdue"
..PivotFields("Percent Overdue").NumberFormat = "0.00%"
End With

Above is a code that creates a calculated field and
inserts it into PivotTable1. The .calculatedfields line
is the formula of the calculatedfield. This code works
fine but instead of specifiying the eaxct pivotfields that
make up the calculatedfield formula, I need for it to
reference the variable IndxNumber. In this example
variable IndxNumber is currently 31. So in this case
instead of the formula being: "= (Total28- 'On Time28')/
Total28"
it needs to say "= (Total31- 'On Time31')/ Total31"

I have tried "= (Total" & IndxNumber & "- 'On Time" &
IndxNumber & "')/ Total" & IndxNumber
but I keep getting an error.

How do I write this formula so it functions like I need?


Thank you
Todd Huttenstine
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula syntax Dale G[_2_] Excel Discussion (Misc queries) 9 January 24th 10 06:08 PM
Need help with formula syntax DStrong Excel Discussion (Misc queries) 4 May 28th 09 03:21 AM
formula syntax. Babs Excel Discussion (Misc queries) 3 February 23rd 08 10:56 AM
Formula syntax {;;;} Simplefi Excel Worksheet Functions 2 June 20th 05 05:48 PM
Formula Syntax Alexey E. Kolmyk Excel Programming 1 October 7th 03 12:41 PM


All times are GMT +1. The time now is 08:36 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"