cell b5, position cursor, string position: One problem is CellValue = ActiveCell.Offset(m,0).Select The select is what s giving the True. You want CellValue = Activecell.Offset(m,0).Value Also, you have screenupdating backwards - should be false first, then true at the end. However, look...

Excel: Require a macro to copy text part from a cell, hydrogen peroxide, iloc

hydrogen peroxide, iloc, question thanks: Sub CopySubstring() Dim iloc As Long, s As String iloc = InStr(1, Range( A1 ), . , vbTextCompare) If iloc 0 Then s = Left(Range( A1 ), iloc) Range( B9 ).Value = s End If End Sub After I run the macro, I check the results in the Immediate...

Excel: Required fields on form, time and billing software, software in excel

time and billing software, software in excel, client matter: ...We could very possibly be interested in hiring you - but I m not sure if from what I have described if it s enough for you to give me a ballpark number.... Well, no not yet but that s because we would need think a bit beyond what this project can and...

Excel: Reset Button, control toolbox, commandbutton

control toolbox, commandbutton, excel spreadsheet: Me only works if the code is in the sheet module of the sheet containing the button (or a userform, but you said your controls are on a sheet). It is just as easy to refer to the sheet directly. Assume that the sheet has a tab name of sheet1. Assume you...

Excel: show a list of dates between two given dates, cell a1, ckb

cell a1, ckb, cell b1: Hi, run this code while you are on the worksheet with the dates: Sub WriteDates() Dim sc As Range sd = Range( A1 ) start date ed = Range( B1 ) end date Set sc = Range( C1 ) start cell check dates If ed - sd = 0 Then Exit Sub ...

Excel: Reset CheckBoxes In Excel, microsoft excel 2000, using microsoft excel 2000

microsoft excel 2000, using microsoft excel 2000, control toolbox: Sorry for the delay. I have been very busy at work today...:-) Since you used the control Toolbox, the syntax will work below. I assuned that your cell to clear is in the cell next to the checkbox in question. Please modify as you see fit. Sub Clear_Checkboxes()...

Excel: Reset, spreadsheet, cheers

spreadsheet, cheers, cells: Hi Rose How are you? How about the following 1. tools - macro - Visual basic editor 2. select THISWORKBOok 3. Copy Sub Workbook_open() Set newR = Sheets(1).Range( B1:B10 ) Set oldr = Sheets(1).Range( A1:A10 ) For i = 1 To oldr.Rows.Count ...

Excel: Resetting Cells to null at the end of the month, acquisition dates, credit numbers

acquisition dates, credit numbers, ogilvy: I don t see anyting automatic being done - this would be complex to me. (A macro runs and everytime the worksheet is open and clears cells based on a date - that sounds troublesome to me) If you are just asking how to code a macro to clear cells Sub...

Excel: Resetting default settings on a dialog box using a marco, sendkeys, default settings

sendkeys, default settings, excel 2000: Sub TestFind() SendKeys %L{up}{up}{down}~%n Application.Dialogs(xlDialogFormulaFind).Show End Sub The reason for the up/up/down, is that the 2nd time you would run this, if there were only {down}, is that it would then change from values to...

Excel: Resize picture macro with VBA, run time error, excel sheet

run time error, excel sheet, first selection: Try it this way then which worked for me: Sub BBB() Dim sh As Object Sheets( Actions ).Select Range( B16 ).Select sImageToLoad = Sheets( Macros ).Range( A2 ) Set sh = ActiveSheet.Pictures.Insert(sImageToLoad) sh.Select Debug.Print TypeName(sh), TypeName(Selection)...

Excel: Resizing MsForms DropDown Box, multiplication factor, proportional font

multiplication factor, proportional font, dropdownlist: Unfortunately there is no real foolproof way to determine how wide a combobox must be t fit the largest text. Especially when you re using a proportional font (which usually look best anyway). The best way is by trial and error: find out the largest string...

Excel: How to get Respective data in Textbox, case problem, roll numbers

