Can't use calculated field for row or column area in Beta 2007

When a new calculated field 'calcfield' is created, a 'SIGMA values' is 
added to the column-labels-area, and a 'sum of 'calcfield' is added to 
the values-area.

But I want to use the new field in the row-labels-area, and it is 
impossible to move the new field to that destination.


When I read books about Pivot tables for Excel 2003, it looks like the 
calculated fields should behave like other fields in this respect.


Do I see  over some checkmark?
0
gertkok (4)
11/12/2006 4:26:35 PM
excel 39879 articles. 2 followers. Follow

5 Replies
295 Views

Similar Articles

[PageSpeed] 29

When you create a calculated field in Excel 2003, it can only be placed 
in the data area.

If there are multiple data fields, you can view them horizontally, or 
vertically. To change the orientation, drag the Data button into the 
column area or row area.

Gert Kok wrote:
> When a new calculated field 'calcfield' is created, a 'SIGMA values' is 
> added to the column-labels-area, and a 'sum of 'calcfield' is added to 
> the values-area.
> 
> But I want to use the new field in the row-labels-area, and it is 
> impossible to move the new field to that destination.
> 
> 
> When I read books about Pivot tables for Excel 2003, it looks like the 
> calculated fields should behave like other fields in this respect.
> 
> 
> Do I see  over some checkmark?


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/12/2006 5:34:09 PM
So when variables are needed to group data based on a calculation based 
on a date (like weeknum, month, workingday, weekend etc) those should
be located as formulas in the original data-table?

What is then a good way to replace the 'hard' data while keeping 
formulas and a references intact?

I tried some solutions but one time the links were broken, another time
I couldn't use import into the table.

Gert Kok

Debra Dalgleish wrote:
> When you create a calculated field in Excel 2003, it can only be placed 
> in the data area.
> 
> If there are multiple data fields, you can view them horizontally, or 
> vertically. To change the orientation, drag the Data button into the 
> column area or row area.
> 
> Gert Kok wrote:
>> When a new calculated field 'calcfield' is created, a 'SIGMA values' 
>> is added to the column-labels-area, and a 'sum of 'calcfield' is added 
>> to the values-area.
>>
>> But I want to use the new field in the row-labels-area, and it is 
>> impossible to move the new field to that destination.
>>
>>
>> When I read books about Pivot tables for Excel 2003, it looks like the 
>> calculated fields should behave like other fields in this respect.
>>
>>
>> Do I see  over some checkmark?
> 
> 

0
gertkok (4)
11/12/2006 8:53:16 PM
So when I want to group data on a calculation  on a date (like weeknum, 
month, workingday, weekend etc) ,  I need variables who should be 
located as formulas in the original data-table?

What is then a good way to replace the 'hard' data while keeping
formulas and a references intact?

I tried some solutions but one time the links were broken, another time
I couldn't use import into the table.

Gert Kok

Debra Dalgleish wrote:
> When you create a calculated field in Excel 2003, it can only be placed 
> in the data area.
> 
> If there are multiple data fields, you can view them horizontally, or 
> vertically. To change the orientation, drag the Data button into the 
> column area or row area.
> 
> Gert Kok wrote:
>> When a new calculated field 'calcfield' is created, a 'SIGMA values' 
>> is added to the column-labels-area, and a 'sum of 'calcfield' is added 
>> to the values-area.
>>
>> But I want to use the new field in the row-labels-area, and it is 
>> impossible to move the new field to that destination.
>>
>>
>> When I read books about Pivot tables for Excel 2003, it looks like the 
>> calculated fields should behave like other fields in this respect.
>>
>>
>> Do I see  over some checkmark?
> 
> 
0
gertkok (4)
11/12/2006 8:59:52 PM
Weeknum, working days and weekends could be calculated in the source 
data. Month could be created by grouping dates in the pivot table.

I don't understand the rest of your question.

