Saturday, April 02, 2005

Microsoft Excel - How do I drag a formula so 1 variable changes and 1 stays on a cel

View Full Version : How do I drag a formula so 1 variable changes and 1 stays on a cel keithptI want to make a formula that when dragged will have one variable that will change as it comes down the page and one that will stay constantly refering to a given cell. Fred SmithYou want an absolute address. Precede the cell you want to stay constant with a $ sign. That tells Excel not to change it. Or, if you want, F4 will do it for you. -- Regards, Fred Please reply to newsgroup, not e-mail keithpt&quot..

Microsoft Excel - Excel 2003 addresses 1GB memory but only uses 200MB

View Full Version : Excel 2003 addresses 1GB memory but only uses 200MB CharlesUpgrading from Excel 2002 to 2003 expected to see memory use increase when calculating, but no change - why does it not use all 500MB available - can I set something? vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - Change a cell's value using a button

View Full Version : Change a cell's value using a button Ash007Hi - I know its possible but have no idea how to do it - How do I set up a cell with two buttons attached to it that increase and decrease the value in the cell? I.e. if I have a cell with 10% in it and hit the up button, the number goes up by 0.5% and down by the same amount if I hit the down button? I am pretty good in Excel but dont know anything about macros - but Im really not sure where you find this function. Th..

Microsoft Excel - cell formate

View Full Version : cell formate TerryWe have a large file the marge from couple small files (using copy and past). Since different personal processing those small files, the fist column (ID) which is numerical has different number fomate. Some file using general, some using number, and some using text to show up 0002 as ID (whenusing number, all the zeros befoer 2 will not show up but those zero sactually has meaning). Now the problem is when we try to sort whole the data, different category of format s..

Microsoft Excel - Inflation

View Full Version : Inflation Craig SchefWhat formula should I use to calculate vacation cost over 10 years at 3% inflation. Jason MorinA1: Vacation cost A2: Inflation % A3: Time Period =A1*(1+A2)^A3 HTH Jason Atlanta, GA >-----Original Message----- >What formula should I use to calculate vacation cost over 10 years at 3% >inflation. >. > Ken RussellIf cell A1 contains the first year's cost, then the formula is =A1*1.03^9. (Note that 9 and not 10 is the incremental years, 10-1=9). -- Ken R..

Microsoft Excel - Open file Slow on Excel 2003

View Full Version : Open file Slow on Excel 2003 VukaWe have upgraded our Office 2000 to 2003 and after the upgrade the response time to open a file on the network or C drive is slow. Once you have click on down arrow next to the look in box it takes a few seconds to respond. All the patches have been loaded. We are running windows XP Any suggetions vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - How do I turn off [Group] mode?

View Full Version : How do I turn off [Group] mode? tothemaxI'm stuck in Excel - I somehow entered a [Group] mode that blocks most functions. Can't find anything in Help on this topic. [Group] appears on the blue barat the top of the Excel window after the filename. I found I could enter [Shared] mode and turn that on and off but have not found the key to turning off the [Group] mode. Gord DibbenFirst.......make note of which sheet tabs are white-colored. These are the grouped sheets. You can select one..

Microsoft Excel - fractions turn into dates when pasting in excel 2003

View Full Version : fractions turn into dates when pasting in excel 2003 Eddie Spaghettiwhen pasting data from a web page [ Metric/Inch Conversion] into excel, fractions turn into dates. The same chart pastes perfectly into word but wont copy from word into excel with out the fractions changing into dates. and I cannot change the dates back to fractions ie 1/64 turns into Jan-64 which converts to 23377 Thanking you in advance tinaHi If you change cell format to text in excel and then copy from word and s..

Microsoft Excel - How do I search for text within text?

View Full Version : How do I search for text within text? Dave BaileyHow do you search for text within text and then when you find a match, return the information in the next column? I usually use the functions index(match) to do the above. Unfortunately, the function only works when there is a identical match. So, I need to find 765849 in 111765849111 and then return the next column. Thanks, I really appreciate the help! Best regards, Dave BiffHi! If the values were really text, you could use wildcards..

Microsoft Excel - Formatting data

View Full Version : Formatting data rnHi When formatting the same type of data frequently, I design a template with three sheets, the first to paste the raw data, the second a temporary for the fuctions/formulas for fomatting the data in the former sheet and the last one to paste the temporary sheet as values. Please see example below. In the second sheet I write the formulas for more than the rows of expected data. Say somtimes 200 rows as the rows of data varies from 10 to 150. My questions are, 1. Is..

Microsoft Excel - trend

View Full Version : trend Dr. Sachin WaghIs there any way in which I could arrive at a trend on reducing percentage Starting with 13% for 2000 & Dropping to 8% for 10000 So basically, when I put any value between 2000 & 10000; say, 7569 in c1, I get the corresponding % in c2 -- Dr. Sachin Wagh MBBS, DHA, DPH MaxOne way .. Assume the known data below is in A1:B2 2000 13% 10000 8% and C1 contains 7569 Put in say, C2: =FORECAST(C1,$B$1:$B$2,$A$1:$A$2) C2 will return 9.52% -- Rgds Max xl 97 --- GMT+8..

Microsoft Excel - display window

View Full Version : display window JATRunning Office 2003 student and teacher edition. I can open Excel, use the worksheet, and save. If I re-open the worksheet, I have a blank grey screen, and have to search recent documents in order to access the worksheet. Word is running ok, only the excel module is a problem. All previous documents are doing the same thing. I have checked my views, options etc. even did a repair on office, nothing seems to work. Any suggestions?? Thanks Jan Karel PieterseHi Jat, &gt..

Microsoft Excel - Re: (Sub)totals

View Full Version : Re: (Sub)totals JulieDHi i would use a SUMIF function =SUMIF(F:F, Sector 1 ,B:B) Cheers JulieD Kwakkel <kwakkel@skynet.be> wrote in message news:42394995$0$14966$ba620e4c@news.skynet.be... > Hello everybody. > I have the following problem: > I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now i > need to count all the quantities within the same sector > Sorting + AutoSum isn't an option, since the file has other calculati..

Microsoft Excel - Macro not running on Office 2000, only 2003

View Full Version : Macro not running on Office 2000, only 2003 Rob GouldHi, Can anyone please tell me why the code below would run perfectly in Excel 2003, but not Excel 2000 (and 97 I assume). It stops on the Paste Special section: If Not IsDate([C1].Value) Then MsgBox Please enter a date in C1! [C1].Value = Enter date here Exit Sub Else thisDate = Range( Production!C1 ).Value Sheets( Data ).Select ActiveSheet.Unprotect Range( G1:BH1 ).Select Select..

Microsoft Excel - How do I get the same header/footer to print on each page of a wo.

View Full Version : How do I get the same header/footer to print on each page of a wo. Stephen ConnollyI've made a custom header / footer how do I get it to print on each page of a work book without having to re-create on each page -- Stephen Connolly Jason MorinGroup the worksheets by clicking the first worksheet tab, hold down the <Shift> key, and click the last worksheet tab. Now set up the header under File > Page Setup. To ungroup, click any one of the worksheet tabs, or right- click one of..

Microsoft Excel - Jeg mangler en skabelon!!!

View Full Version : Jeg mangler en skabelon!!! nillHej med jer, Er der nogen af jer der ved hvor jeg kan finde en template til en lønseddel? Harald StaffPrøv gruppen dk.edb.regneark. HTH. Beste hilsen Harald nill <nill@discussions.microsoft.com> skrev i melding news:FD35861B-9A2E-41D4-975E-42E6302B94FB@microsoft.com... > Hej med jer, > > Er der nogen af jer der ved hvor jeg kan finde en template til en lønseddel? vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterp..

Microsoft Excel - can not open excel file, too many different cell formats

View Full Version : can not open excel file, too many different cell formats Abdurcan not open a specific excel file, too many different cell formats message appears on my screen. the file contians about 85 work sheets Dave PetersonSome info: XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have posted that they could open the file in OpenOffice, then remove some of the formatting, save it and then excel would open that file ok. http://..

Microsoft Excel - Excel prompt to save unmodified files

View Full Version : Excel prompt to save unmodified files AlanExcel keeps asking to save unmodified files, very anoying after a while. Any Idea SP3 installed Dave PetersonThere are worksheet functions that are volatile. They recalculate whenever excel recalculates the workbook. Do you have functions like: AREAS() INDEX() OFFSET() CELL() INDIRECT() ROWS() COLUMNS() NOW() TODAY() RAND() And newer versions of excel like to recalculate older workbooks when they're opened. Then xl knows that the file is chang..

Microsoft Excel - League Tables for sports events

View Full Version : League Tables for sports events mje26811I am trying to find a way to data-sort in real time. I have a league table set up for my football team which lists team name, games played, games won, games drawn, games lost, goals for, goals against, goal difference, and points awarded total. The table auto-populates with the scores I enter into a fixtures sheet. All I would like to do is to formulate a way to get the whole table to data-sort in real time, in the follow..

