array formula, array formulas, curly brackets: Pete both min and max work as array formulas =MIN(IF((MONTH($A$1:$A$500)=3)*($B$1:$B$500 ),$B$1:$B$500)) entered with Ctrl+Shift+enter rather than just enter. If entered correctly it will appear in the formula bar enclosed in Curly Brackets{}...

Excel: Flagging due dates, due dates, g1

due dates, g1, completion date: Hi Helen How are you? I am probably doing this too simple but please have a look at the following 1. Type in G1 =now() 2. Type in A1 the name of the component, in B1 the date of inspection 3. Type in C1 =IF(B1=$G$1, **** , ) Of course you...

Excel: Follow on from last question, last question, ogilvy

last question, ogilvy, step 1: Steve Davis, yes and no for i = cells(1,columns.count).End(xltoLeft).column to 1 step -1 the first 1 says to find the last column in the First (1) row. You can specify any row I like to use the more general cells rather than range and columns.count...

Excel: Formatting Time on imported data, time entries, richard florida

time entries, richard florida, practical solution: Melanie I cannot duplicate the problem but I suspect that the cells are text, not number formating. To convert the cells to numbers you can use the copy paste special multiply-this will convert the cells to numbers. To do this--put the number 1 in...

Excel: Formula to calculate attendance., png format, paint program

png format, paint program, database layout: Your keyboard has a PRINT SCREEN key somewhere. When you press that, it puts a picture of the screen into memory. Now open some sort of paint program and paste into that program, then crop down to just what you need and save as a JPG or something for uploading...

Excel: Formula for Pivot Table, pivot table, cell position

pivot table, cell position, excel formula: Mark You can t use a formula to conditionally insert a pivot table. You can write VBA code to insert a pivot table, triggered by an activity, and conditional on some aspect of your spreadsheet. Alternatively, you could have formulas that behave like a pivot...

Excel: Formulas for Timesheet, hourly appointments, necessary thanks

hourly appointments, necessary thanks, 599cd: I would use a little VLOOKUP table for this. First, here s a tutorial on VLOOKUP you should watch: http://www.599cd.com/tips/excel/vlookup/?key=AllExperts Now what I would do in your case is set up a lookup table that contains all of your different rates....

Excel: Enable grouping activity on protected sheet, vba

Excel: Enable grouping activity on protected sheet, vba

Excel: formula in a column using VB, column c, navin

column c, navin, aidan: difficult to follow from the picture (my email if it helps is aidan.heritage@virgin.net), and I m not clear what you mean by till the values are present - does this actually mean NOT present? Also not clear what is meant by the formula - what did you...

Excel: IF function, destination cell, logic error

destination cell, logic error, cell a2: Ok, in that case I d use a MATCH function to get the number, and then an index function to translate =INDEX($C$1:$O$1,1,MATCH(C5,$C2:$S2,0)) where C1:O1 are the location names, C5 is the header row of the translation table (so your row 1 col3), C2:S2...

Excel: Handling #N/A error when taking AVG( ), sheet names, error values

