douglas m smith, excel vba, correct syntax: Hi again, References in the Range function are constructed like text strings and Range then converts it to an actual reference, not unlike the spreadsheet function INDIRECT. The reference you want would be: Range( A2:F & LastRow).whatever A variation...

Excel: Row Selection, row selection, curser

row selection, curser, excel 2002: Well, ummmm, OK. So, what s your question? I have to assume that you are aking what is the code to do that and therefore my answer is based on that assumption. If ActiveCell.Column = 3 And _ ActiveCell.Value = And _ ActiveCell.Row 6 And ActiveCell.Row...

Excel: Rows & Columns re-size themselves in Excel 97, financial worksheets, excel 97

financial worksheets, excel 97: Joe I haven t had or heard of others having this problem. I can imagine what is causing the resizing. I m assuming that you have saved the worksheets in the resized format. If that is the case they should not be resizing themselves. You might try...

Excel: Rows & Columns resizing themselves in Excel 97 worksheets, microsoft knowledge base, financial worksheets

microsoft knowledge base, financial worksheets, dummy data: I haven t come across this and can find nothing to explain it in the Microsoft Knowledge base - I m assuming that the sheet doesn t have macros (a macro could resize things) and that the sheet has actually been saved between whiles (obviously not saving it...

Excel: Rows to Colums, cell d1, 3s

cell d1, 3s, 2s: I believe I have a solution that will work perfectly (and it is fairly simply despite there being 10 steps)...as long as all of your data is in triplets as it appears above. Assumption: The above data is in column A and columns B through D are currently...

Excel: Rules for Purchase Orders, value case, c1 40

value case, c1 40, case range: Hi Matt, I am know some VBA but not an expert in VBA. But you will need a Select Case statement like the following. Simply copy this and open your workbook then press Alt+F11 to open the Visual Basic Editor and paste it in the blank Right-side pane, then...

Excel: Run Excel macro from command line?, excel macros, access macros

excel macros, access macros, command line options: Scott I don t know of any excel command line options that will do what you need, but I m definately not a macro expert. I suggest you reask the question and check the resumes for someone who is expert in MACRO/VBA. Sorry I couldn t be of more specific...

Excel: Run Excel macro from a command line?, command line switches, microsoft excel 2000

command line switches, microsoft excel 2000, douglas m smith: Hi Scott, I ve never fiddled with Excel command line switches before but I did find some information in Microsoft s KnowledgeBase that could be useful. Here s the link, and I ve copied the text of it below: http://support.microsoft.com/default.aspx?scid=kb;en-us;211481&Product=xlw2K...

Excel: Run Macro Based on Month, sheet tab, case 2

sheet tab, case 2, macros: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Activate() Select case month(date) Case 1: RunJan Case 2: RunFeb Case 3: RunMar etc -- you get what s going on here. End Select End Sub...

Excel: Run Macro when Document is Opened, seconds in a day, operator input

seconds in a day, operator input, time update: right-click the excel LOGO near the File Menu, select View Code, paste this in: Private Sub Workbook_Open() your code here End Sub For a macro to run every second and yet gove the user control, use something like this: Sub Doagain() your...

Excel: Run a Macro when a particular sheet is selected (hide & unhide toolbar), true application, drop down list

true application, drop down list, cells: You can use the activate/deactivate event of the worksheet. To do so, right click on the sheet name, and select View code on the emergent menu. This will open the VBA editor on the sheet that you want, and you can use the two drop down list above the main...

Excel: Run a macro based on cell value, target value, target cell

target value, target cell, target address: Without knowing all of tehe details, you will have to do something like this. Capture the row number and column number of the target cell. Then compare that to what you need to compare it to see if the target falls within the range of cells you need to do...

Excel: Run a macro when cells are recomputed., message box, macros

message box, macros, right direction: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros...

Excel: Run macro on click event, button icon, hasan

button icon, hasan, craig: Hi Mahdi, If I am understanding correctly, you need to assign your button to a macro. Correct? View/Toolbars/Forms, click the button icon and it will ask you which macro you want to assign. If you already have the button, you should be able to just right-click...

Excel: Run Time Error 1004 - Delete Method, run time error, memory issue

run time error, memory issue, selection statements: Using a specific range as an example: Range( D6 ).Resize(Range( D6 ).End(xlDown).Row - Range( D6 ).Row + 1, 1).Delete Shift:=xlToLeft though using a specific range is not the ideal way - this is just one example Put the cursor anyhwhere and run...

Excel: Run-time version, case thanks, microsoft website

case thanks, microsoft website, time version: There are two ways that I m aware of... 1. If you save the worksheet as an HTML document, anyone with a web browser can view it. 2. The people without Excel would have to D/L the Excel file viewer available at no cost from the Microsoft website. ...

Excel: Running Excel 2000, registry directory, desktop shortcut

registry directory, desktop shortcut, microsoft support: Hi Randy, Sorry about that, this must be really frustrating for you! Unfortunately, there could be any number of reasons why Excel is not opening. Hopefully one of these resolutions will work for you.... Corrupted Shortcut to the Excel Program If...

Excel: Running Macro upon cell change and running macro for different rows?, order management system, sheet1

order management system, sheet1, target: Hi Sabrina How are you? I am sorry but your question is too long. But there it goes some hints: A. To do delete the row where you type a y 1. If your data is in Sheet1 and the completed column is C 2. Tools - macro - Visual Basic Editor 3. View - project...

Excel: Running Macro upon cell change and running macro for different rows?, order management system, target cells

order management system, target cells, target range: In that case, change the code I gave you to: Private Sub Worksheet_Change(ByVal Target As Range) Static bWorking As Boolean Dim oCell As Range If Intersect(Target, Range( C:C )) Is Nothing Or bWorking Then Exit Sub Set oCell = Target.Cells(1,...

Excel: Running Macro upon cell change and running macro for different rows?, order management system, good answer

order management system, good answer, sabrina: Sabrina I m not gonna give you the answer you want, but some hope. I am not a macro expert so I can t give you the details of how to write the macro, however I can tell you that what you want to do is very do-able. I suggest you reask the question of...

Excel: Running Macro on value change, target function, target address

target function, target address, critical reference: Jason Did the code work for you ? I go on holiday tomorrow so a reply would be great. Thanks Des Hi Jason The sub routine given below needs to go into the code of the sheet in which you are doing the required work. ie right click the name tab...

Excel: Running a macro involving openning a read only workbook, test xls, open test

test xls, open test, open filename: I don t know what you mean by the two different methods, I usually use only the Open method for the Workbooks collection, with the amount of parameters that fit every case. For example, a simple macro to open a file in read only mode, with the read-only password...

Excel: Running a macro step-by-step to debug, pace line, f8 key

pace line, f8 key, excel macro: Do you mean perhaps have it run at a certain speed or pace so you can watch it at that slower pace to see what each line does line by line? If this is what you mean, then no, to my knowledge, unless you write some code to make the code run at a slower...

Excel: Running a macro, target value, excel objects

target value, excel objects, microsoft excel: put this in the worksheet s macro module: (press ALT + F11 to bring up VBA editor.. then double click on the sheet name in the VBA editor under Microsoft Excel Objects) Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Value End Sub ...

Excel: Running Private Sub_ macros, boston area, dropdowns

boston area, dropdowns, vba: Hello Andy, I don t know of any particular class, but I know in my area, the county offers adult education services at the local high schools. There are classes on Excel there and usually advanced or intermediate classes will include VBA or their might even...

Excel: Running Total, cell b1, circular reference

cell b1, circular reference, aidan: I m possibly missing some information, but I certainly agree that I cannot make sense of the formula you ve been given. The EASIEST way to do a running total is to have the total one at the top of a column, either one column to the left or to the right of...

Excel: Running tally, cell d1, toolbar tools

cell d1, toolbar tools, small construction: James I m not sure what you re worksheet look like but you can do what you want with a macro that copies the result of adding and deleting inventory to a new balance forward and then clearing the contents of the add and delete cells. Assume the following...

Excel: radio buttons, questionare, radio buttons

questionare, radio buttons, radio button: Hi Ramesh Sorry - I do not understand radio button . Do you mean Toggle Button, Command Button or Option Button? However, the first suggestion would be to use Checkbox controls instead. Beyond that, I will need a lot more detail to diagnose self-disabling...

Excel: radio buttons, radio buttons, questionare

radio buttons, questionare, radio button: It depends what type of radio buttons you have used. If from the Control toolbox: - enter design mode (first button of control toolbox toolbar) - Select a set of two radio buttons - show the properties window - enter the same groupname for these two...

Excel: radio buttons, data validation, radio buttons

data validation, radio buttons, questionare: My strong, #1 suggestion is to change from the radio buttons to an in-cell dropdown created with Data Validation. Radio buttons are OK if they re really needed, but you need some understanding of vba and OLE objects. Setting up an in-cell dropdown with the...

Excel: random selection, random selections, allocation table

random selections, allocation table, random selection: Hi Rob, How are you? I am sorry but I have not understood your problem. To generate numbers between 1 and 26 just use the following function =INT(RAND()*(26-1)+1) or the function Randbetween (Previously, TOOLS-ADD INS - tick Analysis toolpack) . ...

Excel: range copy in vba, font format, strikethrough

font format, strikethrough, pastes: Not with the paste (although I would rarely use copy and paste in vba but that s by the by) - but if you record changing the things you want changed, you can copy that coding under your pastespecial instruction eg with range( A2 ).Font .Name...

Excel: range criteria- counting, b2, variation

b2, variation: Brian, Can you show me aan example of your data and the formula that you are using. You could maybe try =SUMPRODUCT(--(A2:A200= X ),--(B2:B200 -- 2006-01-01 )) or some variation of this, but when I see your data and your counting criteria, I can...

Excel: range/criteria formula question, array formula, array formulas

array formula, array formulas, east aurora: Hi Joe, Yes, this is possible and it will open a whole new level of power for you. But first, a little history. Originally, array formulas were needed to what SUMIF and COUNTIF do. Users wanted something simpler for conditional summing and counting....

Excel: Have a range of figures between..., douglas m smith, brainbench

douglas m smith, brainbench, excel help: Hi Jo, Excel has a number of functions to round numbers. ROUNDDOWN should do exactly what you want without involving IFs and ORs. It has the same syntax as ROUND. Other rounding functions include ROUNDUP, CEILING, FLOOR and MROUND, which provide neat...

Excel: range names, capitalization and punctuation, proper capitalization

capitalization and punctuation, proper capitalization, range names: copy the sheet by RIGHT clicking on the sheet s tab, Choose Move or Copy, click Create a copy and choose the preferred book in the To book dropdown, everything gets copied this way except cells containing more than 255 or so characters (depending on version),...

Excel: range names in macros, range names, cell reference

range names, cell reference, relative references: Chris, If I understand your question correctly, yes it is possible to dynamically name and rename a range in Excel using code. You have to make use of relative references in VBA. For example, in the code below the first Range command line selects the...

Excel: range names, range names, blank worksheet

range names, blank worksheet, cell a1: I would normally copy worksheets (right click on the sheet, move or copy, create copy in new workbook) or use File SaveAs to get a duplicate of the entire workbook - both of these would bring the names across with them - if these are still options, the quickest...

Excel: Can the range specified in..., target area, column c

target area, column c, xls: I appologize for the delay; I normally like to respond with an answer on the same day, but I was out of town over the weekend :). I would do this by using the INDIRECT formula. I have uploaded an example to the below website: http://home.swbell.net/nate-sus/excel/indirect.xls...

Excel: get range value for a cell.value, getfolder, code option

getfolder, code option, type microsoft: set MyRange = Range(Range( C8 ).Value) code to process MyRange set MyRange = Range(Range( C9 ).Value) C8 should contain a string that is a singe cell address such as B9 or a range, B9:B30 or a non-contiguous range B9:B30,D9:D30,M2 so it would be...

Excel: ranges in if then statements, b2

b2: A2 is the number of days B2 is the value you want to multiply the 0.2, 0.4, etc by Here is a sample formula: =IF(AND(A2 =1,A2 =29),B2*0.2,IF(AND(A2 =30,A2 =59),B2*0.4,IF(AND(A2 =60,A2 =89),B2*0.8,IF(AND(A2 =90,A2 =120),B2)))) I hope that helps, ...

Excel: if, then and ranges, gross wages, tax brackets

gross wages, tax brackets, gross income: Hi Erica, You ve come the right person because I used to write spreadsheet-based income tax softare. Please email me I ll send you a file this evening (US time) with this kind of calculation set up for you. It will involve tables and VLOOKUP. Doug...

Excel: ranking, I think, number 8, number 6

number 8, number 6, a5: OK let me break the formula down. A1:A5 A1 would be the beginning cell of your data & A5 would be the ending cell. You will need to change these to fit your data. ,8 8 is the value you are counting. If there are 50 8 s the result will be 50. ...

Excel: rate calculation, number solution, problem context

number solution, problem context, rate of return: Hi Brian How are you? This is bit weird to explain remotely as the reason derives from the mathematics. We want a solution that is a real number solution, right? So =Rate(5;;1000;-5000;3%) is equivalent to 1000*(1+i)=5000 i= (5000/1000)^ (1/5) -1...

Excel: rate of return, fv, rate of return

fv, rate of return, 12 months: Here s an example of how RATE() is used. Say you borrow $1000, and the terms of the loan are that for 12 months, at the end of each month you ll pay $100. What is the interest rate? =RATE(12,100,-1000) If this doesn t help, then make up a very simple...

Excel: re: Creating a conditional pull down-menu, cell b1, data validation

cell b1, data validation, mfg 2: Alex You can greatly simplify what you are trying to do by using the data validation feature (toolbar/data/validation) creating an individual list for each manufacturer (I would do this by sorting your list by manufacturer, and attaching the list via...

Excel: re: decimal places, int sum, decimal places

int sum, decimal places, target: Hi Imtiaz, In column J the formula would be. =IF(OR(I2= , I2=0), ,H2/IF((SUM(I2+L2+P2)-INT(SUM(I2+L2+P2))) 0.5,INT(SUM(I2+L2+P2)),INT(SUM(I2+L2+P2))+1)) Hopefully this formula will work for you. The INT function will return just the integer...

Excel: re: Excel links, real time data, dde links

real time data, dde links, excel workbook: M.Lock I believe the best option is to group the 2 sheets involved before you delete a row. In essence, you will delete the row on both sheets, thus preventing the reference error. The other option is to refill the column with the link formulas each...

Excel: re: Excel Macro/Addin Tool, keyboard shortcut, f1 key

keyboard shortcut, f1 key, excel macro: Thanks for the kind words- I m self-taught in Excel, using the F1 key, newsgroups, this service and the printed manuals to help me learn. I don t think your problem would be best served with a lot of buttons (65000+ buttons is a LOT!) Would the macro be...

Excel: re: Excel VBA, type mismatch, learning excel

type mismatch, learning excel, time cell: I think your code has a couple of problems. 1. There is one loop too many (the for I = .Row.... is not needed) 2. Cells may SHOW time, but the date part may be present, so it is best to check against the fractional part of the cell contents. 3. I think...

Excel: re: Excel VBA, excel vba, overflow error

excel vba, overflow error, concise code: The overflow error occurs because the number generated for the ID is greater than the the Long variable s limits. You can either change the multiplier s to a smaller number or change the variables to Doubles and format the result to no decimals. Option...

Excel: re: excel if statement or conditional formatting answer, vlookup, inventory sheet

vlookup, inventory sheet, cell a2: As written, it looks in a workbook named Book1.xls at a sheet in that book named Sheet1 It searches A2:D1000 and returns column C for the matching row. Since you didn t tell my the sheets and files you are trying to use, but I will pick some an...

Excel: re: help with computation in excel, absolute references, kindergarten teacher

absolute references, kindergarten teacher, black arrow: The #VALUE! error in this case can be caused because one of the factors in the formula is not a number. When you drag this formula in a column, the row numbers will increase, so the next formula will be: =SUM(C8/C4)*D4+E4 If any of the cells contains something...

Excel: re: help with excel computation and formula, e4 d5, kindergarten teacher

e4 d5, kindergarten teacher, black arrow: 1 - you don t need the SUM -- C7/C4 will give the same answer as SUM(C7/C4). 2 - if D3 and E3 are always to be referenced when you fill the formula down (and not bevome D4 & E4, D5 &E5, etc), then you need to change the formula to: =C7/C4*$D$3+$E$3 and then...

Excel: re: help with excel formula, kindergarten teacher, black arrow

kindergarten teacher, black arrow, excel formula: I don t know what the various cells represent, so I have no way of knowing if your formula is correct. What s in c7? What s in c4? etc. I ll take a stab, though, and tell you that =SUM(c7/c4) doesn t make sense. Why SUM ? Why not just use the formula:...

Excel: re: help with excel formula and computation, kindergarten teacher, black arrow

kindergarten teacher, black arrow, excel formula: Hi Christian, You can simplify the formula to =C7/C4*D3+E3 I would probably need to see the worksheet to find if this is the correct average formula. If you can send me an example worksheet that would help. You can send it to cloverken@yahoo.com...

Excel: re: hiding a column after sheet is protected, format columns, columns check

format columns, columns check, referenced article: You didn t say what version of excel you have. If you have xl2002 or xl2003, then you should be able enable format columns in the protection dialog box. This should allow the user to perform any of the command under Format= Columns (which included hiding...

Excel: re:macro's, drop down list in excel, cell a1

drop down list in excel, cell a1, drop down list: Hi Martin Have you condsidered using a listbox ? Here is some code to be installed in a listbox which in this example traps the value slected and copies it to cell a1 Private Sub ListBox1_Change() a = ListBox1.Value Range( A1 ).Value = Range(...

Excel: re: splitting cells, store numbers, chesham

store numbers, chesham, cells: Imtiaz You can use the text to columns feature. Go to toolbar/data/text to columns and follow the instructions. If you have data in columns next to column A then you will need to add columns so the column A data can be split into the new columns that...

Excel: re: if statement response, statement response, haveing trouble

statement response, haveing trouble, labled: You gave me a name and a location. if the two worksheets are in separate files, then I would expect a workbook name, a sheet name in that workbook and a range. Telling me the label in the column isn t helpful as the formula doesn t use that. Since you...

Excel: re: value error, value error, null value

value error, null value, space character: use this instead: =SUM(i1,l1,p1) And, one of those cells possibly containing what you think is a null value is not the case. One or more of those cells referenced in the formula probably has a space character in it leading you to think it has what you...

Excel: re: writing data from excel to excel, sql queries, general idea

sql queries, general idea, macros: You can copy data from Excel to another workbook simply enough. You need to determine the range to copy and where to copy to. This is a general idea. Dim wbSource As Workbook Dim wbTarget As Workbook Dim rToCopy As Range rCopyTo As Range ...

Excel: read only easy, shortcut key, copy and paste

shortcut key, copy and paste, arrow: Hi Jey, I am not aware of a quick way to toggle, but I might suggest that instead of cutting and pasting that you copy and paste instead. The shortcut key is to highlight your desired selection and hit ctrl+c, when you are ready to paste, you can paste...

Excel: reading from object in excel imported from html, combo boxes, text boxes

combo boxes, text boxes, rng: This code cycles through every OLE object on the sheet (I m assuming that your text boxes and combo boxes are OLE objects), and grabs whatever value they have, recording it in Col A of the sheet. Sub getBoxValues() Dim wks As Worksheet Dim rng...

Excel: real estate landlord, real estate management, seminole dr

real estate management, seminole dr, ms 2000: Excel can be set up to allow files to be shared, so you could do it this way - the ONLY problem with it is that if people are adding records, they each need to use a different line (which probably won t happen!) - my suggestion therefore would be to set up...

Excel: reconcile numbers, pc09, astronomical number

pc09, astronomical number, statistical nature: Hi again Phil, You mentioned numbers with a total of .02 to 999.99 . Do you mean that the sum of the 25 or so numbers is in this range? Or do you mean that each of the numbers is in this range? If the former, then can numbers be negative? Also, do you...

Excel: could record which are 5 days old (total), array formula, test data

array formula, test data: The formula I gave you, entered as explained, will produce the correct answer, counting ONE for any cases that are 5 days or more before todays date and ZERO for any that are outside this range, then returning the total sum. If you can mail me a sample file...

Excel: record sampling, excel user, n records

excel user, n records, random sample: Enter the formula: =RAND() in a cell and copy it down all 2500 records. Then sort the 2500 records by the result of the RAND column. Now your records are sorted in a random order. You can just take the first n rows that way (as big a sample as you want)....

Excel: recording macro's to rename file, open dialogue, macor

open dialogue, macor, pastes: Mike, this is an interesting request. One that I am tempted to answer differently than asked. However, here s what you are asking - I think... Sub FileNamerename() NewName = Range( A1 ).Value filespec = Application.GetOpenFilename OldName = filespec:...

Excel: recording macros, expert listings, open dialogue

expert listings, open dialogue, excel expert: Mike, the macro recorder does not record what you do while a dialog box is open. You can do anything you like in there, and it won t know about it. Once you click OK, however, then all of the dialog s results get recorded. How you arrive at those results is...

Excel: recurring payments in budget, xls document, budget calendar

xls document, budget calendar, ed ed: Ed There are a few options you can use, an if statement would probably be best and it would go something like this. Cell B4 has the date the rent row is 6 The formula would be =IF(B4=DATE(YEAR(B14),MONTH(B14),1),1500, ) Hope this helps. ...

Excel: recurring payments in budget, xls document, budget calendar

xls document, budget calendar, ed ed: Ed How about this as a solution. Use an IFTHEN formula to determine if the day is the 1st then insert 1,500. Formula looks like this +IF(A1=1,+1500, ) Where A1 is the day of the month. You can drag this formula across the whole year and it will...

Excel: recurring payments in budget, xls document, budget calendar

xls document, budget calendar, toolpack: Excel is an awesome tool for doing budgets! There are a variety of things you can do to get income and expenses to appear on certain days...I have upload a quick sample file to the below address: http://home.swbell.net/nate-sus/excel/samplebudget.xls...

Excel: recurring payments in budget, xls document, budget calendar

xls document, budget calendar, ed ed: Ed Sorry for the late reply If you send me a copy of your file to greg@gsquaredtech.com. I should be able to help you. There are a few ways to automate some of what your trying to do but based on the fact that you are new to excel I d like to see your...

Excel: recurring payments in budget, xls document, budget calendar

xls document, budget calendar, days in a year: 1 - please email a sample to me at bobumlas@yahoo.com 2 - You say you ve dragged out to cover 1 year, but there are only 256 columns & 365 days in a year. So did you stop at about 3/4 of the year? 3 - this is perfect for something like Quicken, but can be...

Excel: recurring payments in budget, xls document, budget calendar

xls document, budget calendar, left column: If I understand what you want and you don t want to put those expenses in BEFORE they are incurred then one way of doing this is with some IF statements. Using B1 as an example of where the rent would go for January AND you have Jan 1, 2006, for example,...

Excel: recurring payments in budget, xls document, budget calendar

xls document, budget calendar, hi ed: Hi Ed How are you? You can start doing very easy things, for example 1. Insert a row above the weekdays, e.g., row 1 a) Type in B1 01/01/2006 (dd/mm/yyyy) or if your Excel is set for American date format (mm/dd/yyyy) b)Type in C1 02/01/2006 (dd/mm/yyyy)...

Excel: recursively autopopulate, array formula, source sheet

array formula, source sheet, aidan: What I gave you WILL do what you want, but its probably not very clear from the example how to extend it to getting multiple values - if you can let me have your email address, I can send you a file which should make it clearer. My email (if it helps) is...

Excel: reduce amount in database, batch sheet, calculation application

batch sheet, calculation application, trap error: Hi Robin 1. Go to VBA (Alt F11); create a VB Code Module (Insert/Module) and copy/paste the following to it (word wrapping of this noddy text editor may cause a problem - if so manually edit): Option Explicit Public BatchRow As Integer Public DatabaseRow...

Excel: reduce number of rows/columns in excel sheet, printing purposes, excel sheet

printing purposes, excel sheet, page break: Hi Nathalie, I m not quite sure I understand the question. Are you trying to reduce rows/columns for printing purposes? If so, try View/Page Break Preview and drag the blue lines to shrink the print area. Are you trying to hide some of your data? You...

Excel: reference cell, path type, shortcut menu

path type, shortcut menu, excel help: Hi Lucian, I think you want to use a hyperlink instead. Attached is the Excel Help section on Hyperlinks. Let me know if you need more help implementing, -Craig Create a hyperlink Show All Hide All Create a hyperlink to a new file Right-click...

Excel: reference cell, literal text, lucian

literal text, lucian, hyperlink: Not sure if you mean jump to as in move to (at which point, insert a hyperlink from the insert menu) or if you want the value to be an external link - at which point, the easiest way to do it is to have the two sheets open - then in the cell you want the formula...

Excel: reference cell, mylink, word name

mylink, word name, lucian: The EASIEST way to hyperlink to another cell in another workbook: Select workbook2 cell and hit Insert-- Name-- Define. Call it MyLink (or some other one-word name). Now, from workbook1, hit Insert-- Hyperlink and choose File. Then choose your file....

Excel: reference cell, cell address, lucian

cell address, lucian, ogilvy: Hello Lucian, if you are trying to create a linking formula to a second workbook which is currently open in the same instance of excel, you can just type in the formula Assumptions - Cell to link to: Workbook name: SecondBook.xls Worksheet name:...

Excel: reference cell, cell b1, sheet1

cell b1, sheet1, cell a1: Open Excel. The first workbook will get the default name Book1. Then choose File, New to open another workbook, which will be named Book2. Now in Book1, select cell A1. Type an = but don t click Enter. Instead, immediately after typing the = , select...

Excel: How to reference cell in another workbook, default selection, cell contents

default selection, cell contents, abcd: IF you have a formula that is already pulling that data from the CLOSE workbook then MyVar = Sheets( Sheet1 ).Range( A1 ) assuming the formula is in A1 of sheet1. Use the Mid function to pull only some of that data that is in A1. gets data starting...

Excel: reference named range in a workbook on server from workbook on users pc's, key performance indicator, ppes

key performance indicator, ppes, cell reference: I m not sure I understand what s wrong with using the defined name instead of B3, like: \Server1ppes-westPPUatExcel[SampleOnlineRpt02.xls]Online_Orders !TheNameGoesHere I don t have any real knowledge about SQL queries, so I m not sure where you...

Excel: reference text box in vb, cell contents, life alot

cell contents, life alot, textbox control: There are a few ways you can accomplish this and neither involves a macro IF I understand what you need/want. You can place a label control from the Forms Toolbar on the sheet. While the label is still selected then type =A1 in the formula bar. Whatever...

Excel: reference a text color, sumif, minimal knowledge

sumif, minimal knowledge, excel 2000: Heath You can t write the formula such that it will use the text color as a criteria for summing, however. It sounds like you have some criteria that you are using to establish the text color. Such as all numbers under 0 are green all numbers that are...

Excel: references, column c, different numbers

column c, different numbers, b17: I don t see the pattern for knowing that b8:b12 has a5 subtracted from it; so I don t know what b13:b17 has subtracted from it -- a10? Where is this result to be -- in column C? I don t understand ...so that every time the reference changes in the a column......

Excel: referencing externally using a variable or INDIRECT, matricies, cell reference

matricies, cell reference, refernces: Not sure if indirect will work, but if it IS to work you will need to enter it as =indirect( [Sub2_FFT_Heli_Data.xls]2_300_40 ! & c1) Possibly easier is to select all worksheets and use the edit replace command to replace the one reference with another...

Excel: reg Excel (Office XP), category text, question sir

category text, question sir, number crunching: Sorry misread You need to also change it to text, excel is limited to 15 digits then it shows as you have seen. format cells number tab Category: Text......Click [OK] If you need to do calculations on this number then you will need to use text...

Excel: regarding cell ill colour, formatting toolbar, yahoo

formatting toolbar, yahoo: I need detailed steps -- I don t have that problem -- simply use the fill color toolbar button on the formatting toolbar. Does that happen on all your workbooks? If so, I have no idea; if not, send me the offending workbook & I ll have a look at what s going...

Excel: regarding coding for macro, number checker, transport number

number checker, transport number, macro tool: Sorry. Typically I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore take longer to...

Excel: regression, polynomial equation, ogilvy

polynomial equation, ogilvy, coefficients: I believe this is what you want: You can see how Bernard Liengme solves for the coefficients of a polynomial curve using LINEST. Perhaps you can adapt a similar approach to working with your 6th order polynomial equation. http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm...

Excel: regression, case range, c values

case range, c values, formula c: Enter your values for A, B, and C in range A1:C1. For an example, we can try cell A1 = 1, B1 = .2, C1 = 5 In cell A3, enter the formula: =A2+1 In cell B3, enter the formula: =$C$1+$A$1*A3^(-1*$B$1) This is the formula that calculates your y-value...

Excel: relative and absolute cell formula, blank cells, c28

blank cells, c28, ogilvy: Jim Sub ABC() Application.Goto Reference:= R65536C20 Selection.End(xlUp).Select ActiveCell.Offset(0, 3).Select r = ActiveCell.Row s = =IF(RC[-18]=660,R & _ r & C29,IF(RC[-18]=550,R & _ r & C28, )) ActiveCell.FormulaR1C1...

Excel: relative reference visual basic button, top menu bar, relative reference

top menu bar, relative reference, names thanks: Start recording a macro You can then right click on the a blank area of the top menu bar and click the stop recording selection or select tools= customize, then click in the checkbox for stop recording or goto View= Toolbars and click on the stop...

Excel: How to release break mode, vbe, c program

vbe, c program, microsoft office: All workbooks? Very strange. If it were ONE workbook, then maybe its macro has a STOP statement in it? Other guess is that in the VBE you have Tools/Options/General tab Break on All Errors checked - change to Break on Unhandled Errors . If these don t...

Excel: Can you remove the 30 character limit?, character limit, cells

character limit, cells, email: Katherine 30 is the limit for 2003 and below. In 2007 you can have up to 255 arguments. As far as I know there is no way around this apart from streamlining your functions. If you would like to send me your workbook I could look at it and see if it can...

Excel: remove the "brush" on excel sheet, gus gus, excel sheet

gus gus, excel sheet, paperclip: Gus, It appears you are using Excel XP (aka Excel 2002). I am still using Excel 2000, thus I cannot comment explicitly on how to remove this cool feature, which I agree is annoying. I particularly hate the little dog or paperclip or other petulant character...

Excel: remove spaces between text, cell a1, permanent solution

cell a1, permanent solution, advanced features: Good morning!! There are actually two ways to do this...depending on what you are looking for: 1) Presuming your number, with spaces, is in cell A1, you would use this formula: =SUBSTITUTE(A1, , ) This formula substitutes all spaces with nothing...which...

Excel: remove spaces from text, excel vba, looping statements

excel vba, looping statements, dlh: So, what s your question? All you told me is what you need. :O. Assuming you are asking me how would you write the code to remove the spaces then: Dim X as Variant X = Range( J1 ) X = Replace(X, , , 1, 75) The next line puts the cleaned up...

Excel: removing blank rows, blank rows, spread sheet

blank rows, spread sheet, shane: Hi Shane, Thank you for the kind words. Thank You, Ken ...

Excel: removing characters, letter prefix, vba

letter prefix, vba: Dan You can use these steps Text to Columns under Tools 1) Insert new column to right of column of ident data 2) Select part alpha column 3) Tools -- Text to Columns -- Fixed -- (how many letters.) 4) Remove original part ident column...

