account values, alternate approach, dalgleish: Reagan, Excel can already do this for you. You can create a pivot table (it is under the data menu in Excel 2003 and earier and on the left of the insert tab in the ribbon in xl2007). You would make Customer a row field and account value would...

Excel: Append Worksheet to Summary Sheet, b cells, blank row

b cells, blank row, data sheet: If this is a one off, I d do it manually, if it s an ongoing process then presumably data may already exist - so what has to happen here? The basic macro is simple enough - if a one off, copy all data from data A to combined data, find the first blank row...

Excel: Arithmetic Operations In A String, excel, evaluate

excel, evaluate: You can use an old Excel 4.0 macro function for this. The reference is here, on the Excel tips and tricks section: http://www.ozgrid.com/News/excel-evaluate-formula-VBA.htm From that site, here are the step by step instructions: 1) Select cell B1 2)...

Excel: How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Row), vba code, input sheet

vba code, input sheet, job2: The basic macro is simple enough, BUT I cannot follow the logic - mainly I suspect due to the layout I see at AllExperts - it would seem to be for each record on the main sheet. X=2, Y=3 If the data (BUT WHAT DATA) remains the same then output to Row...

Excel: How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Row), vba code, input sheet

vba code, input sheet, job2: Nadeem This is the way I would solve it. 1 Sort the data by MOP then by contract then by job. 2 Assuming the first column is contract and in column A--add the number of columns that you expect to have jobs for one particular unique contract and mop...

Excel: How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Row), vba code, output cells

vba code, output cells, therow: Sub Rearrange() Dim n As Integer, TheRow As Integer, TheCol As Integer, i As Integer, Cur As Worksheet On Error Resume Next Set Cur = ActiveSheet Application.DisplayAlerts = False Worksheets( OUTPUT ).Delete Worksheets.Add.Name =...

Excel: Assistance in excel, a10, b10

a10, b10, cells: Hi gvkrishnan, For your first question, you can use the following formula: =SUM(IF(MONTH(A1:A10)=4,B1:B10,0)) You will just need to modify the range to suit your worksheet. Also, the 4 is in reference to the month April . You can modify that as needed....

Excel: Automatic Data population, cell c5, lookup reference

cell c5, lookup reference, cell references: Jax, What you are saying isn t possible, unless you have the calculation method set to manual. If you have a sheet named MASTER (rather than sheet 1, as this followup confusedly says), and cell B4 on the MASTER contains 26, then the formula...

Excel: Automatic Event, excel2003, data application

