pivot table trouble (tricky)
I'm making a pivot table to study information about disease. Each disease has several characteristics like organism that causes it, symptoms, body part it effects, ways to detect it, ways to treat it, etc. Sometimes one category will have multiple entries in it -- for example: H. influenzae causes flu, there might be one treatment, one body part if effects, but 2 ways to detect and 5 different symptoms. I want to be able to arrange and sort my pivot table bases on any single property like the symptom of coughing, but the only way I know to do this is to repeat the entry many many times, each time changing only one variable like: flu | vaccine | cough flu | vaccine | fever flu | Pill X | cough flu | Pill X | fever This way I can sort for any quality independently but I have to have multiple entries. What I'd like is to be able to only enter one line, like: flu | vaccine, Pill X | cough, fever but still be able to sort by things that are treated with pill X. Does anyone know how to do this?
gyeamans
excel.misc
78881 articles. 
5 followers.
1 Replies
437 Views
Use a relational database such as MS Access and SQL queries. P "Gabe" <gyeamans@yahoo.com> wrote in message news:018001c37175$72d7e650$a301280a@phx.gbl... > I'm making a pivot table to study information about > disease. Each disease has several characteristics like > organism that causes it, symptoms, body part it effects, > ways to detect it, ways to treat it, etc. Sometimes one > category will have multiple entries in it -- for example: > H. influenzae causes flu, there might be one treatment, > one body part if effects, but 2 ways to detect and 5 > different symptoms. I want to be able to arrange and sort > my pivot table bases on any single property like the > symptom of coughing, but the only way I know to do this is > to repeat the entry many many times, each time changing > only one variable like: > > flu | vaccine | cough > flu | vaccine | fever > flu | Pill X | cough > flu | Pill X | fever > > This way I can sort for any quality independently but I > have to have multiple entries. What I'd like is to be > able to only enter one line, like: > > flu | vaccine, Pill X | cough, fever > > but still be able to sort by things that are treated with > pill X. Does anyone know how to do this?
laughing
Similar Artilces:
Pivot table question: Sorting the subgroup in a pivot table (when using the top 10 option for the subgroup)
Hello: I have created a pivot table which has a one subgroup in the second column from the left. It uses the Top 10 option, so there are 10 subgroups So my data looks something like this: Group 1 50 Subgroup 1a 10 Subgroup 1b 40 Eight more subgroups Group 2 30 Subgroup 2a 20 Subgroup 2b 10 Eight more subgroups What I would like to do is sort by THe totals for Group 1, but then also sort by the subgroups, based on the values for each subgroup (similar to the "Sort by, ...
Copying All Records from a Query to a Table
Hi All, Here is another one! I have a code which copies one record from a query(TODAY CHARGES) to another table(RESPEL TEST ALL CHARGES). I want the code to copy all the records in the current query to the table. Here is the code I have: Private Sub Toggle1_Click() Dim PoseidonHotelProgram As Database Dim rsta As DAO.Recordset Dim frm As Form Dim Msg, Style, Title, Response, MyString DoCmd. Set db1 = CurrentDb() Set Myset = db1.OpenRecordset("TODAY CHARGES") Set Myset2 = db1.OpenRecordset("RESPEL TEST ALL CHARGE") Myset2.AddNew Myset2![Date] = Myset![Dat...
pivot chart gridlines
I need to make major gridlines between the outer of my two X axis groupings in a pivotchart. In other words, I have 2 levels of rows in my table, which translate into an "upper" and "lower" axis label for the chart. I want to have gridlines that show the divisions between the lower axis label (the ones that occur less frequently). Any way to do this? I am using '07. When I've tried to select the axis, it is treated as one general one, not as two sets of grouping. So my problem is how to isolate the one of the groupings and indicate I want to have gridli...
Comparing data in tables
I have two Tables, A and B. Both have the same structures, keys etc. Both are downloads from another Table at different times. I would like to be able to compare the data in these two tables to highlight the different records. Can anyone advise how to do this, please. Gerry I would use conditional formatting with a formula like =COUNTIF($H$1:$H$1000,A1)>0 for the first and =COUNTIF($A$1:$A$1000,H1)>0 in the other and set a cell colour (pattern) -- HTH RP (remove nothere from the email address if mailing direct) "Gerrym" <anonymous@discussions.microsoft....
join multiple pivot tables?
How can I link up multiple pivottables all based on the same data where all i would have to do is change the page option (drop down menu) from the first pivot and the rest of the pivots would automatically adjust to match those? There's a sample file here in which you can change the page field in one pivot table, and all the related pivot tables change: http://www.contextures.com/excelfiles.html Under Pivot Tables, look for 'PT0021 - Change All Page Fields' or 'PT0008 - Change Multiple Page Fields' clueless_in_mempjhis wrote: > How can I link up mu...
Loop through Table while Renaming Files
Adapted from: http://www.fmsinc.com/FRee/NewTips/Access/accesstip31.asp I need to loop through the a table to make the following changes: If [Photo1] Like "Photo*" Then fOK = RenameFileOrDir([Path1] & [Photo1], _ [Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _ [Path1] & [TAXPINNO] & "_" & [Round] & "_P1.jpg", _ [Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P1.jpg")) End If ...
Help with Pivot Table subtotals?
My Pivot Table looks like COOLANT COOLANT 50/50 GALLONS COOLANT 50/50 Total COOLANT 60/40 GALLONS COOLANT 60/40 Total COOLANT Total I'd lke to get rid of the "Coolant 50/50" and "Coolant 60/40" subtotal lines. Double-clicking "Coolant 50/50" also makes "Gallons" go away. Is there a setting or property that will do this? Ed Apologies - I tried pasting in an actual table from Word and it didn't take too well! But - I did find the answer: Field Settings, select None under Subtotals. Ed ...
Empty Dataset Tables Don't Save to XML
How do you get around the problem of empty tables in a dataset not being included when you do a WriteXML? When you later read the data back into a new dataset, those tables are gone, because they never got saved to the XML file. Only tables containing one or more records get saved to the XML file. Thanks. I would get around this problem by programatically inserting a fake record into the tables, then saving the xml, and then just don't save the fake data. "Phil Galey" <pagaley@starcalif.com.nospam> wrote in message news:%23ck$0qqcFHA.720@TK2MSFTNGP15.phx.gbl... >...
How to permanently change automatic color assignments in a pivot .
I am displaying several measurments over time for on pivot chart for one test sample at a time. I changed the line color so to convey results more "intuitively" But found that once I select a different test sample to display, all color assignemnt return to default. How do I change the default line color assignment for series? Found one trick is Tool < Option < Color, then click on each color block for chart lines to modify to color sequence. Still unsure of how to change the "default" marker shape and color "consuelof" wrote: > I am displaying se...
Pivot Table Counting
I am having trouble trying to calculate fields through a pivot table. Basically, I have a data set that looks like this: Record Number State Value 1 CA 0 2 CA 12 3 CA 15 4 NJ 0 5 NJ 10 I want to set up a pivot table that will show 2 columns, one with th total number of records and one with the total number of records wit non-zero values. Both of the...
Pivot Tables #4
Hello you experts out there. I nned some Pivot table advice ( a website address for basics would be great), for instance how to excplain pivots and how to create them. I use lots of products with lots of sizes from various suppliers to name but a few columns. Is there a limit as to the amount of columns of data? Thank you very much. H Hi check out Debra Dalgleish's site - http://www.contextures.com/tiptech.html - she has a link to Jon Peltier's site which explains PT basics and then lots more interesting info on them. -- Cheers JulieD check out www.hcts.net.au/tipsandtric...
Pivot Tables
Hi, I was windering if some one could provide me with a link to a tutorial for what I am trying to do. I have an aggregate table. This table has several groups and several columns. The intersection of a group and column is an aggreate value such as Sum function of a column value in that group. Normally a Pivot table shows 1 aggregate value at the intersection of a group and a column. What I want is: To show more than one value at the intersection besides the aggregate value. In other words, if a simple pivot table cell is: Aggr(x,y) where aggr is an aggregate function such a...
formula in pivot excel 2007
It used to be possible to enter a formula within a pivot table so it would update if the pivot table was changed. E.g. one column divided by another and the formula would be avaiable even for extra lines if another variable was used. Is this still avaiable in new excel, and if so, where? In EXCEL 2007 take the following action:- PivotTable Tools Options Tools group Formulas Calculated Field The Insert Calculated Field pane should launch. In here you are able to set up to divide one column by another. If my comments have helped please hit Yes. Thanks....
Filter Table based on Cell Value
I have an Excel (2007) worksheet that is linked to an MS Access (2003) table. When I refresh the data, the entire table comes over. I would like to filter this data based on the value of a cell in a different worksheet (same workbook) so that it only brings in the data for the specified value. I would also like to specify which fields I want to bring in (like I said, the whole table comes over). Example: My Access table has daily transaction records and a "transaction date" field. I would like to only bring in records that have a "transaction date" that is...
Linked Table
Good Morning, Does anyone know which format I have to use in excel so when I link it to access it will be converted to memo (Allow Zero Lenght = No)? I have a workbook with several data columns and one of them contens big text which I need to send to access on a daily basis. What is happenning is when I link it to access I am losing party of the text. Thank you very much. Vanessa Simmonds Hi Vanessa, The default registry setting for the "TypeGuessRows" key is 8. Therefore, if your first (8) rows of data contain less than 255 characters, the data type will be Text, thus t...
Pivot Table #5
Dear Reader, I got a block of data in Excel - sheet 1 (26 columns by 806 rows). I try to create a pivot table on sheet 2 BUT whenever I try to add more objects to the layout I receive the following error: "MS Excel cannot make this change because there are too many row or column items drag at least one row or column item off the pivot table, or to the page position..." What can I do to display all the data? Please help, this is rather urgent! Thanks From a post by Tom Ogilvy: http://support.microsoft.com/?kbid=157486 XL97: Limits of PivotTables in Microsoft Excel 97 (Q15748...
Table question
I have WinXP. I created a Table in WordPerfect (6.1) and then opened it up in Word (2003) so I could send it to some people who only have Word. In both instances (WP and Word), there is a blank page at the end that I don't know how to get rid of. Doing Delete on the last table page, or Backspace on the blank page, doesn't work because the cursor is inside the table at the end. Any suggestions would be appreciated. Thanks! "Joy" <joymp2@nospambellsouth.net> wrote in message news:#udUI1CfKHA.2188@TK2MSFTNGP04.phx.gbl... > I have WinXP. I...
two pivot q's
Hi, I've been so lucky as to get new excel and am stuggling with two things I can't do anymore. 1. Remove getpivotdata - if I click in cell B5 I want the formula to use B5 and not lock to a particular cell. 2. Base second, third, etc pivot tables on a first. There's a box for this now, but I've no idea how to use it anymore. I'd really appreciate it if anyone knows how to do these things. Thanks, Light. Debra Dalgleish shows how to remove that annoying GetPivotData feature here: http://www.contextures.com/xlPivot06.html - Jon ------- Jon Pelti...
Conditional Formatting
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01CAB561.6E59E240 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit I have [form1] linked to [table1] which has field [address] I would like to conditional format field [name] (on [form1]) if [form1]![address] matches any [address] field in [table1] I tried using Dlookup in the condition field but couldn't get it work. TIA, J ------=_NextPart_000_0029_01CAB561.6E59E240 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding...
Pivot table from multiple sheets
I have data in multiple sheets. I am trying to create a 3D pivot chart. I have the chart in 3D form with data from one column of the sheet and I need to grab this same column (Column H) from multiple sheets and graph the data in a Count of column, data, sheet name. If it sounds confusing, it's really not. X=Sheet name Y=Count of Version Z=Version Any ideas??? ...
Pivot table labels
I have a pivot table with the months for label headings. Instead of Jan-Feb- Mar, its Jan-Mar-Feb. How can I change the order? You can drag the columns into the correct order, or -- Select the heading cells Choose Data>Sort Click the Options button From the dropdown, select 'Jan,Feb,Mar' Click OK, click OK glenlee wrote: > I have a pivot table with the months for label headings. Instead of > Jan-Feb- Mar, its Jan-Mar-Feb. How can I change the order? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...
Excel Pivot Table
Hi all, I have a pivot table where I created a calculated field that averages three of the fields in the pivot table. My problem is that it averages the "blank" (which are created from blank fields in the source data) fields instead of ignoring them. How can I average the fields in a Pivot Table and not include the blanks in the average? Use =Sum(F1,F2,F3..)/Count(F1,F2,F3...) Blank cells will add nothing to the Sum total, and Count will only count the cells with numbers; hence you can obtain the average for the numbered cells. Paul "Jeff" <Jeff @discussions....
Subform link to Master table
Help - I have two tables. One is called tblMaster and contains information about projects that are project specific such as Project ID, Manager, Vehicle Code, Customer, etc. The other table is called tblProjects and has all the data for each project. The only field that is different with each input is the field called OpenIssues. What I am trying to do is create a sub form view where the end user can select the project number and it will populate all the data in the form from the tblMaster. Then on the form the end user can update the project status. I have created the form but have ...
wrap text in a chart data table #2
Hi, Im have created a graph, which ive had no problem, but i when i click the option to "show data table" i cannot get the titles to wrap to the box, i have searched but i cannot find an option to wrap the text as i need it for a presentation. I do not want to just simply alter the font size if possible. Is there anyway of doing this? ...
create pivot table from multiple sheet (excell 2003)
as understood, excell 2003 have row limit...but, currently i have data that more than the row limit... so, i split my data to 2 separate worksheet...the column name for each worksheet same.. my question, how can i create one pivot table from the two worksheet...i've tried using the wizard n consolidated data but failed.... thank you vm.. Maybe you could try creating the pivot directly from the Access source table (which doesn't have the 65k row limitations) In Excel, in a new sheet, Click Data > Import External data > Import data Navigate to where the Access file is >...
Hello: I have created a pivot table which has a one subgroup in the second column from the left. It uses the Top 10 option, so there are 10 subgroups So my data looks something like this: Group 1 50 Subgroup 1a 10 Subgroup 1b 40 Eight more subgroups Group 2 30 Subgroup 2a 20 Subgroup 2b 10 Eight more subgroups What I would like to do is sort by THe totals for Group 1, but then also sort by the subgroups, based on the values for each subgroup (similar to the "Sort by, ...
Copying All Records from a Query to a Table
Hi All, Here is another one! I have a code which copies one record from a query(TODAY CHARGES) to another table(RESPEL TEST ALL CHARGES). I want the code to copy all the records in the current query to the table. Here is the code I have: Private Sub Toggle1_Click() Dim PoseidonHotelProgram As Database Dim rsta As DAO.Recordset Dim frm As Form Dim Msg, Style, Title, Response, MyString DoCmd. Set db1 = CurrentDb() Set Myset = db1.OpenRecordset("TODAY CHARGES") Set Myset2 = db1.OpenRecordset("RESPEL TEST ALL CHARGE") Myset2.AddNew Myset2![Date] = Myset![Dat...
pivot chart gridlines
I need to make major gridlines between the outer of my two X axis groupings in a pivotchart. In other words, I have 2 levels of rows in my table, which translate into an "upper" and "lower" axis label for the chart. I want to have gridlines that show the divisions between the lower axis label (the ones that occur less frequently). Any way to do this? I am using '07. When I've tried to select the axis, it is treated as one general one, not as two sets of grouping. So my problem is how to isolate the one of the groupings and indicate I want to have gridli...
Comparing data in tables
I have two Tables, A and B. Both have the same structures, keys etc. Both are downloads from another Table at different times. I would like to be able to compare the data in these two tables to highlight the different records. Can anyone advise how to do this, please. Gerry I would use conditional formatting with a formula like =COUNTIF($H$1:$H$1000,A1)>0 for the first and =COUNTIF($A$1:$A$1000,H1)>0 in the other and set a cell colour (pattern) -- HTH RP (remove nothere from the email address if mailing direct) "Gerrym" <anonymous@discussions.microsoft....
join multiple pivot tables?
How can I link up multiple pivottables all based on the same data where all i would have to do is change the page option (drop down menu) from the first pivot and the rest of the pivots would automatically adjust to match those? There's a sample file here in which you can change the page field in one pivot table, and all the related pivot tables change: http://www.contextures.com/excelfiles.html Under Pivot Tables, look for 'PT0021 - Change All Page Fields' or 'PT0008 - Change Multiple Page Fields' clueless_in_mempjhis wrote: > How can I link up mu...
Loop through Table while Renaming Files
Adapted from: http://www.fmsinc.com/FRee/NewTips/Access/accesstip31.asp I need to loop through the a table to make the following changes: If [Photo1] Like "Photo*" Then fOK = RenameFileOrDir([Path1] & [Photo1], _ [Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _ [Path1] & [TAXPINNO] & "_" & [Round] & "_P1.jpg", _ [Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P1.jpg")) End If ...
Help with Pivot Table subtotals?
My Pivot Table looks like COOLANT COOLANT 50/50 GALLONS COOLANT 50/50 Total COOLANT 60/40 GALLONS COOLANT 60/40 Total COOLANT Total I'd lke to get rid of the "Coolant 50/50" and "Coolant 60/40" subtotal lines. Double-clicking "Coolant 50/50" also makes "Gallons" go away. Is there a setting or property that will do this? Ed Apologies - I tried pasting in an actual table from Word and it didn't take too well! But - I did find the answer: Field Settings, select None under Subtotals. Ed ...
Empty Dataset Tables Don't Save to XML
How do you get around the problem of empty tables in a dataset not being included when you do a WriteXML? When you later read the data back into a new dataset, those tables are gone, because they never got saved to the XML file. Only tables containing one or more records get saved to the XML file. Thanks. I would get around this problem by programatically inserting a fake record into the tables, then saving the xml, and then just don't save the fake data. "Phil Galey" <pagaley@starcalif.com.nospam> wrote in message news:%23ck$0qqcFHA.720@TK2MSFTNGP15.phx.gbl... >...
How to permanently change automatic color assignments in a pivot .
I am displaying several measurments over time for on pivot chart for one test sample at a time. I changed the line color so to convey results more "intuitively" But found that once I select a different test sample to display, all color assignemnt return to default. How do I change the default line color assignment for series? Found one trick is Tool < Option < Color, then click on each color block for chart lines to modify to color sequence. Still unsure of how to change the "default" marker shape and color "consuelof" wrote: > I am displaying se...
Pivot Table Counting
I am having trouble trying to calculate fields through a pivot table. Basically, I have a data set that looks like this: Record Number State Value 1 CA 0 2 CA 12 3 CA 15 4 NJ 0 5 NJ 10 I want to set up a pivot table that will show 2 columns, one with th total number of records and one with the total number of records wit non-zero values. Both of the...
Pivot Tables #4
Hello you experts out there. I nned some Pivot table advice ( a website address for basics would be great), for instance how to excplain pivots and how to create them. I use lots of products with lots of sizes from various suppliers to name but a few columns. Is there a limit as to the amount of columns of data? Thank you very much. H Hi check out Debra Dalgleish's site - http://www.contextures.com/tiptech.html - she has a link to Jon Peltier's site which explains PT basics and then lots more interesting info on them. -- Cheers JulieD check out www.hcts.net.au/tipsandtric...
Pivot Tables
Hi, I was windering if some one could provide me with a link to a tutorial for what I am trying to do. I have an aggregate table. This table has several groups and several columns. The intersection of a group and column is an aggreate value such as Sum function of a column value in that group. Normally a Pivot table shows 1 aggregate value at the intersection of a group and a column. What I want is: To show more than one value at the intersection besides the aggregate value. In other words, if a simple pivot table cell is: Aggr(x,y) where aggr is an aggregate function such a...
formula in pivot excel 2007
It used to be possible to enter a formula within a pivot table so it would update if the pivot table was changed. E.g. one column divided by another and the formula would be avaiable even for extra lines if another variable was used. Is this still avaiable in new excel, and if so, where? In EXCEL 2007 take the following action:- PivotTable Tools Options Tools group Formulas Calculated Field The Insert Calculated Field pane should launch. In here you are able to set up to divide one column by another. If my comments have helped please hit Yes. Thanks....
Filter Table based on Cell Value
I have an Excel (2007) worksheet that is linked to an MS Access (2003) table. When I refresh the data, the entire table comes over. I would like to filter this data based on the value of a cell in a different worksheet (same workbook) so that it only brings in the data for the specified value. I would also like to specify which fields I want to bring in (like I said, the whole table comes over). Example: My Access table has daily transaction records and a "transaction date" field. I would like to only bring in records that have a "transaction date" that is...
Linked Table
Good Morning, Does anyone know which format I have to use in excel so when I link it to access it will be converted to memo (Allow Zero Lenght = No)? I have a workbook with several data columns and one of them contens big text which I need to send to access on a daily basis. What is happenning is when I link it to access I am losing party of the text. Thank you very much. Vanessa Simmonds Hi Vanessa, The default registry setting for the "TypeGuessRows" key is 8. Therefore, if your first (8) rows of data contain less than 255 characters, the data type will be Text, thus t...
Pivot Table #5
Dear Reader, I got a block of data in Excel - sheet 1 (26 columns by 806 rows). I try to create a pivot table on sheet 2 BUT whenever I try to add more objects to the layout I receive the following error: "MS Excel cannot make this change because there are too many row or column items drag at least one row or column item off the pivot table, or to the page position..." What can I do to display all the data? Please help, this is rather urgent! Thanks From a post by Tom Ogilvy: http://support.microsoft.com/?kbid=157486 XL97: Limits of PivotTables in Microsoft Excel 97 (Q15748...
Table question
I have WinXP. I created a Table in WordPerfect (6.1) and then opened it up in Word (2003) so I could send it to some people who only have Word. In both instances (WP and Word), there is a blank page at the end that I don't know how to get rid of. Doing Delete on the last table page, or Backspace on the blank page, doesn't work because the cursor is inside the table at the end. Any suggestions would be appreciated. Thanks! "Joy" <joymp2@nospambellsouth.net> wrote in message news:#udUI1CfKHA.2188@TK2MSFTNGP04.phx.gbl... > I have WinXP. I...
two pivot q's
Hi, I've been so lucky as to get new excel and am stuggling with two things I can't do anymore. 1. Remove getpivotdata - if I click in cell B5 I want the formula to use B5 and not lock to a particular cell. 2. Base second, third, etc pivot tables on a first. There's a box for this now, but I've no idea how to use it anymore. I'd really appreciate it if anyone knows how to do these things. Thanks, Light. Debra Dalgleish shows how to remove that annoying GetPivotData feature here: http://www.contextures.com/xlPivot06.html - Jon ------- Jon Pelti...
Conditional Formatting
This is a multi-part message in MIME format. ------=_NextPart_000_0029_01CAB561.6E59E240 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit I have [form1] linked to [table1] which has field [address] I would like to conditional format field [name] (on [form1]) if [form1]![address] matches any [address] field in [table1] I tried using Dlookup in the condition field but couldn't get it work. TIA, J ------=_NextPart_000_0029_01CAB561.6E59E240 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding...
Pivot table from multiple sheets
I have data in multiple sheets. I am trying to create a 3D pivot chart. I have the chart in 3D form with data from one column of the sheet and I need to grab this same column (Column H) from multiple sheets and graph the data in a Count of column, data, sheet name. If it sounds confusing, it's really not. X=Sheet name Y=Count of Version Z=Version Any ideas??? ...
Pivot table labels
I have a pivot table with the months for label headings. Instead of Jan-Feb- Mar, its Jan-Mar-Feb. How can I change the order? You can drag the columns into the correct order, or -- Select the heading cells Choose Data>Sort Click the Options button From the dropdown, select 'Jan,Feb,Mar' Click OK, click OK glenlee wrote: > I have a pivot table with the months for label headings. Instead of > Jan-Feb- Mar, its Jan-Mar-Feb. How can I change the order? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...
Excel Pivot Table
Hi all, I have a pivot table where I created a calculated field that averages three of the fields in the pivot table. My problem is that it averages the "blank" (which are created from blank fields in the source data) fields instead of ignoring them. How can I average the fields in a Pivot Table and not include the blanks in the average? Use =Sum(F1,F2,F3..)/Count(F1,F2,F3...) Blank cells will add nothing to the Sum total, and Count will only count the cells with numbers; hence you can obtain the average for the numbered cells. Paul "Jeff" <Jeff @discussions....
Subform link to Master table
Help - I have two tables. One is called tblMaster and contains information about projects that are project specific such as Project ID, Manager, Vehicle Code, Customer, etc. The other table is called tblProjects and has all the data for each project. The only field that is different with each input is the field called OpenIssues. What I am trying to do is create a sub form view where the end user can select the project number and it will populate all the data in the form from the tblMaster. Then on the form the end user can update the project status. I have created the form but have ...
wrap text in a chart data table #2
Hi, Im have created a graph, which ive had no problem, but i when i click the option to "show data table" i cannot get the titles to wrap to the box, i have searched but i cannot find an option to wrap the text as i need it for a presentation. I do not want to just simply alter the font size if possible. Is there anyway of doing this? ...
create pivot table from multiple sheet (excell 2003)
as understood, excell 2003 have row limit...but, currently i have data that more than the row limit... so, i split my data to 2 separate worksheet...the column name for each worksheet same.. my question, how can i create one pivot table from the two worksheet...i've tried using the wizard n consolidated data but failed.... thank you vm.. Maybe you could try creating the pivot directly from the Access source table (which doesn't have the 65k row limitations) In Excel, in a new sheet, Click Data > Import External data > Import data Navigate to where the Access file is >...