foe example, cell d2, blank row: Al, the suggestion, without being rude, is that you need to be more careful. What I mean is this. In Excel, the formulas are designed to update themselves as long as the cells that the formula refers to are updated in some way. When you insert a row above...

Excel: Making Charts and Graphs in Excell, don crosby, making charts and graphs

don crosby, making charts and graphs, gross rents: I m having a hard time picturing what you want this to look like -- feel free to fax me a mockup (or real printout) of what you want/already have, and once I have the picture I can help better. fax# is 845-365-3629; email is bobumlas@yahoo.com (which would...

Excel: Making Excel database, confirmation button, database capabilities

confirmation button, database capabilities, drop down boxes: Hi Dzonis, Your idea of a protected sheet should be sufficient for this kind of a scenario. However if you do not want the user to even view certain sections of your spreadsheet...trying having one sheets for inputting data and another summary sheet which...

Excel: Making Excel handle a larger file size, max column, there is not enough memory

max column, there is not enough memory, memory problems: The following website contains a LOT of useful information on memory problems with Excel: http://www.decisionmodels.com/memlimitsc.htm Generally speaking, your best bet it to close any programs you are not using (including the little icons in the system...

Excel: Making Excel worksheets work together, common denominator, inventory product

common denominator, inventory product, allexperts: 1 - When you say each worksheet would be linked to an Expenses Sheet, why not simply use the expenses sheet itself and possibly many copies of the expenses sheet -- what do you hope to gain by linking to the sheet instead of just using the sheet? 2 - You...

Excel: Making history spreadsheet, target value, target address

target value, target address, time cells: I think that a formula updating itself does not trigger this event. You might try the Calculate event. To run a routine at a certain interval then use the OnTime method of the Application object, Application.OnTime There is a good example in VBA...

Excel: Making a hyperlink to a directory, object browser, color index

object browser, color index, optional arguments: Darryl, I do not think you can hyperlink to a directory, just an individual file or URL. You could, however, fairly easily recreate the effect with a macro designed to look like a hyperlink. In cell E3, I put the words Open Directory , then I changed...

Excel: Making labels out of certain Excel entries, mail merge labels, excel spreadsheet

mail merge labels, excel spreadsheet, thousand miles: Hi, Dawn! I am so sorry for the delay in my response. I was over a thousand miles away from my PC. :) I hope that you have gotten an answer by now, but just in case... Use Word s mail merge feature. --Create a column in Excel called Label --For...

Excel: Making a macro public., xls workbook, excel 2002

xls workbook, excel 2002: You will either have to have that workbook open all the time or put the code in the Personal.xls workbook, which will invlove some copying and pasting. And you don t have to copy and paste the code, just export and then import the modules and any forms ...

Excel: Making Text appear, cell a1, ms excel

cell a1, ms excel: Hi Ethan, If you use the IF Function you can do that. Such as. =IF(A1= Yes , This text will only appear if cell A1 has text in it , ) If you paste the above formula anywhere in your sheet except for cell A1. And then type the word Yes in cell A1...

Excel: Making table, douglas m smith, location option

douglas m smith, location option, unique records: Hi Yuya, I don t see that this is possible using only formulas, however, there are other ways to do it. Copy the second column of letters down below the first column of letters. Now select Filter from the Data menu, and the Advanced Filter from the...

Excel: Making worksheets visible with a condition., target value, target address

target value, target address, sheet tab: Hover the mouse over Target.Address to see what called in the change event -- ANY change to the worksheet will call in the macro -- you only want it to exexcute particular code when the cell address is B5, B7, etc. Are you certain it s B5 that is the cell...

Excel: Making worksheets visible with a condition., target value, target range

target value, target range, sheet1: regarding the cell where type a value in: - it, of course has to be on a non-hidden sheet. - place the code below into the Worksheet - code page (let s say Sheet1 s - the code shows that if you type 1 into cell A1 then Sheet2 becomes visible, any other...

Excel: Managing pricing list, general ledger system, blank cells

general ledger system, blank cells, paste values: You can do it either with formulas or with a macro. Let s take a look first to a solution with formulas. To do so, you can pick a column (for example, J), choose one of the columns you want to fill (column A, description), and start with a formula like this...

Excel: Mandatory Entry in Cells, mandatory entry, design approach

mandatory entry, design approach, ogilvy: Katie, There is no built in mechanism to enforce that a cell be filled in or the workbook can not be saved. The closest would be to write VBA code to validate the required conditions, but this can be easily defeated by disabling macros. Work arounds...

Excel: Manipulating Query Parameters, query parameters, select count

query parameters, select count, excel worksheet: If doing this in VBA SELECT Count(*) From Orders WHERE SALE_DATE= & format(Range( B5 ).Value2, m/d/yyyy ) & or SELECT Count(*) From Orders WHERE SALE_DATE= & Range( B5 ).text & Sorry for the late reply, but my email provided...

Excel: Mapping, cell a2, standard bank plc

cell a2, standard bank plc, usig: There s no way to assure you re going to find matches. This formula will find matches based on the 2nd file s data BEGINS with the master file s data: assuming the Standard bank is in cell A2 of the master file, this formula will return the row in the 2nd...

Excel: Mapping, standard bank plc, independent maintenance

standard bank plc, independent maintenance, vlookup function: Parmesh Sounds like you need to find matches where the data doesn t match because of its independent maintenance. If that is the case then I suggest we create some system that will allow you to match the data. I don t know what all of your data looks...

Excel: Marco: Search, copy, paste... more info, vba project, button menu

vba project, button menu, excel workbook: Hi, Not sure what you are looking for. I don t have a backup what code I gave you. Anyway, to copy the code: you need to copy the code to your excel sheet. Open the excel workbook and Press Alt + F11. and select the vba project (your workbook name)....

Excel: Marco, explicit function, obj

explicit function, obj, pct: Paul, Hope you are doing well. This might help you. Option Explicit Function make_bigger(obj As Object, pct As Double) obj.Width = obj.Width * pct obj.Height = obj.Height * pct End Function Sub main() Call make_bigger(ActiveSheet.Shapes(...

Excel: Marcro in own Icon -> Shortcut? (in Excel 97), keyboard shortcut, programming problem

keyboard shortcut, programming problem, menu point: Markus, Generally, you assign an icon on your toolbar to a MACRO, not a shortcut key. If CTRL+g starts your macro, and by pushing an icon you wish to start the macro, then just assign the macro to the icon and bypass the shortcut key. Assuming the macro...

Excel: Marks List, bangalore india, data labels

bangalore india, data labels, response asap: Dear Shanker, I think you have not tried on the given assignment please select the data of students and marks click on insert menu select chart and proceed the other steps and complete. Once you finish of and the chart is on your excel sheet by right cliking...

Excel: Marks List, bangalore india, response asap

bangalore india, response asap, rediffmail com: As this is an assignment, you should be doing it based on whatever training you have been given - however, for the first part, SUM and AVERAGE are functions you should be looking at - I m not sure what is meant here by percentage. for the second part, data,...

Excel: Mass Formulation!, cell a2, k2

cell a2, k2, cells: J If you want to sum the numbers in the range and then multiply by 1.5, then you need a formula like this-- =+sum(A1:K10*1.5) This adds A1 thru K10 and then multiplies the total by 1.5. If you want to multiply each cell in the range by 1.5, then...

Excel: Mass hyperlink deletion in Excel?, hyperlinked name, amazon

hyperlinked name, amazon, thanks in advance: Brian, I sincerely doubt that the hyperlinks are the reason it won t open. But I can t diagnose the real reason remotely. In any case, I think the easiest way to get rid of the links is to do the following: 1) Open up a new spreadsheet. 2) Go go the...

Excel: Master Worksheet, word feature, master document

word feature, master document, htis: James I m not familiar with the word feature that you are talking about, but Excel can link you to other excel worksheets. You can link one cell or the whole document. The linking formula looks something like this +Sheet2!B14 Where Sheet2 is the sheet...

Excel: Match Bar Chart Bar Color with Cell Color, ms windows 2000, colorindex

ms windows 2000, colorindex, cell a1: Hello Bob, Glad you like the website. xColor = Range( A1 ).Interior.ColorIndex ActiveSheet.ChartObjects( Chart 1 ).Chart.SeriesCollection(1).Interior.ColorIndex = xColor It s probably apparent, but if you change the series collection to 2,3,4,etc....

Excel: Match and Enter Value, cell b2, vba procedure

cell b2, vba procedure, vlookup function: Hi Mark, Here is a macro that does what I believe you want. Just select a cell in column B (or any other column for that matter), and run the macro. If there is a matching cell in column A, it should be selected. ____________________________________________________________...

Excel: Match & Offset Function, cat d3, reference example

cat d3, reference example, value error: Grant, The OFFSET formula requires 3 arguments in this instance. The first is a reference, second the number of rows to be offset and third is the number of columns to be offset. Take the following formula as a very simple example: =OFFSET(A1,4,2) The...

Excel: Matching over 2 sheets, sheet1, title style

sheet1, title style, style color: For example, say that your 6000 row sheet is Sheet2, and it s got Style#s in Col A and Colors in Col B. It s simplest to create a Col C to concatinate. E.g., in cell C1 put the formula =A1&B1 and copy it down the 6000 rows of the Col. Now assume you ve...

Excel: Matching Data, coloum, refs

coloum, refs, step 1: Makes more sense. This routine assumes your data is in fact in columns A & B: Sub Align() For i = Range( A65536 ).End(xlUp).Row To 1 Step -1 If Cells(i, 1).Value & .jpg Cells(i, 2).Value Then Cells(i, 2).Insert xlDown ...

Excel: Matching two fields in excel then copying the data over, excel macros, vlookup formula

excel macros, vlookup formula, information columns: My suggestion would be to use the VLOOKUP formula instead of macros, something like: =VLOOKUP(A2,Sheet1!$A$1:$B$1000,2,FALSE) If you really need a macro, you can still use that formula, something like: Sub CopyLookup() Range( C2 ).FormulaR1C1 = ...

Excel: Matching ID's, excel sheet, ogilvy

excel sheet, ogilvy, windows system: Assume in sheetA, the first ID is in A2. In C2 put in a formula =if(iserror(match(A1,SheetB!A:A,0)), , X ) then drag fill down the column. You can then filter on the X and copy the data to another worksheet. This assumes both sheets are in the...

Excel: Matching Part Numbers from Various Excel Documents, vlookup, price column

vlookup, price column, time operation: Since this is a one time operation, if the other workbooks are all in the same format (you said they were not in the same format as your master list, but nothing about whether the format is consistent in the other books), and this appears to be a one time...

Excel: Matching records in Excel, address phone number, number column

address phone number, number column, matching records: Sorry you feel that way. Please don t blame me for your post not being written in such a way that would give you the real answer you really want. I just answered your question. ...Is it possible to find and flag every record in Table A for which...

Excel: Matching records in Excel, conditional formatting, address phone number

conditional formatting, address phone number, number column: Sure. Use vlookup. You ll first have to make absolutely sure that the format of both cells is the same. In other words, you can t have one formatted to put the hyphens in for you and the other has the hyphens typed in. Read this article: http://office.microsoft.com/en-us/excel/HP052093351033.aspx...

Excel: MatchText without Using Control Toolbox, control toolbox, macro code

control toolbox, macro code, easy solution: It is a feature of a ComboBox from the Controls Toolbox. You can right click a cell that is in a column of data & choose pick from list to display a list of that column s unique entries. Excel autofills cells that are part of a column of cells by trying...

Excel: Matrix in Excel, function index, matrics

function index, matrics, f6: Hi Emmar, How are you? Please try the following function: =index(B2:F6,2,2) B2:F6 is the matrice, A3 refers to the second row of the matrice C1 refers to the second column of the matrics. But you can also do =Row(A3)-1 in H1 =column(C1)-1...

Excel: Max, max value, absolute reference

max value, absolute reference, aidan: Difficult to answer with the information provided. (1) system set to manual calculation (2) formula has an absolute reference which would not update (3) formula has a range which returns the same result as the previous range (4) probably other reasons - any...

Excel: Max and if in Excel, array formula, ogilvy

array formula, ogilvy, thanks a bunch: Type 1 2 3 4 in row 3 Assume 1 is in A3 in A4 =Max(if(1:1=A$3,2:2)) Enter with Ctrl+shift+Enter rather than just enter since this is an array formula. If you do it correctly, if you reselect the cell and look in the formula bar you will...

Excel: Max "If" Function, array formula, curly brackets

array formula, curly brackets, max function: You can use the MAX function as an array formula, for example, type the following formula: =MAX(Z1:Z10*(C1:C10= volatile )) and enter it with Ctrl+Shift+Enter. Excel will put curly brackets around the formula to show that it is an array formula, and it...

Excel: Max function, max function, c103

max function, c103: Hi Thomas, Unfortunately, the MAX functions only work with numbers. As soon as you put a letter in to a cell it is considered text. You could probably strip the first letter from each cell, and then use the max function, but I am not sure if that is an...

Excel: Max(if())), array formula, sumif

array formula, sumif, ogilvy: Assume the name Susana is in row 3 and column A. =max(if($B$1:$G$1= Rt ,$B3:$G3)) Entered with Ctrl+Shift+Enter rather than just enter since this is an array formula. After entering the formula, reselect the cell and look in the formula bar. The...

Excel: Maxa - return a higher specific number, time formats, time format

time formats, time format, dennie: Dennie: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/maxtime.xls I suspect I am missing some vital information from your question, because as you can see in my example, simply using MAX or MAXA on the numbers you...

Excel: MENTOR FOR FIRST REAL JOB EXCEL APPLICATION, nuclear power school, chemical inventories

nuclear power school, chemical inventories, honeywell inc: your IF statement sounds good - in english, this would be if c2 is greater than 0 use B3 otherwise show a blank value which translates to =if(c2 0,b3, ) For monthly and yearly totals I can think of various ways of handling it, but possibly the easiest...

Excel: MERGING CELLS AND FORMATTING IMPORTED DATA, kathleen kathleen, cell a1

kathleen kathleen, cell a1, cople: Kathleen I am still having problems accessing the website, so hopefully you will be able to use my other suggestions. Richard I am having a real problem with the internet tonight, so i can t go to the website and see what your download looks like....

Excel: Measure time spent, format cells, bill time

format cells, bill time, sum formula: Bill, Time is a special case of date. However, that fact is easily ignored just by formatting it. Treating it this way does not need to be considered in your calculations. It works exactly the same. format your cells using a custom format format=...

Excel: Measure time spent, time column, measure time

time column, measure time, time of day: If you enter 1:30, the cell will contain 1:30, and the formula bar will show 1:30:00 AM. My suggestion is to either not look at the formula bar (!) or enter 1.5 instead of 1:30. The cell WILL read as you wish, so I m not sure what you re not liking. Just adding...

Excel: Measuring total time spent, time column, defeating the purpose

time column, defeating the purpose, time log: Bill It s amazing to me how many questions I get regarding time in Excel. For your case, you are trying to get Excel to do something it doesn t want to do. Because time is decoded as a value in continuum, Excel sees anything entered with a colon as a...

Excel: Memory error when printing using Excel, blank spreadsheet, memory issue

blank spreadsheet, memory issue, memory check: I would say that memory is the first thing to check - both physical and swap file memory - check the memory usage by going to the task manager and see what is running at that point - whilst you may have enough memory on the computer, this would give some insight...

Excel: Menu Bar, custom menu bar, guy answer

custom menu bar, guy answer, menu style: Look in help to see how to create a custom menu bar. It takes too much typing here to explain the steps. To have your custom menu bar display or appear when a certain workbook is opened, which is what I understood you wanted, then you will have to write...

Excel: Menu question, bill reading, menuitem

bill reading, menuitem, level menu: Simply type Case 4 BEFORE the END SELECT and AFTER the If Divider Then SubMenuItem.BeginGroup = True line A simple Select Case statement is TestNumber = 2 Select Case TestNumber Case 1 do something that pertains to 1 Case 2 do something that...

Excel: Merge Date and Time, decimal fractions, gps readings

decimal fractions, gps readings, whole numbers: Assuming cells are formatted properly as date and time (and for this example the date is in A2 and the time in B2) =A2+B2 would return the combined date and time - which I think is what you need to do (dates are whole numbers, times are decimal fractions...

Excel: Merge Excel Files, exact setup, visual basic tutorials

exact setup, visual basic tutorials, exforsys: Sam It was supposed to merge under one another from reading the macro. If that isn t suitable the only other option I can think of is to ask an expert in VBA and they may have another script that would be more suitable or if you have access to Access...

Excel: Merge two or more excel database sheets, jim mcleod, column headings

jim mcleod, column headings, menbers: It sounds like you want to combine all the data you have into one master copy, correct? If so, then set up a master with one field that you can use as a reference and place it in column A. Then use VLOOKUP to reference the other worksheets one at a time....

Excel: Merge Files, microsoft excel 2002, vbe

microsoft excel 2002, vbe, window xp: Select all the sheets from one workbook and use Edit/Move or copy sheet, follow the prompts. From that same workbook you re copying, go to the VBE (where the macros are -- Alt/F11), and either select all the macros & copy/paste them into the macros of the...

Excel: Merge and Match, employee ids, case mgr

employee ids, case mgr, employee id: Everything to the right of EmployeeID on sheetB you can retrieve using Vlookup. =Vlookup(Sheet1!A2,Sheet2!C1:Z14000,4,False) place in the next available column in SheetA, 2nd row, should get the column F value for the match of ID in column C - so from...

Excel: Merge and Match, employee ids, case mgr

employee ids, case mgr, vlookup formula: =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$F$100,3,FALSE)), A1& not found ,VLOOKUP(A1,Sheet2!$A$1:$F$100,3,FALSE)) First the formula. Assume the first EMP ID is in A1 of the current sheet. The more complete list of data is in Sheet2 and is in A1:F100 in my...

Excel: Merge and Match, employee ids, case mgr

employee ids, case mgr, turn around time: Thank you for your kind words - and the very detailed question - in a previous job I used to say when asked about Excel that the answer was vlookup and THEN ask them the question - and that is the function you will need - this is one of the most useful functions...

Excel: Merge for matched data, employee ids, case mgr

employee ids, case mgr, vlookup: Lori Without actually seeing your spreadsheet I can t give a definite answer, but you should look at VLookup. If both tables are sorted (and kept sorted via the one identical column) you can use the vlookup to get your information in sheet a from sheet...

Excel: Merge records, import data source, spreadsheet control

import data source, spreadsheet control, external data sources: Hi Parmesh, (1) To import that many rows into Excel and split it between multiple worksheets will generally require a macro. (Note: When Excel 12 comes out, it will have one million rows.) Another option is to use the spreadsheet control but this can...

Excel: Merge, column c, hth

column c, hth, b1: Via a formula, say in column C: =B1&A1 or if you want separation =B1& &A1 then fill down as far as necessary. If you want it to REPLACE one of the columns, then select this col with formulas, Edit/Copy, select A1 (or B1), Edit/Paste Special, click Values....

Excel: Merged cell trouble, pc memory, mid way

pc memory, mid way, access database: Hi Charissa, It sounds like you are running into Excels limits. Excel 2003 and lower versions has a cell display limit of 1024 characters. Although in the formula bar you can see up to 32767 characters. Excel 2007 has just been released. The new...

Excel: Merging 2 columns, cell a1, cell b1

cell a1, cell b1, zip code: June You can merge the two together by using the following of formula. Assume the first zip is in cell A1 and the second is in cell B1. the formula would look like this: =+A1& - &B1 I assumed that you wanted a dash between the first part of the...