Microsoft Excel - merge/combine workshhets

View Full Version : merge/combine workshhets Jeff224I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Ron de BruinTry this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl Jeff224 <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one sp..

Microsoft Excel - Citation

View Full Version : Citation Daniel FenechHi, I am a Pharmacy student from Malta and found useful statistical information from MS Office Excel Help. Any idea how to cite the information in an author-date citation style? How should the reference be made? Thanks and hope you can help, Daniel Harald StaffHi Daniel I don't really understand what you want done, but see if this get you started: http://www.rondebruin.nl/id.htm Also, Help files (chm type) can be decompiled into a bunch of separate html files. Do..

Microsoft Excel - Print question?

View Full Version : Print question? JohnCan someone gives me help with this excellent code? Print dialog shows on sheet numer 5, and I want to stay at sheet number 1, because command button is sheet 1. Thanks!!!! Private Sub cmdPrint_Click() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Check for protected workbook If ActiveWorkbook.ProtectStructure Then MsgBox Workbo..

Microsoft Excel - Re: (Sub)totals

View Full Version : Re: (Sub)totals Arvi LaanemetsHi When you need to view totals for various groups, then for such cases I sometimes use SUBTOTAL function and autofilter. P.e. header row is row 3, and you want to display the sum of filtered data for columns E and F and to count filtered rows in column C (in column C are text values), at first row. C1=SUBTOTAL(3,RangeC) E1=SUBTOTAL(9,RangeE) F1=SUBTOTAL(9,RangeF) (replace RangeC, RangeE and RangeF in example formulas with real range references, or with d..

Microsoft Excel - How do I convert Supercalc to Excel

View Full Version : How do I convert Supercalc to Excel Bean CounterI have data on disks that are in SuperCalc that I would like to convert to Excel, but I don't know how to do that. Chip PearsonExcel can't open SuperCalc files. You'd have to get a copy of SuperCalc and save the files from there in to a format that Excel can read (e.g., text, CSV, SYLK, etc). -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com Bean Counter <Bean Counter@discus..

Microsoft Excel - Copy and Paste and keep format the same

View Full Version : Copy and Paste and keep format the same Brian CaraherI am trying to copy and paste selected rows from varios workbooks into one master workbook. When I copy and paste a range of cells I find the rows collapse and show only one line of text. How can I copy and paste this information and automatically show all text in that cell? Some rows have one or two lines of text, others have up to 12 to 14 lines of text. Dave PetersonIt sounds like you just have to adjust the cell's formatting for..

Microsoft Excel - spin button value

View Full Version : spin button value tinaHi Does the value in spinbutton properties have to be numeric or can it be a day eg mon tues wed etc if so how do I dio this Thanks tina Jim RechA Forms toolbar spinner control returns an integer. You can have another cell with a formula that references the cell linked to the spinner like =CHOOSE(A1, Sun , Mon , Tues , Weds , Thurs , Fri , Sat ) Here A1 is linked to the spinner. You'd want to l..

Microsoft Excel - how to exclude data outliers from formula or chart without deleti.

View Full Version : how to exclude data outliers from formula or chart without deleti. Michael JI have a data set with some outliers (blunders usually) that I want to temporarily exclude from formula or charts. I would like a way of locking-out these data cells. I could selectively skip over them but this is sometimes cumbersome. I need a way to toggle them in or out depending on my analysis. Dave PetersonIf your data is in columns, maybe just use an additional column and use some kind of for..

Microsoft Excel - How to insert carriage return in the middle of a text formula to .

View Full Version : How to insert carriage return in the middle of a text formula to . DaveI want to insert carriage return in the middle of a text formula to force the resulting text to wrap at a specific point. It would be the same thing as pressing Alt/Return while entering text manually except that the text is being created in a formula. -- Dave Bob PhillipsYou can do it with Alt-Return, but the formulas have a nasty tendency to not work then. -- HTH RP (remove nothere from the email address if maili..

Microsoft Excel - Calculate time

View Full Version : Calculate time Rao Ratan SinghI want to calculate time as per my sheet have data like - a1 a2 a3 a4 a5 a6 a7 Time In time out time in time out regular hrs ovrtime hrs Tota wrkng hrs how should i calculate Harald StaffHi See http://www.cpearson.com/excel/overtime.htm on this. HTH. Best wishes Harald Rao Ratan Singh <RaoRatanSingh@discussions.microsoft.com> skrev i melding news:595E0B84-8633-46A5-88AE-BDF30AD9D266@microsoft.com... > I want to calcu..

Microsoft Excel - Excel 2003 does not prompt to save changes when closing?

View Full Version : Excel 2003 does not prompt to save changes when closing? JohnewallyWe just got Excel 2003 installed on our computers at work and I noticed that when I make updates to an Excel document, that the program does not automatically prompt me to save the changes? Is there a setting somewhere that I need to change for this? Dave PetersonThis is gonna be fuzzy. There have been a couple posts in the last week or so that complained of the same problem. I think those users wrote back saying that..

Microsoft Excel - Spider Grams

View Full Version : Spider Grams KenHi, I hope you are all doing well. A user on our system wants to be able to do spider grams in conjunction with Excel spreadsheets. I've checked Excel help and the term is not locatable from the index. I've heard of spider grams but do not know anything about them. Is this a separate program that somehow works with Excel through export of some other function? Any help will be greatly appreciated. KenP Debra DalgleishAssuming you want something like this: http://www.bud..

Microsoft Excel - Using a macro how do I group every sheet within a book?

View Full Version : Using a macro how do I group every sheet within a book? Pank MehtaUsing a macro how do I group every sheet within a book? I have created a macro, but find that the names of the sheets will differ every time and hence I need a mechanism that will automatically select all of them to allow me to undertake editing within them all at the same time. Bob PhillipsDim arySheets Dim sh Dim i As Long ReDim arySheets(ActiveWorkbook.Worksheets.Count - 1) For Each sh In ActiveWorkbook.Worksheets ar..

Microsoft Excel - How do I calculate a value based on dates?

View Full Version : How do I calculate a value based on dates? JohnDI have a worksheet that calculates YTD (year-to-date) totals from 3 categories...labor, travel, and other expenses. I want to add a row that will separate monies spent from a range of dates (i.e. 1st of the month to the last Friday of the month) in order to calculate monies spent MTD (month-to-date). I am willing to change the monthly values if need be. Dave OHi, John- I arrived at a solution for this by mocking up some data: ~Column hea..

Microsoft Excel - Re: Upgrade from Excel 97 to 2003

View Full Version : Re: Upgrade from Excel 97 to 2003 J. FreedActually, I have a specific problem with upgrading that no one seems to be able to address.... I'm working on a migration project to move people to Office XP, and we've encountered several Excel 97 files with a lot of charts and graphs (over 20 each). When we open them in XP, the charts get dropped off with an error message that the file lost data and this was the best it could do. Opening two of the files in Excel 2000 causes a memory crash,..

Microsoft Excel - Save Changes Prompt at close

View Full Version : Save Changes Prompt at close JohnewallyWith Excel 2003 I am encountering the following: If I open an existing workbook directly by clicking on the file and make changes to that workbook, the program does not prompt me to save changes when I close the document. If I first open Excel, and then browse to find the file I want to open, the program DOES prompt me to save changes when I close the file?? Any idea why this is happening? Dave PetersonSee one guess at your first post. Johnewally..

Microsoft Excel - help with formula

View Full Version : help with formula KevinHi I have a worksheet with column C (start time) & column D (end time). (C1:D50) e.g C D 07:45 08:50 11:50 12:50 In Cells A1 and B1 i have the start break time and end break time '08:00' and '08:20' for the first break. In Cells A2 and B2 i have the start break time and end break time '12:00' and '12:30' for the first break. What i want to do is calculate the amount of time lost but exclude both break times can anyone help? Jason MorinChip Pe..

Microsoft Excel - Drop down list with shapes

View Full Version : Drop down list with shapes NeilI'd like to create a drop down list of 3 items, a red diamond, green circle,& a yellow triangle that I can put into many cells for a stop light chart. I 've tried using the data validation with the ASCII codes but it didn't work Any suggestions or help would be appreciated Thanks Neil Debra DalgleishYou can't format the items in a data validation list, so you won't be able to show the colours, or a different font. Perhaps you could show Low, Medium,..

Microsoft Excel - Subtotaling on column with IF statement results

View Full Version : Subtotaling on column with IF statement results STKI created a spreadsheet with one column having an IF statement - it calculates how much we should re-order per store based on what was sold. I asked it to subtotal for each store - the formula appears in the cell, but it does not calculate the subtotal. I tried saving the column as the values, but it still does not subtotal. When I typed in the numbers, it DID subtotal. What do I need to do so it will subtotal? Thanks for your help. -..

Microsoft Excel - Unexplainable Duplicate Entries

View Full Version : Unexplainable Duplicate Entries matthews_jdI have sales file where each row contains information about an individual part that is being produced and sold. When I ran a pivot table report for sales by program there were two identical entries for Program X . I've looked at the entry detail and both have the same text entered (including use of caps) and general formatting. Any other ideas? This seemingly small error is causing issues with later calculations. Thanks..

Microsoft Excel - Keyboard shortcut for "copy paste special values?"

View Full Version : Keyboard shortcut for "copy paste special values?" Star AJI copy and paste-special values repeatedly. Is there a keyboard shortcut so that I don't have to point and click the mouse to Edit Paste Special Values each time? Jason MorinI have the button attached to one of my toolbars. Go to View > Toolbars > Customize and click on the Command tab. In Categories click Edit and scroll down on the right to find Paste Val..

Microsoft Excel - How is everyone today?

View Full Version : How is everyone today? MommaQI am doing great but I was wondering how everyone else is doing. Have a wonderful day vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - Excel 2000

View Full Version : Excel 2000 jmartin7611How do I color code the tabs in Excel 2000? Gordonjmartin7611 wrote:

Microsoft Excel - excel date shows 01/00/1900!

View Full Version : excel date shows 01/00/1900! KateZitoI have a whole spreadsheet where when I type in the date i.e. 3/5/05 it then shows 1/00/00 as the date! I have tried changing the format to the many different ways to show the date, but I can't get it to show 3/5/05! Is there something I am missing? Thank you so much for your help! Katie Barb R.I'm guessing that you entered the date in the cell like this =3/5/05 If so, a calculation is being done. I get 1/0/1900 when I enter it that way. Try just e..

Microsoft Excel - Excel 2000

View Full Version : Excel 2000 jmartin7611How do I color code tabs in 2000 Jock WFormat - Sheet - Tab colour jmartin7611 wrote: > How do I color code tabs in 2000 vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - can you date time stamp entries in excel

View Full Version : can you date time stamp entries in excel opinkyis it possible that when someone puts an entry on a shared worksheet in excel it can automatically date and time stamp their entry. Bob PhillipsPrivate Sub Worksheet_Change(By Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range( A1:H10 )) Is Nothing Then With Target .Offset(0, 1).Value = Format(Now, dd mmm yyy hh:mm ) End With End If ws_exit: Application.EnableEve..

Microsoft Excel - Does Excel support endnotes?

View Full Version : Does Excel support endnotes? Karen_HarnerI want to insert endnotes into my excel spreadsheet to track sources used for each cell. Duke CareyNot endnotes, but Comments. Select the cell you want to document, then press Shift-F2 & type your notes Cells with documentation/comments are denoted by a small red marker in the top right portion of the cell. Hovering your mouse over one of these cells displays the comment. To be sure the red markers are visible, select Tools

Microsoft Excel - how can i make paragraphs in side one cell in Excell?

View Full Version : how can i make paragraphs in side one cell in Excell? ashraf JulieDHi press ALT & ENTER when you want a new line Cheers JulieD ashraf <ashraf@discussions.microsoft.com> wrote in message news:E508A2C8-CC79-4107-93EE-5D607A53E8BA@microsoft.com... > Marvin P. Winterbottomor, format cell / alignment / wrap text vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - prevent saving a worksheet

View Full Version : prevent saving a worksheet looking4helpWhen I post my worksheet to my website, I want the user to be able to use the filtering functions I have in the worksheet but I don't want them to be able to save the worksheet to their hard drive. Is there a way that I can do this? Thanks! -- looking... Gary BrownIn the 'ThisWorkbook' code section of the workbook, put... Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True End Sub Of course, if they have ma..

Microsoft Excel - HOW DO I CALCULATE SUFACE AREA DEPENDENT ON SHAPE

View Full Version : HOW DO I CALCULATE SUFACE AREA DEPENDENT ON SHAPE RandyI am trying to build a spread sheet that will calculate the surface area of a swimming pool by using the appropriate formula for the calculation. The appropriate formula is dependent on the shape of the pool. If the entry in B3 is rectangle I need a formula in H3 to multiply C3 by D3 where C3 and D3 are the values for length and with respectively. If the entry in B3 is Circle I need the formula in H3 to mul..

Microsoft Excel - I want a name in one spot and it's code in another

View Full Version : I want a name in one spot and it's code in another TwoWheelDemonI am using Excel, I am using drop down list, when I choose a name from a drop down list I want the cooresponding code to go into another column. Without using If then else, can I make excel do this JulieDHi you can use a VLOOKUP function or worksheet_Change code with the VLOOKUP you have your names and the corresponding codes somewhere else in the workbook and then when the name is selected the VLOOKUP will return the app..

Microsoft Excel - Finding a match

View Full Version : Finding a match KenHi, I have two columns 300+ rows long. One (A) contains names. One (B) contains single digit numbers; either 1 or 2 or 3, etc. through number 8. What I would like to do is type in a name in, lets say (C10) that would return EVERY number in column (B) that was next to that name. If I type in(C10) Donald Getts and this name appears 20 times in (A) I need the 20 numbers that are in column (B) next to Donald Getts. Of course I need the numbers separated by a comma or ha..

Microsoft Excel - Can I add different custom font colors in Excel other than the on.

View Full Version : Can I add different custom font colors in Excel other than the on. DomikenI need to add a lighter brown style color in Excel to color a box, and I dont have that custom flower looking option that helps you select which color you want. I only have some set' colors available to me. Is there any way to add or make that box appear for a different color selection? Bob PhillipsYou can change the colours in a workbook, Tools>Options>Color. You could save the changed workbook as a..

Microsoft Excel - set row height upon opening excel

View Full Version : set row height upon opening excel Walter L. SkinnerI would like to have the row height set to 20 upon opening or creating a new workbook. You can change the default setting for the column, why not for the height of a row. If there is a way to write a macro that will execute when that workbook is open I would be interested in that also. Thanks Sunil JayakumarIf you go to your Personal.xls, and paste this code in the ThisWorkbook page, it will automatically set t..

Microsoft Excel - In Excel - a function or operation for "view visible cells"

View Full Version : In Excel - a function or operation for "view visible cells" gpyogitrying to find the view visible cells operation - was in a worksheet on prior version of excel- but seemd to disappear with new computer using excel from office 2003 JulieDHi do you mean select visible cells ? edit / goto / special - choose visible cells Cheers JulieD gpyogi <gpyogi@discussions.microsoft.com> wrote in message news:C426D188-AF57-4931-BA38-D3F287CFF24F@microsoft.c..

Microsoft Excel - Change the low date range regional setting.

View Full Version : Change the low date range regional setting. genesearcherIn genealogy I need to list the 4-digit year. I'm trying to create a spreadsheet that sorts on date. The low end of the date range in regional settings is 1930. I need to push this back to make excel recognize the older dates. How do I do it? Dave PetersonEnter a 4 digit year when you enter the data. But excel doesn't see dates before January 1, 1900. John Walkenbach has an addin you may want to get if you're dealing with dates b..

Microsoft Excel - need a template to compare man hours to workload?

View Full Version : need a template to compare man hours to workload? ace-- ace Gordonace wrote:

Microsoft Excel - Is there a mileage grinder available?

View Full Version : Is there a mileage grinder available? RLPRANEI WORK WITH DISTANCES FROM CITY TO CITYS IN THE US. ALMOST ALL ARE REPETITIVE, BUT THE DESTINATIONS CHANGE DAILY. IS THERE A TOOL, OR WIZARD THAT CAN CRANK THESE OUT LIKE THE FORMULA BAR? IN OTHER WORDS I GET 125 DESTINATIONS FROM ROCK ISLAND, IL, AND ANOTHER 87 DESTINATIONS FROM SHELL ROCK, IA. THESE COME IN VIA E-MAIL IN EXCEL FORMAT, COLUMN A IS ORIGINATION CITY, COLUMN B IS STATE ABREVIATION. COLUMN D IS DESTINATION CITY, COLUMN E IS ST..

Microsoft Excel - Print as draft

View Full Version : Print as draft AlcidesI want to know if there is a way to print a report in excel with the word draft written accross the page in the back ground Barb R.Try Format -> Sheet -> Background. Alcides wrote: > I want to know if there is a way to print a report in excel with the word > draft written accross the page in the back ground Barb R.Also, look up OVERLAYS. Alcides wrote: > I want to know if there is a way to print a report in excel with the wo..

Microsoft Excel - how to share a row throughout the worksheet?

View Full Version : how to share a row throughout the worksheet? asd KassieWhat do yo mean? asd wrote: > vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - want to flip data in an excel column so last is first, first is l.

View Full Version : want to flip data in an excel column so last is first, first is l. kcI have data in a list/column that I'd like to have listed in reverse order, i.e. the list appears with the last item from the initial list first and the first item from the initial list last. Does anyone know of a way to do this? Thanks. Dave O~Insert a column: call it Column A ~Go to the last item in the list, and enter 1 in the new column next to the last entry: suppose that's cell A50 ~In the cell immediately abov..

Microsoft Excel - Copy Cell or Column without advancing formula reference?

View Full Version : Copy Cell or Column without advancing formula reference? sgluntzI have a spreadsheet with formulas referring to another worksheet. I need to copy the columns however, each time I do, the formulas advance to another cell. Which is an incorrect reference, skewing my results. Duke CareyYou need to modify the formulas to use absolute references. Edit each of the cell addresses in the formula and put $ (dollar signs) in front of the column letters. sgluntz wrote: > I have a..

Microsoft Excel - How can I switch so that rows are referenced with letters, and co.

View Full Version : How can I switch so that rows are referenced with letters, and co. willyv1How do I switch the worksheet axis labels? Rather than having a row-column reference of 1A, I would like to switch the row-column reference to A1. Dave PetersonYou can't. You could use column A and Row 1 and put anything you want in there. But what letter would you use for row 65536? willyv1 wrote: > > How do I switch the worksheet axis labels? Rather than having a row-column > reference of 1A, I would..

Microsoft Excel - IF Statement equalling multiple words. Please Help

View Full Version : IF Statement equalling multiple words. Please Help Phillip VongI want to say if cell A1 = word1 or word2 or word3 , then . Otherwise do this. I know how to make check if it = 1 item, but how do you make it check 3 or four things without using multiple IF statements? Thanks Phil CodyUse and If(or(A1= word1 ,A1= word2 ,A1= word3 ),then ,else) That should work for you, Cody Phillip Vong wr..

Microsoft Excel - How do copy text from a picture/image & make it available for edi.

View Full Version : How do copy text from a picture/image & make it available for edi. copy text from image to excelI am trying to copy text from an image. And I want that text to be copied to Excel/word not as a picture or image but as a text hi, this cannot be done with excel. the text on the image is part of the image and excel only see the total image not individual parts of the image so it cannot seperate. sorry. >-----Original Message----- >I am trying to copy text from an image. And I want t..

Microsoft Excel - How to copy text from a picture/image & make it available for edi.

View Full Version : How to copy text from a picture/image & make it available for edi. SubuuI am trying to copy a text from a picture/image and make it available in excel/word/powerpoint as normal text and not as an image/picture. vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - linking files with blanks

View Full Version : linking files with blanks muscogeeWhen I do a paste special to link a source file with some blanks in it to second file, the blanks show up as zeroes in the second file. How can I link these files and have the blanks transfer as blanks? vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - Parsing a space delimited file into segments

View Full Version : Parsing a space delimited file into segments robinsgateI have a space delimited file that contains address information. Is there a way to parse each record so that I parse the records into specific portions (Company Name is field 1, Street Address is field 2, etc.)? Using the space as a delimiter doesn't help in this instance because every word is obviously separated by a space. Any help with this would be appreciated. Thanks. hi, you could use the fixed width option but unless all of..

Microsoft Excel - A peculiar PDF from Excel problem

View Full Version : A peculiar PDF from Excel problem Daniel BonallackI can PDF a full Excel file without problems. But if I copy in a worksheet from another workbook, then when I come to PDF that file, it will no longer print the whole workbook at once - it will do a few pages up until the copied in sheet, then it will ask for another save location, and it will make a new file. I've had the same problem with Distiller and PDF Writer - if anyone has encountered this problem or can suggest a solution, I'd..

Microsoft Excel - re install windows xp

View Full Version : re install windows xp jjoksi reinstalled xp and now my roxio adaptec burner program will not work,,it will hardly open and is very slow,,,like 5 min to open a mp3,,,help Chip PearsonYour best bet is to post in a Windows newsgroup. This newsgroup deals with questions about Microsoft Excel. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com jjoks <jjoks@discussions.microsoft.com> wrote in message news:31CB14F4-3988-4615-9..

Microsoft Excel - How do I purchase just the trial excel program that I have. I don.

View Full Version : How do I purchase just the trial excel program that I have. I don. MoseshaleyI havea rtrial version of Excel 2003 and want to purchase it without purchasing the whole Microsoft Package. I cant locate any information on how to do that. dlwhttp://www.amazon.com/exec/obidos/ASIN/B0000AZJV6/qid=1111100291/sr=2-2/ref=pd_bbs_b_2_2/002-6330957-5925660 Myrna LarsonAre you sure you have no use for the other components of Office? It's a much better bargain than the Excel program alone. On Thu,..

Microsoft Excel - Conditional Formatting

View Full Version : Conditional Formatting Leslie BurnsDoes anyone know how to use the conditional formatting command to find the duplicates in a cell? Jason MorinAssuming your list of values / entries are in col. A, select the column, go to Format > CF, choose Formula Is on the drop-down list, and put: =COUNTIF($A$1:$A1,$A1)>1 Click the Format radio button and format as desired. HTH Jason Atlanta, GA >-----Original Message----- >Does anyone know how to use the conditional formatt..

Microsoft Excel - Inserting Photos into Excel and linking to Word.

View Full Version : Inserting Photos into Excel and linking to Word. Uploading PhotosI need to be able to upload a digital photograph to a single MS Excel worksheet and then link that photograph into a MS Word document from the Excel workbook (photo needs to be visible in both Excel and Word). The photos should all appear the same physical size when they are place into the Excel worksheet and they should appear the same physical size on the linked Word document. Can anyone describe the steps I should use..

Microsoft Excel - Need a formula to determine the greater of a or b...

View Full Version : Need a formula to determine the greater of a or b... sonyavI need to compare 2 number and enter into a 3rd column the greater of the 2 numbers. Paul Bsonyav, how about something like this =MAX(A1:B1) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 sonyav <sonyav@discussions.microsoft.com> wrote in message news:2..

Microsoft Excel - Copying a date format

View Full Version : Copying a date format Michele/Nextelwhen I've copied one cell (formatted in mmm-dd) into a different worksheet within the same workbook, I'm getting wierd results. For instance, From worksheet A, I've copied cell C1 (which is Oct-04) into worksheet B, cell A2. It copies fine, but when I go to change the date to 05/05, it comes out as Jan-00. The actuals cell now reads =05/2005. However, it's still formatted for the date. But I've never actually inputed an = sign. I've tried to just to..

Microsoft Excel - Ever use Excel for documents instead of Word?

View Full Version : Ever use Excel for documents instead of Word? me@privacy.netIve never been a great master of Word. Lately Im finding that even using Excel for non traditional uses such as letters and documents seems as intuitive to me as using Word. Anyone else use Excel for 'bout everything? dlwI worked with a guy who used excel like a word processor, letters, memos, etc. It works fine if all you want to do is replace the typewriter. But if you want to use modern word processing features, like inden..

Microsoft Excel - Linking sheets

View Full Version : Linking sheets SmithI have two worksheets in a workbook to store data about my CD collection; one is alphabetical and one chronological (based on purchase date). How do I link the sheets so that when I enter data about additions to the collection in the chronological sheet the alphabetical sheet automatically updates? Any help appreciated. TIA Tony Smith Earl KiosterudTony, Normally, you keep them in one table, and just sort on the column you're interested in at any time (name, purcha..

Microsoft Excel - Excel cannot shift nonblank cells

View Full Version : Excel cannot shift nonblank cells Mr. Maz.I have an Excel sheet where I try to insert columns. I get the error that says that Excel cannot shift nonblank cell off the worksheet. But there are tons of empty cells to the rigth of my data starting at column BC.... If I save my worksheet I can insert one, two, three columns before I get the same error again. I save again and I can insert one or two more.... It's painfull especially when I try to do this in a macro. I can't be saving betwe..

Microsoft Excel - graphic behind text in Excel?

View Full Version : graphic behind text in Excel? DBrayIn an earlier version of Excel graphics could be placed behind text in a cell. On the drawing menu the icon that controlled this feature was a little dog. In Excel 2002 this does not appear to be a viable option. Does this feature strike a bell with anyone? Is there a way to accomplish this in 2002? I want to add a signature into a box and not have the signature overwrite the box. Earl KiosterudD, You can't put graphics in back of cells, except as a..

Microsoft Excel - Moving blocks of data in excel

View Full Version : Moving blocks of data in excel YasminHi I have 3 columns of data which contains say 20 rows of Africa data, 30 rows of Asia data, 17 rows of Canada data etc. ie. the number of rows change for each group as you move down the columns. I would like to move the data so that it is side by side ie africa first 3 columns, asia next three columns etc. ie. its easier to look across than down. Anyone know how to move data easily from being effetively vertical to horizontal (not the transpose ki..

Microsoft Excel - Compare 2 different workbooks with the result in a 3rd

View Full Version : Compare 2 different workbooks with the result in a 3rd cursednomoreI have two workbooks (2005 Sales, 2004 Sales), which track daily results in half hour intervals. I want to be able to show the increase in 2005 in a 3rd workbooks. The first two workbooks are identically formatted. How can I do this? Many thanks to all in the forum who have helped in the past. Jim RechIf the data is in exactly the same position in the two worksheets you could copy/paste one's data to a new worksheet an..

Microsoft Excel - Can you enable auto complete from a custom list in Excel?

View Full Version : Can you enable auto complete from a custom list in Excel? poaq Gord DibbenNo. Autocomplete or Pick from list gets its information from cells above and below the point in the same column that you are entering new data. A custom list entered in Tools>Options>Custom Lists is used for autofilling and when sorting. Gord Dibben Excel MVP On Thu, 17 Mar 2005 13:27:01 -0800, poaq <poaq@discussions.microsoft.com> wrote: vBulletin v3.0.3, Copyright &copy..

Microsoft Excel - Compound annual growth rate [CAGR]

View Full Version : Compound annual growth rate [CAGR] PaulDoes anybody have the excel formula for CAGR? Don Guillett=rate or =(EndValue/StartValue)^(1/Year­s) -- Don Guillett SalesAid Software donaldb@281.com Paul <Paul@discussions.microsoft.com> wrote in message news:BC6627DA-CE03-4594-BD9E-AF4D1059654E@microsoft.com... > Does anybody have the excel formula for CAGR? KemSDon, If you wanted the CAGR wouldn't you apend a -1 to your formula? =(EndValue/StartValue)^(1/3)-1 e.g (15000/1..

Microsoft Excel - adding columns

View Full Version : adding columns GarryHow can I add additional columns to Excel. I need more columns than are currently available. My sheet runs out somewhere around column IV MichaelHi Garry Sorry, That is the maximum allowed in Excel per sheet. The other option is to break up some of your data onto another sheet and then bring it all back together on a summary sheet. HTH Michael Garry wrote: > How can I add additional columns to Excel. I need more columns than are > curr..

Microsoft Excel - Exporting from Excel to multiple XML files

View Full Version : Exporting from Excel to multiple XML files QuestionsAnyone know if it is possible to export multiple lines of and Excel file to multiple XML files? An example might be an Excel spreadsheet where each row is a Purchase Order (PO) complete with details of ordered items, vendor etc. This might be data exported from a database for instance. The data would be exported using an XML Schema (XML map), defined as PurchaseOrder.XSD. vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Ente..

Microsoft Excel - How do I make a formula refer to given data even if I do a sort f.

View Full Version : How do I make a formula refer to given data even if I do a sort f. keithptI want to create a formula in a spreadsheet that refers to specific cells of data. However, I also want to be able to sort my data but keep the formula refering to the same information. IE, say I have a collumn of ten numbers. I would like to be able to have a formula take the average of the first 5 listed. If I then did a sort function of these ten numbers that changes the order of the numbers, I want my formul..

Microsoft Excel - Re: Calculating dates

View Full Version : Re: Calculating dates Bob PhillipsAssuming your date is in A1 =A1-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1)) if now is 26th July 2008 or =TODAY()-DATE(YEAR(TODAY())-1,MONTH(A1),DAY(A1)) or if now is today -- HTH RP (remove nothere from the email address if mailing direct) Kwakkel <kwakkel@skynet.be> wrote in message news:423a099e$0$10333$ba620e4c@news.skynet.be... > A quick question before i'm off to bed :) > I got several dates, all in the future. I'll just give..

Microsoft Excel - Multi-Page Word Doc Linked to Excel?

View Full Version : Multi-Page Word Doc Linked to Excel? KaleaHow do I get a multi-paged word document to show ALL pages when it is object-linked to an Excel Spreadsheet? I've tried everything I can think of, and it only shows the first page in Excel. Please help, and thanks in advance. -Kalea CyberTazAFAIK only the first page will display on the wksht. In order to access the entire doc, double-click it so it will open in Word. HTH

Microsoft Excel - How do you enter a negative time number in Excel?

View Full Version : How do you enter a negative time number in Excel? Thomas99I am trying to create a spreadsheet to track employee time records, and I need a way to enter negative time numbers. In order to correct data entry errors, I need the ability to enter -8:30 , for example, and include that figure when totaling the column. Myrna LarsonThen you'll have to switch the workbook to the 1904 date system (Tools/Options/Calculation). Be forewarned that if you already have dates on..

Microsoft Excel - Error message opening Excel

View Full Version : Error message opening Excel EdWhen I open Excel I get this message: Microsoft Visual Basic Automatic Error Unspecified Error I close the message box and Excel opens okay but runs slow, especially cutting and pasting. The massage appears with any size Excel file. It also appears when I shut down Excel. No other program, Office or otherwise, gives this message. I am running Excel 2002 SP3 under Windows XP SP2. Thanks -- Ed Anne TroyCheck out Excel troubleshooting. Specifically the stuff..

Microsoft Excel - Automatic numbering feature:

View Full Version : Automatic numbering feature: VVV1299Is there a way to have a template (such as an expense report, policy order, etc.) in Excel automatically number the document by who pulls it up first off of the server? Thanks in advance. ~VR Anne TroyAutomatically number by who pulls it up first? What does that mean? We can make a worksheet have an autonumber if John opens it, then increase by one if Joe opens it, etc... Coupl'a methods: http://www.vbaexpress.com/kb/getarticle.php?kb_id=351 http://..

Microsoft Excel - How can I mirror pages in an excel document for printing?

View Full Version : How can I mirror pages in an excel document for printing? mirroringI have 300 pages in an excel document which need to be double sided and bound into a a publication. I need to mirror the pages so they have the same margin on both left and right depending where they fall in the document. Can anyone help please? MichaelHi File / Page Setup / Margins......set left and tight margins to equal amounts. You might also try ticking the centering check boxes. HTH Michael mirroring ..

Microsoft Excel - How do I sort a column of data and have each data row sort accordi

View Full Version : How do I sort a column of data and have each data row sort accordi OedalisI want to sort a column and have each row sort according to that column as opposed to the column of data sorting alone and no longer corresponding to the data row next to it. The effect will be that I sort my first column of project numbers and each project data row will sort with it so that all my project data is sorted by the project number in the first cell. How do I accomplish this? Ken RussellIf I understan..

Microsoft Excel - Hi Guys. First Time Poster

View Full Version : Hi Guys. First Time Poster noah799Hi guys. First time poster long time reader. Just wanted to say Hi ;) On a side note my Hubby is REAL excited about this new IPO stock GRDX. They just started trading this one like 2 days ago. It's already almost doubled in just 2 days! My Husband is really excited about this stock. Say's it could make us some really BIG money. You guys should check it out. Just spreading the wealth ;) Go to http://www.otcbb.com and check symbol : GRDX you..