Excel: removing empty cells, blank cells, empty cells

blank cells, empty cells, excel worksheet: Brian, When you get to Visual Basic Editor, click Ctrl+R. This should show the Project Window . Click on VBProject . Right Click and click on Insert Module. It might help if you get an Excel book if you need to make use of Macros and other complex...

Excel: removing links, link thanks, spreadsheet

link thanks, spreadsheet, quotes: You can do a find for [ (without quotes) When you find these you can copy/paste special values. Or, if the links exist in defined names or objects, you d be better having a workbook called findlink.xla which I can send to you if you send me your email...

Excel: removing links in a spreadsheet, link thanks, menu bar

link thanks, menu bar, spreadsheet: Hello, Delores, The easiest way to sever a link is to remove the formula and replace it with its resulting value. Here is the quickest way to do so: 1) Click on the formula. 2) Press Ctrl/C to copy it to the clipboard. 3) Without moving the cursor,...

Excel: removing links, blank worksheet, link thanks

blank worksheet, link thanks, copy paste: Delores I assume if you want to remove the link, but you want to retain the values that are created by the link. You can work with the toolbar/copy/paste special.values feature. Create a new blank worksheet or workbook, and use this feature to copy...

Excel: removing rows in xl spread sheet, keyboard shortcut, blank rows

keyboard shortcut, blank rows, filter auto: You can do this with a macro, but equally easy is to use the data menu, then filter, auto filter - filter on blanks in the column you want - then select all the resultant rows and press Ctrl - (the keyboard shortcut for delete row) - then remove the autofilter...

Excel: How can I rename Moduls? (XLS97), programming code, vbe

programming code, vbe, ogilvy: Make the properties window visible in the VBE. Hit F4 in the VBE if it isn t visible or select it under the View menu. In the Project explorer, select the module you want to rename. The only property displayed for a module is the name. Go to the properties...

Excel: rendom cell picker in Excel, colums, flash cards

colums, flash cards, e15: No need to apologize. You will need a macro to do what you want done. And, sorry, I don t just write macros for people on this site who need one, want one, and/or seem to expect me to do so. 99% of all macros are more involved than what you think and...

Excel: reorder level, lead time, spreadsheet

lead time, spreadsheet, pointers: Bertha Its not hard to do with excel, but I need to know some details of what you want your inventory to look like. Also usually reorder quantities depend on ordering lead time assumptions, so you need to calculate that for each product. Give me some...

Excel: repacing value by a macro, number rows, excel sheet

number rows, excel sheet, combo box: Hi MGT, How are you? Qith a few alterations the following will do: 1st. Region 1 - a1:B3 Region 2 - a7:b9 Region 3 - a12:b14 2nd tools-macro-vbe insert-module copy Sub Dropdown1_change() Dim R1 As Range, R2 As Range, R3 As Range Set R1 =...

Excel: How do I repeat the same column..., page setup menu, column headings