Gert Kok wrote:
> So when variables are needed to group data based on a calculation based 
> on a date (like weeknum, month, workingday, weekend etc) those should
> be located as formulas in the original data-table?
> 
> What is then a good way to replace the 'hard' data while keeping 
> formulas and a references intact?
> 
> I tried some solutions but one time the links were broken, another time
> I couldn't use import into the table.
> 
> Gert Kok
> 
> Debra Dalgleish wrote:
> 
>> When you create a calculated field in Excel 2003, it can only be 
>> placed in the data area.
>>
>> If there are multiple data fields, you can view them horizontally, or 
>> vertically. To change the orientation, drag the Data button into the 
>> column area or row area.
>>
>> Gert Kok wrote:
>>
>>> When a new calculated field 'calcfield' is created, a 'SIGMA values' 
>>> is added to the column-labels-area, and a 'sum of 'calcfield' is 
>>> added to the values-area.
>>>
>>> But I want to use the new field in the row-labels-area, and it is 
>>> impossible to move the new field to that destination.
>>>
>>>
>>> When I read books about Pivot tables for Excel 2003, it looks like 
>>> the calculated fields should behave like other fields in this respect.
>>>
>>>
>>> Do I see  over some checkmark?
>>
>>
>>
> 


-- 
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/13/2006 1:30:35 PM
The problem was how to refresh the 'real' data, while keeping the cells 
with formulas synchronised, whether there were more or less new data 
rows in a new dataset. Of course that's why the 'refresh' command was 
invented for tables...

Thank you for the tips

  Debra Dalgleish wrote:
> Weeknum, working days and weekends could be calculated in the source 
> data. Month could be created by grouping dates in the pivot table.
> 
> I don't understand the rest of your question.
> 
> Gert Kok wrote:
>> So when variables are needed to group data based on a calculation 
>> based on a date (like weeknum, month, workingday, weekend etc) those 
>> should
>> be located as formulas in the original data-table?
>>
>> What is then a good way to replace the 'hard' data while keeping 
>> formulas and a references intact?
>>
>> I tried some solutions but one time the links were broken, another time
>> I couldn't use import into the table.
>>
>> Gert Kok
>>
>> Debra Dalgleish wrote:
>>
>>> When you create a calculated field in Excel 2003, it can only be 
>>> placed in the data area.
>>>
>>> If there are multiple data fields, you can view them horizontally, or 
>>> vertically. To change the orientation, drag the Data button into the 
>>> column area or row area.
>>>
>>> Gert Kok wrote:
>>>
>>>> When a new calculated field 'calcfield' is created, a 'SIGMA values' 
>>>> is added to the column-labels-area, and a 'sum of 'calcfield' is 
>>>> added to the values-area.
>>>>
>>>> But I want to use the new field in the row-labels-area, and it is 
>>>> impossible to move the new field to that destination.
>>>>
>>>>
>>>> When I read books about Pivot tables for Excel 2003, it looks like 
>>>> the calculated fields should behave like other fields in this respect.
>>>>
>>>>
>>>> Do I see  over some checkmark?
>>>
>>>
>>>
>>
> 
> 
0
gertkok (4)
11/13/2006 7:15:19 PM
Reply:

Similar Artilces:

PowerPoint 2007 / 2003 "Compare & Merge" crosswalk?
I cannot find the "Compare and Merge Presentations" function on 2007. The tutorial says some of 2003 functions were lost, and you should check with online help. Have not been able to locate anything equivalent or close in 2007. That feature was removed from PPT 2007. It's back (and better) in PPT 2010, though. PPT 2010 is in beta right now. -- Echo [MS PPT MVP] http://www.echosvoice.com "Confused Presentor" <Confused Presentor@discussions.microsoft.com> wrote in message news:0436F244-FE21-449D-9F85-F02788E0BFE0@microsoft.com... > I canno...

Cannot hide column "cannot shift objects off sheet"
When I try to hide a column with the regular procedure (select the column, Format, Column, Hide) I get the message "Cannot shift objects off sheet" and the column doesn't hide. What does this mean and why is it happening? Thanks, Alan -- achidsey Hi Achidsey, See MSKB article # 211769: 'Cannot shift objects off sheet" error message when you hide columns in Excel' http://support.microsoft.com/default.aspx?scid=kb;en-us;211769 --- Regards, Norman "achidsey" <chidsey2@hotmail.com(notmorespam)> wrote in message news:23783FC5-52E4-4326-A1...