case problem, roll numbers, sheet1: Assuming your data range in Sheet1 is A1:B5 (change as needed), you can use the macro below for the TextBox1 change event. Private Sub TextBox1_Change() Dim x On Error Resume Next x = Application.VLookup(1 * TextBox1, Worksheets( Sheet1 ).Range( A1:B5...

Excel: How to get Respective data in Textbox3, sheet1, roll numbers

sheet1, roll numbers, text application: Hi, it is similar to what I had before: Private Sub TextBox1_Change() Dim x On Error Resume Next x = Application.VLookup(1 * TextBox1, Worksheets( Sheet1 ).Range( A1:G5 ), 2, False) TextBox2.Text = TextBox3.Text = TextBox4.Text = If Application.WorksheetFunction.IsNA(x)...

Excel: Restricting a Cells inputs, data validation, error window

data validation, error window, cells: Try this: Pre-select the cell(s) in which you want to restrict input. Click Data, Validation. Under Allow: choose Custom. Type the formula below in the Formula: box =OR( Y , N ) Ignore any message that may appear about the formula being invalid...

Excel: Restricting cursor to unprotected cells., input cell, properties button

input cell, properties button, tab key: Robert The simplest (and the hardest sometimes) is to tell the users to use the tab key, but since most don t remember you can do this. To make the locked cells unselectable, change the worksheet s EnableSelection property. Select View, Toolbars, Control...

Excel: Restricting Macros, chip pearson, control toolbox

chip pearson, control toolbox, macro button: Just add a check at the top of your code for the click event of the button. If worksheets( Sheet1 ).Range( B9 ).Value = then Msgbox the sheet is not complete, so no action can be taken exit sub End if This doesn t disable the button,...

Excel: Restricting toolbars, paste application, open application

paste application, open application, spreadsheet: Hey Dan, To make your custom toolbar appear when the spreadsheet is opened, this is what you would do: -Open Visual Basic when in your spreadsheet -Click on This Workbook under VBAProject on the left -This should open the Open declaration page -Paste...

Excel: Retreiving Data, lookup functions, multiple records

lookup functions, multiple records, single database: You might want to put in all your like data in a single database and set up keys that link the databases. Do this in MS Access. Then you can construct a query that will return multiple records. There might be an Excel approach, but your description...

Excel: Retreiving file attribut with VBA, overflow error, ampersands

overflow error, ampersands, gyula: Yes. You need to get the ExifReader class object (available for VB from sourceforge.net). I tested it. It works like a charm. There is a small bug (overflow error) that they also show how to fix in their bug report (replacing a few ampersands with exclamation...

Excel: Retricting cursor to protected cells only., input cell, cursor

input cell, cursor, spreadsheet: To my knowledge, EXCEL will not do it the way 1-2-3 did this. The only way to do this the way you want to do it is by writing a macro, which gives many many ways of doing this. The code can protect cells and prevent them from being selected in ways that...

Excel: Retrieve data from one spreadsheet to another, paste link, b25

paste link, b25, windows xp professional: You can set up a link, Robert. First open up both spreadsheets. Select the block A8:B25 in File 1 and copy it to the clipboard. Go to the spreadsheet that will contain the link (File 2), and select Edit/Paste Special. In the Paste Special box, click...

Excel: Retrieve data from a web page, line graph, piechart

line graph, piechart, stock code: Raymond There are options using a macro that will do what you need however I haven t created one so am not sure how well they work. There are also programs that you can add to excel to do what you need though they are usually pay programs. Can you tell...

Excel: Retrieving Data From A Matrix, halifax canada, beaumont tx

halifax canada, beaumont tx, vancouver wa: Daniel. Vlookups are great to do lookups on tables. they won t work on lookup on a matrix. luckily, excel has other functions that work on lists. using the match and offset function, you can do a lookup on a matrix. lets assume that your column headers...

Excel: Retrieving data using two drop-down lists, dropdown boxes, japan usa

dropdown boxes, japan usa, china japan: Sorina: I presume you meant B7 and B10...B14 is just a quick formula to calculate what column of data we need. This setting is part of the options for each individual drop-down box. You can access the options by following these steps: 1) Right-click...

Excel: Retrieving desired numbers combination, 4 digits, colums

4 digits, colums, column c: Shouldn t be a problem provided that you always want them as 2 digits, 4 digits etc =text(A2, 00 ) & . & text(B2, 0000 ) & . & text(C2, 00 ) will do it - I would IMAGINE that the values in A, B and C are formatted as numbers but to display to the required...

Excel: Retrieving desired numbers combinations, colums, column c

colums, column c, b2: Try this, Michelle: =text(A2, 00 )& . &text(B2, 0000 )& . &text(C2, 00 ) This will work only if you need the same amount of digits from each cell (as your sample appears to have). I did not test my formula for accuracy, but you seem to understand...

Excel: Retrieving info from a martix, halifax canada, beaumont tx

halifax canada, beaumont tx, vancouver wa: Daniel Hi I really only do VBA code solutions for the problems presented to me as it states in my profile. I am willing to create a VBA solution for you in realtion to your question but I would need to work on your spreadsheet, obviously on a confidential...

Excel: Retrieving information from a seperate worksheet, product id numbers, allexperts

product id numbers, allexperts, model name: As long as the Product ID is in the first column of the Product List Worksheet (or at least, the first column you are interested in) then VLOOKUP will work fine for you =vlookup(what,where,which,logical) WHAT is the value you want to look up - so in...

Excel: Retrieving Multiple Records from Excel, array formula, multiple records

array formula, multiple records, aidan: Vlookup will only ever return one record - so if you need multiple records, you need EITHER an array formula (see my examples at www.aidanheritage.co.uk/excel) OR simply use data, filter, autofilter to filter the list oh, - and email me at aidan.heritage@virgin.net...

Excel: Return correct age without rounding up, and help with hire anniversary, datedif, decimal places

datedif, decimal places, formating: One fast way of getting rid of the decimals, so it won t round up, is to use the function INT on top of the formula that you have used to calculate the years, something like: =INT(your formula here) Other option is to use the function DATEDIF, for example...

Excel: Return info at an intersection, intersection operator, liking

intersection operator, liking: Without your having been more specific, this formula will return the intersection of column 3 and row 7: =C:C 7:7 (the space is an intersection operator) However, =C7 is easier, so I d need more info to really answer your question to your liking, I think...

Excel: Return oldest date from spreadsheet, array formula, b200

array formula, b200, a200: =min(if(A1:A200= Bellevue ,B1:B200)) Entered with Ctrl+Shift+enter rather than just enter since this is an array formula You can replace Bellevue with a reference to a cell that contains Bellevue =min(if(A1:A200=M20,B1:B200)) -- Regards, ...

Excel: Return value of last non-empty cell in a column, array formula, array formulas

array formula, array formulas, indirect address: Yes. Assuming the moving cell column is column B and you want to divide that number by what is in C1, then =INDIRECT(ADDRESS(MAX(IF(LEN(B1:B65000) 0,ROW(B1:B65000),0)),2))/C1 Press Ctrl Shift Enter (CSE) to enter this formula. To make this work the...

Excel: Return all values that appear in both columns., blank cells, column c

blank cells, column c, question thanks: You would get name ins C1 C5 C6 C10 and so forth. If you want to remove the blank cells and have them in move up to C1 C2 C3 C4 etc, then you can use the method I gave you to select the blank cells and delete them. If you are happy with...

Excel: Returning the month and year a largest value occurred., array formula, hold down ctrl

array formula, hold down ctrl, curly brackets: Hi, John, After looking into this a little further, I found a solution that doesn t require your answer to the message I sent earlier. There is a very elegant way of doing this using a combination of things in Excel. I suggest we create an array...

Excel: Returning the name of a workbook, commodity trading, close range

commodity trading, close range, vba: I m not sure I follow the question, but I suspect you may be running into limitations of excel in what you want to do (although with some VBA it can probably be achieved.) If you want to discuss it further, you can get me by email at aidan.heritage@virgin.ne...

Excel: Returning most recent results., rugby team, microsoft excel

rugby team, microsoft excel, sports teams: Simon It is possible but I would need to know the layout of the page, you can create either a formula that will do it or create some visual basic that will also help. If you can send a copy of the file to littlewing@littlewingweb.com I will have a better...

Excel: Returning most recent score, rugby team, simple matter

rugby team, simple matter, microsoft excel: Ideally, you would have the results stored in a given range - possibly with dates? IF they include dates, then I would also have the RANK function running alongside these to rank the dates - which will basically give you the order. A simple matter after...

Excel: Returning a specific cell value from specific sheet, fantasy football league, joe bloggs

fantasy football league, joe bloggs, cell d5: Hello Neil, I am a little perplexed. The original question was clear and the original answer does what you asked then and what you have restated. If you need me to adjust it for your specific values, then In b1 of Sheet1 put in the formula =Indirect(...

Excel: Returning a true or false value from an array of matched cell values, destination cell, cell values

destination cell, cell values, hi thanks: It seemed that your original request had wanted to know if 2 or more PAIRS of numbers were involved, not 3 or more DUPLICATES. My solution finds a,a,b,b,c,d,e,f would be 2 pairs, whereas it seems you want to know if a,a,a,a,a,a,a is the case. Please confirm...

Excel: Returning a Value, brain fart, cell c2

brain fart, cell c2, cell d2: Don, don t feel silly, this stuff takes some practice to get it right. Anyway, here s the formula you need: =IF(Sheet2!C2= X , Hourly ,IF(Sheet2!D2= X , Weekly , NA )) I assumed the last part (NA) so you can put whatever you want in there. Best wishes...

Excel: Returning a value from one column based on a value in another., microsoft office 2000, using microsoft office 2000

microsoft office 2000, using microsoft office 2000, using microsoft office: Hi, Pam, Let s say that your dates are in A2:A20 and your values are in B2:B20. In D2, you use the formula =MAX(B2:B20) to find the largest value. (It s exactly the same as LARGE(B2:B20,1). LARGE is used more often when you need the item that is 2nd...

Excel: Revealing hidden columns as an option., microsoft excel 2000, using microsoft excel 2000

microsoft excel 2000, using microsoft excel 2000, hidden cells: The checkbox would have to run a macro to do the unhiding/hiding. the password can be inside the macro. Something like: Sub hideUnhide() ActiveSheet.Unprotect mypassword Range( E:J ).EntireColumn.Hidden = (ActiveSheet.CheckBoxes(1).Value = xlOn)...

Excel: Reverse concatenate, cell a1, cell c1

cell a1, cell c1, comma: I presume a sample Cell, A1, contains the following data: First Last,Title Two ways to do it... Method #1: You would need two formulas, one in cell B1: =LEFT(A1,FIND( , ,A1)-1) The second in cell C1: =RIGHT(A1,LEN(A1)-FIND( , ,A1)) Method #2:...

Excel: Reverse order of worksheets, vba code, target value

vba code, target value, local setting: Hi Next stage is debug. Insert the following line after Target = ThisWorkbook.Worksheets.Count: a = MsgBox( Target value = & Target, vbOKOnly) Run the code; does Target equal the worksheets count? (Given your symptoms my guess is that it does...

Excel: Reversing both singular and multiple last name first name seperated with comma, njactb org, esposito vincenzo

njactb org, esposito vincenzo, microsoft excel 2002: Hi Nick, There s nothing more frustrating than working with inconsistent data. Writing a one-size-fits-all solution is virtually impossible when consider things multiple people, suffixes, middle names/initials plus a couple of things not in your list...

Excel: LEFT()*RIGHT(), 4x4 system, c10

4x4 system, c10, digit numbers: Hi Cindy, You can use the following formula to multiply the numbers before and after the x . =LEFT(C10,(FIND( x ,C10,1))-1)*RIGHT(C10,LEN(C10)-FIND( x ,C10,1)) Side note your honor: If you change the little x to a capital X you would need...

Excel: Right function, middle space, trim right

middle space, trim right, cosgrove: Here is one way: =TRIM(RIGHT(A2,LEN(A2)-IF(ISERROR(FIND( ,A2,FIND( ,A2,FIND( ,A2,1)+2))),LEN(A2),FIND( ,A2,FIND( ,A2,FIND( ,A2,1)+2))-1))) Here is another place to look: http://www.cpearson.com/excel/FirstLast.htm -- Regards, Tom...

Excel: Root path's and such, command line switches, shell command

command line switches, shell command, hello tom: Denny, 1. There may be a better way, but you could capture that information from the environ command and then use that value in your shell command. Demo s from the immediate window ? environ( SystemDrive ) C: ? environ( SystemRoot ) C:WINDOWS If...

Excel: Rotate column, pointer changes, mouse pointer

pointer changes, mouse pointer, best bet: Hi Eivind, I d say your best bet is to add a column next to it where you order your numbers 1,2,3,4 etc. This is most easily done by putting a 1 at the top and then clicking back on the cell with the 1 in it to be the active cell. Now, hold down the control...

Excel: Rotate data in cells by row follow on, flexible code, mystart

flexible code, mystart, hi thanks: Mark, you may use modified code- Private Sub tgs() myLast = Cells(56, 2) For i = 0 To 54 Cells(56 - i, 2) = Cells(56 - i - 1, 2) Next i Cells(2, 2) = myLast End Sub Better still if you can use this flexible code that will ask starting row & ending...

Excel: Rotate data in cells by row, vba code, command button

vba code, command button, 1 john: Hi Mark, I am giving the code that can be used below- Sub TGS() count the number of rows to be rotated myRows = Application.WorksheetFunction.CountA(Range( A:A )) myLast = Cells(myRows, 2) For i = 0 To myRows - 2 Cells(myRows - i, 2) = Cells(myRows...

Excel: Rotating schedule, conditional formatting, vlookup

conditional formatting, vlookup, employee names: Eric You would have to have columns of data within the workbook showing what days they work otherwise excel wont know. You can then use vlookup or conditional formatting to show who is working today. If you have a seperate row of each worker and the days...

Excel: Round a fraction, roundup, fraction

roundup, fraction: Try this alterative solution: =IF(INT(TRIM(MID(A1,FIND( . ,A1,1)+1,1)))=5,A1,IF(AND(INT(TRIM(MID(A1,FIND( . ,A1,1)+1,1))) =0,INT(TRIM(MID(A1,FIND( . ,A1,1)+1,1))) 5),A1,IF(AND(INT(TRIM(MID(A1,FIND( . ,A1,1)+1,1))) =6,INT(TRIM(MID(A1,FIND( . ,A1,1)+1,1)))...

Excel: Round to the nearest 32nd, custom option, cabinet company

custom option, cabinet company, fractions: You can format the cells using the CUSTOM option BUT the resulting value would not be rounded, so any calculations using the value would be based on the non rounded figure. To store the value as a rounded figure, something like =(ROUND(Value*32,0))/32...

Excel: Round (VBA), great faith, window application

great faith, window application, excel worksheet: Matthew, Demo d from the immediate window: ? Application.RoundUP(1.1,0) 2 ? Application.RoundUP(1.5,0) 2 ? application.RoundUP(1.9,0) 2 ? application.RoundUP(1.25,0) 2 Personally, I found the solution you settled on, to be rather amateurish,...

Excel: Round off values to specific range, resistor values, vlookup function

resistor values, vlookup function, significant figure: Difficult to do with rounding, but it SOUNDS as though what you want to do is look up a given value in a table of possible results, and return the nearest match - at which point the vlookup function would do what you want (see my explanation at www.aidanheritage.co.uk/Excel...

Excel: Rounding, calculating percentages, excel 200

calculating percentages, excel 200, rounding numbers: the 31.3% is really 31.25%, but if you only show 1 decimal position, it shows as 31.3. Same with the 6.3% - really 6.25%. so these 2 actually add up to 37.5, but the display seems to indicate 37.6. The result is accurate. If you DO display 3 decimal positions,...

Excel: Rounding up to cero, douglas m smith, format cells

douglas m smith, format cells, brainbench: Hi Sandra, It sounds like a number format issue. It would be easier if I could see the file to make sure. If you could email the file to doug@abundant-solutions.com, I should be able to figure it out pretty quick. Douglas M. Smith Brainbench MVP for...

Excel: Rounding in Excel., right angle triangle, left hand corner

right angle triangle, left hand corner, correct conclusion: Hi Richard, How are you? Your site is very interesting. Congratulations and keeping Excelling If you want to stand out the rainfall above 0 you can format those cells conditionally 1. Select the range of the rain fall 2. FORMAT - FORMAT CONDITIONAL...

Excel: Rounding in Excel., right angle triangle, left hand corner

right angle triangle, left hand corner, format cells: Hi again, You can set a separate format for how the zero value is displayed. Number formats have four components, positive, negative, zero and text, and are formatted like this: positive;negative;zero;text Using #0.0 applies this format to the neagtive...

Excel: Rounding in Excel, analysis toolpak, tax tables

analysis toolpak, tax tables, decimal places: Just insert the following function around the above: ROUND(original function, 2) or =ROUND((LOOKUP(L4, TAX TABLES !$A$4:$A$11, TAX TABLES !$B$4:$B$11)*L4),2) The 2 portion tells Excel to round to two digits after the decimal point (rounds to the...

Excel: Rounding in Excel., right angle triangle, left hand corner

right angle triangle, left hand corner, format cells: Richard, Nice web site - like it says, something for everyone. As for the 0 matter, this routine will format only the cells that are exactly 0 as a General number (thus they will appear as a 0), else they are formatted as #0.0. The routine starts in...

Excel: Rounding numbers to NEAREST 100, rounding numbers, whole numbers

rounding numbers, whole numbers, co worker: I hope you ve found your answer by now and apologize (profusely!) for the delay in responding. Just in case, here s the answer: =round(a1,-2) =round(a1,2) will round to the nearest HUNDREDTH (2 digits to the right of zero) while -2 makes it round to...

Excel: Rounding problem in Excel, cell a2, cell a1

cell a2, cell a1, exact numbers: Hi Steven How are you? The easiest way is to use temporary Column A1: 125.3358404 A2: 33.91638312 B1: = roundup(a1,2) Copy B1 to B2 B3: =Sum(B1:B2) Select B1:B2 Edit - copy Edit - paste special Choose Value I hope this suits you. Cheers...

Excel: Rounding value, cell content, decimals

cell content, decimals, roundup: =ROUND(B11*16/100,n) for rounding =ROUNDUP(B11*16/100,n)for rounding always up =ROUNDDOWN(B11*16/100,n)for rounding always down where n is decimals you need. ROUND 1.25 = 1, 1.65 = 2 ROUNDUP 1.25 = 2, 1.65 = 2 ROUNDDOWN 1.25 = 1, 1.65 = 1 Regard...

Excel: Rounding off, cell d1, cell d2

cell d1, cell d2, salary figure: Hi Stephanie, I am not clear if you are asking about rounding a number or the number of decimal places displayed. I will explain the difference. 1) Displaying decimal places: In Excel you can select a cell, such as the cell containing the yearly salary....

Excel: Roundup function in Excel 2000 Professional, cell a1, excel 2000

cell a1, excel 2000, roundup: Create a brand new workbook. In cell A1, type the following: =ROUNDUP(32.44,0) and hit enter. The result should display 33 . If you have a current workbook that just shows you the formula (and not the result), then first select the cell, hit Edit--...

Excel: Routine for counting dates from multiple worksheets, countif, vba macro

countif, vba macro, pivot tables: I need more information. Can you do anything to change the layout of the individual sheets? Are you in fact counting up the # of dates in Col D by YEAR? Because the easiest way to do this would be to add a column that converts the dates in Col D to its year...

Excel: Row to column, border area, left border

border area, left border, ogilvy: Moshe, in the Left border area, select the entire row or select just the cells you want to copy (if less than an entire row), then from the menu select Edit= copy. This puts the data in the clipboard. Now select the top-left cell of the location where...

Excel: Row count in excel sheet, rowcount, excel sheet

rowcount, excel sheet, quick reply: I overlooked a line in your code originally. Had I seen that then I would have given you the answer. After I saw the error message you were getting I then saw the line of code I overlooked. The reason for your error is you have declared a as an Integer...

Excel: Row Height, rng, pseudo code

rng, pseudo code, a400: If you have some information in the rows that would mark which rows should not change height: Here is some pseudo code representing an way to do it. Assume if the cell in column A starts with an A then it should not have its height adjusted. ...

Excel: Row number, excel vba, ogilvy

excel vba, ogilvy: Hello David, It is a good think to get away from selecting as it just slows you code down. You can put the row number of the last row in a variable named lastrow (in the example) using this code: Dim lastrow as Long Lastrow = Range( A65536 ).End(xlup).row...

Excel: Row number, douglas m smith, excel vba

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...