Pivot Table Selection

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Misc
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - IF forumla to standardise text I'm looking for a formula to do the following,, Look at column A (cells 1-100) which is populated with text, with 3 or 4 variances of the same output, e.g "ceilings" "cielings" "clings" - i'd need the formula to take this range into account and pick up all results with "ings" in it, and report standard result of "Result Ceilings" for each cell in A1-A100 with any text arrangement of "ings" in it Can this be done? Tag: Pivot Table Selection Tag: 823970
  - 2
    - HOW to close a userform using a button To close a userform I wrote Private Sub XX_Click() UserForm.Close End Sub but don't Close the form I get the a error message " invallide qualifier " HOW to close a userform using a button ? Tag: Pivot Table Selection Tag: 823969
  - 3
    - how do I assign a macro to a tool in Excel 2007 I'm switching from Excel 2003 to Excel 2007. How do I create a tool in 2007 and assign the tool to run a macro? Tag: Pivot Table Selection Tag: 823967
  - 4
    - How to change all - fields to + field Hi all How can I change from -to plus in a worksheet The cells have now negative and I want all to be positive, how to do it? eks : A1 A2 A3 A4 -214 683 -384 454 want to change to A1 A2 A3 A4 214 -683 384 -454 -- Just a regular user :) Tag: Pivot Table Selection Tag: 823963
  - 5
    - Problem using attached excel- files from webmail Problem is very annoying, yet troublecausing. User has opened an e-mail wich includes excel- file. It does not matter, if it is .xls, or. xlsx. The file opens fine in IE and the user may do everything needed. But then, if she opens local excel- file from a link, there is as many worksheets named "object" as she opened excel- files from webmail. Now, if she closes the intended file, or the whole excel program BEFORE closing these object - named worksheets, ther occures this ilegal operation and excel will shotdown. Do you want to send an errormessage.... If she opens excel now, and tries to open that same file she just closed, it is locked. Only reboot relieves it. Opening the file when opening excel, and usin this open file- button, won't bring up this problem. Wich would be fine otherwise, but like I said, if opening from that link, there will be those objects. And if she remembers not to use that link for a month, and then uses it, there will be approximetely 500-600 objects...... And I have tried to go around this problem, but the work she does, demands using these links, webmail, etc. If anyone has anykind of solutionsuggestion, would be appreciated. Oh, and the software info is; Win XP Pro FI SP3 Office 2000 Pre Fi Outlook 2003 Fi, outsourced server Thanks already, -Joke- Tag: Pivot Table Selection Tag: 823961
  - 6
    - How to add user defined functions in excel permanantly I made a function in vb and now I want to add that function in excel permanantly. Whenever I start excel it should be loaded automaticaly. Plz help me on this.... Tag: Pivot Table Selection Tag: 823960
  - 7
    - Merge Cells on Protected Sheet I have a protected sheet with unlocked cells that I need users to be able to merge. Is this possible. Tag: Pivot Table Selection Tag: 823957
  - 8
    - How do I convert Multiplan ver 4.2 to Excel format? I have a lot of financial data in the Multiplan format version 4.2. I still works on XP, but I would like to convert the Multiplan files to current Excel version 2007. I have tried to save the MP file in "Excel format". but it doesn't work. Anybody have any answers? Tag: Pivot Table Selection Tag: 823954
  - 9
    - LOOKUP Sir, Can i get another formula SHEET 1 I HAVE EMPL DETAILS A B C D E F 1 EMP NAME EMPID AMT 2 JOHN K-1234 5600 3 KITE K-1235 7000 I WANT IN ANOTHER SHEET TO TAKE VALUES IN C1 TO C100 IF THE EMP ID MATCHES WITH SHEET 1 IT SHOULD TAKE THE VALUES OF "C" OF THAT PARTICULAR EMP REGARDS SRINIVAS -- srinivas Tag: Pivot Table Selection Tag: 823952
  - 10
    - ODBC Microsoft Access Driver Login Failed Hi there I am trying to import external data into Excel from an Access database (a query), but even though I have full admin/user access/permissions I keep getting the same error message when trying to connect to the data source, even after entering the required Login Name and password: ODBC Microsoft Access Driver Login Failed You do not have the necessary permissions to use the '\\xxxx\xxxxx\xxx \ABCD.mdb' object. Have your system administrator or the person who created the object establish the appropriate permissions for you. The database has a security-enabled workgroup attached to it, and I'm wondering if this is part of the problem as I don't have this problem with databases that don't have workgroups attached to them. We have changed the database's permissions to Owner's via Tools > Options > Tables/Queries > Run Permissions (have also adjusted the RunPermissions for the Query to Owner's), but that doesn't seem to help either. Does anyone else have any idea why I can't connect? or suggestions as to how I can get around this problem? It is driving me nuts! Thanks in advance Sue Tag: Pivot Table Selection Tag: 823950
  - 11
    - grouping A B C D E F G 1 22 1 3 5 7 9 2 33 3 22 4 44 5 55 6 66 7 22 2 4 6 8 10 8 22 5 10 15 20 25 I am desprately looking for a formula that will organize data by using a common cell number, then taking the remainder of the data in that row and only looking at those rows for comparing data. In the example above column A w/ "22" openning the door for the data in rows 1, 7 and 8. Thanks very much Mike Tag: Pivot Table Selection Tag: 823949
  - 12
    - finding a value with more than one criteria Hi I need to find a value in column E but it has to meet 2 different criteria the value in column A and column C must match before so if this is wat it looks like A B C D E 1 X TR X 3.1 2 X TR X 3.1 1 X TL X 3.1 I need to find 3.1 only when Column A is 1 and Column C is TR Thanks in advance Tag: Pivot Table Selection Tag: 823935
  - 13
    - Find and Replace Command Using Wildcard Help I can't think of how to explain this question so I'll just use an example: Using the find and replace command. I want to find < 'Worksheet1'!A* > and replace it with < INDIRECT("'Worksheet1!A*") >. In the example the find command is of course using " * " for a wildcard. the problem I'm having is I need whatever that cell number is to remain the same during replacement. For example, when it finds < 'Worksheet1'!A2 > it should replace it with < INDIRECT("'Worksheet1!A2") > and when it finds < 'Worksheet1'!A3 > it should replace it with < INDIRECT("'Worksheet1!A3") >, etc. Is there a symbol/key command that I can use to tell excel to search using the wild card but replace the cell reference with the same cell reference it finds but also add in the INDIRECT command? Tag: Pivot Table Selection Tag: 823927
  - 14
    - Excel I am trying to create a Excel spreadsheet for students ordering lunch items. I need to know the answers to the following: a. Is there a way to run a macro when a Excel workbook is initially opened? b. Is there a way to read a calendar using the current date to identify the cell where the date is located? c. If the answer from b is yes, is there a way to move data from the 4 cells below the cell containing the matching date to 4 cells in a different worksheet? d. Is there a way to scan a series of cells for a specific character, such as, as X? Tag: Pivot Table Selection Tag: 823921
  - 15
    - CONVERT CURRENCY TO TEXT CONVERT CURRENCY TO TEXT Tag: Pivot Table Selection Tag: 823917
  - 16
    - Formatting Numbers to 3 digit, In a column i have numbers 1 to 999 but I would like to have as 001 to 999, I cannot format as text, becouse another equation finds the higest number (=max(D:D)) from this column. Is there any way, to have the 3 digit format keeping them as numbers? i.e. Col D 001 002 003 004 -- -- 999 Tag: Pivot Table Selection Tag: 823905
  - 17
    - Link problem after linking tables to Word Hi, I've encountered linking problems after I've linked tables to a Word document. On our server we have designated drive mappings. I.e. J: \folder1\file.xls After linking to Word, Excel changes the links in the Excel file to the original folder name on the server. I.e. \\Server_name\Finance \folder1\file.xls After this happens the links do not update properly. It even allows you to have 2 links to 1 file. The cells linked to "J:\folder1\file.xls" updates correct other cells linked to "\\Server_name\Finance\folder1\file.xls" do not update. Has somebody experienced the same or has a solution? Thanks. Harold Tag: Pivot Table Selection Tag: 823894
  - 18
    - Calculating an age from 2 dates I am trying to calculate someone's age on a date that is after their birthday. I know how to do the calculation if I am going from a later date to an earlier date but how do I go from an earlier date to a later date? for example, birthdate visit date 1 12/25/72 8/28/2007 Is there a calculation that can show how old the person was on the visit date? Thank you! -- Terri Tag: Pivot Table Selection Tag: 823880
  - 19
    - Conditional formating I run a report for employees that have review dates that are due, I would like the past due dates to change colors so I see them right away, what formula would I use. Tag: Pivot Table Selection Tag: 823878
  - 20
    - Macro Security Warning I've written some extensive macros into a worksheet in Excel 2003, back when I was on XP. Now that I've moved my copy of Excel to my Vista machine, I get "this author has not been authorized..." message. I've se security to medium ( I DO want security on), but when I open the file, the check box where I should be able to "Always trust macros from this source" is grey so I can't check it. I' ve also tried to add myself to the Tools/Options/Security/Macro Security/Trusted Sources but I can't seem to find a way to add myseld as a source. the only option is t "Remove" (Even though there are no trusted sources listed). What's my next step? Tag: Pivot Table Selection Tag: 823877
  - 21
    - How do I link all dependent cells to a "new" independent cell?? I am working with two sheets in excel 2007. One with assumtions (sheet 1) and this is linked to an other (sheet 2) which uses alot of formulas linked to the data in sheet 1. Anyway. I want to create a third sheet which shall be a copy of sheet 2, except that it shall be using other data than what sheet 2 do. Is it a way to get excel to adjust all cells in sheet 3 to pick data from another cell in sheet one?? There is a lot of formulas to change so it is not possible to change every cell in sheet 3 by hand. Thank you Tag: Pivot Table Selection Tag: 823876
  - 22
    - Charts and Hidden Rows Hello, I have a spreadsheet that has several charts and I am using custom views. Whenever I go to print preview under a custom view with the carts, the sizing of the plot area and the legend become skewed. (i.e. the plot area will get so small you can't see it and the legend will get bigger and cover up the scale). Any thoughts? -- Thank You Andrew Edmunds Tag: Pivot Table Selection Tag: 823869
  - 23
    - Backup I am using the settings defined by MS for automatic bacling up of an excel file when I open a file but it looks like the backups are not being made. I cannot find them in the target folder. Tag: Pivot Table Selection Tag: 823861
  - 24
    - highlight duplicate rows I need a way to highlight rows that are dupilcates based on what I have in column A. I have tried doing this through conditional format but I have crashed and burned on that one. So here is simplier example of what I have: Column A 22 22 22 16 16 8 73 73 Basically I would like to have all the 22 entries highlighted and when I move down I don't want the 16's highlighted but when I move to the 8, I wan't that highlighted, and not to highlight the 73's. It would need to highlight every other group of duplicates (or single entry). I need a way to automate this because I have something like 23,000 rows of data and do not want to go through the entire thing one by one. Thanks for your help. Tag: Pivot Table Selection Tag: 823858
  - 25
    - Conditional Formatting-Referencing adjacent cell I want to format my spreadsheet so that when a cell has an "a" in it and is colored red, the cell immediately to its right will also be colored red. Is this possible through conditional formatting or VBA? Thanks. Tag: Pivot Table Selection Tag: 823848
- Next
  - 1
    - Converting "Rolodex" text format for importing I need to transfer a txt listing of customer information from a Rolodex-type format to a spreadsheet. It is a very old system and the only way I could get the data I needed was via this Rolodex format. However, the listing does have a reproducible format. Iâ??d like to move the individual rows into columns and then slice and dice the individual columns to get the data into additional columns for sorting, etc. The Rolodex format I have is as follows: Line 1 -- First Name, MI, Last Name (no commas) Line 2 -- Address line #1 Line 3 -- Address line #2 Line 4 -- City, State, Zip code Line 5 -- Chart #, DOB, Sex, Registration Date Where: Chart# is 7 digits max DOB is formatted MMDDYYYY Sex is single alpha Registration date is MMDDYY Line 6 -- Home Telephone, Work Telephone Where phone numbers are formatted ###_###_#### (3 spaces) ###_###_#### Line 7 -- Blank line Line 8 -- Blank line Then it starts over at Line 1â?¦ for approximately 800 people. The thought of having to cut and paste all of this is killing me. Any help would be appreciated. Thanks. Tag: Pivot Table Selection Tag: 823847
  - 2
    - Finding certain value in a column In Excel 2007, in column A I have some cells that contain employee's names and certain cells that don't. For instance, in A1 I have employee1, in cells A2-A13 I have other stuff and in cell A14 I have employee2. In cell B1 i have the following formula: =isnumber(match(A1,Employees,0)) and I have a list with employee's names in it defined as "Employees". This is what I need to do: In cell C I would like to have only employee names. If the same row in column A does not contain an employee then I would like to return the employee above it. For instance, cell A13 does not contain an employee and the closest employee above it is in cell A1 so in cell C13 I would like to have "employee1". I can't seem to figure out how to do this. I would prefer to do it without a macro if possible. Thanks. Tag: Pivot Table Selection Tag: 823845
  - 3
    - generate column of 360,000 RAND()s without the click and drag Without VBA (if poss.) I want to create a column of 360,000 Rand()s , or more, without the long click and drag down method. Tag: Pivot Table Selection Tag: 823840
  - 4
    - cell settings hello there is there a way that a cell can be set to no more then 2 digits eg, should accept 01,02 and not 001 or 002 many thanks jez Tag: Pivot Table Selection Tag: 823839
  - 5
    - Changing Celll colour as a result of a calculation When performing a calculation I want to change the colour of the cell to Red if negative or Green if positive. Tag: Pivot Table Selection Tag: 823838
  - 6
    - Do macros in Excel increase file size? I have an Excel file with multiple (220+)macros in it. Does this greatly increase the overall size of the file? If so, is there a way to reduce the file size (by manipulating the macros - similar to compressing images)? Tag: Pivot Table Selection Tag: 823837
  - 7
    - counting with 2 different criteria =SUMPRODUCT(('Domestic #''s '!B3:B152="Assigned")*('Domestic #''s '!K3:K152="New")) Am I using the right formula? I want to count the amount of times Assigned and New are both selected. They are on different columns. But the counts I get are wrong when I manually filter. I checked to make sure there are no leading spaces i.e _assigned, etc.... I am using a drop down to select Assigned and New so figure it is not the case. Any other ideas or am I using the wrong formula. Thanks Tag: Pivot Table Selection Tag: 823825
  - 8
    - HOW DO I PUT A DIFFERENT HEADER ON EACH PAGE? I want to put a different header on each page. I want to use consecutive dates, excluding weekends as headers on each page. Tag: Pivot Table Selection Tag: 823818
  - 9
    - Chart in Excel 2007- Positive & negative value columns Hello, How can I draw a column chart in Excel 2007 where the positive values are of one color and negative values are of a different color. Example- I would like to fill positive value columns in black and negative value columns in red. Thank you Tag: Pivot Table Selection Tag: 823813
  - 10
    - Bronze 3, Silver 2, Gold 1 What are these. I have an idea, but didn't know if someone had a clear classification. I only ask because, briefly, I started getting the bronze 3 next to my posts, but now it's gone. -- John C Tag: Pivot Table Selection Tag: 823810
  - 11
    - looking for a macro I am looking for a macro that will help me with my problem.... Iâ??m having problems with asking this question correctly so please bare with meâ?¦. In my workbook I have a worksheet titled â??CMG SA Masterâ?? and six other sheets â??test1â??,â?? test2â??,â?? test3â?? etcâ?¦ now on worksheet â??CMG SA Masterâ?? I have columns 1 to 300 (this may grow) and rows A:P (this will not grow). What I need is a Macro that will look at A2 and if it has an â??xâ?? in it past the information in A2:P2 into worksheet â??test1â?? if not look at B2 if it has an â??xâ?? then do the same on worksheet â??test2â?? if not continue on 4 more time (C2 â?? test3, D2 test4 etcâ?¦) now it can have an x in A2 and B2 I would need it to show up on both sheets â??test 1 and â??Test2â??. Then go down to the next row and do the same. See example A B C D E F G H 1 2 x x Processing Joy 3 x x x access Pro Header row has already been set up on all the sheets row 1.â?¦. Thanks in advanceâ?¦. Tag: Pivot Table Selection Tag: 823808
  - 12
    - CONVERTING CURRENCY TO WORDS CONVERTING CURRENCY TO WORDS E.G. 1,200 to One Thousand, two hundred dollars Tag: Pivot Table Selection Tag: 823804
  - 13
    - Auto populating excel fields I'd like to be able to auto-populate a cell by entering a number. For example, when I enter the E1 into a cell, I'd like the cell to populate with "Excellent scores". If I enter E2, the cell should populate with "Additional training required". The list goes on and on. Can someone please let me know how I can do this? Thank you in advance! Tag: Pivot Table Selection Tag: 823803
  - 14
    - Conditional Formatting on "Any Text" entered in a cell I Would like to setup some conditional cells to display a single color if any text is entered in the cell. How can I setup the conditional formatting for this? Excel 2000 Tag: Pivot Table Selection Tag: 823802
  - 15
    - EXCEL - I can not drag a cell with text to another cell, Just upgraded to Excel 2007. I have a cell with text only. I can not select and move it to another cell. The cursor changes to a cross hair, then as I move it the cell ID is displayed beside the cursor which changes to an arrow, but when I release it, nothing happens. I also can not cut or copy - then paste to another cell. I can pull the text to the other location with up/down then left/right drags. I have check the options and drag and drop is enabled. Tag: Pivot Table Selection Tag: 823799
  - 16
    - Integer convert to minutes. I want to take the solution to a simple arithmetic formula, say 90/18, and have it output as a value in minutes. I have been unable to figure this one out. In my spreadsheet, I want the answer for 90/18 to be :05. However, all the time functions that I have utilized (timevalue, minute, text (x, ":mm") ) are all giving me a value of :01. Does anyone know how to make that integer display as that number of minutes? Thanks in advance. Tag: Pivot Table Selection Tag: 823787
  - 17
    - number values Score: 1 Excel 2007 treats date values as serial numbers, counting the number of days since when? a. January 1, 0000. b. January 1, 1000. c. January 1, 1900. d. January 1, 2000. Tag: Pivot Table Selection Tag: 823779
  - 18
    - format cell does not work When I right click on a cell and select "format cells" the normal dialog box does not appear. There are some cells that it works ok on. Some of these have protection turned on and some are off. The workbook protection is turned OFF. The "format cells" menu selection is not grayed out Tag: Pivot Table Selection Tag: 823777
  - 19
    - How does a user spell check protected document in Excel? I created an Excel template that is protected. The users are able to enter data in the unlocked sells but can not spell check their work. Is there a way for a user to check spelling without taking off the protection? Mary Tag: Pivot Table Selection Tag: 823776
  - 20
    - Pivot Table Chart Question I have a pivot table that has the days along the left hand column, the the month totals and month average totals on the top. Then on the far right, are the accumulated month totals, and the accumulated Months averages per day. When i create the chart it will not give me the option to get the total averages. I am trying to show daily totals with a bar chart and then a line that shows the averages per day. Does this make sense? Any suggestions? I am using Excel 2007. Thanks Tag: Pivot Table Selection Tag: 823771
  - 21
    - deleting a comma from every cell in a column I have copied from the web a list of names, address, city, state and zip. The problem is in the column with the street address there is a comma at the end of each cell. Is there a way to delete the comma from every cell in that columm? Tag: Pivot Table Selection Tag: 823767
  - 22
    - Confirm a choice before Macro I have a macro attached to a button that will clear all data on a page. I'd like there to be a confirmation (that's how I know it from javascript) to preceed the macro. Here's the code thus far: Sub clearAll() Range("D3,...M48").ClearContents End Sub Is there something I can throw in the macro, before the Range line that will ask, "Are you sure you want to delete all data?" If yes then fire macro, if no then end. Thanks, JSnow Tag: Pivot Table Selection Tag: 823758
  - 23
    - My question disappeared? About five minutes ago I printed down an answer to a question I posted yesterday. Now I'm doing a search using my username or a part of the question title. I would think I'm out of my mind, but since the printout is sitting here right in front of me, I would guess something has happened on your end. The question was called Links not linking and it was posted in Excel General Questions. All I want to do is create another post. Tag: Pivot Table Selection Tag: 823757
  - 24
    - Internet Explorer Hello I get the following error when I try to connect to IE. Explorer has caused an error in GDL32DLL. Explorere will now close. If problem continues, restart the computer. What is causing this and how can it be fixed? I have to start the PC everytime I get this error by pulling the cord from PC. Thanks Tag: Pivot Table Selection Tag: 823755
  - 25
    - Excel worksheets appearance Hi, something is happening in some of my worksheets that is driving me crazy and I cannot figure out how to fix it. On the screen, some of the gridlines do not appear. It can be fixed by showing the gridlines in the Page Setup sheet, but that isn't the correct fix. whay would cause this and how can I make it stop doing it? It only happens in some worksheets and only in some areas of the sheet. Thanks for any advice. Jacke Tag: Pivot Table Selection Tag: 823752