Question regarding how to search a column and print a row(s)
Hello, I have a spreadsheet consisting of 15 columns and 100 rows. I would like to search a column for a specific string or based on some other criteria (e.g., > 150) and for columns meeting the desired criteria, I would like to print the entire row (all columns) within the same worksheet or on a new worksheet. Can this be done using one or more formulas but without getting into VB or fancy macros? Can this be done period? I'm looking for something that searches the column using the same or similar approach as the "sumif" function but instead of summing the specif...

Conditional Comment using Cell value as part of it
What I'm trying to do is the following: if cell(a1) is different from 0(zero) then, cell(b1) should have a comment that say, the value of cell(c1) is the payment, next line the value of cell(a1) was refund, next line Total for today = cell(c1)-cell(a1) Example a1 = $10.00 c1 = $30.00 B1 (comment): $30.00 is the payment (c1) $10.00 was refund (a1) Total for today: $20.00 (c1-a1) can anyone help me on that? thanks in advance ...

Why is .NET 2.0 required to deploy an app using VC2005 C++ Setup project?
I just created a setup project for my mfc app. I noticed that in the prerequisites section the .NET 2.0 is checked by default. My app does not need the framework (at least this is what I think). If I clear the checkbox, build the project, and then try to deploy it on a machine with ..NET 1.1 the installer fails with a message complaining about missing .NET 2.0. I'm not sure if this the installer that needs the framework or my app. Are there any mfc8 dlls dependant on the framework? Thanks Hi, > I just created a setup project for my mfc app. I noticed that in the > p...

New to Access 2007
Hi, I am new to Access 2007 and having trouble working with a 2003(?) MDB. I opened it with the Shift Key down and I can see the tables, forms, etc. But when I select a form, I can only open it; the option to Design the form is greyed out and the entire Forms tab in the Ribbon is greyed out too. Help! Thanks much, Glenn "Glenn H" <glenn@pivotal-z.com> wrote in message news:1187024333.492672.70330@q4g2000prc.googlegroups.com... > Hi, I am new to Access 2007 and having trouble working with a 2003(?) > MDB. I opened it with the Shift Key down and I can see the tabl...

Outllok 2007 reminders keep coming back
I have run the /cleanreminders switch but they keep coming back. Any suggestions? John ...

Insert fields dynamically into database from a staging table
Hi all, In our lab when ever a test is performed results are stored in test_results table ( 10 records at maximum) and with click of a button these results should be loaded to database, but I have any issue here, each time the test_result table will have different field names ( one time it will have test1 , test2 , test3 with labid, next time it might have test1, test4, test5 and labid) so how can I load these fields from test_results table into database auomatically. Datebase has all these fields so how can I map the test_results tables fields to database fields to run my dynamic inse...

Calculate As of Date Check Book Opening Balance
Hi Is there any way to calculate as of opening and closing balance of a given check for Payments and Deposits Listing report Module = Banak Management Regards Irfan ...

System Data Protection 2007 on a SBS 2008 Domain
Hi all, This is a lab situation where I have SBS 2008 running on HyperV and doing well... I would like to install Server 2008R2, give it 2 or 4 GBs RAM, 2 CPUs and then install System Data Protection 2007 Has anyone tried this? Is it doable at all? Thanks to all, Dan On Jan 7, 11:52=A0am, "Daniel Jewel" <cyberdud...@yahoo.com.br> wrote: > Hi all, > > This is a lab situation where I have SBS 2008 running on HyperV and doing > well... > > I would like to install Server 2008R2, give it 2 or 4 GBs RAM, 2 CPUs and > then install Sy...