Microsoft Excel - If

View Full Version : If KevinI would like to make a page that recognizes a column that does not have a value in it. e.g. If 1a,b,c have no value 1d,e,f have a value 2a,b,c have a value and 2d,e,f have no value ect ect for about 20 rows. At the bottom of this I would like to keep a running total of all the empty boxes for every column. CLR=COUNTIF(A1:A20, ) This will count the empty cells in the range A1:A20 Vaya con Dios, Chuck, CABGx3 Kevin <Kevin@discussions.microsoft.com> w..

Microsoft Excel - pivot table limitation

View Full Version : pivot table limitation tchangmianHi, when i tried to generate an excel report. It gives me an error message Microsoft Excel: Microsoft Excel cannot make this change because there are too many row or column items. Drag at least one row or column field off the PivotTable report, or to the page position. Alternatively, right-click a field, and then click Hide or Hide Levels on the shortcut menu. Error in generating report. Please contact Administrator! What can i do with this..

Microsoft Excel - Font displays as squares rather than as characters...

View Full Version : Font displays as squares rather than as characters... JimInVAI can use Estrangelo Edessa in Word but not in Excel. Is there something I can do so that it works correctly in Excel? vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - Are there any templates to manage all students in a school, inclu.

View Full Version : Are there any templates to manage all students in a school, inclu. Fieldmouse vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - How to set up past due notices based on dates