excel2003, data application, excel worksheet: Since you are pulling in data then you should be able to use the Worksheet_Calc method to have the print job run at the interval ou need using the code I provided. Private Sub Worksheet_Calculate() End Sub or try Private Sub Workbook_SheetCalculate(ByVal...

Excel: Automatic population of a cell, automatic population, data validation

automatic population, data validation, cell population: Mittun, The solution depends on if the user has TWO cells in which to enter either a host name or an IP, or only ONE cell. I suppose it is TWO cells, one for the IP and another for the Host name. All you have to do to get the answer to appear...

Excel: AVGIF, excel, array formula

excel, array formula, average: The formula that you have is right, you just need to enter it as an array formula. So type the same as you have: =AVERAGE(IF(A1:A5 0, A1:A5, )) And enter it with Ctrl+Shift+Enter. That way it will only take the negative numbers into the AVERAGE formula....

Excel: Average Amount of Product Used, logical formulas, two tables

logical formulas, two tables, widgets: Randy, The best way to do this is to keep two different tables, one for USEAGE and one for ADDITIONS. Then you can find the average of the USEAGE table. You could use these two tables to create a third, ACTUAL INVENTORY, which would be the combination...

Excel: Averaging data, example benzene, countif

example benzene, countif, sumif: You didn t specify why this had to be done in VB - I d do it with a pivot table as that would do the job in one hit. IF it has to be done in VB or any other formulaic method, then it s basically a countif and a sumif - both of which exist in VBA as worksheetfunction....

Excel: Averaging populated cells, array formula, sampling dates

array formula, sampling dates, ogilvy: Pete, You din t alter the formula quit correctly. I used =AVERAGE(OFFSET($H$1,LARGE(IF(ISNUMBER($H$10:$H$374),ROW($H$10:$H$374)),3)-1,0,365,1)) I entered through Jan 4 and it returned 1.33 (1 1 2) I then entered the date for Jan 5 and it returned...

Excel: array question, single quote, h7

single quote, h7, ogilvy: Joseph, Having those cells formatted as text are the source of the problem. You can enter your values as 7-4 and not format the cell(s) as text. The single quote is considered a formatting character by Excel and the other characters are treated...

Excel: New question., porcelain china, countif

porcelain china, countif, product 01: Ray, I m sorry but the finer points of your new question elude me. Your image might help, except the resolution is so low that I can t read any of the text you so carefully wrote by way of explanation. Even when I zoom in on the image its fuzzy. ...

Excel: auto numbering unique names, unique names, header value

unique names, header value, ogilvy: Nick Assume the first nick is in A2 and the rest below, sorted as you show. then in B2 put in the formula =if(A2 A1,b1+1,B1) Now select B2 and drag fill down the column. If your list does start in row 1 with no headers, then put a 1 in B1 and...

Excel: automatically moving data to another sheet, target value, target address

target value, target address, copy worksheets: Jessica, Right click on the sheet tab where you will be entering date and select view code. in the resulting code module, from the left dropdown at the top select worksheet and from the right dropdown select change (not selectionchange) that will...

Excel: Balance per month, selection functions, month of august

selection functions, month of august, current date: Peter, To find the current month of NOW() or any other date, simply use the MONTH(cell) function, where the cell contains a valid Excel-type date serial number. Such as, Month(A1) where NOW() is in cell A1. You could also use =MONTH(NOW()), but this...

Excel: BUDGET, lloyds bank, cell reference

lloyds bank, cell reference, saving accounts: IF I understand what you are asking then use an absolute cell reference in your formula. So, if you type =A1+$F$1 in C1, for example, and copy it down one row then that cell have =A2+$F$1 as its formula. Copy it to D1 and that cell will have =B1+$F$1 as...

Excel: Calculate sums of random numbers, dim mark, mark case

dim mark, mark case, rng: Tim, You are looping 10 times per cell (so your are generating 100 numbers) - I assume you only want to generate 10 numbers in cells A1 to A10. this worked for me: Public Sub CommandButton1_Click() Dim rng As Range, i As Integer Dim mark, sumFail,...

Excel: Calculations, absolute references, relative reference

absolute references, relative reference, pivot table: Suzette, Use the SUMIF function. SUMIF can take two ranges and a criterion value. =SUMIF(range-to-check-for-criterion,criterion-value,range-to-sum) You need to create a list of unique names where the names appear only once. (If you don...

Excel: Calculations, array formula, cell approach

array formula, cell approach, excel works: Ron, That is the way Excel works. Formatting just determines how the cell is displayed. Underneath, the value in the cell hasn t changed, and when you do your sum, it uses the value; it doesn t use the displayed amount. There are two ways you can...

Excel: Calculations in Excel, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: This sounds like a rounding error. Make sure you re showing enough digits after the decimal points in your column. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Cell above for formula, indirect address, hi matthew

indirect address, hi matthew, b13: Hi Matthew, If I am understanding your question correctly, you want to find the number of days between today and the date in the cell above. If this is correct, please try this formula: =TODAY()-INDIRECT(ADDRESS(ROW()-1,COLUMN())) I hope this answers...

Excel: Cell Shading, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, cell shading: Excel has a feature called Conditional Formatting that you can use to change the format (color, font, bold, etc.) based on the value of the cell. See this FREE video tutorial on my web site for instructions on how to use Conditional Formatting. http://599cd.com/tips/excel/conditional-formatting/?key=AllExperts...

Excel: Changing Chart Based on Value Selected, date selection, indirect method

date selection, indirect method, aidan: You can do it with indirect, but you would EITHER need to use VBA to reset the chart range OR have a charting range within the worksheet that picked up it s data based on the selection. From the description, it does sound as though sheet2 is the charting...

Excel: Charting data, numerical division, displaying your data

numerical division, displaying your data, priority level: Margaret, You may have already solved this by now, but in case not, here is a response that may (or may not!) help. First of all, I d suspect your question should read: ....I want the chart to have the hit and miss for each DIVISION displayed...

Excel: Charts, sheet tab, single store

sheet tab, single store, answer right: Sorry, I took the reference to pages and sheets to refer to worksheets for pages and workbooks for sheets. IF you are moving the data to multiple locations within one sheet, then you are pretty much going to have to do it as you are doing at the moment -...

Excel: COUNTIF, sumif, ogilvy

sumif, ogilvy: sharmane, assume your data starts in row 1 and ends in row 204, then =sumif(I1:I204, 40 ,F1:F204) should give you the total of the prices in column F if the age is less than forty in column I. Change the 204 to reflect the last row with your...

Excel: Code for Upper Case in excel, proper case, case change

proper case, case change, upper case: dim cell for each cell in range( A:A ) if len(cell.value) 0 then cell.value=ucase(cell.value) next gives upper case - change Ucase to lcase for lower case There isn t any direct function for Proper case, but for each cell in range( A:A ) ...

Excel: Column headings, excel, dates

excel, dates, freeze panels: 1. You can use the menu Window- Freeze panels for that. Select a cell, go to that menu option, and the cells to the top and left of that cell will always be visible for that sheet. 2. You can check the custom lists available on the menu Tools- Options- Custom...

Excel: Combinations, credit card statements, statement period

credit card statements, statement period, mehta: It is possible, and it s a fairly common question - so rather than re-inventing the wheel, I ll refer you to http://www.tushar-mehta.com/excel/templates/match_values/index.html where there is a discussion about this that HOPEFULLY will provide what you...

Excel: Command in a Macro to Select a Cell, value contents, m11

value contents, m11, macro recorder: Stan, Cells(rows.count, G ).End(xlup).copy cells(rows.count, M ).End(xlup).PasteSpecial Paste:=xlValues should do it. if you need to work with either of those locations again in the same code, then use a reference Dim rG as Range, rM as Range...

Excel: Compare part of strings in a table against a list of Keywords, master reference, keyword list

master reference, keyword list, great solution: Nagesh, This is a very complex question, but some time ago another questioner gave me a solution which worked just like you describe! I haven t been able to find his result, yet, but I have it somewhere. if you still need an answer to this, PLEASE...

Excel: Comparing and Sort/Extracting Data, error prevention, excel function

error prevention, excel function, customer number: Jef, The best way is to use VLOOKUP to find the matching customer name from one table to the other. Once you know how to find the matches across tables, you can lookup information from one table into another. You can either build a new third table...

Excel: Completing a contract database, negotiation stages, database range

negotiation stages, database range, apologies in advance: Ollie, lastrow = worksheets( Database ).cells(row.count. B ).End(xlup).row + 1 Sheets( New Contract ).Activate Sheets( Database ).Range( B & lastrow &:F & lastrow).Value = ActiveSheet.Range( D5:H5 ).Value Sheets( Database ).Range( G & lastrow).Value...

Excel: Conditinal Formatting, room booking system, mutiple areas

room booking system, mutiple areas, excel 3000: Hi James, You could probably achieve the solution you want by creating a macro, but I was not sure how comfortable you are with using macros? Also, there is a shareware program that merges cells that is available at download.com: http://download.cnet.com/Merge-Cells-Wizard-for-Excel/3000-2077_4-10289817.html?tag=mncol...

Excel: Conditional Formatting, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, question sign: To the best of my knowledge, you can only use the built-in color and icon sets in Excel with conditional programming. The exception, of course, is if you code something yourself using VBA. Or… you could use an IF statement or a VLOOKUP and insert your...

Excel: Conditional Formatting - Training Matrix, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: See this tutorial. Perhaps it will help you: http://599cd.com/tips/excel/extend-conditional-formatting?key=AllExperts Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: Conditional formating, cell a2, cell a1

cell a2, cell a1, please follow these steps: Hey Ian, Please follow these steps to solve your problem: 1 - Select cell A2 2 - Click on Format on the top menu, then Conditional Formatting 3 - Select Formula Is in the first dropdown 4 - In the next box type this formula: =ISBLANK(A1) 5 - Click...

Excel: Conditional formatting, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Sure, you could use VBA to read the format of one cell and use it to change the format of another. I don t have the code handy (nor know it off the top of my head) but it is certainly possible. Let me know if you have any other questions or comments. ...

Excel: Conditional formatting, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, vba code: You re going to need some custom VBA to accomplish this. You could EASILY just create a third column that would show a value if the first two columns didn t match. Just use a simple IF function: =IF(A1=B1, , X ) That would put an X in C1 if A1 was...

Excel: Convert Text to Number, mobiletel, column c

mobiletel, column c, allexperts: Ross, I provided a change event in response to the question you cite. I only adapted the code the questioner provided to work as an event. I can t say what it actually does and I would be surprised if it does what you want. Do you want a macro where...

Excel: Converting my formula to calculate time (Overtime spreadsheet), excel, time

excel, time: If the number is in time format, you may need to convert it to hours first. You can try with something like: =IF(I25*24-40 0,0,I25*24-40) This will give you the hours, but they cannot be formatted as Time directly. If you want the result to be formatted...

Excel: Converting Letters, lt 1, question option

lt 1, question option, code structure: Function MyCode(rg As Range) As String Const Codes As String = ABCDEFGHIJ ========change according to your code scructure (In my excel file, I have already changed it according to our code structure) Const Corresp As String = 1234567890 ========each...

Excel: Converting Letters, lt 1, excel spreadsheet

lt 1, excel spreadsheet, instr: It s a lot easier to use a user-defined function. Press Alt/F11, use Insert/Module, copy this in: Function MyCode(rg As Range) As String Const Codes As String = HIGABCDEFJ ========change according to your code scructure Const Corresp As String...

Excel: Copy Cell Content to another cell based on the Status of another cell in the same row, relevant record, cell content

relevant record, cell content, status column: Varun, The portion of your question which makes it very difficult is this: [I have abbreviated your question and emphasized the significant portion] I would like to .... copy the order# and paste it .... WHENEVER A USER selects the status as...

Excel: Copy Cell Formatting Using Vlookup, master timetable, staff column

master timetable, staff column, vlookup function: I presume the colour information is more complicated than would be allowed by conditional formatting, so yes it would use VBA - I wouldn t use the match function, I d simply check each cell in the range and if it s value was the value you wanted then the data...

Excel: To Copy File And Folder Names, folder object, folder names

folder object, folder names, filesystemobject: I cannot get the code as copied to run, which makes it difficult, but as far as I can tell the important bit is If FullPaths = True Then Dest.Value = OneFile.Path Else Dest.Value = OneFile.Name End...

Excel: Copy Rows of Data Into Master Sheet, master spreadsheet, excel vba

master spreadsheet, excel vba, xls extension: Rhonda, I tested this and it worked for me - at least it worked as I expected. I assume any files with an .xls extension in one of the subdirectories is to have the first sheet in the workbook copyied to a sheet Named Data in Master.xls in the next available...

Excel: Copy From One Sheet To Another, pastes, ogilvy

pastes, ogilvy, thanks in advance: Tom, If you insist on a macro, then I can t help you. I don t offer macro/VBA advice on this website. You ll have to ask an expert who does not say in their profile (as mine does) , NO MACRO QUESTIONS!, but rather, look for an expert who say he has...

Excel: Copying Cells to a close workbook, run time error, sheet cells

run time error, sheet cells, template sheet: Steven DATABASE_RECORDS = DATABASE_SHEET.Cells(Rows.Count, A ).End(xlUp)(2) should be Set DATABASE_RECORDS = DATABASE_SHEET.Cells(Rows.Count, A ).End(xlUp)(2) I tested this and it worked for me: Private Sub CommandButton1_Click() Dim...

Excel: Copying cells to a closed workbook, template sheet, documents and settings

template sheet, documents and settings, b count: Steven, Sorry - I guess I got myself confused when I was restructuring the code. Template_Sheet is a reference to the sheet, not a variable holding the name of the sheet which is what my code would need. So it should be Template_Sheet.Range( c12...

Excel: Copying Data and Eliminating Blanks, selection functions, record selection

selection functions, record selection, autofilter: Russell, There is nothing like over-simplification to make a question (like your original) impossible to understand. Now I get it. The first thing to do is fix your data table so there are no blanks in column A, where the name is. Do do this,...

Excel: Copying Data from sheet to sheet based on condition, target value, type mismatch

target value, type mismatch, cells: Your code is wanting a single cell to be selected, but when copying and pasting multiple cells are in the selection. In this instance, the QuICKEST and easiest fix is a catch-all error trap - make the first line of the code (after the sub...) On Error...

Excel: Copying data speci, input flow, inputbox

input flow, inputbox, excel sheet: Would the date column always be in a given location, or would the macro have to work out which column to use? - the process is easy enough to write, indeed it s fairly easy to do manually - data, filter, autofilter, select the date column, choose custom and...

Excel: Copying formulas with an odd increment, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, cell reference: If you create enough cells so that Excel can see your pattern, you should be able to AUTOFILL them down. For example, create A1 through A6 which should be enough. Now HIGHLIGHT A1 through A6 and then AUTOFILL down. I just tested it, and it worked fine for...

Excel: Count Cells Containing a Date in the Past, count cells, array formula

count cells, array formula, katie c: Katie, Just to add - if you have Excel 2007, Microsoft added a new function in that version called COUNTIFS (with an S on the end) that allows multiple criteria: =Countifs(Katie!C:C, &$B4,Katie!D:D,$C4) ==================== end of revision ==============...

Excel: Count of the number of formatted cells, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: I don t believe that any built-in function like COUNTIF will tell you the color properties for a cell. You d need to use VBA code for that. Here s a tutorial I wrote about conditional formatting that will show you how to set the color info in a cell. It s...

Excel: How to Count the unique cells from a set of Duplicates , using VBA ?, john walkenbach, chip pearson

john walkenbach, chip pearson, contract name: Nadeem, You don t need code. See the formula on this site: (Chip Pearson s site) http://www.cpearson.com/excel/Duplicates.aspx see the section: Counting Distinct Entries In A Range if you want code, use the code at this site: (John Walkenbach...

Excel: How to Count the unique cells from a set of Duplicates , using VBA ?, mail id, contract name

mail id, contract name, richard florida: Nadeem Unfortunately the COUNTIFAND function doesn t work in excel, so what I usually do is combine my criteria and then count the occurences of the combined criteria. Your problem has another twist in that you want to count only the unique occurences...

Excel: How to Count the unique cells from a set of Duplicates , using VBA ?, table contract, contract name

table contract, contract name, code id: Your explanation is a little hard to follow, and I’m not 100% sure why this needs to be done via code – I’d be INCLINED to get the code to generate a pivot table (or refresh an existing one) which would make the grouping a lot easier – the code could work...

Excel: Countifs, cell d1, cell c1

cell d1, cell c1, array formula: Say that in A1:A10 you have the Male/Female indicator, each of these cells contains either the letter M or the letter F. In B1:B10 you have the corresponding ages. In cell D1 (or any cell), type: =SUM(IF(A1:A10= M ,IF(B1:B10 29,IF(B1:B10 36,1,0),0),0))...

Excel: Counting comma delimited items in numerous cells in one column against another identifier (zip code) in another., comma delimited, array formula

comma delimited, array formula, relational tables: Well, you could use the FIND function to see if each search term is in each description… but you d have to do one column for EACH term. Is there ANY way I could talk you into redesigning this as a database? You d need one record per need but in the end,...

Excel: Creating a Graph in a new tab without values, creating a graph, chart wizard

creating a graph, chart wizard, move option: Kevin, As you work through the Chart Wizard in Excel 2003, you ll see an option to Insert the chart you re making on the same page or on an entirely new page. Select the entirely now page. If you are using Excel 2007, after you insert the chart,...

Excel: Creating Hyperlinks to Folders, creating hyperlinks, logical names

creating hyperlinks, logical names, folder name: You will have to write a macro and even then the writing of the macro may not be as quick and easy as it could be depending on how the folders are arranged, how they are arranged, their logical names, if applicable, etc. You may have to write 100 lines of...

Excel: Cumulative total, cell b1, vba macro

cell b1, vba macro, stock purchases: Juergen, All you have to do is create a simple formula in column B which adds the number in the cell above where the formula is, to the number in the cell to the left of where the formula is. If you type this formula in cell B9, it will look like: ...

Excel: Custom menus in 2007, flyout menu, custom menus

flyout menu, custom menus, macro language: I’m not sure what you mean by flyout menu, and I’m slightly worried by the use of XL4 language, which I take to be the macro language from Excel 4 – which was superceded in Excel5 by VBA (which would still be compatable) – if I could see the file I might be...

Excel: How to calculate ratio in excel?, cell references, excel 2000

cell references, excel 2000, gdp: not sure what you mean by GDP feature, but I assume by ratio you mean one value divided by the other - at which point, that is exactly what the formula is =FirstValue/SecondValue where FirstValue and SecondValue relate to the cell references you want...

Excel: calculating Voids on a weekly basis using Excel 2003

Kerith, I noticed your comment but you can write a followup to ask for clarification, instead of sort-of asking a question in the comment area. All you have to do is create a cell whose contents LOOK LIKE the text string that a criteria would look...

Excel: calendar, mr abc, ms alexander

mr abc, ms alexander, oracle 9i: Bhavana, Your question is a little difficult to understand because the attempt you made to create a sample of your data within the question has become all scrambled. I can t tell what the data is, what column its in, or what. Attaching an image of...

Excel: cell input & alphabetization, target address, sheet tab

target address, sheet tab, controls group: XL 2003: Right-click any toolbar button, select Forms, click on the button icon and draw a button onto the worksheet, when you let go of the mouse for drawing you will see a dialog about which macro to run, so point it to MoveIt (from the macro you put in...

Excel: changing direction, move selection, linear fashion

move selection, linear fashion, excel spreadsheet: David, In excel 2003 and earlier, you would go to tools= Options and in the Edit tab it has an entry Move selection after Enter You have two choices. DOWN and RIGHT In Excel 2007, if you click the Office button, at the bottom of the Frame should...

Excel: combining data in Excel, cell a2, cell b2

cell a2, cell b2, cell a1: Example: on Sheet1, cell A1, is the heading Customers . Below it, beginning in cell A2, is the list of Customers. On Sheet2, cell A1, is the heading Materials . Below it, beginning in cell A2, is the list of Materials. On sheet3, cell A2, enter: ...

Excel: compare dates and conditionally format, logical statement, last date

logical statement, last date, spreadsheet: Chris, From the title of your question, it sounds like you already know how to do this: use Conditional Formatting. I can t give you a complete tutorial on conditional formatting here; I wonder what aspect of the application of Conditional Formatting...

Excel: comparing content in a cell range, e mail addresses, excel 7

e mail addresses, excel 7, dear bill: Elke, Use COLUMN C to create a COUNTIF equation. Each COUNTIF will take as input the adjacent email address from column A, and will count the number of times it occurs in column B. Any row containing a COUNTIF value 0 has an email address from A...

Excel: comparing and deleteing multiple rows and columns, autofilter, column c

autofilter, column c, header data: Brad, first copy the sheet and work on the copy. Assume your data is in Column A, starting in row 2. Assume the unique identifier for the contact (whatever it is) is in column A as well I would insert two new columns then I would go to the new...

Excel: comparing two excel spreadsheets, excel spreadsheets, spread sheets

excel spreadsheets, spread sheets, multiple days: I don t understand the part about identify a donation... There s no really good way to compare spreadsheets unless they re truly practically identical. Best bet: on a 3rd sheet cell A1, enter: =IF(Sheet1!A1=Sheet2!A1, , X ) and fill right & down. Wherever...

Excel: complex formula, aidan, attachments

aidan, attachments, heritage: Would you like to be a little more specific please - I have nothing here to base an answer on other than =if(Yourtest=true, Decrease , Increase ) where yourtest is whatever formula you want to use to evaulate the decrease. Should it help, you can...

Excel: conditional formating for dates, conditional formatting, lol thanks

conditional formatting, lol thanks, formating: select all the cells with dates that are to take on this formatting. Conditional formatting has up to 3 conditions you can enter. Use Format/Conditional Formatting, change Cell value Is to Formula Is , assuming A1 is the ACTIVE cell, put in this formula...

Excel: conditional formatting, conditional formatting, blank cell

conditional formatting, blank cell, attis: The problem as I see it is that your duplicated rows could presumably occupy both an odd number or an even number of rows, so the original condition would not work alongside the newer one. I would be INCLINED to do this with a formula in a blank cell, which...

Excel: conditonal formatting, array formula, array formulas

array formula, array formulas, conditional formatting: Not sure I understand now. Row 1 should be one color, row 2 should be a different color, row 3 should be another different color? How many rows are to be used to then determine WHICH row gets highlighted? Give me some specific examples of what should be...

Excel: convert four-digit number to military time, little known fact, military time

little known fact, military time, time int: Virginia, You need to break the number in half (Hours & minutes), and then use the TIME() function to reassemble the halves into a serial number that Excel recognizes as a time. Start by dividing the number by 100. In your example, you ll...

Excel: To copy file and folder names, increment letter, xdirect

increment letter, xdirect, folder names: Probably not the code I d use, but Sub GetFileNames() Dim xRow As Long Dim xDirect$, xFname$, InitialFoldr$ xRow = 2 InitialFoldr$ = c: emp Startup folder to begin searching from With Application.FileDialog(msoFileDialogFolderPicker)...

Excel: copy to new worksheet and keep formulas?, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: My apologies. I probably should have stated that you have to have that file OPEN in the current Excel session. Open your source file. In the SAME Excel Window, click on Office Open and open the destination file. Now go back to the source file and...

Excel: copy range between 2 workbooks, change column

change column: Make the changes as indicated, if necessary: Sub CopyCol6() wb1= Workbook1.xls ====change name wb2= Workbook2.xls ====change name CopyCol = 6 ====change column For i=1 to 10 SheetName = Workbooks(wb1).Sheets(i).Name...

Excel: cost comaprison worksheet, horizontal bar graph, colc

horizontal bar graph, colc, color choices: Nick, How do you want the difference in price to be color-coded? Do you mean that if vendor 2 vendor 1, you want to see one color, and if Vendor 1 vendor 2 you want to see a second color, and if they are the same you want to see a 3rd color? ...

Excel: countif not working, true values, double quotes

true values, double quotes, good solution: Amy, You ve come up with a real puzzler. It seems like what you ve done should work and it doesn t. I haven t been able to make it work either! Excel must be getting confused, because both the and the % characters are OPERATORS. Excel seems...

Excel: creating a total hours column, monday tuesday wednesday, array formula

monday tuesday wednesday, array formula, blank cells: Joseph, Assume the values are in B7:H7 then =SUM(IF($B7:$H7= off ,0,CHOOSE(MATCH($B7:$H7,{ 7-4 , 4-9 , 12-6 , 6-2 },0),9,5,6,8))) entered with Ctrl+shift+enter Add any additional possible shifts to the list inside the brackets {} and the corresponding...

Excel: cross reference 2 sheets, excel spreadsheets, vlookup function

excel spreadsheets, vlookup function, matching company: Steve You can use the VLOOKUP() function to find the matches and isolate those that do match. If I am reading correctly, you want to match the company name in column A of sheet 2 to the company name in column A of sheet 1, and if there is a match you...

Excel: cutting and pasting, hot key, hotkey

hot key, hotkey, cells: Any form of hot key would probably take longer to do than using the mouse - right click on the letter at the top of the column and select cut, then right click on the letter at the top of the destination column and choose insert cut cells - thats two clicks...

Excel: Data Entry, sheet tab, data entry

sheet tab, data entry, wb: If it s in the same cell as the main sheet, you can right-click a sheet tab, select Select All Sheets , and then whatever you do in one sheet will be done in all the sheets. When you re done, right-click the sheet tab & select Ungroup Sheets If it s...

Excel: Date Calculation, cell a1, cell b1

cell a1, cell b1, spreadsheet: Hi Denise, If the date is in cell A1, and the shipping date is to be in cell B1, you would need to enter this formula in cell B1: =A1-30 Excel will automatically calculate 30 days prior using this formula. You can drag this formula down the entire...

Excel: Date change in Excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: I haven t used Windows 7 yet, however, it sounds like something that you d set in the REGIONAL SETTINGS for Windows. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Date change in Excel, excel function, excel sheet

excel function, excel sheet, win 7: Stig, This obviously has something to do with the new O/S you installed. It s probably NOT an Excel function, but there may be a setting in the OPTIONS box for international settings. Hunt around in the OPTIONS box (found from the Office Button in...

Excel: Date Range match, digit number, relevent

digit number, relevent, l5: a date in July 2009 is ACTUALLY a 5 digit number in the 40,000 range from memory- as your dates are only this year, you could use the MONTH worksheet function =if(month(YourCell)=7,YourMatch, ) would be the sort of syntax to use - I hope this helps,...

Excel: Date & Time Difference, timecell, optional range

timecell, optional range, days between two dates: I m not sure about your maths - 6pm to 1pm is 19 hours - but apart from that the basic method should work fine - see http://www.aidanheritage.byethost3.com/excel/Networkdays.xls where I have created a formula for you - to keep it vaguely clear this doesn...

Excel: Decimal Places, file open dialog box, csv file

file open dialog box, csv file, keyboard shortcut: No, not without seeing the csv file. But, you can try opening the workbook using Workbooks.OpenText Filename:= method. This should give you a little more control over the file and then since a workbook is now open you should be able to do the converting...

Excel: Delete entire column if one cell in the column meet conditions, count column, circular reference

count column, circular reference, ogilvy: CK, yes, the range should have been =countif(A2:A5000, *ID* ) A simple typo. If you want a macro, then you should state that as I say in my profile. I have no idea what you want unless you tell me Sub DeleteColumns() Dim r As Range, i...

Excel: Delete Row with Specific keywords, sirajuddin, rng

sirajuddin, rng, macro code: Sirajuddin, Sub DeleteRows() Dim rng As Range Do If Not rng Is Nothing Then rng.EntireRow.Delete End If Set rng = Cells.Find(What:= china , _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext,...

Excel: Deleting drawing object using VBA, rectangle, shapes

rectangle, shapes, drawing: I m not sure I d want to do this with VBA, but ActiveSheet.Shapes( Rectangle 1 ).Delete where Rectangle 1 is the name of the shape, would delete the shape - BUT putting a new shape in place would then cause it to have a new name, so the macro would not...

Excel: Dependent list with criteria, choice c, option c

choice c, option c, columna: Kostas, There is a method to make dependent drop down lists, but I don t think it can be done the way you want. For each choice you make in C1, you have to precisely define the dependent list, by creating the dependent list in cells somewhere. I don...

Excel: Drop down list from another sheet, data validation, dalgleish

data validation, dalgleish, menu structure: Klay, Using data validatkion, using the list option? go to Insert= Name= Define Name: List2 Refers To: Sheet5!$A$1:$A$20 Click the add button. When you choose List for the type of validation, in the resulting text box put in =List2...

Excel: Droplist, combobox, ogilvy

combobox, ogilvy: Anas, You can use formulas to remove duplicates and built the source for the combobox. Then when you add items you won t have to do anything additional. If you want to send me a sample workbook I can show you how to do it or send me your email address...

Excel: date, conversion algorithm, excel function

conversion algorithm, excel function, next five years: Wolfgang, Well, you may see it that way, but that doesn t mean the creators of Excel provided the inverse WEEKNUM function, because they didn t! Nor did they write a DATE() function variation which uses the WEEKNUM value instead of Year, Month and...

Excel: definition, floating point numbers, vba code

floating point numbers, vba code, variable declarations: Dave, The single and double identify the data type of the variable and how much storage (in terms of memory) needs to be set aside to store the values assigned to the variables. Dim means to dimension the variable. What you show are called variable...

Excel: dependent validation lists with dynamic ranges, cell b2, data validation

cell b2, data validation, cell a2: 3rd time s the charm :-) one minor adjustment, sorry! Fill corrected answer here: Suppose the range with the kids names is named Children . Also, suppose C2 contains the dropdown you want to be able to edit . First, in the Error Alert tab of the...

Excel: dependent validation lists with dynamic ranges, cell b2, data validation

cell b2, data validation, dynamic ranges: Hi Hilde, I think that the dynamic ranges you refered to will help you in your solution. If you use a dynamic name range, the user will be able to add to the name list and the dropdown box will be updated automatically. 1 - Click on Insert on the...

Excel: dependent validation lists with dynamic ranges, cell b2, data validation

cell b2, data validation, cell a2: Hilde, You ve created a very brilliant cross-coupled pair of validation lists! I ve never seen that done before, it s fantastic. It seems that the only problem you have is how to make the third drop down editable. I m sure you ve gotten the...

Excel: different coloum how to protect, gray rectangle, coloum

gray rectangle, coloum, column letter: Suresh Select the entire worksheet by clicking the Select All button (the gray rectangle directly above the row number for row 1 and to the left of column letter A). Click Cells on the Format menu, click the Protection tab, and then clear the Locked check...

Excel: discount, homework question, homework problems

homework question, homework problems, sorry bill: Gail, We re not supposed to hand out the answers to homework problems on this site. I tried to give you a hint as to what to do, without handing you the answer on a silver platter.... so you d learn something. I didn t; mean to be snotty... sorry!...

Excel: drag number with substract, substract, hyphen

substract, hyphen, 000000: Hi Ming, In the first field type the following formula: =TEXT(ROW(A16622), 000000 )& -2009 What this does is sets a starting number and the format, but keeps the hyphen and the following 2009 the same. Drag this formula down to auto fill and...

Excel: dragging indirect formulas down the page, sheet cells, indirect sales

sheet cells, indirect sales, page hi: Andrew: I must admit I m confused by why you would want to design your formula the way you did (I think I would need to see your spreadsheet to fully understand#; however, I believe I have a solution to do what you are asking: =IF#Sales!$J3 0,INDIRECT(...

Excel: drop down list, list of states, parag

list of states, parag, drop down list: Parag, Since this is a fairly long topic to describe, let me refer you to Debra Dalgleish s site where she has written out and illustrated two ways to do it. http://www.contextures.com/xlDataVal02.html http://www.contextures.com/xlDataVal15.html...

Excel: How to Make duplicated column data into a Row using Excel VBA?, excel vba, table contract

excel vba, table contract, contract name: Nadeen: I am having a little difficulty understanding what you want and need. Please send me a copy of your file to my personal email address along with another attempt of explaining what you need and try to include a sample of what you want the macro...

Excel: How to Make duplicated column data into a Row using Excel VBA?, excel vba, table contract

excel vba, table contract, contract name: Your explanation is a little hard to follow, and I’m not 100% sure why this needs to be done via code – I’d be INCLINED to get the code to generate a pivot table (or refresh an existing one) which would make the grouping a lot easier – the code could work...

Excel: dynamic data ..., dynamic charts, cell reference

dynamic charts, cell reference, track eight: Sandy, Yours: =SUM(((INDIRECT($A$1 !$H$18:$H$500 = Foundation ))*(INDIRECT(A$1$ !!$M$18:$M$500 = Cash-In ))*((INDIRECT(A$1$ !$I$18:$I$500))) Mine =SUM((INDIRECT(A1& !$H$18:$H$500 )= Foundation )*(INDIRECT(A1& !$M$18:$M$500 )= Cash-In )*(INDIRECT(A1&...

Excel: Email Notification To Track, target range, mitem

target range, mitem, outlook application: When EXACTLY do you want the email to be sent? When the workbook has been saved or when data has been changed regardless if the workbook gets saved? On reason why it is not working is that no email address has been provided/typed in the code. To make...

Excel: EXCEL 2003 array formula, boa c, array formula

boa c, array formula, insurance payments: DJ, this worked for me in Excel 2003: =SUM((CCCU!$B$14:$B$2316 =39904)*(CCCU!$B$14:$B$2316 39934)*(CCCU!$C$14:$C$2316=21)*CCCU!$E$14:$E$2316+(BOA!$B$14:$B$2316 =39904)*(BOA!$B$14:$B$2316 39934)*(BOA!$C$14:$C$2316=21)*BOA!$E$14:$E$2316) Personally...

Excel: EXCEL, coloum, column feature

coloum, column feature, width option: tathagata The text to column feature should work very well for you. First paint the cells that you are working with, then go to toolbar/data/text to columns A pop screen will appear--on that screen select delimited then select next. On the next screen...

Excel: EXCEL Text Lists, data validation, hlookup

data validation, hlookup, blank rows: Clint, This has been a tough one to figure out but I took another look and an idea hit me... at 3 AM! What you want to do try to find each name from the master list in the calendar itself, and list the names that fail to be found. That s just...

Excel: Excel 07 Compare cells, c11, b11

c11, b11, a11: =IF(A3:A11=C3:C11, X ,IF(B3:B11=C3:C11, Y , Z )) Enter the formula as an ARRAY. That is, after entering this formula into a cell, instead of hitting the Enter key, hit Ctrl-Shift-Enter. The formula will return X if A3:A11 is the same as C3:C11; otherwise...

Excel: Excel 2003, two different places, tenses

two different places, tenses, offence: Craig, I understand a little better but you didn t answer all my concerns. So the best I can do is present you with an IF statement which will occasionally fail to deliver the proper result. =IF(AND(Test1 50,Test2 50,ABS(Test1-Test2) =0.06*Average(Test1,test2),...

Excel: Excel 2003 Average on range if multiple conditions are met, seperate sheet, sumproduct

seperate sheet, sumproduct, summ: Collin, =sumproduct(--(All!$D$2:$D$776 = Summ by Task !$A6),--(All!$B$2:$B$776= Summ by Task !$A1),All!$H$2:$H$776)/sumproduct(--(All!$D$2:$D$776 = Summ by Task !$A6),--(All!$B$2:$B$776= Summ by Task !$A1)) should work entered the normal way. ...

Excel: Excel 2007, conditional formatting, sheet tab

conditional formatting, sheet tab, discrepancy: Eliot, You seem to have gotten most of it to work already, recognizing that Conditional Formatting is the way to go. What you ran into is that conditional formatting (CF) has limitations when working between sheets! So, you need to bring a reference...

Excel: Excel 2007, excel help file, significant digits

excel help file, significant digits, decimal places: Barry, My answer regarding Conditional Formatting will automatically format the number based on its magnitude. This answer is no-good if there is no correspondence between the magnitude of the number, and the number of places you want to see. However,...

Excel: Excel 2007 Drop Down List with images, conditional formatting, data validation

conditional formatting, data validation, text response: I like the idea, but I m not sure how easy it s going to be - data validation will allow the use of any font, so if you have one that has these entries then you could have the cell formatted as that font, and use conditional formatting to set the colour. ...

Excel: Excel 2007 Formula, column c, time expertise

column c, time expertise, hi bob: Russell, My name isn t Bob, so perhaps you sent it to the wrong person. Nonetheless if you put in this formula in any cell not in column C, then it will tell you the row number of the last cell in column C that contains a number: =MATCH(9.99999999999999E+307,C:C,1)...

Excel: Excel 2007: Pivot Table - Data Cube - WhatIf Analysis, pivot tables, excel 2007 pivot table

pivot tables, excel 2007 pivot table, intelligence development: Milton, I don t have enough information to help you with a complex problem like this. It is not working doesn t tell me anything about what s wrong. Please include a much more detailed explanation of exactly HOW it is not working and I will see...

Excel: Excel 97 with Vista os, free microsoft excel, spreadsheet program

free microsoft excel, spreadsheet program, question sign: If you burned those files to a CD they might be marked READ ONLY even if you copy them back to the hard drive. Right-click on each file and go to PROPERTIES. Then uncheck the READ ONLY box. That should allow you to edit them in Excel. Let me know if...

Excel: Excel to Access, Access to Excel, shift report, command button

shift report, command button, access database: It s certainly possible, and fairly easy, to write data to an access database - and indeed to pull data from access. I m not clear as to the structure of the access database (are you creating a new table to represent each sheet?) this code (which assumes...

Excel: Excel Character limit question, cell reference, character limit

cell reference, character limit, len: Naomi, You can use the =LEN(text-cell) function to return the number of characters in a cell. If you want to put in ... in the cell next to the text cell, use the IF statement =IF(LEN(text-cell) 30, ... , ) Obviously this equation has...

Excel: Excel Counting, excel help file, calculation spreadsheet

excel help file, calculation spreadsheet, sumproduct: Ryan, What Excel version are you using? if it is Excel 2007, then look at the COUNTIFS function, which can count all occurrences of records meeting multiple criteria. It is designed exactly for what you want. If you use Excel 2003, you will...

Excel: Excel - coding & matching cells, budget spreadsheet, vlookup function

budget spreadsheet, vlookup function, table of equivalents: It makes sense, and I can think of a few ways to do it - IF you are after just an overall total, then a series of SUMIF statements would do it - if you need to show the actual category, then you would need to build a table of equivalents and use a vlookup...

Excel: Excel countif formula, excel countif, column headings

excel countif, column headings, sumproduct: possibly a pivot table could be the easiest solution as this will allow you to put x,y,z as (say) rows and a,b,c as column headings - and sum them inbetween. SumProduct will also work - its =sumproduct(--(firsttest=whatever),--(secondtest=whatever)) ...

Excel: Excel Date cond formatting, st petersburg florida, last date

st petersburg florida, last date, florida usa: Mari You can use conditional formatting. Go to toolbar/format/conditional formatting In the pop up screen select cell value is, select less than The put in your formula =NOW()-365 Then pick your format then clic okay The formula =NOW()-365 takes...

Excel: Excel Date Question, expiry dates, many thanks

expiry dates, many thanks, spreadsheet: a few questions: 1 - when you say the date has more than 6 months to run you mean the date is more than 6 months in the future? Like 2/22/2010? 2 - You want the word EXPIRED in the same cell as the date or the cell to the right? Since you said youhave...

Excel: Excel- days overdue if not completed, cell a1, cell b1

cell a1, cell b1, column c: I apologize if there was a delay in answering your question. I was out of town for a week and I forgot to set myself on vacation - I just got back this morning. Lets assume your 8/21/09 is in cell A1 Lets assume TODAY()-A1 is in cell B1 The result...

Excel: Excel drop down list of formulas, excel drop down list, dropdown boxes

excel drop down list, dropdown boxes, ft row: Chris, Sure, look in Excel Help at the convert function to see what unit abbreviations it recognizes CONVERT(number,from_unit,to_unit) it lists what units it accepts. I would suggest two dropdown boxes. One for the first item and one for the second....

Excel: Excel Extra Rows and Columns, excel, print area

excel, print area: The way that I usually remove the extra spaces is selecting them, and then using delete row or delete column on the edit menu as appropriate. A normal delete of the data is usually not enough, somehow Excel remembers the area as being filled. Other...

Excel: Excel Formula, cell reference, sheet names

cell reference, sheet names, f18: Calfin, No, its not clear yet. How about if you were to say this? If cell F18 on sheet 1 is the total of a column, and the column is also on sheet 1 (for example F1:F17) and then I want the same equation that is in cell F18 on sheet 1 to be...

Excel: Excel Formula Question, basic principle, excel formula

basic principle, excel formula, holiday time: I m not sure I ve got enough information to go on here but I ll do my best hours worked is a simple function of end time minus start time - I would TEND to do this all in time, rather than converting times to decimal, but that s just me (and it depends...

Excel: Excel Formula for Timeclock, excel formula, quarter hours

excel formula, quarter hours, e7: Tim, You need a custom rounding function. There are several ways to do this, and here is one of them. Make a table that has the fractional minutes in descending order in one column, and the resulting quarter hour value in a second column. The...

Excel: Excel Function, excel help file, bin locations

excel help file, bin locations, excel function: Terry, I don t see how an IF statement can return a zero unless that s one of the clauses within the IF. Supposedly all you have are small , medium and large , and possibly unexpected . Or, maybe you have 0 at the end if all the nested conditional...

Excel: Excel formula, cell b1, dropdown arrow

cell b1, dropdown arrow, employee names: ON SHEET1: Say in cell B1 you have the column-header NAME (in bold). Starting in B2 and continuing down col B you have employee names. In C1 you have WORKING (in bold). Starting in C2 and continuing down col C, you have a Y or N for each employee, with...

Excel: Excel formula, excel formula, vlookup

excel formula, vlookup, mary joe: Chris I you were trying to find the name Bob, then I understand you to want this Mary Joe Bob Bob Phil Joe Mary Phil Phil Bob Bob Mary Bob Bob Joe then in Z1 would appear 3 if so, in B1 put in =if(A1= Bob ,A1,...

Excel: Excel formula problem, excel, if

excel, if, and: This formula is a bit long, but I chose it because it is easier to read and understand what it does: =IF(AND(ISBLANK(B1),ISBLANK(C1),NOT(ISBLANK(A1))), Accrual ,IF(AND(ISBLANK(A1),ISBLANK(C1),NOT(ISBLANK(B1))), Credit ,IF(AND(ISBLANK(A1),ISBLANK(B1),NOT(ISBLANK(C1))),...

Excel: Excel formula problem, array formula

array formula: Hi, You will need an array formula for this. The formula in cell F5 would be: =SUM(IF($B$3:$B$15=E5,IF($A$3:$A$15=$F$2,$C$3:$C$15,0),0)) but instead of using Enter, you will need to use Control+Shift+Enter. If done correctly, the formula will have...

Excel: Excel function, excel function, integers

excel function, integers, wikipedia: Dave, Yes there is a formula just like that. I found it on the Wikipedia. It is Sum of integers from 1 to N = (N*(N+1))/2 If your first number isn t 1, then you can find the sum from 1 to one-less-than-your first number...

Excel: Excel Help, conditional formatting, data validation

conditional formatting, data validation, popup message: jeanette, =sum(A:E) will sum up 5 columns or if you have specific rows =sum(A1:E2) as an example. You could apply conditional formatting to the cell with the formula to turn it red if it exceeds 100%, but there is nothing that supports...

Excel: Excel . Help, typical microsoft, sheet layout

typical microsoft, sheet layout, column numbers: The INDEX worksheet function would probably be the way to go with the definition you have given. This works as follows =index(YourDateTable,RowNumber,ColumnNumber) and will return the value from the given co-ordinates. The row and column numbers...

Excel: Excel Help please, stock sheet, vlookup

stock sheet, vlookup, sheet stock: Kerry, assume the sheet with the stock is names STOCK and the codes are in column A from A2:A200 assume each code has additional information about that code in columns B:M of the same row on sheet STOCK. in the materials list say in cell C5 put in...

Excel: Excel Macro?, excel macro, spreadsheet

excel macro, spreadsheet: use the control toolbar, insert a checkbox, right click it to view code and enter SOMETHING like this Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Range( 11:14 ).RowHeight = 0 Else Range( 11:14 ).RowHeight = 12.75 End If End Sub ...

Excel: Excel Macro to Transpose, false selection, keyboard shortcut

false selection, keyboard shortcut, excel macro: Steven, Sub TransposeData() Dim rw_ans As Long Dim col_ans As Long rw_ans = Application.InputBox( Enter Number of Rows , Type:=1) col_ans = Application.InputBox( Enter Number of columns , Type:=1) With Worksheets( Transposed Data ) .Range( A1...

Excel: Excel Micro_output to another worksheet, excel function, date function

excel function, date function, yyyy: Hanxue, You can use the =DATE() function to create a DATE from the three parts, day,month & year. Read about this function in the Excel function reference to learn how to use it. Format the result as the type of date that you want to see....

Excel: Excel macro to add text to one cell based on another cell, partial string, rng

partial string, rng, excel macro: Sub test() Dim rng As Range Set rng = Range( a1 ) Do Until rng = If Right(rng, 3) = def Then rng.Offset(0, 1) = _ Almost & rng.Offset(0, 1) End If Set rng = rng.Offset(1) Loop...

Excel: Excel Non-sequential Comparison, master spreadsheet, sheet1

master spreadsheet, sheet1, two rows: Alan, I understand that all you want to do is flag the master sheet to show which rows in the master sheet match a row in the update sheet. I will assume the data in in Update.xls is in Sheet1. in a column in the master sheet, put in formula like...

Excel: Excel overtime formulas, excel timesheet, cell a2

excel timesheet, cell a2, cell a1: Ricci: I apologize if there was a delay in answering your question. I was out of town for a week and I forgot to set myself on vacation - I just got back this morning. Lets say the start time is entered into cell A1 as 08:00 AM . Lets say the end...

Excel: Excel Question, countif, fifth column

countif, fifth column, three pieces: Mike, This is slightly complicated and you ll have to do a lot of work to get a result. The first thing I d do is combine all four files into ONE workbook. This is easy. You ll have four sheets. Then in each of the four sheets, I d make...

Excel: Excel question, cell c2, a6 a8

cell c2, a6 a8, cell content: We want cell C2 to display the value in cell A2; C3 to display the value in A6, etc. Cell F2 should display the value in A4; F3 should display the value in A8, etc. This can be done with one formula, copied into all the cells. In C2, enter the formula:...

Excel: Excel Rate Calculator, rate calculator, financial functions

rate calculator, financial functions, case type: Lindsay, Unfortunately my expertise does not extend to financial analysis, (even a simple one like this), yet I can help you. Open the FORMULA ribbon (in 2007) or the Function Guide (in 2003) ( in either case, type an equal sign in a cell to begin),...

Excel: Excel rank correction factor, place finalist, ranked scores

place finalist, ranked scores, rank number: James, I agree... it is such a pain the way Excel chose to implement the RANK function. I also have struggled with this, and haven t found a good, easy way to correct this odd behavior. I can think of the beginnings of a way, though. Make a...

Excel: Excel Spread Sheet, usb barcode scanner, scanner interfaces

usb barcode scanner, scanner interfaces, excel spread sheet: Rommel, You ll need to research how to interface your barcode scanner to your computer to produce a data file, when you scan each barcode. Barcode scanner interfaces are not a part of Excel. Once you find the proper software for your barcode scanner,...

Excel: Excel - Sum Dollars of Two Met Criteria in an Array, cell d2, cell a1

cell d2, cell a1, consecutive rows: Jay What you need is the sumifand formula, unfortunatley this formula won t work in excel, So what I usually do is create one criteria out of the two by merging them with and & formula and then use the sumif formula. In your case assume that your column...

Excel: Excel sheet formulas to determine unduplicated records, d2 d2, excel sheet

d2 d2, excel sheet, pivot table: Adele, Here is one guess of what you want =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:D2 calls )*($D$2:$D2=D2)) 1,0,1) If the test is any service but Calls is a match to any other service but calls then =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:D2...

Excel: Excel TIme help, conversion formula, cell a2

conversion formula, cell a2, richard florida: Tom Here is the formula that will convert the time formatted as HH:MM AM/PM to a decimal number. =(A2-INT(A2))*24 So if you have 7:30 AM in cell A2 the result of this formula will be 7.5 You can apply the same formula to the 2:00 PM cell. Assuming...

Excel: Excel and Time addtion, format cells, day zero

format cells, day zero, time values: I pasted your data into my worksheet and put in a sum formula and I got: 0:00 0:00 0:00 0:00 0:00 0:00 3:00 15:30 12:30 5:00 15:30 10:30 5:00 17:30 12:30 3:00 14:45 11:45 47:15 I did format the last cell as [h]:mm I would format...

Excel: Excel use as a searchable database on multiple criteria, medfile, physician name

medfile, physician name, cihost: Dani: Sorry for the late reply. EXCEL can do all of this and depending on much data you will need to store in the file will determine how fast or slow it will run now and eventually later. Without having seen the whole thing I would suggest not using...

Excel: Excel-VBA, excel ontime, chip pearson

excel ontime, chip pearson, excel vba: babu, Chip Pearson has a page that lays out how to do something on a recurring basis using VBA: http://www.cpearson.com/excel/ontime.aspx What you want to do (as I understand it) is have the sheet recalculate. You can put code in Chip s process like...

Excel: Excel VBA functions, vba functions, worksheet cells

vba functions, worksheet cells, excel vba: Function lastName(fullName As Range) As String Dim iChar As Integer, iLen As Integer lastName = iLen = Len(fullName) If iLen 3 Then Exit Function For iChar = iLen To 1 Step -1 If Mid(fullName, iChar, 1) = Then ...

Excel: Excel to Word, aidan, mail

aidan, mail, heritage: Go to word and choose the mail merge option - generate labels based on your data and drop the data fields in where they need to go - I m being VERY general here as you didn t specify version of Office being used and the methods are subtly different in each...

Excel: Excel Workbook and Sheets, sheet1, excel workbook

sheet1, excel workbook, month of the year: Amber, Assume you enter data in sheet1 in sheet2 in A1 put in the formula =Sheet1!A1 Now select Sheet2 A1 and drag fill/copy down, then select that data and drag fill/copy across. these formula will reflect whatever is entered in Sheet1 in the...

Excel: Excel Worksheet Macro - Revised, cell d2, cell c2

cell d2, cell c2, cell references: Stan, I don t have any way to respond to a rating - so best I can do is try to revise this response and hope you get it. r is column H so basecolumn is 8 loff = basecolumn - 4 = 8 - 4 = 4; so loff holds the value 4 (positive 4) r.offset(0,-4)...

Excel: Excell, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: Excell Formulas, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: That s just a matter of simple math. I m assuming you re placing a 1 in each column representing the accept or non-accept values. If so, just count up each column uisng the SUM function, and then add both columns together to get the total number of responses....

Excel: Exel Counting Not to Exceed Question, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: I would use another cell and say: =IF(A1 50,50,A1) Assuming your calculation is in A1, it will display either A1, or 50 if A1 is 50. You could enclose your current function in the IF statement, but that gets messy. You can use the IF function...

Excel: Expenses Between Two Dates, c description, column c

c description, column c, richard florida: Anthony Yes it is possible, here is how you do it. =SUMIF(A2:B100, 01/31/09 ,B2:B100)-SUMIF(A2:B100, 02/28/09 ,B2:B100) This formula is in two parts and sums the expenses between the two dates 1/31/09 and 2/28/09. The formula says--- 1--Sum...

Excel: How to enable delete formula cell, target cells, rng

target cells, rng: If you want to be able to delete formulas, why bother with this routine at all? You CAN unlock the cell, but then that also negates the purpose of protecting the worksheet. You re asking to protect cells with formulas but you want to be able to delete the...

Excel: excel 2000, selection bar, sht

selection bar, sht, excel 2000: Not sure why you need to do this (right clicking the selection bar would show you all the sheets for example) but you could easily use a macro to do this Sub lister() Dim n n = ActiveCell.Row Dim sht For Each sht In ActiveWorkbook.Sheets Cells(n,...

Excel: excel 2007, conditional questions, conditional test

conditional questions, conditional test, excel function: Ahmad, There are many types of conditions. It s impossible to tell you how to use them all! I can get you started, though. In general, most of the Conditional statements are to be found in the LOGICAL functions group. You should use...

Excel: excel 2007 Conditional formatting for dates, conditional formatting

conditional formatting: Hi Dan, You will need to set up three cases to do this. In the Conditional Formatting dialog box, set it up for three cases and select the Formula Is option for all three. Use the following formulas for the three cases. =B3 TODAY()-300 =B3 TODAY()-330...

Excel: excel, 2 names, d3

2 names, d3, text strings: Meg, Everyone is here to answer questions, so if you have them, then ask them. =IF(LEN(D3)-LEN(SUBSTITUTE(D3, , )) =1,D3,LEFT(D3,FIND( ^^ ,SUBSTITUTE(D3, , ^^ ,LEN(D3)-LEN(SUBSTITUTE(D3, , )))))&LEFT(RIGHT(D3,LEN(D3)-FIND( ^^ ,SUBSTITUTE(D3,...

Excel: excel, positive reply, sheet folder

positive reply, sheet folder, mdf files: I m not clear exactly what you want to do here - are you copying a file from a given location and name to another one? Or do you want a listing of all the files. You also didn t specify which version you are using - prior to 2007, the FileSearch command...

Excel: excel, free joe, ebay

free joe, ebay, craigslist: Joe, Excel as such can only be purchased... it is certainly NOT free. It s fairly expensive, $100USD. You might be able to find a deal on Craigslist or eBay. You can also get a similar program called Open Source, which acts like excel, and it is...

Excel: excel, microsoft program, capability

microsoft program, capability, microsoft: You must have a macro that did what you describe since earlier versions did not give you that capability, assuming you are doing this via File, Send To from the menu. You did not say how you did this in the earlier versions so I have no way of knowing how...

Excel: excel shading, excel, conditional format

excel, conditional format: You can use conditional formatting for this. Select all the cells that you want to format in the column, and go to menu Format- Conditional Formatting. Enter a condition there with a formula like: =COUNTIF($A:$A,A1) 1 The A1 reference should be the first...

Excel: excel: time calculated as minutes., manual calculations, true time

manual calculations, true time, ms office: Yes. For example, if 7:04 entered as a TRUE time then use: =A1*60*24 to convert that minutes, which will give you 424 minutes AFTER you format that cell to a General number format. 7 hours is 420 minutes and then add 4 minutes to that. Thus you can subtract...

Excel: excel no's validation, conditional formatting, a12

conditional formatting, a12, thanks in advance: Hi Priyanka, If I am understanding your question correctly, you want to notate which cells have more or less than the required 12 digits in the phone number field. You can do this by using Conditional Formatting. Please follow these steps: 1 - Select...

Excel: excel2003 finding blanks, parent cells, blank cells

parent cells, blank cells, question thanks: in the procedure ProgressChart you loop over the range r For Each cell In r r is defined as Set r = .Range(Cells(9, 11), Cells(lngLastRow, 11)).SpecialCells(xlBlanks) Where end dates are in sheet on sheet Billing. so it should create one...

Excel: Find Nett available quantity from a list of transactions, xyx, aidan

xyx, aidan, a1: Presumably it s a known list of products, so I d build this list first, and then use a SUMIF function (No programming required) which is =sumif(RangeToCheck,Criteria,SumRange) so for example =sumif(sheet1!$A$1:$A$1000,a1,sheet1!$B$1:$B$1000) would...

Excel: Find & Replace Macro, string s2, cell a1

string s2, cell a1, cell reference: Johnathan, Assume cell A1 has the replace text and A2 has the replace with text then Dim s1 as String, s2 as String Sheets( Agents ).Select Columns( B:F ).Select s1 = Range( A1 ).Text s2 = Range( A2 ).Text Selection.Replace What:=s1, Replacement:=s2,...

Excel: Formatting Excel, boggs federal building, hale boggs

boggs federal building, hale boggs, states district court: In the example you give, the commas that you want to replace with carriage returns are NOT followed by a space, whereas the comman that you want to treat differently IS followed by a space. So to accomplish your specifications using search/replace, it takes...

Excel: Formatting Excel, boggs federal building, hale boggs

boggs federal building, hale boggs, states district court: Hi L2, I am not sure how much data you are working with, but here is a quick solution to get it done: 1 - Select the cell(s) that contain the data you want to search and replace 2 - Click on Edit on the top menu, then select Replace 3 - In the Find...

Excel: Formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: Formula for add row, blank line, dependant

blank line, dependant, aidan: Not directly - you can design your sheet in such a way that data appears on a blank line only if conditions are true, OR you can use VBA (perhaps on the change event) to evaluate two cells and dependant upon the results carry out an action such as inserting...

Excel: Formula to find if last 6 characters in a cell are numbers, len function, 6 digits

len function, 6 digits, string 6: James, HAH! I totally revised this answer based on that final thought I had (the one in the previous answer). You only need one equation and its relatively simple. =NOT(ISERROR(VALUE(RIGHT(string,6)))) There may be additional errors...

Excel: Formula Help, modified times, stop time

modified times, stop time, error c: Mealnie, The correct way to do an OR test is like this: =IF(OR(D6 J6,C6 K6), error , ) You d have to create a comprehensive list of all the possibilities, and check for them all, using a set of Nested IF statements. Or perhaps one giant...

Excel: Formula huge, needs simpler solution., logical tests, job1

logical tests, job1, pivot tables: Nico I am not totally sure that I am following how your data is set up but I believe you can use the VLOOKUP() function to get the information you want. i.e. =VLOOKUP($A1,Sheet2!$A$1:$K$2000,11,FALSE) Assuming cell A1 contains the job number This...

Excel: Formula Issue, date room, c 750

date room, c 750, guest names: James, A really quick thought for you: try putting the portion that returns the #NUM inside an IFERROR() statement. I suggest this without looking in detail at your formula. I wanted to shoot you a quick answer... but if my answer isn t possible,...

Excel: IF Function, input number, bob smith

input number, bob smith, bond james: Vish, Okay then! Here s the approach I d use: Use a set of three nested IF statements, where the conditional test for each one is based upon the MATCH() function, embedded within the ISERROR function, which is itself embedded in the NOT function....

Excel: Is Function to change cell format?, cell a2, isna

cell a2, isna, d4: Tania I haven t had the need to do this in the past,and since the conditional formatting feature doesn t allow the percent or numbe formatting--- but it seems to me that you could reference in your formula another cell that would be dependent on the values...

Excel: IF Function using range names, free microsoft excel, hourly salary

free microsoft excel, hourly salary, logical test: The problem is probably just that you have your named cells in quotes. Get rid of the quotes and see if it works. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Automatic Data population, data links

Excel: Automatic Data population, data links

Excel: convert four-digit number to military time, format cells, digit number

Excel: convert four-digit number to military time, format cells, digit number

Excel: countif not working, logical operators, literal characters

Excel: countif not working, logical operators, literal characters, chars

Excel: Drop down list from another sheet, data validation, ogilvy

Excel: Drop down list from another sheet, data validation, ogilvy, microsoft excel

Excel: file password

Excel: file password

Excel: saving VBA-created objects, c range, b12

Excel: saving VBA-created objects, c range, b12

Excel: If statement cell range, intention

Excel: If statement cell range, intention

Excel: file password, ogilvy, macros

ogilvy, macros, two ways: Brian, there are two ways I can think of, but I can t give you the details of either one, because they are beyond my level of expertise. One is something that would have to be done at the Operating System level, where you would assign a password...

Excel: formatting, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: The easiest solution would be to create a cell that shows the NUMBER OF DAYS this invoice is late (subtract the date from today s date) then apply conditional formatting to that cell. There are many different techniques in Excel you can use to break apart...

Excel: Two formatting questions - workbooks & worksheets, footer text, header footer

footer text, header footer, sheet numbers: Hi Elizabeth, Question 1 - I am assuming that you want the page numbers shown when you print the document. If so, you will just need to select all the sheets each time you print it out. When you do this, Excel knows to page all sheets sequentially. You...

Excel: formula/function for payroll, max function, maximum contribution

max function, maximum contribution, pension contributions: no problem- the MAX function will do it for you =max(1000,yourFormula) NOTE also that I d use the ROUND function to ensure you get a value that fits in with your currency- I imagine 2 dp - just displaying as rounded isn t going to be good enough. ...

Excel: formula help, cell b3, gross profit

cell b3, gross profit, b2: Hey Michael, In order to subtract a percentage from another number, you can use the following formula: =B3*(1-15%) This assumes that selling price is in cell B3. So your formula might look something like this: =(B2-(B3*(1-15%))/B3) I hope that...

Excel: If function, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: That s not too hard: =IF(A3= w ,A1-A2,IF(A3= m ,A1, )) That should do it. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more...

Excel: Generate random numbers in a user-specified range, format xi, cell font

format xi, cell font, thanks in advance: Tim, as you say, the code as written will generate a single random number and assign it to all cells. You will need to loop through those cells and assign a random numbers. As long as your are already looping, you can do it as part of that loop. Private...

Excel: Graph info on different spreadsheets, graph info, cell b2

graph info, cell b2, grap: Say you want to make a graph in which one data series is range A2:A5 in Book1.xls, and the other data series is A2:A5 in Book2.xls. Book2.xls is in C: emp. In Book1, cell B2, enter: = C: emp[Book2.xls]Sheet1 !A2 and copy down thru B2:B5. Then use...

Excel: graphing in Excel 07, types of graphs, column headings

types of graphs, column headings, scatter plot: Twee, There are MANY types of graphs in Excel, especially in the 2007 version! You could make a scatter plot (I call these X-Y plots) where there would be one line for each country.... the X-Axis will automatically fix itself up if the X-values...

Excel: graphing numeric data based on text field, spreadsheet column, line graph

spreadsheet column, line graph, toll free numbers: Mark, I realize it has been a month since you submitted this question. You have probably solved it by now. But in case you haven t, you can resubmit this to me after Sept 29th. I m sorry but events of the past month prevented me from doing much work...

Excel: HELP PLEASE!!, odd rows, cell a1

odd rows, cell a1, excel spreadsheet: Lisa Yes you can do what you want--however how to do it will depend on what your data looks like, what you want it to look like, etc. Some suggestions You may want to use the if and formula to find data that is located within the account number range...

Excel: Help about convert row into columns, platinum gold, platinum silver

platinum gold, platinum silver, gold silver: Satyabrata, This is a terribly complex problem that is beyond my ability to explain with short answer! It would take many hours of complex programming to make this work. I suggest that you direct this question to one of the experts who lists MACROs...

Excel: Help with Excel, b9, ogilvy

b9, ogilvy, len: Naomi, I am not exactly sure what you want, but it you want to know if the number of characters in cell B9 are you can use =len(B9) if you want to display a warning =if(len(B9) 30, Warning, B9 contains &len(b9)& characters , ) This message...

Excel: Help on Macro for copying data from one sheet to another, blank cell, challange

blank cell, challange, cells: Range( A1:E13 ).Copy amend to be the range you want Sheets( Sheet2 ).Select amend the sheet name to whatever your second sheet is called Cells(sheets( Sheet2 ).Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).Select ActiveSheet.Paste should be...

Excel: Help on Macro for copying data from one sheet to another, dynamic name, blank cell

dynamic name, blank cell, mayank: Mayank Gupta, Although I usually don t provide Macro advice on this website, I ll give you a brief answer this time. I have successfully done this for several projects I ve developed. All you need is a simple macro that copies the data between...

Excel: Help Please......, sumproduct, colb

sumproduct, colb, excel function: Cosmin, I m sorry for the delay. You can use the advanced form of SUMPRODUCT to count a column based on an input. What you want is something like this: =SUMPRODUCT(--(colA=1/7/09),--(colB 1/1/1900),--(colB 1/1/2030) where colA and colB...

Excel: Help with an IF Statement, cell b2, conditional test

cell b2, conditional test, input value: Sarah, You need a NESTED (sometimes called a COMPOUND) IF statement. These are easy to write once you know how they work; to that end I have included (at the end of this answer) a short tutorial I wrote. When you write this type of statement,...

Excel: Help With Time Function, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Instead of putting 7 AM - 9 AM in one cell, why don t you put 7 AM in one cell, and 9 AM in another cell. Now you ve got actual TIME values, and changing the other times is relatively easy. There are many different techniques in Excel you can use...

Excel: Help w Excel combining duplicate rows and add, meager mind, employee names

meager mind, employee names, pivot table: The EASIEST way is going to be a pivot table - what you didn t tell me was which version of Excel you are using, so I ll guess NOT 2007 - at which point, data, pivot table and chart report, follow the wizard and then drop PCNamd and Name onto the left side,...

Excel: HH:MM:SS, hh mm ss, exact timings

hh mm ss, exact timings, entering time: Hi Key, Unfortunately the shortcut does not provide the seconds. Here are some options that might be helpful to you: 1 - You can create a macro and assign it to your own shortcut key (or even the same on you are currently using for entering time). ...

Excel: Hidden Rows excluding row 1, header row, ogilvy

header row, ogilvy, len: Dave, How are you trying do to it. Sub HideRows() Dim r as Range, cell as Range rows.EntireRow.Hidden = False set r = range(cells(2,18),cells(rows.count,18).End(xlup)) for each cell in r if len(trim(cell)) 0 then if isnumeric(cell) then...

Excel: Hide Formula errors in excel 2003, format font, ref name

format font, ref name, cells: No need to use macro unless you insist, and then you can record these steps: Select ALL cells (ctrl/shift/A twice), use Format/Conditional Formatting, change Cell Value Is to Formula Is , enter: =ISERROR(A1) click Format, Font tab, then pick a white...

Excel: Hide formula error in excel 2003, ref name, zero values

ref name, zero values, click tools: I am not sure what you mean by hide but you can add the ISERROR function to your formulas to check for errors and display whatever you want the cell to display when the formula in that cell raises an error. Example: =IF(ISERROR(A1/B1), ,A1/B1) when...

Excel: help!, free microsoft excel, perspective column

free microsoft excel, perspective column, question sign: Well, which one is the correct price? What criteria should Excel use to determine this? You probably just need to learn the IF function: You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in...

Excel: hide formula in excel 2003., format cells, exact formula

format cells, exact formula, g1: 1 - View/Formula Bar (uncheck it) - but anyone can redisplay it; 2 - Format cells/protection/selcet Hidden , then protect the sheet 3 - Define a name, like SecretFormula, to be the actual formula -- that is, if you have a formula like =VLOOKUP(D3,G1:H22,2,FALSE),...

Excel: how to hide password while typing, free microsoft excel, input mask

free microsoft excel, input mask, question sign: You can t do it with an inputbox, but you can create a password FORM with a single text box and command button on it. For the text box, use the PASSWORD input mask and that will hide the characters. Let me know if you have any other questions or comments....

Excel: hours to mins, manual calculations, example purposes

manual calculations, example purposes, excel tutorials: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: Importing data from a specific date onwards to a new file, input flow, inputbox

input flow, inputbox, excel sheet: Would the date column always be in a given location, or would the macro have to work out which column to use? - the process is easy enough to write, indeed it s fairly easy to do manually - data, filter, autofilter, select the date column, choose custom and...

Excel: Importing data from a specific date onwards to a new file, input flow, inputbox

input flow, inputbox, sh1: anupreet, I don t know what etc means in terms of the number of columns-- so I set up the macro to copy columns A:C. If you have more columns to copy than that, then change Set r2 = sh.Range(r1, r(r.Count)).Resize(, 3) to reflect the proper number...

Excel: INDEX and MATCH formula in VBA?, formula index, silly question

formula index, silly question, vba: Bill, Read my profile: it says that I don t offer macro or VBA advice on this website. However, I question why you say doing this formula for 20,000 rows is too much . What s too much about it? Does your spreadsheet run slowly, or do you run...

Excel: Increasing Date by One Day in Non-Consecutive Cells, free microsoft excel, cell b1

free microsoft excel, cell b1, cell a1: You just have to select enough cells so that Excel can see a pattern. Then you can just Autofill down. Select A1 thru A20, then Autofill down. Excel should see the pattern and continue with the dates every 10 rows. If you don t know how to Autofill,...

Excel: Index Function, e mail address, index function

e mail address, index function, chef bob: Patrick, Assume names are in column A and January data is in Column B, then I would use =sumif(A:A, Chef Bob ,B:B) I don t see any role for the Index function. if that isn t what you want, then post back. -- Regards, Tom Ogilvy...

Excel: Index/Match Array, room booking system, guest checks

room booking system, guest checks, c 750: James, something along the lines of: if(sum((( Current Bookings !$A$2:$A$750=$B43)*( Current Bookings !$C$2:$C$750 =EZ$1)*( Current Bookings !$D$2:$D$750 =EZ$1)) 1,INDEX( Current Bookings !$B$1:$B$164,Small(if(( Current Bookings !$A$2:$A$750=$B43)*(...

Excel: Input box for entry deletion macro, question thanks, input box

question thanks, input box, step 1: Sub DeleteCodes() Dim ToDelete As String, i As Integer, NumDeleted as Integer ToDelete = InputBox( Enter Code to delete ) If ToDelete = Then Exit Sub NumDeleted=0 For i = Range( B65536 ).End(xlUp).Row To 1 Step -1 If Left(Cells(i,...

Excel: Insert, vlookup formula, rng

vlookup formula, rng, input box: This will work except that I can t finish the VLOOKUP formula because I don t know where you re looking col A up! So you ll have to change the formula. Sub Insert() Dim Rng As Range, TheCol As Range, LastRow As Integer Set TheCol = Application.InputBox(...

Excel: import text VBA, array array, simulation model

array array, simulation model, spath: Alexis, The error would be because you don t have a workbook open named Book1 You need to put a legitimate name for the workbook where you want to move the sheet. Suggest you use the proper extenstion as well Workbooks( Master.xls ).Sheets(1) ...

Excel: LArge excel file, slow calculation inefficient

slow calculation inefficient: Dave, There can be many reasons why it is slow. First, two major classes of reasons: 1) Something else is using your computer resources. For example, you are simultaneously solving pi to 1 billion decimal places or running a weather simulation...

Excel: Large function, relative cell value, array formula, curly brackets

array formula, curly brackets, relative cell: Kev, I put random numbers between 1 and 5 inclusive in the range A1:A20. Then I went to B1 and entered the following formula =SMALL(IF($A$1:$A$20=LARGE($A$1:$A$20,1),ROW($A$1:$A$20)),ROW()) I entered the formula with Ctrl+shift+enter rather than...

Excel: Large function, relative value, top salespeople, value question

top salespeople, value question, vlookup: Need a few helper columns, say G & H. In G1: =COUNTIF($D$1:D1,D1)-1 Fill down to G5. in H1: =MATCH(D1,OFFSET($A$1,IF(G1=0,0,MATCH(D1,A:A,0)),0,1000,1),0) in H2: =MATCH(D2,OFFSET($A$1,IF(G2=0,0,MATCH(D2,A:A,0)),0,1000,1),0)+IF(D1=D2,H1) Fill down to...

Excel: Linking data to combo boxes, source sheet, combo boxes

source sheet, combo boxes, linking data: Rodney, I would need to see what you have to suggest a solution. Any solution would be specific to how you have the data laid out on your source sheet. If you want to send me a sample file, I will try to show you a solution/approach. send a sample...

Excel: Linking information from one cell on one sheet, to another cell on another sheet, cell b11, sheet1

cell b11, sheet1, a12: You have two sheets. On the sheet named Sam, you want cell A12 to display the value that s in cell B11 of the sheet named Joe. Select A12 on Sam. Hit the = key. Click the Joe tab. On Joe, select cell B11. Hit the Enter key. Cell A12 of Sam will now have...

Excel: Linking Workbooks, excel workbooks, first instance

excel workbooks, first instance, instances: Lisa Do you have two instances of Excel open? ie do you open one workbook then start another instance of Excel and open the other workbook? If so open both workbooks from the first instance of Excel. If that isn t the problem get back to me. Richar...

Excel: Listing all external file references, path workbook, sheet tab

path workbook, sheet tab, cell reference: I m assuming you have an open file A.xls, which has a reference to a closed file B.xls. Select all worksheets within A.xls (unhide all sheets, click on the first sheet tab, hold the shift key, then click on the last sheet tab). Hit Ctrl-F, and do a Find...

Excel: Listing new items, array formula, curly brackets

array formula, curly brackets, colomn: StuartJohnWood, I tested with constants in column A of worksheet2 and overlooked that the formula would produce the null string so here is the revised formula be B1: =IF(ROW() =SUM(1*(LEN(TRIM($A$1:$A$1000)) 0)),INDEX($A$1:$A$1000,SMALL(IF(LEN(TRIM($A$1:$A$1000))...

Excel: Locating Cells, matching number, different numbers

matching number, different numbers, visual representation: Luis, A very tricky arrangement! I would never have guessed from your first question! I see how to do it! It s not too difficult. You need to put a FORMULA in the conditional formatting [CF] dialog box which checks the value in that...

Excel: Logical/Nesting functions, hey sandy, d7

hey sandy, d7, e7: Hey Sandy, I believe the following formula is what you are looking for: =IF(D7 60,2.45,IF(AND(D7 =60,D7 =119),2.27,IF(AND(D7 =120,D7 =239),2.12,IF(D7 =240,2.00)))) I hope this answers your question. Please let me know if it does not. Thanks! Jessic...

Excel: Look up formulas, blank cells, array reference

blank cells, array reference, absolute references: you need to use absolute references here – A2 is a relative reference, in that as the formula moves around the sheet, so does the cell it is referencing. $A$2 is an absolute reference – it refers to A2 no matter where you put the formula. In the formula...

Excel: Lookup Date Range, report wizard, pivottable

report wizard, pivottable, index formula: 1. Add a column to your data labeled Month 2. type in this column in each row the following starting with row two =month(a2) This will list the month in each row of this column 3. Click somewhere on the data 4. Select the Data drop down menu...

Excel: Lookup a given cell ref in another workbook and return another cell's value based upon the reference, cell references, colb

cell references, colb, proper location: John, You can use the indirect function for this. Both workbooks must be open in the same instance of excel. then you can do for example, in A2 you could put a formula like: =Indirect( [Other bookname.xls]Sheet1 ! &B2) You would need to adjust...

Excel: Lookup Multiple Rows., cell references, lookup functions

cell references, lookup functions, vlookup: Nabam, This is an EXTREMELY difficult problem, made difficult by several factors: 1) your data is not consistently spaced. In some cases, one set of data spans THREE rows; in other cases, TWO. There may be other cases that span 1 or 4 rows,...

Excel: Lookup question, column c, question question

column c, question question, shooting from the hip: Yes and there needs be another change as well, for the OFFSET function, which I have done below =IF(ISERROR(OFFSET(C1,MATCH(50,C1:C5,1),-1)), ,OFFSET(C1,MATCH(50,C1:C5,1),-1)) this means that column C will have the 50% value you are looking for and...

Excel: ledger, receivables, b2

receivables, b2, collections: You can use a helper column to see if the date is older than 30 days, something like this (assuming B has the dates): =IF(TODAY()-B2 =30,2,1) and fill down, then sort by this column and all the 2 s will be at the bottom. You can cut/paste these to the other...

Excel: lookup and return a value from a table, sheet1, vishwanath

sheet1, vishwanath, ogilvy: Vishwanath, Assume the table is in sheet1 so for an entry in B2 of the other sheet then in A2 of the other sheet =IF($B2= , ,Vlookup($B2,Sheet1!$A:$B,2,False)) I assume you will enter a date that is an exact match to one of the dates in the...

Excel: MAtch text, dharmender singh, email addresses

dharmender singh, email addresses, correspondences: bhavana, You didn t give me any examples of what the corresponding email addresses look like, so this is difficult to answer. Probably 10 examples showing the different variations would be helpful. It s unlikely that this will be easy, since...

Excel: Macro, test xls, valid expression

test xls, valid expression, wkb: Example: you want to know if the workbook named Test.xls is open. You want the variable blnOpen to be TRUE if Test.xls is open, and FALSE otherwise. Dim wkb As Workbook Dim blnOpen as Boolean On Error Resume Next Set wkb = Workbooks( Book1.xls ) blnOpen...

Excel: Macro to extract specific values from a list, vba code, mitali

vba code, mitali, ogilvy: Mitali, Although my profile says that I don t offer macro advice on this website, I wonder if you are using the word Marco properly. Macro is VBA code, which runs and performs a function. I am not sure you need a macro to do what you want. But,...

Excel: Macro for finding data, isnumber, sheeta

isnumber, sheeta, worksheet functions: Philip, You have asked at least 3 questions since the one you are following up on. I answered all three in one way or another in less than 30 minutes after they were asked. I asked you to send me your workbook and gave you my email address - according...

Excel: Macro to pick value and copy row, excel spreadsheet, reference numbers

excel spreadsheet, reference numbers, ogilvy: Jay, This worked for me. Test it on a copy of your data. Sub CopyData() Dim r As Range, cell As Range Dim r1 As Range, res As Variant Dim r2 As Range Set r1 = Range(Cells(1, A ), Cells(Rows.Count, A ).End(xlUp)) Set r = Range(Cells(1, AC...

Excel: Macros, layout location, sheet1

layout location, sheet1, voice of experience: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Mail/Hyperlinks, hyperlinked, a10

hyperlinked, a10, aidan: two options - one is via a macro, the other uses word to do a mail merge - see http://www.rondebruin.nl/tips.htm for macro instructions on emailing, for mail merge, use the wizard in Word to set this up and set the mailing recipients. Sorry to be so brief...

Excel: Maintaining Cell References in a Formula During A Sort, source cells, cell moves

source cells, cell moves, cell references: Bill, You can t maintain the references to cells when they sort, as you have observed. Formulas external to the sorted data will refer to the same cells; the cell references don t change with the sort order, as you have seen. Its the VALUES in the...

Excel: Manipulating Characters WIthin a Cell, 4 digits, macro in excel

4 digits, macro in excel, jones consulting: There are many ways to do this. Here is one way that might help you. Change the range as needed. Note that your data will have to be formatted consistently for this to work every time correctly and on the right cells since it looks for space dash space...

Excel: Master sheet, positive reply, detail sheets

positive reply, detail sheets, excel sheet: There s no such thing as automatically updated in the master sheet -- I d need to know a lot more information, like how the detail sheets are laid out, how the master sheet looks, if you want data copied/pasted somewhere onto the master or it it should pick...

Excel: Matching Two Columns, logical formula, logical function

logical formula, logical function, career industry: Kevin, This is perfectly clear, and there are several ways to do it. If it were me, I d use Conditional Formatting [CF] to change the color of all rows where the before and after don t match. You could also add a column with a logical function...

Excel: Merging cell contents, free microsoft excel, paste values

free microsoft excel, paste values, excel tips tricks: Make your concatenation formula in column C, then copy the whole thing and paste it into column D but only paste the values: Edit Paste Special Paste VALUES Let me know if you have any other questions or comments. Also, be sure to visit my web...

Excel: MODE function, pivot table, numeric column

pivot table, numeric column, matching numbers: Ramya, The MODE function only works with numbers so you can t use if for determining the most frequently occurring piece of text. Do to so, you must: A) create a list of each piece of text (a unique list). You can use a single-column pivot...

Excel: Move data from cell without moving formula, input table, true expert

input table, true expert, sort keys: Jonas, To make the rows automatically change order when data is entered (without writing a macro) is not easy, in fact it may be impossible. But you can do something like what you want. My first suggestion is to find one column that you can...

Excel: macro for sorting like entires, jamie price, dummy data

jamie price, dummy data, couple rows: Jamie Price, Send me a sample workbook with some dummy data in it or real data - your choice. If dummy data make sure it accurately represents you data. Tell me what version of Excel you are using. The first step will be to sort the data so all...

Excel: mm/dd/yy hh:mm:ss AM/PM format in excel, hh mm ss, format cells

hh mm ss, format cells, cell a1: Open a new Excel workbook. Select cell A1. Type in a sample date, e.g.: 7/18/1959 3:33:10 AM With A1 still selected, choose from Excel menu: Format, Cells, Number, Category: Custom, Type: mm/dd/yy hh:mm:ss AM/PM (you re typing this into the entry box...

Excel: modified workdays function, free microsoft excel, custom vba function

free microsoft excel, custom vba function, question sign: Excel makes it easy to count the number of WORK DAYS between two dates. You can use NETWORKDAYS if you only need to worry about excluding Saturdays, Sundays, and a list of holidays. However, if you have a custom requirement, you ll need to write your...

Excel: moving data, cell a1, receivable ledger

cell a1, receivable ledger, sheet1: Edgar You can use an IFTHEN formula to determine that the account is over thirty days and then move it to collections. This formula will do it =IF((NOW()-A1) 30,+B1, ) Assuming the date of the invoice is in cell A1 The formula says, if the...

Excel: Need help with Combo Box and Exporting Data, table values, concrete example

table values, concrete example, combo box: It s generally beyond the scope of this free service for me to create an entire vba solution for you from scratch. Many paid services are available in which someone will do all the work for you. If you d do some of the work yourself, and ask a question here...

Excel: I need help with solving this problem I've tried a pivot table but can't quitem make it work., commission percentage, pivot table

commission percentage, pivot table, gross margin: Adam, A pivot table might work, but you probably are having trouble getting it to be in the table-format you want. What would be better is one of the single-or-multiple criterion record-selector and summarizing functions. There s a whole...

Excel: nested sumif?, b2, c2

b2, c2: Not clear what you mean by seperate formula, but for your sumif try =SUMPRODUCT(--(A2:A10=B2:B10),C2:C10) amend my A2:a10 etc to the actual range you want for the certain percentage something like =SUMPRODUCT(--(B2:B10/A2:A10 1),C2:C10) will do...

Excel: Obtain values in column M based on alphanumeric match in column C, countif, agent names

countif, agent names, corresponding service: Glenn Names in C, values in B given a name, sum the corresponding values in B and also get a count of the rows that contain the name =sumif(C:C, AgentQ ,B:B)/Countif(C:C, AgentQ ) if AgentQ is in cell Z5 it could be =sumif(C:C,Z5,B:B)/Countif(C:C,Z5)...

Excel: Offset function to multiple rows, bj2, result row

bj2, result row, right combination: You didn t tell me what the formula does, so I m not sure if offset is actually the way to go, but =offset($a$1,1,2) would return the cell one row DOWN from A1 and 2 columns across - but possibly using the right combination of absolute, relative or mixed...

Excel: Page number refencing, double quotation mark, cell references

double quotation mark, cell references, text values: Page numbers in excel don t actually exist - they do on printed output, but on the sheet itself it has no meaning - changing the margins or zoom would affect the page. You can reference other cells, and you can use the CELL worksheet function to return information...

Excel: To Paste Jpg Images form Folder, employee id number, string s2

employee id number, string s2, myfolder: The Application.FileSearch has gone in office 2007 - though heaven knows why - this code With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & .Title = Please select a folder to list...

Excel: Percentage, format a3, format cells

format a3, format cells, percentage formula: Stephen =24/42 if you go to format cells, then select percentage and pick one of the percentage formats I get 57.143% or just 57% depending on how many decimal places you want to show. A1: 42 A2: 24 A3: =A2/A1 if you have your values...

Excel: Pivot Table, excel 2007 pivot table, best regard

excel 2007 pivot table, best regard, pop up menu: Khurram, I think you can accomplish this by using INSERT CALCULATED FIELD. This is an option which appears in the (Excel 2007) Pivot Table Ribbon, which becomes available when you click anywhere in the existing pivot table. The option (which is on...

Excel: Pivot Table, best regard, pivot table

best regard, pivot table, borders: You can t do this automatically - you either need a macro to do both the pivot table and this extra column or the extra column itself, OR You can VERY easily do it manually: Select the entire Credit column, copy it, select the Balance column, Paste special...

Excel: Printing nonadjacent cells in Excel 2003, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, paste link: I don t believe that s possible the way you re doing it. You could create a summary sheet with the two regions you want, and then print that. Just copy and paste-LINK the regions you want into a new sheet, and then print that summary sheet. Let me...

Excel: How do I preserve the '0's in text-number conversion?, cells, conversion

cells, conversion, conclusion: Brent, An example of one case, without a general problem statement, isn t clear enough for me to draw the general conclusion of what you want. If you want to make 0004.00 into 5350004.00, add 5350000 to 4.00 and you ll get 5350004.00. I m sure...

Excel: question on ranges, strikethrough, outl

strikethrough, outl, start 1: Manny, You haven t told me where to look to find the last row, so I will assume it is column M (which column will always have a value in the last row you want to fill to?). Then Sub GetPM() Dim lastrow as Long lastrow = cells(row.count, M ).end(xlup).row...

Excel: Random Selection of cells and values averaged., 6 cells, index function

6 cells, index function, ethen: Rahul I have only needed to do this once, and as far as I know there is no function in excel that automatically will choose randomly a range of consecutive cells. As I recall what I did was something like this. First I created a grid that represented...

Excel: Ranking formulae, excel formulae, format cells

excel formulae, format cells, relevant names: Using my original answer, change the D2 entry to =IF(CELL( row ,D2)=2,1,D1+IF(SMALL($C$2:$C$6,CELL( row ,D2)-1)=SMALL($C$2:$C$6,CELL( row ,D2)-2),0,1)) and the E2 entry to =VLOOKUP(SMALL($A$2:$A$6,CELL( row ,D2)-1),$A$2:$B$6,2,0) and copy down...

Excel: Recognising data fitting a mask, many different things, cell a1

many different things, cell a1, ogilvy: Claire, try this formula: =IF(LEN(A1=7),IF(ISNUMBER(RIGHT(A1,4)*1),IF(SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6,7},1)*1),{1,2,3,4,5,6,7}),1))=4, Match Mask , No ), No ), No ) lightly tested, but worked for me. -- Regards, Tom Ogilvy...

Excel: Referencing a cell beneath the vlookup function, vlookup function, specific solution

vlookup function, specific solution, request number: Steven, the solution is reasonably complicated, so let me refer you to a link which explains it with some examples and illustrations that should make it easy to implement - a lot more details than I could provide in this medium. Article by Ashish Mathur...

Excel: Reg: VBA Code, employment address, education column

employment address, education column, database column: Avinash, I would see adding code like this: Cells(Target.Row, 12).Value = Holdvar - this is already in your code, add these lines after Dim v1 as Variant, j as Long, i as Long v1 = Array( Education , Employment , Criminal , Database , Address ,...

Excel: Reg: VBA Code, target cells, column c

target cells, column c, tsd: Using the visual basic editor, enter the following code in the worksheet object: Private Sub Worksheet_Change(ByVal Target As Range) Dim rngColA As Range, rngColB As Range If Target.Cells.Count = 1 Then If Target.Column 3 Then ...

Excel: Removing zero values from columns to only show non-zero values in a different worksheet, ms excel 2000, zero values

ms excel 2000, zero values, maintenance cost: Josh, John Walkenbach documents a way to do this although in his example, he has one column of labels on the left (you have Name and User#). See if this method will work for you. He also has a link to a macro that will do it. I doubt it would work...

Excel: Reset a validation list that is in a protected worksheet, data validation, proper password

data validation, proper password, error msg: Yve, Try unprotecting the sheet in code and then protecting it Dim r As Range Dim r1 As Range Activesheet.Unprotect Password:= ABC Set r = Range( C10 ).EntireColumn On Error Resume Next Set r2 = r.SpecialCells(xlCellTypeAllValidation) On Error...

Excel: Retain Original Cell Value in Macro, loff, cc3

loff, cc3, rg: Stan, Just guessing here, but perhaps this is what you want. (you want to capture the values in the last row in F and G at the beginning of the macro and at the end of the macro you want to put those values back in the same cells - that is how I understand...

Excel: Retrieving data based on cell match, cell b1, sheet1

cell b1, sheet1, column c: James, I believe this is what you want: In D1 of sheet1 put in the formula =if(iserror(match($B1,Sheet2!$B$1:$B$265,0)), ,Vlookup($B1,Sheet2!$B$1:$C$265,2,False)) Now select D1 and drag fill the formula down column D for as far as you have data...

Excel: cannot reinstall Excel 2003, ms office professional, word attachments

ms office professional, word attachments, computer friend: Ivan, I cannot tell what might be wrong from the little information you have. This is a case where you might want to read the log file and see what it actually says. or, you can send this to me and I will take a look (but you must read it also). ...

Excel: Save importing file, vba procedure, shell application

vba procedure, shell application, excel2003: Insert the following code into your vba procedure: Dim selectedFolder As String Dim ShellApp As Object selectedFolder = Set ShellApp = CreateObject( Shell.Application ).BrowseForFolder(0, Please choose a folder , 0) On Error...

Excel: Search Across Worksheets, type in a keyword, that would allow users

type in a keyword, that would allow users, aidan: It s certainly possible, though I d need slightly more information to give you a working macro - are you searching for a sheet name, or the contents of a cell within the sheet? If the latter, is it all cells, or just a given column or set of columns. Are...

Excel: Shading Cells in Exccel, format painter, column format

format painter, column format, excel 97: You didn t specify version but as I m sure it s at least excel 97 that shouldn t matter too much - this can be done with format, conditional formatting - it would need a formula option - you say it should already be set up, so if so, you should see the formulas....

Excel: Skip a cell in a For Each Cell action, cell c5, cell c3

cell c5, cell c3, range selection: Mark For each next works as you state, so you can t use for each next. You could use a for next construct and make a decision on each cell, but I think a Do Loop is a better construct here. Sub abc() Dim r As Range, i As Long Dim xcell As Range,...

Excel: Sort a digit column by last 2 numbers, 6 digits, hidden answer

6 digits, hidden answer, a1: Say your list is in col A, beginning A1. In cell B1, enter =RIGHT(A1,2)+0 and copy down col B as far down as your data goes. Then use a sort of A:B, using B as your primary sort, to accomplish your tax. If you put the col B formulas in white font, then...

Excel: Sorting Groups of Rows in Excel, bob dole, sort key

bob dole, sort key, holiday hours: Mahala, The only way I can see to do this is to add two columns to the spreadsheet and use them to sort with. The first column to add is a column which repeats the employee name five times, thus creating a group which will stay together when...

Excel: StartBlink, excel sheet, excel worksheet

excel sheet, excel worksheet, animation: If you looked at the answer I think you are referring to (http://en.allexperts.com/q/Excel-1059/Animation-Excel-Worksheet.htm) you would see I mentioned using an event - I suggested using the open event - if you can clarify what the issue is I can possibly...

Excel: IF Then Statement with a total sum, sumif, richard florida

sumif, richard florida, n100: Sara You didn t give me your formula so I can t tell exactly how you have written it, but based on what you are saying I think your formula should look something like this =SUMIF(K1:N100, premier ,N1:N100) Check your formula and see if it does look...

Excel: If Statements, conditional test, cell references

conditional test, cell references, cell values: Barbara, Your question isn t clear. You made a nice little table of a & b, but then never referred to the b-part. I assume that what you really meant is this: How do you ask if a = x and b is less than 15, and a = x and b is greater than 15...

Excel: Strip numbers from text, isnumber, cell b1

isnumber, cell b1, cell a1: Kathy, =1*MID(A1,SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)),FIND( + ,A1)-SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)))+(1*MID(A1,FIND( + ,A1)+1,5)) in B1, then drag fill down column B. ...

Excel: Strip numbers from text, cell b2, isnumber

cell b2, isnumber, cell b1: Kathy, I accidently hit something that marked this a rejected, but here is the answer that worked for me. I put this in B1 =1*MID(A1,SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)),FIND( + ,A1)-SUMPRODUCT(SMALL(IF(ISNUMBER(MID(A1,{1,2,3,4,5,6},1)*1),{1,2,3,4,5,6}),1)))+(1*MID(A1,FIND(...

Excel: SUMIF (Array), pivot table service, works microsoft

pivot table service, works microsoft, cell references: Hi Ravi, In order to categorize the times, please follow these steps: 1 - Insert a column in front of column U. This will move the current column U to column V. 2 - Starting in the first cell in column U, enter the following information (This adds the...

Excel: Sub Procedure question, rs1, sh2

rs1, sh2, table reference: The first problem I see is that you see rs1 to Nothing and then try to loop thru each cell in that NOTHING, which can t and wont work. Secondly, you don t tell the code that rs1 should reference the range name ISYES. Thirdly, you can t type a variable as...

Excel: Sum of Lookups, array formula, column names

array formula, column names, intermediate steps: Ctrl/shift/enter: =SUM(N(OFFSET(A2,0,MATCH(lookupnames,$B$1:$D$1,0))) * $B$27:$C$27) INDEX only returns one value; OFFSET returns multiple values. Oddity is that the N-function, which here does nothing, makes it work (without it you get #VALUE!) If...

Excel: Sum Question, array formula, curly brackets

array formula, curly brackets, blank cell: John You can use a Sum an If statement to get what you need. Type in the following assuming that C column starts at 2 and goes to 80 =SUM(IF(C2:C80 0,1,)*D2:D80) then press the Ctrl+Shift+Enter key to complete the formula (this is called an array...

Excel: saving VBA-created objects, command button, macros

command button, macros, boxes: Since I can not and have not seen your code and thus do not know how specifically you are doing this and/or WHEN they are being populated then it is real hard for me to answer. Clicking the command button does not permanently populate those controls. If...

Excel: search and load user form, vba code, combo boxes

vba code, combo boxes, firstchoice: You may ask multiple questions in separate posts, but please limit each post here to one specific question. I ll answer your first question here. Suppose that your data is on Sheet1, with Advanced, Advanced, Caterpillar etc in A1:A4, and Hazelhurst, Memphis,...

Excel: shading between two polynomial fits, pam pam, type appearance

pam pam, type appearance, signal amplitude: Pam, When you say you want to draw a shade area between THESE two lines , you can ONLY mean between the two data lines, NOT between the polynomial fit lines. Hmmm, I seem to be assuming that your chart consists of FOUR lines; the original two...

Excel: skipping rows of data, free microsoft excel, paleoclimate data

free microsoft excel, paleoclimate data, excel tips tricks: Kerry, this would be MUCH easier to do in a database program like Access. Excel is just not designed for this kind of thing. When you say you need to extract 11 rows what do you need to do with them? Copy them to another sheet? If so, I m sure a macro...

Excel: sorting out e- mails, e mail adresses, dropdown arrow

e mail adresses, dropdown arrow, pivottable: There are several options: A. Use the sort option (Each Column of Data must have a header) 1. Click on the database 2. Click on the Data drop down menu 3. Click on Sort This will bring up the Sort popup menu 4. In the Sort By section, click...

Excel: sorting letter-by-letter, test words, abac

test words, abac, word by word: Enter your test words in range A1:A4 like this: AB CD AB EF ABAC ABAF In cell B1, enter: =SUBSTITUTE(A1, , ) then copy this formula down through range B1:B4. Then sort A1:B4, using col B as your primary sort col (no headers). Col A will end...

Excel: If statement cell range, array formula, cell reference

array formula, cell reference, cell b1: Don, Your formula has an error, the second paren around the range should not be there. It should read: =IF(B1:B8= DOG , , X ) You also state the result inverted... you re thinking that a BLANK should appear (according to the formula...

Excel: if then statements from a drop down list that comes from range., free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Link your drop-down box to a specific cell, then use an IF statement to set the value of the second cell. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: stuck on excel formula :(, vlookup, excel formula

vlookup, excel formula, excel spreadsheet: If I read this right, it s not Vlookup, but HLookup that you want - that would look ACROSS the data until it comes to a date that you want, then return a value x rows down from that. today() is the function to return todays date so something like =hlookup(today(),YourHorizontalTable,2,false)...

Excel: stuck on excel forumula :(, vlookup formula, excel spreadsheet

vlookup formula, excel spreadsheet, last time: When you say ...put that data... , is that data in one cell? I other words, will you have just one cell in the other sheet that corresponds to today s date that you want to pull over into the other sheet? If so, then a VLOOKUP formula will work IF...

Excel: sum bill of material by assembly level, spreadsheet design, cumulative sum

spreadsheet design, cumulative sum, cumulative value: James, Ass a column that takes the cumulative value of all the rows where there is a 1 in the level column, and which puts in a zero when it encounters a zero in the Level. Write a simple formula that says: If there is a 1 in the Level column...

Excel: sumif with partial keyword, SUMIF, wildcard

SUMIF, wildcard: Hi Setiadi, If the first line is on row 1, the formula for cell D1 would be: =SUMIF($A$1:$A$5, * &C1& * ,$B$1:$B$5) The asterisk (*) character is a wildcard and in this formula, it looks for the column C value anywhere in the column A value, and it...

Excel: Time calculations, time calculations, time range

time calculations, time range, time hope: Hi Ravi, Here is more of an explanation on how to use the formula I wrote for the VLOOKUP. This will definiely work best because IF statements will become very confusing, especially with the limitations to nested IFs. =VLOOKUP(T1,$U$1:$V$7,2) What...

Excel: Time/conditional formatting, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: There are MANY ways to do this, but assuming you have your date/time value in A1, then set B1 to: =INT(A1) Now format that as a DATE field. That will give you the whole date (only) portion. Now what you have left over (the fraction) is the time. So...

Excel: Time difference calculation, cell a1, time column

cell a1, time column, train time: William, You need to re-create the times so this doesn t happen. make a new table according to the concept set forth below, and use the new times to perform your calculations. What you need to do is detect this condition. if the end-time is...

Excel: TRIM function problem, ascii character code, cell a2

ascii character code, cell a2, colums: If I read this correctly, it s simply a question of paste special, values - but possibly doing a search and replace on the original column would be a better way to go, or use the substitute worksheet function within your exact to remove these returns - eg...

Excel: Trouble with IF statement, data changes, aidan

data changes, aidan, c4: I don t know what the IF statement is doing, but if a macro is generating results, it should be easy enough- the counta worksheet function would give a number of rows, or test for a cell being blank and carry out the process whilst that condition is not true?...

Excel: Trying to copy a cell contents once validated, sheet1, cell contents

sheet1, cell contents, cells: A formula won t really do it, you need VBA code -- Assuming the sheet with all the data is on Sheet1 and the other sheet is Sheet2, then this macro will do what I think you want: Sub MoveRemove() Dim n As Integer On Error Resume Next With Sheets(...

Excel: Trying to insert a row and then manipulate data, worksheet selection, blank rows

worksheet selection, blank rows, blank row: Tejal, Test this on a copy of your worksheet. It did what I expected it to do, but I don t know if that is what you want it to do. It inserts the row and copies the data all in the same step so you only run the Insert_Blank_rows macro. Again,...

Excel: timesheet calculations, free microsoft excel, timesheet calculations

free microsoft excel, timesheet calculations, excel tips tricks: It s not just ONE formula, it s knowing how to set up the timesheet. I cover timesheets specifically in my Excel 232 course: http://www.599cd.com/site/courselist/excel/excel232?key=AllExperts However, the following tutorials might help you as well:...

Excel: Use VBA to copy sheets between workbooks - "Subscript out of range", source cells, source copy

source cells, source copy, range error: Set xlw_source = Excel.Workbooks(ResultSheet) is basically fine, SO the contents of ResultSheet cannot equate to a currently opened workbook at the runtime, hence the error - I d try recording a macro to do something similar and that would give you an...

Excel: Using Excel interface to execute an external program, shell command, vba code

shell command, vba code, excel sheet: Depends on how you want EXCEL to launch the program but you can use the SHELL command in VBA to launch an external program. WHERE you put this code will be what you need to decide. You can use the Workbook_Open even to store the code, which means the code...

Excel: Using VBA to replace names, left hand column, english muffins

left hand column, english muffins, number of names: Aaron, I would put this table in one sheet, call it sheet Table with data in A:B in your data where the names need to be replaced, I would insert a column next to those names. Assume the names are in column C so I would insert a new D. In D I would...

Excel: How to update column, cell b1, iteration count

cell b1, iteration count, cell a1: Stephen, It s very difficult to do this, since it requires that Excel have a memory of the previous values that led up to the number. What you re asking is for Excel to remember that you typed in a 5, and to retain the value of 105 when you take the...

Excel: using excel to download files from sharepoint/ websites, analysis spreadsheet, mybook

analysis spreadsheet, mybook, vba macro: downloading files is the same as opening them, so the VB command to open a file would be the way to go - and referencing sheets is easy enough dim mysh as worksheet set mysh=activeworksheet dim mybook as workbook set mybook=workbooks.open( YourFilePath...

Excel: VB blanks and such, c cells, copy worksheets

c cells, copy worksheets, blank rows: Jason, This is what I understood you to want. Test it on a copy of your data. Sub Update() Update Macro Macro recorded 8/18/2009 by Administrator Sheets( Domains_about_to_expire ).Select Cells.Select Selection.Sort Key1:=Range(...

Excel: VB copy/ transfer, gtrd, c cells

gtrd, c cells, srow: Ray, I understand you want to go to each of 4 different sheets and then run the macro to have the data on that sheet copied to mastersheet in the next available row. then you just need to change how drow is initialized. Instead of drow = 1 use...

Excel: VBA, copy worksheets, header row

copy worksheets, header row, ogilvy: Jason, Sub ABC() Dim r As Range, r1 As Range Dim cell As Range With ActiveSheet Set r = .Range(.Cells(2, A ), .Cells(Rows.Count, A ).End(xlUp)) End With For Each cell In r If Len(Trim(cell)) 0 Then If cell.Text = 1/1/1900 Then ...

Excel: VBA Code Help, spath, excel template

spath, excel template, filesystemobject: If I understand your question correctly, what you re trying to learn is to use vba to determine the file within a folder that s been most recently saved. Here s some sample code, which you should be able to customize to your needs. This sample code stores...

Excel: VBA code to capture data from Windows Explorer to Excel, excel template, hi bob

excel template, hi bob, sheet1: Actually, it doesn t clarify it for me. What s your original template ? What s its name and how is it relevant? How is it known what new file(s) are in the folder? How is that relevant? Can you be a lot more specific? Why isn t what I gave you going to work...

Excel: VBA code to copy data from a workbook that has password protection., command button, pastes

command button, pastes, wb: Jill If you are selecting in that worksheet and those cells are locked and protection doesn t allow selection of locked cells that could be a problem. I believe you said the sheet was/is hidden, so you can t select a hidden sheet. The solution might be...

Excel: VBA Excel Interior, target cells, cell a2

target cells, cell a2, vba excel: Below is some sample code. If entered to the Worksheet object, using the Visual Basic Editor, then it ll be triggered whenever user makes a change to a single cell in col A of that wks. It will turn cols A:W in that row to Red if the col A entry is AA; Yellow...

Excel: VBA/Macro help, array array, old fashion way

array array, old fashion way, computer locations: Why not just use a formula? =MID(A1,FIND( ,A1,1)+1,FIND( ,A1,FIND( ,A1,1)+1)-FIND( ,A1,1)-1) The formula finds the position of first and then the position of the second . It then uses that data to extract or pull out whatever is between...

Excel: VBA match and copy cells., wb1, wb2

wb1, wb2, sheet1: Sub Untested() dim N as integer, WB1 as Workbook, WB2 as workbook Set wb2 = Workbooks( 2ndWB.xls ) change accordingly Set wb1 = Workbooks( 1stWB.xls ) also. On Error Resume Next for i=1 to wb2.sheets(1).range( A1 ).end(xldown).Row n=0...

Excel: VBA - Replace Cell Value Each Month, valus, number changes

valus, number changes, cell address: Dave, Here is the revision: =IF(A1 IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)) 6,13-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),6-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)))+14+DATE(YEAR(A1),MONTH(A1),1)-2,MONTH(A1),MONTH(DATE(YEAR(A1),MONTH(A1)+1,1))) -- Regards,...

Excel: VBA ranges & Pivot Tables, pivot tables, labels

pivot tables, labels: Well, it wouldn t be a button because that would also reside on the workbook which contains the macro. Probably the easiest is to assign a shortcut key, like ctrl/Shift/G or something. You can press Alt/F8, Select the macro, click Options, put in the assignment...

Excel: VBA Script, stm 1, paris 1

stm 1, paris 1, script thanks: Still confusing. How do you go from PARIS.1, LineWest-A Slot 302 STM-1 1 1-2-6-1 - Trib3 Slot 306 2 Mb/s 16 2M (active) to PARIS.1, Trib3 Slot 306 2 Mb/s 16 ?? Why does this section: STM-1 1 1-2-6-1 - Trib3 Slot 306 disappear?? You...

Excel: VBA script, stm 1, 2m

stm 1, 2m, a2: Solay, If you meant the original data is on sheet2 and you want to process it in place there then: Sub ABC() Dim r As Range, cell As Range, s As String Dim s1 As String, v As Variant, v1 As Variant Dim iloc1 As Long, iloc2 As Long, iloc As Long With...

Excel: VBA for sorting excel with color, yellow cells, rng

yellow cells, rng, excel macro: Tanya, In Excel 2007, they added the ability to sort on color. They also added a SORT object. In the code you first show, that is the sort method of the range object and it doesn t support the new functionality - you have to use the sort object. This...

Excel: VLOCK in excel, vlookup formula, vlock

vlookup formula, vlock, good answer: Alex I have not done this before and I have not been able to figure it out very quickly. The Vlookup formula should work, but pictures are treated differently in excel and I can t get it to work for me either. Rather than waiting for me to figure it out...

Excel: Vlookup Formul, follow ups, formul

follow ups, formul, aidan: I m not 100% sure I have enough information here - how is the data to be imported? Is it being imported to the one master sheet already? Are we to use macros or formulas (especially confused as you mentioned vlookup but it doesn t sound a lookup question?)...

Excel: vba code find cell value, lmax, max value

lmax, max value, column c: C, This code will select the max value in the specified column. In the example, that is column C (the 3 in columns(3)) - change to suit. Sub FindMaxinaSingleColumn() Dim r As Range, res As Variant Dim r1 As Range, lmax As Double Set r = Columns(3).Cells...

Excel: vba conditionally delete rows, absolute reference, b2

absolute reference, b2: Set currentCell = Range( B2 ) Do While Not IsEmpty(currentCell) Set nextCell = currentCell.Offset(1, 0) If InStr(cell.Formula, $F ) + InStr(cell.Formula, $G ) + InStr(cell.Formula, $H ) + InStr(cell.Formula, $J ) + InStr(cell.Formula, $M...

Excel: vba script, ubound, ogilvy

ubound, ogilvy, instr: The second script fails It runs fine for me. It may not give you exactly what you want, but it didn t fail for me using all the test data you have provided. Here is a revision that may help. It produces the output you say you want except it removes...

Excel: vba script, ph5, ocb

ph5, ocb, rh: Solay, This worked for me with your test data. Sub ABC() Dim r As Range, rw As Long Dim s As String, s1 As String, v As Variant Dim l As Long Set r = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) rw = 1 For Each cell In r If InStr(1, cell.Value,...

Excel: vba search for numbers, vbyesno, digit numbers

vbyesno, digit numbers, nbr: Sub SearchNbr() Dim Nbr As String, FirstResult As String Dim FindNext As Integer, i As Integer Dim sheet As Worksheet Dim looped As Boolean Dim result As Range On Error Resume Next Nbr = InputBox( Please enter a 2-digit Number ) If...

Excel: Week no./procedure matrix, cell b2, column headings

cell b2, column headings, rev level: Richard, I still don t understand the nature of your table. You have three ROWS per procedure? And they are called Procedure name , revision level , and hours taken? It sounds as though only one of the three rows will have data across...

Excel: Wirting a Macro, macro book, dde link

macro book, dde link, sheet tab: Bud Horrell, How are you running your flashing program now? You would just run the wav playing program there as well. If you used one of my suggestions then (since you say it is streaming and that doesn t really tell me much, but I will assume it is DDE...

Excel: Workday if function, holiday range, g18

holiday range, g18, bank holidays: I would suggest the easiest way is to test your end of month date against the holiday list =IF(COUNTIF(G10:G18,eomonth(B9-DAY(B9),1)) 0,workday(eomonth(B9-DAY(B9),1),1,G10:G18),eomonth(B9-DAY(B9),1)) where G10:G18 is the holiday range and B9 is the...

Excel: 3 dimensional constant values, relative reference, absolute reference

relative reference, absolute reference, anchor: I don t follow what it is you are trying to do - unless you simply mean you want to select a cell on another worksheet - if so, the EASIEST way is to do it by clicking rather than typing - after entering the = excel knows that you are entering a formula, so...

Excel: Adding cells in different Workbooks, switch windows, task bar

switch windows, task bar, ribbon: Sorry, should have been clearer - I was referring to the one on the task bar, but I forgot that I m now using winows 7 - possibly a better way is to use the ribbon and click on the view ribbon,then switch windows - this gives a list of open workbooks...

Excel: Adding rows and automatic merging, spreadsheet, cells

spreadsheet, cells: I m not sure I follow this, especially as you say you want to sort and filter - having a note in a row BELOW data would surely mean that when sorting or filtering your note could end up out of position? It would be more logical to have a comments column at...

Excel: Adding a sroll bar in a chart in Excel 2007, scroll bar, axis

scroll bar, axis, good luck: Hi Sami, So sorry - I accidentally clicked on the automatic Thank you note option instead of emailing you back. I wanted to tell you that I am unable to answer this question. I did find this website though that has some useful information on adding...

Excel: Adding Time to a Date & Time, analysis toolpak, cell b1

analysis toolpak, cell b1, adding time: In Excel menu, choose Tools, Adds-In, and make sure Analysis ToolPak is checked. Without this add-in Excel does not recognize the function WORKDAY(). In H4:H17, enter holiday dates. In A1, enter 11-Sept-09 8:30 (request date/time). In cell B1, enter...

Excel: Advance Filter, combo box, aidan

combo box, aidan, search terms: If it s a macro, I d probably get it to set the row height to standard for matched cases and zero for non matched ones - at which point the process is easy - though I d want to have some known value seperate the search terms (or ideally have them in a list)....

Excel: Alphabetizing, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: That s easy. Just select all of your data (so if you have 3 columns and 10 rows, make sure it s all selected). Now click on Data Sort and choose which column you want to sort by. Let me know if you have any other questions or comments. Also, be sure...

Excel: Analyzing a column in a large data set, onecell, quick reply

onecell, quick reply, text strings: Sub cleanUp() Dim wksFrom As Worksheet, wksTo As Worksheet Dim rngFrom As Range, rngTo As Range, oneCell As Range Set wksFrom = ThisWorkbook.Sheets( Sheet1 ) Set wksTo = ThisWorkbook.Sheets( Sheet2 ) wksTo.Cells.ClearContents ...

Excel: Array Formula, array formula, precise answer

array formula, precise answer, pivot table: Difficult to give you a precise answer here as I don t know the ranges, AND for three countries I might be inclined to do something like =sumif(FirstSet)+sumif(SecondSet)+Sumif(ThirdSet) (I ve simplified the structure to give you the idea - this isn...

Excel: Array might be needed?, column c, 2 names

column c, 2 names, many names: Press Alt/F11, use Insert/Module, copy this in: option compare text Function Countx(Rg As Range, ColAVal As String, ColCVal As String) As Integer Dim n As Integer, Found As Boolean For i = 1 To Rg.Columns(1).Cells.Count If Rg.Columns(1).Cells(i)...

Excel: Attach particular sheet and mail through lotus notes 8.5 / gmail, open mail, owb

open mail, owb, smsg: Modify your routine like this: Sub SaveSheets() Dim oSh As Worksheet Dim oWb As Workbook Dim vFilename As Variant Dim sPath As String sPath = c:data For Each oSh In ActiveWorkbook.Worksheets oSh.Copy Set oWb = ActiveWorkbook...

Excel: Autofilling dynamic data in a column, correct sequence, question thanks

correct sequence, question thanks, dynamic data: 2 separate macros: Sub EnterFormulas() For Each x In Sheets x.Activate Columns(4).Resize(, 3).Insert lrow = Range( A65000 ).End(xlUp).Row Range( D1 ).Resize(lrow, 3).FormulaR1C1 = =rc[-3]/10000 Next...

Excel: Automatic data extraction?, intersection cells, empty worksheets

intersection cells, empty worksheets, cell a2: ---(Sheet1!B:B) - Why B:B and not $B:$B or B2:B100? Could have been B:B or $B:$B, referring to the whole column, either works since you re filling DOWN, not RIGHT. However, B2:B100 would fill down and change to B3:B101, then B4:B102, etc. so if you DID...

Excel: Automatic Highlight, cell c1, conditional formatting

cell c1, conditional formatting, format feature: Leanne You can use the conditional formating feature to do wht you want. It is found on Toolbar/format/conditional formatting. In the pop up box select formula is and put a formula in that will do the calculation indicating whether your date is beyond...

Excel: activate/deactivate macros, keyboard shortcut, custom toolbar

keyboard shortcut, custom toolbar, aidan: both are possible, and both would need ANOTHER macro to achieve (although it would be the same macro)- what I don t know is what CURRENTLY activates it - I m GUESSING the open event of the workbook - effectively you would need to move the code from that location...

Excel: adding columns in a pivot table, row labels, column labels

row labels, column labels, region managers: Not sure i can help if Excel says too many calcs, but if you can send me the (large) wb that illustrates the issue + your detailed instructions how to replicate the message, I ll see if I can come up with a workaround. Send to me (maybe zip it) at bobumlas@yahoo.com,...

Excel: assigning values to letters in excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Sure. You could make another column to change Y into 1: =IF(A1= Y ,1,0) And then do the same thing for N. Or, you could just count up all of the Ys and Ns as they are using COUNTIF. See: http://www.599cd.com/tips/excel/sumif-countif/?key=AllExperts...

Excel: auto increment by 1, auto increment, invoice number

auto increment, invoice number, cell a1: I see you already tried to do the increment using this line: r.Value=r.Value+1 But you do not tell VBA what r is. To assign a cell to a variable, you must use this syntax: Set r=Range( A1 ) So if the invoice number is in cell A1, you can either:...

Excel: Backup of an excel file, options window, trying to find a way

options window, trying to find a way, xls: First of all I am not Bob but regardless, yes, this will work in 2007. However, just saying ...but I can t seem to get it to work... does not help me a lot. This is like only telling a doctor I am sick and then expect him to make you better from only...

Excel: Basic Excel Vba Help, excel vba help, layout location

excel vba help, layout location, basic excel: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: ByRef type argument mismatch when trying to generate new array, spreadsheet index, depth column

spreadsheet index, depth column, irow: Nick, ============================== You can t upload a file to allexperts. You can contact me direclty at twogilvy@msn.com Sep 21, 2009 (Monday) ============================== This worked for me: Function depArray(reportDate As Date, lithArray...

Excel: Calculate horizontally to end, j5, c5

j5, c5, intellect: Excel luckily doesn t go to infinity so the easiest fix would be to enter the last possible column value - prior to 2007 this would be =sum(k5:IV5)-c5 Blank (non created) cells would not be summed. If you insert entries, you don t need to have the formula...

Excel: Calculating cells with a specific colour and text, colorindex, cell interior

colorindex, cell interior, color index: Dunc, I can add a argument for the text you want to count and then it will return the count for the combination of color and text: Function CountColorText(R As Range, ColorIndex As Integer,s as String) As Long Returns a count of the number of cells...

Excel: Calculating number of days in a date range, date function, month of january

date function, month of january, ogilvy: Danielle, Assume 3/31/09 is in cell J6 then if you want to know the total number of days in that month and the two previous months you can use: =j6-Date(year(j6),Month(j6)-3,0) to explain: Date(year(j6),Month(j6)-2,0) Month(j6)-2 would...

Excel: Calculating unknown future date from a given date excluding weekends and holidays, free microsoft excel, custom vba function

free microsoft excel, custom vba function, question sign: There is no function that will automatically ignore holidays. You will need to write your own function. Here s a tutorial that will help you: Excel makes it easy to count the number of WORK DAYS between two dates. You can use NETWORKDAYS if you only...

Excel: Calculating variables across columns, array formula, sumif

array formula, sumif, statuses: I think I understand. You can use the Conditional Sum Wizard to help you build the formula to sum these items and then change the SUMIF to COUNTIF. The Conditional Sum Wizard is an add-in that helps you build the array formula you will need, IF I understand...

Excel: Calendar, mr abc, column c

mr abc, column c, c lab: Your way of working isn t very effective from Excel s point of view :-) Excel has no built-in formula or function to determine if you have merged cells. I would not merge cells to indicate when a trainer is scheduled. Instead I would put 1 s or x s in each...

Excel: Calendar, mr abc, column c

mr abc, column c, three sheets: Bhavanh Sorry I am having difficulty wading through the information to determine the questions/answer. Having said that--one answer might be to use linking formulas to the worksheets maintained by the others to add the changed data to your worksheet. ...

Excel: Cell Colour Query, colored cells, blue cells

colored cells, blue cells, cell entries: Have cell B1 be the active cell. That is, select cell B1. Then, Excel 2003: Insert/Name/Define Use any name, but I suggested: TheColor in the Refers-To box, type =Get.Cell(24,A1) Excel 2007: Formulas Tab/Define Name Use any name, but I suggested:...

Excel: Cell copy, last question, b8

last question, b8, len: The answer to your question is to enter in cell B8: = S & RIGHT(B7,LEN(B7)-2) Also, you might consider reworking your sheet so that F is in one cell and 10:36 is in a different cell. That way, Excel could recognize 10:36 as a time; Excel won...

Excel: Cell Option, data validation, drop down list

data validation, drop down list, c2: Rahul, The first thing to do is make a Y-detector. Then, apply its result to cause some action to occur. To make Y-detector, you need an OR of all 9 cells, like so: =OR(B1= Y ,B2= Y ,B3= Y ,.... etc) this will be TRUE is any of...

Excel: Cell Referencing, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: If you insert rows or columns INSIDE of the range Excel generally increases the references to that range for other sheets IN THAT WORKBOOK. Of course, the best way to find out for sure is to TRY IT and see. Let me know if you have any other questions...

Excel: Cell ref autofilter on protected sheet, target value, target address

target value, target address, autofilter: James, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Address = $F$3 Then Set r = Me.AutoFilter.Range me.Unprotect Password:= whatever it is If Len(Trim(Target.Value)) 0 Then r.AutoFilter Field:=1, Criteria1:=Range(...

Excel: Cell references - THANKS RICHARD, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use the OFFSET function. =(B1,35,0) This says go 35 rows down and 0 columns across from B1. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL....

Excel: Change colour of cell when expiry date is greater than, credit card expiration, conditional formatting

credit card expiration, conditional formatting, mm yy: You have a column of credit card expiration dates that are TEXT in the format MM/YY. If they re all exactly MM/YY you can do this: Assuming the EXP date is in A1, put this in B1: =LEFT(A2,2) That gives you the MONTH. Now put this in C1: =RIGHT(A2,2)...

Excel: Check if a cell is blank - before checking if another cell has an error, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Sure. Just use the ISBLANK function and nest the IF functions. =IF(ISBLANK(A1),0,IF(ISERROR(A1), Oops ,A1)) Easy enough? Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: Clear duplicates below cell, lrw, advanced auto

lrw, advanced auto, rw 2: Stephen Douglas Test this on a copy of your data. It works from the Active Cell down to the last filled cell in the column in the column of the ActiveCell when you run it. So if you want to process the whole column, select a cell in the first row. If...

Excel: IF CONDITION, format cells, coloum

format cells, coloum, dear mr: In A2 enter YLY In B2 enter 15-Jul-03 Select col D. From menu, choose Format, Cells, Number, Category: Date, Type: Mar-01, OK. In D2, enter =DATE(YEAR(B2),MONTH(B2)+IF(A2= YLY ,12,IF(A2= QLY ,3,IF(A2= HLY ,6,0))),DAY(B2)) The formula will...

Excel: COPY FORMULAS BETWEEN WORKBOOKS - FILTERED, auto filters, row numbers

auto filters, row numbers, exact sequence: Roger, Before you copy or filter the data, put in =row() in the next available column. Then copy the formula down. Select the column and do Edit= Copy, then Edit= Paste Special and select values. How you have the original row number as a constant....

Excel: COUNTIFS function, criterias, formula one

criterias, formula one: Pelambre You can do it with a static number such as your =10 but not with the sign as excel doesn t recognize that in a cell, it turns the value to text. Have a look at this link http://www.mrexcel.com/archive/Formulas/16665.html Richar...

Excel: Code needed, free microsoft excel, blank cells

free microsoft excel, blank cells, question sign: You can LOCK cells and turn on SHEET PROTECTION, but you would have to manually lock the cells yourself between users. Personally, I would probably do this in an Access database. Let me know if you have any other questions or comments. Also, be sure...

Excel: Combine 2 rows in excel, cell1, customer numbers

cell1, customer numbers, customer number: Chris, You did say A3 didn t you. I forgot that when I went off to develop the code. Here is the revision to start at A3: Sub fixdata() Dim r As Range, cell As Range Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range Dim cell1 As Range, res...

Excel: Combine Multiple Rows of Data, free microsoft excel, allexpert

free microsoft excel, allexpert, question sign: And spreadsheet with more than a few hundred rows should be moved to a database. I would recommend using the SUMIF function in a summary table. Just make another table that says: =SUMIF(B1:B100, =12 ) Do this for each of your data items and it will...

Excel: Combobox (activeX control), c values, time robert

c values, time robert, space character: ROBERT, I ran this code and had no problem: Private Sub ComboBox2_Change() ComboBox2.List = Array( A , B , C ) If ComboBox2.Text = A Then [63:76].EntireRow.Hidden = False Range( b63:b76 ).Value = Range( h63:h76 ).Value ...

Excel: Comparing cells and moving data between sheets, cell1, blank cell

cell1, blank cell, sheet1: Kyle, Set r1 = .Range(.Cells(10, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With With Worksheets( sheet2 ) Set r2 = .Range(.Cells(7, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With Both column references in a particular sheet should be the same - so...

Excel: Complex concatenations using intermediary tables, array formulas, presentation table

array formulas, presentation table, entry tables: Assume that the dates are in col J (the first col of addArray) and additives are in col K (2nd col of addArray): Function strAdd(startDate As Date, endDate As Date, addArray As Range) as String dim iRow as Integer dim rngDate as Range, rngAdditive...

Excel: Complicated Sum, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Here s what I would do: replace your tick with a 1, and leave the cell blank (or 0) for no tick . Now, just SUM up that column and you re all set. If you really want to keep your tick marks, then use the COUNTIF function. You can use the SUMIF...

Excel: Conditional Formula in a VLookup, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: How about: IF(X1=0, ,X1) In this example, X1 is your existing VLOOKUP statement. In other words, if the cell contains a zero, replace it with a blank . Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: Conditional IF statement over data range, array formula, tim ward

array formula, tim ward, luis ramirez: Your formula would do it IF you entered it over 16 rows(ie select 16 rows FIRST then start entering the formula, but instead of using the tick or pressing enter, press Ctrl Shift enter to make it any array formula) - BUT you would get 16 results returned which...

Excel: Conditionally Delete First Characters, cell entries, excel microsoft

cell entries, excel microsoft, typical entry: I would PROBABLY do this with vba - a sample of that is here http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.newusers/2008-09/msg00508.html I m happy to create a custom function for you that does something similar - my email if it helps...

Excel: Connecting a diagram to a table, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can refer to values on another sheet as: =SheetName!A1 Where SheetName is the name of the sheet, and A1 is the value cell (or range). In your case, when you create the diagram/chart/whatever just tell the Wizard where the data is, then select a different...

Excel: Consolidation formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You could say: =IF(A1=0, ,A1) This would display a BLANK if A1 is zero. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Copy cell from many sheets to one sheet, case sensative, false application

case sensative, false application, data sheets: This error appears when sh is a worksheet with a name that does NOT appear at all (case sensative) on the weekly votes sheet. For instance, when sh.name is weekly votes itself, if there isn t a cell on the sheet weekly votes that has precisely the value...

Excel: Copy paste Worksheets with conditions., vba code, tab sheet

vba code, tab sheet, sheet1: Hi Jack, I believe the following code does what you describe: Private Sub Workbook_Open() Copies MPR and Form64 sheets to MPR 2009 and Form64 2009 workbooks the first time this workbook opens in a given month. Dim LastOpened ...

Excel: Copy similar data (specific columns) from multiple excel files into one, u50, u10

u50, u10: I don t understand. What do you mean by ...a different excel. A new workbook? Is there any matching of data between TicketDetails and RawData? How? Are you wanting a new workbook with 2 sheets (TicketDetails and RawData) which contains ALL the other workbooks...

Excel: Copying cells after a filter, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Once you apply a filter, you can t copy and paste it, but you can select the cells and then Autofill to the right one column. That should copy over JUST the records in the filter. You should, however, SERIOUSLY consider using a DATABASE like MS Access any...

Excel: Copying cells after a filter, left arrow, paste command

left arrow, paste command, alpha bravo: easiest way to do this is to use a blank column - in which you enter =if(a2= Bravo ,a2,b2) (this assumes row 2 as I m sure you realised) Drag this down, then copy the results and click on B2 - then paste special, values. You can now delete the formula...

Excel: Copying columns but admitting certain values., blank worksheet, randoms

blank worksheet, randoms, random number: a couple of options - firstly, data, filter, autofilter - then choose custom and select does not equal 0 - this will give you a list which you can copy but the data won t be continuous and you d have to reapply the filter each time you wanted a list. secondly,...

Excel: Copying by dragging down formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You need to use ABSOLUTE REFERENCES in your formulas. A1:B1 should be $A$1:$B$1 Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Copying Order of a Formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Don t use copy and paste. Use AutoFill and give it enough to see the pattern. For example, if you type in the following into cells A1, A2, and A3: 3, 5, 7 Now select all three cells and AutoFill them across the sheet, Excel will see the pattern and...

Excel: Copying & Pasting (top down format to left to right format), sheet names, z1

sheet names, z1, z2: You can use a formula like this -- first select A1:Z1, for example, in the summary sheet, enter =TRANSPOSE(Sheet1!A1:A26), then enter it with Ctrl+shift+enter Note the 26 is because you selected 26 columns. Adjust as necessary. For the 2nd row you d first...

Excel: Countif Formula - I think, excel, countif

excel, countif: You are right, you can use COUNTIF. For example: Greater than 0 but less than 5: =COUNTIF(A1:A50, 5 )-COUNTIF(A1:A50, 0 ) Greater than 0 but less than 10: =COUNTIF(A1:A50, 10 )-COUNTIF(A1:A50, 0 ) Greater than 10: =COUNTIF(A1:A50, =10 ) Hope this...

Excel: Counting Blank Cells..., blank cells, sumproduct

blank cells, sumproduct, peson: If I follow this correctly, it s basically a count IF requirement - you are counting IF it s the right person AND its blank - you can do this using SumProduct - =SUMPRODUCT(--(A1:A4= a ),--(ISBLANK(B1:B4))) where A1:A4 relates to your name data, a...

Excel: Counting in multiple columns, array formulas, excel user

array formulas, excel user, score: =sumproduct(--($A$2:$A$10= A ),--($B$2:$B$10=80)) for the first part and =sumproduct(--($A$2:$A$10= A ),--($B$2:$B$10 =70))+sumproduct(--($A$2:$A$10= B ),--($B$2:$B$10 =70)) for the second part - NOTE that as this stands it includes the first part....

Excel: Counting number of times a combination appears in different rows, sumproduct, ogilvy

sumproduct, ogilvy, b1: Luke list of values in column A value 1 (in B1), Value 1 (in C1), Formula (in D1) this is the formula =IF(B1=C1,SUMPRODUCT(((LEN($A$1:$A$30)-LEN(SUBSTITUTE($A$1:$A$30,B1, ))) =2)*1),SUMPRODUCT(ISNUMBER(FIND(B1,$A$1:$A$30))*ISNUMBER(FIND(C1,$A$1:$A$30))))...

Excel: Creating a VBA script to change the drop down selection of a pivot table, target text, target value

target text, target value, set target: Cornell, that is what this code does. You right click on sheet2 and select view code. then put in this code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub MsgBox...

Excel: Cross referencing occurances., SUMPRODUCT

SUMPRODUCT: Dominika: This can be done with the SUMPRODUCT formula. Based on your example I suggest the following formula: =SUMPRODUCT((A1:A10= attack )*(E1:E10= right )*(1)) Of course the above formula assumes only 10 rows of data - thus, just expand the formula...

Excel: calulating overdue and underdue dates, cell references, column c

cell references, column c, magic number: Gareth, You can perform easy arithmetic on dates. Dates are stored as special serial numbers , as Excel calls them. But don t be afraid! This magic number is simply the number of days since an initial (and fictitious) date: Jan 0, 1900. So as of...

Excel: If cell A1=A then delete data in cell B2 and repeat, cell b2, cell a1

cell b2, cell a1: Tyr this: Sub Delete_Data() Dim X As Long Dim Counter As Long On Error GoTo Err X = 1 Do Until Range( A1 ).Offset(X - 1, 0) = If UCase(Range( A1 ).Offset(X - 1, 0)) = A Then Range( A1 ).Offset(X - 1, 1).ClearContents ...

Excel: cell reference in VBA, relative cell reference, point thanks

relative cell reference, point thanks, target: John, If you are using the change event (and it sounds like you are - if not, you should be using change, not selectionchange), then Target holds a reference to the cell that triggered the event. So you want to use arget instead of Activecell --...

Excel: change cell color when, conditional formatting, a12

conditional formatting, a12, a5: You are on the right lines - under conditional formatting, choose the FORMULA option - this then allows you to use a formula to dictate the condition =$A$12 $A$5 This condition will evaluate to TRUE if a12 is greater than A5 and therefore the formatting...

Excel: checking conditional formatting, conditional formatting in excel, sanity check

conditional formatting in excel, sanity check, allexperts: I m sorry, but I know of know utility which will list all the conditional formatting settings in a workbook. I can probably write such a routine but that d be beyond the scope of AllExperts and I simply don t have the time to dedicate to writing it -- it would...

Excel: comapring columns and output out of that in a same cell, visual basic for applications, unique records

visual basic for applications, unique records, t1 t3: Sub makeOutput() Dim rngFrom As Range, rngTo As Range, rngCheck As Range Dim strOutput As String Set rngFrom = Range( a2 ) Set rngTo = Range( d2 ) rngTo.Range( a1:b1 ).EntireColumn.ClearContents Do Until rngFrom = If IsError(Application.Match(rngFrom,...

Excel: combining a group of rows into one cell, apple orange, alice jane

apple orange, alice jane, bval: Here s a macro to do it: Sub Combiner() n = 1 BVal = Range( B1 ).Value Range( c1 ).Value = Range( A1 ).Value & For i = 2 To Range( A1 ).End(xlDown).Row If Cells(i, 2).Value = BVal Then NextOne: Cells(n, 3).Value...

Excel: combo box list, variant array, lct

variant array, lct, combobox: Hi Chris, Yes you can do that, but you would need two controls: a textbox and a listbox or combobox. You use the change event of the textbox to add the items to the list. I have a form with TextBox1 and ListBox1 and use this code to handle things: ...

Excel: comparing two worksheets and merging when matches are found, asking this question, aidan

asking this question, aidan, email: The COUNTIF worksheet function would enable you to determine if one value exists in another location, and vlookup would bring the data across - once done, you can replace the formulas with values. =if(countif(SecondSheetRange,YourCell) 0,vlookup(YourCell,SecondSheetRange,2,false),...

Excel: complicated lookup, logical tests, shipping invoice

logical tests, shipping invoice, shipping zone: You can do this with a trick using SumProduct which allows a multiple condition sumif(which if there is only one possible match becomes a multiple condition lookup) =sumproduct(--(ShippingService=type),--(shippingzone=zone),--(packageWeight=weight),rate)...

Excel: conditional pop-up messages, target value, data validation

target value, data validation, sheet tab: Sandy, I don t see where data validation could do this isnce you would have to have an invalid value to raise the message and then it wouldn t let you enter the value. You could do it with a macro right click on the sheet tab and select view code...

Excel: connection between workbooks with VBA, bk1, vba

bk1, vba, ogilvy: Kuba, If there are only two workbooks, then I guess one workbook contains the code. For that workbook, you can get the name and path with Thisworkbook.Name thisworkbook.path or to get both together thisworkbook.Fullname ThisWorkbook is a predefined...

Excel: converting letters to numbers, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You wouldn t have to alphabetize it if you use the fourth VLOOKUP parameter. If you set it to FALSE, then VLOOKUP will find an exact match and the list doesn t have to be alphabetical. Let me know if you have any other questions or comments. Also, be...

Excel: converting numbers in Excel, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Absolute Value =ABS(A1) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts Cordially, Richard...

Excel: copying sheets/formulas, self reference, a10

self reference, a10, thanks in advance: probably the easiest fix is to do what you are doing, then edit, links and change the source - to the destination file, so it becomes a self reference (and therefore ceases to be a link) Sorry for the delay in replying - somehow your question got lost in...

Excel: Data merge, ogilvy, data sheets

ogilvy, data sheets, data sheet: Hello Marty, You can use vlookup. Say you want the post code to appear in column AC in Sheet2 (it can be any column, but I will use AC for example) in Sheet2 cell AC2 put in this formula =IF(ISNUMBER(MATCH($B2,Sheet1!$B:$B,0)),VLOOKUP($B2,Sheet1!$B:$AC,28,FALSE),...

Excel: Data validation for multiple criteria, cell b2, data validation

cell b2, data validation, revenue assurance: Please send me a sample wb so I can show AND explain rather than just explain which is more difficult -- send to me at bobumlas@yahoo.com, use subject of AllExpertsQ and I ll send back your solution with explanation. Please repeat what needs doing...

Excel: Decimal places based on integer in cell, conditional formatting, format button

conditional formatting, format button, hermanson: Ed, I don t know what Bill Hermanson provided in his answer, so I can t comment on his solution. I applied conditional formatting to cell H6 and made the number of decimal places dependent on the value in I6. You can only do this in Excel 2007 as I...

Excel: Default file location in Excel, application programming interface, microsoft knowledge base

application programming interface, microsoft knowledge base, location changes: The MAPI folder is supposed to be their for mail processes (messaging application programming interface) but that doesn t explain why Excel would be looking their - possibly a setting in the startup location, or possibly an add-in misbehaving? Other than...

Excel: Delete rows where their Column B entry = 00000, false selection, rowcount

false selection, rowcount, fileformat: I am curious as to why you did not get IT Services, which is who recorded this macro for you, to fix what they did. Regardless, based on the info you provided: Dim R As Range Dim X As Integer Dim RowCount As Integer Dim FirstC As Range Set R =...

Excel: DOLLAR puts the dollar sign to the right of the amount, target cell, currency symbol

target cell, currency symbol, time susan: Susan, I would go into the control panel found under Settings in the start menu in most versions of windows. Once in the control panel, look for regional settings. Look at the settings for currency and see if there is anything unusual there - like the...

Excel: Document not saved, query document, lcid

query document, lcid, excel workbook: There are so many possible reasons for this. The most common: You re saving to a directory which is protected You (or someone) have the file open elsewhere (separate instance of excel) Out of disk space for other reasons, check: http://support.microsoft.com/search/default.aspx?qid=10165&query=document+not+saved+excel&catalog=LCID%3D1033&mode=...

Excel: DSUM - swtuching from 123 to Excel, column headings, cell a2

column headings, cell a2, employee numbers: EXCEL also has a DSUM function. The primary difference is that you can not embed the criteria argument in the function as you have done and as you could do in 1-2-3. You have to create a range, with appropriate column headings and, of course, the criteria...

Excel: Duplicate, excel, conditional format

excel, conditional format: You can use the menu Format- Conditional Formatting for this. Select your range of data, and enter a formula like this as a Formula Is condition: =COUNTIF(A:A,A1) 1 I am assuming that the values are on column A, and that the first cell of the range is...

Excel: Duplicating Cells, cell b1, cell a1

cell b1, cell a1, richard florida: Alex I am not sure how your data is being input, but assuming it is being input into column A, then a formula in column B row 1 such as +A1 will bring over the data into cell B1 as you are inputting into cell A1. Of course this formula can be copied down...

Excel: Dynamic Copy Ranges, data cells, copy worksheets

data cells, copy worksheets, column c: Kevin, Dim lastrow as Long with Worksheets( Daily ) Lastrow = .cells(rows.count, C ).End(xlup).row .Range( AA4:AK & lastrow).Copy _ .Cells(Rows.Count, AM ).End(xlUp)(2) End With If it will always be pasted to AM4 (AA4 ??) then Dim lastrow...

Excel: date plus time plus hours worked equals, numeric representation, company holidays

numeric representation, company holidays, workdays: Brett: I m not 100% certain, but it sounds like you are adding dates/times, but want to ignore weekends. There is a function called WORKDAY that will add a specific number of workdays (ignoring Sat and Sun) to a starting date. The WORKDAY function also...

Excel: difference between dates Excel 07, calendar months, decimal value

calendar months, decimal value, a1: I d tend to use YEARFRAC to handle this - this returns a decimal value indicating the exact fraction of time - I m not aware from your question how this data is to be used, so either go with yearfrac s result as returned IF you need the period of time, or...

Excel: How to direct Excel to select from the intersection in a table, cell a1, index formula

cell a1, index formula, column number: Dorell The formula to look up the value at the intersection of the row and column is-- =INDEX(C9:G15,+MATCH(B22,B9:B15),+MATCH(B21,C8:G8)) It looks a little complicated but-- The reference in the formula to C9:C15 is the range of the rows and columns...

Excel: Email list cleanup, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use the MATCH function to determine whether or not that address appears in column A. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Embed an "OR" into a "COUNTIF", free microsoft excel, countif

free microsoft excel, countif, question sign: Try: OR(CountIf(a:a, x ),CountIf(a:a, y )) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts Cordially,...

Excel: Error message in Excel 2003, microsoft knowledge base, data validation

microsoft knowledge base, data validation, exit design: Bri, I didn t want to leave you hanging, so I did some research on your question. It appears that a lot of people have had this error, but I don t find any specific solutions. The only documentation of it I can find on the Microsoft Knowledge Base site...

Excel: EXCEL 2007 formula evaluation question followup, evaluation question, formula evaluation

evaluation question, formula evaluation, correct evaluation: Jim, Wow, that is really odd. I took your spreadsheet and wrote SUM(D45:E45) in one cell and =F44-A45+B45 in another and compared them.... they are NOT the same. I expanded both of these to ELEVEN decimal places. Amazingly, the =F44-A45+B45 equation...

Excel: EXCEL 2007 formula question, free microsoft excel, cell a1

free microsoft excel, cell a1, question sign: Depending on how many items you have, you could do this with an IF statement. Anything else would require a macro. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: EXCEL Compare, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use the MATCH function. If the value exists, it will return the index (location) of the value, otherwise it will return an NA. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: EXCELL TIME FORMAT, excell format, valid entries

excell format, valid entries, time format: not sure if the brackets are part of this but I ll assume they are - probably the easiest method is to do three search and replaces replace a) with [space]am (just type a space where I ve put [space] replace p) with [space]pm replace ( with nothing ...

Excel: Excel 07 Conditional Formatting, formula g1, conditional formatting

formula g1, conditional formatting, hth: First, color the range red (default) Next, with the range selected, and assuming G1 is the ACTIVE cell, use Home/Conditional Formatting, New Rule, Use a formula: =AND(G1 =TODAY(),G1 TODAY()+60) then use the patterns for Yellow, then go back to New Rule,...

Excel: Excel 2000 formulas, free microsoft excel, excel 2000 formulas

free microsoft excel, excel 2000 formulas, question sign: You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more information on how to use the IF function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts...

Excel: Excel 2003, using ms office, computer issue

using ms office, computer issue, excel expert: Amol, This is probably a computer issue and has nothing to do with Excel specifically. I suggest you try a computer expert instead of an Excel expert. The only thing that could cause slow operation in Excel only, is if you have a rather...

Excel: Excel 2003 Formula to calculate annual due dates, analysis toolpak, logical test

analysis toolpak, logical test, functions in excel: I m not sure I ve got enough to go on here - you mention annuals, and also 4 years indicating that this may be significant. It might be worth taking a look at the xldic01.xls file stored at http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls as this...

Excel: Excel 2003 - Mandatory Fields, excel spreadsheet, mandatory fields

excel spreadsheet, mandatory fields, msgbox: Press Alt/F11, double-click the ThisWorkbook you ll see in the project window, put in this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For i = 1 To Range( A65536 ).End(xlUp).Row If Application.CountA(Cells(i,...

Excel: Excel 2003 remove cells containing text, index column, menu toolbar

index column, menu toolbar, sort key: Nancy, You cannot remove cells with a formula. TO do such a thing, a formula would have to be able to delete itself. A better answer to your question depends on what you mean by remove . This is not an Excel term... there is no remove...

Excel: Excel 2007, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: No, but you can change the default file location by clicking on Office Excel Options Save. Change the Default File Location setting there. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my...

Excel: Excel 2007, negative signs, column c

negative signs, column c, richard florida: Ruth The minus behind the number indicates that the number is being recognized as text by excel. What we need to do is convert it to number format. The positive numbers or those that do not have a minus behind them may also be text format if so they will...

Excel: Excel 2007 version - backup workbook, backup file, select tools

backup file, select tools, drop down menu: Wynneth, How to make a backup file. 1. Open the file you want to have a backup of. 2. Select File 3. Select Save As This will bring up the Save As popup menu. 4. In the upper right hand corner of the popup menu select Tools 5. Select...

Excel: Excel, canada column, b1 and b2

canada column, b1 and b2, word time: John, Your basic question here seems to be about circular references [CR]. You REALLY don t want to use a circular reference here. But, there IS a way to make Excel allow CR s. What you have to do is set the calculation method so...

Excel: Excel, hh mm ss, cell d1

hh mm ss, cell d1, minute time: Rachel, In order to multiply a time by an hourly rate, you must convert the minutes portion of the time to a fractional hour, then add it back to the hours. Then multiply the result by your rate. =(HOUR(time)+ MINUTE(time)/60)* hourly-rate ...

Excel: Excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, hidden columns: I can t think of an EASY way to do this in Excel. In Microsoft Access (a database program) you could create a combo box with hidden columns, but I m not sure if Excel would support that. Let me know if you have any other questions or comments. Also,...

Excel: Excel addition corresponds to date, excel, sumif

excel, sumif: You could use the SUMIF formula for this, something like: =SUMIF(A1:A100, &C2,B1:B100)-SUMIF(A1:A100, &C1,B1:B100) I am assuming that the dates are on column A, the values or column B, and the start and end date on cells C1 and C2. Hope this helps,...

Excel: Excel attachment & Lotus notes123, mail, owb

mail, owb, smsg: Maybe you can combine the code below (last sub does the sending of the active sheet) with the code I already sent: Function SendMail(vEmailSendTo As Variant, sEmailSubject As String, sBody As String, sFileToAttach As String) Dim objNotesSession As...

Excel: Excel auto numbering, invoice number, xls

invoice number, xls, novice: Not sure this works well for a novice as it would need a macro - but see http://www.aidanheritage.byethost3.com/excel/invoice.xls which is already programmed - this might help! let me know if you need further help and I ll do my best for you. NOTE...

Excel: Excel bill spreadsheet I created, cell b2, format painter

cell b2, format painter, seperately: Select cell B2 and then define a name, like yellow, to be =GET.CELL(38,Sheet3!A2)=6 In cell B2 enter: =OR(yellow,NOW()=0) For the total (in A9?), enter: =SUMIF(B2:B8,TRUE,A2:A8) Now the first time you change a cell to yellow nothing will happen unless...

Excel: Excel COUNT formula, letter reply, full time job

letter reply, full time job, excel count: This is in response to your comment: The answer was not helpful at all. I already knew what Excel function to use, I needed help writing the specific wording . Thank you for letting me wait two and a half days to receive what was as informative and useful...

Excel: Excel - Conditional Formatting, excel conditional formatting, maximum value

excel conditional formatting, maximum value, spreadsheet: As you guessed it s something else - conditional formatting would set the FORMAT of the cell based on certain conditions being true - so you could have the maximum value in a-c set to a different colour for example. In your case,it s simply a formula as you...

Excel: Excel chart range with vba, spreadsheet template, variable assignment

spreadsheet template, variable assignment, somtimes: You can use the RANGE command or the cells command - I m not sure what you are doing with the range, but myVar=1000 in reality, would be your variable assignment range( A1:A & MyVar).select selects a1:A1000 which is the result of concatenating the...

Excel: Excel - color cell based on increase/decrease in data, conditional formatting, necessary code

conditional formatting, necessary code, two colors: It is certainly possible - the only issue is that this would ALWAYS kick in Public oldval, myval Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 1 Then oldval = myval If Target.Value oldval Then Range( A & Target.Row).Interior.ColorIndex...

Excel: Excel data sorting, solution area, macro code

solution area, macro code, whole lot: Is this dropdown from a filter? If so, clearing the filter is equivalent to having ALL show up -- if it s really a filter, selecting ALL would show all the rows which contained the text ALL . Is this a real dropdown which runs a macro? What s your macro...

Excel: Excel data sorting, solution area, vlookup formula

solution area, vlookup formula, richard florida: Sathya It is difficult to read into your questions exactly how you are arranging your data. etc. but it appears that you could use a vlookup formula to look up the selection EG ALL function and thereby return the correspnding data from the rows you need....

Excel: Excel Formula, blank cell, excel formula

blank cell, excel formula, g24: These two lines of code: Range( F9 ) = Range( F15 ) = put a blank character, like pressing the space bar and then pressing ENTER, in those two cells and your formula looks for a blank. Try changing the code to: Range( F9 ) = Range(...

Excel: Excel Formula - Grabbing data from multiple worksheets by copying formula, excel formula, trying to find a way

excel formula, trying to find a way, a5: If your sheets are named Sheet1, Sheet2, etc, then you can do it with dragging the fill handle by setting up the reference this way: =INDIRECT( Sheet & ROW(A1) & !A5 ) The ROW(A1) is just to get the 1 so the formula in the first cell is effectively =Sheet1!A5...

Excel: Excel - Formula for Looking up max and min values from specific groupings, excel, large

excel, large: If the line is on column B and the data in columns C and D, you could use some array formulas like: Earliest: =LARGE(C2:C8*(B2:B8= LINE 2 ),COUNTIF(B2:B8, LINE 2 )) Final: =MAX(D2:D8*(B2:B8= LINE 2 )) Enter them with Ctrl+Shift+Enter. Change the ranges...

Excel: Excel Formulas, a3 b3, excel formulas

a3 b3, excel formulas, d3: Amy, Use the SUMPRODUCT function for this. It is designed to take the sum of products, just like you want. Read how to use this in the Function Reference (which appears when you type the equal-sign in any cell). It explains the syntax much...

Excel: Excel Formulas, excel formulas, ogilvy

excel formulas, ogilvy, brief description: Taryn, The only one I am aware of is one done by Peter Nonely. It says it was updated in Dec 2008, so it may contain some of the new functions from Excel 2007 - I can t say for sure. I also can t say it includes every function, but it includes a lot....

Excel: Excel formula, excel, consolidate

excel, consolidate: To consolidate information, I have found that the articles on this page explain it better that I can do on this box: http://www.rondebruin.nl/tips.htm Go to the Copy/Paste/Merge examples, and you have a couple of articles on how to do it with macros, or...