Calculation time
I have a large file, approx 10MB, in excel 2003. Most of the data relates to calculations for around 5,000 records, and the workbook takes too long to recalculate. All the vlookup's have been replaced with Index / Match, but it's still slow, and I have a number of macros that rely upon it recalculating. Based on a post here, I went to this site: http://msdn.microsoft.com/en-us/library/aa730921.aspx and ran the timer macros to try and isolate the problem. The FullCalcTimer takes around 19 secs The ReCalcTimer takes 0.00013 secs, and The SheetTimer takes no more than 0.01...

How to use Find (Ctrl-F) to find non-alpha
Is there some way to search a sheet for anything BUT a-z or A-Z? tx ;-) Finding numerals or a mixture of numbers and letters (and other characters) works ok for me. What are you having trouble with? On 10/22/2010 16:31, Heather Mills wrote: > Is there some way to search a sheet for anything BUT a-z or A-Z? > > tx ;-) -- Dave Peterson On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson <petersod@XSPAMverizon.net> wrote: >Finding numerals or a mixture of numbers and letters (and other characters) >works ok for me. > >What are you having trouble with? > >...

Power Point Viewer 2007 won't open ".pps" files
Using Windows 7, Home Premium, 32 bit. No Office 2007 installed. Using Microsoft Works. Have experienced several issues with Office PPT Viewer 2007. Cleared them, but additionally each time I try to open a .pps file, either from the Desktop or Windows Mail, I receive the following: "C:\Users|AMD\AppData\Local\Microsoft\Windows\TemporaryInternetFiles\content.IE5\E7D22GOW\Gathering of Mustangs and Legends2007.pps is not a valid Win32 application". I have opened this file many times on another computer, so don't think it is an issue with the file. The software has be...

VC++ 2005
I am attempting to compile a project in Visual Studio 2005 which compiles and links properly in VC++ 6.0. It seems to have problems with C++ i/o functions. Here is the error I get: g:\src\analyzer\digitdialpages.h(136) : error C2146: syntax error : missing ';' before identifier 'm_InFile' Line 136: ifstream m_InFile; The statment: include <fstream> is at the top of the source file. Some assistance would be helpful. We didn't have any problems with using ifstream or ofstream in VC++ 6.0. Thanks. On Tue, 12 Dec 2006 18:22:47 -0500, "Raj Kulkarni...

How do I create custom border using peoples names?
There are many it could be done. I'd set up text boxes and go from there. -- JoAnn Paules MVP Microsoft [Publisher] "Val" <Val@discussions.microsoft.com> wrote in message news:BFF16BB3-4C90-4E05-B0AA-303874A445AA@microsoft.com... > In addition to JoAnn's reply, you could use WordArt as well. WordArt allows you the opportunity to rotate, flip, arrange etc. and resize as well as save as an image. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. &...

match dates in two columns get value from third
I have a data set on daily basis and another data set on weekly basis. i want to put the weekly data in daily data set by putting a formula which picks values from col c for relevant dates in daily set. example - let us say this is the data set ColA Col B Col C jan1 jan1 8 jan2 jan7 7 jan3 jan4 jan5 jan6 jan7 I want the data from colC against dates in ColA as in ColD below. it should leave other cells blank (colA and ColB are dates format) ColA Col B Col C ColD jan1 jan1 ...

2007 dst issue
the client i support currently has about 20 exchange 2003 sp1 servers (mixed FE/BE (mostly clusters) PF, BH) i am putting a plan in place to move to sp2 early next year i have since come across article 926666 regarding the post sp2 exchange update for 2007 dst change i have built a test environment to successfully create this issue i have a 2003 sp1 dc and a 2003 sp1 server with exchange 2003 sp2 including the windows dst update but not the exchange update i then created a test mailbox and from outlook setup 5 calendar entries the week of march 26 at various times when viewing in OWA, al...

deleting entire rows with the same cell value in the first column
Hi! I have an huge table in which there are lots of rows that have the same value as the one in the row before: 1 1 1 2 2 2 How can I delete them leaving this way only one row per value without searching for them and deleting them manualy? Any help welcome! Pedro, assuming your data starts in a1, in b1 type =if(a1=a2,"Y","") and copy down for the length of your data. Then filter on Y and delete all those rows. HTH -- Sincerely, Michael Colvin "Pedro F." wrote: > Hi! > I have an huge table in which there are lots of rows that have the same >...