page setup menu, column headings, sheet tab: You didn t say WHY they needed to be on each page - if (as I suspect) this is for printing purposes, then do it from the file, page setup menu - you can then click on the sheet tab and select the rows to be repeated at the top of each page - which should (I...

Excel: repeat a macro

10 was just an example. Other examples: runs the same macro 3 times For X = 1 to 3 code to run - in this case, 3 times Next X runs the same macro 7 times For X = 1 to 7 code to run - in this case, 7 times Next X runs the same macro...

Excel: repeating function in array?, array formula, sumproduct

array formula, sumproduct, variable number: =SUMPRODUCT((A1:Z1*(MOD(COLUMN(A:Z),2)=1))*(B1:AA1*(MOD(COLUMN(B:AA),2)=0))) change Z & AA if you wish, like: =SUMPRODUCT((A1:IU1*(MOD(COLUMN(A:IU),2)=1))*(B1:IV1*(MOD(COLUMN(B:IV),2)=0))) (thanks for the challenge!) P.S. NOT an array formula...

Excel: How to replace error messages, blank cells, blank cell

blank cells, blank cell, integer values: Select all the cells, use Format/Conditional Formatting, change cell value is to formulas is , then enter =ISERROR(A1) (assumes A1 is active cell), then click Format, then Font, then select white font (or match background color of worksheet). Average...

Excel: How to replace values for different selected ranges using VBA, range f1, range areas

range f1, range areas, vba macro: Seems to me that this is basically a copy and paste function though you do not actually copy and paste. Transfering data from one location to another is essentially copying and pasting, isn t it? Isn t that what you are doing or want done - copying the value...

Excel: replacing formulas, paste values, vlookup

paste values, vlookup, excel spreadsheet: David, If you are familiar with VBA (aka macros), I could help you write a macro that would perform the copy paste action based on some indicator. do you have another field that you can key in on to determine if the vlookup is necessary or not? Let me...

Excel: replacing formulas, paste values, vlookup

paste values, vlookup, excel spreadsheet: Hi David How are you? 1. Assuming you have in B1:B20 the formula =VLOOKUP(A1,$J$10:$K$12,2,FALSE) =VLOOKUP(A2,$J$10:$K$12,2,FALSE) ... do the following 1. tools - macro - visual basic editor 2. insert - module 3. copy Sub pastingvalues() Set...

Excel: replacing formulas, paste values, vlookup

paste values, vlookup, selection formula: After the cells are selected, you can run this one-line macro: Sub MakeValues() Selection.Formula = Selection.Value End Sub But you should not be having slowness due to vlookup formulas -- they are very fast. I suspect something else is causing the...

Excel: replacing formulas, paste values, vlookup

paste values, vlookup, continuous lines: Hey David, Is there any way you can send me your spreadsheet so I can see the exact formula you are using? I could possibly find an alternative for you to use. My email address is bluecat74@yahoo.com. Generally you can use a combination of MATCH and...

Excel: report - date range, seperate dates, res1

seperate dates, res1, dt2: Private Sub CommandButton1_Click() Dim res1 As String, res2 As String Dim dt1 As Date, dt2 As Date Dim i As Long, j As Long Dim sh As Worksheet, sh1 As Worksheet Dim v As Variant, r As Range Set sh1 = ActiveSheet sh1.cells.Clearcontents res1 = InputBox(...

Excel: report form from excel data, wise guy, macros

wise guy, macros, creativity: There is no place you can go to per se to tell you EXACTLY how to do what you want to do. All the features are there in EXCEL to do what you want to do. It s just a matter of creativity and imagination applied to EXCEL features such as formulas, macros,...

Excel: report format, mail forms, outlook mail

mail forms, outlook mail, mail form: Hi Rodney, Yes, I can see why this would be a very useful capability. I believe that the best way to do this if you are using MS Outlook is to customize an Outlook Mail Form. Just for an overview, here is the first paragraph of the Help file for the...

Excel: request excel help, data mining project, space character

data mining project, space character, instances: IF I understand the question correctly, you want to replace all instances of a space character in column E with nothing - if so, select column E as a block (click on the E) and then do Edit Replace - type a space in the find what box and NOTHING AT ALL in...

Excel: requesting an autofill macro/ option in excel, blank cells, invoice numbers

blank cells, invoice numbers, blank cell: If it is in a pivot table, then no, you can t edit the cells occupied by a pivot table. Other than that, select the first invoice and hold down the shift key while you pick the last cell that needs to be filled (using the mouse). Now you should have...

Excel: require users to save workbook with different name, excel workbook, partial solution

excel workbook, partial solution, office excel: Sharon, I think the code below will work better for you, though it won t prevent a user from copying it in Windows Explorer and pasting it in a different directory. Basically, the code will function when the user chooses to Save the file (not SaveAs as...

Excel: reset cell values to zero across multiple worksheets, input cells, global error

input cells, global error, cell values: Well, yes, and it would involve a macro as you have already concluded. If you mean to ask what is wrong with my code and what is causing the _Global error message then I would obviously need to see the code. If you did not mean to ask that then yes there...

Excel: reset cells across multiple worksheets, input cells, global error

input cells, global error, users input: You should name each sheet s input cells, then reference them in the routine, something like: Sub ClearCells() Sheets( Sheet1 ).Range( RgForSheet1 ).ClearContents Sheets( Sheet2 ).Range( RgForSheet2 ).ClearContents Sheets( Sheet3 ).Range( RgForSheet3...

Excel: reset values, input cells, reset values

input cells, reset values, global error: If i were doing it, I would loop through the sheets and process sheets individual. VBA does not support grouping with a few very unique exceptions. Private Sub CommandButton1_Click() Const s as String = A2,B3:B10,C4:C4,F9,M12,G1:I2 Dim sh as worksheet...

Excel: How to restric duplicate entry, duplicat, data validation

duplicat, data validation, invoice number: Hi Vidjut, Select Column A by clicking on the A in the column, then go to the Excel menu and select Data then Validation... then from the Allow: drop down menu select Custom then in the Formula box type in the formula =COUNTIF(A:A,A1)=1 ...

Excel: restricting a function to todays date, time today, current date

time today, current date, a3: Whereas the function NOW() returns the current date and time, the function TODAY() returns today s date only (time is set to midnight). If column has today s date with a particular time, TODAY() won t match it. Can you write back and let me know exactly...

Excel: retreive data with more than one criteria, retreive data, null value

retreive data, null value, resnick: Here s a very simple example of how to retrieve data with more than one criteria. You should be able to customize it to your needs. Say that in Col A you have Dates , which you seem be expressing as integers like 3, 5, or 7. In Col B, you have Qty, which...

Excel: return calculaton when focus changes, target target, elementary school classroom

target target, elementary school classroom, correct test: Your code works fine. Where you are placing it is the problem. It should not go into a Standard Module, it goes in the Worksheet Code. Right click on the Sheet tab that you are using & select View Code. This will open the VB Editor. In the left hand dropdown...

Excel: return count of 2 values based on 1 criteria, array formula, west delivery

array formula, west delivery, yolk: John: I may need you to email a sample spreadsheet to Lotus@swbell.net if this doesn t answer your question. Based on the description, it kind of sounds like you have data set up like this: ACME DELIVERY ACME 57 ACME DELIVERY ACME 55 YOLK DELIVERY...

Excel: return last data in column meeting criteria, vba solutions, column index

vba solutions, column index, hi thanks: Hi thanks for writing. Unfortunately sheet functions are not my area. As I state in my profile I work exclusively on VBA solutions. Im certain oneof the otherexperts will be able to assist you though. However if you can tget help and you want a VBA solution...

Excel: How to return from edit mode, rtd project, undefined error

rtd project, undefined error, vba code: have you compiled the vba code? Do you have the vbe available (not hidden) when this happens so you can be directed to the line of code causing the error? When you get the Object undefined error, what choices are you presented with (or is it only OK )?...

Excel: return results to one cell for many calculations, correct answers, gradebook

correct answers, gradebook, d7: Hi Rodney, I certainly can help you with this. the best way to handle it is that you send me an example workbook to gygulyas - at - yahoo - dot - ca (I don t like spammers..;-) ) In the workbook, please just put in the layout you envision and enter some...

Excel: returning color to a cell in excel, conditional formatting, logical function

conditional formatting, logical function, fond memories: Conditional formatting will do this quite happily - I ve fond memories of RAG reporting myself, which this sounds like - if you go to the format menu, you will find conditional formatting - change the Cell value is to Formula is and enter whatever you want...

Excel: returning value from column, cell b2, cell a2

cell b2, cell a2, cell a1: Hi jerome I think your evalution of me is a little unfair. If you had read my profile you would have explicetly seen I dont do worksheet function I write code. I offered to do code for you and you didnt even reply. Best Des limbic_lesion@hotmail.co...

Excel: returning a value, cell reference, craig

cell reference, craig: Hi Fiona, This is an IF THEN statement inside another IF THEN statement. If a number is greater than .95, then check the next if then statement, If that same number is less than 1.05, then return HIT. If either of these two tests are false then return...

Excel: returning values, array index, b cells

array index, b cells, inputbox: Hi Annie, How are you, The best way to solve your problem is saqve the length of the materials in a range of cells or an array - index variable, not a simple variable. Please study the following Option Base 1 Sub test() For i = 1 To 10 A = InputBox(...

Excel: reverse data, blank cell, negative numbers

blank cell, negative numbers, simple 1: Here s a cute trick that many people aren t aware of, Sheila. It s really quite simple: 1) Enter -1 in a blank cell somewhere. 2) Copy the -1 to the clipboard. 3) Select all of the positive numbers that need to be converted to negatives. 4) On...

Excel: reverse a number, set of numbers, b1

set of numbers, b1, craig: Hi Maria, There is no easy way to do this. The only thing I can think of is to take the original number and perform the text-to-columns function on it and then the concatenate function. Let me know if you need help following up on that. -Crai...

Excel: revised question, brainbench, d30

brainbench, d30, c30: Daran, In this case it is necessary to use the N() function. By breaking the formula apart it may be easier to see what is happening. I ll use your example and assume that the values are in cells A1:B30. First, select cells C1:C30 and type =MATCH(date,date,0)...

Excel: Getting rid of Carriage returns, douglas m smith, format cells

douglas m smith, format cells, few suggestions: Murray, I m not sure what point you are at in the process, but here s a few suggestions: I forgot to mention that nothing will show up in the Find what box when the Alt sequence is typed in. Also, make sure you are typing the number sequence from...

Excel: Get rid of junk part, cell b2, number column

cell b2, number column, separator: You can use the LEFT formula, as in this case the data you want has always the same lenght (10). For example, in cell B2: =LEFT(A2,10) And then copying the formula across the column. Other option is to use the menu Data - Text to columns, choose delimeted...

Excel: Get rid of maximun and minimun values among several fields, numeric fields, maximun

numeric fields, maximun, field 3: If you are interested in sums and averages, you can use the functions SUM, MAX and MIN to get the results. For example, having your example in cells A1:A6 the following formula will give you the average without the fields #2 and #5: =(SUM(A1:A6)-MIN(A1:A6)-MAX(A1:A6))/(COUNT(A1:A6)-2)...

Excel: Get rid of unwanted list in Excel, file excel, excel 2000

file excel, excel 2000, arrows: Carmen If by list you mean validation list ie there are drop down arrows in each cell when you are in the cell, highlight the entire sheet and go to Tools - Validation and remove them all. If this isn t the problem can you send the file to richard@littlewingweb.com...

Excel: Get rid of an unwanted list on Excel, file excel, database access

file excel, database access, access list: Carmen Converting the data into a list should not have changed the date format, so they might have changed it before the conversion. If it was converted to a database Access list, which I am assuming it was, I won t be much help. I seldom use Access and...

Excel: right function, trim right, hyphen

trim right, hyphen, delimiter: Taking A1 as being the cell you want to search, use this formula:- =TRIM(RIGHT(A1,LEN(A1)-SEARCH( - ,A1))) Note - I ve used TRIM here to remove any leading/trailing spaces. Also note that if you want to split the cells permanently you can use the data...

Excel: right way to set up certain type sheet, price columns, column headings

price columns, column headings, verson: PK Usually a question asked generates more questions that I have. There are a lot of different possibilities with what you want to do. For instance what does compare products mean--could be prices, models, features, etc. etc. At its simplest you...

Excel: Left and right, type b1, c6

type b1, c6, theses: Hi Chris, How are you? Do you want to swap the lines with the columns? If this the case use 1. select E1:J3 2. type =tanspose(A1:C6) 3. press ctrl + shift + enter or do you want to swap the contents of a cell, writing it from left to right?...

Excel: rollover max, array formula, blank cells

array formula, blank cells, empty cells: I guess I am a bit confused Monika. I gave you an answer to this on 9 August. At that time it was 250 cells, so make the necessary adjustments: Monika Category: Excel Private: No View Published Answer Subject: rollover sum Question:...

Excel: rollover max, array formula, blank cells

array formula, blank cells, empty cells: I guess I am a bit confused Monika. I gave you an answer to this on 9 August. At that time it was 250 cells, so make the necessary adjustments: Monika Category: Excel Private: No View Published Answer Subject: rollover sum Question:...

Excel: rollover sum, array formula, blank cells

array formula, blank cells, empty cells: Hello Monika, I assume when you are saying that are filled you are saying that I should find the last filled cell, step back 249 rows and sum from there forward - rather than saying that there are blank cells in the the data and you want to sum a total...

Excel: rookie problem, pairs, lcount

pairs, lcount: I think this should do: Sub RemoveSome() Dim oCell As Range Dim lCount As Long Set oCell = ActiveCell Do If Abs(oCell.Offset(lCount) - oCell.Offset(lCount + 1)) 100 And oCell.Offset(lCount + 1) Then oCell.Offset(lCount).EntireRow.Delete...

Excel: roster, tennis roster, aidan

tennis roster, aidan, pairs: How very strange - I worked on something incredibly similar for someone earlier this week - I had to do it with a macro - the actual macro generates the pairs, it doesnt group them into sets of games, but that SHOULDN T be a problem - if you can email me (aidan.heritage@virgin.net)...

Excel: rotating excel charts in word documents, landscape format, size tab