View Full Version : How to set up past due notices based on dates LinnHow can I use a date function to setup a past notice when a date expires? A second question is how to set up a date function that gives a notice prior to the expiration date say 30 days prior? Anne Troy Gives a notice Please define what you mean by give a notice . **************** Anne Troy www.piersontech.com www.mrexcel.com Linn <Linn@discussions.microsoft.com> wrote in message news:D7E1A9C2-15..

Microsoft Excel - How do I convert a list to an Excel file?

View Full Version : How do I convert a list to an Excel file? Pilot PersonI have a WORD file with 48 lines of comma delimited data in the form: xxx,xxxxxxx,xxxxxx,x,x,xxx,xxxxxxxxx xx,xxx,xxx,xxx,xxx,xx,x I would like to convert the WORD list to EXCEL. When I attempt to open the WORD file in EXCEL, I thought a conversion window would appear....what I actually get is incorrect format Anne TroySelect the data, including the paragraph returns at the end of each line. (Turn your show/hide button..

Microsoft Excel - How do I display data from multiple worksheets in a summary sheet.

View Full Version : How do I display data from multiple worksheets in a summary sheet. stardustI have over 100 worksheets which are layout exactly the same. I want to create a summary sheet to display only certain information from all of them...like name, sales total, numbers of calls. I don't want to consolidate or tally the information just display them. Does anyone know how to do this? Jerry W. LewisMany ways to skin this cat =Sheet1!C4 =Sheet1!C4 ... If the sheets will be always be accessible in an o..

Microsoft Excel - how do i create a find function for an excel drop list?

View Full Version : how do i create a find function for an excel drop list? RCI have a program that i have written in exel that has become to large to manage. i need to be able to select my drop down list and be able to enter the first character of the item that i trying to find and have excel return for me closest match Anne TroyYou cannot. Sorry if this is bad news. You may want to consider moving over to Access...which does this automatically. **************** Anne Troy www.piersontech.com www.mrexcel..

Microsoft Excel - How to open different excel books in different windows?

View Full Version : How to open different excel books in different windows? YogaI just want to know which options should be enabled to open different exel sheets in different windows by opening single Excel application KassieJust open them! If you want to see more than one at the same time, reduce the size of each window, so that you can fit them either side-by-side, or one in the upper half, and one in the lower half. I.e., move your mouse to one of the borders, click and drag to the middle of the scree..

Microsoft Excel - Pivot table, data from various spreadsheet

View Full Version : Pivot table, data from various spreadsheet DerrickI have data in 3 separate spreadsheets (in the exact format). I want to have a pivot table that consolidate data from these sheets. Is this possible? MaxTry Debra's page on: Excel -- Pivot Tables -- Multiple Consolidation Ranges at: http://www.contextures.com/xlPivot08.html -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <at>yahoo<dot>com ---- Derrick <anonymous@discussions.microsoft.com> wrot..

Microsoft Excel - no enough memory to run excel viewer

View Full Version : no enough memory to run excel viewer seanhow to this problem vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - vlookup in two condition

View Full Version : vlookup in two condition val via OfficeKB.comHi, I have problem with using vlookup function fo the following case A B C 1 from to amount 2 jkt cape 1 3 jkt bdg 2 4 smg bdg 3 5 smg bdg 7 What is the formula if I want tolook up from jkt to bdg the result would be 2. Thanks -- Message posted via http://www.officekb.com Arvi LaanemetsHi =SUMPRODUCT(--($A$1:$A$1000= jkt ),--($B$1:$B$1000= bgd ),$C$1:$A$1000) -- When sending mail, use address arvil<at>tarkon.ee..

Microsoft Excel - I can not open my excel program,

View Full Version : I can not open my excel program, myriamI cannot open excel on my pc (XP) ...it is given the message that excel cannot find the file SKU011.CAB...i do not know what to download to arrange it....and on which page... Jim RechI'd reinstall Office/Excel. You might start with an uninstall. The file you're missing, btw, is (on my system) under c:\msocache. You might have deleted it mistakenly. -- Jim Rech Excel MVP myriam <myriam@discussions.microsoft.com> wrote in message..

Microsoft Excel - Readonly files

View Full Version : Readonly files ncHi How do I change all the files in a specified directory from a read only file to a non read only file usig macros. The macro should take in account that some files might not be read only. Thanks. Bob PhillipsSub ProcessFiles() Dim sFolder As String Dim Folder As Object Dim file As Object Dim Files As Object Set FSO = CreateObject( Scripting.FileSystemObject ) sFolder = C:\MyTest If sFolder <> Then Set Folder = FSO.GetFolder(s..

Microsoft Excel - Alert If Cell Value Is a Certain Value

View Full Version : Alert If Cell Value Is a Certain Value SteveLincolnHi Everyone How can U set up an alert to format cells with a pink fill colour if say A1 ever has the value eg 1000 in it? This could be used to Highlight the Name and address of someone (with a pink fill) that has ever owed £1000 even if they now do not. In other words the name and address of that person stays in pink fill if ever they have owed £1000. Thanks for any help. Steve Lincoln UK turn cells get Excel record that a cell has..

Microsoft Excel - if A1=Null and B1=A1 why is result in B1=0 ??

View Full Version : if A1=Null and B1=A1 why is result in B1=0 ?? WGeorgif A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Bob PhillipsUse =IF(A1= , ,A1) -- HTH RP (remove nothere from the email address if mailing direct) WGeorg <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B..

Microsoft Excel - How do I get a formula to use data from a fixed location(NOT vicev

View Full Version : How do I get a formula to use data from a fixed location(NOT vicev Pete wI want a formula to use data from a fixed location. For example I am using a table B2 to I4. During the week new value are added to the right of this table and then the table is shifted to the left. So a value in I4 becomes G4 etc. I want the formula to continue using I4 despite the fact it has been shifted but the formula automatically adjusts to read G4. IN SHORT Is there anyway to fix the formula to read a fix..

Microsoft Excel - Closing Excel

View Full Version : Closing Excel ChadIs there a way to disable pressing the x at the top right corner of the screen from closing an Excel document. Here's my dilema: I have created workbooks that I allow beginner-level users to use to access data. In these books, there are buttons that perform all of the actions, including saving and closing the documents. However, I have trouble making the users press the buttons instead of pressing close or x in Excel, closing the document where..

Microsoft Excel - formula for a seating chart

View Full Version : formula for a seating chart M ZahediHello. I have a class that has 4 rows of seating. 8 in rows 1-3 and 5 in row 4 for a total of 29. I change the seating chart 8 times a year. My objective is that a student only sit once in row 1 and row 4, and preferably all get to sit in these rows. Is there any way that I can set a formula to do this? vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - How can I recover deleted Excel workbook?

View Full Version : How can I recover deleted Excel workbook? JayHow can I recover deleted Excel workbook? hi, unless it't in the recycle bin, you don't. you get out the backup copy. if you have no backup, sorry. it's gone. >-----Original Message----- >How can I recover deleted Excel workbook? >. > snax500There are third party applications that can retrieve deleted files. I just used one called...http://www.pcinspector.de/. There are many others. Do a Google search. anonymous@discussions.mic..

Microsoft Excel - why does currency format change to number format?

View Full Version : why does currency format change to number format? Cassieevery time I format a column in the currency format and save, when I go back to that column, the formatting has changed to number format. How can I get this format to stay? Dave PetersonAre you actually closing and reopening your file? If you are, how are you saving your workbook--as a .xls ( Microsoft Office Excel Workbook (*.xls) ? It kind of sounds like you're saving as a .csv (comma separated values) file. This type..

Microsoft Excel - Need help with linking sheets.

View Full Version : Need help with linking sheets. wwHi all, I have a problem I was hoping someone could help me with. What I'm doing is using a =sumif() to pull some numbers from different files. On the files that I'm pulling the information from I have two ranges named Code and Current and we'll call the actual file names Table.xls, Table1.xls, Table2.xls, etc. On the file that I'm trying to pull the information into I have =sumif('Table.xls'!Code,A5,'Table.xls'!Current) in C5. This pulls the informati..

Microsoft Excel - histograms not counting blank cells

View Full Version : histograms not counting blank cells GJR3599how do i get a histogram to accept an input field that has some non-data fields in it? the fields are mostly blank cells, but the histogram doesnt like them and wont produce a chart/graph. if that doesnt work, is there another way to count how many scores fall within specific ranges and then graph those counts? vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - Merge and Center button

View Full Version : Merge and Center button JTThe Merger and Center button appears to be turned off . No matter which cell or range of cells I select, I am unable to use this button. Any suggestions would be great. Thanks...... Dave PetersonAny chance your worksheet is protected? If yes, then unprotect it. Any chance your workbook is shared? There are some features that are unavailable in shared workbooks. One of them is merging/unmerging cells. Take a look at Excel's Help for Features t..

Microsoft Excel - How do I increase the limit on the number of import rows in Excel

View Full Version : How do I increase the limit on the number of import rows in Excel AleckHi, Does anyone know how to increase the number of rows that Excel can import or read? My version is XP pro. Now the limit is about 65000 rows. I have data sheet that is much more than that. Thank you. Aleck hi, 65536 is the max. hard coded into excel. in xl help type specifications for more details >-----Original Message----- >Hi, > >Does anyone know how to increase the number of rows that Excel can im..

Microsoft Excel - Finding Words In Excel Spreadsheets & Word Documents

View Full Version : Finding Words In Excel Spreadsheets & Word Documents rbonner79416I have a number of excel spreadsheets that list chemicals. If I wanted to compare one of these spreadsheets against a second document (Word) that has chemicals, is there anyway to find all words that match both documents without having to type each word of the spreadsheet in the find feature comparing it with the word document? vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - comments in excel cells

View Full Version : comments in excel cells FDickensWhy is insert comment not available in one of my worksheets ? I can insert into other sheets. The cell and sheet are not protected. Dave PetersonDo you have multiple worksheets grouped? Look in the title bar for [group]. If you do, rightclick on a worksheet tab and choose Ungroup Sheets. FDickens wrote: > > Why is insert comment not available in one of my worksheets ? > > I can insert into other sheets. > > The..

Microsoft Excel - spell check for protected worksheet for Excel 97

View Full Version : spell check for protected worksheet for Excel 97 JessicaSo this code works for Excel 2003: Sub Spell_Check() ActiveSheet.Unprotect Password:= justme Cells.CheckSpelling SpellLang:=1033 ActiveSheet.Protect Password:= justme , DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub However when I was trying to get it to work for someone, they were using an older version of Excel (I believe it was 97), so how can I get the code to work to get the spell check..

Microsoft Excel - How do I compare a vertical column of numbers to another verical .

View Full Version : How do I compare a vertical column of numbers to another verical . warehouse guyI have an Excel workbook that contains 2 verical columns of numerical part numbers that I need to compare for matches and no matches so I can synchronize one set of data to another. Gordonwarehouse guy wrote:

Microsoft Excel - Force Page Break

View Full Version : Force Page Break EugeniaNot use to EXCEL, and need to force a Page Break as needed. Find that there is a limitation. How do I over ride the limitiation to insert a Page Break? KassieHave you tried View

Microsoft Excel - Scan and edit document in excel 2003.

View Full Version : Scan and edit document in excel 2003. johmocI would like to scan a document (has several charts and text within the chart) and edit this document in MS Excel 2003. Is this possible using MS Office Document Imaging to scan and then export to Excel to edit? If not, is there a way I can do this through Excel itself using my HP All-in-One to scan and then immediately edit? I have Readiris Pro 7.5 that came with the HP All-in-One driver and I can scan and edit documents in MS Word, but not..

Microsoft Excel - merge unformatted date with text

View Full Version : merge unformatted date with text ArtMy original date column was formatted 02/01/71. I created a new column and created a custom date format; 020171. I would like to take this column and merge it together with the last 3 digits of a column where a ss# resides. This column is a text column. For example, the new column should look like 020171008. Can anyone help? I tried this based on your example and used the =Text (originaldate,mmddyy) function and used a =Right(ss#,3) to create a new..

Microsoft Excel - copying worksheet tabs

View Full Version : copying worksheet tabs LynnHi all, I have an excel worksheet which I'm trying to copy. Meaning I right click on the tab and select the tab name and check the create a copy box. But it does not create a copy. screen flashes once and then disappears. Also, I made sure that the file was not protected. Please help. Thank You. Dave PetersonBe careful. Maybe you're not specifying the correct workbook to copy to--or maybe you're copying to a new workbook and just didn't notice the difference..

Microsoft Excel - EXCEL CERTIFICATIION

View Full Version : EXCEL CERTIFICATIION Bruce HensonIs it possible to obtain certification from Microsoft to demonstrate my level of proficiency in Microsoft Office applications? jasonjmorin@OPPOSITEOFCOLDmail.comSee: http://www.microsoft.com/learning/mcp/officespecialist/req uirements.asp HTH Jason Atlanta, GA >-----Original Message----- >Is it possible to obtain certification from Microsoft to demonstrate my level >of proficiency in Microsoft Office applications? >. > vBulletin v3...

Microsoft Excel - Excel Columns & Rows Flipped

View Full Version : Excel Columns & Rows Flipped Linda in HoustonUpon opening Excel, my worksheet columns & rows are flipped? Everything is on the right side of the screen - not on the left side of the screen. Example: G F E D C B A & #s run down the left side. This just started? How can I reset to the correct position A B C D E F G? THANKS, Linda in Houston Dave PetersonTry Tools

Microsoft Excel - How do I set the Enter key to go to a specific cell?

View Full Version : How do I set the Enter key to go to a specific cell? JohnI am entering data on a worksheet from left to right, using the tab. At the end when I hit Enter, I want the very first cell on the next row to be where I start. I have looked at Options-General, but it only will determine Up, Down, Left, Right . I would like to set it to where the Home begins on the first cell of the next row. Dave PetersonIf you select your input range first (say A12:D99), and change tools

Microsoft Excel - in excel can one repeat the text from a the last used column cell.

View Full Version : in excel can one repeat the text from a the last used column cell. MillardIn excel 2003 can a formula be constructed that will copy a cell into a new spreadsheet if there is text in the original spreadsheet cell but will also repeat the last used cell within a column if the original spreadsheet is empty? hi, formulas return values. they cannot perform actions like copy and pasted. sorry this could possible be done with a macro. >-----Original Message----- >In excel 2003 can a fo..

Microsoft Excel - Gathering open-ended question text into one sheet

View Full Version : Gathering open-ended question text into one sheet DaveCVCI have an Excel file with data from 60 or so respondents to a questionaire. I have all my raw data on one worksheet. On another worksheet I have totals for the various questions broken out in various ways. I would like to create another worksheet that lists the answers to an open-ended question at the end of the questionaire. Less than half of the people responding wrote comments so I don't want to include blanks. I'd like to ha..

Microsoft Excel - Macro

View Full Version : Macro titotoI need to create a macro that will do the following: Sheet1 containg data (range A:J) In sheet2 I need macro to use ROW 1 Column A [ = VICE ] Column B [ = I ] Column C [ blank ] Column D [ =IF(Sheet1!I1= B , B1 ,IF(AND(Sheet1!I1= C ,Sheet1!J2= C* ), C1 , NIL )) ] Column E [ a given date ] Column F [ =Sheet1!F1 ] Column G [ = CONTROL &Sheet1!E1 ] Column H [ =0 ] ROW 2 Column A..

Microsoft Excel - How can I change default keyboard shortcuts?

View Full Version : How can I change default keyboard shortcuts? cnielsenWhen I hit the enter key, it stays in the same cell. How can I change it so enter takes me down a row? Chip PearsonGo to the Tools menu, then Options, then the Edit tab. There, put a check next to Move selection after Enter and set the direction to down. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com cnielsen <cnielsen@discussions.microsoft.co..

Microsoft Excel - Easy Cash Flow Spreadsheet

View Full Version : Easy Cash Flow Spreadsheet joolsI have been searching all morning for a FREE and BASIC software spreadsheet for my e-bay, and my booth in an antique mall. So far, no luck. I had a wonderful one last year, but in changing computers it did not transfer over. Any help would be appreciated..Karen Gordonjools wrote:

Microsoft Excel - must press enter 2x to move cell focus

View Full Version : must press enter 2x to move cell focus LynnI now have to hit enter or tab twice after editing cell data to get cursor to move to next cell, but only on existing workbooks. New workbooks stil moves after first enter. Move after entry box is checked, tried unchecking, reopen excel, recheck, reopen excel, not fixing it. Excel 2003 on Win XP Pro, AV files current and running. Don't think I changed anything lately. Have compared settings, can't find any differences. Searched KB and newgrou..

Microsoft Excel - how to do global replace in hyperlinks for entire excel document?

View Full Version : how to do global replace in hyperlinks for entire excel document? AKennedyhad file restored and hyperlinks point to resotre location instead of original location - is there a way to make the change to redirect to original location for all links? JulieDHi AFAIK you'll need to use a macro for this, if you'ld like to type out a couple of examples of the new hyperlinnk references and the old hyperlink references we might be able to come up with something. Cheers JulieD AKennedy&quot..

Microsoft Excel - More complex Frequency function

View Full Version : More complex Frequency function Pedro VazHello, Can you please help me on this one? I have this table to wicj I apply the frequency function (=FREQUENCY(B7:B13,D7:D10) ), which is ok. Age # of People Bin Frequency 33 6 10 0 34 25 15 1 14 9 20 2 17 6 30 1 29 16 3 20 4 35 1 Now, I want to calculate the frequency based on the number if people I have in that age. For instance, the frequency of 15 should be 9 and not 1 (there are 9 persons with 14 yrs of age); the freq of 20 should be 10 a..

Microsoft Excel - data validation list from two columns

View Full Version : data validation list from two columns otinkyI have a list of every county, parish and borough in the US in a worksheet, like this: A B 1 HI Kauai County 2 HI Maui County 3 ID Ada County 4 ID Adams County 5 KY Adair County 6 KY Harlan County I would like to create a data validation list so that a user can quickly lookup county level data--accessed by an Index and Match formula. I hope to accomplish two things: 1) not force the user to scroll through all 3,000 county names 2) keep sepe..

Microsoft Excel - Excel: How do I set up a cell to click it to link a database?

View Full Version : Excel: How do I set up a cell to click it to link a database? jp@nesI keep track of pricing info in a spreadsheet. I would like to click on a cell to be directed to somewhere that I can keep a (purchsase) history attached to that cell. Any ideas how I might be able to do that? in the following, the counter - which determines the row the historical data is entered - is maintained in cell (1,30). The data you wish to periodically record is in cell (10,1). when you run this m..

Microsoft Excel - Name and number of sheets

View Full Version : Name and number of sheets MBCould someone please post the code that will count the number of sheets in a workbook and get each sheet name. I thought I could use the for statement after I got the number of sheets, but can't seem to get that number. Thank you Dave PetersonMaybe something like: Option Explicit Sub testme() Dim wks As Worksheet MsgBox Sheets.Count MsgBox Worksheets.Count For Each wks In ActiveWorkbook.Worksheets MsgBox wks.Name Next wks End Sub Worksheets.count may not eq..

Microsoft Excel - Emailing worksheets within a workbook as ATTACHMENTS

View Full Version : Emailing worksheets within a workbook as ATTACHMENTS Catch 22I am trying to email each worksheet of a 150 worksheet workbook to a different person or maybe a group of worksheets to a person BUT I want to do it as an attachment NOT as in-line text. Is there any way to do this? GordonCatch 22 wrote:

Microsoft Excel - Re: Cannot edit query.

View Full Version : Re: Cannot edit query. dabramov@gmail.comFred wrote: > I use Microsoft Query via Excel to pull data into a > spreadsheet. Occasionally I edit the query by > selecting Edit Query from the External Data toolbox. > > The problem is that after accessing the data about five > times Microsoft Query will not open anymore. The data will > still refresh and the ODBC drivers are all working > properly, I just can't get to the SQL to edit it. Even > after r..

Microsoft Excel - Fill-in series (by minute)

View Full Version : Fill-in series (by minute) nemmeI have a start time and an end time for each record, but I want to create a series which would create a cell for each minute between the start and end time. vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - Excel: Instead of the figure I typed I get ########. Why?

View Full Version : Excel: Instead of the figure I typed I get ########. Why? NanaThereseExcel: Instead of the figure I typed I get ########. Why? Chip PearsonTypically you get this display when the column is not wide enough to display to number. Increase the column width and you should be all set. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com NanaTherese <NanaTherese@discussions.microsoft.com> wrote in message news:64990789-73BD-4D41..

Microsoft Excel - Save as

View Full Version : Save as JOHN SMITHHi, I need to create a function that will copy a sheet on my workbook to a new workbook how do i open a save as window? I am using win2000 and office 2000. Regards, Chip PearsonJohn, To create a worksheet with a copy of a single worksheet, use code like ThisWorkbook.Worksheets( Sheet1 ).Copy Then, do a Save As with the new filename: ActiveWorkbook.SaveAs filename:= whatever.xls -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Con..

Microsoft Excel - Automatic Calucalation Default

View Full Version : Automatic Calucalation Default BrandonI've read the previous threads regarding the default calculation setting returning to Manual I do use personal.xls for storing macros and suspcet this is related. The expert advice was to open Personal.xls to start the excel session and change the calculation setting to automatic. Unfortunately, personal.xls does not have a worsheet associated with it, therefore options is not available on the tools menu. Please advise. Dav..

Microsoft Excel - Will a Pivot Table Help with my problem?

View Full Version : Will a Pivot Table Help with my problem? HRDataFoolI am putting together a form that will be used throughout our organization. There are specific jobs linked to specific departments. How can a display a list of only those jobs associated with the department number that is keyed in? This is a big organization and managers are not going to want to scroll through every job we have just to find the ones in their department. Help is appreciated. -- Scott Debra DalgleishA pivot table is goo..

Microsoft Excel - Force Cell Movement

View Full Version : Force Cell Movement JasmineIs there a way in Excel to force the user to move from a specific cell to another specific cell? The cells may not be right next to each other. For example, Worksheet opens with B5 being the active cell, when they hit enter/tab it goes to C7. Any help would be appreciated! Thanks! Gord DibbenJasmine To TAB to desired cells....... 1. Unlock the cells you want to TAB to then protect the worksheet. If your unlocked cells are in a left to right, top to bottom se..

Microsoft Excel - Odd problem - loosing cell information

View Full Version : Odd problem - loosing cell information TedJust opened a financial workbook that has several several columns. The data in one column is missing for the past five days. This column is updated daily and the data was current as of yesterday but now it is missing. I would understand, if only the last days information was missing (could have forgot to save), but not the last five days. Has anyone else seen this problem or have an idea what happened? Thanks Ted Dave PetersonJust a guess. You..

Microsoft Excel - can Excel randomly sort numbers

View Full Version : can Excel randomly sort numbers gasmithI would like to sort numbers in a random sequence. How can I do that in Excel or any office application? Fredrik Wahlgren gasmith <gasmith@discussions.microsoft.com> wrote in message news:9A905226-47EA-4EE3-BADE-E22A58A1C15E@microsoft.com... > I would like to sort numbers in a random sequence. How can I do that in > Excel or any office application? Yes. Assuming that your numbers are in column A, insert =Random() in column..

Microsoft Excel - How do you automatically put spaces between rows in a lengthly w.

View Full Version : How do you automatically put spaces between rows in a lengthly w. marvaodI have a lengthly work sheet and I want to put spaces between every row--how do I do that? Gord DibbenAutomatically usually involves VBA coding. Do you really need the inserted rows? Perhaps you could just change the height of the rows to look double-spaced? Here is a macro to do the row inserting. Sub InsertALTrows() 'David McRitchie, misc 2001-06-30 Application.ScreenUpdating = False Application.Calculation = x..

Microsoft Excel - VLOOKUP

View Full Version : VLOOKUP drWould like to transfer result data from one workwheet to another...thx/dr vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - count if

View Full Version : count if Kevin=COUNTIF(B4:B17, X ) this works great with X, but I would like to include L and V in this formula. How would I do this??? Thanks Dave PetersonYou could do the brute force version: =COUNTIF(B4:B17, X )+COUNTIF(B4:B17, L )+COUNTIF(B4:B17, V ) or you could use something like: =SUM(COUNTIF(B4:B17,{ X , L , V })) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel..

Microsoft Excel - VLOOKUP

View Full Version : VLOOKUP drWould like to post data from one worksheet to another. Chip PearsonCould you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com dr <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. vBulletin v3.0.3, Copyright ©2000-2005, Jelsoft Enterprises Ltd...

Microsoft Excel - How can I convert an excell file from windows 98 on to works spr.

View Full Version : How can I convert an excell file from windows 98 on to works spr. PumaI have changed my computer and need to know how I can transfer some of my files Office 97 excell, to Works XP spread sheet Debra DalgleishYou should be able to open your Excel files in Works, but not all of the features will be available. Puma wrote: > I have changed my computer and need to know how I can transfer some of my > files Office 97 excell, to Works XP spread sheet -- Debra Dalgleish Excel FAQ, Tips..

Microsoft Excel - Read Only when download from Outlook

View Full Version : Read Only when download from Outlook Consulting JoanI have a file folder with a master excel file with links to files I receive from various stores. Each week I receive an updated store file via outlook and save the file in the folder with a standard file name. Every once in a while, I get a message that the file I am trying to save over is read-only. How does this happen and how can I prevent it from happening again. Anne TroyJoan: This is likely a feature that is set in the SENDER's..

Microsoft Excel - Can you reformat order of items in Pivot Tables after refresh?

View Full Version : Can you reformat order of items in Pivot Tables after refresh? marmur1When I set up pivot tables for time reporting entries at work, there are often new projects that come in weekly, and every time that I refresh the report, the new items do not show up in alphabetical order in the drop down list (in the Page Field). Do any of you know if it's possible to reformat the order of these items without having to rebuild the report? I'm dealing with about 200 projects, and it becomes very di..

Microsoft Excel - Data Validation Not Working

View Full Version : Data Validation Not Working BrettIs there a problem with Data Validation using Defined Names? I created a Defined Name and then used it as a Validation Criteria Formula, but it didn't work. Then I tried troubleshooting the problem by entering the Defined Name's formula instead, and it worked? Debra DalgleishIn the Source box, type an equal sign, then the range name, e.g.: =MonthList There are instructions and examples here: http://www.contextures.com/xlDataVal01.html Brett wrote: >..

Microsoft Excel - How do I automatically alphabetize multiple worksheets in a singl.

View Full Version : How do I automatically alphabetize multiple worksheets in a singl. TSI have about 35 named worksheets in a workbook, and I would like to organize the sheets alphabetically. Is there a way to do this without moving each sheet individually by hand? I'm using Excel 2002. Thanks! Dave PetersonYou can use a macro: Chip Pearson's: http://www.cpearson.com/excel/sortws.htm David McRitchie's: http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets If you're new to macros, you may want..