Excel: Merging 2 columns, cell a1, mouse pointer

cell a1, mouse pointer, quotation marks: Hi June, You can use the ampersand sign & to join separate items. If your zip code is in cell A1 and the 4 digit additional zip code is in cell B1 then the formula would look like this =A1&B1 You may want to add a space or a minus sign - To do...

Excel: Merging 2 worksheets, unqiue, ogilvy

unqiue, ogilvy, names and addresses: Joey, I guess it depends on what you mean by merge. To physically merge the two lists, you can copy one list and paste to a new worksheet, then copy the second list and paste immediately below the first pasted list in the new sheet. Then sort the combined...

Excel: Merging cells in Excel, fancy text manipulation, microsoft excel 2000

fancy text manipulation, microsoft excel 2000, hold down ctrl: Oh, Bryan, this is a cinch! You ll be surprised how easy it is. Let s say that you have first names in A1:A4500 and last names in B1:B4500. You d like to have full names in C1:C4500. Follow these steps: 1) Click in C1. 2) Press F5, a shortcut to the...

Excel: Merging cells in a shared workbook, microsoft excel, bad idea

microsoft excel, bad idea, spreadsheet: Firstly I would strongly recommend against sharing workbooks - it is generally considered a BAD idea by experts in Excel. Secondly, no there is no way round it - excel will try to keep track of changes by all users to each cell, and would get very lost if...

Excel: Merging columns in Excel, specifics, b1

specifics, b1, c1: Well of course it does just row one if you did not copy and paste the formula down to row 1167 inclusive. You need to copy and paste the formula down to row 1167 to merge each row s data from A and B to C. I assume you want row 1 s data from A and B to...

Excel: Merging columns in Excel, hi bob, excel 2000

hi bob, excel 2000, b1: In a 3rd column, enter =A1&B1 or =A1& &B1 (if you want a space separating them) and then fill down as far as necessary. TO remove the formula, you can copy this column & paste/special values on top of one of the original columns (A?), then clear B &...

Excel: Merging Data from two Workbooks, book2, key field

book2, key field, variable names: If the key field is the leftmost field, and both workbooks contain the info in coluns A:D, you can use VLOOKUP to finish the A,1,2,3. In workbook 1, enter this in E1(A1 has A, B1:D1 have 1,2,3): =VLOOKUP(A1,[Book2]Sheet1!$A$1:$D$1000,2,FALSE) in F1: =VLOOKUP(A1,[Book2]Sheet1!$A$1:$D$1000,3,FALSE)...

Excel: Merging data lisys, busy at work, spreadsheets

busy at work, spreadsheets, spreadsheet: You would need to use a macro to do this. Scanning down one list, you d have to search the 2nd list to see if there s name match, then add the 2nd list s numbers to the end of the first list, and continue on to the end of the list. Once you finish that list...

Excel: Merging data

Amita If you are familiar with access you can use that to import the information into a database and collate your information that way. If you don t know or have access the easiest way would be to put all the excel files into separate sheets in the same...

Excel: Merging From Excel To Word, excel spread sheet, decimal place

excel spread sheet, decimal place, word doesn: Yes, this is an irritating little issue with MS-Word. When you insert a merged field from Excel into Word, the formatting does not carry over from the source spreadsheet. You have to format it in Word. This doesn t sound like much of a big deal at first,...

Excel: Merging Excell Workbooks, student id, misnomer

student id, misnomer, spreadsheets: Hey Ray...I m going to discuss several items here...they may be considered steps (except for #1). 1) Just want to point out that Excel has a merge feature (you may have noticed it), but it is a misnomer. It does not merge in the sense you are looking for....

Excel: Merging Spreadsheets, widgets, hth

widgets, hth, spreadsheet: What s wrong with copying the data from one and pasting it to the bottom of the other? Or do you want a 3rd spreadsheet which only has the companies from widgets that are also companies from gadgets? If the latter: On the gadgets sheet, in an unused column...

Excel: Merging Spreadsheets into one document (tabs), excel spreadsheets, drop down menu

excel spreadsheets, drop down menu, ms excel: Hi Robert, It s been awhile since I ve used version 97, but I think you should be able to right-click on the tab part of the sheet you want to move and select move or copy, then check the box for create a copy and choose from the drop down menu the file...

Excel: Merging two spreadsheets into one, mouse pointer, excel spreadsheet

mouse pointer, excel spreadsheet, customer view: Hi Leslie, Open both workbooks. Hover your mouse pointer over the first sheetname in one of the workbooks and right-click and select Select All Sheets then right-click again and select Move or Copy... select the drop-down list underneath where it reads...

Excel: Merging two worksheets using VLookup, key field, occurrences

key field, occurrences, uniqueness: If you have ONE occurrence of the key in the main sheet and several occurrences in the 2nd sheet, you d need VBA to do it. If there s a one-to-one match, you can do it by a more complicated set of formulas. In each sheet you d need a new key field. If the...

Excel: Message boxes in vba, excel system, message boxes

excel system, message boxes, fundamental limit: Alan, Strange that your Excel/system split that msg into 2 parts - mine did not. Nevertheless, try this method to get more lines into your msgbox. There is a fundamental limit to the size of the msg - Excel will simply truncate anything over a certain...

Excel: Message when opening an excel file, drop down boxes, inbetween

drop down boxes, inbetween, final result: Hi Sergio, You can put a simple macro into the worksheet to show up each time the workbook is opened. Open the workbook that you would like this message on and press Alt+F11 this will open the Visual Basic Editor. You will notice on the left-side a file...

http://en.allexperts.com/q/Excel-1059/Method-Add-object-Validation.htm

win2000 sp4, validation rules, astriks: I ve taken your code out of the process, and written a basic subroutine to set data validation using it alone - Dim datarule datarule = =a1=1 With Range( A1 ).Validation .Delete .Add Type:=xlValidateCustom,...

Excel: Microsoft Exce;l, microsoft exce, column headings

microsoft exce, column headings, data entries: Jack, are you wanting to delete *everything* except for the column headings? Are the column headings all in row 1? If that s the case, then you should select entire rows, from 2 all the way down. Here s an example: 1) Press F5 to bring up the Go To box....

Excel: Microsoft Exce, microsoft exce, b1 and b2

microsoft exce, b1 and b2, excel 5: Hi Carmen, How are you? Please see the following example 1. tools- macro - vbe 2. Insert- module 3. copy Public Function namef(param1) As Integer Dim A As Range Set A = Range( a1:B2 ) t = 0 For i = 1 To 2 For j = 1 To 2 t = t +...

Excel: Microsoft Excel, followup question, partial answer

followup question, partial answer, microsoft excel: DaN Ok Dan now I think I understand--- This is the type of formula that you need =SUM(A1:G1)-(SMALL(A1:G1,1)+SMALL(A1:G1,2)) The formula can also be written like this, and it looks a little simpler because it has less parenthesis. =SUM(A1:G1)-SMALL(A1:G1,1)-SMALL(A1:G1,2)...

Excel: Microsoft Excel 2000 Font Color Toolbar Button, microsoft excel 2000, color button

microsoft excel 2000, color button, formatting toolbar: The code I sent you worked in Excel 2003, not 2000. This works in 2000: Private Sub Workbook_Open() Workbooks.Add Set cb = Application.CommandBars( formatting ) SendKeys {down 2}{right 5}{down 1}~ cb.Controls(21).Execute ActiveWorkbook.Close...

Excel: Microsoft Excel 2000, microsoft excel 2000, totr

microsoft excel 2000, totr, excel 5: Hi Jeremy, How are you? Please try the following (I assume your date is in column B in both sheets. The last cell with a date is B2000) (Make a copy of your file in case some problems occur !!) 1. Tools - macro - visual basic editor 2. insert-...

Excel: Microsoft Excel 2002, microsoft excel 2002, circular reference

microsoft excel 2002, circular reference, circular references: Carolyn I m terrible at charts, so I m not a very good one to answer a chart question. However---sounds like your references in the chart are circular. Make sure your references are only to the subtotal line, and that the subtotal line doesn t include...

Excel: Microsoft Excel 2003 - macro query., conditional formatting, photgraphs

conditional formatting, photgraphs, alan smith: As the number of photgraphs and captions can vary, why not have a column that indicates the number of photographs and captions expected? You could use vlookup to retrieve the number of records you are searching for and COUNTIF to count the number of photos...

Excel: Microsoft Excel 2003, move selection, page moves

move selection, page moves, arrow keys: Sounds like you accidentally pressed the scroll lock button on your keyboard. Down at the bottom of your Excel app, if you have the statusbar visible you should notice NUM if numlock is turned on and SCRL if scroll lock is turned on. Merry Christmas,...

Excel: Microsoft Excel, douglas m smith, empty cells

douglas m smith, empty cells, lee ray: Hi Jeana, I m sorry about the delay. Sometimes AllExperts notifications get lost or end up in my spam folder. There s no way to do this directly other than the manual method. However, the following macro should do the trick: Sub BoldLastNames()...

Excel: Microsoft Excel 97 VBA, microsoft excel 97, texta

microsoft excel 97, texta, word wrap: Hi Katherine, How are you? I have tried the following 1. Allocated the name Texto to A1 2. Draw a userform with a textbox named texta and a command box 3. Assign the code to the button Private Sub CommandButton1_Click() ActiveWorkbook.Sheets( Form...

Excel: Microsoft Excel Combo Boxes, cell c2, c cells

cell c2, c cells, combo boxes: Hello Brandon Since you provided no specific information on the layout of the subs sheet, I will assume the unique identified for the sub which you have listed in the dropdown is in column A of the Subs sheet. if the subs worksheet has data like ...

Excel: Microsoft Excel File Compare, word comparisons, blank copy

word comparisons, blank copy, link creation: Hi Amy, I don t have any really good thoughts - especially not for MS Access. I ll share a thought or two about Excel and Word comparisons. Word: This app has a compare feature. Open the edited version of the document (the student copy for example)...

Excel: Microsoft Excel formula, cell b2, cell b1

cell b2, cell b1, cell a2: At one stage in my life I used to get phone calls and always answer them with The answer is Vlookup, now tell me the question - and that is indeed almost the solution to your problem - I have a web page that explains it www.aidanheritage.co.uk/Excel ...

Excel: Microsoft Excel - Linking of worksheets, pankaj gupta, candidate names

pankaj gupta, candidate names, paste link: Copy from A01 s individual workbook. Go to the master workbook. Hit Edit-- Paste special-- Paste link. If that doesn t work for you, then I m afraid I don t understand your requirement and you ll have to explain in another way or give a better example....

Excel: Microsoft Excel - Linking of worksheets, pankaj gupta, candidate names

pankaj gupta, candidate names, a04: Assuming the data in master does not move around, then Open A01 and Master in workbook A01, put in formulas like =[Master.xls]Sheet1!A1 If the data in Master is contiguous, you can select this and drag fill across or down or both to link to other cells...

Excel: Microsoft Excel - Linking of worksheets, pankaj gupta, candidate names

pankaj gupta, candidate names, a04: Yes this is possible but it will really require a macro. But, I don t just write macros for people on this site who need one, want one, and/or seem to think I am expected to do so. 99% of all macros are more involved than what you think and rarely am I...

Excel: Microsoft Excel and Word problem, mouse pointer, journal feature

mouse pointer, journal feature, anti virus software: I don t THINK it will be a virus, although it s odd that it applies to both Word AND Excel - it s POSSIBLE that it s the journal feature of outlook, or fast indexing that is causing the problem - I would suggest trying to turn these off. Certainly is worth...

Excel: Microsoft Excel - wrapping text in cells, text boxes, column width

text boxes, column width, microsoft excel: This is (I m afraid) I feature of text wrap - possibly pronounced bug - that it does exactly what you describe - there is little or nothing that can be done about it - possible solutions include using text boxes if the text that is to wrap is to be a heading,...

Excel: Microsoft Excel, microsoft excel 2002, circular reference

microsoft excel 2002, circular reference, cumulative totals: All you asked was is there a way - not specifically how to do it - and that is what I answered. Too many times I have assumed what the real underlying thing is that a person wants to know ( how to create the totals as opposed to is there a way ) in his/her...

Excel: Microsoft Excell, microsoft excell, vlookup function

microsoft excell, vlookup function, spreedsheet: The tax table isn t set up right to do what you want - you have text, and you need numbers. Change the table to: 0 20000 10% 20000 39999 15% 40000 59999 20% 60000 1e10 30% (that s the equivalent of 10,000,000,000) Also, let...

Excel: Microsoft excel, option windows, task bar

option windows, task bar, microsoft excel: Sumalatha I don t know why this setting would be changed just because you opened a shared file, and I don t believe there is a shortcut to reset that setting. An option may be to use Alt Tab to cycle through each of the sheets. It won t display them...

Excel: Microsoft Word & Excel, driver video card, xlstart directory

driver video card, xlstart directory, excel programme: I am not aware of any setting in Word which would cause this behavior (of course, I don t claim to be an expert in word.) Usually such problems are centered around an incompatibility with the the video driver or settings related to the video driver/video...

Excel: Might be a macro question., keyworker, colour series

keyworker, colour series, excel spreadsheet: Hello, Adam, nice to meet you. This was a very nice message, and I d like to help you out. I have an idea about what you need to resolve this, but it s hard to tell for sure without seeing the spreadsheet. Why don t you send me a little portion of your...

Excel: Migrating code modules and forms to a new workbook, excel coding, migration utility

excel coding, migration utility, userforms: Arif, if you are doing the same event for every worksheet, then you should use the workbook level equivalent event. It is found in the Thisworkbook module which you can get to through the project explorer or if your work is a Window rather than maximized,...

Excel: Mileage Sheet Calculations, basketball officials association, column c

basketball officials association, column c, c note: Doing it this way gets around the limit: =IF(AND(A1= Chico ,A2= Biggs ),52, )&IF(AND(A1= Next City ,A2= AnotherCity ),52, ), )&IF(AND(A1= City3 ,A2= City3A ),52, ), )&IF(AND(A1= City4 ,A2= City4A ),52, ) etc. Each IF(...) is complete, so there s no...

Excel: Min to Max Copy of Range, setb, setc

setb, setc, thanks in advance: Sub copySets() the workbook should have the sets on Sheet 1, and 4 additional sheets to receive the copied sets Dim wksCopyFrom As Worksheet, wksCopyTo As Worksheet Dim topRow As Range, botRow As Range Dim iSetNumber As Integer Set...

Excel: Min, Max, Standard Deviation Graphing, max lines, b1000

max lines, b1000, data changes: It s OK if the values change, the graph should reflect the change as well. If you mean that the location of the data changes (or the lnegth), then you ll either need a macro to put the formulas in the right place or defined names which auto-adjust (grow/shrink)...

Excel: Minimizing, crazy reason, cursor changes

crazy reason, cursor changes, excel97: Workaround first - pressing Alt and Tab will switch you between active applications, so this should stop the need for file exit. I cannot see a reason why Excel would not minimize, but it SOUNDS as though it might be running in a window rather than full...

Excel: Minimum and Average Functions, array formula, array formulas

array formula, array formulas, null values: Jason: Give the following array formulas a whirl and let me know if they work for you (they worked in my tests). NOTE - An array formula must be entered by pressing CTRL-SHIFT-ENTER instead of only ENTER. A correctly entered array formula will have...

Excel: Minor Changes to Code, target range, double application

target range, double application, row numbers: Hi again Jonathan, Here is a modification of my previous code that should work with your multiple named rows: Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long the last row containing data between CommSystem and ATS...

Excel: Misc. questions, cell a1, cell a2

cell a1, cell a2, excel application: Q1: If you have a cell that contains values that you want to split you can use the following functions in a formula: LEFT RIGHT LEN FIND If cell A1 contains 2006 sales To get out the 2006 portion you could use the following formula: =LEFT(A1,4)...

Excel: Missing Information, missing days, spreadsheet

missing days, spreadsheet, tabs: Hi Barbara, Does anyone else have access to your desktop? Also, it is possible you could have accidentally deleted data if you keep the document open during the day. I would suggest locking it in the future not only to avoid the possibility of others...

Excel: Missing text when importing to Word Perfect, word processor, microsoft word

word processor, microsoft word, spreadsheet: You would need to address this to a WordPerfect expert as WordPerfect is NOT Microsoft Word! You don t say how you are importing however, so a SIMPLE import that should work with any word processor would be to use copy in excel, then paste in the word processor...

Excel: Mix rows, sort command, excel sheet

sort command, excel sheet, aidan: I presume you mean that you want the data to end up on rows 1, 2 and 3 as specified - so row 3 moves to row 1, row 1 to row 2 and row 2 to row 3. It is not clear what the criteria for this would be, but the EASIEST way would be to use the sort command - IF...

Excel: Mode of a list of combinations, pivot table, occurances

pivot table, occurances, vba: Kim The simplest way would be to concatenate the cells like this in E1(assuming we start in A1) =A1&B1&C1&D1 which would give you 5784587 as text and copy down the column. Then sort that column and do a subtotal on the table. You could also do a pivot...

Excel: Moderate color, format cells, cheers

format cells, cheers: Hi Hosseein How are you? I am not sure what you mean by moderate the color. Is it to paint the background Select the cell Select Format -Cells Tab patterns Choose a color and pattern. I am sorry if it is not what you mean Cheers Adelaide...

Excel: Modifeid Date, excel spreadsheet, good answer

excel spreadsheet, good answer, leah: leah There probably is a way to do what you want, but unfortunately I don t know how. I suggest that ou reask the question of another allexperts person, and I m guessing that you can get a good answer. Sorry I couldn t be of more help. Richar...

Excel: Modified date, current date, excel 2000

current date, excel 2000, time stamp: Hi, Steve, There are ways you can quickly insert the current date and time into a cell, or know when the worksheet was last modified, but there is not a way to track the modification date and time for any one given cell. If you d like to write back with...

Excel: Modified date, time stamp, spreadsheet

time stamp, spreadsheet, e mail: Steve I ve done this with a macro that compares the cell to previous cell and inserts the date of any change. I ll be glad to send you the spreadsheet with the macro in it and you can adapt it to your situation. Richard Steve My e-mail is RIRSTRESS@aol.com,...

Excel: Modified time, douglas m smith, macro code