sheet names, error values, game 1: Sarah, Here is another way to attack the problem (that I forgot to mention) if your sheet names are really Game1, Game2, etc or have any type of naming convention that would lend itself to this solution. =SUM(SUMIF(INDIRECT( Game &{1,2,3,4,5,6}&...

Excel: HELP: SEARCH function using range instead of single value, array formula, countif

array formula, countif, word cat: marcel, this worked for me: =IF(OR(ISNUMBER(SEARCH($O$2:$O$5000,H2))), YES , NO ) this must be committed/entered with Ctrl+Shift+Enter (hold down the ctrl and shift keys, then hit enter) because it is an array formula. Also, you must have words...

Excel: Hide and Unhide Combo Box, oleobj, combo boxes

oleobj, combo boxes, data validation: Jay, well, again, you haven t told me what type of combobox or what version of excel for activeX you can do Sub showNamesActiveX() For Each oleobj In ActiveSheet.OLEObjects If TypeOf oleobj.Object Is MsForms.ComboBox Then MsgBox oleobj.Name...

Excel: Hiding cells based on cell format, blank cells, vba procedure

blank cells, vba procedure, blank cell: Steve Davis, When you make that comparison, the value of the cell is coerced into a number to make the comparison. That coercion makes the cell equivalent to zero. So it isn t a formatting problem since the value property usually ignores formatting. ...

Excel: Hyperlinking in the same workbook in excel, vlookup formula, richard florida

vlookup formula, richard florida, sheet1: Cindy Can t quite tell from your question if you want the entire worksheet to appear or if you want just the contents of the single cell. But the formula for the single cell is =Sheet2!A1 This formula might be in a cell in sheet1, or sheet 3, or...

Excel: hours and rate to get wages., free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, cell c3: 4:23 is 4 hours and 23 minutes which Excel stores internally as a FRACTION of a whole day. So, if you have 4:23 in cell A1, then in cell B1 you can put: =A1*24 This will show you 9:12, but don t worry about that, change the FORMAT of the cell to a NUMBER...

Excel: IFFormula, gain value, column c

gain value, column c, loss column: Trisha I will make some assumptions and give you a formula based on those assumptions Assume column A represents, and is headed, Date. Assume that each of you has three columns Trisha has the first three columns column B,C,D Column B is headed...

Excel: Ignore Zeros in Top 10 Reoccuring Text, vlookup formula, cell d1

vlookup formula, cell d1, text values: Stephen, I just got errors in the formulas in E1, F2, and G2. So I got rid of those and put this in E2 =IF(AND(ISNUMBER(SMALL($C:$C,ROW($A1))),ROW($A1) =$D$1),INDEX($A:$A,MATCH(SMALL($C:$C,ROW($A1)),$C:$C,0),1), ) then drag filled it down for...

Excel: Import selected txt on Macro, filedialog, txt files

filedialog, txt files, recognise: Alvaro, assuming FICHERO contains the information you need, then it would be With ActiveSheet.QueryTables.Add(Connection:= TEXT; & FICHERO, Destination:=Range( $A$2 )) so you have to concatenate the value held in FICHERO -- the way you have it...

Excel: Incorporating pivot tables in VBA Macro Excel (2), pivottables, proper headers

pivottables, proper headers, vba macro: Irma, You show the source of the data for your Pivot Table as LIWD!C1:C5 . That is 5 cells in a single column. But your code is trying to work with at least two Pivot Fields which would not be possible in a one column source. Just to be sure, I created...

Excel: Insert chr(10) every after ever n number of characters in string, array elements, cell a1

array elements, cell a1, string s1: Kevin, basically yes - however, the way my macro works, it is doing everything character by character. So it would probably need some modification to avoid splitting words. Perhaps when it is handling a space character, look ahead and see if there is...

Excel: Invoice Automation, cell a1, invoice template

cell a1, invoice template, question thanks: My coding already increases the number by one - it runs on the OPEN event of the workbook - the SAVEAS coding which you have recorded gives you the syntax, just change the string given to a string variable - so something like C: empInvoice & range(...

Excel: How to ignore blanks, blank cell, time james

blank cell, time james, first test: Hello James, Thank you for your question. Try the formula below, it should leave the cell in the C column blank if the cell in the A column is also blank. For example, if A4 is blank, then C4 will also be blank. I used a nested IF statement to first...

Excel: insert equation into legend, legend text, subscripts

legend text, subscripts, quotes: =SERIES( SERIES NAME GOES HERE, IN QUOTES ,...) Seems like sub/superscripts aren t allowed in the legend. To confirm, I made a superscript text in the sheet and then created a chart and the legend did NOT have the superscript. You could fudge it with a...

Excel: invoicing via email, excel sheet, notebook computer

excel sheet, notebook computer, pertinent data: Since you repeated the same information again, I have no choice but to repeat, based on the information provided, there is no reason you should not be able to open/edit the Excel sheet sent back to you by the technician if it s STILL an Excel file. If he...

Excel: jumping typeing letters, windows xp professional, windows internet

windows xp professional, windows internet, internet explorer: The keyboard is a function of the operating system, and as you have mentioned typing in (I assume) Windows Internet Explorer that would seem to bear that out - there MAY be an issue with the keyboard, or with the driver for the keyboard - but I m not a hardware...

Excel: Lookup Function: Worksheet Selection based on Value, cell a1, worksheet selection

cell a1, worksheet selection, b1: You could make use of the INDIRECT worksheet function to pull the sheet name you want to use from another cell. Say Cell A1 contains the text Sheet1 Cel B1 contains your lookup value and you want to do a look up like this: =VLOOKUP(B1,Sheet1!$A$1:$B$100,2,False)...

Excel: label (form control) to cell without vba, cell b1, object dialog

cell b1, object dialog, index command: Clarkson, ============== start of revision ========= whoops - I misread your post. Disregard all the stuff about a dropdown box below. I thought you were asking about a Forms control in general and I have since reread your posting and now realize you...

Excel: look up, e mail address, lumber type

e mail address, lumber type, radiata pine: Hello Billy, Thanks for the follow-up. I will give you the formula I plan to use. You may be able to get it to work yourself. If not, feel free to e-mail the workbook to me, and I will have a look at it. My e-mail address is below. The formula is:...

Excel: How to look up or match two criteria - one vertical and one horizontal, e mail address, exact layout

e mail address, exact layout, store names: Hello Missy, Thank you for your question. This certainly sounds like something Excel is capable of doing. However, since I am unable to see your data, it is not possible I can provide a solution without knowing more about your data and seeing the exact...

Excel: Macro to make dynamic line graph, line graph, cell addresses

line graph, cell addresses, dynamic charts: A picture of the resulting chart would have been good for final comparison. I ran your existing macro on the data you posted and got an odd looking chart. I then converted the macro into a dynamic macro and ran this, getting the exact same odd-looking...

Excel: Macro Help - Find the cell with the highest value in a column, range selection, column c

range selection, column c, max value: Wayne, assume you have a named cell with the name that holds the number of rows to fill. Assume that if that number were 10 as an example, then the formulas would be filled in A1:P10 (so row 1 is included in the count of the number of rows. Sub AAA()...

Excel: Macro Question, cell d5, target value

cell d5, target value, input cell: * Paste the code behind any worksheet where the input cell is. If it changes, put the code behind that sheet instead of the original sheet. change: Set myrangefrom = Range( R:S ) to be more specific, and in fact really we should always qualify...

Excel: Mandatory Cells (again), question column, allexperts

question column, allexperts, hi bob: the code as is checks for a 1 in column B. If you want it to ensure there s something in X when ANYTHING is in B: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For i = 1 To Range( A65536 ).End(xlUp).Row If Len(Cells(i,...

Excel: Merge Madness, cat sat on the mat, exact situation

cat sat on the mat, exact situation, ogilvy: Paul Sub ABC() Dim bk1 As Workbook, bk As Workbook Dim spath As String, sname As String Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, lastrow As Long, lastcol As Long Set bk1 = Workbooks( three.xls ) Set sh1 = bk1.Worksheets(1) spath =...

Excel: Microsoft Excel, format cells, decimal points

format cells, decimal points, column width: Hello Rohit, I am sorry, but I failed to answer your question properly. To display a number this large, you will have to format the cell as Text, and Excel will display the entry exactly as entered. You will still be able to perform mathematical...

Excel: Missing components error message, microsoft home publishing, encartia

microsoft home publishing, encartia, purchasing a new computer: AllExperts is a good place to come for answers, but at top level you can navigate through different categories to find one that fits your question - thanks for the extra information - this page http://support.microsoft.com/?kbid=245829 MAY give a clue...

Excel: macro for excel to internet based portal, subject report, text boxes

subject report, text boxes, txt files: Excel can certainly do a SaveAs text file - so it could create a file with the data you need. What isn t clear is what the upload process is - if this is as simple as SaveAs then the macro will have no problems -otherwise, I would need to know the upload...

Excel: macros tested and works at home but not at work., repetitive works, array formulas

repetitive works, array formulas, paypal account: Can t help without actually seeing the problem workbook. Send me a copy and tell me what cell(s) you re trying to conditionally format. Send to bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what you re trying to do. ==================================...

Excel: master spreadsheet, master spreadsheet, unnecessary complications

master spreadsheet, unnecessary complications, vba: Leann The best way is to keep your similar data in one place. There are lots of tools e.g. sorting, filtering, subtotaling, pivot tables, formulas, etc to deal with data that is in rows and columns. Once you spread it out on multiple sheets, you usually...

Excel: math formula/calculation, math formula, excel sheet

math formula, excel sheet, escalation: * Do you want to have just one result, in one cell, with one formula, that shows you the total value after a given number of years? (if so, what is the given number of years?) There are probably a few ways to do this, but how about treating as a security...

Excel: microsoft Excel 2007, Sixthsense Excel

Sixthsense Excel: Hi King, I am not sure whether this is what you are looking for. Press ALT to activate the menu items and press P to move on to Page Layout and press I to select Print Titles. Alt+P+I Place the cursor In Rows To Repeat at top: field and select...

Excel: microsoft excel, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: You want to use CONDITIONAL FORMATTING. Here s a tutorial that explains what that is: http://www.599cd.com/tips/excel/conditional-formatting/?key=AllExperts Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: ms excel 2003 conditional formatting with countif function., city state zipcode, conditional formatting

city state zipcode, conditional formatting, array formulas: I need more info. First, you really want the conditional formatting formula to be one which returns true or false, not the result of a COUNTIF (which does work by treating 0 as false and all others as true), so a better formula would be =COUNTIF(UPDATEA,$A2)...

Excel: Need Formula, countif, sheet1

countif, sheet1, error msg: I m assuming you re in R1C1 mode, not A1 mode or this won t be valid. Next, I don t know what you mean by count the maximum date value. You mean how many times does the latest date appear in that range? And how does the 2 given dates fit into that? I...

Excel: Need Formula, array formula, maximum value

array formula, maximum value, max max: Let s say that in cells A1 to A10 you have the values with the corresponding dates in cells B1 to B10. You have another set of values in cells C1 to C10 with the corresponding dates in D1 to D10. Now cells A1 to A10 and cells C1 to C10 are the two columns...

Excel: Need Formula, maximum value, p2

maximum value, p2, cells: Sorry, even with the picture, I don t get it. Max of G & H doesn t help, because in your example G & H were all blank; I don t know what follow between the dates means; If you gave another example with G & H containing some values, AND your expected results...

Excel: Need Help with Dates, isdate, proper solution

isdate, proper solution, column c: Shawn, You just left out an End IF for the test if r is a date. This complied for me. Sub AAAA() Dim I As Long, FinalRow As Long Worksheets( Bug Report ).Activate FinalRow = Cells(Rows.Count, 2).End(xlUp).Row For I = 9 To FinalRow ...

Excel: Number Sorting/Ordering In Excel, street indexes, prefix

street indexes, prefix, yahoo: * This is a very difficult forum (allexperts.com) to ask ? s back and forth. why don t you email me a little sample (or a big sample if you want) to ipisors at yahoo dot com. I m sure there s some way to do it, I m not sure if you will need to change...

Excel: obtaining formula, sumproduct, column c

sumproduct, column c, b100: Manuel Say data is in rows 1 to 100. Lets say I have names in column A and ages in column B and gender in column C (M or F) and I want to count the number of males older than 15 =sumproduct(--(B1:B100 15),--(C1:C100= M )) Say you want the sum...

Excel: PercentRank in VBA (for "selection"), input box, vba

input box, vba, cells: Can you provide some data and sample results you ve moved up manually so I can see the BEFORE/AFTER? You can attach a picture here, or upload a workbook on the internet somewhere an provide a link. You can also use my profile to access my website and...

Excel: Postcode analysis, sumproduct, pivot table

sumproduct, pivot table, postcodes: Wouldn t a pivot table be easier? Use a formula to split the postcode into the bit before the space (if you have a space) =LEFT(G25,SEARCH( ,G25)) where G25 is the postcode field This gives you the town part of the code, you can then use a pivot table...

Excel: Problems with a data sorting macro, validation data, basic header

validation data, basic header, left header: Paul, instead of using a hard coded range in your code, you will have to make the range dynamic. On way is to search for the upper left header using the find command Dim rng as Range, rng1 as Range, rng2 as Range Set rng = columns(1).Find(What:=...

Excel: pivot table value lookup, array formula, formula index

array formula, formula index, maximum formula: mark twain, Assume cells A5 and B5 contain the ID and maximum value combination that you want to return the B value for. So now you would put a formula in say C5 of the Pivot Table sheet. Assume the data is on a sheet named Master and the Pivot table...

Excel: What is practical appl. for HLOOKUP?, hlookup, appl

hlookup, appl, apps: Hello octester, Thank you for your question. HLOOKUP works the same way that VLOOKUP does, but it looks horizontally, rather than vertically. For expample, if your data is laid out horizontally across several columns, HLOOKUP can look up the value...

Excel: Query in excel, google search, circular reference

google search, circular reference, learning excel: You can do a google search on useful circular reference excel for many examples, but here s one: Suppose there s a premium value which is 10% of the total value, but it s included in the total: Gadgets 500 Widgets 600 Premium 10% of Total ...

Excel: Query in excel, Sixthsense Excel

Sixthsense Excel: Hi Talha, It s surprising to me I am not able to use circular reference . No one would like to evaluate a formula with circular reference. Circular reference is a an error. Circular references are a common error in Excel that can cause problems with...

Excel: Question about bill breakdown in Excel 2007, credit card charges, store2

credit card charges, store2, pivot table: Well, I can see why you d want to have that. You can probably pull this off using formulas, especially if you use a couple of helper columns to do it. Unfortunately, I m very busy this week, so I can t help you set something like that up entirely. ...

Excel: Remove Duplicates Based on Date Criteria, greetings from australia, location number

greetings from australia, location number, kind assistance: Yogi, you example shows your rows being sorted so that the last occurance of a LocNum is in the highest numbered row. Using the assumption that it is sorted like that, then this will remove an duplicates in lower numbered rows. Test this on a copy...

Excel: Remove duplicates from combo box, combo boxes, combo box

combo boxes, combo box, aidan: I think you would need an array filter that was used to return only ONE entry rather than the duplicates, and have this set as the source for the lists - not sure if I have talked you through array filters before - happy to show you on a sample file - my email...

Excel: Replace function, vba macro, cells

vba macro, cells: =if(or(a1= B ,a1= C ,A1= D ), X ,a1) would be the function I d use - this would go into a different column - if it is then to replace the existing values you could copy and then paste special, values over the existing data, then delete the helper column...

Excel: Rolling Numbers in Excel 2003-2007, vbyesno, related event

vbyesno, related event, consecutively: * I m not sure if you re in excel 2003 or excel 2007. Right-click on the name of the worksheet (tab). Click View Code. Now you re in the VB project. Double click on the ThisWorkbook object, near upper left. then copy and paste the code I provided in. ...

Excel: removing bulk amount of text, auction listings, answering the question

auction listings, answering the question, trying to find a way: * Sure, I could do that. I am always trying to drum up a little business on my local (Phoenix) craigslist. I would also prefer to work by project . my email is ipisors@yahoo.com If you can give me as detailed a description as possible of what you...

Excel: removing bulk amount of text, auction listings, ecommerce website

auction listings, ecommerce website, trying to find a way: Sorry I was so long in answering... If you re comparing 2 lists, this formula will find the items on the active sheet which are not in the list on the external workbook (named Lists.xlsx here): array-enter (ctrl/shift/enter) this: =INDEX(A:A,SMALL(IF(ISNA(MATCH($A$1:$A$5,[Lists.xlsx]Sheet1!$A$1:$A$7,0)),ROW($1:$5),...

Excel: removing bulk amount of text, cell a1, auction listings

cell a1, auction listings, test strings: Michael, assume tweaked sheet is [Webbook.xls]Data so it is in a workbook named Webbook.xls on a sheet named Data assume similarly the master list is [Master.xls]SKU Now assume both workbooks are open in the same instance of excel. In the tweaked...

Excel: Scatterplot, conditional formatting, score ranges

conditional formatting, score ranges, score value: A scatterchart is a chart, and charts don t have conditional formatting -but that doesn t mean you can t do it - see http://peltiertech.com/Excel/Charts/format.html#CondChart for some examples and http://www.andypope.info/charts.htm for some more! both of...

Excel: Search Functions > 3D VLOOKUP, vlookup, google

vlookup, google, exit function: Easier with VBA. Enter this function into VBA, then use =Sheet( Name A ,row(A1)) and fill down Function Sheet(what As String, which As Integer) As String Dim n As Integer Application.Volatile True On Error Resume Next For Each sh In...

Excel: Search Functions > 3D VLOOKUP, website references, search functions

website references, search functions, google: Oh, there are 1000s of ways to lookup multiple instances of items on multiple sheets. The problem is there 1000s of ways because everyone s workbooks and lookup needs are different every time. You ve already found my website, use the links at the top of...

Excel: Seeking VB code help for reading a column, snap shot, copy contents

snap shot, copy contents, correct reference: Based on your description, you cn just copy ALL of column A into AG and apply the formula you gave. Nothing changes if you want to do it by unique values or filtering, since the formula will apply the correct reference (AG2, AG3, etc) and get the right answer....

Excel: Sheet2 to Sheet1, thero, column c

thero, column c, b7: On Error Resume Next ColRP=Sheet2.Rows(1).Find( RP ,lookat:=xlwhole).Column ColSD=Sheet2.Rows(1).Find( ShippingDiscount ,lookat:=xlwhole).Column ColTD=Sheet2.Rows(1).Find( TotalDiscount ,lookat:=xlwhole).Column ColSIP=Sheet2.Rows(1).Find( SIP ,lookat:=xlwhole).Column...

Excel: Sort the number of occurences by year, earthquake occurrences, builtin functions

earthquake occurrences, builtin functions, survey table: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. -------------- Interested in online Excel classes led by me? 2 are scheduled: May 26, 7-8PM est, Intro to VBA May...

Excel: Sorting data in ascending order across multiple worksheets, queries in access, ecel

queries in access, ecel, pivot table: This macro seems to do the trick for me. Note that it assumes your sheets with data are called Data1, Data2, data3, ... Each sheet is assumed to contain an equal amount of rows, except for the last sheet. The number of rows can be set in the code (MAXROWS)...

Excel: SumIF when adjacent cell contains a particular letter, kendrick, couple of days

kendrick, couple of days, d1: Kendrick That doesn t seem like too bad an approach. It might make sense in this case to add a helper row that handles the checking for s . If say, row two has the formula =IF(ISERROR(FIND( s ,A1)),0,1) then you would be able to use a simple...

Excel: out of spec icon set, yellow exclamation point, array formulas

yellow exclamation point, array formulas, data validation: It seems like you re wanting to use icon sets for column A depending on values in columns B:D, so it d be relative references to the same row. This isn t possible using icon sets. If it s particular values that don t change per row, like B2:D2 only, then you...

Excel: special search and movie in excell, english question, search method

english question, search method, first language: I m afraid this seems to be somewhat out of my area of expertise, though it may be that I still haven t grasped the task that you are trying to accomplish. It s ALMOST sounding as though you simply want to find all matches on all sheets (at which point, select...

Excel: sub total on each page, balace, cell reference

balace, cell reference, page a4: Sandi Without knowing how your invoice is set up, you can create the first sub total on page one then link the subtotal to the second page by using =cell reference for the subtotal. then add the subtotal to the next set of numbers on the second page using...

Excel: Taking Average and getting #N/A error, vlookup, game1

vlookup, game1, return zero: Assuming you re using Excel 2007, use the IFERROR function to return zero in cases where the result is an error. So using iferror(vlookup(a1,2004 game1!A:B,2,FALSE),0) will return the same result as vlookup(a1,2004 game1!A:B,2,FALSE) except...

Excel: Time card in Excel, time card, column c

time card, column c, richard florida: Helen I don t understand why you need to know 7 hours minus the total hours worked, it seems to me that the calculation would yield the total hours in a normal work day that were not worked, which is not a normal feature in a time card. But assuming...

Excel: Top 10 Text Value Functions, blank cells, cell d1

blank cells, cell d1, vlookup: You say that cell D1 is 10, to indicate a top-10 list. But what if there are less than 10 unique non-blank items in col A? So the 10 you currently have in D1 should be entered in D2 instead. In D1, enter =MIN(D2,MAX(C2:C100)) Now, instead of always...

Excel: if/th, cell a1, contingent

cell a1, contingent, ck: ck It is definitely possible, but you really haven t provided enough information to get much help. First of all, do you really want to copy the information, or would it be okay to just have the information show up? If that is the case, then a simple IF...

Excel: Use a function to determine the Range() value, value question, chicken wings

value question, chicken wings, syntax error: OK, it s clearer now - you had mentioned a function, and VBA so I assumed it was a VBA function. As the commands are actually excel worksheet commands you would need to use worksheetfunction within VBA - but much easier would be to have your formula in a...

Excel: Userform Comboboxes Percent Typing, builtin functions, excel formulas

builtin functions, excel formulas, paypal account: can you send me the userform (or workbook) and I ll have a look... send to bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what needs doing. ================================== Are you interested in online Excel classes led by me? 2 are...

Excel: Using Excel to help with my tax returns, veterinary medications, professional fee

veterinary medications, professional fee, zero rate: I am sure the 2/3 rule is not the only VAT calculation hurdle to jump through. What is VAT for $100 orders that break down in other ways? 2/3 service = 13.5% VAT 2/3 non-oral meds = 21% VAT 2/3 oral meds = 0% Now list the other scenarios will occur,...

Excel: Using formula in Excel file, rephrase, drop down list

rephrase, drop down list, f8: 1 - I have no idea what PN is 2 - ...drop down list entered to cell Card P]N =F8... seems gibberish to me -- no idea what you re saying. 3 - Please rephrase in plain English what you re trying to accomplish and yuo can send a sample to me at bobumlas@yahoo.com,...

Excel: Variable data range, sq ft range, tier table

sq ft range, tier table, variable data: Pricing tables work in tiers. Humans think of tiers as 0-3 and 4-10 and 11-20, but Excel only needs to know the START of each tier since the the start of the next tier clearly indicates the previous tier has ended. So, in your workbook you will create...

Excel: VB macro to find cells containing Page and then delete entire row, blank row, column c

blank row, column c, rng: TJ Sub DeleteRows() Dim rng as Range do Set rng = cells.Find(What:= page , _ After:=Range( A1 ), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext,...

Excel: VBA, blank cells, consecutive rows

blank cells, consecutive rows, a20: You could even stop them saving it! This simple routine counts blank cells in a range Sub tester() Dim counter As Long Dim cell For Each cell In Range( A1:A20 ) If Len(cell.Value) = 0 Then counter = counter + 1 Next MsgBox counter End Sub ...

Excel: VBA, vba code, debugging tools

vba code, debugging tools, title survey: J What you show is the declaration for a subroutine. It is designed to be called from another subroutine since it takes an argument. An example might be Sub Main() Dim questionlist as String questionlist = B RunsQuestions questionlist End Sub...

Excel: VBA, vba excel, vba code

vba excel, vba code, copy windows: Ian Fan Workbooks.Open (strInFile) strInfile = Activeworkbook.name prevously, strInFile would hold a fully qualified file name (includes the path) this command strInfile = Activeworkbook.name will change strInfile to hold just the workbook...

Excel: VBA - Conditional Formatting, formula1, ogilvy

formula1, ogilvy, constants: Daniel, Here is a list of the xlformatConditionalOperator constants which you can use. XlFormatConditionOperator constants: xlBetween, xlEqual, xlGreater, xlGreaterEqual, xlLess, xlLessEqual, xlNotBetween, or xlNotEqual. -- Regards, Tom Ogilvy...

Excel: VBA Directory Search and Save cont'd, sincere apologies, coding sheet

sincere apologies, coding sheet, cell d5: On my website I already had published a function to MakeDirectories. You feed in a full path string and it goes through the entire string making sure all the folders in the string exist, creating them if needed. The original macro and a sample file to test...

Excel: VBA Directory Search and Save, target folder, coding sheet

target folder, coding sheet, cell a1: Item #2 - You didn t specifically respond, but in the long string we will ALWAYS use the second word as the folder?? ALWAYS?? (With text strings holding names, I am wary of it being that simple.) You have lost me on item #3. Please explain it again...

Excel: VBA: Find row containing text, copy and paste into new sheet or workbook, blank row, best guess

blank row, best guess, cstr: TJ, Sub ABC() Dim sh as worksheet Dim lastrow as Long, i as Long set sh = workbooks( workbookname.xls ).Worksheets( Sheetname ) lastrow = sh.cells(sh.rows.count, A ).End(xlup).row for i = lastrow - 1 to 2 step -1 if cells(i, A ).value cells(i...

Excel: VBA: Find word, calculation application, blank row

calculation application, blank row, word question: TJ, Sub abc() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, A ).End(xlUp).Row For i = lastrow To 2 Step -1 If Len(Trim(Cells(i, 1))) 0 And Len(Trim(Cells(i - 1, 1))) 0 Then Rows(i).EntireRow.Insert End If Next End Sub...

Excel: VBA? Macro or Formula to Group rows and either border it in groups or shade, vba macro, vlookup

vba macro, vlookup, code description: TJ, It sounds like you want to have one row per entry/Cable code. If you do that then you can vlookup the data. If you want to send me a sample file, I can take a look at it. I would propose using low level file IO to parse the information out of...

Excel: VBA read a record, write a new (conditional on content), 6 cells, cell a1

6 cells, cell a1, rw 1: Uffe, This worked for me if the 0 is in cell A1 on sheet named lessons. you have given two names for the destination sheet named lessonswithduplicates lessonswith duplicates adjust the code to reflect the actual names of your sheets. Sub...

Excel: VBA - replace cell value, up arrow, h10

up arrow, h10, dialog: Select column H (Or H1:H1000). Use Ctrl/F to find Call Me . Assuming you have Excel 2007 or 2010, once you get the dialog with the Find results, press Ctrl/A inside that dialog, then close the dialog. All Call Me s should be selected. Type = (no quote),...

Excel: Visibility, cell a1, target

cell a1, target, shapes: Rightclick the tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Range( A1 ).Value = 0 Then ActiveSheet.Shapes(1).Visible = True ActiveSheet.Shapes(2).Visible = False ElseIf Range( A1 ).Value...

Excel: Vlookup formula copy to another colum, Sixthsense Excel

Sixthsense Excel: Hi Reza, Yes it s happening due to the Column Index Number is higher than the Number of Columns selected (i.e.) Table array is limited. For example =VLOOKUP(F1,A:A,2,FALSE) The above formula will result #REF! error because the Table array is A:A only...

Excel: ven diagram, free microsoft excel, ven diagram

free microsoft excel, ven diagram, excel tips tricks: You can use the MATCH and/or INDEX functions to do this. I just covered this exact example in my Excel Expert Level 4 class: http://www.599cd.com/site/courselist/excel2010/expert/x4?key=AllExperts Let me know if you have any other questions or comments....

Excel: vlookup w/ AUTO column index number, vlookup, column index

vlookup, column index, index number: Joe, as I understand it you have a range of data named Table1 as an example. and you want to do a vlookup in the table and return the say the energy use for building5 (the value in the top row of the table is literally building5 then =vlookup(...

Excel: Weighted averages, sumproduct, counta

sumproduct, counta, weighted averages: Nate, assume the first product is in column B from Row 2 to row 26. the weights in column D from row 2 to row 26. =sumproduct(B$2:B$26,$D$2:$D$26)/Sum($D$2:$D$26) should give you the weigted average. You mulitply the values time their weights...

Excel: Weighted percentage, counta, builtin functions

counta, builtin functions, company names: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. -------------- Interested in online Excel classes led by me? 2 are scheduled: May 26, 7-8PM est, Intro to VBA May...

Excel: Working with pivot tables, pivot table, table options

pivot table, table options, pivot tables: You need to use the function GetPivotData which has many parameters, but if you click inside the pivot table after typing the = and DON T get the GETPIVOTDATA formula, you can turn that feature ON by first selecting any cell in the pivot table, then in the...

Excel: weekly data sheet update, high school football, stat sheet

high school football, stat sheet, macro recorder: Hello Joseph, Thank you for your question. There are a couple of things you can do. You could use a Total column to the right of where the data will end (week 12 or whatever), or if the data is on different sheets, you could have a sheet called Total...

Excel: Zero in Excel Formula, array formulas, format cells

array formulas, format cells, conditional formatting: not sure what help you re looking for, but feel free to send me a copy of the wb and indicate which cell is wrong . send to bobumlas@hotmail.com, use subject of AllExpertsQ . ================================== Are you interested in online Excel classes...

Excel: 1 more question on Excel changing date in cell upon reaching a specific date, martha martha, time periods

martha martha, time periods, auto renewal: Martha, the formula was designed to only work with one year time periods. You date is beyond one year. This formula should handle that situation with all subsequent extensions being one year. this assumes that your original expiration date (12/31/12...

Excel: putting √ and x depending file is present or not, notepad files, files names

notepad files, files names, navin: Navin The following procedure and function should do what you want. I have assumed the file list is in a range named list . I have also assumed the files are in a folder named C: emp . Sub test() Dim R As Range Dim cl As Variant ...

Excel: Advanced list management, array formulas, reverse chronological order

array formulas, reverse chronological order, management question: Sandy, =INDEX($Q$8:$Q$68,MATCH(MAX(IF((MONTH($O$8:$O$68)=MONTH(S21))*($Q$8:$Q$68 ),$O$8:$O$68)),IF((MONTH($O$8:$O$68)=MONTH(S21))*($Q$8:$Q$68 ),$O$8:$O$68),0),1) entered with Ctrl+shift+enter (because it is an array formula) worked for me. (or...

Excel: arrays or formulas, blank cells, countif function

blank cells, countif function, blank rows: I d be inclined to NOT use a formula for this but use a pivot table as this would enable you to analyse everything! BUT you COULD use a decreasing countif function - with lets say the workshop being in cells F2 to F200 =countif(F2:$F$200,f2) This formula...

Excel: Auto Populate Data Validation List, data validation, blank cells

data validation, blank cells, abc def: Gigi, I could see doing this two ways. One way would be to construct the list of dates in another location (on the same sheet or another sheet) and have that location referenced by your data validation dropdown. This would be done using formulas based...

Excel: autofill multiple worksheets, master spreadsheet, cell references

master spreadsheet, cell references, richard florida: Chris When removing the = sign, the fill function advanced to the next cell within the sheet, not the same cell in next sheet I am not sure how the text is telling the formula to advance to the next cell if the cell reference isn t changing in the formula,...

Excel: Backfilling Data, question pool, sheet names

question pool, sheet names, text c: Ty, you didn t ask me this question - I found it in the question pool. But I have to wonder why you want to use a macro. In say C5 I would put the formula =INDIRECT( [B.xls] &TEXT(C$4, d-mmm-yy )& !D7 ) assuming your sheet names in workbook...

Excel: Book recommendations, john walkenbach, pam pam

john walkenbach, pam pam, chip pearson: Pam, you can t go wrong with John Walkenbach s books - he has books for Excel VBA (power programming) and for Excel itself. He has separate books for formulas and one for charts and probably one for something else. Then he has the Excel Bible (which covers...

Excel: Two BoundColumn - (VBA question), sheet1, combobox

sheet1, combobox, mauri: Mauri, you can just access the information from the list property and write it in the click event. Here is sample code that worked for me: note: there was a little confusion in your explanation about where the data is to be written - E1 or E2. I am...

Excel: Creating dynamic 2D charts, 2d charts, dynamic chart

2d charts, dynamic chart, graphs: Define a name like Companies which is =OFFSET($H$13,0,0,COUNTA($H:$H),1) and define a name for the series for 2006 (make up a name) as =OFFSET(Companies,,1) and for 2007 as =OFFSET(Companies,,2), etc. Then use these names in the SERIES formula for...

Excel: calculating bonus, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: This is a job for VLOOKUP. Set up a little lookup table just like you have there and VLOOKUP will take care of the rest. Here s a tutorial that will get you started: http://www.599cd.com/tips/excel/vlookup?key=AllExperts Let me know if you have any...

Excel: CALCULATION OF RETIREMENT AGE, cell b3, retirement age

cell b3, retirement age, microsoft xp: JAGANNATH B S Your formula should look like this =IF(DAY(B3)=1,EOMONTH(B3,719),EOMONTH(B3,720)) As you can see the formula is a combination of the day formula and eomonth formula. the formula is referring to cell B3 which in this instance is the...

Excel: Calling a function in a Sub, john wolford, wshome

john wolford, wshome, best guess: John Wolford, One problem is you have rDestination misspelled in the code in your function. wsDestination.rDesitination Another problem is that even if it is spelled correctly, a construct like wsDestination.rDestination would raise an error...

Excel: Calling protection from Workbook_Activate event, string password, ogilvy

string password, ogilvy, scenarios: Mark SetProtection True don t use run. Private Sub Workbook_Activate() SetProtection True End Sub if you want to use run (and that is slower), then it would be run SetProtection , true the reason you can t see SetProtection in...

Excel: Cell Color, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Tawnie, Thanks for sending your question my way, and the same is possible using VBA. But as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim...

Excel: Cell Protection, cell contents, gibberish

cell contents, gibberish, vbe: Hi Vikas, There is no way to make an individual cell invisible (I assume you mean make the cell CONTENTS invisible). However, you can hide the entire row or the entire column containing the cell, and keep users from unhiding it by password locking...

Excel: change event of a cell containing formula, target address, cell a1

target address, cell a1, ogilvy: shravya, everything you say is true. So there is no built in support for what you want to do. If you want to work around this problem, you would have to look to the cell that is actually changing that causes your formula in the cell that has the formula....

Excel: Changing the column letter as I drag row down, cell a2, curly brackets

cell a2, curly brackets, cell a1: Daniel Try using the OFFSET and ROW functions. =OFFSET($E$1,0,ROW()) Alternatively, you could use the TRANSPOSE function (array function). In A1:A6 put {=TRANSPOSE(F1:K1)} This one needs to be array entered (shift-control-enter). It will add...

Excel: Changing Range Selection in a Macro, string s1, question pool

string s1, question pool, range selection: Ted, you didn t ask me this question - I found it in the question pool. If your still looking for an answer, you can get the selected with ActiveCell in VBA. Then you can use a case statement to make a decision on what to select. Dim sh as worksheet...

Excel: Chart Issue, chart elements, relevant steps

chart elements, relevant steps, mary hope: Hi James, On your unstacked column chart, click the data series to which you want to add a trendline or moving average, or do the following to select the data series from a list of chart elements: Please be aware that if you select a chart that has...

Excel: code works in one workbook but not another, vba code, target cell

vba code, target cell, filesearch: My guess is you put the code in the wrong place. Right-click the sheet tab of the sheet which needs to respond to the change event, select view code, and paste the code there. If that still doesn t work, feel free to email be the wb which is not responding...

Excel: Colourformatting cells, due dates, due date

due dates, due date, cells: Select the cells which should change color. Use Conditional formatting/New Rule and for each rule select Use a formula... Enter this formula =A1 =TODAY()+30 and format the cells as yellow do it again, new rule, enter this formula =A1 TODAY() and...

Excel: combine grouped line shapes, line shapes, tetromino

line shapes, tetromino, straight lines: You can certainly group shapes, but this simply creates a group of shapes, not a new shape - so a group of straight lines has no fill option as a straight line has no fill option - to group them, click one at a time and hold down ctrl whilst doing so- then...

Excel: combining conditional, meros, allexperts

meros, allexperts, coexistence: It sounds as though you basically want a lottery type of program - there are 18564 possible combinations of 18 numbers (using the COMBIN function in excel will tell you this) =COMBIN(18,6) and it would be possible to generate all possible combinations...

Excel: Combining data from multiple workbooks into one worksheet, run time error, run time error 1004

run time error, run time error 1004, credit card payments: Anthony, the problem lies in the fact that workbooks can have 1 million rows or 65535 rows. I have run into this problem even trying to do it manually. Hopefully this will fix the problem. In the offending line qualify the rows.count command ...

Excel: Combining two excel formulas, excel formula, grade test

excel formula, grade test, excel formulas: Cheryl It seems like the formula I gave you should work. The VLOOKUP formulas will just need to reference the grade spreadsheet. Is the grade spreadsheet a different worksheet in the same workbook, or a different workbook? It shouldn t really matter...

Excel: Combining Excel Worksheets, hold down ctrl, file names

hold down ctrl, file names, microsoft office: When a shared workbook is updated by other users, and you want to compare the changes that they have made before you update the workbook with those changes, you can use the Compare and Merge Workbooks command. This command is not available with other shared...

Excel: Compare Two Colums, column c, colums

column c, colums, question thanks: Sorry for misunderstanding - more complicated, but still do-able =IF(MAX(IF(ISERROR(SEARCH(B1,$A$1:$A$3)),0,ROW($A$1:$A$3)))=0, Not Found ,INDIRECT( A &MAX(IF(ISERROR(SEARCH(B1,$A$1:$A$3)),0,ROW($A$1:$A$3))))) entered as an ARRAY formula - so press ctrl...

Excel: Compare text and copy another text based on comparision, Sixthsense Excel

Sixthsense Excel: Hi Trond, Are you using Open Office??? If you are using Ms-Excel then your formula should be like the below =VLOOKUP(E40, N3 !D:H,1) Have a look in the below link to know about referring to other worksheets http://www.homeandlearn.co.uk/me/mes12p2.html...

Excel: compare value in two sets of data and return not matched value, column c, straightforward application

column c, straightforward application, casey: Casey I am not sure what you want; the subject says return not matched data but below it looks like you want matched data. Also, where to you want to return the value? Do you want a column E that shows the value in column D of values in column A that...

Excel: Comparing two nimbers, spreed sheet, excel formula

spreed sheet, excel formula, colums: Rick I really don t understand the real data change you are trying to make. If the reference number were 12.100 as in your example, what would the adjusted 11.800 be? If you changed it to 11.9 it would be within 2% but so would 11.86 and so would 12.000....

Excel: convert string date to value, question column, value question

question column, value question, string values: I assumed since I saw a date like 28/04/2011, that you were using a system with dd/mm/yyyy dates instead of mm/dd/yyyy. So those aren t string dates, they re plain old string values (not dates). Change the macro to Sub Cvt() Columns( A:A ).TextToColumns...

Excel: converting a 2007 VBA script into a 2003 compatible, vba commands, vba script

vba commands, vba script, conditional formatting in excel: Ryan, No, that wasn t the only change. All these lines changed Added: Selection.FormatConditions.Delete Recall I said: I have commented out the incompatible commands. Deleted: Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority...

Excel: copy data from multiple workbook in a single sheet, spath, documents and settings

spath, documents and settings, sname: Jain Varun, This should only copy the header from the first sheet where data is copied. It assumes the header is in row 1 of each sheet from which the data is copied. Sub ABC() Dim sPath As String, sName As String Dim bk As Workbook, r As Range ...

Excel: Copy and paste macro, srow, b cells

srow, b cells, scount: Hi again Derrik, You can move the code to the very beginning of the Sub procedure like this: _________________ Sub Genlist2E(sSheetName As String, mastsheet As String) Dim sCol As Variant the search column sCol = InputBox( Enter mastsheet...

Excel: Create a list of Sheets, with data from the top rows in a new sheet, header row, column headers

header row, column headers, cells: 1) Create a sheet in your workbook called Master List 2) Run this macro Option Explicit Sub CreateHeaderList() Dim ws As Worksheet Dim NR As Long On Error Resume Next Application.ScreenUpdating = False With Sheets( Master List ) .UsedRange.Cells.ClearContents...

Excel: Creating a sequential code from multiple cells, alphanumeric code, cell a2

alphanumeric code, cell a2, cell a1: Hello James, Thank you for your question. I am not for sure I completely understand what you want to accomplish. For example, if your data runs from A1 to A10, are you wanting Cell A1 to be 1A and Cell A2 to be 2BC down to Cell A10 which might be...

Excel: Creating Simple Forms for Invoice Tracking, excel formulas, paypal account

excel formulas, paypal account, submission form: I don t know what you mean by like the form to recognize when a particular code is entered. To click a button implies you need a macro. If you re really a beginner, then describing this process in words is a tough task. Send me a sample workbook and I ll...

Excel: Creating Simple Forms for Invoice Tracking, richard florida, submission form

richard florida, submission form, florida usa: Ben What you want to do is not difficult, but--- There are a couple of issues that are slightly problematic. 1 You state I d like the form to recognize when a particular code is entered--- , but I am not sure what you want excel to do when it recognizes...

Excel: Drop down menus- conditions, data validation, drop down menus

data validation, drop down menus, drop down menu: If the A,B,C list is in A1, then you still need to set up the other lists somewhere and give a name. For example, in E1:E3, put 1,2,3 and name the range something like rrrr1. In F1:F3 put 4,5,6 and name it rrrr2. in G1:G3 put 7,8,9, name it rrrr3. In the data...

Excel: Data Validation Formula, data validation, daily diary

data validation, daily diary, countif: Graham, In Excel 2007, Microsoft added the Countifs and Sumifs formulas. These are similar to the originla countif and sumif formulas, but they allow the specification of multiple conditions. so you would use the countifs formula like this ...

Excel: Data Validation to prevent duplication and ensure 6-digits entry, nicholas tang, 6 digits

nicholas tang, 6 digits, data validation: Using the Visual Basic Editor, enter the following in the Worksheet object: Private Sub Worksheet_Change(ByVal Target As Range) Dim inputRange As Range, changedCell As Range, changedRange As Range Set inputRange = Me.Range( A1:A50 ) Set changedRange...

Excel: How to Decide AM/PM from date value, day time, night time

day time, night time, b1: Formula to return D if time in B1 is between 6:00 and 22:00, and N otherwise: =IF(AND(B1 =TIMEVALUE( 06:00 ),B1 TIMEVALUE( 22:00 )), D , N ) Formula to return sum of all cells in col F for rows in which col B is between 6:00 and 22:00: =SUMIFS(F:F,B:B,...

Excel: Dependent Variables Through Macros, input cells, dependent variables

input cells, dependent variables, button title: John, you would declare the variable at the top of ONE of your modules only and make it public Public trigger as boolean Sub macro1() no declaration for trigger trigger = False msgbox( Put some data in to the input cells and then click on the...

Excel: detecting duplicate entries in a worksheet, range c, inputbox

range c, inputbox, navin: Hi Navin How are you? Please try the following: Sub highligtrept() Dim R As Range, c As Range Set R = Sheets(1).Range( A1:Z10000 ) Y = InputBox( Words/number to look for ) For Each c In R If c.Value = Y Then With c.Interior .Pattern...

Excel: Displaying a Valid Production Task, e9 e10, wash dishes

e9 e10, wash dishes, clearer view: Now the original file image is missing. Please send both the sample and the valid lists to me at bobumlas@hotmail.com. Use subject of AllEpertsQ , repeat everything from above. ================================== Are you interested in online Excel classes...

Excel: Divide dates into quarters, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, excel expert: To determine the QUARTER that a date falls in, use the equation: =ROUNDUP(MONTH(A1)/3,0) I cover this, and most of the other DATE functions in my Excel Expert Level 2 course: http://599cd.com/site/courselist/excel2010/expert/x2/?key=AllExperts ...

Excel: Doubt in calculating average, time savings, workout

time savings, workout, efficiency: This formula will give you a Percentage Time Savings , format the cell as percent: =1 - (47/227) I m not sure how you want to apply Percentage of Efficiency. The basic formula for that is: (WorkOut/WorkIn) * 100% In your...

Excel: Excel, overtime hours, previous question

overtime hours, previous question, ogilvy: Matt, If A1 contains total hours then =min(A1,40) will give the number of regular hours =max(A1-40,0) will give the number of overtime hours if A1 contains day and each day is 8 hours then =min(A1*8,40) =max(A1*8-40,0) -- Regards,...

Excel: Enter time in Excel, target value, value application

target value, value application, sheet tab: Sub Worksheet_Change(ByVal Target As Range) If Target.Column = Range( AJ1 ).Column Then Exit Sub ===change the AJ to whatever column you want.... If IsNumeric(Target.Value) Then If Target.Value 1 Then Exit Sub If Target.Value...

Excel: excel 2002 - if statement, cell b11, victor victor

cell b11, victor victor, excel 2002: Victor I think you need to use the ABS function in your IF statement. Maybe something like =IF(A1 0, ABS(A1)+10000,A1) If the value in A1 is less than zero it will return the absolute value of A1 plus 10000 otherwise it will return the value in A1....

Excel: Excel 2003 Win XP help, currencies exchange rate, exchange rate data

currencies exchange rate, exchange rate data, validation tool: With automatic calculations turned on, Excel updates all formulas all the time, so what you re describing shouldn t be happening unless you ve set calculations to manual. Try pressing F9... do the values update then? Check your Excel Options, make sure...

Excel: excel 2007, question pool, number thanks

question pool, number thanks, median number: Maria, You didn t ask this question of me - I found it in the question pool. but I would think it would be =countifs(A:A, small pox ,B:B, Santiago ) so for the example, the disease name is in column A and the region name is in column B. We are counting...

Excel: Excel 2007 IF Function, cell b1, f1 sounds

cell b1, f1 sounds, error value: Hello Frank, I think I understand your question now. Below is a formula you can use in F1. If the value in B1 is an error, such as #VALUE!, then the formula returns the value in E1. Otherwise, if the value in B1 is valid, then it will return the...

Excel: excel 2007 vba, vba macro, column c

vba macro, column c, c check: Hello Solman, Copying the values from column B to column C can be done two ways: via a cell formula, or via a macro. Since most people prefer cell formulas (find them easier to understand) I am going to give you that solution, but if you prefer a VBA macro...

Excel: Excel 2010, concrete example, e5

concrete example, e5, spreadsheet: (1) Please ask one question with each posting. You can make multiple postings if needed. (2) In asking a question, always include a concrete example. Make this example as simple as possible, just enough to illustrate what you want to learn. PRECISELY what...

Excel: Excel 2010, question pool, history sheet

question pool, history sheet, ogilvy: Joelle, You didn t ask me this question; I found it in the question pool. first, it isn t totally clear what you expect in the output sheet and you don t list any locations - any answer would have to be specific to the cells involved. If you want...

Excel: Excel 2010: How to automatically update spreadsheets, speedy response, jane smith

speedy response, jane smith, smythe: I think I would need to see the real (perhaps shortened) workbook. It s not clear if this is all one workbook or more than one, and in order to really help I need to know where things are. The mailing list spreadsheet would certainly have to be created by...

Excel: Excel, conditional formatting, cell a1

conditional formatting, cell a1, richard florida: Joelle Yes you can use an if formula in the conditional formatting. For example in cell B1 go to toolbar/format/conditional formatting in the drop down condition is box pick formula is in the formula box to the right put =A1=1 then pick your...

Excel: Excel, conditional formatting, format button

conditional formatting, format button, blank worksheet: I ll assume you re using Excel 2007 or later. On a new blank worksheet, select row 1. From Home ribbont, select Conditional Formatting, New Rule, Use a formula to determine which cells to format. Under Format values where this formula is true, enter ...

Excel: Excel, excel formula, test3

excel formula, test3, abbie: The logic is fine, it depends on what you want the result to be =and(test1,test2,test3) requires all three conditions to be true (so numbers in all 3 cells in our case) for the condition to be true whereas =or(test1,test2,test3) will return a TRUE...

Excel: Excel, excel addin, custom toolbars

excel addin, custom toolbars, excel function: Talha Khan, I believe the number of modules would only be limited by memory. I suspect you have misspelled the name (or there is a space in the name or something that makes it different) or something similar. Five modules should be no problem. --...

Excel: Excel, excel function, current sheet

excel function, current sheet, talha: make it an addin -- save it with the xlam extension, and either keep it in the directory which is always opened when you start excel (one you specify), or open it. Just know that if yuo give these workbooks to others, you ll also have to send along the xlam...

Excel: Excel, consecutive rows, detail sheets

consecutive rows, detail sheets, detail sheet: OK, maybe I ll marry you. Or, you could attend a class or 2: ================================== Are you interested in online Excel classes led by me? 3 are currently scheduled: June 23, 7-8PM (NY Time) VBA 101 Part 2 June 27, 7-8PM (NY Time) Userforms...

Excel: Excel, Sixthsense Excel

Sixthsense Excel: Hi Pogi, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. https://docs.google.com/leaf?id=0B2TMxNyEPQ2rNDk4MjRlMTEtMWU4NC00YThmLWI0NTQtMjZhYzkwNDc3MzY4&hl=en_US Hope that helps....

Excel: Excel, countif, list of names

countif, list of names, ogilvy: Evan, in C1 =if(countif(A:A,b1) 0, Yes , No ) then drag fill that formula down the column (or copy and paste). The yes/no will refer to the value in column B being found in colmn A or not. If you want to mark the values in column A (are they found...

Excel: Excel and arrays, column c, half days

column c, half days, e5: I m not sure WHEN I started doing this, though it was definitely more than 10 years ago! Your two questions are actually related - I like to learn about Excel and Word (and other parts of office) and a good way of doing that is by answering questions - and...

Excel: Excel Basketball Statistics, Sixthsense Excel

Sixthsense Excel: Hi Thomas, The Average function never consider the Text Values (DNP) in calculation. Its better to us Average function like =AVERAGE(N9:N15). Adapt the cell range N9:N15 to your desired range. Dont hesitate to contact me, if your requirement is not...

Excel: Excel crashes when saving, after running large macro, filename path, file excel

filename path, file excel, excel macro: Mike, as a followup to my previous response to modify you main routine to put the sheets in the proper place. I have given you an example here of how to do that (based on my understanding of how you are arranging the files). Sub CombineMultipleRPTs()...

Excel: Excel error warning box, cell references, exact wording

cell references, exact wording, spreadsheet: I SUSPECT that you may have deleted rows on the template - if for instance you delete rows 1 to 10, and a formula refers to those rows, as all rows have now moved to 10 rows less, this formula now refers to negative cell references - which don t exist, which...

Excel: Excel formula., attendance bonus, excel formula

attendance bonus, excel formula, blank rows: Sorry, I d really like to help, but you re uploading full sheet shots with all the blank rows/columns visible and the forum is shrinking that image down further than my bad eyes can read. You will have to edit/crop your image down to just section of data,...

Excel: Excel-Formula, question pool, format button

question pool, format button, excel formula: Deep, You didn t ask me this question - I found it in the question pool. It sounds like you know how to conditionally format something and you just wnat help with the formula. In the name box type A1:AK705 and hit enter this will select all those...

Excel: Excel Formula, Sixthsense Sixthsense

Sixthsense Sixthsense: Hi Laura, The workbook should be in Manual Calculation Mode. Press F9 to calculate the whole workbook and Shift+F9 to calculate the Active sheet. To change the mode of calculation in Excel 2007 Click the Microsoft Office Button, and then click Excel...

Excel: Excel-Formula, format painter, excel formula

format painter, excel formula, thanks in advance: I would select the first row and set the formulas =AA1= YES (when entered in row 1) and =AA1= NO with the required formatting - then use format painter to apply this to other cells OR use the applies to box to change the range it relates...

Excel: Excel formula, sick hours, cell b11

sick hours, cell b11, excel formula: D The formula is =IF(B11 80,0)+IF(AND(B11 79,B11 152),4)+IF(B11 151,8) The formula refers to cell B11 where there is a total for all of the weeks. You should be able to copy and paste the formula directly to your sheet. Hope this helps Richard...

Excel: Excel Formula, excel formula, ogilvy

excel formula, ogilvy, e1000: Christine Shores if you have excel 2007 or later =countifs(I:I, 7 ,E:E, Monday ) note that the function is the new COUNTIFS function with an S on the end. in Excel 2003 and earlier =Sumproduct(--(I1:I1000 7),--(E1:E1000= Monday )) in Excel...

Excel: Excel Formula, Sixthsense Excel

Sixthsense Excel: Hi Krishna, Vlookup Or Index & Match Function will do the job. I have given below some of my sample file links for you, Click or Copy and paste the below link in web browser to download the Example files. Vlookup https://docs.google.com/leaf?id=0B2TMxNyEPQ2rY2QxNGY4ZDUtODEwOC00MzZhLWJkNTctZDkxYzY0M2VmYTli&sort=name&layout=list&pid=0B2TMxNyEPQ2rOGU0MjRkZTktZjhlMy00MTFkLTk0YWQtYzQ2MDMzZTRhYzhi&cindex=52...

Excel: excel formulas, question pool, bill lang

question pool, bill lang, input areas: Bill Lang, You didn t ask me this question - I found it in the Question Pool. If you still need help with this, send me a sample workbook (just the parts you need help with - it can hold dummy data) and an explanation of what you want to do and where you...

Excel: Excel formulas, therapy sessions, excel formulas

therapy sessions, excel formulas, physical therapist: Jenna, When is the 2nd visit going to happen during that week, if Wednesday is the first visit? I don t readily have a formula for you, but the attached image may help to show that visit 19 has to happen past week 8 (2 x 8=16). Visit 13 can happen during...

Excel: Excel lists, data validation, aidan

data validation, aidan, spreadsheet: I m GUESSING that this is a list used for data validation - however, the same is BASICALLY true for any list - excel knows that a list extends from row X to row Y. Any cells after Y or before X are therefore outside of the list - so you would have to manually...

Excel: excel macro error, excel objects, macro error

excel objects, macro error, sheet tab: What about hitting alt+F11 after opening one of those files and checking the project explorer. Expand all projects in there, expand all tree items which say Microsoft Excel objects . Look for the sheet in question. If it appears there, click on it and hit...

Excel: EXCEL OUTPUT ORGANIZATION, road segment, excel formulas

road segment, excel formulas, pivot table: seems like if you use a pivot table, you wouldn t need to reorganize the data at all. If this doesn t help, send a sample of the data to me so I can see what would need to be done to reorganize it... bobumlas@hotmail.com, use subject of AllExpertsQ ==================================...

Excel: Excel question, countif, column c

countif, column c, ogilvy: Tom, in c1: =(countif(A1, *word* ) 1)*24 will display 24 if the value in A1 constains the substring word you could then drag fill this down column C for as far as you had values in column A I suspect you want something different from this -...

Excel: Excel Sheet to Sheet, littlewing, jennifer jennifer

littlewing, jennifer jennifer, gmail: Jennifer There are a couple of options available, it would be up to what the first sheet is setup like. would it be possible for you to send me a copy so I can give you the best idea. My email is richard(dot)littlewing(at)gmail(dot)com Richar...

Excel: EXCEL SPREADSHEET, meaningful answer, excel spreadsheet

meaningful answer, excel spreadsheet: It s certainly possible, but I d need more information to provide a meaningful answer - the quickest way to find a name with the data I have is to press Ctrl F (which is the find command) and type the name you want - but I m sure that isn t what you meant...

Excel: Excel VB macro, custom filter, string x

custom filter, string x, occurances: I don t make heads nor tails of your sample data pictures. But it s easy to filter a data set like that and copy. 1) Turn on the Data Filter Autofilter 2) Apply a Custom filter for x* 3) Copy the visible rows to your other sheet 4) Apply a...

Excel: Excel-vba, excel vba, column c

excel vba, column c, ami: You changed my working evaluation formula to something that doesn t work. MATCH works on a single column or row. If you need to search multiple columns, perhaps: Sub Example() If Evaluate( COUNTIF(AN:AS, AMI ) 0 ) Then _ Range( C7:C33...

Excel: Excel VBA Macro - searching text in range, vba macro, excel vba

vba macro, excel vba, word apple: Irma, You can use the Inputbox to get the string you want to use/check for. Sub Process_Data() Dim lastrow As Long, i As Long Dim r as Range Dim ans as String lastrow = Cells(Rows.Count, A ).End(xlUp).Row ans = InputBox(Prompt:= Enter the Exclusion...

Excel: Excel VBA Macro - SUMIF, vba macro, excel vba

vba macro, excel vba, ogilvy: Irma, I will assume your data is always sorted on the values in column A since that is what you show in your example. If that is not the case, then post back. Test this on a COPY of your data until you are sure it does what you want. Sub Summarize()...

Excel: Excel - VBA for showing Cell Ref, excel vba, sheet tab

excel vba, sheet tab, cell a1: you can put this in cell A1 and fill down to A100: =CELL( address ,A1) then you can right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Range( A1:A100...

Excel: External links in formula only update while file open, abc x, timesheet

abc x, timesheet, timesheets: INDIRECT doesn t work on closed workbooks. You ve discovered that. If this is a MUST, then you ll have to install more functionality into your Excel program. Only people with the same installed ADD-IN will be able to use your sheet. The ADD-IN is called...

http://en.allexperts.com/q/Excel-1059/2011/6/f/compare-text-copy-text-1.htm

Excel: Create a list of Sheets, with data from the top rows in a new sheet, vba macro, questioner

Excel: Create a list of Sheets, with data from the top rows in a new sheet, vba macro, questioner

Excel: Data Validation to prevent duplication and ensure 6-digits entry, politeness, lack of knowledge

Excel: Data Validation to prevent duplication and ensure 6-digits entry, politeness, lack of knowledge, ogilvy

Excel: formula ?

Excel: formula ?

Excel: Interpolate between two dates, bottom right, b2

Excel: Interpolate between two dates, bottom right, b2, refinement

Excel: Is its possible to create a formula that will give me different values in the same cell?, vlookup, z21

Excel: Is its possible to create a formula that will give me different values in the same cell?, vlookup, z21, f 65

Excel: VBA code, recommended string, command button

Excel: VBA code, recommended string, command button, a1

Excel: left and find function, paste values, ogilvy

paste values, ogilvy, services ltd: UPOrcut assume the first entry is A1, then in b1 put in the formula =Left(A1,find( - ,A1)-1) then drag fill down column B. Select column B and do a copy, then paste special and select values (paste values in Excel 2007 and later). now delete...

Excel: Find & replace macro, berd, traditional model

berd, traditional model, good solution: James No. You want the replacement text to be what? What is the actual cell ? What does the copy part of your code have to do with the replacement? If you explain what you are trying to accomplish, I am sure we can come up with a good solution. ...

Excel: to find the second value in a range, Sixthsense Excel

Sixthsense Excel: Hi Rajesh, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. https://docs.google.com/leaf?id=0B2TMxNyEPQ2rZTMxMzkwODAtNjNjMS00ZWI4LTk0ZmEtYmUyMmM4NjZhMWNl&hl=en_US Hope that helps....

Excel: Find word, blank row, cell1

blank row, cell1, word question: TJ, I replaced your sum formulas with subtotal formulas subtotal does not include hidden rows/cells in the specified range when the first argument is 109 subtotal does not include cells containing the subtotal worksheet function when it sums the...

Excel: Finding a cell with specific text in 2-d range of cells., array formula, hlookup

array formula, hlookup, index function: Tim match will work across multiple columns but only one row or it will work across multiple rows but only one column. If neither of those will work for you, then you need to use an array formula. That will only work on one sheet. If you want...

Excel: Finding Max of date in Excel for a Ticket Number, array formula, curly brackets

array formula, curly brackets, max function: Nazeeb, I put the first node ID in cell D4. You can list all the unique node IDs below that in D5 and on down. Then drag fill the equation from E4 down the column next to the list. You can get a list of unique node ID s by selecting your data in...

Excel: follow-up to "Need Excel to update dates upon reaching a specific date', martha martha, origination data

martha martha, origination data, experation: Martha, If you type in my first formula, then you specify the day and month when the expiration date is and the formula manages the year. So you don t need two cells. this formula would appear as the cell to display the expiration date. My second...

Excel: formula?, g1 b1, dennis excel

g1 b1, dennis excel, mr cowen: Dennis Excel has lots of database formulas to exactly what you want. The formula =SUMPRODUCT is by far my favorite and the most powerful and it should work in your case. If your dates in in A1:A18 and your numbers to be totaled are in B1:B18 the following...

Excel: formula ?, good luck

good luck: Hi Dennis, Yes, it is possible with an If Statement (see below) =IF(Sheet1!A1=0, Blank ) You can have 8 Nested Ifs, so if I were looking for a result from Sheet1 where cell A1=0, the resulting value would be Blank. Good luck and hope this helps...

Excel: Formula, k2, drop down list

k2, drop down list, n2: In M2: =IF(J2= Long ,K2-L2,L2-K2) and fill down In Q2: =IF(J2= Long ,N2-K2,K2-L2) and fill down In O2: =Q2/M2 In V2: =IF(J2= Long ,S2-K2,K2-S2) and fill down in T2: =V2/M2 Then select all these formulas and fill down as far as necessary ...

Excel: formula in excel, hp 1000, insurance premium

hp 1000, insurance premium, hp: This sounds like a simple lookup table. Here s a picture of how you would setup that table. Notice the beginning of each range marks each tier, both in AGE down the left side of the table and in CC/HP across the top of the table. The INDEX/MATCH function...

Excel: Formula inbedded in a Macro, inbedded, autofilter

inbedded, autofilter, google: This doesn t really sound like a simple question, it sounds like the kind of thing you hire someone to do for you. The logic I would use would go something like: 1) Identify the data sheet 2) On each summary sheet list a series of criteria at the top...

Excel: Formula question, cell references, ogilvy

cell references, ogilvy, f2: Tom You are basically adding the first 4 cells and subtracting the last 4 cells in you example. So you could do it like this =SUM(F1:F4)-SUM(F2:F5) if you were doing it down to F10 =Sum(F1:F9)-Sum(F2:F10) then looking at that, you can see your...

Excel: Now formula updates all the Cells,, target value, custody staff

target value, custody staff, sheet tab: assuming the column with the X is column C, right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And UCase(Target.Value) = X Then Cells(Target.Row, 4).Value = Now End Su...

Excel: If formula using vlookup, vlookup, l4

vlookup, l4, asset value: There s nothing wrong with the formula, so maybe there s something wrong with the range named fee -- fee must be at least 2 columns wide, What s in L4? What s in K4? If this doesn t help, send a sample wb to me at bobumlas@hotmail.com, use subject of ...

Excel: formulas, pivot tables, array formulas, s parameters

array formulas, s parameters, excel formulas: If the pivot table werer based on a table, then all that would be needed is a simple macro that would run whenever the pivottable was updated to recreate the sheets. If you send me a smallish sample of the data, I can point out what s needed, if yuo need help....

Excel: Graph changes size after refresh, ms knowledgebase, graph changes

ms knowledgebase, graph changes, positioning properties: J, I haven t run across this problem. I did some research to see if there is a known bug - but didn t find much. One thing you can try is to set the object Positioning Properties of the chart to not move and size with cells - this may help. this...

Excel: importing emails to Excel, csv excel, question fields

csv excel, question fields, aidan: Yes, it s possible - though it would PROBABLY need a macro - it s presumably a question of reading the first question, finding its column in excel and then writing the data. I would PROBABLY expect to do this without actually exporting the data - a macro...

Excel: Inserting Rows with formula and format between specific location, vba code, formating

vba code, formating, msgbox: First, assign the name Here to the first row after the formatted cells, then run this code: Sub Inserter() norows = Application.InputBox( How many rows to insert? , Type:=1) If norows 1 Then MsgBox Invalid # of rows -- stopping... ,...

Excel: Interpolate between two dates, dear mary, return key

dear mary, return key, interpolate: Hi Ryan, Unfortunately, I do not know of any way to do this except to autopopulate the dates in the B1 to B31 columns. To autopopulate, type the start date (i.e., 1 May 2011) and click on the Enter/Return key on your keyboard. Return to the cell with...

Excel: Is its possible to create a formula that will give me different values in the same cell?, graph functions, graph data

graph functions, graph data, raw score: Hi Katie, There is no way to do this unless you want to name the range (maybe name the 8 nested if formula) and then do another series of 8 nested ifs. I am not sure if this would work, but it is worth a try. Regarding your graph, it is unclear as to...

Excel: Kind of a Max IFS question, array formula, sheet1

array formula, sheet1, highest score: Carol, =MAX(IF((Sheet1!$M$2:$M$500= ABC Major )*(Sheet1!$P$2:$P$500= Yes ),Sheet1!$J$2:$J$500)) =MIN(IF((Sheet1!$M$2:$M$500= ABC Major )*(Sheet1!$P$2:$P$500= Yes ),Sheet1!$J$2:$J$500)) enterd with Ctrl+Shift+Enter rather than just enter (hold down...

Excel: List from data base, vlookup, office microsoft

vlookup, office microsoft, matur: Ines, Ashish Matur had documented how to do this in much more detail (and clearer) than I could post here: http://office.microsoft.com/en-us/excel/HA012260381033.aspx?pid=CL100570551033 shows how to return multiple values for a single lookup value....

Excel: A List that Removes Blank Cells, Duplicate Data, and Automatically Updates, cell b6, blank cells

cell b6, blank cells, better solution: If you have column N available this will work fine: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Application.ScreenUpdating = False Columns(14).Clear...

Excel: Logic on macros, Excel Sixthsense

Excel Sixthsense: Hi Sam, In my profile I have stated that I am not having proficiency in VBA (Macros), but the same can be achieved in using Validation in excel. Have a look in the below link to know more about it. http://www.contextures.com/xldataval02.html Hope...

Excel: Lookup info from two or more tables, timein, input area

timein, input area, cat mouse: On a new sheet, in range A1:A3, enter dog cat mouse In range B1:B3, enter red black grey Name range A1:B3 animals . The range name animals is now a table connecting animals and colors. In cell D1, enter animals In cell E1, enter...

Excel: FOR loop based on IF, column c, human actions

column c, human actions, macro recorder: Looking at your desired end results reveals a macro still isn t really needed, nor is your Duplicate test column. With values in column A, put this formula in B2 and copy down: =A2 & COUNTIF($A$2:$A2, $A2) The good thing about this technique...

Excel: Loop through Userforms, knowledge base article, ccont

knowledge base article, ccont, userforms: Steve, the useforms collection is the collection of loaded userforms. So you need to load your userforms before you can loop through that collection. I assume you are not trying to discover what userforms exist, so I supply a list of the userforms to...

Excel: Lost excel spreadsheet in Excel 2010, unsaved files, excel spreadsheet

unsaved files, excel spreadsheet, real option: Julie the only real option you have is to do a search using open dialog box and search within files for a specific keyword. If you don t have the original saved there may be a backup copy on the harddrive. If you saved it to a usb then you may be in more...

Excel: Macro code error, subscript out of range, srow

subscript out of range, srow, b cells: Derrick Wong, MsgBox List Generated , vbInformation, Transfer Done would become MsgBox sSheetName & Generated , vbInformation, Transfer Done Notice that sSheetName does not appear between any double quotation marks. It is concatenated...

Excel: Macro runs really slow. Better process?, c range, lbo

c range, lbo, hh: TJ, You seem to quite when one of the entries you search for has been found and then search for the next one. Can I conclude that only one row matches each v(j). Can I conclude that every v(j) WILL have on matching row. The searching can certainly...

Excel: Macro, blank sheet, ontime

blank sheet, ontime, const: Hi Troy, I am assuming that the workbook is opened each morning earlier than 7 am so that it is open at the time the sheets should be printed. If this is not the case, then you would need to set a Windows automatic startup task that opens the workbook each...

Excel: Macro, bank pcl, sheet names

bank pcl, sheet names, private bank: No, sorry for not being clearer Sub FORMAT1() FORMAT Macro Macro recorded 5/8/2008 by Edward H Mori Sheets.Select Cells.Select Selection.EntireColumn.Hidden = False Columns( A:A ).Select Selection.Delete Shift:=xlToLeft...

Excel: Macro to find sheets, insert hyperlink, support thanks

insert hyperlink, support thanks, job: Billy The easiest way it probably to insert a hyperlink. On the Insert tab, there is an Insert Hyperlink item. It is easy to follow and will let you insert a hyperlink to any cell on any sheet in your document. If you want to use a macro, that too is pretty...

Excel: Macro Help - Find the cell with the highest value in a column, range selection, column c

range selection, column c, max value: Wayne, assume you have a named cell with the name that holds the number of rows to fill. Assume that if that number were 10 as an example, then the formulas would be filled in A1:P10 (so row 1 is included in the count of the number of rows. Sub AAA()...

Excel: Macro for paste special Transpose, estimate sheet, h18

estimate sheet, h18, s56: Sub Nikhil() n = 16 For i = 19 To Sheets( Estimate ).Range( IV5 ).End(xlToLeft).Column Step 2 n = n + 1 Sheets( Estimate ).Cells(5, i).Copy Sheets( Summary ).Cells(n, 2).pastespecial xlpastevalues Sheets( Estimate ).Cells(6,...

Excel: Macros for pasting the content of a text file, macro button, myway

macro button, myway, input files: Hi Thripura, It would help for me to see the code you are now using so I can suggest the best way to modify it or the best alternative code. Can you email me your workbook? If your workbook is proprietary then can you at least email me the code? My...

Excel: Merging data from excel sheet into excel forms, excel forms, excel sheet

excel forms, excel sheet, mail: I m not aware of an easy way of doing this, though in theory there should be - http://en.allexperts.com/q/Microsoft-Word-1058/2009/3/Grouping-mail-merge.htm indicates that other experts have struggled with it! I ve TENDED to do this by a bit of VBA to create...

Excel: Microsoft Excel, excel passwords, vba code

excel passwords, vba code, excel workbook: Ron alt-F11 should take you to the VBE (visual basic editor). Make sure the Project Explorer is visible (under View, Project Explorer if it is not) You should see your workbook. Under your workbook you should see a row for each sheet, and after that...

Excel: Missing Number, numerical receipt, receipt numbers

numerical receipt, receipt numbers, richard florida: Meenakshi I may not full understand the quesion or how your data is arranged, but I will try. Assuming your data is arranged in numerical receipt order, or if not you can sort it into numerical order. A formula comparing a number with the previous...

Excel: Missing Receipt Numbers, Excel Sixthsense

Excel Sixthsense: Hi Meenakshi, I have created two sample files for your reference and you can download the files in the links provided below; Find Missing No (Start & End Value - Automatic).xls https://docs.google.com/leaf?id=0B2TMxNyEPQ2rZGZmMjFhYWMtYmRjNi00MzVkLTg2NDEtNTMyODg4MjAyYzQ5&sort=moddate&desc=true&layout=list&pid=0B2TMxNyEPQ2rOGU0MjRkZTktZjhlMy00MTFkLTk0YWQtYzQ2MDMzZTRhYzhi&cindex=1...

Excel: Modify from Record Macro, column letter, scol

column letter, scol, macro function: Derrick Wong, Yes Private Sub CommandButton1_Click() Dim rr As Range, sc As String Set rr = Worksheets( Alloc2E ).Range( A7:BC210 ) sc = R Ranksort rr.Parent.Name, sc, rr End Sub Sub Ranksort(Activesht As String, sCol As String, r As Range)...

Excel: Multicolor Date Format, format dates, negative numbers

format dates, negative numbers, single unit: As far as I know you won t be able to do this, though it is possible someone else knows differently! In text format, I assume you have formatted each part of the cell as required - in any other format, the cell is a single unit, so can have conditional formats,...

Excel: multiple macvros, autofit, column width

autofit, column width, possable: Billy, If I understood you requirement correctly, this worked for me. I understand you want to loop through every sheet in your workbook and perform the listed actions on each sheet. See my note below about reversing the order of setting the width...

Excel: Need Excel to update expiration dates upon reaching a specific date, question pool, 24th day

question pool, 24th day, date function: Martha, you didn t ask me this question - I found it in the question pool. This is the general approach I would use =IF(today() DATE(YEAR(today()),1,24),DATE(YEAR(today()),1,24),DATE(YEAR(today())+1,1,24)) so the date function is =Date(year,month,day)...

Excel: need a excel vba inventory spreadsheet for footwear company, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Shahid, The task provided to me should not be performed by assumptions of your data and requirement. It requires lot of input from your end and your expectations which will take more time. Based on your requirement I suggest you to go for Ms-Access...

Excel: Nested items in exel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Without knowing more about your problem it s impossible for me to help you, but usually people who need to nest more than 3 or 4 items should really be using a lookup function like VLOOKUP. http://www.599cd.com/tips/excel/vlookup?key=AllExperts Let...

Excel: Please help with Excel!, text string, a5

text string, a5, cells: Vanessa If your data is in A1:A5 the following should do what you want: =IF(SUMPRODUCT(--ISBLANK(A1:A5)) 0, missing price ,SUM(A1:A5)) Most formulas that work like this need to be array entered (shift-control-enter) but SUMPRODCUT does not. I hope...

Excel: Payroll Timesheet Calc Help, payroll timesheet, vacation hours

payroll timesheet, vacation hours, vacation day: Barbara You need to change the formula in your Total Hours cell so it doesn t include the vacation hours, or if you need that total for something else you need another cell that totals all the hours that contribute to an employee s first 40 hour, triggering...

Excel: Permutations, format button, home tab

format button, home tab, cell a1: Randy, the only change would be to change the formula for the defined name Permutations. It would now be =CHOOSE({1,2,3,4,5,6},(MID($A$1,1,1)&MID($A$1,2,1)&MID($A$1,3,1)),(MID($A$1,2,1)&MID($A$1,3,1)&MID($A$1,1,1)),(MID($A$1,3,1)&MID($A$1,1,1)&MID($A$1,2,1)),(MID($A$1,2,1)&MID($A$1,1,1)&MID($A$1,3,1)),(MID($A$1,3,1)&MID($A$1,2,1)&MID($A$1,1,1)),(MID($A$1,1,1)&MID($A$1,3,1)&MID($A$1,2,1)))...

Excel: Please help with Excel!, text string, cells

text string, cells: Assuming the cells to sum are B2:B10, then this formula would do it. =IF(COUNTA(B2:B10)=9, SUM(B2:B10), Missing Price ) B2:B10 has 9 cells, that s why there is a 9 in that formula. When you adjust this for your real range, make sure the number...

Excel: possible IF function questions, question pool, f200

question pool, f200, ogilvy: Andrea, Your posting is not very clear as to what you want which is probably why it was put in the question pool by whomever you asked. I will make a guess at what you want: if you want the count of rows that have numbers in them for a single column,...

Excel: Pre existing phone #'s, formatted values, direct email

formatted values, direct email, kinds of variations: I m not sure what you mean by the special format phone number, but it SOUNDS as though you have a lot of mis-formatted values, and need to modify them - POSSIBLY the substitute worksheet function could be used if there are standard errors, but I d need more...

Excel: Preparing a checklist in excel, notepad files, getfolder

notepad files, getfolder, files names: Sorry about the delay in replying - my daughter has been unwell (nothing serious, but it s made it difficult to find time to deal with questions) This code Sub ProcessListFilesInFolder() Cells.Delete With Range( A1 ) ...

Excel: Printing Macro, procedure test, macro recorder

procedure test, macro recorder, blank sheet: troy The best way to learn about that kind of stuff is to use the macro record. Turn on the macro recorder and record yourself doing what you want to happen. Then rerun that code as part of the macro I started for you. To get to the macro recorder, you...

Excel: Question, question question, cells

question question, cells: Try this: Sub SelectDate() Dim i As Integer, cl As Integer, rw As Integer For i = 1 To 300 If Month(Cells(4, i).Value) = Month(Date) And Day(Cells(4, i).Value) = Day(Date) Then cl = i Exit For End If Next For...

Excel: Randomly distribute a list 6 times with no repeats..., group networking, speed session

group networking, speed session, group move: You can have a list of the values in one column, and in the next column you have =RAND(). You sort both of the columns by the RAND() column and the 90 #s are effectively shuffled. In your layout, you reference fixed cells, like P1, P2, P3, etc. but after the...

Excel: Randomly distribute a list 6 times with no repeats..., wendy shepherd, formula b4

wendy shepherd, formula b4, attendance list: Steve, think about what you have to do if you were to assign the rounds manually. Everytime you assigned a person to a table, you would have to check previous rounds to see if that person was assigned to that table. Then you have to check whatever other...

Excel: re formulation my question, curly brackets, array function

curly brackets, array function, max function: Bassam You need an array function. The following should work {=MAX(--(A2:F2=1)*A1:F1)} The curly brackets are not typed in; they should up automatically when you array enter your formula. To do that you use shift-control-enter. The first term...

Excel: Replacing 'invisible' characters, Sixthsense Excel

Sixthsense Excel: Hi John, Use the below formula to find the character. Copy and paste the invisible character in between the double quotes and you will get the code of that character. Then use that code number in your Macro. =CODE( ) Dont hesitate to contact...

Excel: Return cell value once, valu, len

valu, len, spreadsheet: You need a user-defined function. Press Alt/F11, then Insert/Module, then enter this: Function Simplify(rg As Range) Dim st As String num = Len(rg) - Len(Application.Substitute(rg, , , )) For i = 0 To num valu = Split(rg, , )(i)...

Excel: Rounding, cell a1, budget report

cell a1, budget report, final result: Hello Josh, Thank you for the additional information. That helped me make sure the formula I give you will be correct. Use this formula: =ROUND(a1*1.04,-2) where the number you want to round up after increasing it by 4% is in cell A1. If the number...

Excel: Rounding question, decimal digits, question pool

decimal digits, question pool, whole number: Mary, You didn t ask me this question. I found it in the Question Pool but will attempt to help. My first thought was to use =ROUNDUP(A1,1)-0.01 which worked well except if the value contained a whole number such as 7 or something like 7.1 rather...

Excel: Specific Column Reference, mm ss, time column

mm ss, time column, column name: Jun Sub StartTime() Dim r as range, res as variant set r = Range( A1 ,Cells(1, columns.count).End(xltoLeft)) res = Application.Match( Start time , r, 0) if not iserror(res) then With Cells (ActiveCell.Row, res) .Value = Now .Number Format...

Excel: Specific Column, time value, f7

time value, f7, ogilvy: Jun Sub StartTime() With cells(ActiveCell.row, F ) .Value = Now .NumberFormat = mm/dd/yyyy hh:mm End With End Sub change the number format to what you want. this doesn t use the now() worksheet function. It uses the VBA function...

Excel: Specific column, column c, ogilvy

column c, ogilvy, cells: Jun, if you only want the macro to run if the activecell is in column C (as an example) Private Sub MyMacro() if activecell.column 3 then exit sub rest of your code Also, the activecell might be in column 3 but other columns might be...

Excel: Search, Find, and Display on Seperate Page "VBA", sheet names, bhas

sheet names, bhas, sh1: Elizabeth, you have to combine the Find with FindNext to find them all Here is some sample code: Sub Macro1() Dim rng As Range, sh1 As Worksheet, sh2 As Worksheet Dim rw As Long, sAddr Set sh1 = Worksheets( Source ) Set sh2 = Worksheets( Destination...

Excel: Selective copying in Excel 2011, artbeats, queen mary

artbeats, queen mary, ogilvy: Morgan Before A2: QRF9366TR TEMP queen mary los angeles harbor flight 004-C003R 3d [rf] ~artbeats.mov B2: QRF9366TR After A2: QRF9366TR B2: QRF9366TR is that what you are asking. Just select all of column B and do a copy then select...

Excel: Simple Stock inventory counting 2, target value, stock inventory

target value, stock inventory, dear tom: Wan, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, r1 As Range, res As Variant If Target.Count 1 Then Exit Sub If Target.Column = 7 Then == entry in column G If Target.Row = 8 Then If Len(Trim(Target.Value)) 0 Then Set...

Excel: Solver Macro help., target cells, target cell

target cells, target cell, question pool: Russ, for your sample, goal programming gave me I7= 17353.6794551978 The value in AC7 was 0. I adjusted the maximum change value in Excel options under iteration to 0.000000000001 and enabled iteration. (maybe that is your problem) Anyway, I can...

Excel: special search and movie in excell, english question, search method

english question, search method, first language: I m afraid this seems to be somewhat out of my area of expertise, though it may be that I still haven t grasped the task that you are trying to accomplish. It s ALMOST sounding as though you simply want to find all matches on all sheets (at which point, select...

Excel: specific condition, Sixthsense Excel

Sixthsense Excel: Hi Neyaz, It s better to ask your exact requirement instead of giving your formula and explaining your expectation in half is making me to go for several assumptions. At the same time your formula and your query is not in line and I am confused what your...

Excel: IF statement and cell referencing, apple 2, excel programming

apple 2, excel programming, wildcard character: Hi Meg, I m not sure I understand your question. If what you are seeking is a formula that will yield 2 if the string starts with apple and 4 if it starts with orange then the following formula will do it: =IF(LEFT(A1,5)= apple ,2,IF(LEFT(A1,6)=...

Excel: If then statements with columns, cell a1, vlookup formula

cell a1, vlookup formula, richard florida: Don If I am following your problem correctly, you need to use a VLOOKUP formula The formula might look something like this =VLOOKUP(A1,D1:F30,3,FALSE) The formula says lookup the value that is in cell A1 within the array D1 thru D30 And if it...

Excel: How to sum a set of values based on name criteria, excel sheet, patient name

excel sheet, patient name, p3: Hi Aseem, I am not sure that I understand the question, so let me attempt to restate the question and propose a response. Example: A B C D 1 Name Procedure Date Amount 2 P1 3 P2 4 P3 5 P4 On Summary Sheet (Insert Sheet...

Excel: SUMIFS QUESTION, b31, string values

b31, string values, cata: Cal, My guess would be that GP_CATA_01 contains numbers (such as you show 3333) rather than a string value like 3333 given that assumption, then you can get your answer with =SUMIFS(GP_Amount_01,GP_WK_01,C$20,GP_COUNTRY_01,$B31,GP_CATA_01, 3000...

Excel: Sumproduct including text, row labels, time sort

row labels, time sort, time tracker: The only reasons a formula that returns an answer in one instance then returns zero in another instance are: 1) The data no longer has matching value for the formulas test 2) The formula changed Which is true in your case? What all exactly does your...

Excel: tagging performance, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Teresa, I hope your last criteria Poor should be 1.60. I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. https://docs.google.com/leaf?id=0B2TMxNyEPQ2rNTNkOGYxNzctZTViMi00NDJiLThhNjgtYWY5MzZkOWMxOTA4&sort=name&layout=list&pid=0B2TMxNyEPQ2rMjM3ODQ5MDctNDU1ZS00ODA3LTlhYzItNDg2ZjA0YjE4ZWUw...

Excel: Template to chart data from csv files, csv files, csv file

csv files, csv file, column chart: sure - bobumlas@hotmail.com, use subject of AllExpertsQ ================================== Are you interested in online Excel classes led by me? 3 are currently scheduled: June 23, 7-8PM (NY Time) VBA 101 Part 2 June 27, 7-8PM (NY Time) Userforms...

Excel: Tracking individual changes in mutipal cells, cell b2, time example

cell b2, time example, single action: Shawn, Just list them sequentially with the proper If statement to test if the cell for that process was in the cells that triggered the event. Here is an example consistent with your Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating...

Excel: Traffic Light Flagging System Drawing Status - Excell 2007, status cells, target date

status cells, target date, conditional formatting: I might be able to help. Use my profile to get my web site and send me your file to look at through there. Be sure to include a link to this thread to help remind me what we re talking about (I help a lot of people on various forums). In the sample workbook,...

Excel: Transforming a letter grade to a numerical equivalent, numeric equivalent, numerical equivalent

numeric equivalent, numerical equivalent, numerical format: Fer, you can use a formula like this =VLOOKUP(F1,{ A+ ,100; A ,95; A- ,90; B+ ,85; B ,80; B- ,75; C+ ,70; C ,65; C- ,60; D ,30},2,FALSE) so if you have a grade in F1, this formula will return the numeric equivalent (for example if F2 has A- then...

Excel: User Form to Worksheet, mileage data, search worksheet

mileage data, search worksheet, input data: Harry, this would be my take on the answer to your question. Private Sub CmdRemovePS_Click() Dim ws As Worksheet, myrow As Long Dim c As Range, sAddr As String Set ws = Worksheets( Insert Picks_Summary_Report ) If Len(Trim(Me.txtItem.Value))...

Excel: unhide multiple row by vba, false application, ogilvy

false application, ogilvy: Vimal, If they enter the data in the same format as you have which would be 1:1,5:5,10:10,36:36 then you would change you Set R = Rows(U) to Set R = Range(U) to demonstrate from the immediate window: ?Range( 1:1,5:5,10:10,36:36 ).entirerow.Address...

Excel: update data, swathi, vb script

swathi, vb script, ogilvy: swathi say you want to increment the value of a cell Sub ABC() Dim i as Long i = 1 do while i 10000 Range( B9 ) = i i = i + 1 Loop End Sub the problem is that macro will move very rapidly - perhaps too rapidly to see. Since I don t...

Excel: Using the last row in a user defined function, question pool, counta

question pool, counta, header row: Mark, you didn t ask me this question - I found it in the question pool you could create a defined name with the name lastrow. Then use that in your formula as you show. Name: LASTROW Refers to: =OFFSET(Sheet1!$A$3,COUNTA(Sheet1!$A$3:$A$65536),0,1,1)...

Excel: Same Value in different cells, cell a1, spreedsheet

cell a1, spreedsheet, problem with this approach: Hello Steve, Thank you for your question. There are a couple of ways you can do this. If this is a one-time thing, you can select the 5 cells (A1, G85, J32, Z32, and R54) while holding down the Control key on your keyboard. Then, after you enter...

Excel: Value function, geological stratigraphy, soil parameters

geological stratigraphy, soil parameters, documents and settings: =INDIRECT(path_load) will work, but only if the external file K8129.xlsx is open. If the external file is not open, then =INDIRECT() wont work; this is a limitation of Excel. The only alternative would be a custom function in vba, like this one: http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd?pli=...

Excel: VB- filtering and copying, sh1, column c

sh1, column c, prl: Brenna, the code worked fine for me. It copied rows that just had PRL out in column AP as well as any other column in the range from C to AP. The only time I could reproduce your problem is if the row that was copied did not have data in column C...

Excel: vba animation, animation question, graphic animations

animation question, graphic animations, animation speed: Hi again Lee, I m still updating those examples. But in answer to your question, I suspect that something like MatLab might be a better application for developing and running the type of application you describe, but I m not sure about the whether you would...

Excel: VBA Code, agreement numbers, code question

agreement numbers, code question, e10000: Abdul Jaleel, ============= revision ======= I found one more glitch in the code which I have fixed and tested. It did successfully send the email with the attachment as well but you may have to respond to a security question on each email - that depends...

Excel: VBA Code, r2c, r2a

r2c, r2a, sh2: Abdul Jaleel, this is the line that copies the data into the new workbook r2d.Copy sh4.Range( A1 ) as you can see, there are no options offered. It is the same as a manual copy and paste. If a manual copy and paste works, then this should work. ...

Excel: VBA code, cell a1, time a change

cell a1, time a change, shortcut key: Jay What do you want to trigger the macro? If you want the process to happen every time a change is made to the worksheet, you could put this in the Worksheet change event code for the worksheet: Option Explicit Private Sub Worksheet_Change(ByVal...

Excel: VBA Code for hiding row with condition, code addition, blank row

code addition, blank row, ogilvy: Abdul Jaleel I assume bye no value, you mean the cell is blank. so the answer depends on whether the cells are realy blank or you have some formula like =if(b6=2, ,sum(d6:e6)) so that the cell just looks blank when some condition is meet. If...

Excel: vba excel 2007, ra range, ogilvy

ra range, ogilvy, rb: solman, this should do what you describe. I have assumed your data starts in row 1. Change the code if that is not correct. Sub ABCD() Dim rA As Range, rB As Range Dim cellB Set rA = Range( A1 , Cells(Rows.Count, A ).End(xlUp)) Set rB = Range(...

Excel: VBA, Excel, Array Looping, nrow, neach

nrow, neach, redim: Ty It seems like you are doing a whole lot of work to replicate the functionality of a pivot table. Assuming your data is in rows and columns, with no blank rows and no blank columns, and a header row; fairly reasonable assumptions based on your brief...

Excel: VBA export, explicit function, sname

explicit function, sname, address range: Sure, like this: Option Explicit Function SelectChartOnNamedRange(sName As String) Dim oCht As ChartObject For Each oCht In ActiveSheet.ChartObjects If oCht.TopLeftCell.Address = Range(sName).Address Then Set SelectChartOnNamedRange...

Excel: VBA to Export Data, createtextfile, column address

createtextfile, column address, c test: Hi James How are you? Please have a look at this. You still have to adjust to your problem Public Sub quoting() Dim T As String, R As Range Set R = ActiveSheet.Range( A:A ) T = For I = 1 To 1000 - 1 If R.Cells(I, 1) Then T = T + Chr(34)...

Excel: VBA to insert a formula into a cell, proper syntax, string copy

proper syntax, string copy, g10: Hi TJ, The easiest way to get the proper syntax is by manually putting the foemula in one cell, then do this: Alt+F11 to go to the VBE Control+g to get to the immediate pane type: ?ActiveCell.FormulaR1C1 and hit enter. You ll get the proper formula...

Excel: VBA Loop question, column c, excel vba

column c, excel vba, part of the loop: Hello Hunter, as written, this is used as a demo. have your values in column B starting in B2 with some cells in the range that need to use a different value. Then run this macro. It will write the decision it makes in column C so you can see how it...

Excel: Get VBA to run agains selected Worksheets, not just the Active work sheet, column c, range c

column c, range c, rng: Dale It is pretty easy to loop through all the sheets with something like dim ws as worksheet for each ws in activeworkbook.sheets do your stuff unless ws.name is the one you don t want to mess with end if If you only operating on a few...

Excel: VBA - shared file user #, file excel, ubound

file excel, ubound, different design: Hi Martin, I m afraid I do not know the answer to this question. However, I wanted to share some advice with you. Sharing workbooks is not a very good idea. The feature isn t well implemented in Excel and it tends to lead to various problems with different...

Excel: winzip and excel, Sixthsense Excel

Sixthsense Excel: Hi Mel, First of all having and working with a excel file with the size of 514 MB is not at all suggestible. I am sure that there is no guarantee for your file since it will get corrupted at any point of time. Follow my below guidelines. 1)Whether...

Excel: workbook protection, samuel smith, vba code

samuel smith, vba code, joe johnson: You can use VBA and protect the vba code so one can t see it... Let s assume your name is Samuel Smith and your friend s name is Joe Johnson Have an Auto_Open procedure something like this: Sub Auto_Open() if Application.UserName = Samuel Smith...

Excel: Working with Data filters, john walkenbach, autofilter

john walkenbach, autofilter, inputbox: Gary, 1) ans = MsgBox( Yes to Continue, No to Print? , vbYesNoCancel) If ans = vbno Then CALL YOUR PRINT MACRO elseif ans = vbcancel then Exit sub End if You talked about two separate things. Here is a possible answer for each. To...

Excel: Worksheet Formula, down arrow, ogilvy

down arrow, ogilvy, a4: Nick, the only methods that will do that are to manually rename the sheets or run a macro to do it. I can write the macro for you Sub RenameSheets() dim sh as worksheet on error resume next for each sh in worksheets sh.name = sh.Range( A4...

Excel: Wrapping text in a check box control, checkbox control, thanks in advance

checkbox control, thanks in advance, spreadsheet: As far as I know with form controls this is automatic - once you have edited the text and clicked away, the text will flow in the SIZE of the control - so you may need to make it taller than the default - right clicking will give you the sizing handles and...

Excel: "XIRR" formula problem, array formulas, paypal account

array formulas, paypal account, excel formulas: yes - send to bobumlas@hotmail.com, use subject of AllExpertsQ ================================== Are you interested in online Excel classes led by me? 3 are currently scheduled: June 9, 7-8AM (NY Time) Array Formulas June 23, 7-8PM (NY Time)...

Excel: 169 MB excel spreadsheet, adequate memory, excel spreadsheet

adequate memory, excel spreadsheet, wrong addresses: Crystal Size is not really a problem if your computer has adequate memory. I suspect there is more to the problem than just size. Is the file linked to other files? Are there pictures in it, or other objects that contribute to the size? It seems awfully...

Excel: Absolute Value Macro, command button, absolute value

command button, absolute value, dural: Jon Run this code from your macro and it should do what you want. Sub test() Dim r As Range Dim cl As Range Set r = Intersect(Columns(2), ActiveSheet.UsedRange) For Each cl In r If cl.Value 0 Then cl.Value = -cl.Value Next cl End Sub...

Excel: Accounting system set up with Excel, accounting system, richard florida

accounting system, richard florida, florida usa: Louisa You may not know, but your question is very broad. The term accounting system can include many many things. For example it may include the maintenance of inventory, the maintenance of accounts receivable including individual invoices for each...

Excel: on add picture function, properties dialog, excel spreadsheet

properties dialog, excel spreadsheet, initial post: Joe, sorry for the delay. I wanted to be able to test the code before I sent it since you seem to be having trouble with it. I pulled all the pictures out of a directory and fitted them to cells set for specific height and width. I matches the appropriate...

Excel: Adding Minutes, value error, minutes and seconds

value error, minutes and seconds, colons: You shouldn t get a value error adding large numbers though you might find it hard to see the results - a value error would indicate problems with a formula - I m happy to look at a sample file if this would help - my email is aidan.heritage@virgin.ne...

Excel: Adding and multiplying in one formula, array formula, course registrations

array formula, course registrations, af3: Carol, =sumproduct(a3:af3*100)+sumproduct(ag3:av3*200) 100 and 200 can be replaced by cell references that contain the old and new price such as =sumproduct(a3:af3*M1)+sumproduct(ag3:av3*N1) you can also do this =SUM(A3:AF3*M1,AG3:AV3*N1) but...

Excel: adjust text wrap with row spacing, optimum height, default size

optimum height, default size, double click: Now either I don t understand the question, or yes, you do need to size each row individually. When you double-click the row separator, aren t you getting each row displayed at the optimum height? They re not all the same, so it s not clear to me what you...

Excel: adjust text wrap with row spacing, text feature, richard florida

text feature, richard florida, florida usa: Sandi You can format all of the cells to what is needed for the wrap text feature by the following In version 2007 of excel or later Click on the top left junction cell thus selecting all of the cells. Then clic on home Then select format Then select...

Excel: Advance filter criteria, Sixthsense Excel

Sixthsense Excel: Hi Mario, First of all what I think is your dates are text values and not real dates and that is the reason you are facing this problem. Before going to do something in excel just check your system date format setting by doing the below. Start Run...

Excel: Too many arguments in Excel 2007, d19, d16

d19, d16, j11: It was rejected by the original expert as one to put into the question pool - I am painfully aware that this is often a way of getting rid of difficult questions without the person who asked actually getting an answer! I still suspect that the formula could...

Excel: array, variant operations in vba, cell c1, portfolio builder

cell c1, portfolio builder, cell a1: Try this (turned off calculation & screen updating), used with/end with blocks... see if this helps: Sub portfolio_builder() Administrative variables Dim vinclude As String Dim voffset As Integer Temporary Arrays [Variants] Dim temp_atcf Dim...

Excel: How to autogenerate & fix order no's, input cells, data validation

input cells, data validation, random value: With these constraints, I m not sure how I can help. If the workbook is locked except for INPUT cells, unlock the INV NUM cell, too and use the drop down trick I gave you to make the inv number permanent once selected. Do not use a formula in that cell....

Excel: Broken formula, national insurance number, employee id

national insurance number, employee id, missing data: You ll need some IFs, ORs, and ISBLANKs. You can use ISBLANK to determine if a cell is left blank. Use the IF and OR functions, and this will tell you if any of the four cells A1 thru D1 are blank. Put this in E1: =IF(OR(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1),ISBLANK(D1)),TRUE,FALSE)...

Excel: calculating a retirement eligibility date in excel, retirement eligibility, column c

retirement eligibility, column c, trues: Mario I guess I oversimplified it. The Trues are those who are currently eligible and the falses are those who are not. To calculate the date of eligibility you should only need the D.o.b. (assumed in column A) and the commencement of service date...

Excel: If a cell is blank use another cell, ogilvy, due date

ogilvy, due date, clarification: Ricardo, =if(EOT Date is blank,if(Start Date Due Date, No , Yes ),if(Start Date EOT Date, No , Yes )) is what it sounds like you are saying. If so the formula would be =if(AO7= ,if(P7 AN7, No , Yes ),if(P7 AO7, No , Yes )) Not sure where EOT...

Excel: Cell Color Formula, excell spreadsheet, conditional formatting

excell spreadsheet, conditional formatting, base colour: Technically, conditional formatting allows 4 variables (the base colour of the cell being the 4th option) BUT it ONLY allows up to 4 - you cannot get round this with conditional formatting in your version of Office - SO it needs to be done with VBA - see http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm...

Excel: cell with many numbers, adobe acrobat professional, numbers and words

adobe acrobat professional, numbers and words, excel sheet: Hi Mustafa, Based on the application you are using (Adobe standard or professional), you will be able to copy the table data as a table and paste easily into a spreadsheet, but you must be using the professional version of Adobe Acrobat (i.e., Adobe Acrobat...

Excel: cell selection, sumproduct, q11

sumproduct, q11, g11: Mike, If the drop down lists are not used the sumproduct cell will i suppose produce an error as it is unable to match the columns in sheet M no - it produces a zero. So you have a zero if there is no match and you have a zero if either cell is blank...

Excel: chaging the colour of the columns based on a single cell value using excel 2007 macros, format cells, format painter

format cells, format painter, followup question: Firstly, select all cells with formulas (or simply select all by clicking the square at the top left corner above the number 1). Right click a cell, then format cells, click on the protection tab and click the hidden checkbox. Once done, Use the Review...

Excel: Click counter macro, target value, target address

target value, target address, click counter: Nathon If the cell you want to provide this functionality to is A1, the the following code in the particular worksheet module should do what you want. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = $A$1 Then ...

Excel: How to make a column mandatory in excel?, Sixthsense

Sixthsense: Hi Prachi, Thanks for sending your question my way and I am also a novice in VBA like you. I stated the same in my profile also. I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that...

Excel: Combining hundreds of cell values into one cell, routine ends, cell values

routine ends, cell values, character limit: Function Combine(Rg As Range, Optional tf As Boolean = False) As String Dim Stg As String, Stg2 As String If tf Then Stg2 = Else Stg2 = For Each thing In Rg Stg = Stg & thing.Value & Stg2 Next Combine = Stg End Function...

Excel: Combining multiple rows with different data into one, cellular assays, gene synthesis

cellular assays, gene synthesis, colc: Try this routine: Sub Reorganize() Dim Comp As String, curr As Worksheet, n As Long Dim ColB As String, ColC As String, ColD As String Comp = Range( A1 ).Value ColB = Range( B1 ).Value ColC = Range( C1 ).Value ColD = Range( D1 ).Value...

Excel: Combining multiple rows with different data into one, set aws, cellular assays

set aws, cellular assays, gene synthesis: Hi Jean-Marc, I believe this macro will do what you describe. It will create a new sheet named Combined on which it will write the combined rows. The original data sheet must be the active sheet when you run the macro. The run will error if you already...

Excel: Complex formula for assigning points, obscure results, sports prediction

obscure results, sports prediction, prediction game: Based on the sample sheet, I would change the portion of the formula in G9 where your formula has a result of 5 , replace that whole section with this: 5 + (2*(SUMPRODUCT(($B$2:$B$16=$B9)*($D$2:$D$16=$D9))=1)) The resulting formula: =IF(Results!$S$2=...

Excel: Conditional Formatting, conditional formatting, cell a1

conditional formatting, cell a1, richard florida: Alex Yes you can use conditional formatting to change the cells format based on a condition such as A2 going over 7 months. Unfortunately I do not know which version of excel you are using and the way you do conditional formatting is different in each...

Excel: Conditional Formatting Pivot Table, pivot table, patient visits

pivot table, patient visits, new values: Before refreshing the table, copy/paste-value the number of patient visits to a different range. Either do this manually, or with a macro if you know how to use macros. Use Conditional Formatting to compare the new values to the saved value (i.e., 6) to create...

Excel: conditional formula, array formula, cell a2

array formula, cell a2, richard florida: Mike I can t tell from your formula exactly what you want the formula to do, but maybe this is it. =IF(AND(B2= x ,B3 0,C2= x ,C3 0),A2,0) all of the parameters must be met i.e. B2=x B3 0 C2=x C3 0 If all are true then the formula will bring...

Excel: copy paste from worksheet to many workbooks, description sheet, spath

description sheet, spath, hello tom: Kevin, Although I have never run into a problem with the DIR loop in that code, since the code is saving the workbook it may upset the DIR function. I can alter the code to get the list of filenames before it does anything with the files and that should...

Excel: Copy whole rows based on test word, vlookup, test word

vlookup, test word, person information: Fernando Can you insert a column? If so, I would insert a column to the left of the part number. Then use the formula =LEFT(part number,2) to fill the column with something that will work as a proxy for the salesperson. You could use that to filter, or...

Excel: Copying Formula in Excel, contiguous data, apple key

contiguous data, apple key, control key: Mike, First let me say that I don t have a MAC, but I will tell you what I would do in the Windows based version of Excel and assume the MAC offers the same features. The alternative would be to just reject your question, but I can only imagine that the...

Excel: Copying whole rows based on a test word, vlookup, test word

vlookup, test word, person information: I would do the following (1) create a new column which contains the first two characters of the P/n =left(CellWithPN,2) would be the formula I d then use a pivot table to get the data out - refreshing this would be a right click, then go to the...

Excel: Copying whole rows based on a test word, vlookup, test word

vlookup, test word, person information: I use macros for parsing data the way you ve described. On my site (see my profile), there are many example macros there for parsing data similar to the way you ve described. But your scenario is unique. I imagine you have a list in your workbook somewhere...

Excel: CountA Range, variable length, ogilvy

variable length, ogilvy, constraint: Alan, this would be the approach I would use since you haven t listed any constraint on data below row 1000 that you might not want to count. countAcol = WorksheetFunction.CountA(Range( a:a )) - worksheetfunction.CountA(Range( A1:A2 )) -- Regards,...

Excel: counting in dozens, Sixthsense Excel

Sixthsense Excel: Hi Rylan, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. https://docs.google.com/leaf?id=0B2TMxNyEPQ2rNDkwZDQ5ZjUtMjI0Ny00NzZhLWExOTctY2RkYjY2ZjA4ZmE2&hl=en_US Hope that helps....

Excel: Counting hours, finish time, decimal value

finish time, decimal value, ogilvy: Brin, time is stored as a decimal value between 0 and 1 representing the fraction of a full day. 8 AM is stored as 0.25 Noon is stored as 0.5 Mod does this calculation: =(B2-A2)-1*INT((B2-A2)/1) for the formula =Mod(B2-A2,1) more generally...

Excel: Creating a Macros, wbs, single file

wbs, single file, google: WORKBOOKS TO 1 SHEET STACKED Here s a macro for collecting data from all files in a specific folder. https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/merge-functions/consolidate-wbs-to-one-sheet The parts of the code...

Excel: DDE in Excel, dynamic data exchange, stock trades

dynamic data exchange, stock trades, vba module: Patrick, I do have such an account. So if you want to send a sample workbook I can take a look at it. I won t claim to be a DDE experts since that technology is part of windows and not Excel but I can see if there are any efficiencies that can be done...

Excel: Delete Duplicates Using 2 criteria, copy columns, rng

copy columns, rng, reference numbers: Charles, It was unexpected that you would run the first routine on data that did not require any action. I have modified the code to expect that as a possibility. It should now work as you wish. Sub RetainLatest() Dim rng As Range, rng1 As Range...

Excel: delete rows, joe joe, time error

joe joe, time error, missing something: Joe I am not sure why you are using a function at all. If you want to delete the entire row, if the value in column I is zero, why not go through column I and delete the rows with zeros? I don t see any benefit to putting a formula in column Q that is...

Excel: Dependent Data Validation, chevy cobalt, range target

chevy cobalt, range target, dodge neon: John, sorry, but I don t see anything in you code that makes any sense. The dropdown itself does not generate any type of event. You would need to trap the worksheet level change event for the cell that contains the dropdown. This would fire the Change...

Excel: Desire to streamline recorded Macros, pam pam, debugging tools

pam pam, debugging tools, macro recorder: Pam, The way I would do it is to make the immediate window visible in the VBE and then I would do ? typename(Rows( 1:1 )) and hit enter. The result would be Range so now you know that rows is an object and it is a range object (it isn t a...

Excel: Difficult count in Excel, raw score, raw scores

raw score, raw scores, sheffield uk: Mark The rule about selecting the higher of two spelling scores in case of a tie is going to make this pretty tricky with worksheet formulas but it might be possible. How is your data arranged? Do you have a table of raw scores (student down the side,...

Excel: Difficult count in excel, north herts, herts uk

north herts, herts uk, raw score: Greetings from Stevenage, North herts, UK! This is indeed tricky, but I think achievable - I would SUGGEST adding the assessment mark to the spelling mark divided by 100. This would THEN enable you to pick up the two highest marks - using the RANK function...

Excel: E-mail problem, mail problem, mail program

mail problem, mail program, case range: Mikko, have you tried doing it manually. Does it work when you do it manually. If so, you can try turning on the macro recorder and see what code it records for copying your range. Capturing everything as a picture and then attaching that to your...

Excel: emailing from outlook, HTML, email

HTML, email, range: Hello again Billy, You requested pasting the body as text, and this can easily be done, but I suspect that you would prefer having the range inserted as HTML as this will at least preserve the table-oriented alignment of Excel rows and columns, making the...

Excel: Emailing workbook w/hyperlinks, mail list, insert hyperlink

mail list, insert hyperlink, drive location: Adam, the drive letter is a mapping set up on each individual computer. In most companies, this is standardized - but I guess not in yours. In any event, the drive is mapped to a specific folder on the server. You need to consult with your IT people...

Excel: Enter time in Excel, target value, value application

target value, value application, excel spreadsheet: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If IsNumeric(Target.Value) Then If Target.Value 1 Then Exit Sub If Target.Value 100 Then n = Target.Value * 100 Else...

Excel: Excel 2007 - report macro filtering on dates, cell a1, isdate

cell a1, isdate, regional settings: Sue, in this command r.AutoFilter Field:=1, _ Criteria1:= = & dStartDate, _ Operator:=xlAnd, _ Criteria2:= = & dEndDate the 1 refers to the first column in your autofilter range. If your autofilter range starts with the left most column...

Excel: Excel 2010, autosum button, correct formulas

autosum button, correct formulas, richard florida: Britany Your cells that you are summing are most likely formatted as text, that would produce a 0. So you need to reformat the cells as numbers both the cells with the data in them and the cell with the autosum formula in it. You may have to re-enter...

Excel: excel, ss hope, format cells

ss hope, format cells, mm ss: Hi Jerry, You should be able to right click the value and choose format cells and choose Number/Custom. If you don t see what you are looking for, you could try to enter in the box below Type: m/d/yyyy h:mm:ss Hope that helps, -Crai...

Excel: Excel - Alarm on expire date, irow, c cells

irow, c cells, isdate: Hi Diego, There are several ways to do this, but here is a method that I favor. This example checks all the dates in column C to see if any are within the 180-day window. It does this check each time the workbook is opened. If a date is within 180 days...

Excel: Excel: comparing values in cells, blank cells, excel function

blank cells, excel function, cell values: Press alt/F11, then Insert/Module, then paste in the code below. Once that s done, enter something like =SeriesOK(A1:M1) and fill down. Function SeriesOK(rg As Range) As String Dim FoundOne As Boolean, n As Integer, Comp As Integer, comp2 As Integer...

Excel: Excel Conditional Formatting, format cells, excel sheet

format cells, excel sheet, k3: Hi Asim, This involves 3 conditional formats for K3. The actual conditional formatting steps are a bit different depending on which version of Excel you have, so I am going to assume you have 2007 or up. If an earlier version I hope this gives you the...

Excel: Excel formula, excel formula, therapy sessions

excel formula, therapy sessions, physical therapist: You re not out of luck, but Excel won t make decisions for you in these scenarios. Excel can be instructed HOW to make the calculations based on all the logic you define, nothing else. So, you ll have to step back from your one example (twice a week starting...

Excel: Excel formula, excel formula, direct email

excel formula, direct email, aidan: Question is not clear - it sounds like it s an IF statement but I don t know what the logic is if B = X then add D to WHAT else WHAT? =if(b2= x ,D2+ xxx,xxx) where 2 represents the row, and xxx indicates the logic steps I m missing to help you. ...

Excel: Excel Formula, excel formula, d21

excel formula, d21, a21: The syntax for an IF statement is =IF(condition,What to see if true, what to see if false) If you only put: =IF(D21= X , Paid ) then if D21 was NOT X, you d see FALSE so since you don t want to see anything, you put , which is the text string for...

Excel: Excel formula cell reference - moving, d32, cell references

d32, cell references, cell reference: In that case, I would typically use the SEARCH/REPLACE function for this. 1) Highlight the cells with J27 in the formulas you want to change 2) Press CTRL-H to open the REPLACE window 3) Search for J27, replace with F27, click on ADVANCED OPTIONS and...

Excel: excel function prb, cell b1, cell a1

cell b1, cell a1, validation purposes: Imran I am assuming that you are using a list to pick from for data validation purposes. As part of the list you can also add the identification numbers. in the second column next to the list. You can then use the vlookup function to match the identification...

Excel: excel function prb, cell b1, cell a1

cell b1, cell a1, vlookup formula: Hi imran, This can be done fairly easily as long as the names in A1 are unique. You can have another tab (or even another workbook) that must have a list of all names in column A and all corresponding id card numbers in column B. Let s call the tab MATCH....

Excel: excel function prb, cell b1, cell a1

cell b1, cell a1, data validation: I use VLOOKUP or INDEX/MATCH for this all the time. Assuming: 1) There is a table on Sheet2 with names in column A and the ID Card numbers in column B 2) The drop down is on Sheet1 in A1 ...then put this formula in B1: =IF(A1= , , VLOOKUP(A1,...

Excel: excel function prb, cell b1, cell a1

cell b1, cell a1, data validation: imran, assume in M1 to M6 you have this imran Micheal sameer shahrukh john Fernandez and next to them in column N, their ID card number then in B1 you would have this formula =if(A1= , ,vlookup(A1,$M$1:$M$6,2,False)) then drag fill...

Excel: excel function prb, cell b1, cell a1

cell b1, cell a1, data validation: You can use the VLOOKUP function to do this - assuming that the ID Card numbers exist in a table somewhere =if(isblank(a1), ,vlookup(a1,YourList,2,false)) Where YourList is a 2 column list with the names in the first column and the ID cardnumbers in...

Excel: excel function prb, Sixthsense Excel

Sixthsense Excel: Hi imran, Vlookup Or Index & Match Function will do the job. I have given below some of my sample file links for you, Click or Copy and paste the below link in web browser to download the Example files. Vlookup https://docs.google.com/leaf?id=0B2TMxNyEPQ2rY2QxNGY4ZDUtODEwOC00MzZhLWJkNTctZDkxYzY0M2VmYTli&sort=name&layout=list&pid=0B2TMxNyEPQ2rOGU0MjRkZTktZjhlMy00MTFkLTk0YWQtYzQ2MDMzZTRhYzhi&cindex=52...

Excel: excel numbering, dropdown arrow, ok step

dropdown arrow, ok step, home tab: Hi Wendy, Thank you for the opportunity to assist you with this project. Step 1. In Excel, on the Home Tab, click the dropdown arrow to the right of the number format and select Custom number Step 2. Enter the following exactly as it is: _1.1;2.1;3.1...

Excel: Excel pick last 10 numbers out of row, excel formula, ogilvy

excel formula, ogilvy, handicaps: Assume the values are in column A to AX. Formula written for row 2 =OFFSET($A$2,0,COUNT($A2:$AX2)-10,1,10) will pick out the last 10 numbers in the specified range you can get the 5 lowest with small(OFFSET($A$2,0,COUNT($A2:$AX2)-10,1,10),{1,2,3,4,5})...

Excel: excel problem, sumproduct, q11

sumproduct, q11, g11: Mike, =IF(AND(B5 ,D5 ),SUMPRODUCT(--(M!G11:G1002=B5),--(M!I11:I1002=D5),M!Q11:Q1002),0) either produces Zero or the results of the sum product. SUMPRODUCT(--(M!G11:G1002=B5),--(M!I11:I1002=D5),M!Q11:Q1002) should produce a value 0 ...

Excel: Excel vb to detect first and last entry in sequence, code column, text values

code column, text values, macros: Andy, see below ChangeValueinColumnBandSaveValue() Dim r As Range, lastrow As Long, i As Long lastrow = Cells(Rows.Count, B ).End(xlUp).Row Set r = Cells(2, B ) Set r = Union(r, Cells(lastrow, 2)) For i = lastrow - 1 To 3 Step -1 If Cells(i,...

Excel: Excel-vba, excel vba, column c

excel vba, column c, ami: You changed my working evaluation formula to something that doesn t work. MATCH works on a single column or row. If you need to search multiple columns, perhaps: Sub Example() If Evaluate( COUNTIF(AN:AS, AMI ) 0 ) Then _ Range( C7:C33...

Excel: Excel VBA - Vlookup for Array?, excel vba, column c

excel vba, column c, vlookup: Cris, here is a way to accumulate the values in column D at the same time you are populating the unique values in the array. That will show you how I find the values to increment an existing State and ID combination when it already exists in the array....

Excel: Excel workday formula, conditional formatting, excel spread sheet

conditional formatting, excel spread sheet, colums: Dave If you have to allow for the possibility of tasks taking more than a week, this is a pretty complicated formula; but, I think the following should work =A2+B2+INT(B2/7)*2+(WEEKDAY(A2)+MOD(B2,7)+INT(B2/7)*2-1=7)*2+(WEEKDAY(A2)+MOD(B2,7)+INT(B2/7)*2-7-1=1)*2+AND(WEEKDAY(A2+MOD(B2,7)+INT(B2/7)*2-1)...

Excel: Excell check boxes, check boxes, direct mail

check boxes, direct mail, false values: you haven t specified version of excel which does make a difference - in excel 2010 you would use the developer tab, then insert the checkbox - right click, select properties and set a cell link - checkboxes will return true/false values but you can turn these...

Excel: exporting, time cells, richard florida

time cells, richard florida, drop down list: JERRY This sounds as though it is just a matter of formatting. Try formatting the time cells using a format that includes the seconds and it should work for you. clic on the cells you want to format, then right clic and select format from the drop down...

Excel: Combining multiple rows with different data into one

Excel: Combining multiple rows with different data into one

Excel: Creating a Macros

Excel: Creating a Macros

Excel: Desire to streamline recorded Macros, john walkenbach, power programming

Excel: Desire to streamline recorded Macros, john walkenbach, power programming, vba

Excel: question regarding serial number drag cursor, suggestion, prob

Excel: question regarding serial number drag cursor, suggestion, prob, long time

Excel: spliting the cotents of a cell, 7 digits, column c

Excel: spliting the cotents of a cell, 7 digits, column c, curser

Excel: Undo Macro

Excel: Undo Macro

Excel: VBA Code to Restrict Entry, yogi, cheers

Excel: VBA Code to Restrict Entry, yogi, cheers, genius

Excel: Find exact match and count, comma separators, excel worksheet

comma separators, excel worksheet, set of numbers: Hi again Lilly, Okay, here is a solution. Rather than a macro approach I took the user-defined function (UDF) approach. I wrote a UDF named CountDupLists which you can use as a worksheet function and call if from the cell you want the count to appear...

Excel: Finding a list of values, plain vanilla text, compromise approach

plain vanilla text, compromise approach, question pool: Justin, You didn t ask me this question, but I found it in the question pool. Perhaps this will help. Ashish Mathur has documented how to do what you describe (in terms of getting multiple returns from a Vlookup type situation). You would need to create...

Excel: Formating Cell based on formula, department of the army, quick response

department of the army, quick response, commas: Say you want to apply this formatting to range F2:F10. Select F2:F10. Choose Home, Styles/Conditional Formatting, New Rule, Use a formula to determine which cells to format In the box labeled Format values where this formula is true: enter =m2-today()...

Excel: IF THEN formula?, sheet1, text numbers

sheet1, text numbers, g12: It sounds like you re saying Sheet2 is some sort of Reference Sheet, and the Column I is the column with the pertinent matching values. Is this correct? So, if you were to enter Cat in I2 on sheet1 and on Sheet2 there was a cat entry in cell I12,...

Excel: formula dependent drop down lists, circular function, vlookup

circular function, vlookup, drop down list: Hi Brad How are you? I do not think you can do this in Excel because you would have to use a circular function, i.e., if you pick up one category in E5 and would like to convert this into a code you vaveto put in E5 something like =Vlookup(E5, $A$1:$B$5,2,...

Excel: FORMULA-EXCEL 2010, sumproduct, false answers

sumproduct, false answers, blank cell: If you want to see HOW that formula is doing it s magic, click on the cell with that formula, then click on the Evaluate Formula icon on the Formula Auditing toolbar. It will let you step through the calculations one at a time and you ll seem them unfold....

Excel: Formula issue (Indirect data validation list), data validation, cell c5

data validation, cell c5, validation code: These are called Dependent Drop Down lists and they use named ranges and INDIRECT() references for Data Validation lists in the proceeding drop boxes. Here s a sample sheet I ve posted showing how it s done. The sheet CHOICES has the dependent drop lists...

Excel: IF formula using 2 different columns, countif, c test

countif, c test, column c: In Excel 2003, this is really going to be two formulas. =SUMIF(C:C, * & test & * , A:A) / COUNTIF(C:C, * & test & * ) In Excel 2007+ I believe there is a function called AVERAGEIF() and another AVERAGEIFS() that can do what you want in a single...

Excel: If Function for multiple conditions, Sixthsense Nestedif

Sixthsense Nestedif: Hi Dilshad, Try this =IF(ISNUMBER(--TRIM(LEFT(A1,SEARCH( DAY ,A1)-1))),IF(AND(--TRIM(LEFT(A1,SEARCH( DAY ,A1)-1)) =95,--TRIM(LEFT(A1,SEARCH( DAY ,A1)-1)) =150),10,IF(AND(--TRIM(LEFT(A1,SEARCH( DAY ,A1)-1)) =61,--TRIM(LEFT(A1,SEARCH( DAY ,A1)-1)) =90),15,IF(AND(--TRIM(LEFT(A1,SEARCH(...

Excel: To generate Sheets, cell a1, value move

cell a1, value move, cell values: Sub SheetCreation() Dim Curr As Worksheet Set Curr = ActiveSheet On Error Resume Next For i = 5 To Curr.Range( A65000 ).End(xlUp).Row Sheets( Form ).Copy after:=Sheets(Sheets.Count) With ActiveSheet .Range(...

Excel: graph on VB form, stephen bullen, way over my head

stephen bullen, way over my head, userforms: Uwazie I am not sure I understand what you want to do but you can find a utility called PastePicture in a zipped file on Stephen Bullen s website http://oaltd.co.uk/Excel/Default.htm It is way over my head, but I use it to put charts on userforms....

Excel: graph on Visual Basic form, Sixthsense

Sixthsense: Hi Uwazie, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas,...

Excel: Identifying Blank Cells with vba, blank cells, a63

blank cells, a63, h200: Brandon I can t tell exactly what you are doing. I suspect that you will be able to use the Resize function to delete the range; something along the line of if range( a63 ).value= then range( a63 ).resize(137,8).entirerow.delete where the resize...

Excel: Import, word application, looper

word application, looper, msword: The simplest method would be something like Dim AppWd As Object Dim looper looper = 5 On Error GoTo notloaded Set AppWd = GetObject(, Word.Application ) notloaded: If Err.Number = 429 Then Set AppWd = CreateObject(...

Excel: import date from csv file, csv file, input sheet

csv file, input sheet, daily basis: First, you re going to have to import all the data, then manipulate what you need from within Excel. However, I don t understand 60 latest dates from each month s last available date... -- how can there be 60 dates in a month?? are there duplicates? If...

Excel: increase row height while text wrap, Sixthsense Excel

Sixthsense Excel: Hi Rakesh, Press Cntrl+A+A which will select all the cells in the active sheet and take the mouse pointer to the excel default row numbers and do double click in the end of any row number which will adjust the row size depends upon the wrapped text length....

Excel: incrementing numbers, increment, cells

increment, cells: I m not clear what it is you want to increment here - are you making all cells in K have a comment from CQ? dim n as long for n=23 to 200 200 being the last row you want to update cells(11,n).addcomment cells(11,n).comment.visible=false cells(11,23).comment.text...

Excel: insert image based on cell information, Sixthsense

Sixthsense: Hi Joe, Thanks for sending your question my way and this can be done using VBA. But as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise...

Excel: insert picture to cell from source is in adject cell, vba code, column width

vba code, column width, a500: Joe, sub GetPictures() Dim r as Range, cell as Range, pic as variant Dim r1 as Range set r = Range( P1:P500 ) On Error resume next for each cell in r if len(trim(cell)) 0 then set r1 = cells(cell.row, A ) r1.Select Set pic = ActiveSheet.Pictures.Insert(cell.Value)...

Excel: Insert picture in excel 2007 worksheet and send it thru e-mail, mail question, e mail

mail question, e mail, multiple images: Peter, Do you mean when you bring the images into your worksheet, add some instructions to your code to resize the image. If that is the case, you can put in this added line. It scales the image to 75% of it size (change the .75 to what the scale factor...

Excel: Inventory Formulas, array formula, steel shapes

array formula, steel shapes, steel shape: Say the initial length is in A2 and your lengths are in A3:T3. in B5 enter =LARGE($A$3:$T$3,ROW(A1)) in C5 enter =$A$2-SUM($B$5:B5) Fill B5:C5 down to row 24. The lengths in column B are the sorted lengths from A3:T3 and column C is the cumulative lengths...

Excel: Macro, inverted commas, column c

inverted commas, column c, raw data: Sudhir, -- added information --- you can delete this line from the code: Range( N1 ).Resize(UBound(v, 1), 3) = v I just had that in there so I could check that the array was being properly sorted and forgot to take it out. --- End of added information...

Excel: Macro, alternate method, ogilvy

alternate method, ogilvy, line 6: Wayne, I assume your A:6 notation means cell A6 Range( A6:V & Range( Linecount ).Value + 5).Copy if you actually want to select that range before copying (which isn t necessary) then Range( A6:V & Range( Linecount ).Value + 5).Select Selection.Copy...

Excel: Macro to Delete all rows that do not include "", ogilvy, cells

ogilvy, cells: Steven, if the cells are actually empty, you can do this Sub DeleteRows() Dim SrchRng As Range Set SrchRng = ActiveSheet.Range( G1 , ActiveSheet.Range( G65536 ).End(xlUp)) On Error Resume Next SrchRng.SpecialCells(xlBlanks).EntireRow.Delete...

Excel: Macro for Weekly Dated Worksheets, tim tim, ogilvy

tim tim, ogilvy, spreadsheet: Tim, I can take a look at it, but your description so far has not been very illuminating. You need to tell me exactly what you want if you want me to try to write code. With no further explanation, my code would just copy the sheet Sub ABC() Activesheet.copy...

Excel: matching, command button, two rows

command button, two rows, step 1: Sub Deleteit() Application.ScreenUpdating = False Dim i As Integer Dim j As Integer For i = Sheets( wat ).Range( b65536 ).End(xlUp).Row to 2 Step -1 For j = Sheets( den ).Range( b65536 ).End(xlUp).Row to 2 step -1 If Sheets( den ).Cells(j,...

Excel: matching columns from a drop down list, e7, f5

e7, f5, ogilvy: Mike, in E7 of Sheet1 =if(And(C5 ,F5 ),Sumproduct(--(M!G11:G1002=C5),--(M!I11:I1002=F5),Q11:Q1002),0) this should give you the value you need. It assumes that there is only one row in sheet M where a match would be made to the selected...

Excel: Matching rows (records) in one spreadsheet to another then copy and paste certain cell values, cell values, column titles

cell values, column titles, number column: The Excel MATCH function finds the relative position of a value in a supplied array. The user can request either an exact match or can request the position of the closest match (above or below), if an exact match is not found. The syntax of the function...

Excel: Merging lines with macro fulfilling certain criteria, lr, step 1

lr, step 1, br: Assuming: 1) Row 1 is titles, data starts at row 2 2) You will make sure the data is already sorted by columns D, E and F ==================== Option Explicit Sub MergeRows() Dim LR As Long, Rw As Long Dim delRNG As Range LR = Range( A & Rows.Count).End(xlUp).Row...

Excel: Named Ranges in VBA, current date, vba

current date, vba, google: Hi Katie, There are various ways to address a named range in VBA. Syntax 1, using the names collection: MsgBox ActiveWorkbook.Names( TheName ).RefersToRange.Value Syntax 2, using the Range object: MsgBox ActiveWorkbook.Worksheets( Sheet1 ).Range(...

Excel: Need vba help, array array, abb group

array array, abb group, false selection: Avinash, This worked for me with you test data copied to several rows starting in row 1. Sub SplitData() Columns(1).TextToColumns Destination:=Range( A1 ), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,...

Excel: Nested IF statement, question pool, error value

question pool, error value, good answer: Michael, you didn t ask me this questions. I found it in the question pool. The missing information is what you want returned. =IF(K6=variables!L3,VLOOKUP(L6,variables!$J$5:$L$11,3),0)& - &IF(K6=variables!N3,VLOOKUP(L6,variables!$J$5:$L$11,5),0)&...

Excel: non contiguous areas., header row, irow

header row, irow, row header: Hi Samuel, If I understand you question correctly, this macro should do the job. I took the approach of doing the sums in VBA rather than writing formulas into the cells. But if you would prefer the latter feel free to follow up letting me know and I ll...

Excel: numbers into text, google search results, string place

google search results, string place, sum function: After you enter these functions in a VBA module, put something like this in the worksheet: =SpellNumber(a1) Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9)...

Excel: numbers into text, google search results, sum function

google search results, sum function, addin: imran, vba is pretty much the only solution. If you can t get your vba to work and it is written correctly, then there isn t much I can tell you. I will give you a link to Bob Phillip s site where he has links to various solutions for this - some...

Excel: numbers into text, google search results, cell a1

google search results, cell a1, macro code: You can add this capability to your spreadsheet with a User Defined Function (UDF), the one I ve used in the past successfully is called SPELLNUMBER(). http://www.ozgrid.com/VBA/ValueToWords.htm The macro code found on that page is installed into a normal...

Excel: numbers into text, google search results, sum function

google search results, sum function, google: It WILL need VBA - my own routine is set out below - to use this, open the VBA editor (Alt F11, use the Insert menu to insert a new module, then paste the code below - to return the text of the number, you can then use this function as any other - an example...

Excel: numbers into text, Sixthsense Excel

Sixthsense Excel: Hi Imran, Follow the below link and download the MoreFuction Addin and save it in your Local Drive. http://download.cnet.com/Morefunc/3000-2077_4-10423159.html Now run the setup. After doing that open excel and select Tools Add-Ins Check the...

Excel: Pivot Macro, cell c1, cell b1

cell c1, cell b1, cell a1: Dev, cnt = 0 For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables cnt = cnt + 1 pt.RefreshTable With pt.PivotFields( Per ) if cnt = 5 then .PivotItems(month1).Visible = True .PivotItems(month2).Visible...

Excel: Pop up message alert in excel, cell c2, status column

cell c2, status column, popup message: Say today s date is 7/29/2011. In cell A2, enter 6/29/2011 In C2, enter =IF(A2-TODAY()=30, Create PO , ) Format cell C2 with bold red font, or whatever way you want to hilight it. Cell C2 will display Create PO, because the date in A2 is...

Excel: Pressing Enter in Text Box that is in locked spreadsheet, couple paragraphs, shape properties

couple paragraphs, shape properties, microsoft excel: I ve re-read the question, and managed to find my machine with office 2010 on it, both of which help - I m not sure which textbox you used, but if you go to the developer tab you can insert an activeX textbox, which has many properties including multiline...

Excel: Pulling data from cells on one spreadsheet to another, paste values, 1234568

paste values, 1234568, 1234567: Candace, if the data in column E are numbers and the values in columns B, C and D combine to identify a unique row, you can do this using the Sumifs function in say column F of workbook1 put in a formula like this (I will call workbook 2 Book2.xls...

Excel: Pulling data from other workbooks, sheet names, correct column

sheet names, correct column, storage files: Katie, the message you show is not refering to the length of the link/formula. It is telling you that if the cell linked to in the closed workbook is more than 255 charcters, it will only return the first 255 characters. I can t say about the message...

Excel: Question, vlookup, change references

vlookup, change references, error number: Ammon, Assuming the table where you do the look up has both text and numbers, and anything that looks like a number is stored as a number, and the list you show is formatted as Text. I would do this A2: rtu B2: =if(iserror(A2*1),vlookup(A2,Sheet2!A:B,2,False),vlookup(A2*1,Sheet2!A:B,2,False))...

Excel: question regarding serial number drag cursor, serial number list, drag cursor

serial number list, drag cursor, excel sheet: vidya sharma, the ability to drag is an option setting. You didn t say what version of excel so it is done differently in excel 2007 and later and excel 2003 and earlier. In Excel 2003 and earlier, you go to tools= options in the menu and in the resulting...

Excel: Question on =SUM formula with Variables, cell b2, cell d2

cell b2, cell d2, sum formula: Rob The formula would look like this-- =IF(B2 80,+B2+C2,+A2+C2) The formula says If B2 is less than 80 then add together the cells B2 and C2 Otherwise add the cells A2 and C2 I assumed that cells in col A and B and ROW 2 were what you wanted...

Excel: Range Macro, copy worksheets, reading values

copy worksheets, reading values, numeric values: Wayne, Here is a guess at what you want I assume the cell named count means you have a defined name/named range named count that holds a number with the number of rows you want to copy. Further, I assume count is a workbook level name. Sub ABC()...

Excel: reconcile number, invoice amounts, invoice number

invoice amounts, invoice number, pc09: Hi David, I assume that you are referring the the winning submission by IOANNIS that won the MrExcel.com Challenge. The solution I submitted finds only one solution each time it is run, whereas IOANNIS solution finds all solutions. Of course, it the list...

Excel: Retrieving sheets name, mohammad ali, sh1

mohammad ali, sh1, macor: Mohammad Ali, Sub GetData() Dim sh as worksheet, sh1 as worksheet Dim cnt as long, r as range worksheets.Add After:=Worksheets(worksheets.count) set sh = worksheets(worksheets.count) cnt = 0 for each sh1 in worksheets if sh1.Name...

Excel: Selection Between two dates, countif, question thanks

countif, question thanks, c100: You shouldn t really work with workbooks with #REF! errors. But there is a workaround. Define a name, like Last to be =MATCH(TRUE,ISERROR($A$1:$A$100),0), and Data to be =OFFSET($A$1,0,0,IF(ISERROR(Last),100,Last),1) Then use =SUMPRODUCT(--(Data =1*...

Excel: very simple macro, subscript out of range, material name, subscripts

material name, subscripts, variable value: Kevin Your arrays are defined as 1 to 20, but you are trying to access components 5 through 24. You may be able to fix it by changing materialnames(a) to materialnames(a-5) to keep your subscripts in range. Alternatively, you could just make your arrays...

Excel: spliting the cotents of a cell, 7 digits, cell c

7 digits, cell c, numeric code: Hi Aisha, I am not sure how to accomplish this, but I would suggest a macro that selects the first 7 digits and places them into Cell B and the other 7 in Cell C. You must bear in mind that if the number is less or more than 14 digits, the situation changes....

Excel: If Then Statement then Lookup?, richard florida, k10

richard florida, k10, l6: Brian This formula will work for you. This formula is in cell A6 =IF(A$5= MC ,+K6)+IF(A$5= H ,+L6,0) If you copy the formula down thru cell A10 then the formula will change with relative referencing and will reference the appropriate cell in the table....

Excel: Subtract one column from another column, onecell, request column

onecell, request column, inventory values: For each cell in range B2:B5 on Sheet1, enter the existing value in that cell minus the value in the Column A cell to its left: Sub sample() Dim wks As Worksheet Dim secondCol As Range, oneCell As Range Set wks = ThisWorkbook.Sheets( Sheet1...

Excel: Can't get macro to run on a protected XL2003 Worksheet, p122, f8

p122, f8, culprit: Lonny If you the word stop in the code as shown below, your code will stop at that line. Then you click F8 to step through your code. When it comes to the line that is causing the problem, it will trigger the useless error message, but, we will know exactly...

Excel: Text formula, number selection, date selection

number selection, date selection, double quotes: Steve Davis, Revision: Sorry - My focus was on the flawed formula you provided. I know how to use double quotes in a string constant so sorry for not noticing your error with respect to that and pointing it out. you formula is incorrect. I have...

Excel: text to numbers calculation, value case, case statement

value case, case statement, cell address: Pete, Sub CountText() Dim cell As Range, v As Long, tot As Long tot = 0 For Each cell In Selection selection is A1:A10 Select Case cell.Value Case A v = 1 Case B v = 2 Case C v = 3 Case Else ...

Excel: on time macro, time macro, timevalue

time macro, timevalue, thakar: now+timevalue( 01:05:00 ) will run in 1 hout and 5 minutes from now, not 1 hour from now. If your routine to run the sql is based on the START, not the END of the sql, you shouldn t have that problem. For example: Sub SetUpTimer() Application.ontime...

Excel: total on each sheet, setup settings, printer driver

setup settings, printer driver, subtotal: Not really possible to know, since this could depend on the printer driver you have, the page setup settings (for example, if you set the page size to be 5 tall then the subtotal would have to appear in a totally different place than if it were at 7 tall,...

Excel: Typing sentences on excel, sorry ken, excel spreadsheet

sorry ken, excel spreadsheet, sentences: barry If you start typing the text will go in the cell you are typing in. It will spill over and cover the adjacent cells, but, it won t be in those cells. You can make the cell wider to accomodate more text. If you want text to actually be in the next...

Excel: Undo Macro, false selection, blank rows

false selection, blank rows, excel sheet: If you carefully position the button so that the top is clearly in a particular row, then you can assign all the buttons to run the same routine this way: Sub Whatever() TheRow = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row then work on...

Excel: Using Excel VBA to change workbook name automatically, excel vba, myfolder

excel vba, myfolder, dropbox: Eugene, Here is my guess at what you are trying. You are using the workbook_open event of workbookA to open workbookB. You are then using the workbook_open event of workbook B to do a saveas on workbookA. (this is the only way you can rename workbook...

Excel: Validation and lookups, higher school certificate, titulo de bachiller

higher school certificate, titulo de bachiller, year 10 certificate: VLOOKUP will only return one value, that s what it is designed for. An INDEX/MATCH can be forced into submission to return more than one result, but would require adding helper columns to your original source data sheet. If you want to send me the sheet...

Excel: Variable Rate Spreadsheet, input example, brick wall

input example, brick wall, direct email: Always tricky things I find - presumably a shift can extend across any 24 hour period, so could be day/night/saturday/sunday combinations? Could you give me the night rate END time as I would then have more data to play with - also, is it the same rate for...

Excel: VB Timestamp...two codes that won't work, autotime, sheet tab

autotime, sheet tab, column 8: ActiveCell.Column = 8 And ActiveCell.Column = 81 is impossible. The active cell s column can t both be 8 and 81! You need OR , not AND : If (AutoTime And ActiveCell = And (ActiveCell.Column = 8 Or ActiveCell.Column = 81) And ActiveCell.Row 1) ...

Excel: VBA Clearing Duplicate Entries, true case, fay

true case, fay, test data: Fay, this worked for me with your test data and a few other data configurations I added. Test it on a copy of your worksheet until you are satisfied it performs as you desire. Sub ABC() Dim rw As Long, r As Range rw = 2 Do Select Case True ...

Excel: VBA Code, de bruin, code question

de bruin, code question, automatic email: Abdul Jaleel, Hopefully this altered code will do what you want. Sub Send_Row_Or_Rows_Attachment_1() If MsgBox( Please make sure, you want to send the email to all the Customer? , vbYesNo) = vbNo Then Exit Sub Working in 2000-2010 Dim OutApp As...

Excel: VBA Code to Restrict Entry, target value, value case

target value, value case, row c: Yogi, Here is my second guess. You said nothing about column E, so that has been removed Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim r As Range On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(...

Excel: VBA timer, excel ontime, chip pearson

excel ontime, chip pearson, vba code: irish, take a look at Chip Pearson s page on this topic. I would guess that what is written there is the information you need (your description of your requirement is quite general) http://www.cpearson.com/excel/ontime.aspx -- Regards, Tom Ogilvy...

Excel: vba - using a function instead of a sub, excel sheet, cells

excel sheet, cells, good luck: Lea I don t think you are going to be able to get a function to populate multiple cells by clicking on a single cell. You could highlight the n cells then enter a function. How do you anticipate providing n to the code? If you just want the sub to do...

Excel: vlookup multiple values, abbreviated version, mathur

abbreviated version, mathur, office microsoft: Ammon, Ashish Mathur has written up this article on how to do this: http://office.microsoft.com/en-us/excel/HA012260381033.aspx?pid=CL100570551033 shows how to return multiple values for a single lookup value. This is a lot better than trying...

Excel: wages formula, exact formula, incorrect answer

exact formula, incorrect answer, richard florida: Frank Your formula should work fine, I don t see any real issues with it. You could use a few less parenthesis, which makes it a little easier to read. It could be stated as follows IF(A1 =5,(A1*26),((A1-5)*110%)*26)+(5*26) Yours is IF(A =5,(A*26),(((A-5)*110%)*(26)+(5*26)))...

Excel: Write an Excel macro, conversion rule, relative address

conversion rule, relative address, address value: Quang, I will assume that each cell will contain one of the 16 directions and that is all the cell will hold. I assume the first row and first column hold headers, so processing starts at B2. Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet, r As...

Excel: 2007 Application.FileSearch, documents and settings, attis

documents and settings, attis, spath: attis, You really can t use anything specific to the filesearch object. for example .foundfiles(lcount) is refering to the foundfiles attribute of the filesearch object so that can t be used. What I was suggesting was a way to get a list of files into...

Excel: i am able to turn off scroll lock on my laptop, fn key, dell laptop

fn key, dell laptop, ctrl keys: Find the colored Fn key, usually near the ALT or CTRL keys on the first row of your keyboard. Now find the key with a colored lock symbol on it on the top row. Press the Fn key, then tap the lock . Alternately, you will find a key on the top row...

Excel: Accumulation error, moderate difficulty, accumulator

moderate difficulty, accumulator, thanks in advance: Well, there are too many custom items in here for me to be able to easily (or even with moderate difficulty) simulate them, apparently named ranges or other variables. Then there are apparently all kinds of functions and parameters referred to, which of...

Excel: Adding a Filter and a sum, cell reference, second date

cell reference, second date, d6: T This is a job for the SUMPRODUCT formula. It will need to look something like =SUMPRODUCT(--(B2:B6 date1),--(C2:C6 date2),D2:D6) where the first date data is in B2:B6 and the first date is named Date1 (or just a cell reference) and the second date...

Excel: adding and subtracting from the same total, sumif, new question

sumif, new question, ogilvy: Sheila, =sumif($F$5:$F$1000, Sheila ,$C$5:$C$1000) that sums everyting in column C where column F does not contain Sheila. Does that do what you want? You can also use something like =Sum($C$5:$C$1000)-sumif($F$5:$F$1000, Sheila ,$C$5:$C$1000)...

Excel: Adding values using check box and VBA., check boxes, interior color

check boxes, interior color, g5: Hi Staszek, I am a bit uncertain of what you are trying to do. Could you email me a copy of your workbook with a few comments describing how you want it to work? My email address is VBAexpert@myway.com. Also, is there a reason in the code above why...

Excel: addition with a look up and a criteria, cell reference, column c

cell reference, column c, pvr: Anthony in general: =sumifs(CrntPVR,PCC, No ,Fund,942) assume PCC is in column B and crntPVR is in column C =sumifs(C:C,B:B, No ,A:A,943) Sumifs was introduced in Excel 2007 (so good for Excel 2007 or later versions). If you have an earlier...

Excel: adjust text wrap with row spacing, optimum height, default size

optimum height, default size, double click: Now either I don t understand the question, or yes, you do need to size each row individually. When you double-click the row separator, aren t you getting each row displayed at the optimum height? They re not all the same, so it s not clear to me what you...

Excel: Attendance Report, attendance cell, attendance report

attendance cell, attendance report, school attendance: Something like this Sub GetAbsentees() Dim n As Long, m As Long, outvar As Long, absent As Long Dim WrkSht As Worksheet Dim CurSht As Worksheet Set CurSht = ActiveSheet Set WrkSht = Sheets( Report ) For n = 8 To 10 outvar = 2 absent = absent +...

Excel: Auto Copy or Move, cell c2, auto copy

cell c2, auto copy, input cell: Then what? Will the next thing you do is enter another value in C2 on sheet2 and want that value to go to D3 on sheet1? What s the ongoing process here? IF you just want a formula link, you can put this formula in Sheet1 D2: =Sheet2!C2 ...and the value...

Excel: Auto Copy or Move, Sixthsense Excel

Sixthsense Excel: Hi Jason, Have a look in the below link http://www.404techsupport.com/2009/11/04/referencing-cells-in-excel/ Hope that helps! Feel free to post a follow-up, if your requirement is not fulfilled. Don t forget to rate the posts, since feedback is...

Excel: Auto date insert, blank cell, vba code

blank cell, vba code, question thanks: Add a test to the start of the formula like so: =IF(COUNT(B2:C2)=2, MIN(MAX(B2,TODAY()), C2), ) As long as calcs are set to automatic, your formulas should update. If you re using macros, perhaps you ve left ScreenUpdating=False somewhere,...

Excel: Auto population of data in MS Excel 2007, set of numbers, l7

set of numbers, l7, formulae: Vicky Hopefully I am understanding what you need correctly. I will give you a suggestion as a way to approach it. First --in one part of the worksheet I would create formulas that would be dependent on the value that you enter. That formula might...

Excel: autofill formula?, price values, correct terminology

price values, correct terminology, dataset: I m not sure I know what you mean. If you mean your dataset is an Excel Table/List that expands itself when you add new information in the expansion row at the bottom of the current list, then as long as the formula is already in the last row of the list,...

Excel: Autofilter base on cell value., target address, data validation

target address, data validation, endless pages: The code that Tom gave looked to me as though it would do the job you want but I would ensure the calendar control has no cell link - the code would then look like this Private Sub Calendar1_AfterUpdate() Range( C2 ).Value = Calendar1.Value End Sub ...

Excel: Automatic Workbook closing base on cell text,, target value, target address

target value, target address, history sheet: Hi Sarwar, Thanks for sending your question my way, but as I stated in my profile, I am not proficient in VBA and therefore can not answer your vba question. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas,...

Excel: averageif function while skipping blank cells, blank cells, array function

blank cells, array function, sumproduct: Doc There are lots of ways to do this. My preference is to use my favorite formula SUMPRODUCT. If your data is in A1:B8, with the dates (or blanks) in column A and the numbers to be averaged in column B, the following should work: =SUMPRODUCT(--(A1:A8...

Excel: calculate days with special circumstances, budget parameters, example image

budget parameters, example image, special circumstances: Grr... this one stumped me for hours. Finally got it. This will give every other Sunday off for all the Sundays counted in the range after the date put in G1. In E2: =C2 - B2 + 1 - IF($C2 $G$1, 0, INT(INT(($C2 - ($B2+MOD(1-WEEKDAY($B2),7)) + WEEKDAY($B2...

Excel: calculate retirement date in excel, Sixthsense Excel (Time)

Sixthsense Excel (Time): Hi Kingshuk Choudhur, Assume that your data is like the below. ----------- Instance-1 ----------- A1 Cell DOBirth A2 Cell 30-Nov-73 B1 Cell DOAppointment B2 Cell 19-Dec-96 C1 Cell DORetirement C2 Cell =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),DATE(YEAR(A2)+58,MONTH(B2)+1,0),...

Excel: How to calculate total hours in different days for different employees?, excel sheet, basic math

excel sheet, basic math, scenarios: Unfortunately there s not a good guess without exacting examples of your workbook layout. SUMPRODUCT() can be used in some scenarios, if you re already doing some basic math on the various shifts in each row you might be able to use some SUMIF() formulas....

Excel: How to calculate total hours in different days for different employees?, Sixthsense Excel

Sixthsense Excel: Hi sara, I recommend you to go for Sumif Function and have a look in the below links to have a better understanding about sumif. http://www.techonthenet.com/excel/formulas/sumif.php http://www.msoffice-tutorial-training.com/sumif-function.html http://www.excelbuddy.com/sumif-function-in-excel...

Excel: How to calculate total hours in different days for different employees?, direct email, excel sheet

direct email, excel sheet, pivot table: vlookup would return the first matching value, so probably NOT what you want - it s not clear what the data structure looks like, but possibly SUMIF would do it OR a pivot table If it helps, my direct email (which would allow attachments to be sent) is...

Excel: How to calculate total hours in different days for different employees?, sara sara, vlookup formula

sara sara, vlookup formula, excel sheet: Sara Its a bit hard to give you a specific formula without knowing what your data looks like. You should be able to use a vlookup formula in conjunction with other formulas, but if thats not working for you I will suggest this. First--on the tab which...

Excel: calculating overtime, spredsheet, reg time

spredsheet, reg time, h14: No, mine do not. 8 hrs max reg time is a standard where I live, so my apologies. I fact, where I live, if you work a lot of overtime in your early days of the week you would reach 40 hours total and be in 100% overtime after that, even if your total reg...

Excel: Cell Copy, new location, cells

new location, cells, formula bar: thomas Cells are pretty much always copied as a whole. However, you can copy parts of the contents from the formula bar (or edit in the cell if you use that option) but you will have to copy in parts, first the part before the x then the part after the...

Excel: changing currency settings, changing currency, currency settings

changing currency, currency settings, control panel 1: Follow these steps to change the Currency button s default county/region settings by resetting them in Control Panel: 1. From the Start menu, open Control Panel, and double-click Regional Language And Options if using Windows XP. If you re working with...

Excel: Close workbook or make read only the username is administrator, target value, target address

target value, target address, history sheet: Sarwar, If the user doesn t enable macros then any macro related solution you design either for protection or for controlling whether the workbook is open or for any other reason - they will not work. The only thing you can do it to design the workbook...

Excel: Comments Question, vba macro, dalgleish

vba macro, dalgleish, ogilvy: Sherri, you can copy or cut the cell it is associated with and paste that in the master sheet. A cut operation would move the comment. Is that what you are asking? If you mean with a formula - not with any built in formula. (you might be able to...

Excel: Commissions Calculation, dilshad, ogilvy

dilshad, ogilvy, c2: Dilshad, if C2 has the 65days entry (as an examle) and D2 has the money collected, then =IF(C2= , ,IF(VALUE(SUBSTITUTE(C2, days , )) =60,1.25%,IF(VALUE(SUBSTITUTE(C2, days , )) =90,1%,IF(VALUE(SUBSTITUTE(C2, days , )) =120,0.75%,IF(VALUE(SUBSTITUTE(C2,...

Excel: compare column in excell & formating., Sixthsense Excel

Sixthsense Excel: Hi Pramod, I assume that your 1st row consist the Column Headers and your data starts from 2nd row. Place the cursor in A2 cell and hold shift and extend your selection upto B6 (A2:B6) based on the data you have provided in your question. If your data...

Excel: Comparig the Cell values, q13, cell values

q13, cell values, exact formula: M. Sarwar, I tested the formula for every condition it is structured to handle =IF(F13= ,35,IF(Q13 25,45,IF(COUNTIF(Q14:Q39,Q13) 0, ,Q13))) if produced exactly the correct results every time. It NEVER EVER returned true or false. if you are...

Excel: Comparing several column values in a row, empty cells, cell values

empty cells, cell values, excel spreadsheet: QUESTION: Mr. Ogilvy, The sub Main() works fine but my issue is more with the VBA code logic. The function that I provided is not working correctly. I need help with how to code the logic because it s not catching everything. In testing the code...the...

Excel: Complex Function string - need help to remove error value wthin arguments( IF ISNA INDEX MATCH and SUM), Sixthsense Excel

Sixthsense Excel: Hi Jon, ********************************************************************************************** Take your mouse and select from Hi Jon to my signature sixthsense and do copy and by pressing Cntrl+C and open a winword and paste it you will get...

Excel: concatenate text with cell values, paste values, cell values

paste values, cell values, correct interpretation: Gail, that isn t too precise a description but I will assume you have A1: 1 A2: 2 A3: 3 A4: 4 A5: 5 and you want to end up with 1 OR 2 OR 3 OR 4 OR 5 in as single cell as your result. so in B1 put in the formula =A1 in B2...

Excel: Conditional Formatting, conditional formatting, bottom choice

conditional formatting, bottom choice, question pool: Julian, You didn t ask me this question - I found it in the question pool First - I don t use a macintosh and most on this forum don t - but it should be similar to the windows version and this is how I would do it in the windows version. Say your...