I made a pivot table after selection of complete data. After that some more
data added to that database. Anyone suggest me when I click on "refresh
data" pivot table should be updated automatically on updated database.

Re: Pivot Table Selection by Bill

Bill
Fri Oct 10 04:32:27 CDT 2008

"Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in message
news:5BDAC1C0-2ED2-4328-8A36-FDAD9C75E035@microsoft.com...
>I made a pivot table after selection of complete data. After that some
>more
> data added to that database. Anyone suggest me when I click on "refresh
> data" pivot table should be updated automatically on updated database.

Yes! But, if you add data to the end of the worksheet it wont be seen by
the Pivot Table. It's easier to insert a line(s) in the body of the
worksheet in which to add data. I presume there is a way to correct the
actual Pivot Table after the event. However, I find it easier to create
lines, cut and paste the additional data.

Bill Ridgeway

Re: Pivot Table Selection by Roger

Roger
Fri Oct 10 11:32:12 CDT 2008

Hi Sandeep

Create a Dynamic Range as the source for your Pivot Table.
Insert>Name>Define>
Name myData
Refers to =INDEX($1:$65536,COUNTA($A:$A),COUNTA($1:$1))

Right click on your PT>Wizard>back>Source =myData>Finish

--
Regards
Roger Govier

"Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in message
news:5BDAC1C0-2ED2-4328-8A36-FDAD9C75E035@microsoft.com...
> I made a pivot table after selection of complete data. After that some
> more
> data added to that database. Anyone suggest me when I click on "refresh
> data" pivot table should be updated automatically on updated database.