how do i prefix a column with letters
I am deigning a catalogue and want to prefix a column with 4 letters. How can i do this? Hi, You could paste something like this into the code window of the worksheet in question (NOT a normal code module) Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim NoOfCellsToFormat As Integer NoOfCellsToFormat = Target.Value On Error GoTo ws_exit: Application.EnableEvents = False If Target.Column = 1 Then If Target.Row > 4 Then If Target.Row < 11 Then Target.Value = "ABCD" & Target.Formula ...

deleting rows after 3
basically i need some help deleting rows, i want a button click to delete all the rows after 3. the ones before 3 have text that i would like to stay, but the rows after three need to be deleted. there is no fixed end to the list as people add more rows - so the delet function would need to delete ALL rows greater than 3. thanks you could use a macro like: Rows("4:65536").Delete Shift:=xlUp "kishan" wrote: > basically i need some help deleting rows, > i want a button click to delete all the rows after 3. the > ones before 3 have text that i would ...

IFERROR function from XL 2003 to 2007
File was created in XL2003, I had an "IFERROR(x,y)" VBA function referenced in cell formula, see: http://min.us/mvj137H Now in XL2007, I removed the VBA "IFERROR" so as to use the built-in IFERROR. The problem is as you see in the picture, XL2007 is NOT automatically using the built-in. (F9 does not work) I have to manually "touch" the individual cell formula (like put in a space after "=") to have the formula recalculate. Please help , thanks Untested. How about just selecting all the cells Hit ctrl-h (like Edit|Replace in xl2003 menus) What:...

Outlook 2007 Distorts Inserted Pictures -- How do I make it stop?
When inserting pictures in HTML or RTF emails, Outlook 2007 uses the size in INCHES (for some ungodly reason) instead of in PIXELS. How the heck do I revert to the traditional 1:1 pixel mapping so that a 900 x 600 pixel picture shows up as 900 x 600 pixels in the email? I want it to ignore the INCHES and DPI attributes and just display the JPG as a website would : using the X x Y information only. Wow, not a single reply after weeks and weeks. :-( "mjs" <no@thanks.com> wrote in message news:ur9qg2bxIHA.1936@TK2MSFTNGP04.phx.gbl... > When inserting pictures in HTML...

Uninstalled 2007 Trial, now all docs don't open
I used the 2007 Trial and uninstalled it, and now my docs do not open in the 2003 Version that came on my computer from the beginning. Any ideas? I do not have the original CD. Did you save your files as .docx or .doc? If it's the former you need to go to Microsoft's site and download the 2007 compatibility pack for 2003. -- "Don't pick a fight with an old man. If he is too old to fight, he'll just kill you." "JenLy" <JenLy@discussions.microsoft.com> wrote in message news:79ABD79A-9164-4B20-AE07-013AD9DFE4F3@microsoft.com... ...

how to remove non commercial use in the title bar in office
how to remove non commercial use in the title bar in office 2007 Buy an edition that is not for non-commercial use only. You own the Home and Student edition. -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Mario" <Mario@discussions.microsoft.com> wrote in message news:0E14F850-0B65-4093-926F-B680E1C7D6B1@microsoft.com... > how to remove non commercial use in the title bar in office 2007 ...

Error 2203 when trying to upgrade Office 2003 to 2007
Error 2203 keeps appearing. I have added Outlook 2007 with Business Contact Manager about a year ago. Could this cause the error message to show up and what can I do do resolve? "Bosh" <Bosh@discussions.microsoft.com> wrote in message news:3EF38140-358A-42CE-9448-396621A7F927@microsoft.com... > Error 2203 keeps appearing. I have added Outlook 2007 with Business > Contact > Manager about a year ago. Could this cause the error message to show up > and > what can I do do resolve? Try a Google Search on ERROR 2203, and scroll the list for your p...