landscape format, size tab, excel charts: It s not an option with this type of object - so your options are (1) convert it to a picture and then rotate that (2) possibly easier - change the layout of the current page to landscape format (this will give you section breaks, and different layout...

Excel: round up to $0.99, douglas m smith, brainbench

douglas m smith, brainbench, 49 up: Hi Karen, ROUNDUP will roud a number up to a specified number of decimal places. Using it with a little math will give you the formula you want: =ROUNDUP(your formula,0)-0.01 In your example, it would round $25.49 up to $26.00 and then knocks a penny...

Excel: round function, frequency chart, e7

frequency chart, e7, e9: Format the cell to a percent format to 2 decimal places then =ROUND((C7/100*$C$5)+(D7/100*$D$5)+(E7/100*$E$5)+(F7/100*$F$5),4) should work Use 4 and not zero for your rounding number since you are dealing in percentages, a number less than 1...

Excel: round function, cell references, frequency chart

cell references, frequency chart, e7: Hi Joel, I do not see anything wrong with your formula. =ROUND((C7/100*$C$5)+(D7/100*$D$5)+(E7/100*$E$5)+(F7/100*$F$5),0) but I do notice different cell references between this formula and the first formula. =(C9/100)*$C$5 + (D9/100)*$D$5 + (E9/100)*$E$5...

Excel: round function, frequency chart, decimal points

frequency chart, decimal points, whole numbers: Joel Is the final answer a decimal? ie for 80% it will show as .8, if this is the case then we will have to try and increase the round function by decimal points as opposed to whole numbers. Can you send a copy of the spreadsheet to richard@littlewingweb.com...

Excel: round function, frequency chart, e9

frequency chart, e9, e7: Joel The formula you are using should round correctly--if applied to the result of the first formula you mentioned in the question. =ROUND(((+C9/100)*$C$5 + (D9/100)*$D$5 + (E9/100)*$E$5 + (F9/100)*$F$5),0) This formula just rounds the result of ...

Excel: round function, frequency chart, e7

frequency chart, e7, ogilvy: Event though the formula displays as a percentage ex: 83.1%, it is actually stored as a decimal .831 so you need to put a 2 as the second argument =ROUND((C7/100*$C$5)+(D7/100*$D$5)+(E7/100*$E$5)+(F7/100*$F$5),2) and, of course, the cell should remain...

Excel: round function, douglas m smith, decimal fraction

douglas m smith, decimal fraction, percentage values: Hi Joel, Sorry about the delay, I was off-line this weekend. You have a zero for the ROUND argument (last number in the formula), which means zero decimal places, with respect to whole numbers. You will need to change the value of zero to 2 to show...

Excel: How to round time formulas to zero or 0.5, complicated questions, exact time

complicated questions, exact time, cells: Yes, you can contact me directly if you d like. I am a consultant who develops EXCEL and other applications/programs so I will be glad for you to contact me directly. My direct email address is billedd@aol.com. I am in Alabama. I charge $75 per...

Excel: rounding, toolpack, analysis toolpak

toolpack, analysis toolpak, cell a1: Good afternoon Steve! This is really just a little bit of mathematical trickery, but it should get the job done... Presuming the value you want to round is in cell A1: =ROUNDUP(A1/10,0)*10 I m fairly certain ROUNDUP is available in the standard...

Excel: rounding, b20, roundup

b20, roundup, number 1: oh! just a little trick. you probably are thinking, why did not they just invent MROUNDUP? well this is the solution for multiples of 10. you just use ROUNDUP(number,-1). where number = A20*B20. THE -1 IS TO MAKE SURE NUMBER IS ROUNDED TO THE NEAREST...

Excel: rounding, douglas m smith, companion function

douglas m smith, companion function, brainbench: Hi Steve, The CEILING function should do the trick for you: =(CEILING(A20*B20,10) The 10 tells it to round up to the next multiple of 10. It s companion function is FLOOR, which rounds down. For completeness, other rounding functions (besides...

Excel: rounding, b23, cells

b23, cells: Rachel I m not sure whether you are rounding to 1 s, thousands, etc., but the formula looks something like this. =ROUND(B23,-3) This formula is in some cell other than B23, and it says go to B23 and transfer the number from B23, but round it down (-3)...

Excel: rounding, hundreth, thousandths

hundreth, thousandths, a1: Have had trouble accessing the site the past few days. Try this. There may be a more elegant way but this worked with the examples you provided. The ISERROR traps for an error, like when there is no value in A1, in this example, and returns zero if...

Excel: Getting the last row, cell d1, header row

cell d1, header row, thanks in advance: This macro would work on any workbook by clearing column D except for cell D1 and by formatting cells R2 through U65536. Sub ClearRowD() Application.ScreenUpdating = False Range( D2:D65536 ).Select Selection.ClearContents ...

Excel: How to get the last row and last col, worksheet cells, simple answer

worksheet cells, simple answer, worksheet function: The simple answer is to get the code to unprotect the sheet first, then return the column information, then reprotect - alternatively, you could use the COUNTA worksheet function (as long as you are happy there are no blanks) to get the value you need...

Excel: row to column, moshe, cells

moshe, cells: Moshe I assume you want to copy the cells in a row (going across)to cells in a column (going down). Copy the cells, then make active the first cell in the column that you want to paste to. Then right clic/select paste special then select transpose, then...

Excel: Make row disappear, zero measurement, having a bad day

zero measurement, having a bad day, smart ass: You re welcome. And as I said, I just answer the question literally as it is asked. I do not want to infer and interpret what the REAL question is from what is actually worded for the sole purpose of preventing so many back forth emails to get to the real...

Excel: row height the same, page margins, spreadsheet

page margins, spreadsheet: June Excel will automatically adjust the margins for each column when you remove the page margins, this is by default and I haven t found a way around this, the row height and font shouldn t change though. Check the page setup and ensure that it is set...

Excel: Getting the last row, numberformat, header row

numberformat, header row, thanks in advance: in a macro: Sub ClearD() Range( D2:D65536 ).ClearContents End SUb Sub FormatRtoU() Range( R2:U65536 ).NumberFormat = m/d/yyyy ) End Sub It doesn t matter that the data doesn t go so far down. If that s important, then: Sub ClearD() Range(...

Excel: rows and numbers, blank rows, blank cell

blank rows, blank cell, negative numbers: Here s an idea for inserting 3000 blank rows in-between your existing data. Let s say that your data goes from A1:J3000. Follow these steps: 1) Insert a new blank column at A. (This will be a temporary column we ll delete later.) Your data is now in...

Excel: no of rows as Range, ltrim, g3

ltrim, g3, dest: Hi Zeeshah, What about that? Sub CalculateTime() Dim nRows As Long, dest As Range, addr As String nRows = Application.ActiveSheet.UsedRange.Rows.Count addr = G3:G + LTrim(Str(nRows)) Set dest = ActiveSheet.Range(addr) For i = 1 To dest.Rows.Count...

Excel: rows search and extraction, inventory data, wks

inventory data, wks, thanks in advance: Assume that the dates are in cells A1, B1, C1, ... etc. The code below finds the column headed 01/01/07 and selects that column: Sub findDate() Dim wks As Worksheet Dim cl As Range Set wks = ActiveSheet Set cl = wks.Range( A1 ) ...

Excel: rpage break issue, setup tab, excel 2000

setup tab, excel 2000, cells: IF the pages have to break at a certain point, then you are going to run into problems as different printers can have different tolerances - you CAN go into the File Page Setup tab and set that the page must fit to one page tall and one page wide (or leave...

Excel: run batch of excel files from a Macro, excel worksheet, peter answer

excel worksheet, peter answer, file names: You will have to loop thru all the files, open each one, get the data you need from it, and then close it IF you want the code to open each one automatically for you. You will need some way of telling your code which files to open and there are several...

Excel: run calculation for only a 12 month window, date function, widget

date function, widget, widgets: format the cells that contain the date to Date Format. To chaange the format, right click the particular cell, click on Format, choose Number tab and select Date and the Date format you like. After all your dates are formatted correctly, you proceed to...

Excel: run macro based on cell content's change, chip pearson, macro code

chip pearson, macro code, cell content: It depends on how the value will be changed and what pattern of changes is possible. if you will manually edit the cell, then you can use the Worksheet_Change event and trap for the Target argument indicating the triggering change was in cell A1. This...

Excel: run macro based on change on cell's content, macro code, dear bill

macro code, dear bill, target: You write a macro that will run itself automatically, which will do so based on events. Event examples are saving the file, printing, and the two below, among others. The code you want to run will need to be placed in one of the two sheet level event driven...

Excel: run a macro if a cell contains some text, target range, e11

target range, e11, lot one: Well, Target is the range you changed when the macro fires and the entire part: Intersect(Target,Range( E11 )) is the intersection of the range you changed (Called Target ) with cell E11. If there is no such intersection (because you did not change cell...

Excel: run macro code based on cell's change, macro code, target

macro code, target, aidan: Use the CHANGE event of the workbook Private Sub Worksheet_Change(ByVal Target As Excel.Range) if Target.Row=1 and target.column=1 then do your event here end if End Sub NOTE that as described here, the macro would run WHATEVER happened to A1 -...

Excel: how to run a macro from another work book, services specialist, pensions

services specialist, pensions: Yes, no problem - it s along these lines Workbooks.Open(FileName:= http://thepulse/_uklife/_services/_specialist_pensions/excel/Todays%20fund%20and%20pension%20v%203.xls ).RunAutoMacros Which:=xlAutoActivate Set mywrk = ActiveWorkbook holdvar...

Excel: run private object, private object, macros

private object, macros: Don t declare it as private. Declare it as public then you can run it by doing Tools= Macro= Macros. or you can assign it to a button or call it from an event associated with a button. I don t know what you mean by bottom click , so I am assuming...

Excel: How to run a program until a letter is pressed, break key, application object

break key, application object, api functions: To do exactly that, I think you would have to subclass Excel and query the key buffer using API functions. I don t think Excel has anything that will work exactly that way (but I know very little of what you are trying to do). Hitting escape or Ctrl+C...

Excel: How to run a .wmv file from a command button, shell run, public declaration

shell run, public declaration, command button: Richard, I use this for playing sounds: Declare Function sndPlaySound32 Lib winmm.dll _ Alias sndPlaySoundA (ByVal lpszSoundName As String, _ ByVal uFlags As Long) As Long Then for example... Sub Clear_wav() Call sndPlaySound32(...

Excel: running code when excel opens, access macros, worksheet menu

access macros, worksheet menu, menu toolbar: Hi Tanaya, Yes, there is a similiar feature in Excel. It is the Open event of the Workbook object. Simply right-click on the Excel icon at the left end of the Worksheet Menu toolbar and select View Code. This will take you to the Visual Basic Editor and...

Excel: a running daily total, excel user, inputed

excel user, inputed, column c: I assume you want the accumulation to start over again with each new date and accumulate while the date stays the same. Assuming row 1 is for headings and column V is available for the running totals in U, put this in V2 and fill down as far as necessary:...

Excel: running an excel macro from project, microsoft excel application, bubble chart

microsoft excel application, bubble chart, xls workbook: Try code something like this: ********** Dim xl As Excel.Application Dim wb As Excel.Workbook xl is the Microsoft Excel application Set xl = New Excel.Application make Excel visible xl.Visible = True open the PERSONAL.XLS workbook...

Excel: running totals, target, end result

target, end result, balance sheet: Ok. I hope I understand what you want. So here is the example for sheet 2. I assume that you want to put the total from Sheet 2 (from column A) on to A1 of Sheet 1. Steps: 1. While on Sheet 2, right click on tab and go to view Code. 2. Copy and...

Excel: running totals, cell a2, foll

cell a2, foll, irma: Irma, sorry I misunderstood. The only way I know to make something like that work, without formulas, is to use VBA code. The code needs to be inserted into the project for the sheet, not in a module. So you press Alt F11 to open the Visual Basic Editor....

Excel: runtime error 1004, color pallett, explenation

color pallett, explenation, runtime error 1004: You must use a ColorIndex that s available on Excel s color pallett, which apparently doesn t include 100. Record a macro while setting the interior of a selected cell to the color you want. Then check the automatically generated code to see what ColorIndex...

Excel: Salary moves with constraints, max salary, salary increase

max salary, salary increase, salary range: Yes, you could assign a point value to each employee s education level. For instance, if you decide BS=4 and Diploma=1, you re saying that a BS should be worth a salary increase 4 times the level of a Diploma. Once you ve done that, you can sum the total...

Excel: Sales cash out formula, colume, time functions

colume, time functions, colum: I don t know a way to automate this in Excel. There may be a way to do this using VBA, but I can t help with that. If you want to store a value based on a formula that uses date/time functions like TODAY(), you ll have to manually type the values in each day...

Excel: Sales Tax, sales tax rate, conditional formatting

sales tax rate, conditional formatting, small fonts: What kind of small business operator doesn t own a computer! Or at least a handheld calculator??? Interesting question, although I think you re approaching it from the wrong angle... It would be rather difficult to attempt to develop a formula (or more...

Excel: Sales Tax, sales tax rate, column c

sales tax rate, column c, excel 97: Lila I assume that you need to print the tax schedule so that your client can refer to it when they are charging the tax, so you will need a large schedule, to cover all of the sale ranges, therefore the need for excel to make the schedule rather than...

Excel: Sampling, sampling function, data analysis tools

sampling function, data analysis tools, sampling analysis: Hi Linda How are you? I am sorry but I think this question takes a long time to discuss. But, please see the Excel help. It says the following The Sampling analysis tool creates a sample from a population by treating the input range as a population....

Excel: Save As, mr smith, raw data

mr smith, raw data, workaround: I think you can avoid these problems by using SaveCopyAs instead of SaveAs -- something like ThisWorkbook.SaveCopyAs C:TEMPSMITH.XLS after which you can reset any fields back to original (empty) status. This way the file Calculations is always the one...

Excel: Save as box is reverse alphabetized, dialog boxes, newest files

dialog boxes, newest files, explorer windows: I had to think about this one - in Word 6, you could set the order and that has now gone - BUT that is because the File Open and File SaveAs dialog boxes are really mini explorer windows - SO as long as you have the box in DETAIL view (i.e. the style where...

Excel: Save Excel file as new file, value rs, ado recordset

value rs, ado recordset, true field: Your code does not tell where the save is tried to (which drive/folder). This means Excel uses the default location or the current folder. Which is it (maybe best tto set that in the code too) and check if you have read/write/delete access to that folder...

Excel: Save Excel file as new file, value rs, ado recordset

value rs, ado recordset, true field: Hi Yamuna, First, let me make clear that I am not experienced with server-side procedures, so take the following with that in mind. I suspect the problem with the SaveAs is that the since xlApp resides on the ASP server, the SaveAs wants to save the...

Excel: Save File using Macro, saveas, false hope

saveas, false hope, c5: Tony You can use code like this: Sub TonyMa() Filename = Range( B4 ) & _ & Date & Range( C5 ) & _ & Range( A1 ) ActiveWorkbook.SaveAs Filename:=Filename End Sub That won t prompt the user for the name, but you can use something like this one...

Excel: Save macro in excel, cell a1, macro in excel

cell a1, macro in excel, dear bill: Details, details, details. On what drive letter does the folder exist? I made the assumption that is the C: drive - you do not say. Again, remember I can not see what you see and ALL I know is ONLY what you tell me Sub SaveFile() Dim FD As String...

Excel: Save as read only, options dialog, vba

options dialog, vba, file browser: Hi Wan, When you save the file using SaveAs you can save it as read-only by requiring a modify password. To do this, when you do SaveAs and get the SaveAs browser dialog, use the Tools button and select General Options. In the Save Options dialog that appears...

Excel: Save workbook without Microsoft Excel links, microsoft query, microsoft excel

microsoft query, microsoft excel, access database: Bill Before sending the workbook, select the data, then do Edit= Copy, then, without changing the selection do Edit= Paste Special and select values. This will break the link to the data. If you don t want to do this in the original workbook, make a copy...

Excel: Save workbook without Microsoft Query links, microsoft query, ms query

microsoft query, ms query, quess: I have never had cause to use MS Query (to get data from Access I ve pasted the query as a link or used VBA to interogate the database) - HOWEVER, my QUESS would be that if you select the queried data, copy it and do a paste special as VALUES - paste onto...

Excel: Excel VB, excel vb, vb programs

excel vb, vb programs, macro in excel: Hi, I am just guessing what you are asking exactly. Here are my two interpretations. 1) Using code to open another workbook is easy: Workbooks.Open Filename:= yourfile_with_path.xls Windows( Book1 ).Activate to return 2) If your problem...

Excel: Save As, time phil, dfas

time phil, dfas, slagle: Upgrading from an earlier version to a newer version is not and will not cause this problem. If it worked or did not work with the older version you should get the same results. Since I have not seen the code then I can t confirm your suspicion. But as...

Excel: Saving cached links into Excel, good answer, excel 2000

good answer, excel 2000, spreadsheet: Dinah Sorry I ve not done what your requesting so I d have to do some research to figure it out. As you might have noted in my resume I m a CPA, and I m real real busy right now so I really just don t have the time to do that research. I suggest you...

Excel: Saving to Desktop, wscript shell, wshshell

wscript shell, wshshell, location thanks: Try this Sub saveToDesktop() Dim WSHShell As Object Set WSHShell = CreateObject( Wscript.Shell ) Dim fPath As String Dim fName As String fPath = WSHShell.SpecialFolders( Desktop ) Input box to name new file ...

Excel: Saving two different versions of same spreadsheet in 2 different locations, excel spreadsheet, saveas

excel spreadsheet, saveas, documents and settings: Sarah I m not a PDA expert, so i don t know if there is anything special about the Macro that you are using relative to PDA s, but it doesn t look like it to me. I really don t believe you need to use a macro to do what you need to do. it looks like...

Excel: Saving EXCEL spreadsheet in two locations, excel spreadsheet, blow description

excel spreadsheet, blow description, folder c: Sub Savingtwofolders() chDir1 = C:1Sarah... chDir2 = C:Documents and Settings... Filename = Accommodation.xls ActiveWorkbook.SaveAs Filename:=chDir1 + Filename ActiveWorkbook.SaveAs Filename:=chDir2 + Filename On Error GoTo File_Error ...

Excel: Saving in Excel, info buttons, checkbox

info buttons, checkbox, boolean: Assuming it is a check box (you never did say) and the check box is from the Forms menu and its name is Check Box 1 and it is on Sheet1 (none of which you provided) then use the following. Be sure to put this code in the ThisWorkbook or it won t work since...

Excel: Saving excel sheet, hi bob, excel sheet

hi bob, excel sheet, data changes: Hi Bob, You can copy the data from the selected sheet and when you paste the data on to the new file/sheet, be sure you choose Edit, Paste Special (rather than just paste) and select Values. Keep in mind that this will wipe out any formulas and leave...

Excel: Saving a file, sonia sonia, empty workbook

sonia sonia, empty workbook, shift key: Sonia, No, you would have to save it as a separate file. Then you could open both files in a single instance of Excel (using File= Open in Excel). Then tile the windows so the workbooks are side by side (Window= Arrange= Vertical) and you can select the...

Excel: Saving files in excel, worksheet menu bar, custom document

worksheet menu bar, custom document, document property: Hi Jim, Yes, this is possible, but requires a bit of VBA code. Here s how: 1. Go to the Excel File Properties Custom tab. Select Destination in the custom document properties list. Type your other file path C:shared in the Value textbox and...

Excel: Saving formatting as .CSV, ms excel 2000, csv file

ms excel 2000, csv file, upc number: Nathaniel This is a common problem with Excel and CSV s, when you open the file in word it shows the leading zero s, when you open it in excel it converts the text to numbers and drops the leading zero s. The easiest way is to not save as a csv but a txt...

Excel: Saving formatting in excel, hi bob, excel workbook

hi bob, excel workbook, scroll bars: You would need to do all of what you did through VBA code -- for example, right-click the excel LOGO near the File menu, select View Code, enter this (for a starter): Private Sub Workbook_Open() With ActiveWindow .DisplayGridlines = False...

Excel: Saving a partial file, saveas, windows xp professional

saveas, windows xp professional, spreadsheet: Robert I generally like to just copy the sheet in question to a new book, then save that new book as whatever you like. That would look like this: Sheets( Sheet1 ).Select Sheets( Sheet1 ).Copy ChDir C:Temp ActiveWorkbook.SaveAs...

Excel: Saving selected Models and selected worksheets to new workbook, sheet names, newname

sheet names, newname, input box: Models isn t the right word. We re talking about MODULES. Here s some sample code that will take the module named Module1 from the active workbook, and copy it into a different workbook named Book2.xls that s located in folder C: emp. By adapting this...

Excel: Saving an un-password protected copy of a worksheet, goto tools, lostpassword

goto tools, lostpassword, saveas: If you can open the workbook, then you can remove book and sheet level protection using a macro at JE McGimpsey s site http://www.mcgimpsey.com/excel/removepwords.html Again, you can only use this if you can open the problematic file in Excel. If...

Excel: Scatter chart with columns instead of points, scatter chart, column chart

scatter chart, column chart, numerical grade: If you make it a column chart to start with, but temporarily change the grades to a date format, then you should be able to select a time-series chart: Chart/Chart Options/Axes tab, select Time-scale. Then reformat the dates as general and enter the real...

Excel: Scatter chart with columns instead of points, secondary axis, scatter chart

secondary axis, scatter chart, column chart: You can usually right click on a bar and format the individual bar. So if you set up your series to display a bar at that location. You could probably use the secondary axis. You could manually draw a line and put a label on it perhaps to show what...

Excel: Scatter charts, cost benefit analysis, scatter charts

cost benefit analysis, scatter charts, colour bands: Maybe you can use a bubble chart which is a scatter with a 3rd factor -- bubble size. You can assign sizes to departments(instead of colors), like dept1 is 1, dept2 is 2, etc. and when you include them in the chart, you can put a textbox inside indicating...

Excel: Scatter Plots, plot chart, space constraints

plot chart, space constraints, scatter plots: Hi Kerry, I m not sure there is a dynamic/interactive way to zoom in on a chart like you are hoping. The best solution I can think of is to make a second copy of the chart and keep it on the same worksheet and change the scales for both the x and y coordinates...

Excel: Scatter plot, excel charts, info charts

excel charts, info charts, scatter plots: Charts are not things I use very often, but I have some saved reference pages which might help - these are http://processtrends.com/ http://peltiertech.com/Excel/Charts/index.html http://www.andypope.info/charts.htm http://www.stfx.ca/people/bliengme/ExcelTips.htm...

Excel: Scenarios, scenarios, a5

scenarios, a5, cells: All a scenario is is a way of SAVING sets of values. For example, you can put 1,2,3,4,5 in cells A1:A5, and save that set of values with a name, then you can have 6,3,8,1.5,9 in A1:A5 and save that set with a different name, and have 1,2,3,4,5 in cells A1:E1...

Excel: Schedule of months, aidan, a2

aidan, a2, montreal: I m sure there are various ways to do this, but the quickest method I can think of is to start with a known date (in this example in cell a1) then put the following formula in A2 and drag down =A1+32-DAY(A1+32) this will add enough days to guarantee...

Excel: Scheduled Printing, time dave, amount of time

time dave, amount of time, colleague: Hi Dave, I asm sorry but I cannot help you much ... I am a bit busy but I will give you this hint to start with Private Sub SettingHour() Rem It runs printing at 9:30 every day. You have to define Monday at 9:30 by adding a certain amount of time...

Excel: Scrollbars within spreadsheets, column headings, column numbers

column headings, column numbers, format menu: Jason, my first impression is that the task is going to be a bit tough. However, it is possible to load a form based on certain cells, rows, or columns, and to make the rest of the worksheet hidden or covered by the form. The size of the form dictates how...

Excel: Scrolling Text input box in 2007, vertical scroll, input text

vertical scroll, input text, input box: The method (I think) that I described has not changed in all versions of Excel from 5 onwards. There is an issue with a vertical scroll in that getting excel to realise it SHOULD scroll can be a problem. If you can email me a sample file I can check it further...

Excel: Scrolling text box, square icon, arrow keys

square icon, arrow keys, scroll bar: You need the control toolbar - so you were right there. You then right click on the text box (having placed it) to view the properties - which will allow you to set the appropriate scroll value. Finally, you need to take the spreadsheet out of design mode...

Excel: Scrolling text box, control toolbox, arrow keys

control toolbox, arrow keys, scroll bar: What you asking about is called rich text formatting. The control toolbox controls don t support this. The original textbox you spoke of, from the drawing toolbar, does support this, but I don t see any attributes that would allow you to have a visible scrollbar...

Excel: Scrolling text box, arrow keys, hi aaron

arrow keys, hi aaron, listbox control: Hello Al, There is indeed a way to do it, but it s a little tricky. Doesn t involve a textbox actually. Instead you d use the ListBox control from the Controls toolbar. Then you enter the text in a defined range (call it MyText ) within a single...

Excel: Scrolling text box in excel, scroll control, arrow keys

scroll control, arrow keys, piece at a time: I am not sure if this is what you want, but it involves scrolling and text. I am supposing that you have a big piece of text, and you want a scroll control to show a bit at a time. If this is not it, please give more details. What I would do is to separate...

Excel: Scrolling text box, arrow keys, control toolbox

arrow keys, control toolbox, scroll bar: Al Sorry I can t give you a really good answer on this, but--yes you can do it. I ve done in the past what you need with the spin button from the control toolbox. This works great with it, but I can t remember exactly how I set it up. I could refigure...

Excel: SEARCH function ??, r2 value, colorindex

r2 value, colorindex, cell interior: Janet, first, make a copy of your workbook so you are working on a copy and not the original. This way if anything bad happens, you know you haven t lost your data. Now open the copy of your workbook. Make sure it is the only workbook open in Excel....

Excel: SEARCH Text within an IF loop, cell b11, vlookup function

cell b11, vlookup function, excel sheet: David I approached the solution a little differently, and I hope I understood the question. As I understand it if none of the cells contain x, Improved then there will be no changes in the cells however if x, improved is present then all cell with...

Excel: SEARCH, douglas m smith, vlookup formula

douglas m smith, vlookup formula, brainbench: Hi, If you used a VLOOKUP formula: =VLOOKUP(A6,$A$1:$A$5,1) then result would the closet lower value, in your case, 25. Since you want the higher value, it gets much more complicated: =IF(A6 VLOOKUP(A6,$A$1:$A$5,1),INDEX($A$1:$A$5,MATCH(VLOOKUP(A6,$A$1:$A$5,1),$A$1:$A$5,0)+1,1),VLOOKUP(A6,$A$1:$A$5,1))...

Excel: SERIALITY CHECK, invoice numbers, legacy system

invoice numbers, legacy system, check answer: Kazaure, my understanding was that you wanted to know where the missing numbers were located. The answer that I gave you completely solved your problem as I understood it. I see from your rating comment that you still need more. You re welcome to write...

Excel: Search in Excel sheets, sheet tabs, visual cues

sheet tabs, visual cues, group mode: Hi, Sanjay, I m glad to tell you that yes, you can do this. There actually is a way to search and make edits to more than one sheet at once, but it s a little dangerous. I ll explain it to you, but I want to caution you to be careful with it. Here s...

Excel: Search and Find, rcell, many different ways

rcell, many different ways, wildcard character: Look at the line of your code that says If rCell(1, 13) Like StrFind2 Then At this point, rCell is the cell in col 12 of rRange in which you ve found your StrFind1. So the line of code as you ve written it says If the cell 12 columns to the right of...

Excel: Search and Find, rcell, many different ways

rcell, many different ways, wildcard character: Ken, It isn t clear what column the second value is in since you haven t provided what rRange refers to. if you are searching in rRange.Columns(12) and then looking for a second match in rcell(1,13), then for illustration, let s say set rRange = Range(...

Excel: Search formula, cell c1, cell b10

cell c1, cell b10, cell d1: From the formula you have given me, you have nearly got it correct BUT there is a missing comma - it should be =SUMPRODUCT(--(NOT(ISERROR(SEARCH(B10, 1 !H2:H10000)))), --( 1 !A2:A10000 =A14),--( 1 !A2:A10000 =A15), 1 !K2:K10000) (formula broken...

Excel: Search formula, cell c1, array formula

cell c1, array formula, array formulas: I believe I have it: =SUM(IF( 1 !A2:A10000 =C1,IF( 1 !A2:A10000 =D1,IF(ISERROR(SEARCH(B1, 1 !H2:H10000)),0, 1 !K2:K10000),0))) This is an array formula, so be sure to use the CTRL-SHIFT-ENTER to get the braces around it. Notes: A1 contains the formula...

Excel: Search function on my excel spreadsheet.?, false selection, minor fault

false selection, minor fault, cell reference: Have you tried using Advanced Filter instead in your macro? It seems that is what you need. Look in help for how to setup and use Advanced Filter, which requires a separate area where you will have to copy the column headings or field names which will be...

Excel: Search function, command button, command buttons

command button, command buttons, search function: No, this is not possible since Command buttons don t do anything UNLESS a macro has been assigned to them. Therefore a macro will have to be written and then assigned to the command button. Here is some code (untested) that should work Sun Find_A Cells.Find(What:=...

Excel: Search functions..., false application, sheet tab

false application, sheet tab, internal departments: Sorry this took so long - was away. I don t understand where the #s 9.99, 16.77, 1.29 are coming from. The other totals can be done with this code, which assumes the first sheet is named Sheet1 and the 2nd is named Sheet2: Right-click the sheet tab, select...

Excel: Search in hidden folders, hidden folders, filesearch

hidden folders, filesearch, hidden folder: This is a new question that I haven t encountered before... but I m pretty sure the following is correct: .FileSearch will never include hidden folders. So you must first unhide the folder, then do .FileSearch, and then hide it again after. For instance:...

Excel: Search and replace prob, format cells, numerical entries

format cells, numerical entries, cell content: The easiest fix is to use a blank column and put a formula like this in it =value(RIGHT(A1,LEN(A1)-1)) (this assumes that it is A1 that is being fixed) This will give you the results you need - you can then copy this column and use Edit Paste Special...

Excel: Search using more than one criteria, microsoft excel 2000, array formula

microsoft excel 2000, array formula, worksheet functions: hi paul, there are two options as I see it: 1. Create an extra column (leftmost) and concatenate the 2 columns you want to search on. Like B2&D2 copied down for the entire range, then use vlookup as =vlookup(lookup value concatenated,lkptable-first...

Excel: Search with VBA on recursive sheets FOLLOW UP, leters, weird reason

leters, weird reason, vba: Well, it depends on what you want to achieve. My initial understanding what that you wanted to check on one sheet, and then return everything from every sheet if the check was true. Now, it looks like you want to perform the check on every sheet, and move...

Excel: Search with VBA on recursive sheets, leters, result table

leters, result table, vba: Hi Blue Thanks for clarifying that. Ok so I can set up some code which 1) Asks for input a day number. 2)Checks eachsheet in the workbook for that number. 3)If found copies the previous and next row A-J to a results sheet and appends the...

Excel: Search with VBA, task selection, sheet1

task selection, sheet1, range selection: Hello Nathan SOme code for you It assumes numbers in col A of sheet1 and text in col B of the same sheet If data is found it accumlates it on sheet 2 column A Any problems or questions just let me know Regards Des Rem TRAP SEARCH...

Excel: Searching/Extracting/Changing Text within Cells?, vlookup function, random number generator

vlookup function, random number generator, sequential numbers: I m not at all sure I follow the question - you have a random number generator to get a number within the specified bounds - you use vlookup with this (which by the way is overkill) to get an item and a price - two different numbers would give two different...

Excel: Searching for a excel cell using two criteria, target values, dallas department

target values, dallas department, rng: Hello Robert, This should be the basic code you need. Basically, you find all the target values in column 1 until you find one that has the corresponding value in the other column. In the sample, I look in columns 1 for CritA and in columns 2 for CritB....

Excel: Searching And Matching Data, vlookup formula, ogilvy

vlookup formula, ogilvy, alphabetical order: assume bk1.xls, sheet1, column A and bk2.xls, sheet1, column A in bk1, sheet1 some column to the right of A, in row 1 of that column, enter =countif([bk2.xls]Sheet1!$A:$A,A1) then drag fill this down the column next to your data. in bk2,...

Excel: Searching in Range, logical result, dog collar

logical result, dog collar, ogilvy: The easiest I would think is to use countif =if(Countif(A1:C3, *Dog* ) 0,1,0) if the cell will contain only exactly the single word Dog then =if(Countif(A1:C3, Dog ) 0,1,0) this will work for a more complex string =if(Countif(A1:C3, *Studded...

Excel: Searching tables and arrays in Excel, matchentry property, combobox

matchentry property, combobox, correct word: Hello Charles. The best and, I think, only option is a Combobox from the Controls Toolbar. You need to set the ListFillRange Property to the Range that holds your list and also make sure the MatchEntry Property is set to 1-frmmatchEntryComplete. Now...

Excel: Searching tables and arrays in Excel, dialog sheet, printdlg

dialog sheet, printdlg, arros: What you need to do is revise the Sub SelectSheets() code with the 9 lines of code in the NOTE at the bottom. These 9 revision lines go in between the line If SheetCount 0 Then and the line else . This is what that section of code will end up looking...

Excel: Searching tables and arrays in Excel, control toolbox, vb scripts

control toolbox, vb scripts, drop down boxes: Hello Charles, there are many web sites John Walkenbach: http://www.j-walk.com/ss/excel/tips/index.htm and http://www.j-walk.com/ss/excel/links/index.htm Links to other sites Also see John Walkenbach s books. His Power Programming book is one of...

Excel: Searching tables and arrays in Excel, listboxes, keyword searches

listboxes, keyword searches, arrays: The answer to your specific question is yes. You don t say how or whee you put these controls. Are they on a worksheet or a userform? If on a sheet, with which menu/toolbar did you use to place these controls? Finally drop-down controls can be either...

Excel: Searching for text strings in rows that also contain cells meeting other criteria, contamination incidents, recycling company

contamination incidents, recycling company, recycling bins: Here s an example that will hopefully give you the understanding to create any formula of this sort. Suppose that you have data in range A1:B50. You want a count of all rows in which Col B shows a date in January, and the text in Col A contains either the...

Excel: Searching a value in a matrix?, e5 e6, g5 g6

e5 e6, g5 g6, hlookup: Nayyier There may be an easier way, but this is how I would do it. First use the match formula to find the row +MATCH($F$19,D3:J3,0) Where F19 contains the cell value I want to match. put this type of formula in a column on each row that contains...

Excel: Securing workbooks from templates, percentage discounts, quoter

percentage discounts, quoter, prospective customer: Hello, Dustin, I m a little lost here. If you want to protect the fields from the salespeople, then what do they need the password for? If you want to protect the fields from the customers, then give the password to the salespeople, and have them protect...

Excel: Security with worksheets, hyperlink, hyperlinks

hyperlink, hyperlinks, confidential information: Amy You can restict access to a worksheet in 2003 and later versions, Toolbar/tools/protection/protect sheet. I do not know if the sheet will still be protected if you access it via a hyperlink or if it will give them the error message. I suggest you...

Excel: Seeking Exporting and Importing VBA Help, csv file, file in c

csv file, file in c, inputbox: Ken, you can use csv file types just like they were excel files. You can save direct, and you can open without hesitation. Excel saves the current worksheet with simple code like this: ActiveWorkbook.SaveAs Filename:= C:Tempken2.csv , FileFormat:=xlCSV,...

Excel: Seeking Help With Chart Macro, vba code, sheet1

vba code, sheet1, series names: 1 - in your example, you Dimmed CValue1 several times and assigned it several times as well. so XValue2 & 3 never get any values. 2 - the chart needs to already exist with 3 series in it before this macro runs. You can t say ActiveChart.SeriesColleciton(1)=XValue1,...

Excel: Seeking Help to Free Up System Resources, xls file, mb memory

xls file, mb memory, ken ken: Ken, Hmm... there aren t really any settings besides autorecover that help free up memory in Excel. As to question #1... You probably added more programs or software of any kind that run constantly in the processes window of the task manager (Ctrl+Alt+Del)....

Excel: Select based on date, colums, ogilvy

colums, ogilvy, wich: Gianluca, Sure, send me a copy of your workbook. I am not sure what you mean by a form - where you want to put your dates. If you want to put in dates and have it automatically print without doing anything else, this would have to be done with a macro...

Excel: Select Case, integer row, car case

integer row, car case, case statement: Hello Daniel, I would do it this way: Dim quoi As String Dim row As Integer row = 1 Do While Cells(row, 4).Value quoi = Cells(row, 4) Select Case quoi Case Is = Honda Cells(row, 7) = Car Case Is = Airbus...

Excel: Select Data between two date range, d2, match

d2, match: If you put your from/to dates in D1 & D2, name a range, say values , to be: =OFFSET(Sheet1!$B$1,MATCH(Sheet1!$D$1,Sheet1!$A:$A,0)-1,0,MATCH(Sheet1!$D$2,Sheet1!$A:$A,0)-MATCH(Sheet1!$D$1,Sheet1!$A:$A,0)+1,1) Then you can Edit/Goto Values to select that...

Excel: Select second duplicate item in listbox., colomns, thanks in advance

colomns, thanks in advance, von dutch: Not sure what you are asking. Are you concerned about the duplicates and need to know how to populate the listbox with non duplicate items? If this is the case and then when you select a name, which name s corresponding data should the textboxes display...

Excel: Selected info for Combo boxes, ymba, combo boxes

ymba, combo boxes, control toolbox: assume two comboboxes with combobox1 having its listfillrange (worksheet) or rowsource (userform) bound to the left column of numbers your data shows. so in the click event Private Sub combobox1_click() use listfillrange for combobox on a ...

Excel: Selecting a cell from a text box, control toolbox, speadsheet

control toolbox, speadsheet, commandbutton: the basic approach would be (textbox from the control toolbox toolbar) Dim s as String s = worksheets( Sheet1 ).OleObjects( Textbox1 ).Object.Value Range(s).Select or if the button is a commandbutton from the control toolbox toolbar, it might be possible...

Excel: Selecting cells from other sheets in macro., macro menu, xf

macro menu, xf, excel 2000: Roi, I m not sure what to tell you. There is nothing wrong with the lines of code you have provided. I copied your lines of code verbatim and pieced them into the subroutine below. The sub ran fine both from VBA and from a button on sheet one. ...

Excel: Selecting Data, ms knowledgebase, xcell

ms knowledgebase, xcell, control thanks: Use the code below, modified from MS knowledgebase. Alt-F11 takes you to the VB editor. Insert module and copy the code below. Sub SelectEveryNthRowInUsedRange() Initialize ColsSelection equal to the number of columns in the selection. ...

Excel: Selecting a file, data excel, application path

data excel, application path, fto: You say you want it to open up with the form. The form has got to be started by something, hence the tools menu. Change your code to: Dim FTO As String ChDrive C Chdir C:DataExcel sets the directory FTO = Application.GetOpenFilename...

Excel: Selecting hidden worksheets in code, false selection, hidden answer

false selection, hidden answer, strikethrough: Hi Anthony, Here is how I would modify your code to eliminate the selections: Dim DestWS As Worksheet the destination (paste) worksheet ActiveSheet.Unprotect With Sheets( 010-Global ) Set DestWS = Sheets( 2007 CPT Update ) ...

Excel: Selecting only Cells with Data, blank cells, red cells

blank cells, red cells, safety measure: Judy, I would suggest that you go to this site: http://j-walk.com/ss/pup/pup5/index.htm and download the utility pak that John has put together. In that pak, you will find the ability to select a range, and then have it select all of the blank cells...

Excel: Selecting a Range, down arrow key, blank cell

down arrow key, blank cell, shift key: Hi, Lynda, Let s say that your data goes from B2:B13348. You re sitting in B2 and you want to select from there to the bottom. Simply hold down both Shift and Ctrl, press the down arrow key, and you re instantly there. If there is a blank cell along...

Excel: Selecting a range dynamically, word hope, excel sheet

word hope, excel sheet, first appearance: One way of doing it is by cutting and inserting the full column, something like: Sub MoveColumns() Columns( C:C ).Cut Columns( A:A ).Insert Shift:=xlToRight End Sub This will take the C column and put in on place of the A column, shifting...

Excel: Selecting range, keyboard shortcut, background color

keyboard shortcut, background color, thanks in advance: Here is one way of many. Dim CellSelected As Long CellSelected = ActiveCell.Row Range( F & CellSelected).Resize(1, 251).Select This will select all cells in the row where the cursor is from column F to column IV and not select columns A thru E....

Excel: Selecting ranges in excel spreadsheet with VBA, screen flicker, excel macros

screen flicker, excel macros, blank cells: First of all, you RARELY need to have VBA select a cell(s) to do anything to it or with it. Doing so is usually redundant and is more of a burden on the system, though very very slight. In addition, not selecting a cell creates less screen flicker and movement...

Excel: Selecting smaller numbers in one column that equals larger number in another column, cell a1, different numbers

cell a1, different numbers, gbl: See this link for a solution I suggested to this type problem. Some other contributors (Dana De Louise) added some additional information. Harlan Grove pointed out that this would not be practical for large combinations of numbers. Nonetheless, this can...

Excel: Selecting & using content from AutoFilter list, stephen bullen, case operator

stephen bullen, case operator, string filter: James, This would require using VBA to create a function that can be used in the worksheet. If this is something you think you are familiar with, then Function FilterCriteria(Rng As Range) As String By Stephen Bullen Application.Volatile...

Excel: Selection.Copy Destination, copy worksheets, active range

copy worksheets, active range, cell reference: Try this, I can t test it without the workbooks but it should be basically OK Option Explicit Sub Combine() Dim rToCopy As Range Dim J As Integer On Error Resume Next Worksheets( Merge ).Range( A2:AB65536 ).Clear ...

Excel: Selection ? in loop, default property, cell a1

default property, cell a1, master database: Hi Dave, The offending line should stop offending if you change it to: .Offset(0, 7).Value = .Offset(0, 7).Value + 1 This is because rLoopCells.Offset(0, 7) is the cell (a Range object) 7 columns to the right of cell rLoopCells. And adding .Value...

Excel: Selection Question, false selection, last selection

false selection, last selection, copy mode: Hi If it is up to the ussr when they activate the macro having perhapes moved the cursor then you have may have difficulties retaining the copied region location. Asking people to select do a copy action then run a macro is asking for problems really...

Excel: Send automatic mails with excel macros, chip pearson, excel macros

chip pearson, excel macros, de bruin: Gabriele, Ron de Bruin has about everything you need to know about mailing/excel and VBA http://www.rondebruin.nl/sendmail.htm You might want to also look at Chip Pearson s page on using events in Excel http://www.cpearson.com/excel/events.aspx ...

Excel: Sending E-mail to different e-mail acct, e mail account, default mail

e mail account, default mail, mail settings: Cheri Excel uses the default mail server that is on your system, if it is Outlook you need to go to the e-mail settings Go to the Start menu, point to Settings, click Control Panel, then double-click Mail And Fax or Mail , and finally choose the E-Mail...

Excel: Sending mails, free time

free time: Sorry, I don t just write macros for people on this site whenever someone says he needs a macro to do a specific task. I volunteer here to answer questions not be a macro writer for everyone. I am sure you can understand since I volunteer on my own free...

Excel: Sending Outlook tasks via VBA, excel vba, windows xp sp2

excel vba, windows xp sp2, task request: Perhaps this will work: Sub TestTask() Note: You must first manually bind the Outlook 10.0 Object Library by going to Tool, References in the VBA editor (this is for Outlook 2002). Otherwise the code below will not work! Dim olApp As Object...

Excel: Sendto macro question, jane doe, excel workbook

jane doe, excel workbook, subject text: Assuming workbook is a variable referencing the workbook to be sent, then further assuming the subject text is in the first worksheet in the tab order in cell A1 then Workbook.Sendto Jane@doe.com , Workbook.Worksheets(1).Range( A1 ).Text for the text...

Excel: Separate Windows, douglas m smith, excel spreadsheets

douglas m smith, excel spreadsheets, web page link: Hi Richard, Normally, when you click on a web page link, it will open the link in the existing window unless the website has set it up to open a new window. However, you can usually force it into a new window by right-clicking on the link and selecting...

Excel: Separating addresses from within a block of cells, street address city, address city state

street address city, address city state, shipping labels: Hi Boots, The only simple way is to have your data layout consistent. Not wanting to put a damper on your situation, but I rather doubt there is consistency in what you describe. You would need to find something that separated the elements you want...

Excel: Separating Columns with Spaces, finance option, menu choice

finance option, menu choice, delimited: I m ASSUMING that the one cell contains a fixed width, OR the only place a space occurs is after each field of data. IF SO, then make sure you have enough blank columns to contain your data - so in your example, columns B,C,D and E would need to be blank....

Excel: Seprate numbers, cell c1, cell a1

cell c1, cell a1, column c: Give this formula a shot in column C: Place this one in cell C1 and copy down as far as you need (the A1 will update to A2, A3, etc as you copy down): =IF(ISNA(MATCH(A1,B:B,FALSE)),A1, ) This formula essentialy attempts to find a match of the value...

Excel: SeriesCollection, data cells, cross tab

data cells, cross tab, general idea: Hi, My understanding of your problem is this: a. you have a cross-tab of data, say columns are years , rows are months and the values are the number of boxes . b. You have a series of line charts - for example one series per year, month is your x axis...

Excel: Server Based Excel Question, very time consuming, google

very time consuming, google, customer number: The obvious solution, as it seems to me, is the relational database but you said that you can t afford that or spare someone s time to design it. So, the next perhaps not so obvious solution IF you continue to use EXCEL is to write macros that will solve...

Excel: Set Go To (Cell) when we Click on Button, cell reference, menu tools

cell reference, menu tools, cursor: You can create a macro like this: Sub GoToCellX Range( A10 ).Activate End Sub And assign it to the button. You can also create the macro by recording (menu Tools- Macro- Record new macro) the action of selecting the cell. Hope this helps, ...

Excel: Set Go to Cell Reference in Button, cell a1, cell reference

cell a1, cell reference, cell address: Hi Parantap, If you have selected a button from the Forms toolbar. You will need a Macro like this. When the worksheet is selected you need to press Alt+F11 to open the Visual Basic Editor and then select Insert then Module then paste the following...

Excel: Set Initial Folder, folder c, dialog box

folder c, dialog box, folowing code: Example: to open a dialog box showing Excel files in the folder C: emp, use code like this: Sub browseToFile() Dim selectedFile ChDir C: emp selectedFile = Application.GetOpenFilename( Excel Files (*.xls), *.xls , , _ Select...

Excel: Set Initial Folder, initial path, c drive

initial path, c drive, correct answer: Terence, I noticed you are still looking for an answer to your question. I apologize - I only had xl2000 on hand and when I was working your question and this feature wasn t added until xl2002 - so I couldn t check it out and forgot to pursue it. Anyway,...

Excel: Set MsgBox location on screen, home office computer, excel vba

home office computer, excel vba, absolute genius: Sorry - Msgbox can t be positioned with VBA. You can, however, create a Userform which looks just like a Msgbox and whcih you can place wherever you want, and you can have any buttons. And thanks for the ending comment! I am teaching a Master Excel...

Excel: Set up a master spreadsheet., spreadsheet history, master spreadsheet

spreadsheet history, master spreadsheet, model calibration: Assuming the master is a separate spreadsheet formatted the same as the template, and assuming the template s 1st row is column headers, you can do something like this in a macro - this also assumes the workbook ( history of Unit.xls ) is open and the template...

Excel: Set Up Standard Output Printing, printing sheet, zero values

printing sheet, zero values, use tools: Use a second sheet as the printing sheet. You only need to set up 4 formulas in Sheet2: A1:=OFFSET(Sheet1!$A$1,INT((ROW()-1)/36)*36+ROW()-1,0) B1:=OFFSET(Sheet1!$B$1,INT((ROW()-1)/36)*36+ROW()-1,0) C1:=OFFSET(Sheet1!$A$1,INT((ROW()-1)/36)*36+ROW()+35,0)...

Excel: Set sheet to display first when opening excel, left hand corner, presentation purposes

left hand corner, presentation purposes, brainbench: Emma, This can be done with VBA using the following steps: 1. Select Tools Macro Visual Basic Editor to open the Visual Basic Editor 2. In the left margin all of your worksheets will be listed. Double click each one individually and paste the following...

Excel: Set viewing page, excel spreadsheet, scroll bar

excel spreadsheet, scroll bar, computer screen: I think I understand - IF I m right, when you press Ctrl End you will end up WAY outside the actual end of the spreadsheet - this basically means that Excel is confused about the size of the sheet - I can let you have a macro that tidies up documents like...

Excel: Setting Contstant Ranges for Macro Module, input parameter, excel 2002

input parameter, excel 2002, msgbox: Hi Nick, How are you? You can parameterise the macros so to have an input parameter that represents the range they are to process. Just use in the macro something such as: Sub procedure1(R) Msgbox R.address .... end sub This procedure1...

Excel: Setting cell range using another cell, e61, cell references

e61, cell references, cell reference: Long question with nice easy short answer! The formula you want is INDIRECT which will take any amount of cell references or literal text to create a valid reference so =indirect( E & A584) will give you the results of cell reference E10, if A584...

Excel: Setting up a Data base with names and numbers, cell b1, vlookup function

cell b1, vlookup function, player numbers: Hi Garry, The VLOOKUP function works well for this. Let s try this. On Sheet1 cell A1 you are going to type in the number of the player, in cell B1 you will have the cooresponding name appear with the formula. So On Sheet2 lets enter the players numbers...

Excel: Setting a List value to a user selection from a ComboBox, worksheet cells, cell a1

worksheet cells, cell a1, combo box: Example: on your userform, you ve got a combobox named ComboBox1. When you click the OK button on this userform (we ll assume the button s named CommandButton1) on this userform, you want whatever value s been selected in ComboBox1 to appear in cell A1 of...

Excel: Setting named range in VBA, macro recorder, excel vba

macro recorder, excel vba, ogilvy: The easiest way is to do with Worksheets( Sheet2 ) .Range( B1:B10 ).Name = MyNamedRange End with msgbox Range( MyNamedRange ).Address(0,0,xlA1,True) You can also use the ADD method for names. The easiest way to see the syntax for this (and...

Excel: Setting Print Areas with VB, string s1, number 0

string s1, number 0, string set: Brendan, There is a difference between setting the PrintArea and defining how the page will break. If you just want to Print A1:D15 you can do Activesheet.Range( A1:D15 ).Printout And you don t even need a PrintArea Defined. if you want to set...

Excel: Setting up percentages of completion, underground electrical conduit, linear footage

underground electrical conduit, linear footage, proper functions: Can you set up a table with a column for the different parts, column 2 for the total required, then a third column for completed work. Then the fourth column would have a formula to calculate the percentage (completed/total*100). Then at the bottom of column...

Excel: Setting up a program, milk delivery, delivery days

milk delivery, delivery days, degree student: Hi Jacquie, I would start by entering what information that you have into row1 such as the list areas, customers, etc. then the cooresponding data directly below that. Selecting the entire range of data go to Excel menu and select Data then PivotTable...

Excel: Setting Range, address string, excel macro

address string, excel macro, syntax: There are two mistakes in your code. First of all, you cannot assign a string to a Range object variable, you must declare a separate String variable if you want to hold the range address string. Also, when you use a string variable you don t need the extra...

Excel: Setting up a Spread Sheet in Excel, spread sheet, answer phones

spread sheet, answer phones, e mail: Vicki This should be a pretty easy application, however there are several ways that it can be done, and without knowing how it was done in the past we run the risk of creating problems with the old data. I m guessing that you are correct, the prior months...

Excel: Setting up a separate "History" file, example model, paste code

example model, paste code, xxxxxxx: Russell, I put some code together that should do the trick for you. I had to guess on the cell locations so make sure those make sense in the code. I also added a line that forces the user to enter data into each of the 4 cells. You will need to edit the...

Excel: SHEET TO SHEET FUNCTIONS, exact number, f1

exact number, f1, countif function: The function you need (COUNTIF) doesn t work across sheets, only on 1 sheet. So, for each of the 30+ worksheets, you can use the COUNTIF function in the same cell. For example, if the column in question is C and the name is MATTHEW then you can use =COUNTIF(C:C,...

Excel: SHOWING AN ARRAY?, set of numbers, couple weeks

set of numbers, couple weeks, comcast: Ken, A form to simplify input... Sure, you can build just about anything you want. You ll probably have to spend some time understanding a few examples and perhaps reading up on an XL forum or two before diving into creating your own userform apps. ...

Excel: Shade Cell, conditional formatting, format button

conditional formatting, format button, drop down menus: Hi Roxanne, You can use Conditional Formatting for this. Select the cell that you want to evaluate. Then go to the Excel menu and select Format then Conditional Formatting... you will see two drop-down menus the first one should read Cell Value...

Excel: Shade a cell, header row, question thanks

header row, question thanks, professional edition: Hey Remon, I am not sure if I am understanding your question. Are you saying you want to recreate the header row that designates the columns by letter? I don t believe this is possible. But you can mimic what the actual header looks like for rows and...

Excel: Shading cells, douglas m smith, autoshape

douglas m smith, autoshape, format button: Hi Dennis, This sounds like a Conditional Formatting trick to me. Select a cell and then select Conditional Formatting... from the Format menu. In the dialog box, you should see the Formula is option selected and a formula that looks something like...

Excel: Shading in Excel, tom cannon, formula f1

tom cannon, formula f1, format button: Suppose you want columns G:I shaded when col F is blank. 1 - select G:I and give them the shading you d want to see 2 - when them selected, use Format/Conditional Format 3 - in the CF dialog, change Cell Value Is tp Formula Is 4 - assuming G1 is...

Excel: Shading between the lines, secondary axis, info charts

secondary axis, info charts, excel charts: The question is really good, and in fact I haven t been able to come with a complete solution. I could get to the point of shading an area in intervals, but couldn t get it to fit the Blazier curve. If you want to take a look, you can check it at: http://zapicm.googlepages.com/TimingCurve.xls...

Excel: Shading between the lines, info charts, excel charts

info charts, excel charts, hardest time: I m not called upon to do much charting, so my expertise here comes from others - after much searching I found this thread http://groups.google.com/group/microsoft.public.excel.charting/browse_thread/thread/eab209f46c854846/f41ea8aaab735a94?lnk=gst&q=scatter#f41ea8aaab735a94...

Excel: Shading every other row, grand nephew, excel 2002

grand nephew, excel 2002, aidan: I know the sort of print you mean (easyEye we used to call it) and I can t see anything that would do it automatically in any version of Excel. You can CERTAINLY do it by shading the cells, and it would be possible to create a macro to shade the cells. I...

Excel: Shading on spreadsheet-Excel 2002, blank row, excel spreadsheet

blank row, excel spreadsheet, range selection: Rina, you have 2 options. If the current formatting isn t too big a deal, then you can highlight one row, and select it and a blank row, either above or below, and copy those, then select the rest of the worksheet, and Paste Special/Formatting. The more...

Excel: Shading on spreadsheet-Excel 2002, format button, excel spreadsheet

format button, excel spreadsheet, excel 2002: Select all the cells (ctrl/shift/spacebar), then use Format/Conditional formatting; change Cell value is to Formula is , then enter =MOD(ROW(),2)=0 then click the format button, patterns tab, pick a color (I like the lightest grey), then OK your way out....

Excel: How to Share a macro in Excel, microsoft excel 97, macro in excel

microsoft excel 97, macro in excel, addin: John, One way is to put it in a workbook and send the workbook to your colleagues. If they open your workbook, the macro should be available. A more formal way it to create an addin. here are some sources of information: http://support.microsoft.com/?id=156942...

Excel: Share a work book., data validation, experts exchange

data validation, experts exchange, warm regards: This sounds to be version specific and although I have office 2003 I have not yet installed it - this is the first question I ve had that is version specific in Excel! However, my warning about not sharing workbooks holds - I can refer you to this page ...

Excel: Shared Files, orginal document, desk top

orginal document, desk top, shared files: Unfortunately, the options of sharing and tracking changes need to be activated before the changes have taken effect. Both options can be accessed from the Tools menu. If there is a way of retrieving the original version without having done this before,...

Excel: Shared Workbook, error proof, proof method

error proof, proof method, eric eric: Eric apart from recreating the entire thing in Access the earlier way is the safest at the moment. You could try one of the online options (google sheets etc) depending on the complexity of the tables. I would also look at this link which is for Office...

Excel: Shared Workbook unnecessary files, server folder, excel workbook

server folder, excel workbook, unnecessary files: As I say, in our case it s a permissions thing -the users cannot save the original files, so Excel has to abort the save process - one POSSIBLE fix would be to have a macro which ran before save which STOPPED the file being saved, but that s only any good...

Excel: Shared Workbook unnecessary files, server folder, excel workbook

server folder, excel workbook, unnecessary files: Hi Ashvin How are you? You can for example create a macro and assugn it to a button that deletes all the previous files whose name starts with a certain string of characters. But I really do not know wheter this suits you. Cheers Adelaide ...

Excel: Shared Workbook unnecessary files, excel knowledge base, server folder

excel knowledge base, server folder, microsoft doesn: Hi Ashvin, I don t believe this problem has been reported to Microsoft (doesn t appear in the Excel Knowledge Base), and I haven t heard of it before. You didn t mention which version of Excel you are using, but I assumed it is either 2002 or 2003 since...

Excel: Shared Workbook, share drive, excel 97

share drive, excel 97, time out: Karen I haven t any experience with this problem but you may want to check that all the versions of Excel are updated to SR2. If you are running inoculan this can also cause problems but they do have a patch for it as well. Another option could be your...

Excel: Shared Workbooks. macro problems, currency format, compilation errors

currency format, compilation errors, active project: Michael. I am not retired. There was a lapse in our support contract and it has taken forever to recompete it. It looks like it will be re-awarded to use, but not sure when the effective date will be. should be soon. Shared workbooks have a lot of...

Excel: Shared worksheet sorting cells, columes, custom macro

columes, custom macro, topic features: There are a number of things that you cannot do whitin a shared book (see the help topic Features that are unavailable in shared workbooks ). This one is not listed there, but it should be, as the sort option works on full rows always on shared books. The...

Excel: Sharing of an ADD-IN, addins, calender

addins, calender, reply: You can use the Workbook_open event to activate the add-in, something like: Private Sub Workbook_Open() AddIns( Calendar ).Installed = True End Sub Be sure that the name of the add-in is correct, and the same for all the users, and also that they enable...

Excel: Sharing one excel documents over the network, schedual, access database

schedual, access database, vba: If the document is shared, it can be accessed by as many users as you wish - HOWEVER, there is an issue if more than one person wants to update the same cell - for this reason, I don t like shared workbooks very much!! You could have VBA that saves the file...

Excel: Sharing one excel documents over the network, excel workbooks, excel workbook

excel workbooks, excel workbook, schedual: Shared Excel Workbooks = VERY DANGEROUS I don t recommend it. Eventually one of you will write over the work of the other. It s not a matter of IF but WHEN... If you don t mind redoing things or losing data then you probably don t need to worry. A...

Excel: Sharing one excel documents over the network, schedual, target

schedual, target, jared: if you have excel XP or later, you can use tools/options/save and set up an automatic save there. Otherwise, you can use the tools/add-ins, and select autosave, then click on tools/autosave and set your options there. Lastly, for all versions, you can...

Excel: Sharing macro code, empty workbook, code excel

empty workbook, code excel, macro code: Hi Wilke, If you want to automatically include certain macros, user-defined functions (UDFs), etc., in new (blank) workbooks that you create probably the easiest way is to create an Excel template named Book.xlt that contains your macros (your VBAProject),...

Excel: Sheet Names with Dates, sheet names, current date

sheet names, current date, len: You can use the Worksheet_activate event, with a code like this: Private Sub Worksheet_Activate() If Right(ActiveSheet.Name, 2) Right(Year(Date), 2) Then If CInt(Right(ActiveSheet.Name, 2)) = CInt(Right(Year(Date), 2)) - 1 Then ...

Excel: Sheet tabs in Excel, excel mac os x, sheet tabs

excel mac os x, sheet tabs, mac os x: Hi Crystal, In Excel 2002 or later you are able to change the color of sheet tabs by right-clicking while hovering over a sheettab and clicking on Tab Color... and pick a tab color you like. White is the default tab color. I don t exactly know why your...

Excel: Sheets Tabs, sheet tabs, lvalue

sheet tabs, lvalue, sheet tab: Hi Keith, There are sort Macros out on the internet such as this one. Sub QuickSortSheets(Optional SortOrder) Dim i As Long Dim j As Long Dim SheetsCount As Long Dim FirstSheet As String Dim NextSheet As String Dim LValue...

Excel: Sheets Tabs, sheet tabs, graph

sheet tabs, graph: Keith I do not know of a way to sort the sheet tabs automatically. You can move them manually and put them in the order that you want. To move them manually left clic on the tab and hold down then drag the tab to the location that ou want. I am using...

Excel: Shell in Excel !!!, shell command, macro code

shell command, macro code, ogilvy: http://support.microsoft.com/?id=214248 XL2000: How to Force Macro Code to Wait for Outside Procedure http://support.microsoft.com/?id=129796 HOWTO: 32-Bit App Can Determine When a Shelled Process Ends http://support.microsoft.com/?id=147392 XL: How...

Excel: Shift cells, international prefixes, mobile indicators

international prefixes, mobile indicators, work phone numbers: Hi again Kevin, Okay, here is a macro that will handle the three phone number formats you mentioned. This code will shift the numbers from column A to column B. Sub ShiftTelNos() Dim iRow As Long Dim Cval As String For iRow =...

Excel: Shifting Data In Same Cell, target target, sheet tab

target target, sheet tab, ogilvy: John, Sounds like you would need to use VBA code and the Change event to do what you want if you want the value in the cell to actually change. You can t have one value and display another unless you are rounding. Right click on the sheet tab and...

Excel: Short Cut Menu, target range, macro work

target range, macro work, menu start: When you copy a sheet with a named range, you get a local name on the copied sheet. To ensure your macro references the correct name, precede it with the sheet reference, something like: If Union(Target.Range( A1 ), Activesheet.Range( data1 )).Address...

Excel: Shortcut commands for Excel, shortcut commands, brazilian guy

shortcut commands, brazilian guy, buttom: You can select the cell BELOW the cell to copy (hopefully it s blank), then use Ctrl/ (dbl-quote) which will pull the VALUE out of the cell, then you can cut/paste this value anywhere. Or, you can use ctrl/C, then click in the new location and press /esv...

Excel: Shortcut key, shortkey, shortcut key

shortkey, shortcut key, parenthesis: Harendra I m not sure I m answering your question, but a way to get the functions up and to fill in the blanks relative to the functions is as follows: Suppose I want to une the DCOUNT function. I type into the cell +dcount(). Then place the cursor...

Excel: Shortcut key, shortcut key, shortcut keys

shortcut key, shortcut keys: I haven t seen it done (and wouldn t want to do it) and cannot see it in the list of shortcut keys - so I ve no idea I m afraid - you could POSSIBLY use a macro to do it for you. The only shortcut key I know of is the = sign and the + sign - typing these...

Excel: Shortcuts to hide/unhide columns?, type keyboard, keyboard shortcut

type keyboard, keyboard shortcut, hidden columns: There is - if you type Keyboard Shortcut into the Excel help, you can get a list of all shortcuts - copied from that :- Show, hide, and outline data ALT+SHIFT+RIGHT ARROW Groups rows or columns. ALT+SHIFT+LEFT ARROW Ungroups rows or columns. ...

Excel: Shortening Code, range c42, retrieving data

range c42, retrieving data, excel 2002: Hello Robert, Try this: for i = 1 to 52 Cells(42,i+2).FormulaArray = =SUM((Team= Cardiff )*(recvdweek= & i & )) Cells(43,i+2).FormulaArray = =SUM((Team= Glasgow )*(recvdweek= & i & )) Cells(44,i+2).FormulaArray = =SUM((Team= LeedsG...

Excel: Show detail, excel 2002, pwd

excel 2002, pwd, scenarios: For Excel 2002 onwards you can use .EnableOutlining - this allows you to enable outline symbols to collapse/expand the data. I have also rewritten your code to make it more efficient, note the Select Case simplifies the sheets to exclude form Protection....

Excel: Show/Hide combo box when checkbox is checked/unchecked, vb text, hidden values

vb text, hidden values, combobox: When you create a combobox, it s given a default name, like Drop Down 1 . If you right-click on the combobox, you ll see its name in the name box. You can use this name to hide/show the combo box, with code like: ActiveSheet.Shapes( Drop Down 1 ).Visible...

Excel: Show Pivot Item based on Named Reference, pivot table, serial number

pivot table, serial number: If you are talking about a Names Range called DeliveryDate, then ActiveSheet.PivotTables( PivotTable1 ).PivotFields( Date ).CurrentPage _ = Range(strDate) might work. Date is tricky as it is stored in Excel as serial number. Try and let me...

Excel: Show less than percent display on a chart, data labels, chart values

data labels, chart values, pie chart: Hi Bill, I am sure a macro can be written for that. But I do not have enough VBA knowledge for it. One thing you can do is right click over one of the percentage labels and select Format Data Labels and the select the Number tab and increase the decimal...

Excel: Showing Formulas, format cells, visual basic for applications

format cells, visual basic for applications, cl: Select the column for which you DON T want the formulas to show in the formula bar. From the menu, choose format, cells, protection, hidden. Then protect the sheet (Tools, Protection, Protect Sheet). The formulas in that column only won t get shown in the...

Excel: SIMPLE MATCH FORMULA, warm regards, copy paste

warm regards, copy paste, bbb: Dear FKWAN, I hope that you want to calculate the quantity sold day wise. If I am not wrong, the formula you have to enter the columns given below. (Copy & Paste) D: =SUM(IF($C$10:$C$12 0,IF($C$10:$C$12 15,$B$10:$B$12,0),0)) E: =SUM(IF($C$10:$C$12...

Excel: SIMPLE QUESTION, array formula, blank cells

array formula, blank cells, circular reference: There are lots of ways to hande this depending on the type and variety of data...as detailed on this website: http://www.xldynamic.com/source/xld.LastValue.html Out of all of the sample formulas I like this one the best: =INDEX(A:A,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535))))...

Excel: Similar Excel question, format button, drop down menus

format button, drop down menus, input box: Hi Tyson, Select the first cell in the column that you would like to turn yellow if older than two years. I will assume to illustrate an example that your dates are in column A. Then go to the Excel menu and select Format then Conditional Formatting......

Excel: Simle formula needed, vlookup function, g4

vlookup function, g4, spreadsheet: Hello again, Here is a variation on the IF function sent previously: =IF(C1 =50000,0,IF(C1 75000,1,IF(C1 100000,2,3))) Since the IF function has a limit of 7 nested formulas, another technique (for perhaps a future need) is the VLookup function. ...

Excel: Simple One, background color

background color: For this example, we ll assume the cell you want to format is A1. Select A1. From the main Excel menu, select Format, Conditional Formatting, Condition 1, Formula Is, =LEFT(LOWER(A1),1)= y , format, patterns, [green], OK, Add, Condition 2, Formula Is, =LEFT(LOWER(A1),1)=...

Excel: Simple addition - right?, e numbers, lookup tables

e numbers, lookup tables, negative values: Hi Andy Did that referwence assist you. Regards Des ...

Excel: Simple yet complex formula, random numbers, xls

random numbers, xls, f1: If you place the workbook name (say Source.xls) in F1, for example, this formula in C1 will work: =VLOOKUP(DATEVALUE( 12/30/06 ),INDIRECT( [ &F1& ] &A1& !A:E ),5) If you want to hard-code the workbook name, this will work: =VLOOKUP(DATEVALUE( 12/30/06 ),INDIRECT(...

Excel: Simple Excel function is not working, excel function, f10

excel function, f10, f13: You re missing a parenthesis in the formula you give for F11. It should be =IF(G11= standard ,D2,IF(G11= Express ,F2)) If I make that change, and plug in all the other numbers & formulas exactly as you ve given them, I get F13 returning 148.65. Note...

Excel: Simple formula, dividing by 3, mtrs

dividing by 3, mtrs, roundup: Hello, Paul, Let s say that you have the quantity of 50 in A1. First you need to find how many 3-meter lengths you need. You do this by dividing by 3 and rounding up: =ROUNDUP(A1/3,0) This gives us 17 lengths. Next we need to multiply that by 3 to...

Excel: Simple (I hope) excel question, zebra, cells

zebra, cells, beta: Sometimes, I have to create a worksheet to get the answer, so I ve loaded them up here to give you a sample: http://www.officearticles.com/allexperts/sam.zip See the formula in cells B2 of the Alpha workbook. Likely, the formula will NOT update unless...

Excel: Simple Macro?, expert listings, excel expert

expert listings, excel expert, luck jim: Hi, Jim, There isn t a built-in way to track the number of times a workbook has been opened, but a macro could certainly be put into place to track this (from now on). You may have noticed in my expert listing and on the question page where I say that...

Excel: Simple Question, array function, frequency function

array function, frequency function, compensation level: Hello TCVB, There are a number of methods to accomplish your task. Which you use depends on your comfort level and the data layout in part. Of the methods I talk about below, each is as good as the other. Method #1: Use a combination of the IF and...

Excel: Simple Question, douglas m smith, cell b1

douglas m smith, cell b1, cell a1: Hi Karen, This can be done with a lookup table. Example: your prices are listed in column A and the calculated markups will be in column B. In cells D1:D4, enter the values 0, 50, 100 and 1000 respectively. In cells E1 through E4, enter 40%, 35%, 30%...

Excel: Simple Question - Excel, excel spreadsheet, joe joe

excel spreadsheet, joe joe, datasource: Joe - It sounds like you re talking about a mail merge. In that case, you want to create the labels template in Word, using the Excel spreadsheet as the datasource. In Word, go to Tools Mail Merge Envelopes and Labels. Hope this helps - Andre...

Excel: Simple Question on formulas, compensation level, employee names

compensation level, employee names, employee compensation: TCVB, I m going to assume that the employee names are in cells A2:A6 and the Compensation is in B2:B6. You will need to adjust the formulas below to match the actual ranges. The first criterion is the easiest since we only need to check one condition...

Excel: Simple Question, last four digits, cell a1

last four digits, cell a1, apostrophe: Hi TC, Are you saying that the zip code in A1 is the first 5 characters? If so, then A2 looks like this =A1& -0499 . However that seems too clutzy. If the last four digits are in a different cell, then the formula is: =A1& - &A3 If you are experiencing...

Excel: Simple Qusetion, douglas m smith, level numbers

douglas m smith, level numbers, countif: Hi, You can do this easily using COUNTIF and the Compensation Level numbers. Assuming the above data is in cells A1:C6, put the comp level numbers 1, 2 and in cells E2:E4. In cell F2, enter the following formula: =COUNTIF($C$1:$C$6,E2) Copy this...

Excel: Simple question, sumproduct, anthing

sumproduct, anthing, null values: Hello Richard, IF all zeros, it returns zero If all blanks or zero, it returns which makes the cell look blank Anthing else is as before. =IF(COUNTIF(A1:A4,0)=4,0,IF(SUMPRODUCT(--(A1:A4 0),--(LEN(TRIM(A1:A4)) 0))=0, ,SUM(A1:A4)/SUMPRODUCT(--(A1:A4...

Excel: Simulation, card simulation, random card

card simulation, random card, andrew miller: There s an example on my website called Rand_Unique.zip that shows how to randomly draw numbers like the lotto (ie. 6 picks from a set lot without duplication). Same logic applies to drawing n cards from a deck of 52. You ll find the example in the formulas...

Excel: Simultaneous changes to an excel spreadsheet, simultaneous changes, simultaneous work

simultaneous changes, simultaneous work, excel spreadsheet: Sean You have an option to share work book in excel. Have a look in the help menu for excel as Ways to allow multiple people to work on a workbook . The option is in Tools, Share workbook option check the option Allow changes by more than one...

Excel: Since Hit, lotto results, occurence

lotto results, occurence, l1: Hi Eugenio, I do not know whether any function can do the job. However, I tried a small MACRO with following data- (1) Range A1:G24 contains data about Lotto results. You can go on adding more, MACRO will not change. (2) L1:L51 contain numbers 1 to 51 whose...

Excel: Sinchring two sheets, cells, checks

cells, checks, dictionary: In this case, I would propose doing it with a macro, that checks which names are shown in sheet1, and filters sheet2 based on that list. Something like: Sub HideRows() Dim wks1, wks2 As Worksheet Dim rng1, rng2 As Range Dim d ...

Excel: Sir please help me to do this, excel workbooks, l23

excel workbooks, l23, command button: I won t do all your work for you... but I will show you some sample code that opens a different Excel workbook based on the entry in a cell in the current workbook, cut/paste-values a range from the other workbook into the current one, then closes the other...

Excel: Sizing window, hi bob, curser

hi bob, curser, excel 2000: It stays as an arrow for me, too, but it will resize the window. Try clicking it and then see if you can move the window by its title bar. If you can, then you can resize it with the mouse. If it doesn t move, click it a 2nd time and try again. Clicking this...

Excel: Slope / Linear Regression, linear regression, jan feb mar

linear regression, jan feb mar, slope: Shanna, I think that Excel will need all of the data points to calculate slope correctly. You can calculate slope in Excel by using =Slope(known_y s,known_x s), but I think that if you try to use the summary rates, you will get invalid results. For example,...

Excel: Small loop help, incometaxindiaefiling, kanwaljit singh

incometaxindiaefiling, kanwaljit singh, a41: Based solely on your description: Sub Macro1() Dim i as Long, k as Long for i = 1 to 100 k = k + 20 With ActiveSheet.QueryTables.Add(Connection:= _ URL;http://incometaxindiaefiling.gov.in/knowtan/tanpage.jsp?tan= & i, _ Destination:=Range(...

Excel: Smart Tag, smart tag, cell a1

smart tag, cell a1, smart tags: VBA cannot work with smart tags, but you can convert the date BEFORE you put it in the cell, e.g.: Dim vDate As Variant vDate = Split( 03/10/07 , / ) ActiveCell.Value = DateSerial(CInt( 20 & vDate(2)), vDate(1), vDate(0)) ActiveCell.NumberFormat...

Excel: Softball stats, e mail address, game sheets

e mail address, game sheets, blank sheet: Hi Jan, How are you doing. If you want to do it with formulas, I think you would need to create an intermediate table on each sheet that tabulates the counts for that game - an image of the summary sheet, but only for that game. Then you could use...

Excel: Solver with multiple spreadsheets, linear program, sheet1

linear program, sheet1, objective function: I believe they have to be on the active sheet. But the cells on the other sheets can be simple references to the cells on the active sheet, as a temporary way to change things. For example, if you wanted one constraint to be Sheet2!A1:A5 100, you can change...

Excel: Solving for average minute per mile pace, minute mile pace, dificult time

minute mile pace, dificult time, exercise log: Jim, It could be done with a macro or with formulas. If you want to send me a sample workbook with your 3 sheets (formatted as you want them with headings and so forth and your data entry sheet set up as it will be, including some sample data for each category)....

Excel: Sorry Bod, yes you a right,..., open to suggestions, drop down list

open to suggestions, drop down list, bod: OK, you can run this untested macro: Sub XYES() Application.calculation=xlcalculationmanual for each thing in range( J6:J6485 ) if thing.value= YES then thing.offset(0,1).value= X Next Application.Calculation=xlcalculationautomatic...

Excel: Sort and align routine needed, frog fish, fish fish

frog fish, fish fish, mouse mouse: Hi - I ve had LOADS of questions recently, so had to go back over the old ones to find a reply , so the revised code may have been slightly misleading - the KEY part is the parts of the routine that refer to a specific range - I ve written it as .Range(...

Excel: Sort and align routine needed, blank cell, cell space

blank cell, cell space, variations: john there could be a variety of ways to do this but it would be easier if I could see a copy of the sheet so that I can give the best ideas. If this is possible then send a copy to richard.littlewing@gmail.com. If it isn t possible can you tell me...

Excel: Sort and align routine needed, sort result, blank cell

sort result, blank cell, cell space: There may be an easier way to do this, but I took into consideration that there may be values in any one particular column that might not appear in any other column (in my example, Black and Burgandy). Because of that assumption, it makes doing any “simple”...

Excel: Sort and align routine needed, blank cell, cell space

blank cell, cell space, hi bob: This is a pretty involved routine, and I m about to leave on vacation for a week. Consider these columns after the sort but without aligning: Col A B C c b a d e b clearly you d want Col A B C ...

Excel: Sort applicant by job selection, douglas m smith, tea lady

douglas m smith, tea lady, brainbench: Hi Barry, I m a lttle confused with regard to your data setup. If I could see it, a solution should quickly follow. If you could email the file to doug@abundant-solutions.com, I ll take a look at it. Douglas M. Smith Brainbench MVP for MS Excel www.brainbench.com...

Excel: Sort by Cell Color?, colorindex, numeric value

colorindex, numeric value, spreadsheet: Not directly no - you CAN do it if you create a custom function to return the numeric value of the formatting - to do this, press Alt F11 to view the VBA editor, then insert a module and enter Public Function colval(CellVar As Range) colval = CellVar.Interior.ColorIndex...

Excel: Sort Cells by Type, sort cells, de bruin

sort cells, de bruin, formula approach: Thomas, a simple solution is to use a macro to make copies of the data (an automated to do what you describe you could do by hand although you don t have to sort the original data). Code that can be used to do this can be found at Ron de Bruin s site: ...

Excel: Sort Cells by Type, sort cells, vlookup

sort cells, vlookup, swbell: This sounds like something that could be done with an indexing formula - for example, VLOOKUP or INDEX/MATCH. VLOOKUP would probably be easier and it can also be used to pull all of the corresponding info (eight cells to the right, etc), with the origianl...

Excel: Sort by cell reference, mail group, hi aaron

mail group, hi aaron, cell reference: Sorry it s taken so long to respond, my father needed some emergency surgery this week and things are hectic. If someone hasn t already helped you with feel free to post it on my mail group for assistance. Mail Group http://groups.yahoo.com/group/XL-Logic/...

Excel: Sort dates when they are within 30, 90, days of current date, conditional formatting, format button

conditional formatting, format button, custom filter: You can do the highlighting with conditional formatting. In your case, select the first row that you want to color, for example A2:F12, go to the menu Format- Conditional formatting, and enter the following conditions: FormulaIS: =(TODAY()-$D2) =30 FormulaIS:...

Excel: Sort in group of columns, a8, aidan

a8, aidan, a3: Your explanation doesn t quite explain to me the logic, but basically you will need to create a NEW column as far as I understand this question - that new column would contain the sorting criteria (a sum, or concatenation maybe?) - you can then sort on this...

Excel: Sort Macro, keyboard shortcut, excel spread sheet

keyboard shortcut, excel spread sheet, syntax error: Jeff, I just fired up Excel 2007 to see what is different - sort certainly is g . This macro should do what you want. One thing to note is that I am assuming that you are sorting ascending, on column 1. I have added a constant that you can change that...

Excel: Sort Macro, time constraints, necessary thanks

time constraints, necessary thanks, bob works: I did not say that the SubTotal feature allows you to sort by three fields. I said EXCEL allows sorting on up to 3 fields/columns - referring to Data, Sort. You have to sort the data before you subtotal it if, of course, you want valid subtotals. These...

Excel: Sort problems, excel 97, cheers

excel 97, cheers, windows 98: Hi Marshall How are you, In Excel 2003, the data is sorted well. I have no means to check what happens with Excel 97. Try to check tools -options to see if 0 is somehow excluded. I am sorry but I cannot check it I have Excel 97 anymore. Cheers...

Excel: Sort a range and protect, vba code

vba code: Hi Jerry, your best course of action is: 1. Create a dynamic named range for your data. These ranges essentially expand with your data as you add new rows (or even columns to it). For a good discussion: http://www.cpearson.com/excel/named.htm 2. Record...

Excel: Sort on specific time frame in row, colums, sort data

colums, sort data, time range: Yes, there are formulas you can use to gather time ranges, usually inside IF formulas. In order to compare, it is safer to build the comparison using the TIME formula, and if you want more than one criteria, you may also use the AND formula. One example...

Excel: Sort Top 10 Descending List - Two Columns, field settings, pivot table

field settings, pivot table, question thanks: OK, Forgot you wanted the result on a single sheet, sorry. Here is revised code that worked for me: Option Explicit Sub CreateTop10() Dim oSource As Range Dim oCell As Range Dim colDepts As Collection Dim vDept As Variant Dim...

Excel: Sort

You did not say what data you have now so I have to assume that the coulmn you want to sort on is a full date, like 4/27/2004. Write this formaula in an adjacent column and copy and paste it down as far as you need it to get the month portion of the assumed...

Excel: Sorting, header row, column letter

header row, column letter, column title: Hi Michael, I believe the reason is that once you go to Data then Sort sort will ask if you have a header row, such as First Name, Last Name, etc. If you check mark that it does then excel deselects the header row. Then you Sort. After you have sorted...

Excel: Sorting, 20th century fox, column c

20th century fox, column c, suitable solution: You can use formulas like this in additional columns (supposing the data is column A): Customer (Column B): =IF(LEFT(A1,9)= Customer: ,A1, ) Agency (Column C): = =IF(LEFT(A1,7)= Agency: ,A1, ) Other (if needed, in Column D): =IF(OR(LEFT(A1,9)= Customer:...

Excel: Sorting, good evening, tenths

good evening, tenths, cols: This isn t sorting, but filtering. Let s say your list is in column E. In an unused area, say H2, enter this: =E2-INT(E2)=0.5 Then select any cell in col E, use Data/Filter/Advanced filter, then enter H1:H2 in the criteria, and click OK. You will only see...

Excel: Sorting, moon column, planet moon

moon column, planet moon, time column: Let s presume the information is organized as such: Column A1:A4 = Planet/Moon Column B1:B4 = Constellation Column C1:C4 = Date (no time) Column D1:D4 = Time (no date) Add this formula to E1: =+C1+D1 This will add your date and time together to...

Excel: Sorting by blocks, spread sheet, guidance

spread sheet, guidance, ties: One way of achieving it can be the following: - Use an additional column, and fill it with the same figure/code for all the rows in each block. The criteria for this will depend on your layout, but normally you will be able to do it using formulas. - If...

Excel: Sorting blocks in Excel, numeric values, cells

numeric values, cells: Difficult to visualise, BUT it sounds to me as though the easiest way is to create a temporary column which contains the value you want to sort by in the first row and the same value in the next 4 - do it by putting a formula on the five cells pointing to...

Excel: Sorting/Colours, sort cells, spreadsheets

sort cells, spreadsheets, spreadsheet: ...when the second one is sorted through the macro the colours applied to specific cells do not move as with normal sorting... Huh? I can t duplicate this. Cells sorted whether manually or by macro have their colors move with the cells. Maybe you can email...

Excel: Sorting columns, inserting blank cells., blank cells, chris answer

blank cells, chris answer, number column: Sub Align() last = ActiveSheet.UsedRange.Rows.Count i = 3 starting row of data also assumes cols A:D Do If Cells(i, 1).Value = Cells(i, 3).Value Then GoTo iUp If Cells(i, 1).Value Cells(i, 3).Value Then Cells(i, 3).Resize(1,...

Excel: Sorting columns, questios, personal emergency

questios, personal emergency, click options: Yes. Select the values in the table that you want to sort. The click Data, Sort. The click Options. Choose Sort Left to Right and click OK. Then under Sort by choose the Row you want to sort by, and click Ascending or Descending. This will sort the columns...

Excel: Sorting, cutting and pasting data into a new file, unsubscribed, mass mailings

unsubscribed, mass mailings, spacebar: click in the column containing the word Unsubscribed and click the sort toolbar button (A-Z). Now they re all together. Select them all, use Ctrl-spacebar (selects entire rows), use Ctrl/- (deletes these rows), then ctrl/* (selects all the remaining cells),...

Excel: Sorting, cutting and pasting data into a new file, drop down boxes, resultant data

drop down boxes, resultant data, query options: If I was doing the mailing, I would simply filter on the field that specified not unsubscribed, using the query options on the mail merge - however, if the data is in excel, use Data Filter Auto Filter to get the drop down boxes on the fields, then choose...

Excel: Sorting Daily Dail Data Once a Week, vlookup formula, column c

vlookup formula, column c, c data: Randy You can use a VLOOKUP formula to find the column C data on the appropriate date. The formula might look like this. =VLOOKUP(H1,A1:C4,3,FALSE) Where cell H1 contains the date that you want to look up, and columns A thru C are arranged as indicated...

Excel: Sorting Data, radiation values, latitude and longitude

radiation values, latitude and longitude, latitude south: Hi Steve, How are you? I think that you have to split the latitute into 4 columns: Direction,Degrees,minutes and seconds. Then sort by direction and then use data sort to sort by degrees,minutes and seconds if you have the latitute in A1 (0 1 0 N) 0....

Excel: Sorting Data, radiation values, latitude and longitude

radiation values, latitude and longitude, latitude south: Steve Yes excel will sort based on first number or letter in the sort column, so what we need to do is separate the north and south letters from the degrees part of the latitude description. To do this you can use the Text to columns feature of excel...

Excel: Sorting Data, vlookup formula, notebook model

vlookup formula, notebook model, reference column: Johnny: Updated Spreadsheet: http://home.swbell.net/nate-sus/excel/vlookupnotebook.xls You could also do a vlookup by price, but vlookup has a small limitation where the lookup item must be (essentially) in the furthest left column. I will...

Excel: Sorting Data in Charts., max function, hi bob

max function, hi bob, source data: Any sorting must be done in the worksheet data, not in the chart. You d need to set up a separate area which uses references to the source and which uses the MAX function to get the largest, and perhaps the LARGE(...,2) to get the 2nd largest, etc. and the...

Excel: Sorting Data, radiation values, latitude and longitude

radiation values, latitude and longitude, latitude south: Steve, I may have a solution for you, but I m not sure. Goto the attached link and download the file. I have each reading in its own column. I deleted the lon and lat since they were redundant. I first sorted the LatDir column Ascending as the...

Excel: Sorting Difficulties, random intervals, vlookup

random intervals, vlookup, index figures: Yes there is a logical way to approach and accomplish this with one exception. First, to create the entire indez with no gaps in the numbers. In a separate column (D1 in this example) type 1 in D1 and 2 in D2. Select both cells. Use the auto fill handle...

Excel: Sorting data in Excel Spreadsheet Win98, format cells, mail service

format cells, mail service, down arrow: Are the numbers in a single column? if so, select the entire column and format as General (format= Cells= Number Tab, selec general go to a blank cell and enter the number 1. select that cell and do Edit= Copy select the data in the troublesome...

Excel: Sorting in Excel 2003, north yorkshire uk, score card

north yorkshire uk, score card, dear tom: John, You really haven t told me enough to understand what is happening or how you have your data set up. If you want to send a better explanation and a small sample workbook that illustrates the problem then perhaps I can help. twogilvy@msn.com --...

Excel: Sorting in Excel, dear tom, ogilvy

dear tom, ogilvy, headings: Richard, Excel does a Stable or static sort, so you just need to do successive sorts. Assume you want to sort by columns A, B, D, J, M and O going from left to right. First you would sort on J, M and O then sort on A, B, D the net effect...

Excel: Sorting in Excell 2002, header row, movie list

header row, movie list, dialog box: Hi Jim, You will need to first select the entire selection of data in columns 1-4 and down however many rows. Then go to the Excel menu and select Data then Sort.. First notice at the bottom of the dialog box where it reads my data range has and...

Excel: Sorting excel, headings, formatting

headings, formatting, good luck: Here s a little trick I learned, Kevin, to keep this from happening. You have to make sure Excel understands that the first row of your data is headings. There are two things that will help: • Make sure that all the columns do indeed have an entry in...

Excel: Sorting by extra columns/criteria, sort keys, 3 ways

sort keys, 3 ways, hth: You can sort 256 sort keys, not just 3. The trick is that you re limited to 3 AT A TIME. To affect sorting levels a:b:c:d, you can do it in 2 passes, starting with the least significant as the first pass. With 4 keys there are 3 ways: 1 - sort d, then sort...

Excel: Sorting or filtering invalid data, douglas m smith, relevant cells

douglas m smith, relevant cells, willenken: Hi Karen, The easiest way to do this is create a temporary column with a formula that returns a sortable result. Something like: =IF(B2 C2, X , ) This will return an X for the offending numbers. Sort on this column to get all the X lines together....

Excel: Sorting with formulas, vlookup, jeff c

vlookup, jeff c, bill c: All explanation below refers to tab B. In col A, you have names. In col C, you have numbers associated with those names. You want to create a list so that the name with the highest number in col C will be first, followed by the name with the 2nd highest...

Excel: Sorting Macro for Excel sheet with frozen cells, frozen cells, proposal manager

frozen cells, proposal manager, selection area: I would make the macro refer to a DYNAMIC range that didn t include the header area - so in your example of A4:H48 you presumably want to sort A8:H48? And I m ASSUMING you will have valid data in all column A (for example) - something like Range( A5:H...

Excel: Sorting Made Easy, format cells, fool proof

format cells, fool proof, selection sort: Ok, lock the sheet - format the cells/columns that they CAN enter data into as protection, unlocked (i.e. format cells, protection tab, uncheck the locked box). Lock the worksheet (preferably with a password) and don t allow them to select locked cells. (Tools...

Excel: Sorting merged cells, format cells, missing something

format cells, missing something, b1: Hi Kevin, You are correct, cannot sort merged cells is a more appropriate error message. I m not sure what you have in b1 and c1, but you could try to highlight b1 and c1 and format cells/horizontal choose center across selection. and try sorting those...

Excel: Sorting numbers, water polo championship, matchday

water polo championship, matchday, thanks in advance: No. Functions do not perform actions; they only calculate something and return the result of that calculation. The RANK function will help you determine who is in first place, second place, etc as new scores are entered but it will not sort the data according...

Excel: Sorting numerically, layout hope, digit numbers

layout hope, digit numbers, must be a way: I would use an additional column, and use a formula like this there: =SUBSTITUTE(A1, S , )*1 This will give you a column of numbers (the *1 part makes the number format happen), where you can sort in the way you need. Once sorted, you can get rid of the...

Excel: Sorting Problem, ball valve, se4

ball valve, se4, concatenate: Robert, you re going to be so pleased at how easy this is. Basically, you want to end up with the data sorted by column A. That is considered your first sort key. Within each grouping of column A, you want column B to be in order. That is considered your...

Excel: Sorting problem, column headings, manufacturer description

column headings, manufacturer description, bottom of the list: Jim are there any blank columns in the table? This can sometimes cause excel to miscue. Try this test. Click anywhere in the table and then go to sort and it should highlight the entire table. If it doesn t then you have a gap in the columns somewhere...

Excel: Sorting Rows (follow up), sort selection, sort columns

sort selection, sort columns, selection options: Steve, Your example and desired results were consistent with what sort does - which is sort all the data based on the key row - same as when you sort columns. So my answer was totally consistent with what you said you wanted. You are correct, if you...

Excel: Sorting Sheets, sheet tabs, scriptcenter

sheet tabs, scriptcenter, qanda: Keith Nothing that comes with excel but you may want to look at these links and I am not sure it will work with dates either. http://www.cpearson.com/excel/sortws.aspx http://www.ozgrid.com/VBA/sort-sheets.htm http://www.microsoft.com/technet/scriptcenter/resources/qanda/nov05/hey1122.mspx...

Excel: Sorting Sheets, sheet tabs, drop down boxes

sheet tabs, drop down boxes, google: Sarcasm? huh? You must have misread my answer. Did you expect me to write the macro for you? The link I provided give you a very detailed answer. What more could I have done? In the future please direct your questions to another volunteer. -----------------------------------------------...

Excel: Sorting text, crazy question, cells

crazy question, cells: Suppose the word is Helen. In another column, you can use a formula: (I m referencing A1 to start - adjust as necessary) =IF(RIGHT(A1,5)= Helen ,1,2) and fill this down Sort everything on this column. The 1 s will come first (containing Helen) and the 2...

Excel: Sorting troubles, ms excel 97, ascending order

ms excel 97, ascending order, sorts: Marshall If the data is imported click on the column you are going to sort on and go to Tools text to columns, sometimes when importing excel wont always account for text and numbers in a column. If this doesn t solve the problem can you send me a copy...

Excel: Sorting a worksheet, paste move, sheet names

paste move, sheet names, town names: Sub MoveRows() Dim ws As Worksheet On Error Resume Next this assumes the possible sheet names are in column A For i = Range( A65536 ).End(xlUp).Row To 1 Step -1 Err.Clear Set ws = Sheets(Cells(i, 1).Value) If Err.Number...

Excel: Sorting a worksheet, paste move, town names

paste move, town names, vba code: You don t need a macro and if you did then explaining how to code this would be more trouble than just wriitng the code myself. Simply filter the data on each of the towns one by one. Then copy the filtered data to the appropriate sheet. See help on...

Excel: Sorting a worksheet, cell d1, cell d2

cell d1, cell d2, paste move: Harry Sorry I ve been away from my computer and just saw your question. first-let me say that I m not a real VBA expert. Having said that I believe there is a way that you can get to your answer using a combination of formulas and macros. First I...

Excel: Sorting a worksheet, de bruin, paste move

de bruin, paste move, town names: Mike, Ron de Bruin has a page on his web site with the code you need. http://www.rondebruin.nl/copy5.htm I don t believe it deletes the original sheet, but if you have dispersed all the entries to other sheet, all you would have to add is a line of...

Excel: Sorting on a worksheet, ipaq palm, chip pearson

ipaq palm, chip pearson, hp ipaq: Eric, The easiest way to get the code is to turn on the macro recorder while you do the sort manually. Then turn off the macro recorder and look at the code. You would then call that code from the change event. If you are not familiar with events,...

Excel: Sorting between worksheets, a2, comma

a2, comma, array: Need a macro. Sub ttc() Application.ScreenUpdating = False For Each sh In Worksheets sh.Activate Range( A2 ).Select Selection.TextToColumns Destination:=Range( A2 ), DataType:=xlDelimited, _ ...

Excel: Sorting, sort function, excel spreadsheet

sort function, excel spreadsheet, max number: Hi Gaia, I am not sure why this is happening. I have not been able to find a limit on the number of sorts for Excel. I do know that if you use the regular Filter it is limited to 999 items. But it sounds like you are using the sort function with Excel....

Excel: Sound in excel, beep sound, down arrow

beep sound, down arrow, defsound: you didn t say what sound you d found in Excel, but I d hazzard a guess that the one causing the problem is the help wizard Turn the Office Assistant sound on or off Some of the content in this topic may not be applicable to some languages. To hear...

Excel: SPIN BUTTON UP INCREMENT, decimal places, increment

decimal places, increment, shout: Private Sub SpinButton1_Change() Me.TextBox1 = Me.SpinButton1 / 100 End Sub This works on the spin up or down And, in the future, please don t SHOUT. Typing in ALL CAPS anywhere online is considered SHOUTING, as well as rude and poor net etiquett...

Excel: SPLIT A CELL, format cells, numerical value

format cells, numerical value, appearance: Stormy You can only give the appearance of a split cell, you can t actually split the cell. To give it the appearance go to format cells border and look for the diagonal buttons. If you want numbers in the cell you will have to use text ie 12 gives a...

Excel: SPREADSHEET PROTECTION, format cells, excel spreadsheet

format cells, excel spreadsheet, sally: Hi Sally, I presume you need to protect only certain cells. (1) Select entire sheet. (2) FORMAT-CELLS-PROTECTION (3) Untick Locked (4) Select the desired cells (5) Step 2 (6) Tick Locked (7) TOOLS-PROTECTION-PROTECT SHEET (8) Untick Select Locked...

Excel: Spaces at the end, ascii code, width option

ascii code, width option, space characters: Hi Drew, There are several ways to deal with this, depending your situation. I ll go through them all for completeness. The easy solution is to use the TRIM function. This trims off leading and trailing spaces in text as well as extra spaces in the...

Excel: Spatter Plot, y intercept, plot diagram

y intercept, plot diagram, population figure: Hey Ed, The formula to solve for Y is called the linear trendline calculation: y=mx +b where m is the slope and b is the intercept. So, +2E+06 is the intercept. I believe you replace x with 10 to solve for future population in 10 years. I m not...

Excel: Special character search, macro cells, character search

macro cells, character search, must be a way: You cannot do it with a global search and replace as this assumes that * is anything, and ? is a wild card - BUT you CAN do it with a macro Sub Macro1() Macro1 Macro Cells.Find(What:= * , After:=ActiveCell, LookIn:=xlFormulas, LookAt:=...

Excel: Special Date Formatting, electric shock therapy, entry cells

electric shock therapy, entry cells, range tests: If you understand event procedures, this is what you will likely need to use as your event procedure for the sheet object. This particular example watches column A for inputs that match the 8 digit filter ######## and processes them as date values automatically....

Excel: Special keys, capslock, scrolllock

capslock, scrolllock, h14: In a general module put in code like this: Const VK_CAPSLOCK = &H14 Const VK_NUMLOCK = &H90 Const VK_SCROLL_LOCK = &H91 Declare Function SetKeyboardState Lib User32 _ (kbArray As Byte) As Long Declare Function GetKeyboardState Lib User32...

Excel: Specialist and Expert, maximum productivity, core concepts

maximum productivity, core concepts, office applications: Hi Angelo: A specialist is familiar with the core concepts of a particular Office product. They know how to effectively use the product. On the other hand, an Expert is familiar with the core and advanced concepts of the product (ie macros, formula writing,...

Excel: Specific number ranges, macro name, whole number

macro name, whole number, random number: This UDF will generate a random number between two values Option Explicit Public Function RandomNumber(Lowest As Long, Highest As Long) Generates a random whole number within a given range Randomize RandomNumber = Int(Rnd * (Highest + 1 -...

Excel: Specifying a folder, mybook, mypath

mybook, mypath, getopenfilename: I believe that the basic structure of what you re trying to do is have the user browse to a file in any folder, and then have the folder open each .xls file in that folder. I would use a File System Object (FSO) to do this. I think it s worth learning about...

Excel: Speed of Macro's in Excel, macros in excel, spoolsv exe

macros in excel, spoolsv exe, print drivers: Leo Removing and adding rows is changing the page layout for printing purposes. Why the change, that question would have to be asked of Microsoft. I have been grappling with 2007 lately and it is a whole new ball game, nothing is where it was and it doesn...

Excel: Spell Check, custom dictionary, correct spelling

custom dictionary, correct spelling, excel 5: This seems to be a bug within Excel, which is carried through at least until Excel 97 - Word will happily suggest from the custom dictionary, but Excel doesn t want to know. Therefore ONE possible fix is to copy the data to word, do the check here and then...

Excel: Spell Checking Cell Range Not Workbook, keyboard shortcut, password selection

keyboard shortcut, password selection, options button: Hi Michael, You can make it check specific cells with the following Macro, just adjust the cells to the cells you need to Spell Check. It will only spellcheck those cells. Sub SpellCheckIt() Sheets( Sheet1 ).Unprotect password Range( F7,F12,H11,H14,J8,K12,L15,L17...

Excel: Spell check in Protected Excel doc, visual basic menu, spell check

visual basic menu, spell check, select tools: Hi Jeri, Open your workbook with password protection that you want to spell check and press Alt+F11 to open the Visual Basic Editor. Then from the Visual Basic menu select Insert then Module then in the right-hand blank pane paste the following macro...

Excel: SpellCheckIt macro for protected sheets, visual basic menu, select tools

visual basic menu, select tools, spell check: Open workbook with password protection that you want to spell check and press Alt+F11 to open the Visual Basic Editor. Then from the Visual Basic menu select Insert then Module then in the right-hand blank pane paste the following macro and remember to...

Excel: Spelling Dictionary, microsoft office xp, grammar tab

microsoft office xp, grammar tab, spelling dictionary: Dave These steps should do it for you. To add, delete, or edit words in a custom dictionary: On the Tools menu, click Options, and then click the Spelling & Grammar tab. Click Custom Dictionaries. Select the check box next to the dictionary you...

Excel: SpellingCheck, custom dictionary, capitalization matters

custom dictionary, capitalization matters, correct spelling: Byron, let s check one simple thing first. Go into the Spell checker, and click on Options. Make sure that the Suggestions option is checked. If it was unchecked, then turn it on and you should now get suggestions during your spell-check. If it was already...

Excel: SpinnerButton, spinner button, excel 2002

spinner button, excel 2002, hth: Hi Jimmy, Hi Again! I hope I can still be of help. The only way, I think, to do this is to apply some type of muliplier (or divider ) to the value from the spinner. I recall setting up something that allowed the user to select a percentage. So once...

Excel: Split Cells., split cells, iloc

split cells, iloc, sstr: I guess the easiest way would be to do it with a macro. copy your data to a new sheet. Then select it. then run this macro Sub splitCell() Dim cell As Range Dim iloc As Long Dim i As Long Dim chr As String For Each cell In Selection iloc = 0 ...

Excel: Split a cell/select first half, c command, len

c command, len, second half: Yes, it s possible - how you do it is slightly dependant on the data, but from what you have said the key is the space in these examples, I ve assumed A1 contains the data To get the AB =LEFT(A1,SEARCH( ,A1)-1) To get the 10 =RIGHT(A1,LEN(A1)-SEARCH(...

Excel: Split Excel Colomn, delimiter character, concatenation operator

delimiter character, concatenation operator, cell references: I suppose that you are asking for the reverse of the split process, for that, you can use the concatenation operator (&) and craft the formula with the different cells. Something like: =A1 & & A2 & & A3 & & A4 You can change the cell references...

Excel: Split Screen, column labels, freeze panes

column labels, freeze panes, window menu: Hey Matt, It sounds like you split the screen instead of freezing the panes, which I think it what you might want to do. Here are the steps to freeze the panes: Freezing panes allows you to select data that remains visible when scrolling in a sheet....

Excel: Split text into two columns, task selection, k town

task selection, k town, range selection: Hi Charles This rountine splits the data type you mention ie street,town into speparate items. It requires the data to be processed in column A and pus the split data in column B and C. It processes 3000 records in about 2 seconds by my reckoning....

Excel: Spliting Cell Content, query parameters, separator lines

query parameters, separator lines, efgh: I had a similar question recently, so I will provide that information in the context of your question: If you mean manually: select the cell (or if multiple cells in a single column, select the column or group of cells in a single column) and do Data=...

Excel: Spliting a column, empty cells, autofilter

empty cells, autofilter, ogilvy: select columns 1 and 2. do Data= Filter= Autofilter in the top menu select column B and in the dropdown, select blanks. Select all the data and copy and paste it to another sheet. This will be the data with blanks (hidden rows won t be copied)....

Excel: Spliting name into 3 columns, comma delimiter, address city state

comma delimiter, address city state, address text: Hi Leon, Yes, this is a problem because the data uses two different delimiters in the same text string, and the data must not be parsed using both delimiters at the same time. Basically, the name part must be parsed with a space delimiter, and the address...

Excel: Splitting a column, excel 97, last names

excel 97, last names, delimiter: Before Excel 97 this was possible but required formulas - from excel 97 onwards it became easy - make sure you have a blank column after the names,then use the data menu to select Text to columns - I m guessing that you have a space or a comma between first...

Excel: Splitting Information from one cell across multiple cells, cell a1, january february march april

cell a1, january february march april, january february march: Sean: Presuming your date is in cell A1 (and it is a valid date and not text), you can use these formulas: =YEAR(A1) =MONTH(A1) NOTE - The month formula really only provides the number of the month, so if you want text you would need to use this formula:...

Excel: Splitting single cell content into multiple cell, cell content, digits

cell content, digits, step 2: 2 simple ways come to ming: 1 - select the column with the 8-digit #s. use Data/Text to Columns. Select Fixed width in step 1, click Next. In step 2, click in the sample data between 2nd & 3rd digits - this will place a line in the preview. Do it again between...

Excel: Splitting single cell content into multiple cell, cell content, digits

cell content, digits, reply: Easiest way is to use Data, Text to columns, choose fixed width and put the lines in the appropriate place - you will need blank columns for them to go into (else the existing data in the subsequent columns will get overwritten) IF you need to do this with...

Excel: Splitting/sorting an array using macros, respectful group, top bottom

respectful group, top bottom, cols: If I understand, just remove the sort and start looking from the 2nd value, since the 1st is 0: Sub SortAndSplit() Set rg = Selection.CurrentRegion.Resize(, 2) n = Application.Match(0, rg.Offset(1),Resize(, 1)) rg.Offset(n).Resize(1000, 2).Cut...

Excel: Sports league tables, input sheet, hi bob

input sheet, hi bob, sports league: The table needs to have formulas which reference the input sheet. For example, if the input sheet (named Sheet1) has a value in cell D3, then somewhere on the other sheet there needs to be a reference to =Sheet1!D3 If this isn t what you re looking for,...