douglas m smith, macro code, brainbench: Yes, change the AddComment line in Stamper to something like: Range( A6 ).Value = Date + Time This adds the date and time values together and drops it in cell A6, in a true date/time format rather than text. Here s another option: ActiveCell.Offset(0,...

Excel: Modify VBA code, cell c3, cell c2

cell c3, cell c2, cell b2: The method of finding the last row is a very good one BUT because you are pasting one cell at a time, the bottom keeps moving! The following code should do it (without using copy and paste!) Dim X As Byte Dim Y As Byte Y = Workbooks( Masterlist.xls...

Excel: Modifying Macro, a160, c range

a160, c range, thanks in advance: This is my last answer - remember I do this on my own free time. Scrolling - take it out and see. Putting data in a cell, Range( A160 ) for example, via code does not mean that that cell needs to be on the screen. Range( A160 ) is Range( A160 ) regardless...

Excel: Mouseover - move?, mouseover event, image control

mouseover event, image control, excel sheet: You would need to put down a label and then an image control on top of the label. The label should be higher and wider than the image control so it sticks out on all sides. Make both visible, but make the backstyle property transparent. Remove the border...

Excel: Move Cursor where there are duplicates., base font, excel worksheet

base font, excel worksheet, xls: This will ensure the first duplicate cell is selected: Dim xlR As Excel.Range Dim xlS As Excel.Worksheet Dim i As Long Dim j As Long Dim str_cc As String Dim row_base As Long Dim col_base As Long Dim dup_count As Long Set xlS = ActiveWorkbook.ActiveSheet...

Excel: Move the cursor to active cell when detecting duplicates, base font, excel worksheet

base font, excel worksheet, xls: Hi Jawad, Hope you are doing well. Where you have dup_count = dup_count + 1, add the next line as xlS.Range(xlS.Cells(row_base + i - 1, col_base), xlS.Cells(row_base + i - 1, col_base)).Select This will select the last dup. Now that may not be...

Excel: Move to Other Sheet with VBA, control toolbox, exit design

control toolbox, exit design, sheet1: I m assuming you ve already got as far as opening the control toolbox and placing the button on your sheet. Now, with the control toolbox still open, double-click on the button; this will open the Visual Basic Editor. You should see code like this: Private...

Excel: Move Range Of Cells Based On Cell Criteria, novartis, tcs

novartis, tcs, begining: Sub MoveRangeOfCellsBasedOnCellCriteria() Move Nora , Novartis, Stevens, and TCS Dim myrange, cell As Range, s as String Set myrange = ActiveSheet.Range( O2 , Range( O65536 ).End(xlUp)) For Each cell In myrange s =...

Excel: Move select rows from Excel to Word, time ann, word document

time ann, word document, spreadsheet: Easiest way is to keep your macro as is, BUT before copying go through each row in the macro and get it to hide the row if the values don t exist - something like For Each cell In Range( A1:A40 ) If Len(cell.Offset(0, 1).Value) = 0 Then cell.EntireRow.Hidden...

Excel: Moving Arrays between ListBoxes, boundcolumn, listboxes

boundcolumn, listboxes, column index: Hello May, Right after your AddItem statement that populates column 1 you can transfer the values in the 2nd column (column index 1) from ListBox1 to ListBox2 using the Column property: ListBox2.Column(1, ListBox2.ListCount - 1) = ListBox1.Column(1,...

Excel: Moving Averages of series with gaps, day gap, x3 x4

day gap, x3 x4, professional programmers: See http://home.comcast.net/~sresnick2/AverageQuestion.xls for solution. I ve assumed that when there aren t 7 days in series (as in the beginning, and immediately after a reset ) the result should be the average of the 7 days up to that point...

Excel: Moving around in subroutines, subroutine initializes, frms

subroutine initializes, frms, inputbox: Does your code unload frmEnterReasonforRejection? The code can t continue until that form is hidden or unloaded. If frmEnterReasonforRejection has a frmDiscrepencies.show in it, then that can be problematic as well. If you can t straighten it out...

Excel: Moving the contents of a cell or a paragraph to another file(s)., microsoft language, microsoft world

microsoft language, microsoft world, dollar signs: Hi Ali, You can set up a formula in the My Documents file to link to the sentence cells. You will need to have both files open. In the My Documents file, type an = sign in the cell you want the sentences to start. (Don t hit Enter!) Now switch to...

Excel: Moving the contents of a cell or a paragraph to another file(s)., ali ali, microsoft language

ali ali, microsoft language, microsoft world: Ali You can copy by the following steps. First it usually helps to have the document you want to copy to already open. Go to the cell that you want to copy, Right clic, Select copy Go to the document that you want to copy to Go to the cell or...

Excel: Moving Data in Excel from one sheet to another, dropdown boxes, seperate sheet

dropdown boxes, seperate sheet, vlookup: Mike, I do not know of a way to efficiently do what you are attempting to do except to use VBA code (aka a macro). I ll assume you are comfortable with macros and how to manage them. The routine below will scroll through all the names listed on your ...

Excel: Moving Data Between Sheets, excel 98, leftmost column

excel 98, leftmost column, room1: Not sure what you mean by populate the top sheet with that information - if you mean add up the information in the same cell for each sheet (as in the sum of cell D3 for all the sheets), then you can use a 3D formula, but since the # of sheets vary and the...

Excel: Moving Data from vertical to horizontal format, mail name, blank row

mail name, blank row, horizontal format: If your data is truly consistently 3 rows of info then a blank row, then 3 rows of info & a blank, etc. then this will work. Follow the steps exactly (assuming everything is in column A): Enter this in cells B1:D2: B1: a1 C1: a2 D1: a3 B2: a5 C2: a6...

Excel: Moving Data, visual basic menu, select tools

visual basic menu, select tools, line 1: Hi Ivan, First I would want you to try this on a copy of your workbook and not the original just so you can test it first. If you select the first cell that contains the Apartment #1A and the run the macro from there I believe it will work for you. To...

Excel: Moving data by months, pivot table, week ends

pivot table, week ends, theses: Hi Keith, I would suggest using one sheet to collect all data (for a month at least, maybe even more). Add a column to your information that includes the date. When you have it all in one sheet, you can use a pivot table/pivot chart report to create your...

Excel: Moving data by a particular month, string s1, school attendance

string s1, school attendance, sheet tab: Keith, change If Month(DateValue(s1)) = Month(Date) Then to something like If Month(DateValue(s1)) = Month( _ Worksheets( Graph ).Range( H5 ).Value) Then where cell H5 on the sheet Graph contains a valid Excel date entry (not Dec or 12, but a valid...

Excel: Moving info between 2 different spread sheets, spread sheets, question thanks

spread sheets, question thanks, water bill: Lowell Go to sheet 3 and put the cursor in B12 and type in = then click on the P&L sheet (or sheet 1) and click on cell B4 then press enter, this will fill in the information for you. You can do this for any sheet to any sheet, even workbook to workbook...

Excel: Moving multiple excel sheet to single workbook, instance aa, excel sheet

instance aa, excel sheet, free time: Sorry, 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 write and...

Excel: Moving Row(s) Of Data, vlookup, column text

vlookup, column text, excel tutorials: Robert: If you could send me even a sample of your workbook, I can probably help you a great deal. Here s what you should do, though: Take my two Excel Tutorials: http://www.officearticles.com/tutorials/an_extreme_tutorial_in_microsoft_excel.htm http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm...

Excel: Moving records from one worksheet to another., vlookup, existing records

vlookup, existing records, question thanks: As I said, use a loop in your code and cycle thru each one. As you you stop on each new record s record number, look for that number in the other sheet. One was is to use a VLOOKUP or a MATCH function in the code. Do you know anything about writing...

Excel: Moving rgoup of cells based on a formula, false argument, price description

false argument, price description, excel 2002: Hello, Steve, It sounds like what you need is VLOOKUP. This function looks in the first column of some data for a value, and when it finds it, it returns a value from another column in the same place. Here s an example (the dots delimit the columns): ...

Excel: Moving rows in Excel, pair data, key column

pair data, key column, ogilvy: Hello Joe, I think a macro would be overkill on this. Try this: assume your data is in column A starting in row 1 in B1 put in the formula =if(Left(A1,2)= AK ,Countif($A$1:A1, AK* ),Countif($A$1:A1, AJ* )) then drag fill down column B. the select...

Excel: Moving between unlocked cells, douglas m smith, arrow keys

douglas m smith, arrow keys, brainbench: Hi John, That s the normal behavior when protection is turned on, with tabbing to the unlocked cells being the feature. Arrows keys continue to function as normal. The only way I can think of would be write a macro that reads the keystrokes and if arrow...

Excel: Moving between unlocked cells, arrow keys, selection property

arrow keys, selection property, control toolbox: John, If you also go to the worksheet properties and for the enable selection setting, set it to unlocked cells, then you will get the behavior you want. (make the control toolbox toolbar visible, then click on the right top button to show the properties...

Excel: MS EXCEL lookup to retrieve multiple records, excel lookup, common denominator

excel lookup, common denominator, vlookup: If I understand correctly, the first col of malldoors has mall codes, and you want to list the 5 entries in the 3rd col of malldoors where the mall code is a specific number, let s say 111111. So in cell E1 enter =VLOOKUP(111111,malldoors,3,0) which...

Excel: MS Excel, target value, control toolbox

target value, control toolbox, sheet tab: You didn t specify which version of Excel you re using, but this is what you can do in Excel 2003: Right-click any toolbarbutton, select Control Toolbox. Click the last tool ( More controls ), then select Calendar Control 11.0 (may be 10.0 in XP or 9.0...

Excel: MS Excel, left mouse button, transfer capability

left mouse button, transfer capability, excel workbook: Yahya, I m glad the file worked. It will be easier to explain concepts to people over this medium with the file transfer capability. Thanks for the feedback. Regarding how to put this in your workbook, do this. Open your file. Press ALT+F11 to start...

Excel: MS Excel 2000, ms excel 2000, memory memory

ms excel 2000, memory memory, enough memory: Hard disc space is NOT memory - memory is the amount of RAM installed, but basically the message is the answer to the question - there isn t enough memory on your machine to handle the job you are asking it to handle - I m not sure I d relish trying to manipulate...

Excel: MS Excel 2000, ms excel 2000, cell paint

ms excel 2000, cell paint, format cells: Elizabeth The best way to think about it is to expand the cell above the columns rather than decreasing the size of the columns. Here is how you do it. For example your adjustments columns headings might look like this Adjustments # Dr # ...

Excel: MS Excel, inverse proportion, spread sheet

inverse proportion, spread sheet, likelyhood: The probability of finding anything useful on your computer is in inverse proportion to the value of the data (a line I used to use in training courses) - the reality is that whilst you can search for temporary files altered yesterday, the likelyhood is that...

Excel: MS Excel, cell b1, database database

cell b1, database database, list of names: Assume Joe appears in A1 on both sheets. In B1 of sheet2 put in this formula =Vlookup(A1,Sheet1!$A$1:$B$8,2,False) or =Vlookup(A1,Sheet1!A:B,2,False) then select cell b1 and drag fill down the column as far as you have data. -- Regards,...

Excel: MS Excel Chart, ms excel chart, data labels

ms excel chart, data labels, good afternoon: I m not quite sure what you want to achieve - you can certainly have one legend showing both values, and you can have data labels on each Y axis - you could also use a text box to put extra information in. As far as I know, you cannot shade the area as you...

Excel: MS Excel : col val in one sheet to another sheet, move one, cols

move one, cols, ms excel: Hello Eswar I think i might be able to assist you but Im really not clear what you want from the way you question is phrased. I think you want to move one or more values from one sheet to another sheet. DO you also want to copy totals from one sheet...

Excel: MS Excel - Finding last data row, counta, blank fields

counta, blank fields, excel 97: I would create a named range as a dynamic range - this example uses a worksheet called incoming and has data in every row of A (important if the data could have blank fields) =Incoming!$A$1:OFFSET(Incoming!$Q$1,COUNTA(Incoming!$A:$A)-1,0) This range...

Excel: MS Excel font, conditional formatting, ms excel

conditional formatting, ms excel, phrases: Yes - Use Conditional Formatting and use your formula there. You will still need the formula in the cell to display one of the two phrases. You will need to modify the formula for conditional formatting to something like this (untested): =(NOW() G2)...

Excel: MS Excel formula, douglas m smith, excel formula

douglas m smith, excel formula, alphanumeric character: Hi Maurice, The formula would look something like this: =SUM(Sheet1!A1:A100) or if the sheet has a space, dash or other non-alphanumeric character in it: =SUM( Sheet 1 !A1:A100) You can also start typing the formula =SUM( and then point & click...

Excel: MS Excel formula, asap utilities, mouse pointer

asap utilities, mouse pointer, excel formula: Hi Richard, Actually it s not that simple. But you can download ASAP utility which will make it pretty simple. If you have not used it before go to this website to download it, it is free and you will probably find other useful tools on it if you have not...

Excel: MS Excel Macro, ms excel macro, explicit function

ms excel macro, explicit function, excel worksheet: Hi Amit, Hope you are doing well. This should help Option Explicit Function identify_similarities() Dim xls As Excel.Worksheet Dim in_row As Long Dim out_row As Long out_row = 1 in_row = 1 Set xls = Excel.ActiveWorkbook.ActiveSheet...

Excel: MS Excel Pivotchart, data labels, pt font

data labels, pt font, project impact: You can try something like this (I m not a pivotchart expert :-( Right-click the Chart Tab, select View Code, enter this: Dim NotNow As Boolean Private Sub Chart_Calculate() If NotNow Then Exit Sub NotNow = True ActiveChart.SeriesCollection(1).ApplyDataLabels...

Excel: MS Excel Scan, staff attendance, time sheet

staff attendance, time sheet, ms excel: Hello Syed I still dont know what you mean precisely. Do you mean time sheet records on paper which are then electronically scanned and imported to Excel. Or do you mean using a nahd scanning device to record staff attendance with direct data dunping...

Excel: MS Excel IF then statememtn, coloumn, temperature values

coloumn, temperature values, nominal value: Sorry, I don t understand the logic behind the numbers. Do you mean that if T1 and T2 are behind 26 and 33.5, it should count as 0.5? It looked so, but the samples confuses me. If so, you may use a formula like this: =0.5*AND(A2 =26,A2 =33.5)+0.5*AND(B2...

Excel: MS Excel Tab Key Jumping 10 cells?, scroll lock key, startup directory

scroll lock key, startup directory, tab button: Allan, Assuming it happens in all workbooks, then My guess is that the Scroll Lock key is turned on. Obviously, this is a guess since I can t examine the situation physically. To the best of my knowledge, there is no setting that controls this behavior....

Excel: MS Excel VBA Programming Emergency, excel vba programming, blank copy

excel vba programming, blank copy, phaseone: Jeanna, Its morning now so not sure if this is later than you needed but it seems that your CopyCallsheet function needs an input parameter that triggers the row in question. unfortunately, it seems that you are using several ranges that I am not sure...

Excel: MS Excel Visual basic/macros, macro security, correct syntax

macro security, correct syntax, collumn: Hi Henry How are you? Please see the following 1. Tools - Macro - security - security level - low Tools - Macro - security - trusted publishers- tick the two boxes at left-lower corner 2. Tools - macro - Visual Basic Editor 3. Insert module 4. Copy...

Excel: MS Excel, target value, value application

target value, value application, ms excel: I believe you want something more like this. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set rng = Range( A1:Z20 ) If Union(Target, rng).Areas.Count = 1 Then Target.Value = Target.Value + 1 ...

Excel: MS Excell find duplicate records, duplicate records, excel spread sheet

duplicate records, excel spread sheet, million records: Any formula that would be written would have be a fairly hairy formula and would have to written and then copied and pasted the million times. This will cause your PC to probably take hours and hours to return the results of esch formula and it will probably...

Excel: MS Office 2000 Premium, error message states, ms office 2000

error message states, ms office 2000, ms office 2000 premium: The program that is being shut down in the help program, which explains why the help menu is unavailable. I have searched the knowledge base for an answer to this and haven t found anything conclusive - there are references to viruses that COULD cause the...

Excel: MS Office 2002 - Excel Problems, ms office 2002, installing new software

ms office 2002, installing new software, frontpage program: Stephanie Sounds like somehow your properties of your excel icon are pointing to the frontpage program and not the excel program, or the .exe file in the excel program is really the frontpage exe file. I d suggest this Right clic on the excel icon and...

Excel: MS Office Pro 2000. I have..., ms office pro, text strings

ms office pro, text strings, cells: Tony Excel does have limits and you are correct about the 1000, there is no way that I know of getting around this. From Microsoft. 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. You might want to look at...

Excel: MS Query Through Excel 2000, microsoft query, parameter query

microsoft query, parameter query, ms query: Sounds like you want to do a parameter query. this article tells how to create a case insensitive parameter query. http://support.microsoft.com/default.aspx?scid=kb;en-us;211974 Once you set up the paramter (you will want to use less than or equal...

Excel: MS Query through Excel, ms query, microsoft query

ms query, microsoft query, access query: I have never actually made use of MS Query, BUT the criteria should (I believe) be entered more as an access query - eg DateField =today()-3. NOW()-3 would return the date and time, today would return the date only, but MS Query PROBABLY only accepts the...

Excel: MS Query --prompt for date range?, ms query, query data

ms query, query data, sql database: I haven t used .qry files, but if you want to use parameters in MS Query you can use the symbol ? in the place you want the parameter. For example SELECT foo FROM bar WHERE foo ? will have one parameter that you can manage. I don t know from the .qry...

Excel: MS Word 2003 SP3 & MS Outlook, temporary folder, ms outlook

temporary folder, ms outlook, sp3: If you double click a file, it has to be saved in a temporary location, which can probably be modified (I suspect via the registry). I would SUGGEST that the BEST method for such files is to use the SAVE option to save them to the location you want and THEN...

Excel: Ms Query for todays date, hh mm ss, dd hh

hh mm ss, dd hh, ms query: Robert, format(Date, yyyy-mm-dd hh-mm-ss ) & -00 from the immediate window in Excel: ? format(Date, yyyy-mm-dd hh-mm-ss ) & -00 2004-09-24 00-00-00-00 you would need to concatenate that into your sql query string. Possibly it would require...

Excel: Msg Box Options, message box

message box: Mark You can t change the text for a message box and I haven t tried changing any other options on one (but I am fairly certain you are stuck with what excel wants to give you). You could create a text box that activates when click on a specific cell but...

Excel: MsQuery, valid database, faqs index

valid database, faqs index, database source: Try selecting your data in Excel and naming it Database. Other sources of information: http://support.microsoft.com/search/default.aspx?qu=excel+mailmerge+ list many articles on mailmerge This looked interesting: http://support.microsoft.com/default.aspx?scid=kb;en-us;194747...

Excel: MTD Total, office salaries, seperate category

office salaries, seperate category, options tab: Hi Maryann How are you? I do not know if i have understood your problem. But if you mean something like B2=B2+a2 Which is a circular formula. B2 calls itself You can solve it by setting the number of iterations 1.tools -options tab calculations tick...

Excel: MTD Total, seperate category, learning curve

seperate category, learning curve, office microsoft: Maryann, Pivot Tables (PT) are the way to go. PTs are a built in feature of Excel and can summarize data exactly the way you are describing. The learning curve is a little steep, but once you know PTs, you will wonder what you ever did before. The best...

Excel: MULTI ATTRIBUTE UTILITY TABLES, utility tables, widows

utility tables, widows, microsoft windows: Robert I need a bit more information. Since what you describe might be done in a number of ways depending on what your data looks like, it would help if you can describe to me the data and how its gonna be set up in the table. I.E. columns and rows. ...

Excel: Mulit SUMIF, sumif function, workable solution

sumif function, workable solution, mulit: RICHARD What you need is a formula that is a SUMIF(AND formula. Unfortunately it can t be writen this way. What I usually do is break it down into two parts. The first part being one that evaluates the criteria and places a symbol in a cell if both...

Excel: Multi Column list, array formula, array formulas

array formula, array formulas, hidden columns: I think that my previous solution is still a good example, so I have uploaded it at: http://zapicm.googlepages.com/Validation_Indirect.xls In the Data sheet is your original data, and the list is the drop down with the headers, with the column changing every...

Excel: Multi Condition Sum Column, cell reference, food travel

cell reference, food travel, parenthesis: Only The subtotals will show if you click the column 2 at the top left of the sheet-- this will take the data down one level and minimize the details. Here is another way. First in a column adjacent to the date column put the formula =MONTH()...

Excel: Multi-dimensional cell values: using comments, column march, dimensional values

column march, dimensional values, external reviewer: Hi Dario, I m sorry about the delay. I ve been having trouble accessing the AllExperts site. I m not quite sure what you re setting up, but using cell comments is not the place to store data. Cell comments cannot be read by formulas and a macro would...

Excel: Multi Formula, numeric values, job

numeric values, job: No problem at all - for your situation, a nested if will do it =if(CellCheck=0, AWAY ,if(cellcheck 101, DRAW , HOME )) replace CellCheck with the cell being checked - note that I ve written this as you said, but a value of 101 will be HOME on this condition....

Excel: Multi SumIF, number 1s, array formula

number 1s, array formula, sumproduct: You re trying to add cells containing 1-Critical and that s not numeric & Excel can t add it. If you want to COUNT the # of cells containing that value where 3700 is in J, use SUMPRODUCT (leave out the quotes is the 3700 is numeric, not text): =SUMPRODUCT(--(...

Excel: Multi-sheet function in Excel 2002, column c, southeast division

column c, southeast division, contract price: Hi, Lori, All of your answers can be taken care of with a little-known wonder of a function called SUMPRODUCT. It is very powerful, and really deserves much more recognition! Let s say that your data goes from row 2 to row 100. Here is the formula that...

Excel: Multi-sheet function in Excel 2002, column c, southeast division

column c, southeast division, contract price: Lori: It seems to me that you do need to use the DCOUNT function for tasks one and two, especially since you have more than one criteria. An array formula will work but they are difficult to understand. You need to setup a criteria range and it can...

Excel: Multi Workbook, sales invoices, passowrd

sales invoices, passowrd, xls file: Sorry, I do not write macros for people on this site who need one written and who ask me to do so especially since I answer questons here on my own free time. If I did then that is all I would be doing. I am sure you can understand. Simple macros I will...

Excel: MultiColumn List box, multicolumn listbox, code structure

multicolumn listbox, code structure, excel 2002: Chuck, I m not near a computer with Excel, so I ll give you a code structure - you ll have to test it and amend as necessary. ***UNTESTED Sub FillRange() For Each cell in Range( YourNamedRange ) ListBox1.AddItem cell Next cell End Su...

Excel: Multifunctional Command Button, command button, excel programming

command button, excel programming, command buttons: Hello Madhu. You should be able to do what you want. I would see it as something like this: Private Sub CommandButton1_click() Dim s as String s = me.CommandButton1.Caption if lcase(s) = show inputs then Range( A1:A5,A20:A25,A33 ).EntireRow.Hidden...

Excel: Multifunctional Command Button, command button, excel programming

command button, excel programming, command buttons: Write an IF statement on the button s click event that checks to see what its caption is. If the caption is one thing, then do whatever needs to be done for that, change the caption and exit the sub within that IF. Do the same thing for the other caption...

Excel: Multilpe lookup, hi bob, cat mouse

hi bob, cat mouse, vba: You need a user-defined function (VBA) to do this. First, name the table (1-dog-2-...-7-snake) something, like tbl . Press Alt/F11, use Insert/Module, paste this in: Function cvt(rg As String) As String Dim ans As String all = Split(rg, , )...

Excel: Multipage User Forms, public variables, initialize event

public variables, initialize event, userforms: ... so the user would click on a button NEXT to get the next userform... You can do this in one of many ways. IF you want to use just one form as you imply based on your subject then create as many pages on the multipage control as you need. When...

Excel: Multiple Comparisons in Excel like Vlookup, a3 b3, jack painter

a3 b3, jack painter, david farmer: Assuming the data from Name:Place is columns A:C, and assuming the data with the Income goes to row 500, then in sheet1 cell D3 (near the New York , you need to ctrl/shift/enter this formula (NOT simply enter): =INDEX(Sheet2!D:D,MATCH(A3&B3&C3,Sheet2!A1:A500&Sheet2!B1:B500&Sheet2!C1:C500,0))...

Excel: Multiple Criteria, current sheet, tab sheet

current sheet, tab sheet, cell c: 1st question, can it be easier? Yes: put the 5892 in a cell, say C1, then change your formula to this (it s longer, but you don t have to edit it each time): =SUMIF(INDIRECT( [ACH-5876 - 5900.xls] &C1& !$A$10:$A$5000 ),C$2,INDIRECT( [ACH-5876 - 5900.xls]...

Excel: Multiple currency conversion, currency conversion, currency converter

currency conversion, currency converter, decimal points: Hi Scott I hope you are well. Is Adelaide a nice place to live in? The easiest way to do this is the following 1. Assuming your $USD amounts are in A1:A440 2. Type in B1 the exchange rate, e.g., 1.2 3. Type in C1 =A1*$B$1 4. Copy C1 to C2:C440 ...

Excel: Multiple currency conversion, douglas m smith, currency conversion

douglas m smith, currency conversion, cell b2: G day Scott, Your question brings back memories from my days in Australia. I lived in Perth when Australia converted from pounds to dollars, 41 years ago yesterday. I ll assume your list of numbers start in cell A2. Select a cell away from your list...

Excel: Multiple Data Lines in Pivot Chart, chart wizard, pivot table

chart wizard, pivot table, pivot tables: Its not clear what you mean by two sets of data in the data field, but yes, that is almost certainly the problem - for this type of chart, build a pivot table first using the wizard and THEN use the chart wizard to turn the resultant table into the chart you...

Excel: From Multiple Excel files to one database, pivottable, cells

pivottable, cells: Automatically? No. You would either need a macro to copy/paste the info into a master file or do it manually. If the data is of the nature that it could be summarized/combined with data/consolidate or via a pivottable, that may be the way to go, but you d...

Excel: Multiple Excel Sheets, data searches, model manufacturer

data searches, model manufacturer, mechanical device: You want 700 copies of the worksheet? You want 700 worksheets as opposed to 700 workbooks? Or do you want 700 copies of the range pasted down the rows in the same worksheet (so that if your form is 50 rows you ll have 35000 rows?) In any case you ll probably...

Excel: Multiple Excel If Statements, g54, f54

g54, f54, hth: Put the 54 combinations in a list, like in F1:G54 where F1 contains the combinations (in the above example it would have 1111) and in G1 it would have the result 68.71. Now you can use a formula like: =VLOOKUP(1*(a1&a2&a3&a4),F1:G54,2,FALSE) Since the &...

Excel: Multiple IF Functions, necessary values, basic principle

necessary values, basic principle, vlookup: Actually, AllExperts imposes a 3 day deadline on answers, so you HAVE to have it before Christmas!!! The basic principle of your first part is easy - it just uses the AND statement =if(and(A1= Box ,B1= Collect ,C1= Next Day ),.75,WhateverHappensIfFalse)...

Excel: Multiple Goal Seek, vba procedures, row index

vba procedures, row index, row selection: This appears to be vb, not vba, and I really know little of vb. However, your line xlSheet.Range(xlSheet.Cells(num_row, 9)).GoalSeek Goal:=0.495, ChangingCell:=xlSheet.Range(xlSheet.Cells(num_row, 7)) is not varying anything, since num_row doesn t change...

Excel: Multiple Graphs, data validation, type of graph

data validation, type of graph, graphs: As I said previously, you can select each UID in the drop-down menu in turn, copy out the resulting graph & data somewhere (a different location on the sheet, a different sheet in the same workbook, or to a different workbook), then select the next UID, etc....

Excel: Multiple Hyperlinks in Excel, space character, charecter

space character, charecter, url1: If you tried the space character separator, which I figured wold not work anyway, then without a macro, if even that could be written, I doubt it can be done. And, you never did say WHY you have more than one hyperlink in one cell. It seems you will have...

Excel: Multiple Hyperlinks in Excel, insert hyperlink, hotmail

insert hyperlink, hotmail, google: Hi, Bertram, A hyperlink is associated with a *cell*, not with the *words* in a cell. Since you assign a hyperlink to the cell, you can only assign one. I suggest you create three small cells right near each other, and that way you can click on whichever...

Excel: Multiple pivot tables created via Macro, run time error, pivot tables

run time error, pivot tables, pivot table: Sorry, I meant Pivot Table 2. I d suggest you name the Pivot Table as it is created and then use that name in your code. If EXCEL treats new Pivot Tables as it does all other objects it creates, it will keep incrementing the number until you either close...

Excel: Multiple If Statements, h37, d9

h37, d9, d8: You can nest up to seven IF statements, in this case it will be enough: =IF(AND(ISBLANK(D5),ISBLANK(D6)), ,IF(AND(ISBLANK(D8),ISBLANK(D9)), ,IF(D5= yes ,IF(D8= yes ,A37*G37,A37*I37),IF(D8= yes ,A37*H37,A37*J37)))) The first thing we check is if both pairs...

Excel: Multiple sheets if, then, countif, c500

countif, c500, a500: You can use SUMPRODUCT instead of COUNTIF, so you don t need to sort the data. Something like: =SUMPRODUCT((C1:C500= May )*(B1:B500= NY )*(A1:A500= Prod1 )) The idea is to enter each condition that you want to check inside the formula, and it will return...

Excel: Multiple If statements within a range, cell reference, aidan

cell reference, aidan, brackets: I didn t mention VBA - you can always use any nested brackets provided you keep the number down, BUT the formula I gave you would do the job on the information you gave me - simply replace VAlueBeingChecked with the cell reference. It may also be that a lookup...

Excel: Multiple IF statements, douglas m smith, data validation

douglas m smith, data validation, formula index: Hi Jahan, Excel chokes when you try to nest more than seven IF statements. Fortunately, there s other ways to do what you want to do. I m assuming you are using a Data Validation in-cell dropdown box to select the Cap item. If you used put the Cap...

Excel: Multiple Table Refresh, excel pivot tables, plasma screens

excel pivot tables, plasma screens, olap server: Anil, There are at least three solutions that come to mind. None of them are optimal in my opinion, so I will let you decide which one fits your situation best. Method 1: Put the following line of code in the Activate event of the worksheet where the...

Excel: Multiple tabs - formulas not updating, parent child relationship, addition and subtraction

parent child relationship, addition and subtraction, circular reference: Ideas…in no particular order: 1) You have a circular reference somewhere. Excel will tell you if you have a circular reference on the message bar at the bottom of Excel. 2) Random attempt at correcting formula recognition problems – Try searching and...

Excel: Multiple Users, multiple users, poeple

multiple users, poeple, ogilvy: Lee, That is what databases are for. You should look at using something like SQL server, mySQL or something like that that is designed for multiple users. Suggesting a workaround that involves excel would require indepth information on your work requirement...

Excel: Multiple Workbooks, sum formula, sumif

sum formula, sumif, ogilvy: A function like Sum should work with a closed workbook =Sum(C:Myfolder[Mybook1.xls]Sheet1!$A$1:$A$10) Some functions don t such as Sumif or countif. YOu don t say enough to know what you are doing, so hopefully this has given you enough information...

Excel: Multiple Worksheet Referencing, cell c3, pivot tables

cell c3, pivot tables, name of countries: Ian You may be able to do this using Pivot Tables, I haven t tried it but it should work if all your sheets are the same. Have a look at this link http://support.microsoft.com/kb/213947/en-us If this doesn t suit get back to me. Richar...

Excel: Multiplication formula, cell b2, dollar signs

cell b2, dollar signs, aparna: Hi Aparna, We can definitely solve this problem. We will use three very useful functions: - Offset() which gets the value from a cell a certain number of rows and columns away from your reference cell - Rows() which counts the number of rows from...

Excel: Multiplication / sum on parts of a number, relevant cells, cell b1

relevant cells, cell b1, unfortuantely: Ulf Unfortuantely since all of your conditions need to be tested before the calculation can be done, I know of no other way to do it other than with an if formula. I would simplify the formula a bit by testing the conditions in other cells and then using...

Excel: Multiplication / sum on parts of a number, question column, vlookup

question column, vlookup, bank balance: Hi Ulf, How are you? Please do the following: 1: Type tthe balance amounts in a1:A10 2: Type 0 in D3, 1000 in D4, 2000 in D5, 3000 in D6 0% in E3, 1% in E4, 2% in E5, 3% in E6 3: Type in B1 =VLOOKUP(A1,$D$3:$E$6,2)*A1 4: Copy B1 to B2:B10 Hope...

Excel: Multiply selected cells by a constant, keyboard shortcut, vbyesno

keyboard shortcut, vbyesno, code option: Use this code Option Explicit Sub TextToNumbers() On Error Resume Next Dim c As Range Dim res As String res = MsgBox( Do you want to change all text numbers to real numbers? , vbQuestion + vbYesNo) If Not res = vbYes Then...

Excel: Multivariable formulas, dropdown boxes, box icon

dropdown boxes, box icon, foot stone: Good evening! It is time for you to become familiar with the Forms toolbar. Right-click on any toolbar and make sure there is a check-box by “Forms”. This will open a new toolbar that allows you to create drop-down lists and other various form items. ...

Excel: Mutiply results froma table

In cases like this I normally use the autofilter (Menu Data- Filter- Autofilter). Active it on your range, and on the dropdown that should appear on the results header, select (Custom...) . Here you can enter a condition for the result being less than 85,...

Excel: MyRange.Address, dim statement, limmited

dim statement, limmited, thanks for your patience: Bill, many thanks for your patience with me, but I can see that I have asked one question too many. NO, NOT REALLY. IT WAS JUST THAT I HAD TO TYPE SO MUCH OR THE ANSWERS WOULD NOT HAVE BEEN AS USEFUL BECAUSE OF YOUR LEVEL OF KNOWLEDGE AT THIS POINT. I...

Excel: I'm stumped !!, cell d1, multiplication factor

cell d1, multiplication factor, mark 6: You can use goal seek for this. For example, if your 5 numbers are in range A1:A5, enter the following formula in B1: =IF(A1 5000,MAX(A1*$D$1,5000),A1) And copy the formula all the way down to B5. Now, enter the number 1 on cell D1, and on cell B6 enter...

Excel: I'm using Excel 2003I'm..., assignment sheets, assignment sheet

assignment sheets, assignment sheet, cell values: Hi Laura, Yes I would like to see an example of what you are trying to do. You can send it to Cloverken@yahoo.com What I need to know is what is it that specifies the day assignment sheet to someone in particular. Or do you simply select a new assignment...

Excel: I'm working with Microsoft..., paint shop pro, graphic space

paint shop pro, graphic space, transparent colour: Not sure that this is an Excel question as such (indeed, not certain that doing a newsletter in Excel is the best program but that is by the by!) - I THINK that the problem is with the picture, which has (I m guessing) a white background - if so, using a graphic...

Excel: if-then macro, format button, grey color

format button, grey color: In addition to my previous solution, you can select the column containing CLOUD/READABLE , and use Format/Conditional Formatting. Change the word Between to equal to , then enter CLOUD in the next edit box. Click the Format button, click the Patterns...

Excel: macro, spreadsheet data, art layout

spreadsheet data, art layout, margo: Margo It is possible to do just about anything with a macro--so I m guessing that you can do what you want to do. I am not a macro expert, so I won t be able to give you any real advice on how to do it, but I suggest you reask the question of another allexperts...

Excel: macro, strikethrough, strick

strikethrough, strick, mistake: Pat, Try this. I just tweaked your code Private Sub CheckBox1_Click() Range( B4:CM4 ).Select If CheckBox1.Value = True Then With Selection.Font .Name = Arial .Size = 12 .Strikethrough = True .Superscript...

Excel: if-then macro, a103

a103: Hi KB, This Macro should work for you. Just adjust the ranges to fit your ranges. Sub CheckValues() Dim n As Variant Application.ScreenUpdating = False For Each n In Range( A2:A103 ) If n.Value = 0 Then n.Offset(0, 1).Value = CLOUD...

Excel: if-then macro, light grey background, a100

light grey background, a100, formatting: Firstly, you don t have to do this with a macro - an IF statement plus conditonal formatting would do it for you - but as you specifically asked for a macro.... For Each cell In Range( A1:A100 ) If cell.Value = 0 Then cell.Offset(0, 1).Value = CLOUD...

Excel: if-then macro, light grey background, elseif

light grey background, elseif, ogilvy: Select the cells you want processed and run this macro. Sub ProcessSelection() Dim cell as Range for each cell in Selection if cell.Value then if isnumeric(cell.Value) then if cell.Value = 0 then cell.offset(0,1).Value =...

Excel: if-then macro, light grey background, colum

light grey background, colum: Do you mean on a cell be cell basis down the entire colum or if just ANY cell in the column has a zero? And then on what cell in the second column should these words appear? You don t make things clear enough for me to know. Rememebr I can not see what...

Excel: macro, word application, macro programming

word application, macro programming, excel sheet: Dim appwd As Object On Error GoTo notloaded Set appwd = GetObject(, Word.Application ) notloaded: If Err.Number = 429 Then Set appwd = CreateObject( Word.Application ) End If appwd.Visible = True...

Excel: macro?, macro security, command button

macro security, command button, security level: Hi Pete How are you? Where do you import the data from?You may import data and format - it depends on the source But you can always write a macro to shade some rows. For example, if you want to shade the rows 1,6, 11, etc. 1. Tools - Macro - security...

Excel: macro?, shading, macros

shading, macros, spreadsheet: CAN be done with macros, but this is easier: Select A1. Define a name (MUST be done this way), say patterned , which has this as its Refers to: =GET.Cell(13,$A1) 0 then, in column Z (or wherever), enter =IF(patterned,1,0) and fill down. Be sure to recalc...

Excel: macro?, douglas m smith, colorindex

douglas m smith, colorindex, brainbench: Hi Pete, Yes, it s going to take a macro to tag which rows are shaded. This one loops through the cells in column A and puts 1 s in column Z if shading is found: Sub MarkFormats() For Each x In Range( A1:A999 ) If x.Interior.ColorIndex xlNone Then...

Excel: macro assistance, particle equipment, cathodic protection

particle equipment, cathodic protection, thickness gauge: Example: In cell A1, there s the header QTY in bold font. In cell B1, there s the header EQUIPMENT in bold font. Starting in row 2, col A has either a number or a FALSE. Col B has a name or a FALSE. You want to hide all rows with FALSE in Col A. Solution:...

Excel: macro button and control alignment, control alignment, macro buttons

control alignment, macro buttons, macro button: If you go into View= Toolbars and click on the drawing toolbar to make it visible, you can select the first menu item in the drawing toolbar and it will have a snap item which allows you to select Snap to grid. if you right click on the button and select...

Excel: macro + button, macro button, option button

macro button, option button, click option: Hey Rodney, I think you maybe created your button using the Control toolbar? Try using the Forms toolbar to create your button instead: To create an option button and to assign a macro to it, follow these steps: 1. On the Forms toolbar, click Option...

Excel: macro to calculate Decimal Deg, decimal deg, cell c1

decimal deg, cell c1, expert description: It sounds like you are describing a Visual Basic Program/Macro, and as per my Expert description, this is outside my area of expertise. I recommend finding an expert who lists Visual Basic and Excel as thier areas of expertise. However, I will take a wild...

Excel: macro to calculate Decimal Deg, decimal deg, query in access

decimal deg, query in access, math functions: Shah I m not a real MACRO expert, but here is a simple way to write the macro. Go to toolbar/tools/macro/record new macro Then make all of the keystrokes necessary to do the parse of your data. Then go back to toolbar/tools/macro/stop recording ...

Excel: macro code, macro code, control toolbox

macro code, control toolbox, sheet tab: Private Sub Combobox1_Click() me.Range( A1 ).Value = _ me.Combobox1.Value End Sub or Private Sub CommandButton1_Click() me.Range( A1 ).Value = _ me.Combobox1.Value End Sub Code assumes controls from the control toolbox toolbar and should...

Excel: macro to convert excel columns into rows, interval value, uniform layout

interval value, uniform layout, minute interval: Most macros are so task specific that none rarely exists for the task you need it to do. So, there is probably not one for your needs and I don t have one either. I don t write macros for people on here who ask for them, especially since I do this on my...

Excel: macro which copy from 1 sheet to other based on condition, code target, target value

code target, target value, material inventory: 1. If you don t want to copy the entire row, then instead of Target.EntireRow.Copy use Target.offset(0,-7).range( a1:d1 ).copy In this code, target is the cell in column I where YES has been entered. .offset(0,-7) means 7 columns to the left...

Excel: macro to copy from one excel file, and paste to other excel file?, xls file, 25m

xls file, 25m, free time: Sorry, I can t figure out what you are asking. Are you asking about how to write a macro to do your copying and pasting or just how to copy and paste? Your abbreviated way of describing what you need is not informative enough. From your first post, it...

Excel: macro to copy sheet with multiple repetitions, rng, ogilvy

rng, ogilvy, repetitions: Sub AddSheets() Dim rng As Range Dim cell As Range with Worksheets( nameoflist ) Set rng = .Range( A1 ,.Cells(rows.count,1).End(xlup)) End With For Each cell In rng worksheets( Results ).copy after:=worksheets( _ worksheets.count) ActiveSheet.Name...

Excel: macro for copying and pasting in Excel, macros, copy and paste

macros, copy and paste, interval: Rick I am definately not a macro expert. I have written a macro that will insert a row whenever there is a change in what is in the column, but that doesn t sound like what you need. To get a quicker (and probably better) answer I suggest you reask the...

Excel: macro for copying, school districts in ohio, copy and paste

school districts in ohio, copy and paste, free time: Sorry, I don t just write macros for people on this site who need one, want one, and/or seem to think I am expected to do so. 99% of all macros are more involved than what you think and rarely am I provided with enough specific and complete details. This...

Excel: macro for data transfer, nextpos, documents and settings

nextpos, documents and settings, string source: Here is some modified code that does what you want. Email me at gygulyas - at - yahoo - dot - ca if you need help with it. Gyula Sub RunIt() Call ImportTextFile( C:Documents and Settingspathtofile.csv , , ) End Sub Public Sub ImportTextFile(FName...

Excel: macro for data transfer, max rows

max rows: Joseph I suggest you transfer the data manually to the excel file, as it sounds as though you only need to do it once, and writing the macro to transfer will be more difficult than just going ahead and manually making the transfer. Depending on what your...

Excel: macro deleting first row when it isn't supposed to, autofilter, question thanks

autofilter, question thanks, rng: Try this Dim rng As Range With Range( A:A ) .AutoFilter .AutoFilter Field:=1, Criteria1:= =XMJ???????????U??????????????? , _ Operator:=xlAnd End With With ActiveSheet.AutoFilter.Range ...

Excel: macro for deleting worksheets, record mode, digital printing

record mode, digital printing, spreadsheet: IF I understand what you need then all you need to do is just copy that one sheet to its own separate workbook(file). The easiest way is to RIGHT click on the tab of that worksheet and select Move or Copy. Check Copy and select (new book) from the To Book:...

Excel: macro&developer, controls group, mrexcel

controls group, mrexcel, google: You get the developer controls by Office button/Popular tab, 3rd checkbox. Now, on the Developer tab, the Controls group, the Insert control has both forms and activeX controls. How to use them is WAYYY too long an explanation here -- but you can Google how...

Excel: macro error, rob bovey, code execution

rob bovey, code execution, strange errors: I have never encountered it, but when you start getting strange errors like this and NOTHING HAS CHANGED (as you implicate), it might be time to use Rob Bovey s free code cleaner utility. You can download it at http://www.appspro.com go to the free...

Excel: macro excel

HI there Please can you clarify your question I m uncertain what you mean by Move from left to right . Try explaining what the purpose of your spreadshete is and what you are trying to achieve relative to the quaetion you have asked. Regards Limbic...

Excel: about macro excel, shortcut key, excel spreadsheet

shortcut key, excel spreadsheet, single step: Normally, you TEST macros by pressing the F8 key and single step through the macro from within the VBE. But you RUN the macro after testing is complete, from Excel, by using Tools/Macro/Macros and selecting your macro to run, then click the Run button. Certainly,...

Excel: macro for extracting data from web into excel, excel sheet, stock prices

excel sheet, stock prices, macros: ...i need to write a macro which has the vb code to extract the data from web to the excel sheet. ... 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 do so unless...

Excel: macro with find copy and paste functions...!!!!, spreadsheet example, paste functions

spreadsheet example, paste functions, column name: Well to guide all depends on how much you know about writing macros. If you know nothing, or very little, then to guide you would first require me to teach you about macros and then to guide you. To teach you via this web site would require a LOT of typing...

Excel: macro to find/replace and rename, paste functions, 12 steps

paste functions, 12 steps, copy and paste: So, what s your question? Assuming you are asking me how to write this code, then the code can not be recorded completely as you have discovered. Secondly, writing the code for you or telling you how will be too time consuming for me because it will invlove...

Excel: macro for formatting, empty column, hidden information

empty column, hidden information, strikethrough: Is there anyway to send you a copy of the information I am running the macro on? Yes, you can email it to me to answer your question specifically but I do not accept emails from people on this site. Have you looked in that column to see if you can see...

Excel: macro or formula, visual basic for applications, excel macros

visual basic for applications, excel macros, relative reference: From the main menu, do the following: Tools, Macro, Record New Macro, and give it the Macro name TimeStamp , and click OK. On the popup that appears, click the Relative Reference button. In your activecell, type =now() Then, when the date...

Excel: macro help (expiration dates), excel functions, first contract

excel functions, first contract, iitbombay: Hi Melissa, To answer in short...YES. This is very much possible with a simple MACRO. The macro can be triggered by (1)clicking a button (2) automatically while opening the file. You are right, we can use now() or today() to identify the qualifyig rows and...

Excel: macro help (expiration dates), excel functions, first contract

excel functions, first contract, expiration dates: Well, to answer your question, yes, this is possible. And, yes you would use the Now function to have the macro determine which proposal has expired and then move to the Expired sheets. Assuming you really meant to ask is How can I do this , you could...

Excel: macro help, sheet cells, blank cells

sheet cells, blank cells, input sheet: Hello Josh, I don t really see where you would get a prompt, but I have added code that wiii suppress prompts from excel - if you have code that puts up a prompt asking that question, you have not shown such code. To not process a blank cell (cell...

Excel: macro icon to menu bar, macro name, bar tab

macro name, bar tab, mouse right: Hi, Ivan Sorry for delay in responding i have been busy with a change in my job. Coming to our problem just click mouse right point there you can find options of macros name and select the one to which you one to assign the smiley. Hope this will help...

Excel: macro and import files, array array, import files

array array, import files, exact number: That s up to you, but if they are empty there would be no reason to delete. You implied that you only wanted 9 columns. Obviously if you don t want to do that (restrict it to 9 columns), don t include that line of code. -- Regards, Tom Ogilvy ...

Excel: macro loop replace cell, cell b11, macro button

cell b11, macro button, load factors: if the macro is being run from Automated and the S5 cell is on the sheet with the button, then you can qualify it with the reference ME Workbooks.Open Filename:= K:LPP Consensus TeamsHuntC027930.xls Sheets( Load Factors ).Select Sheets( load...

Excel: to macro or not to macro, forcast report, phrasing

forcast report, phrasing, ins: You can t enter data into a cell that contains a formula without writing over it. Probably the simplest way would be to have an extra column and use an IF statement to check if the cell s value is over 0, if not use the value entered into the extra column...

Excel: macro needs to repeat, array array, range f1

array array, range f1, keyboard shortcut: You may use a for loop to cycle on the cells, repeating the same process for each file. Something like: Sub rtpe() Dim strPath$, strDir$, strFile$ Dim dteDateTime As Date, dteLatestDateTime As Date strPath = Range( FilePath ) For i = 12 to 16 ...

Excel: macro to open file and run macro, spath, seperate files

spath, seperate files, sname: Hello Jason, Let s assume all the workbooks are in the same folder and only those workbooks are there (C:Myfolder) Sub OpenandProcess() Dim sPath as String, sName as String Dim bk as Workbook sPath = C:Myfolder sname = dir(sPath & *.xls ) do...

Excel: macro for Pie chart (data source change), jul aug sep, jun jul aug

jul aug sep, jun jul aug, pie chart: HI The following routine handles the new sheet problem for you Sub NEWSHEETMACRO() Sheets( Sheet1 ).Select Sheets( Sheet1 ).Copy After:=Sheets(3) Sheets( Sheet1 (2) ).Select Sheets( Sheet1 (2) ).Name = MY NEW SHEET ...

Excel: macro to print 2 copies, menu view, double click

menu view, double click, visual basic editor: From main menu, View, Toolbars, Control Toolbox. Use the Control Toolbox to create the button. Double-click on button to get to Visual Basic Editor. Create code something like this: Private Sub CommandButton1_Click() Sheets( Sheet1 ).PrintOut Copies:=2...

Excel: macro to process same calculations on different sheets, copy columns, false selection

copy columns, false selection, autofit: Hello Yu, If there are only worksheets in the workbook: Dim ws1 As Worksheet if worksheets.count 4 then exit sub for i = 4 to worksheets.count Worksheets(i).Select Set ws1 = Worksheets(i) Range( C2 ).Value = ActiveSheet.Name Range( C2 ).Select...

Excel: macro recorder, keystroke macro, faq type

keystroke macro, faq type, expert archives: I don t know about searching all experts. I have never used it. start recording go to tools= customize on the first tab, select Stop Recording close the dialog not click on the stop recording button You can delete what was recorded. Now...

Excel: macro to retrieve set of data from excel sheet, copy worksheets, dt2

copy worksheets, dt2, private x: This is one way of doing it, copying the entire row if the conditions are met: Private i, j, l As Integer Sub m1() Dim Input_Date, dt2 As Variant Dim dt, dt1 As Date dt = Date dt1 = dt - 7 j = 1 Set Worksheet1 = ActiveWorkbook.ActiveSheet l = Worksheet1.UsedRange.Rows.Count...

Excel: macro to send a report via email, vba code, function shellexecute

vba code, function shellexecute, william rose: Your report will have to be just be the body of the message as text with no EXCEL formatting. Assume that you have data in A2, B2, and C2. This is a bot more involvd than what you might think For example, the email message (your report) would read:...

Excel: macro set with expired date?, question thanks, current date

question thanks, current date, thanks in advance: You can put a condition at the beginning of every macro, to check the current date. Something like: Sub TimedMacro() If Date CDate( 1/1/2008 ) Then Exit Sub Your macro here End Sub If the current date on the computer is greater than the...

Excel: macro to sort values., sort values, sort method

sort values, sort method, effective solution: This macro will do the trick, except the part of activating the sheet before the sorting. The sort method that I have used, that I think is the simpler and more effective solution, works only on the active sheet. The user won t have the opportunity to see...

Excel: macro for sorting a range of cells in column B for identical range in column A, b47, a47

b47, a47, b23: 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 do so unless they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros are...

Excel: macro unhide and hide a sheet, target value, number 0

target value, number 0, msgbox: You did send the entire code but because you did not use or start a new paragraph in your original post the I overlooked its first line. You reference Target.Address in your code for I7 then later on you reference I6. The target never changes while the...

Excel: macro and VB, design button, design mode

design button, design mode, hasan: Depends on the type of button. If from the forms toolbar: Right-click the button and choose Assign Macro If from the control toolbox: - make sure the control toolbox toolbar is visible - double click the button - if nothing happens, click the design...

Excel: macro VBA question, copy worksheets, sheet names

copy worksheets, sheet names, column number: Hi again Yu, I assumed you want to copy just values, not formulas. In addition, I assumed that when you referred to sheet one, sheet 5, etc., you are referring to the sheets tab order, not the sheet names. So this macro will copy to the first tab column...

Excel: macro in vb for excel, format cells, macro in excel

format cells, macro in excel, true application: You need to use the On Error statement. Either use On Error Resume Next IF THE ERROR is OK to happen, which in your case it seems to be this way. Or, use On Error Goto statment to have the code go to a place that will exit cleanly. Here is your code...

Excel: macro / vba, developing a questionnaire, inset

developing a questionnaire, inset, cheers: Hi Mal How are you? You can inset the buttons directly on the worksheet. Please try the following: 1) Tools - macro - Viosula basic Editor 2) Insert - Module 3) Copy Sub clearing() Dim A As Range Set A = Range( A1:A10 ) A.ClearContents End Sub...

Excel: macro vertical to horizontal, sheet application, transposing

sheet application, transposing, good afternoon: It s not clear how you DO want to paste. It s the Transpose:=True which is causing the paste from horizontal to vertical, but given where you re pasting (B4, then D4), I m assuming that s what you want, so what s the question here? I don t understand I...

Excel: Why does my macro work?, cell b3, ubound

cell b3, ubound, s line: ? Why does my macro work? My macro - I was the one who posted it. g although I am not the author of the original engine portion. I expanded the code to handle something other than integers k to n. The application.Transpose is to convert it to...

Excel: macro not working, target value, cell interior

target value, cell interior, explorer bar: The macro is fine, BUT you probably didn t get instructions on where to put it - in the VBA editor, choose the worksheet you want it to be on from the explorer bar on the left. THEN paste the macro - it needs to be run on the change event of a worksheet....

Excel: macro that goes from worksheet to worksheet, left hand corner, sheet names

left hand corner, sheet names, macro commands: Sounds like you need a way to navigate to each of the sheets in your workbook easily and quickly. YOu don t need a macro to do this though a macro could be written to accomplish this. However, I do not write macros for people on this site when requested...

Excel: if-then macro

1 - you don t nee a macro: THis filled-down formula will do: =IF(A1=0, CLOUD , READABLE ) 2 - if you want a macro anyway, you can use this (assumes the range of #s is in A1:A100): Sub CloudReadable() For each x in range( A1:A100 ) if x=0 then...

Excel: macros, macro commands, lindner

macro commands, lindner, rr: Ron You can add one of the macro commands to the other and make one macro out of the two. You ll have to pick the order that you want the commands to occur in, but otherwise there should be no problem. Easiest way to do this would be to copy the commands...

Excel: macros, relative cell reference, relative reference

relative cell reference, relative reference, cell references: You need to change the numbers 229.5, 171#, 6#, 6#. The numbers, respectively, are Left, Top, Width, and Height. To make this a true relative reference (I assume you want the rectangle to be placed where the cursor is when you run the macro - you...

Excel: macros, relative cell reference, generic code

relative cell reference, generic code, error reference: Hi Chris, (1) The correct sysntax is - myDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 100, 100, 200, 50).TextFrame.Characters.Text = Test Box The working example is- ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 13.5, 9, 10,...

Excel: macros, personal workbook, menu tools

personal workbook, menu tools, vbe: From main menu, Tools, Macro, Record New Macro. In the Record Macro dialog box, under Store macro in , choose Personal Macro Workbook. OK. Record your macro. It will be available in all workbooks. This Personal Macro Workbook is a hidden workbook that...

Excel: macros, sheet names, horey

sheet names, horey, box pop: Tom, To clean up all of the junk that gets left behind after creating a macro do the following: - Press Alt + F11 to open the Visual Basic Explorer - In the left pane is the Project Explorer. There should be a project listed as: VBAProject (Your_WB_Name)....

Excel: macros, empty column, period number

empty column, period number, external software: Hi Sudha, How are you? What about the following: Sub periods() Dim A As Range Set A = Range( a1:A20 ) redefine in order to fit your date range Dim B(3) B(0) = 11/05/2004 : B(1) = 21/05/2004 : B(2) = 31/07/2004 redefine to fit your date...

Excel: macros, empty column, cell reference

empty column, cell reference, period number: Sudha You do not need a macro to make our comparison, you can do it with a formula as follows +IF(AND(A11 B9,A11 B11),1)+IF(AND(A11 B12,A11 B13),2)+IF(AND(A11 B14,A11 B15),3) In this formula, A11 is the date that you want to compare for the criteria...

Excel: macros, minute intervals, lotus 1 2 3

minute intervals, lotus 1 2 3, control break: Hi Grant, How are you? Please try the following: 1. TOOLS-MACRO-VISUAL BASIC EDITOR 2. INSERT - MODULE 3. COPY Sub KillingTime() If Application.Wait(Now + TimeValue( 0:00:5 )) Then Range( A2 ) = Time() End If End Sub 4. FILE - CLOSE AND RETURN...

Excel: macros, false application, true application

false application, true application, yahoo: Without seeing the macro, I can only give some general suggestions. At the beginning of your macro, put: Application.Screenupdating = False Application.Calculation = xlcalculationmanual Application.EnableEvents = False and just before the macro ends:...

Excel: macros, left hand corner, finished press

left hand corner, finished press, signs: Yes. Press ALT F11. Then press CTRL R. Look for the Project Explorer window in the top left hand corner. Expand the list of all items there by clicking on any + signs you see. Look for a Modules folder. Left click on any module in a Modules folder....

Excel: macros, cell a2, b tab

cell a2, b tab, c output: Hi, You don t need macros for this. assumption: a. Tab DATA has all the IDs from cells A1 to J1 b. Tab Daily has the data that you get from the daily feed from cell A1 onwards - so cells a1, a2, a3 and a4 are related to ID in a1. A5, A6, a7 and a8 are...

Excel: macros, relative cell reference, cell references

relative cell reference, cell references, shp: Chris, The problem is in the last four arguments to the AddShape method. These arguments are Left, Top, Width and Height. Since you have constant values there, it is always placed in the same location. You haven t given me enough information to know where...

Excel: macros/colour, conditional formatting, cell shading

conditional formatting, cell shading, time jody: Hey Jody, I think I understand your question and I think that Conditional Formating will solve your problem. Conditional Formatting is a format, such as cell shading or font color, that Excel automatically applies to cells if a specified condition is true....

Excel: macros/colouring cells, conditional formatting, hand deals

conditional formatting, hand deals, list of names: I assume you are using Conditional Formatting to produce the colors since you did not say. And secondly, I also assume that when you say ... all entries move down to accommodate the latest entry... you mean that a new row is inserted. Thirdly, I assume...

Excel: macros for comparing, cell b3, coloumns

cell b3, coloumns, free time: Sorry, I don t write macros for people on this site who need one, want one, and/or seem to think I am expected to do so unless they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros are more involved than what...

Excel: macros in custom toolbars, toolbar option, custom toolbar

toolbar option, custom toolbar, option button: My guess is that the toolbar isn t being removed when the workbook closes, so next time you open the workbook from the network, the old toolbar which points to the original macros is still present and doesn t get replaced. I recomment this code in the original...

Excel: macros in excel, macros in excel, target value

macros in excel, target value, quantity column: not clear to me - sounds like you only need formulas, not a macro. If I understand, tho, If the Price column is B and the Quantity column is A, then this macro could do the job. Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal...

Excel: macros help, blank row, vba macro

blank row, vba macro, vba code: This code can give you some ideas. It transposes the data from sheet1 to sheet2, assuming that the data starts in A1 and there is no headers, and transforming the data in a simple way to get numbers instead of strings (using the Left function). Sub TransposeData()...

Excel: macros and undo/ reformatting, date formats, d7

date formats, d7, reformat: Chris, 1. That s hard to say because there are many date formats. For instance, consider the following date formats and their VBA equivalents: 5/28/2003 = m/d/yyyy Thursday, May 29, 2003 = dddd, mmmm dd, yyyy 30-May-03 = d-mmm-yy;@ 5/31/03...

Excel: macros and undo, reformat, extreme cases

reformat, extreme cases, subroutine: There is no line of code to check the format of a cell to see if it is in a date format. You will have use some logic and look at the formatting string of the cell to see if that string contains characters like a / , m , d , and/or y and then assume...

Excel: macros in all workbooks, personal workbook, macro name

personal workbook, macro name, menu tools: 1 - it s available in all workbooks ON YOUR MACHINE because it s in YOUR personal.xls 2 - Once you turn on the macro recorder, you need to DO something. What you do will be recorded. I all you did was turn the recorder on and then stop recording, you ll get...

Excel: macros in all workbooks, book1

book1: Brian, You should be able to access the macro from any sheet. lets say the macro is present in Book1. And you want to use it on Book2. 1. open Book1 2. Open Book2 3. Select the cell in Book2 that you want to tighten 4. Go to Tools - Macros - Macros...

Excel: macros, macros in excel, mouse pointer

macros in excel, mouse pointer, macro name: Hi Tiffany, Macros can be made by going to the Excel menu and selecting Tools , Macro , then Record New Macro , under Macro name type Tiffany and click OK . Now do something to the spreadsheet such as color a cell yellow. Then Click the stop recording...

Excel: magic formula involving different "sheets", lolo hotshots, douglas m smith

lolo hotshots, douglas m smith, forestry work: Hi Brice, The basic syntax you listed will sum up the same range of cells across multiple sheets, however, there are some pitfalls. If there are sheets physically inbetween the start and end sheets in the reference, they will get picked up in the calculation....

Excel: mail merge, resultant document, flyer template

resultant document, flyer template, follow ups: From the information given, no I have no idea why this would be the case - are you merging to document (at which point, the resultant document may be too large to handle) or direct to print? Have you set a filter on the results? Are there blank lines in...

Excel: mail merge mailing list to labels, mailing labels, 1000 names

mailing labels, 1000 names, satisfactory answer: Not enough information to give a satisfactory answer - what format is your mailing list in, and is it seperated in any manner at all (eg certain character, tab, etc) if it helps to email to continue the question you can get me at aidan.heritage@virgin.ne...

Excel: mail merger in excel, tabular format, colum

tabular format, colum, excell: You could probably set up a macro to do the job for you, although this would need programming as it s not recordable - see http://www.mvps.org/dmcritchie/excel/email.htm for some good hints on this. Worth noting that IF the data was in Word in a tabular format,...

Excel: mainframe reports opened in excel, blank cells, blank cells, up arrow

blank cells, up arrow, blank cell: Sure -- select from the first date to the last blank cell in that column which should contain a date. Now use Edit/Goto, click Special, click Blanks. Click OK. Type = (no quotes), press the up arrow, then Ctrl/Enter. You re done! See other tips like...

Excel: making a button in excel, sales tax rate, input cells

sales tax rate, input cells, proration: Michael, Do the following example and it should point the way. In my sample spreadsheet, I have Sales , Tax Rate and Total in cells B2, C2, and D2 respectively. In B3, I type in $100. In C3, I type in 6%. In D3 I type =A3*(1+B3) which yields $106....

Excel: making chart, line charts, dear friend

line charts, dear friend, cheers: Hi Parvin How are you? The problem is that I do not have Excel 12 yet. I do not know if it is very different. in Excel 2003, to draw achart that represents y=ax+b you have to choose the type scatter. the type line just plots points and draws a line to...

Excel: making a date change color ..., conditional formatting, color question

conditional formatting, color question, format button: Hi Danielle, I will assume that the cell1 is located in cell A1 and cell2 is located in cell B1 and cell3 is located in cell C1. Select cell2 and go to the Excel menu and select Format then Conditional Formatting... then change the first drop-down...

Excel: making two different cells keep adding up to 100, colomn, colomns

colomn, colomns, l12: Hi Bob, What you are trying to achieve is ability to have formula & value in same cell. This will not be possible in Excel (to the best of my limited abilities). Either you can enter values in L and M is caculated or vice versa. You cannot enter in L12 to...

Excel: manual page break, double arrow, page breaks

double arrow, page breaks, window options: Hi Nancy, Let me make sure I understand your question. Do you mean you forgot how you selected page breaks in Options? If this is the case, this is how you can unselect them: -Click on Tools on the top menu -Select Options -Click on the View tab...

Excel: marco, macro security level, macro in excel

macro security level, macro in excel, macros: Hi Steve, I haven t upgraded to Vista yet, so if it is a Vista-related problem I can t be much help. But just in case you are not aware, when you change the macro security level in Excel you must close the workbook and re-open it in order for macros to...

Excel: match, message server, data source

message server, data source, d8: Email reply sent yesterday, but came back with message server not responding - so reposted below! Here is a custom function for you - put this in a module on the workbook and then use it as any other function - as an example, here is the entry for K8 ...

Excel: match, data source, ogilvy

data source, ogilvy, vba: If the use of VBA to write a custom function is acceptable, then you can email me. twogilvy@msn.com and I will see what I can do. If it has to be a combination of existing worksheetfunctions, I am not sure I have a solution for you. Put ALLEXPERTS: in...

Excel: How to match on a NULL value, double quotation marks, mail private

double quotation marks, mail private, vlookup function: Hi, I gather you re the same Bill as the one who wrote earlier today. (I recognize your work for your description!) In your earlier question, I asked you a quick follow-up, and then realized that I was maxed out on AllExperts and you couldn t answer me...

Excel: How to match on a NULL value, double quotation marks, vlookup function

double quotation marks, vlookup function, territory name: I am confused. If there is no corresponding data item in the data sheet then you will never be able to do this regardless if the data came from Access or not. You never will be able to lookup something that is not there in the table. What did you have in...

Excel: match, hi tim, data source

hi tim, data source, b1: Hi, Tim, This was just a little hard to follow. Perhaps you can send it to me in a somewhat simpler format. What would be helpful is if you can spell out one or two examples for me. Here s one example: - B1 has Monday . - If C1 is blank, I want...

Excel: matching two columns and copying related data to another sheet, programming newsgroup, excel programming

programming newsgroup, excel programming, incoming data: On Sheet2 in the designated cells enter: Q1: =if(iserror(match($A1,Sheet1!A:A,0)), ,Vlookup($A1,Sheet1!A:R,17,False)) R1: =if(iserror(Match(A1,Sheet1!A:A,0)), ,Vlookup($A1,Sheet1!A:R,18,False)) replace Sheet1 with the actual Name of Sheet1. If...

Excel: matching two criteria in a range, douglas m smith, array formula

douglas m smith, array formula, orange 1: Hi Norlito, You can do this with an array formula but you will need to fill down the numbers in column so each line has an outside diameter. The formula would be: =SUM(IF(A1:A12=E1,IF(B1:B12=E2,C1:C12,0),0)) Note:E1=OD criteria, E2=color criteria...

Excel: matching data, destination cells, tmm

destination cells, tmm, assumptions: Hi Jesse, Here is a macro that I believe does what you want: Sub AlignEm() Const FirstRow = 2 Dim RowOffset As Long Dim i As Integer For RowOffset = FirstRow To [a65536].End(xlUp).Row If Cells(RowOffset, A ) =...

Excel: matching list of names in excel, vlookup formula, data validation

vlookup formula, data validation, excel spreadsheets: You need to replace TAN in the formula with the cell address that contains the value, such as A1. You need to replace A:A with the RANGE of cells in your List 1, such as List1!A1:List1!D34. The best way to reference this list is to create RANGE name for...

Excel: matching, employee numbers, vlookup function

employee numbers, vlookup function, false hope: Hi, Imtiaz, This is what the VLOOKUP function does. On Sheet1, you have the following setup: A2:A500 Employee numbers B2:B500 First names C2:C500 Surnames On Sheet2, you have an employee number is A2. In order to put the appopriate first name...

Excel: Is there a maximum size of a spreadsheet? I think I might have hit it., douglas m smith, csv files

douglas m smith, csv files, csv file: Hi Lauren, Yes, you ve hit the farthest right column of Excel (for now). IV is the 256th column. You will have to wait for Excel version 12 (aka Excel 2007) to come out for more columns, 16,128 more to be precise. That version will have spreadsheets...

Excel: meged cell trouble, excel sheet, format menu

excel sheet, format menu, access database: This type of question is difficult to answer without seeing the workbook. Have you got the cell wrapping text? Maybe the cell just needs resizing. I believe Excel limits Text imports to 256 characters, does the cell exceed this? There is more information...

Excel: menu item vba, right click menu, row selection

right click menu, row selection, selection range: You can use the intersect method to check the particular range with the selection range, that is the cell that have triggered the macro from the right click menu. For example: Sub InRange() Dim rngValid, rngCheck As Range Set rngValid =...

Excel: menu, column headers, toolbars

column headers, toolbars, caution: For the first question, you can add code like this on the Exit event of the text box: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) If Not IsDate(TextBox1.Value) Then MsgBox You must enter a valid date , , Error ...

Excel: menus, cell b1, cell c1

cell b1, cell c1, cell a1: Hi, Mark, If I were you, I d want the information about MARK displayed all of the time. wink Excel formulas can make things appear and disappear based upon the contents of cells, but they can t make things appear and disappear based upon the location...

Excel: merge two columns (match column's text string), cell b2, excel 3

cell b2, excel 3, b 111: Presuming I understand the question correctly, this can be done with VLOOKUP. Setup: 1) List A {111,111,333,333,333,333,444, etc} is located in rows 2 through 17 of column A in Excel 2) List B {111,222,333,444, etc} is located in rows 2 through 10 of...

Excel: merge data into excel spreadsheet, spreadsheet data, spreadsheet format

spreadsheet data, spreadsheet format, excel spreadsheet: Hi Terri, I d say your best bet is to use the vlookup function. The only thing you need to do is make sure that you have a unique ID for each new form to be printed. Set up your raw data as a list where each record is a row with data filled across columns,...

Excel: merge excel info into Word doc, dell gx60, title case

dell gx60, title case, word doc: In part this is by design, although I m worried about the lack of decimal point (which implies the value may not be as you expect) - it is however relatively easy to fix, just view the field codes and add the appropriate formatting switches to them # ...

Excel: merge two sheets based on one common col., sheet1, g100

sheet1, g100, hth: Lots of assumptions here. Say the ID column in sheet2 is col D. Say the missing info from Sheet1 that you want to show up on sheet2 is in columns A, C, G of Sheet1 and you need them to be in cols E, F, G of sheet2. Say that the ID column from sheet1 is col...

Excel: How to merge two spreadsheets using VLOOKUP, vlookup function, computer names

vlookup function, computer names, room numbers: Assuming the first set of data is in sheet 1 and the second set of data is in sheet2 then in C1 of sheet 2 try this formula: =OFFSET(Sheet1!A1,MATCH(A1,Sheet1!$B$1:$B$4,0)-MATCH(A1,Sheet1!$B$1:$B$4,0),0). This will pick up the computer name from sheet1...

Excel: merging based on one common column, number column, aidan

number column, aidan, heritage: Do you have one sheet that has all records? If so, use this one to get the data from the other sheet - assuming that the ID column is the first column, then the formula you need is vlookup - my pages at www.aidanheritage.co.uk/Excel explain this. If not,...

Excel: merging cell, format cells, v11

format cells, v11, alignment: In simple terms, no it isn t as this is by design. HOWEVER, you could do it by using formulas to build the merged cells in another column/row which you could then copy back over the existing values (or build the data on another worksheet), or (and probably...

Excel: "merging" documents, text import wizard, student performance data

text import wizard, student performance data, semi colons: Without VBA programming, you can open each file (hold the shift key down when you do this to avoid the text import wizard), then select all the data (ctrl/*), copy, new workbook (which will be the master), go to the first blank cell, paste. With VBA programming,...

Excel: merging excel work books into one, work books, hold down

work books, hold down: Not exactly sure what you need to know - you could select all sheets of the one workbook (click on the first one, hold down shift and click on the last) then right click and move or copy to another workbook if it is just getting sheets across, but if it is...

Excel: merging in excel, cell b2, social security number

cell b2, social security number, duplicate records: Emma: Here are the steps for removing duplicate items (you may need to adjust depending on your situation). 1) There should be a unique identifier for each record (row), such as social security number. In our example we will remove any record with a...

Excel: merging excelinfo into word doc, dynamic data exchange, dell gx60

dynamic data exchange, dell gx60, microsoft windows operating system: Tanya, This one is kind of strange. When you merge data into Word using the standard process, this is actually expected. Sorry. However, you can alter the way Word talks with Excel. Dynamic Data Exchange (DDE) is one method available in the Microsoft Windows...

Excel: merging two files & removing duplicates, douglas m smith, hi doug

douglas m smith, hi doug, location option: Hi Joan, Excel used to have a Data Delete menu item but that went away in the mid-90 s for some reason. These days, you can use the Advanced Data Filter to accomplish that task. Select a cell in your combined list and then select Filter from the Data...

Excel: merging two sheets, vlookup formula, leftmost column

vlookup formula, leftmost column, absolute reference: I think it will be easier to work with your actual layout. What I would need to craft the formulas is the following: Ranges where the data is located (for example, A1:J500 in sheet1), headers for both ranges (ID, name, etc.) and what data you would like to...

Excel: merging workbooks, spreadsheet, wizard

spreadsheet, wizard: Excel has a merge wizard, but it only works with shared workbooks that originally were the same spreadsheet, but later deviated from each other – I suspect that is NOT your case. Are you worried about duplicated data? If not, then you can highlight all...

Excel: Go to method, autoopen macro

autoopen macro: Guillermo You can create a goto macro that will do what you need, just record the macro and then do all the steps you want for that cell to be showing, then save the macro and place it onto your toolbar so that you can access whenever you need it. You...

Excel: microsoft excel formula, microsoft excel 2000, douglas m smith

microsoft excel 2000, douglas m smith, vlookup function: Hi Dag, You question pretty much describes what the VLOOKUP function does. =VLOOKUP(lookup value, table array, column index number) lookup value would be a reference to the cell with the employee s name to lookup. table array is the cell range...

Excel: migrating data, customer profile, mail

customer profile, mail, vb: I take it this is one page for each of the 150 customers - the easiest way to do it is to use Word and do a mail merge - you could also write a macro that did the job but you would need knowledge of VB to do this (I could probably do it for you, but would...

Excel: min function combined with a condition, apartment type, field settings

apartment type, field settings, minimum rent: The EASIEST way to do this is by using the data menu, then the pivot table and chart report - this gives you a fairly helpful wizard - once completed, drop the type and square feet headings in the left column, and the rent TWICE into the centre area - right...

Excel: misbehaving array formula, douglas m smith, array formula

douglas m smith, array formula, statements sum: Hi Alan, You are on the right track. The array formula version would be: {=SUM(IF(let=1,cost,0)} The array formula evaluates the corresponding values in let and cost. However, Excel has a regular function that will do the same thing: =SUMIF(let,1,cost)...

Excel: missing database record numbers, excel programming, database record

excel programming, database record, google: greg you can do it with VBA however I am not an expert with VBA I would suggest trying Aidan Heritage. You may also want to look at this link for some VBA compare options http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/d3a991c70200361f/82f45e58db65b390?lnk=st&q=compare+two+spreadsheets&rnum=4#82f45e58db65b390...

Excel: missing database record numbers, database record, record numbers

database record, record numbers, a7: If the numbers are in column A, then this will put the missing ones in D: Sub Missing() Dim n As Integer On Error Resume Next For i = 1 To Range( A65000 ).End(xlUp).Row Err.Clear n = 0 n = Application.Match(i, Range(...

Excel: missing help on excel tutorials, linear regression, blank cell

linear regression, blank cell, excel tutorials: Hi Anna How are you? If you are using VBA, you can detect the first empty cell on a column by applying the CountA function. E.g. Sub testingg() Dim A As Range Set A = Sheets(1).Range( a:a ) proxi = WorksheetFunction.CountA(A) + 1 MsgBox proxi ...

Excel: mode on excel, array formula, formula index

array formula, formula index, excel formula: Paul: I have uploaded a reference file here: http://home.swbell.net/nate-sus/excel/antimode.xls Presuming you have a range of numbers in cells A1 through A20 you would use this formula to get the MODE: =MODE(A1:A20) Of course, you knew that much...

Excel: How to modify this code, vlookup function, sheet tabs

vlookup function, sheet tabs, circular reference: sub LookupData() change the b9 to refer to the cell containing the formula. Change sheet6 to the correct sheet name Dim rng as Range, tot as Double Dim i as Long, res as Variant, v as String Dim shStart as Worksheet, shEnd as Worksheet Dim bStart...

Excel: modify a list, column c, comma

column c, comma, email: Hi , I assume that each email is contained in a row in a single column. Insert a blank column next to the email column temporarily). If the email column is B and the emails are from row 1 to row 20 and C is the blank column, in the C column put this...

Excel: mortgage calculation, mortgage calculation, financial calculator

mortgage calculation, financial calculator, mortgage company: Here s an example. Say you owe $150,000, with a yearly interest rate of 6%. At the end of each month, you make a $900 payment. The formula to calculate how many payments you ll have to make to reach a balance of zero is: =NPER(6%/12,-900,150000,0,0) ...

Excel: mouseover, how to insert a hyperlink, activex object

how to insert a hyperlink, activex object, box pop: Sounds like you have a comment in a cell, which shows when you hover the mouse over it. Maybe the image leaves room for the edge of the cell under the image for the comment to show. If not, the image must be an ActiveX object with VBA code to show this text...

Excel: move cursor where duplicates are found., base font, excel worksheet

base font, excel worksheet, xls: As I understand it... you have entries in B77:B132, and you want to identify duplicates. To make this simpler, I d enter this formula in cell C78: =IF(ISERROR(MATCH(B78,$B$77:B77,0)), , dupe ) and then copy it down through C78:C132. This formula will...

Excel: moving cells, true condition, n1

true condition, n1, excel 2000: In this case, the formula has to reside in Q1. The formula in Q1 would start out as follows: =IF(N1=1,B1, Following the comma, enter the value of Q1 when N1 is NOT equal to 1. Please let me know if you have any other questions on this. Jan ...

Excel: moving the cursor, douglas m smith, cursor down

douglas m smith, cursor down, macro tools: Hi Marlin, Yes, this can be done but you will need a macro to do it. Start to record a macro (Tools / Macro / Record new macro...) and in the dialog box, enter the macro name and the control character you want to use for the shortcut. Click OK, select...

Excel: moving data between sheets in 1 workbook, sum formula, three sheets

sum formula, three sheets: If you just want the numbers and formats, you can do the following: - Select the column that you want to move and go to menu Edit- Copy - Select the column on the other worksheet where you want to put the data - Go to menu Edit- Paste Special, and select...

Excel: moving file with linked graphs, excel 97, stead

excel 97, stead, macros: K In stead of moving the files from outside excel, open the file that contains the links, then open all the linked files, and use Save As to save these linked files on the ?: drive. This updates the links automatically. And don t forget to save the...

Excel: moving files from micrsoft office excel 2003, micrsoft office, floopy drive

micrsoft office, floopy drive, floppy disc: Clare This link will explain what you need to do http://www.webtree.ca/windowsxp/saving_files_to_cd.htm I would suggest though that you work on your files as you normally would and save them to the hard drive, then at the end of the day back them...

Excel: moving sheets, and a linking question, excel programming, excel spreadsheet

excel programming, excel spreadsheet, alphabetical order: Hi, Kristina, Both of these things can be handled with macros. What you need is an expert who can do some Excel programming for you. You probably noticed in my description that I don t do VBA programming. But there are many experts on the site who do....

Excel: ms excel: combining data of many worksheets to one, paste option, formatted data

paste option, formatted data, time option: abby You can use the copy paste option if it is a one time option, this would then leave the formatting intact. If you want to do this and keep it updated automatically you will need to create a macro (VBA). I would suggest contacting Aidan Heritage as...

Excel: ms excel graph, line graph, point move

line graph, point move, hi bob: If you want to see the point move, you can update the value in the cell in increments like this -- assumes you want it to grow from current value to current value + 5: Sub GrowPoint() Range( E12 ).Value = Range( E12 ).Value + 1 ActiveSheet.Calculate...

Excel: ms excel help, budget info, macro menu

budget info, macro menu, vertical columns: I would recommend recording a macro (menu Tools - Macro - Record new macro). Select that menu option, put the macro a name and a shorcut, and do all the operations that you want to automate. After that, every time you hit the shortcut keys Excel will do...

Excel: ms excel help needed, cell b2, budget info

cell b2, budget info, sure what kind: I ll have to be honest; I really don t understand the question. Perhaps it would be better if you sent a copy of the spreadsheet to Lotus@swbell.net and I could then look at the actual sheet. Nevertheless, here is an answer to what you are asking. Presume...

Excel: ms excel help, detail cause, budget info

detail cause, budget info, vertical columns: I don t really understand your scenario too well. If you re trying to fill cells with values on the condition that an entry is made in another cell, I would use the ISBLANK function in a simple formula. I m not going to go into detail, cause I m not really...

Excel: msgbox enable macros, macro security, security settings

macro security, security settings, malicious code: Alan, the security settings are set to Medium. You change that by going to Tools/Macro/Security and setting it to Low (in Excel). That of course, may be seen as a risk by an administrator if you have one, so you should discuss it with them if needed. Note...

Excel: msQuery, open data source, unfiltered data

open data source, unfiltered data, ms query: Sorry for the delay in replying - I wanted to re-install Office 2000 on one of my computers which I have now done so that I can be sure of my instructions - luckily, 2000 is the same as 97 and therefore makes mail merge as easy as 1-2-3 use the tools menu,...

Excel: muliple instances of same workbook open, commandbutton, code modules

commandbutton, code modules, opendatabase: Hi Jason, I really don t know much about databases, having only dabbled with the most trivial of examples. As a result I was reluctant to even hazard a guess as to the cause of your problem. However, one thing does come to mind, so I will stick my neck...

Excel: multi stage discount calculator, online calculator, added advantage

online calculator, added advantage, dcf: Hi Brian, Yes, I think I can do a calculator in Excel that has the same functionality. I can t get exactly the same functionality on a worksheet--in particular the PV instantly updating as values are being typed in (Excel wants an Enter before doing a calculate)--so...

Excel: multiple cell value copy, wkb, microsoft excel

wkb, microsoft excel, f10: I would very rarely use copy in a macro, but if you select the range first, then copy the selection it should work (you can record this macro). My own suggestion would be dim curBook as workbook set curbook=activeworkbook open the file wks.range( a65535...

Excel: multiple column formula, c number, cols

c number, cols, g1: If you want a result based on 2 conditions (e.g., sum of units for rows in which col A is a date in a particular range AND col B is a particular code), that can be done with a DSUM formula, not a SUMIF. If you want to use the simpler SUMIF, you need to...

Excel: multiple column matches, consolidated location, column number

consolidated location, column number, designation: It s hard to picture what you re looking for. What, exactly, would you want to see in this resulting cell, and where is this cell? For example, are you wanting to see something like 3,5,6,7,9,12,14,15,18,22,35,102,103,104,200,205 in cell IV4, for example?...

Excel: If / multiple condition formula, douglas m smith, blank cell

douglas m smith, blank cell, bob douglas: Hi John, Basically, you would just copy the above formula (without the = sign) and paste it back into the formula, replacing Not Bob . In the internal IF statement, change Bob to Steve , Not Bob to , and the numbers in the AND statement to correspond...

Excel: multiple external reference, myfolder, external reference

myfolder, external reference, xls files: Rather than have the macro open the files, have the macro write the formula be refering to the two columns that have workbook name, sheet and cell in the sheet. for each cell in Range( A1:A450 ) cell.offset(0,2) = =C:MyFolder[ & cell.Value & _ ...

Excel: multiple listBox and user input manipulation, base source, multiple choice

base source, multiple choice, arrays: Hi Hanan Thanks for trying to make your need clearer Thinking about your second listbox why not have the datentered from a simple text box. Then taking the current index vale of the first list box up date the second list box at the same index point...

Excel: multiple spreadsheets, cell b2, portfoilo

cell b2, portfoilo, share portfolio: Neither SUMIF, nor VLOOKUP allow you to search over multiple worksheets. What I d do is create a consolidation sheet, with a table of formulas. In column A you write the worksheet s name and in the adjacent columns you use a combination of the INDIRECT...

Excel: multiple if statement, target figure, d11

target figure, d11, number 43: Yes -- look again at the formula. IF(D11 =30,4,IF(OR(D11 =20,D11 =29), 3 ,IF(OR(D11 =10,D11 =19), 2 ,IF(OR(D11 =0,D11 =9),1)))) Suppose D11 has 15 in it. Well, 15 IS =29, so it satisfies the condition IF(OR(D11 =20,D11 =29), so it never goes any farther....

Excel: multiple if statements, spread sheet, profit loss

spread sheet, profit loss, h4: Find the FALSE portion in one of the IF statememts. Copy the other IF statement from the formula bar EXCLUDING the first = mark. Paste that over the FALSE portion of the other IF statement. And, a rhetorical question - why do you use so many parentehsis?...

Excel: multiple tabs, asap utilities, cell a2

asap utilities, cell a2, index sheet: Hi Shane, Yes you can. You can use Hyperlinks to navigate between worksheets. Here is the basic way to do this. If you have a workbook with 3 worksheets named Sheet1, Sheet2, Sheet3 select Sheet1 and cell A1 then go to the Excel menu and select Insert...

Excel: multiple vlookup = nested IF ?, vlookup formula, cell d2

vlookup formula, cell d2, sumproduct: You can try the following formula in column D of sheet 2, copy it at cell D2 and drag it: =INDEX(Sheet1!$D$2:$D$11,SUMPRODUCT(--(A2=Sheet1!$A$2:$A$11),--(B2=Sheet1!$B$2:$B$11),--(C2=Sheet1!$C$2:$C$11),ROW(INDIRECT( 1:10 )))) Change the ranges as appropriate,...

Excel: multiply time and money, excel 2002, amount of money

excel 2002, amount of money, time and money: Hi Doug, Sorry in my rush last night I made a mistake on the formula for Cell E2. It should be =(C2-B2)*D2*24*2 Here is where you can download an example workbook that I made for you. http://www.squish.com/cloverleaf/babysitting.xls Thanks Ke...

Excel: multiplying, j2, thanks in advance

j2, thanks in advance, macros: Sub Mult() find an empty cell to use: Set emp=range( A65536 ).end(xlup).offset(1) Range( H2:H65536 ).copy range( J2 ) emp.value=9.5 emp.copy Range( J2:J65536 ).PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply ...

Excel: multiplying, qestion, cell a1

qestion, cell a1, cells: Doug I m not sure from the qestion whether you are multiplying each cell in a group or the sum of the group, so I ll give you the answer to both. To multiply a single cell, the formula looks like this =A1*.10 That says take the contents of cell...

Excel: mysterious addition of '2' in pivot table, pivottable, pivot table

pivottable, pivot table, header section: Karen: Based on your description I believe you are seeing the ACRONYM in the header section of your PivotTable. PivotTables require each column header of your source data to have a unique title. I suspect two of your headers are identical (they both...

Excel: IF for #N/A, douglas m smith, cell a2

douglas m smith, cell a2, isna: Hi Moshe, You can use the ISNA and OR functions to trap that error: =IF(OR(H2=0,ISNA(H2)),1,2) If H2 is either 0 or the #NA error, then 1 will be the result. You might also consider using the ISNA function in the cell H2 formula and trap the error...

Excel: IF for #N/A, cell a2, numeric value

cell a2, numeric value, moshe: To excel, only a numeric value of zero or a blank is equal to zero - a space or an error do NOT evaluate to zero - so there are two options - one being to modify H2 such that it returns zero in the event of the error,the other being to handle the error in...

Excel: IF for #N/A, cell a2, moshe

cell a2, moshe, a1: If the formula receives #N/A as the input, it will return #N/A, as it doesn t know what to do with it. To avoid this error, you can use a formula like: =IF(ISNA(H2),1,IF(H2=0,1,2)) First check the error, and then do the original statement. Hope this...

Excel: Name Box - Database, matter of course, datasource

matter of course, datasource, macros: When using excel as a datasource, make sure the data is on the first sheet in the workbook, and that the data has no blank columns - if necessary, just put a heading row in blank columns - this should ensure that the database is picked up in full. Macros...

Excel: NameBox Causes Excel to Crash, spreadsheet, spreadsheets

spreadsheet, spreadsheets, macros: Hi Steve, I think I would need to see the spreadsheet to see if I could figure it out. I would suggest removing the Name Box and hyperlink to confirm that that is where the error is occurring, then at least you ll have the location of the error isolated....

Excel: Named Ranges in Macros, relative reference, name selection

relative reference, name selection, cell references: To get the full address of the selected range Dim Addr As String Dim Sh_Name As String Sh_Name = ActiveSheet.Name Addr = Sh_Name & ! & Selection.Address Use this if you want or need to get a relative reference: Dim Addr As String Dim Sh_Name...

Excel: Names of worksheet tabs, excel 5, hi john

excel 5, hi john, a3: Hi John How are you? Please do the following 1. TOOLS - MACRO - VISUAL BASIC EDITOR 2. INSERT MODULE 3. COPY Sub wsnames() Set r = Sheets(1).Range( a1 ) For i = 1 To Sheets.Count r.Cells(i, 1) = Sheets(i).Name Next i End Sub 4. File - return...

Excel: Naming the file, feature details, filesearch

feature details, filesearch, freds: I assume that the month is the month they are currently in? If so, then in code format(now(), MMMM ) would give you the name of the month. As to the week 1, week 2 etc - I can think of a couple of ways of doing this - one would be to use an INI...

Excel: Naming and Labels, douglas m smith, intersection operator

douglas m smith, intersection operator, eva marie: Hi Eva-Marie, In a nutshell, named ranges are physically named and defined, but labels implied/interpreted. Named ranges can be referenced on other sheets and workbooks but labels sheet-contained. Labels only work within a data table layout. You...

Excel: Naming a Range with VBA, cell content, macros

cell content, macros, many thanks: If I understand your question, then the code at the end takes the text in A1 and creates a range name for A1 using the the text in that cell. For example, if A1 contained the Test then A1 would then have a range name of Test. If you have invalid range...

Excel: Naming Sheets, cell c3, sheet tab

cell c3, sheet tab, sh1: Keith, One way would be to copy Main: Sub EFG1() Worksheets( Main ).Copy _ After:=Worksheets(Worksheets.Count) With Worksheets(Worksheets.Count) .Name = .Range( C3 ).Text End With Worksheets( Main ).Cells.ClearContents End Sub to do literally...

Excel: Naming the saved text file, getopenfilename, fname

getopenfilename, fname, optional arguments: Hello Denny, Dim fName as Variant fName = Application.GetSaveAsFilename() if fName false then Open fName For Output As #1 else msgbox You chose to cancel exit sub end if see Excel VBA help on GetOpenFileName and GetSaveAsFileName...

Excel: Navigating in excel, cell b1, cell a1

cell b1, cell a1, vba code: ActiveCell.offset(-1,0).Select North ActiveCell.Offset(-1,1).Select NorthEast ActiveCell.offset(0,1).Select East ActiveCell.offset(1,1).Select SouthEast ActiveCell.Offset(1,0).Select South ActiveCell.offset(1,-1).Select Southwest...

Excel: Navigating sheets with dropdown lists, target address, target value

target address, target value, data validation: You don t need an additional sub, you can modify the current If function adding additional checks, something like: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $B$14 Or Target.Address = $B$10 _ Or Target.Address = $B$5...

Excel: Navigation via Drop-down Menu, sheet names, cell a2

sheet names, cell a2, cell a1: Somewhere on your sheet, create a range containing all the Sheet names in your workbook. For instance, Sheet1 in cell A1, Sheet2 in cell A2, etc. Then from the main menu, select View, Toolbars, Control Toolbox. Use the Control Toolbox to put a Combo...

Excel: NETWORKDAYS and MEDIAN?, list of holidays, patient samples

list of holidays, patient samples, signout: Hi Barrett, I don t quite understand how your spreadsheet works. But I would assume there is a range of cells that have these =NETWORKDAYS function. It should be to simply select a different cell with the funtion =MEDIAN(RC1,RC12) depending on the range...

Excel: Need a button or some other way, document button, input area

document button, input area, data input: By the way (BTW) HTH stands for Hope This Helps and is not my name or initials - two common acronyms you will see on sites that help people Just use a hyperlink. Right click the cell where you want the user to click that would take them to the desired...

Excel: Need Complex VBA Code for Macro, estate investment analysis, real estate investment analysis

estate investment analysis, real estate investment analysis, real estate investment: Wow -- you put a lot of effort into this where it;s not needed. First, you re never changing sheets, so you could always refer to ActiveSheet instead of Sheets(ActiveSheet.Name), and you don t even need to do that because Activesheet is default for the range...

Excel: Need Countif across many worksheets, sum works, relative cell

sum works, relative cell, 3d type: Laura, this is definitely a job for VBA code to handle for you. The example code below will add up the number of times across your sheets how many times the name in A124 appears in the A124 on all of the other sheets. Here s that code: Sub Laura() Code...

Excel: Need to copy data from one excel sheet to another, address lines, strange one

address lines, strange one, column c: 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 do so unless they are very short, quick, and simple. Perhaps someone else on this site would. 99% of all macros are...

Excel: Need to count the number of times a digit appears in a sheet, looper, rar

looper, rar, digit: I would do this with a custom function - press Alt F11 to get into the VBA editor, then use the insert menu to insert a module. Paste this Function Counter(rar As Range, val As String) Dim looper For Each cell In rar For looper = 1 To Len(cell.Value)...

Excel: Need Formula, cell b1, cell c1

cell b1, cell c1, cell a1: Tim You can use and if statement for this question. =IF(A1*B1 =6.97,6.97,A1*B1) This multiplies the cells and if the answer is true places 6.97 and if the answer is false (or greater) then places the larger amount. Hope this helps. Richar...

Excel: Need to find all instance of a text string and copy information from the row(s) it is located on., array formula, array type

array formula, array type, vlookup: Meg, It could probably be done with a complex array formula, but you don t provide enough information to even begin to provided a detailed solution. This site gives you a general overview of array type formulas although it is specific to sumproduct...

Excel: Need formula to check if cells below <, ogilvy, ascending order

ogilvy, ascending order, h2: If you want to check each cell. go to H1 put in the formula =if(g1 =g2, OK , Problem Greater than next ) then copy this down the column. If instead, you want to start in H2 and check the previous cell, in H2 put =if(G2 G1, Problem, Less than previous...

Excel: Need formula to track time spent, douglas m smith, format cells

douglas m smith, format cells, decimal fraction: Hi Bill, It sounds like your spreadsheet is really doing what you want but the following information should help you show it the way you want. Some details of displaying and calculating time: If you change the number format (Format / Cells... / Number...

Excel: Need a formula, array formula, curly brackets

array formula, curly brackets, l8: You can use an array formula like this: =SUM(IF(((L1:L9=1)+(M1:M9=1)+(P1:P9=1)) 0,1,0)) Enter it with Ctrl+Shift+Enter, Excel will put curly brackets around the formula in the formula bar to let you know it is an array formula. You expand and change the...

Excel: Need Help, conditional statement, manhours

conditional statement, manhours, error value: It sounds like a lookup is called for if I have understood the question correctly -you basically need to return a different value for your calculation for each of the entries that COULD be chosen in P5 - so it would be =vlookup(What,Where,Which,Logical)...

Excel: Need Help in Automating Time Stamps & a little more., target address, visual basic for applications

target address, visual basic for applications, time stamps: You can modify the code to Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Range( T1 ).Address Then Range( U1 ).Value = Now ElseIf Target.Address = Range( M1 ).Address Then Range( V1 ).Value = Now ...

Excel: Need Help with Excel Macro, excess cash, sh1

excess cash, sh1, dear tom: Benjamin, The best I can do is take a guess at what you want to do Private Sub CommandButton1_Click() Dim sh as worksheet, sh1 as worksheet Dim r as Range set sh = worksheets( Dest ) set sh1 = worksheets( Source ) set r = sh.cells(rows.count,2).end(xlup)(2)...

Excel: Need Help in a Excel Standard Function, vba code, minute delay

vba code, minute delay, time thanks: It is impossible for me to answer that because I have no idea what the function does and/or how it does what it does. It could be that the logic of the function and/or how you call it could be changed to increase its efficiency. There are other sheet events...

Excel: Need Help in Freeing Up Excel Resources, xls file, mb memory

xls file, mb memory, system resources: You did not say if you have been enabling macros all along PRIOR to getting this message Two questions : (1) any idea why I m receiving the warning mentioned above, and NO (2) what settings for Excel can I safely turn off to free up more system...

Excel: Need Help, y10, error msg

y10, error msg, excel 97: Here is a function that will tell you whether the scores in D10:G10 Fail or not. It will accept Blank cells, or numerals 1 or 0. If any cell has a 0 in it, it is an automatic failure. If all 4 cells contain only a combination of 1 or Blank characters,...

Excel: Need help with Conditional Formatting - Dates & Shading, microsoft excel 2000, cell shades

microsoft excel 2000, cell shades, workcenter: Using F12 as the sample cell, you can enter this as the FIRST condition formula: =AND(F12 TODAY(),YEAR(TODAY()) YEAR(F12)) and color this red SECOND condition is what you already have - =F12 TODAY() & colored yellow. (This is untested, but I think...

Excel: Need help with Excel array to update pricing, sorry for the confusion, vlookup function

sorry for the confusion, vlookup function, material id: Yes, I see ...83% Water 5% Salt 1% Surfactant 11% something ? but no calculations, just hard numbers. I now understand WHERE your calculations are. One suggestion is to use the VLOOKUP function. Your table of data needs to have the ID# in the first...

Excel: Need help with Excel Macros, excel macros, filename length

excel macros, filename length, excel macro: Press Alt/F11 - brings you to the VBE Use Insert/Module Paste this in: Sub Sree() [a1].CurrentRegion.AutoFilter beg = DateValue(Month(Date) & /1/ & Year(Date)) endg = DateValue(Month(Date) + 1 & /1/ & Year(Date)) [a1].CurrentRegion.AutoFilter...

Excel: Need help with a formula, douglas m smith, brainbench

douglas m smith, brainbench, absolute reference: Hi Tamara, The Discount formula would be: =IF(Quantity 1,Unit Price * Quantity * $I$1,0) The formula for total would be: =Unit Price * Quantity - Discount or this should too: = UnitPrice * Quantity * IF (Quantity 1, 1-$I$1, 1) Douglas...

Excel: Need help for LOOKUP Dates, douglas m smith, index lookup

douglas m smith, index lookup, brainbench: Hi Ranjith, The combination of MATCH and INDEX functions is better suited for this: =MATCH(lookupdate,lookup range,0) This locates the start date s location, returning it s position in the range. Looking for 6-Oct-04 in your list returns a value...

Excel: Need help with IF statement, n3, l4

n3, l4, h4: You didn t tell me what you EXPECT it to do so I m guessing - I can tell you that the formula has incorrect bracketing, some of which isn t needed (+M4) would be exactly the same in this formula as M4. I m guessing you want MULTIPLE if conditions so it SOUNDS...

Excel: Need help with IF statement, true side, n3

true side, n3, arbitrary number: That gives me a headache. Simplifying your statement really has nothing to do with what I suggested. Build the simplest IF statement first: IF B4 does not equal B3 I want the number to be the same as cell M4. =IF(B4 B3,M4,200) Then build the IF...

Excel: Need help with IF statement, n3, h4

n3, h4, l4: Sorry...I stepped away for the afternoon. Here we go: IF B4 does not equal B3 I want the number to be the same as cell M4. If B4 does equal B3 (it means B4 is the same part number as B3) so therefore if H4 equals PO then I want the answer to be the same...

Excel: Need help with sumproduct & dynamic ranges, cell b10, dynamic ranges

cell b10, dynamic ranges, column headings: Sounds like you know how to do this if you have one column to sum in conjunction with several conditions. So sounds like the best solution is to add a column in the source data that gets a total for that row based on the number of months in B4. Then...

Excel: Need help with sumproduct & dynamic ranges, cell b10, array formula

cell b10, array formula, dynamic ranges: I m not an expert in SumProduct, BUT the help implies that this wouldn t do what you want as it wouldn t filter the list - I think the EASIEST way to do it is with an array formula =SUM(IF(A29:A34=B1,IF(B29:B34=B2,IF(C29:C34=B3,INDIRECT( C29: & ADDRESS(34,COLUMN(C29)+B4)),0))))...

Excel: Need help with Time Stamps, stamp details, time stamps

stamp details, time stamps, vba code: Right-click the sheet tab, select View code, paste this in: Private Sub Worksheet_Change(ByVal Target As Range) Select Case Selection.Column Case 13 Cells(Target.Row, 21).Value = Now Case 20 Cells(Target.Row,...

Excel: Need help with VBA macro, vba macro, copy paste

vba macro, copy paste, macros: weston, You talk about finding the last column, then copying to the end of a series. I have no idea where the series is. You example is about copying rows. So I will address both the last column copied to the next column and the last row copied to the...

Excel: Need help writing a reference Macro, false application, writing a reference

false application, writing a reference, change path: IF I understand what you now want then your code can just write a file linking formula but that really will not help all that much until after you save the file, close it, and then reopen it. The only other way is to have the code loop thru all of the files,...

Excel: Need to lookup values, and perform calculation to populate field, record sum, result2

record sum, result2, database title: Sandi, not a problem: Sub Sandi() The J5 designation is the column containing Blend. The code starts here, and places results in this column anytime column C contains Sec . It selects J5 to start, allowing for titles in row 4. You need to use the column...

Excel: Need Macro, surname letter, value case

surname letter, value case, jones case: Not enough information to go on - what criteria should be used to pick the names (is it simply a lookup table for example, or is it based on Surname letter). The macro itself will be relatively straightforward, but on the information given at the moment the...

Excel: Need Macro to Automate, percentage range, excel spreadsheet

percentage range, excel spreadsheet, excel worksheet: To get a new workbook with 5 sheets with the appropriate name workbooks.add While Worksheets.Count 5 ActiveWorkbook.Worksheets.Add Wend Worksheets(1).Name = 0-20% Worksheets(2).Name = 21-40% Worksheets(3).Name = 41-60% Worksheets(4).Name...

Excel: Need a Macro, spreedsheet, tho

spreedsheet, tho: I have no idea what you mean by ... every 6 rows need to be turned horizontally... - Rows ARE horizontal. Please be more specific. As to deleting 4 rows: Sub Delete4() For i=1 to activesheet.usedrange.rows.count step 259 rows(i).resize(4).delete...

Excel: Need macro to print sheet with multiple repetitions, cell a1, sales territory

cell a1, sales territory, excel 5: Hi Bill! How are you? I am sure it does not solve your problem, but it may help to start 1 TOOLS - MACRO - VISUAL BASIC EDITOR 2 INSERT A MODULOE 3 COPY Sub regionsreport() Dim A As Range Set A = Range( a1:H10 ) For r = 1 To 4 number of regions...

Excel: Need macro to print sheet with multiple repetitions, cell a1, sales territory

cell a1, sales territory, new territory: Yes, there is CAN BE A macro but you will have to write it. EXCEL does not have one built in to do this for you. If you are familiar with macros then you will understand that using a loop will help you accomplish this. You will need to loop thru all districts...

Excel: Need macro to print sheet with multiple repetitions, cell a1, data1

cell a1, data1, sales territory: You would have to develop a list of the data you want to place in cell A1, then you could have your macro run through this list, update A1, then print the sheet. Assume the list of territories is in a sheet named DATA1 and the list starts in A1 (through...

Excel: Need a macro to update Excel spread, spreadsheet, norm

spreadsheet, norm: Norm You need to create a name for a cell immediately below the current end of the table, something like end, when the name is created you can then tell the macro to go to that name, insert a new row (make sure it inserts the row above the current one)...

Excel: Need a macro, spreedsheets, hello bill

spreedsheets, hello bill, free time: Sorry, I only answer questions. I do not write macros from scratch for anyone and everyone who asks me to write macros for him/her from this site. Imagine how many, for free and on my own free time no less, I would write if I honored every request like...

Excel: Need a method to convert french words to english within a cell, target words, english phrase

target words, english phrase, simple exercise: Here s a better translation code, that should be easier to understand. It assumes that there s a translation table with the english words in column one and the french words in column two. The translation table must have the range name tblTranslate. In the...

Excel: Need to reference value of another cell which is determined based on value of a different cell, cell b1, inventory worksheet

cell b1, inventory worksheet, cell c1: Hi Stacey, Certainly! You can use VLOOKUP Function. I will pretend that you have a sheet names Inventory and this is where the database of information is. I will assume the information is stored in cells A1:D10. Then on the Wrapup worksheet...

Excel: Need to scale a trendline in Excel, code solution, linear trend

code solution, linear trend, negative trend: HI Ross I ll be direct I can t help here. YOur data is your data. So if what you have given me is real data, say for example percentage return on investment or some such thing, then thats your data and thats your trend. What ever this data represents...

Excel: Need more space per cell in Excel, memo field, database program

memo field, database program, microsoft access: First of all, why do you think you need to put more text than that into ONE cell? That s a lot of words in one cell in my opinion. Why not split up the text? Access is not a spreadhseet but a memo field in one record can hold approx 65,000 characters....

Excel: Need more space per cell in Excel, database program, microsoft access

database program, microsoft access, best of luck: Will, I don t know the other programs well enough to say that one has the ability to accept more characters in any one cell. I ve heard that there is one, and it may be Quattro or OpenOffice, but I don t remember. I would encourage you to think outside...

Excel: Need to split a field in an Excel spreadsheet, cell b2, excel spreadsheet

cell b2, excel spreadsheet, spreadsheets: In cell A2, enter: 123 Main Street In cell B2, enter: =FIND( ,A2) this is the location of the first blank in the address in A2. We must assume that the house# is at the start of the address, and a space separates the house# and the street name....

Excel: Need VBA Code for Copying Last Column, code references, data entries

code references, data entries, cells: This is too complicated for me to understand, but here are some basics. If you ve got headers starting in cell A1, B1, etc, and you want to find the last column with data, you use code like this: Dim lastCol As Range Set lastCol = Range( a1 ).End(xlToRight)...

Excel: Need VBA code for Macro, copy paste, macros

copy paste, macros, cells: Sorry, I don t know what you need or want to know. You said ...The only difference is that the code needs to reference some counter at the end of data to signal where to copy and insert the new column between the copied column and the counter... Well,...

Excel: Need VBA Help with Chart Dilemna, sheet1, vba code

sheet1, vba code, lmt: You would use SeriesCollection.Add after you have set up the chart. Here is the extract from help: Adds one or more new series to the SeriesCollection collection. expression.Add(Source, Rowcol, SeriesLabels, CategoryLabels, Replace) expression...

Excel: Need VBA Help in Copying Range Values, run time error, range values

run time error, range values, project1: Hi Ken, The brackets [] are just shortcuts for VBA. They aren t official code, but everyone uses them anyway. Unfortunately, they have their limitations as you found out. They can be used to reference a range in the same workbook that the code is in,...

Excel: Need VBA ToolBar Help Please, blah blah blah, tbar

blah blah blah, tbar, custom toolbar: Hey Ken, I m doing well..how are you? Sorry for the delay in my response. I think this solution might help you... After the last End With and before the End Sub, add this code: With TBar .Height = CommandBars( ABC ).Height * 2 .Width = 40 End With...

Excel: Need vba code, b cells, b cell

b cells, b cell, bar 8: Hi Jacqueline, Writing a code for this is very easy and it will be a 4 line code. But I personally feel, it is too easy a task to be done by a MACRO (vb Code). Basically the steps would be - (1) Select B1 (2) Press Ctrl-Down Arrow (3) You would be at...

Excel: Need vba code, b34, jacqueline

b34, jacqueline, formatting: Hi Jacqueline, I recommend this code. It uses the UsedRange property of the sheet to determine how many rows beyond the last populated cell in column B to delete to ensure no data or formatting exist beyond that cell. Dim LastBrow As Range ...

Excel: Need to view a worksheet in an XLA file, nature of the beast, visible property

nature of the beast, visible property, excel workbook: It s the nature of the beast. It s not that it s an xla file (any extension is fine), it s that it was designated to be an add-in (the isaddin property). That being said, you simply can t have it be an add-in. It can be a regular workbook with everything...

Excel: Needing to Round Currency to 2 digits in a workbook, menu tools, decimals

menu tools, decimals, a2: This option can lead to some calculation deviations, but it is fast: Format all the currency figures as currency with two digits, and click the option Precision as displayed in the menu Tools- Options- Calculation tab. This will affect all the values in...

Excel: Negative numbers, negative numbers, b1

negative numbers, b1, cells: Hi Tracy, For my example, assume that column A has the numbers (both positive and negative) and that your formula is in column B. This is what you would type in column B1: =IF(A1 0,0, ) You can drag this formula down the column for all your cells....

Excel: Negative 'parenthesis', format cells, regional settings

format cells, regional settings, negative numbers: Brian Yes the display is a function of how the cell format is set. To change the format, paint the cells you want to format, then go to--- toolbar/format/cells/select the number tab on the format cells window that appears, then select number, then...

Excel: Nested If/CountIF?, array formula, mark taylor

array formula, mark taylor, bm: Hello Mark, Yes, there is a problem with your implementation. You can t use entire columns with this type of formula. It is basically an array formula, but sumproduct doesn t need to be array entered - however it still has the limitations of any other array...

Excel: Nested IF's in Excel Macro, master spreadsheet, cell c2

master spreadsheet, cell c2, vlookup: No need for a macro. Set up a two column range like this: Scores Letter 9 J 10 I 20 H 50 G 100 F 200 E 500 D 1000 C 2000 B 5000 A And then use this formula to find a letter belonging to the number in cell C2: =VLOOKUP(C2,$A$2:$B$11,2,TRUE...

Excel: Nested equations, excel function, case structure

excel function, case structure, excel 97: Justin, You are correct that Excel (by itself) only allows 7 nested If statements. There are several workarounds, though. Here are the first two that come to mind. 1. Consider listing the inputs and outputs in a table and using a VLOOKUP statement (built...

Excel: Nested IF with AND and an OR Function, parens, correct answer

parens, correct answer, g8: The last set of closing parens at the end needs one more closing parenthesis BUT that does not mean that is where it SHOULD be to make the formula return the correct answer. That just means at the end is ONE place where another closing parenthesis CAN be...

Excel: Nested Functions, mileage range, range names

mileage range, range names, excrutiating: Follow along with this sample, and it will show you the technique you ll need to solve this and similar issues. In cells A2:A4, enter: 0 70000 110000 Then in cells B2:B4, enter: 0-69,999 70,000-109,999 110,000+ Now select range A2:B4 and choose...

Excel: Nested functions, mileage range, array formula

mileage range, array formula, vlookup: Theresa, Hope you are doing well. One possibility is: 1. Use cells H2:J19 to enter the data as in Column H - the year (2002, 2003 etc) in Column I - the number after the sign - 0, 69999, 109999 etc in Column J - the text you want to appear Hence,...

Excel: Nested IF functions, douglas m smith, highest score

douglas m smith, highest score, high score: Hi Andy, As you re discovered, you can only nest seven IF statements, however, since you have your data in a table, you can use other functions to retrieve the information. Something like this will retrieve the name and high score respectively: =INDEX($C$4:$C$14,MATCH(MAX($L$4:$L$14),$L$4:$L$14,0),1)...

Excel: Nested IF'S, ogilvy, af17

ogilvy, af17: New Updated formula: =IF(YEAR(TLPeriod) 2004,IF(YEAR(TLPeriod)=2008,AF17, IF(YEAR(TLPeriod)=2007,AF16, IF(YEAR(TLPeriod)=2006,AF15, IF(YEAR(TLPeriod)=2005,AF14,0)))),IF(YEAR(TLPeriod)=2004,AF9, IF(YEAR(TLPeriod)=2003,AF8, IF(YEAR(TLPeriod)=2002,AF7, IF(YEAR(TLPeriod)=2001,AF6,0)))))...

Excel: Nested IF Statement Regarding #Value! Cells, r100, value error

r100, value error, spreadsheets: Couple of observations I am not aware of a formula that will hide a row or column. =if(istext($r100), show , 0 ) Results in the 0 being text, if instead of 0 you use +0 in the formula it should result in a number and eliminate some of your errors....

Excel: Nested IF Statements, cell a1, aidan

cell a1, aidan, a3: Not easy to answer without more details - Possibly multiplying the values by a given number to arrive at a set of unique values and using choose could do it? My email is aidan.heritage@virgin.net - a follow up with attachment can be sent that way if it...

Excel: Nested If Statements to Check Multiple Conditions, vlookup formula, vlookup function

vlookup formula, vlookup function, c15: I don t normally use nested IF statements, they are normally hard to create and maintain. I would propose to use the VLOOKUP function, creating a table that will contain the ranges that you want to code. In this case, I have used the range A1:B5 on sheet2,...

Excel: Nested IF Statements, vlookup function, chris answer

vlookup function, chris answer, refence: That is the point of the fourth parameter of VLOOKUP. When the parameter is FALSE, the function works as you describe, looking for exact values. When it is TRUE, it looks for ranges, so in your case, if you craft the lookup table as I stated in my previous...

Excel: Nested IF's, g2, g7

g2, g7, g6: Try this formula =SUM(G2:OFFSET(G1,E2,0)) NOTE that this assumes you have a value in E2 - otherwise it will error, but you can handle that with an IF statement. You might also want to look at the indirect function as this could also be used to get the...

Excel: Nested IF's, chip pearson, ogilvy

chip pearson, ogilvy, g2: Yes, you are just using the else side of the if statement. You can divide you problem into two halves and make an initial branch if(E2 4, (handle 1 - 4), (handle 5 - 7)) When I say handle, I mean write nested IF statements, but the total nesting...

Excel: Nested IF statement, spreadsheet, r2

spreadsheet, r2, suggestion: Put this in R2 & fill down: =CHOOSE(MIN(IF(AND(P2 =60.1,P2 =62.8),1,IF(AND(P2 =58.5,P2 =64,),2,IF(AND(P2 =57.5,P2 =64),3,IF(AND(P2 =56.5,P2 =67),3,IF(OR(P2 56.5,P2 67),4))))),IF(AND(Q2 =53,Q2 =57),1,IF(AND(Q2 =52,Q2 =60,),2,IF(AND(Q2 =51,Q2 =65),3,IF(AND(Q2...

Excel: Nested IF statement help!, logic function, colb

logic function, colb, percents: Ryan: I believe I have a solution for you. Instead of a nested IF lets go with SUMPRODUCT. I have uploaded a file here for your review: http://home.swbell.net/nate-sus/excel/percentlookup.xls The formulas you are looking for are in I2 through...

Excel: Nested If statement, time sheets, datetime

time sheets, datetime, support microsoft: Hi Steve, Calculating dates and times is always tricky. I would check out a few websites that explain some basic concepts, problems, ansd solutions: www.cpearson.com/excel/datetime.htm http://support.microsoft.com/default.aspx?scid=kb;EN-US;q214094...

Excel: Nested IF statements vs lookup?, anniversary 15, hlookup

anniversary 15, hlookup, date function: Hello, Andrea, There is a little known date function which might be very helpful to you here. Since I m not sure exactly how your workbook is set up, it might be helpful to see it. Why don t you send me a copy of it? You can send it to kawai54@excite.com....

Excel: Nested If Then statment, lookup functions, table of values

lookup functions, table of values, excel 2000: You cannot change the way the IF statement works, but you don t actually need to - it sounds as though you have a table of values, based on certain criteria- so one of the lookup functions would do the job (probably vlookup - for an explanation of this see...

Excel: Nesting & IF, cell b1, cell a1

cell b1, cell a1, test score: =IF(A1 =723 875,... is incorrect form. It should be stated: =IF(AND(A1 =723,A1 875),... You should consider setting up a table connected the scores to the levels, and then using =VLOOKUP instead of nested IFs. For instance, if you had in range...

Excel: Nesting, sales period, person types

sales period, person types, period 2: Thanks, John, now I understand. Let s say someone enters a date in A1. (I m assuming that the date will always be in 2005.) If so, here is the formula that returns the correct sales period: =IF(A1 DATEVALUE( 5/1/05 ), 2005.p1 ,IF(A1 DATEVALUE( 9/1/05...

Excel: Netscape & Excel, sheet tab, copy option

sheet tab, copy option, e mail: As far as I know, you don t - although a quick way to do it is to right click the sheet tab and use the move or copy option to copy the sheet to a new workbook which you can then email - and of course you could record that as a macro so that you can invoke...

Excel: New to Macros, microsoft access database, blank row

microsoft access database, blank row, counta: I will try to outline the process that I would follow, it will be up to you to make the current coding, as you know better than me the layout of the data and the exact set of requirements. 1. Create a name on the workbook (menu Insert- Name- Define) that...

Excel: New Window - how to undo, option window, excel 2002

option window, excel 2002, menu option: Hi, Doug, You re going to laugh when you learn how easy it is to remove the second window. Do you know how to close a worksheet without closing Excel? You simply click the little X in the top right-hand corner of the worksheet window. That s it! Closing...

Excel: For each Next & Copy Problem, run time error, run time error 91

run time error, run time error 91, simplicity sake: My guess is that in the loop: For Each ws In Worksheets ... With ws ... ActiveSheet.Next.Activate ... End With Next ws when you reach the last worksheet, the statement Activesheet.Next.Activate will no longer...

Excel: For Next Loop, nrow, loop through

nrow, loop through, ogilvy: If the cells are really empty (not just appear empty), then you don t need a loop Sub DeleteRows() On error Resume Next Range( M1 ).EntireColumn.SpecialCells( _ Banks).EntireRow.Delete On Error goto 0 end sub SpecialCells only looks at the Usedrange...

Excel: Next sheet and 10 rows down, horizontal format, vertical format

horizontal format, vertical format, pivot table: My suggestion to accomplish this would be to design your calculations on sheet 2 in a horizontal format, so instead of going down 10 rows, go across 10 columns. If you need to display the results in a vertical format, you could then summarize it using a pivot...