Excel - pivot table calculated field or item
Asked By craig
28-Jan-10 11:32 PM
Hi, my question is..I want to use an If formula based on the Grp colomn value
BU (see formula below), but it doesnt recognise the value as "BU", it just
returns the false value (value if false).
Pivot table rows are as follows
SO # Grp Cust Whse SO
311450 BQ BAKERS S 10-Nov-Tue
312385 0 MODERN S 12-Nov-Thu
312403 BU BUNNINGS S 12-Nov-Thu
I have inserted a calculated field with formula
=IF(Grp="BU",0,1)
The formula always returns 1
Thanks for your answer
Database
(1)
CraigAdd
(1)
CraigIf
(1)
Whse
(1)
Grp
(1)
Technology4u
(1)
BUNNINGS
(1)
Colomn
(1)
Roger Govier replied to craig
Hi Craig
Add an extra column to your source data headed Test with a formula like
=IF(B2="BU",0,1)
Then expand your source to include the new column.
Drag Test to the area where you want it on the PT - presumably the Data area
--
Regards
Roger Govier
__________ Information from ESET Smart Security, version of virus signature database 4817 (20100129) __________
The message was checked by ESET Smart Security.
http://www.eset.com
craig replied to Roger Govier
Hi Roger, Thanks but I didnt reallly want to have to modify my source data.
Is there a reason the pivot table formula wont recognise the text criteria?
Is this a limitation of pivot tables? The formula is simple enough, I cant
understand why it wont work.
Roger Govier replied to craig
Hi Craig
If you want to send me a sample of your raw data, then I will see if I can
come up with any other solution.
To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.
--
Regards
Roger Govier
__________ Information from ESET Smart Security, version of virus signature database 4822 (20100131) __________
The message was checked by ESET Smart Security.
http://www.eset.com
craig replied to Roger Govier
Thanks Roger but I have used your suggestion (sort of) by using a different
field from my data for the if logic test criteria. In summary instead of
using a text field I am using a numeric value field which works fine. It
appears to me that formulas in pivot tables do not like text values as
criteria in the row fields.
Thanks for your help
run a number of SQL stored procedures in Excel VBA Server};Server = nae-is-sql1;Database = Database;Trusted_Connection = no;UID = UserID;pword = Password" I have created a specific login account for UserID and open the file in your favourite text editor. NickHK Server};Server = nae-is-sql1;Database = Database;Trusted_Connection = no;UID = UserI D;pword = Password" at have be Hi Nick, I tried to Delete Shift: = xlUp Range("A1").Select With ActiveSheet.QueryTables.Add(Connection: = _ Server};Server = Server1;Database = Database;Trusted_Connection = no;UID = User;pword = pass" _ , Destination: = Range("A1")) .CommandText = Array("select * from CCI2 the password attribute name instead of pword. ODBC;Driver = {SQL Server};Server = nae-is-sql1;Database = Database;Trusted_Connection = no;UID = UserID;Pwd = Password - - Hope that helps. Vergel Adriano Hi Vergel, Thanks for
Get results from Access Database. Excel Hi All, I have Excel File & Access Database. Access Database Contains - Employee details like Employee ID, Address, Contact No., Process. Database has more 5000 records. In Excel I have 200 Employee ID - I need Contact No How can i get this results using ADO connection. Earlier we use to export access database to excel and then do VLookup to get the results. Now we are trying to 1) Sheets (1) Excel (1) ADODB (1) DB (1) Employee ID (1) Application (1) Access Database (1) Try this Lastrow = Sheets("Sheet1").Range("A1").End(xlDown).Row vecLookup = Application.Transpose(Sheets Sql & " WHERE EMP_ID IN (" & Join(vecLookup, ", ") & ")" HTH Bob Hi All, I have Excel File & Access Database. Access Database Contains - Employee details like Employee ID, Address, Contact No., Process. Database has more 5000 records
what is a database? Excel what is a database? Excel New Users Discussions Excel 2003 (1) Excel (1) Workbook (1) VBA (1) XP (1) Database (1) ExcelTutorials (1) Realationships (1) http: / / en.wikipedia.org / wiki / Database Contrary to some, Excel is not a database. Tyro fm11 - - A database is a structured set of information. In Excel, it's often a table of information than columns, so we can have bigger tables of information. HTH Excel is not a database nor does Excel maintain databases. Please explain to me the relational concepts that Excel exposes schemas. Excel does not. There must be relationships between the files(s), ie. tables in database terminology, along with schemas. Excel does not have records. Excel has rows. Databases have tables
Database design Excel A little while ago (on one of these NewsGroups) there was a thread about using Excel as a database and database design. I undertook to consolidate my thoughts (which were over several posts) on an information http: / / 1001solutions.co.uk / Designing_a_spreadsheet.pdf Regards. Bill Ridgeway Computer Solutions Excel Discussions Excel (1) Database (1) This is quite good. One suggestion: while it's possible to use Excel as a database, it is not really possible to have multiple concurrent users of the database, and so Excel's ability to serve as a database (as the word is generally understood) is rather limited. Given that caveat, looks good. Dave at her incessantly as she slept, and any number of oddities on-screen. Rob keywords: Database, design description: A little while ago (on one of these NewsGroups) there was a thread