table b2, pivottable, excel formula: Joseph, The best way to handle such data is using a PivotTable. To use this do the following: 1. Click somewhere on your raw data 2. Select Data 3. Select PivotTable and PivotChart Report... This will bring up the PivotTable and PivotChart...

Excel: Formula, cell a1, f1

cell a1, f1: Chris: Suppose your first price is in cell F1 and it shows 100.00. Place this formula in cell A1 (or any cell really): =F1*(1-0.45)* 0.18+(64.9*2.4) The result of the above formula is 165.66. From that point forward you can copy the formula down...

Excel: Formula for date, excel spreadsheet, d note

excel spreadsheet, d note: =MIN(30,DATEDIF(G6,TODAY(), d )) will stop at 30. Select the cell, use format/coditional formatting, change cell value is to formula is , and assuming the cell is G7, enter this: =G7+7 today() then click Format, click patterns tab, select the color...

Excel: "IF" Formula in Excel, free microsoft excel, microsoft office xp professional

free microsoft excel, microsoft office xp professional, microsoft office xp: You ve just got your quotes in the wrong places. It should be: =IF(B10= Small ,29.89,… You want the word Small not a cell named Small. Likewise, your dollar amount should not be text. You might want to consider using VLOOKUP for this instead: ...

Excel: Formula Problems, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can do this with a couple of string functions. Let s say: A1= 1234xxxx5678 And you want to get rid of that xxxx in there. B1= xxxx C1=FIND(B1,A1) D1==REPLACE(A1,C1,LEN(B1), ) This will FIND where xxxx appears in A1 and then replace...

Excel: Formula problem, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: This is just going to be a compex, nested IF function. =IF(A1 =100,A1*3.25/23,IF(A1 =200,A1*2.75/23,IF(A1 =300,A1*2.10/23,X))) X represents everything else (what value to use if the miles are over 300). You can use the IF function to have Excel place...

Excel: Formulas as text, cell reference, cell a1

cell reference, cell a1, a3 b3: Mickey, Indirect can built a cell reference, but it can t build a more complex expression. So you can dynamically build a formula string and then evaluate it - at least not with just the built in functions. In your case, you can design the formula...

Excel: auto increment by 1, d3, tiny bit

Excel: auto increment by 1, d3, tiny bit, invoices

Excel: Document not saved

Excel: Document not saved

Excel: Excel VBA: Copy and Paste 2

Excel: Excel VBA: Copy and Paste 2

Excel: Excel - vlookups, vlookup

Excel: Excel - vlookups, vlookup

Excel: Finding and Deleting empty spaces before and after characters in Excel, cells, hidden characters

Excel: Finding and Deleting empty spaces before and after characters in Excel, cells, hidden characters

Excel: Student Address Excel Database

Excel: Student Address Excel Database

Excel: Text to speech in Excel, boumediene, excel 5

Excel: Text to speech in Excel, boumediene, excel 5, text to speech

Excel: Travelling allowance, travels

Excel: Travelling allowance, travels

Excel: forms / databases, free microsoft excel, microsoft access database

free microsoft excel, microsoft access database, question sign: I honestly would not do this as an Excel form. I would set up a Microsoft Access database or a Web-based HTML form that stores the data in a table. Doing this in Excel would be like using a hammer when you should have a screwdriver. Let me know if you have...

Excel: formula, ribbon bar, cell reference

ribbon bar, cell reference, integer value: Make X another cell reference in the spreadsheet and then use Goal Seek to solve it - in this instance, I m using A1 as my test which has this formula =IF((E1-(A4-3%)*1.25%*31/365) =I40,1,0) goal seek tries to set the value of A1 to 1 by changing E1...

Excel: formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: If your value is in A1 you should be able to say: =(((A1*.45)*.18)*2.4) Or thereabouts. It s just simple math. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: formula, string manipulation functions, free microsoft excel

string manipulation functions, free microsoft excel, string concatenation: It adds S to the right X characters of whatever s in B7 where X is the length of B7 - 2 characters. I cover the various string manipulation functions in my EXCEL 232 tutorial: http://599cd.com/site/courselist/excel/excel232?key=AllExperts In this...

Excel: IF formulas, ot hours, project numbers

ot hours, project numbers, sumif: Tracey, Yes, you have to type in the unique list of project numbers in B53:B75. Another way is to add a blank sheet and copy the two sets of project numbers all into a single column such as column A on the blank sheet. In A1 put in the word Numbers and...

Excel: formulas/copying sheets, richard florida, a10

richard florida, a10, florida usa: Jeff Obvious answer and I am guessing one that you have thought about would be to copy the entire workbooks/worksheets that are involved--since everything is the same. If that won t work then-- I believe if you copy your formulas to the new worksheets...

Excel: If formulas for reminders., follow ups, column c

follow ups, column c, richard florida: Ram I am a bit confused as to whether you have three columns for the reminders or two because in your question you mention three reminders but you need your formula in column E with A being the first column used and with D being the second reminder, which...

Excel: frequency table, array formula, vertical array

array formula, vertical array, array formulas: This answer comes from the help file Show All FREQUENCY See Also Calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall...

Excel: now() function result, template sheet, macro code

template sheet, macro code, wasting your time: What footer code are you referring to? Is there a macro (code) being run? To prevent the date from updating you will have to type a hadrd date in the footer or write a macro that puts today s date as a hard date and not a dynamic date, which is what Now()...

Excel: Generate automatic Invoice no and P.O, invoice format, invoice numbers

invoice format, invoice numbers, ini file: I m not clear what you want to happen here - there is no automatic process to update a sheet name, but VBA could be used to create a sheet with a name based on the current name - however, if the sheets are all in the one workbook, then it s easy enough to...

Excel: Generating an Array in with a for-each loop in VBA, irow, new element

irow, new element, shooting from the hip: try SET depArray(depArCount) = rngDepth since the DepArray function is a range. And, depArray(depArCount) must be a range or this will still not work. And finally, yes, you can only preserve a one dimensional array - I missed the fact that you were...

Excel: Generating an Array in VBA, irow, array

irow, array, cells: A much easier way to accomplish this is by using built-in Excel functionality: filter the table of cells on the proper date. Then copy the filtered table to another location. Like this: Sub SortNumbers() Dim oResultRange As Range Set oResultRange...

Excel: Grabbing values from multiple workbooks, index function, sheet1

index function, sheet1, shared server: easiest way is simply =[filename1.xls]sheet1!B10 +[filename2.xls]sheet1!B10 etc simply open all the files in excel then after typing = use the window to switch between the workbooks and click on the cell you want, then type the + and continue until...

Excel: Help, code index, upc code

code index, upc code, unistrut: Steve: The formula you have chosen is one of the best choices to use. You ve done the hard work already - now it is just a matter of copying the formula to all of the existing cells. I m not 100% certain, but it sounds like the problem is you don t...

Excel: Hide rows without a particular word, hideme, word question

hideme, word question, header row: If you could state that a full word was followed or preceeded by a space, they you could search for price or price . Alternatively, you could search for the variants (if they are known) and IF found reset the boolean value - hope this makes sense...

Excel: Highlight Duplicates, conditional formatting, yuo

conditional formatting, yuo, drop down list: There s no way to do it for the non-contiguous ranges, but yuo may be able to get away with this. Select ALL the cells in the ranges you listed, and use this formula in the conditional formatting (assuming B2 is the ACTIVE cell): =COUNTIF($B$2:$T$15,B2) 1...

Excel: Horizontal Sorting and counting, vba code, combinations

vba code, combinations, cells: Hi Norman, Not sure how the columns need to be sorted, in order? or each column by its own? This does the former: Sub SortNumbers() Dim oSortRange As Range For Each oSortRange In ActiveCell.CurrentRegion.rows oSortRange.Sort oSortRange.Cells(1,...

Excel: help, code index, upc code

code index, upc code, unistrut: First, select the cells you want to apply the formula to (i.e., select cell E1, then hold down the shift key as you extend the selection to E1:E10000). Then type: =VLOOKUP(B1,I:L,4,0) but after you type it, instead of hitting the Enter key, hit Ctrl-Enter....

Excel: hourly/daily averages, creating pivot tables, minute intervals

creating pivot tables, minute intervals, pivot table: Easiest way would be to create a new column that shows the hour and a new column for the day =hour(a2) would give the hour of A2 and =int(a2) would give the date only with no time. Then use the data menu to create a pivot table and get that to...

Excel: Importing CSV files into excel, csv files, csv file

csv files, csv file, code excel: Change: Copia los colores ActiveSheet.UsedRange.Copy ThisWorkbook.Worksheets( Hoja2 ).Cells(UltimaFila, 1) to Copia los colores Set NewWorksheet = Thisworkbook.Worksheets.Add ActiveSheet.UsedRange.Copy NewWorksheet.Range( A1 ) NewWorksheet.Rows(1).Font.Bold...

Excel: Importing data point (cell) from one excel file to another, sheet tab, cell reference

sheet tab, cell reference, documents and settings: If they re open, type = , then click on the cell you want to get the value from. If they re not open, you need to include the entire path to the workbook, the workbook name, the sheet/tab name and the cell reference, like = D:Documents and Settings umlasMy...

Excel: Improving on Do Loop macro efficiency, loop macro, c7

loop macro, c7, aidan: I would probably do this without a macro - you create the subtotals, then use data, filter, autofilter, custom to select those lines that contain total - select and copy the result and paste to a blank worksheet - which gives you the data you need to work...

Excel: Inconsistent formula, macth, d15

macth, d15, snap shot: Ravi, I noticed one problem with the formulas you provided. The example you give for the equation in D21 is using for input the value in a different row! (A18) Though this value AB is present, isn t it confusing to look for input values...

Excel: Inserting Comma's into values, string manipulation functions, free microsoft excel

string manipulation functions, free microsoft excel, state abbreviations: The big question is this: are all the states EXACTLY 2 characters at the end of the cell? If so, you could get away with this to get the CITY: B1 =LEFT(A1,LEN(A1)-3) The state would just be: C1 =RIGHT(A1,2) Now put them together D1 ...

Excel: Interest rates, cell c1, cell b2

cell c1, cell b2, maturity value: In cell A2 enter =A1+3 In cell B1 enter 100000 Format cell C1 as Percentage with 2 decimal places. In cell C1 enter 4.00% In cell B2, enter =B1*(1+$C$1/4) Copy A2:B2 down through A2:B8. From Excel menu, choose Tools, Goal Seek, Set...

Excel: ISBLANK formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: How about: =IF(ISNUMBER(S4),S4,IF(ISNUMBER(R4),R4, closed )) You just need to nest your IF statements. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: isblank, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: The NOW() function is going to continuously update that cell to the current time. You would need a macro to run when your cell is updated to manually set that cell equal to the current time. Let me know if you have any other questions or comments. Also,...

Excel: if iserror, srow, instr

srow, instr, formula 1: You don t, though you can, use the ISERROR function in code. Instead, just to check to see if the cell has valid data, something like If IsNumeric(Range(scol & 168 )) And Trim(Range(scol & 168 )) then do the stuff you need to do because Range(scol...

Excel: isna and vlookup, isna, null string

isna, null string, e9: Dan, If no match is found, rather than returning N/A, it returns a null string, i.e. the cell with teh formula appears blank. Otherwise it does what you describe. =IF(ISNA(VLOOKUP(A9,Sheet2!$A$2:$G$97,7,FALSE)), ,if(VLOOKUP(A9,Sheet2!$A$2:$G$97,7,FALSE)=0,E9*C9,VLOOKUP(A9,Sheet2!$A$2:$G$97,7,FALSE)))...

Excel: Linkage issues, financial models, source file

financial models, source file, microsoft excel: Tristan One of the things I use is to turn off the automatic calculation and use the manual calc (F9). That way nothing gets updated until you want it to. (toolbar/tools/options/check the manual calc box) Also review the following it is from the excel...

Excel: Linked Cells, proper sort, cell reference

proper sort, cell reference, sort function: Hi Kath, In the current structure you are correct: no sorting allowed. Without seeing your sheet it is hard to advise what to do next, but I expect a redesign of your setup is needed. I strongly suspect what you are doing here would best be done using...

Excel: Linking excel files to be used in a Macro, excel spreadsheet file, vba code

excel spreadsheet file, vba code, directory folder: I m not sure I m clear what you want the code to do - it sounds as though it would need to open and reference one or more books, whilst retaining a reference to the active one - so something like dim CurBook as workbook set CurBook=activeworkbook you...

Excel: Linking excel formulas to macro buttons, array formula, multidimensional array

array formula, multidimensional array, dimensional array: Nick, on a blank sheet, in A1 put in this formula =CHAR(COLUMN()+64)&ROW() Drag fill it to the right for a couple of columns and then drag the first row down for 10 or so rows. This should put the cell names in each cell. Now go to cell M22...

Excel: Linking multiple excel sheets and retaining the values, rajaram, richard florida

rajaram, richard florida, copy paste: Rajaram I believe the easiest way for you to handle this is to use a copy/paste special/values in the child sheet, thereby eliminating the parent changing values due to links. You can use another sheet to copy to or another part of the same child sheet...

Excel: Linking worksheets, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You could create a HYPERLINK from one cell to another. That s about all I can think of without programming. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO...

Excel: Look up functions, microsoft site, mathur

microsoft site, mathur, office microsoft: Danielle, Yes, there is no difference with respect to this. If you want to send me a sample workbook and tell me exactly what you want and where you want it, I can show you how to do it. twogilvy@msn.com Otherwise, I would just be repeating...

Excel: Lookup the closest match, vlookup formula, different time

vlookup formula, different time, occuring: The problem is that IF you are using vlookup it will look in the FIRST column for a match and then return values from that or a subsequent column - so table B needs to have the time entered AFTER the entry number - which you can do by adding an =a2 or equivalent...

Excel: linking references, cell c2, cell references

cell c2, cell references, window menu: Cannot be totally specific as I don t know the cell references involved, and indeed I m not sure what you wanted to do with the link, but any formula can reference any cell in any workbook - if the worksheets are in different workbooks, have them both open,...

Excel: as listed, format cells, damons

format cells, damons, date stamp: Dave, Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect([G2:G12], Target) Is Nothing Then Application.EnableEvents = False Cells(Target.Row, 6) = Now Application.EnableEvents = True End If End Sub -- Regards,...

Excel: lookup, core units, isna

core units, isna, ogilvy: ermias, In xl2003 and earlier: =if(isna(VLOOKUP(A3, P:Core UnitsCost Centres[ES3011.xls]ES3011 !$A:$G,3,FALSE)),0,VLOOKUP(A3, P:Core UnitsCost Centres[ES3011.xls]ES3011 !$A:$G,3,FALSE)) If you have Excel 2007, you can use the new IFERROR function...

Excel: lots of ifs, value entries, brainbench

value entries, brainbench, doug smith: Hi Hilde, The easiest solution would be to select all the date cells and use the Replace command (Edit menu) to replace all the #VALUE! entries with nothing. Then your formula should work as-is. Otherwise, the formula would need to look something like...

Excel: Macro, blank cell, loop condition

blank cell, loop condition, open range: It s easier to put data at the bottom of a worksheet, but it s not impossible to do it the other way round - I d tend to put at the bottom and then sort the data. To put the data in a given location, use Selection.Insert Shift:=xlDown instead of paste...

Excel: Macro to Correct Data Entry Errors, minute values, summary activity

minute values, summary activity, iloc: Stan, This worked for me. It works on column B. Text it on a copy of your worksheet It assumes that column B is formatted as hh:mm It assumes that row 1 has headers, so it starts processing in B2 of the active sheet. Sub AdjustTime() Dim...

Excel: Macro to copy rows, datar, c statement

datar, c statement, rerun: Politely, this is why I don t write macros for people who need one, as my intro says, because it can be an almost never ending stream of changes and modifications. I made an exception in your case because this was as simple and short macro. How easy would...

Excel: Macro efficiency, r1 value, sheets field

r1 value, sheets field, dim button: Pete, Private Sub CommandButton1_Click() Dim Button As Control Dim r as Range, r1 as Range, r2 as Range Dim res as Variant set a reference to the list of names in Playlist with Worksheets( Playlist ) set r = .Range(.cells(1, A ),.Cells(1,...

Excel: IF THEN Macro Help, c cells, b cells

c cells, b cells, scount: Your ENTIRE macro can be: Sub Vendor() Dim DestSheet As Worksheet Dim sRow As Long row index on source worksheet Dim dRow As Long row index on destination worksheet For sRow = 1 To Range( G65536 ).End(xlUp).Row If Cells(sRow,...

Excel: Macro Help, cell b1, column c

cell b1, column c, richard florida: JOHN I think we can do what you want with an IFTHEN formula, something like this-- = IF(B1= A,B,C , X , ) The formula says If the cell B1 has the text A,B,C in it, then put an X in the formula cell. If it doesn t then put a blank in the formula...

Excel: Macro Help, size column, john doe

size column, john doe, johndoe: Why not just use a formula, like this in C1: =IF(B1= John , Doe , ) and fill down? But if you insist on a macro: Sub JohnDoe() for i=1 to range( B65536 ).end(xlup).Row if cells(i,2).value= John then cells(i,3).value= Doe Next End Su...

Excel: Macro to run through autofilter options & print each, pivot table, pivot tables

pivot table, pivot tables, table toolbar: Sure you can, but before I start cooking code, let me point you to a handy option Pivot tables have. If you add the corresponding field of the table as a page filter for the pivot table, then you can use the Show pages option from the Pivot table dropdown...

Excel: Code that displays the Date at which a cell/cells Changed., vlookup function, code string

vlookup function, code string, char c: Sujan, Based on what you have said, I would do this: Private Sub Worksheet_Change(ByVal Target As Range) if more than one cell is triggering, then exit sub If Target.Count 1 Then Exit Sub check if triggering cell is empty If Len(Trim(Target))...

Excel: Macros and pivot tables, excel, vba

excel, vba, pivot tables: You can use the worksheet events for this. This code can give you some ideas: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $A$1 Then Sheets( Sheet1 ).PivotTables( PivotTable ).PivotFields( Country ).CurrentPage...

Excel: Macros and pivot tables, pivot table, pivot tables

pivot table, pivot tables, accurate answer: James Sorry I don t fully understand the what you need, and if I did I might not be a good one to answer because I seldom use pivot tables therefore I am defiantely not an expert in them. To get a quicker and probably more accurate answer I suggest you...

Excel: Mandatory fields, column ranges, registration dept, patient tracker

registration dept, patient tracker, vba help: I can probably help, though I m not sure I ve got enough to go on to be specific at the moment - however, my suggestion would be to use a USER FORM to get the data - this would decide which stage we were at in the process and present the questions. Only a...

Excel: If and Match Function Combination, store numbers, bb3

store numbers, bb3, aidan: The formula looks fine, so SHOULD be returning the right store, though it would always return the first entry if there are multiple entries - I m not sure that the lookup formula is right though as it would look in column A of the WM Store file and return...

Excel: Merging columns, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Well, you could convert the date to TEXT first: =TEXT(A1, mm/dd/yy ) Excel stores DATES internally as a long integer. That s why it s showing up as 40167. Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: MODE for text values, array formula, vba solutions

array formula, vba solutions, text values: I ve found some VBA solutions online, but you can do it with an array formula - I assume your list is in cells - so I ll assume A1:A8 =INDEX(A1:A8,MAX(IF(COUNTIF($A$1:$A$8,A1:A8)=LARGE(COUNTIF($A$1:$A$8,A1:A8),1),ROW(A1:A8),0)),1) This needs to be entered...

Excel: Mouse over pop-up, pointless message, mouseover event

pointless message, mouseover event, mousemove event: There isn t a mouseover event for the cell, but you can get one to work by inserting a control object - eg a label - this has the MouseMove event which you can program - you would need a control in each cell that you are interested in - the code is along the...

Excel: Moving data using a check box, run time error, sheet1

run time error, sheet1, design mode: Sabrina, Assume you can use column A to check for the next blank row. You can use set r = Worksheets( Sheet2 ).Cells(rows.count, A ).End(xlup).Offset(1,0) now r refers to column A of the next blank row. This assumes you have header data in row1...

Excel: MS Excel 2007, visual basic for applications, familiarity

visual basic for applications, familiarity, visual basic editor: Customizing Excel like this requires the use of Visual Basic for Applications coding (vba, sometimes called Macros ). So you first need some basic familiarity with vba. Once you have that, go to the Visual Basic Editor. In the workbook object, you can...

Excel: Multiple Criteria, product conversions, array formula

product conversions, array formula, logical tests: not sure your summary helped, but I assume you are summing if multiple conditions apply - at which point, you can do it without an array formula but using a trick of sumproduct which allows you to turn true/false values into 1 s and 0 s =sumproduct(--(left(I25:I101,8)=...

Excel: Multiple IF Formulas, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: =IF(A1 =.25,0,IF(A1 =.75,2,IF(A1 =1,1,-1)))) Try that. A value outside your range will give you -1. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: macro, tool users, filing system

tool users, filing system, aidan: Not sure I follow the question - but if each file equates to a sheet in the workbook then the macro can simply copy these to a new book (record this action by right clicking on a sheet and selecting move or copy, create a copy destination to new book). Can...

Excel: merging the columns of a matrix into a single long vector, basic principle, excel spreadsheet

basic principle, excel spreadsheet, acceptable solution: Ah, OK, sorry for the misunderstanding - obviously you can do it manually by copy and paste, but that s not ideal (although I have to admit for one off jobs it s something I do do as it s quicker than some alternatives). The BEST solution would be to have...

Excel: modfied sorting, sort columns, persian words

sort columns, persian words, sort command: Dave, My first suggestion is to give custom lists another try. Create a list with the first character followed by the wildcard *. I can t say for sure if this will work or not, but it is worth a try. Another thought is to create a set...

Excel: Need ISTEXT Formula Assistance, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Use the =ISNA() function to determine whether or not the cell is #N/A or not. It’s not text, it s a special code. =IF(ISNA(AG5), LSFV Null , LSFV 3yrs ) Let me know if you have any other questions or comments. Also, be sure to visit my web site...

Excel: Negative time and conditional formatting, conditional formatting, negative time

conditional formatting, negative time, diffence: I m not clear what you want to do here as there are (it seems) two contradictory statements - you want the text to be red, but you don t want to see the text? Format, conditional formatting is the basic setting you need and it should be easy enough to achieve...

Excel: Nested IF Statements, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You can still use VLOOKUP. There is a fourth optional parameter that does an EXACT lookup. Say: =VLOOKUP(Cell,Range,Column,FALSE) Now VLOOKUP will work, and you won t have a monstrously large IF statement. You can use the VLOOKUP or HLOOKUP function....

Excel: Numbers w/o .0, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Just select your data and click on the DECREASE DECIMAL PLACES button on the toolbar. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Opening Files via macro's, excel, open file

excel, open file, vbs: Yes, it is possible. As this is not a workbook, but a .tif file, my recommendation would be to place an hyperlink on the cell, and then following it with code, something like: Range( A1 ).Hyperlinks(1).Follow If it were Excel workbooks, you could use the...

Excel: Option buttons, optionbutton, control toolbox

optionbutton, control toolbox, option buttons: Gracie, The option buttons from the forms toolbar (which use a groupbox) operate the way you describe. OptionButtons from the control toolbox toolbar should each be assigned to its own cell. then if you group them using the groupname property, the...

Excel: Option buttons, control toolbox, option buttons

control toolbox, option buttons, click properties: Check the properties for each (Right click the control and then left click Properties). When the Groupname property is the same name for each then they will be dependent on each other, which means when you click one the other gets unclicked. Does that help...

Excel: How do I obtain the average of letter grades?, letter grades, numerical value

letter grades, numerical value, suggestion: Not sure you can do this in the way you are trying without making a real complicated formula. As an alternate suggestion, create a small lookup table and assign values to each grade. Then use VLOOKUP, or MATCH, to get the numerical value for each grade...

Excel: Personal workbook, personal workbook, xls

personal workbook, xls: It shouldn t. It refers, of course, just to personal.xls -- are you certain it s not referring to another workbook? Have you turned events off? I have the same code in my personal.xls and it doesn t ask, so it s hard to know what s happening in your case...

Excel: Pivot Table, general ledger account, gl account

general ledger account, gl account, business credit card: Martin, It would be easier to spit it up manually and create an equation in the allocated total column to ad it up, than to enter the number in the total column and have equations detect how to split it up. Its unclear but it seems that a given...

Excel: PO spreadsteet, free microsoft excel, microsoft office online

free microsoft excel, microsoft office online, question sign: Have you looked in the Microsoft Office Online templates? Perhaps someone else has made one. If not, create your own. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Populating cells, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use an IF function in Sheet2!B1 =IF(A1=Sheet1!A1,Sheet1!B1, ) You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more information...

Excel: Preventing data entry errors in Excel, target value, data validation

target value, data validation, sheet tab: Vickie, You could do the first part with data validation if the letters are listed say in the first row in their respective columns. If you want to do the second part, you would have to write a macro using the change event. If you are going to...

Excel: Print options, continuous area, landscape

continuous area, landscape, aim: Not clear what you mean by virtual pages, but IF excel views two items as being two pages, it will print to two pages - you therefore need to find a way to get excel to view this as being one page. assuming your two virtual pages are (for example) two worksheets,...

Excel: Prob in Vlookup, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Make sure your list is sorted ascending for VLOOKUP to work - if not you have to use EXACT MATCH. You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet...

Excel: Problem with end.(xlup)?, validation routines, details button

validation routines, details button, marketing information: Lee, In the Previous Customer sheet, you do an end(xlup) and then add a row to the end of the list. I suspect at that time, the active cell is the new row. In the Marketing List sheet, you do an end(xlup) and start writing over the last customer. ...

Excel: Profit And Loss Graph, xy scatter chart, negative colors

xy scatter chart, negative colors, profit back: Dom: This is not something that can be done with Excel using the standard chart options. I have listed two websites that offer Visual Basic alternatives; however, they don t really exactly work perfectly (I ll let you give them a shot and see if they are...

Excel: Progressive Invoice Numbers / protection, excel invoice template, invoice numbers

excel invoice template, invoice numbers, invoice number: you probably know that a worksheet can be hidden or unhidden by using the menu choices - within VBA there is a setting called VeryHidden which makes it impossible to see the sheet to unhide it - so you could set up your sheet such that ONLY if the macros are...

Excel: Pulling Report?, free microsoft excel, ms access database

free microsoft excel, ms access database, question sign: This really would be better accomplished with an MS Access database, but you could do this with VLOOKUP on a second sheet. Here s a tutorial for you: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that...

Excel: program a simple equation using excel, target value, cell b2

target value, cell b2, macro in excel: Feras, right click on the sheet tab and select view code. then paste in this code in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Count 1 Then Exit Sub If Len(Trim(Target)) = 0 Then Exit Sub...

Excel: qouestion on exchangin data in excel, cell a1, a10

cell a1, a10, a11: Example: say you want to replace the data in Col A and Col B with each other. First, select col A and copy it to col C. Then select col B and copy it to Col A. Then copy col C to col B. Then select col C and hit the Delete key. Example: Say on sheet1 you...

Excel: question on maintaining inventory excel sheet, closing stock, stock tracker

closing stock, stock tracker, first language: I appreciate that english is probably not your first language, but it is hard to follow what it is you want to achieve - if it helps to send me a sample file, my email is aidan.heritage@virgin.net - please note that I am on vacation at present so may not be...

Excel: Range objects in Excel VBA, free microsoft excel, excel vba

free microsoft excel, excel vba, question sign: A1 refers to just that cell, relative to the current sheet. If you want to specify a sheet, you need to say: SheetName!A1 Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: RE:, target value, cell b2

target value, cell b2, target address: Example: you have 2 documents in folder C: emp. The document filenames are A.xls and B.xls. You need a dropdown menu, such that the user can choose one of these documents and will automatically follow the hyperlink to it. Everything below refers to Sheet1....

Excel: RE: Deletin unwanted rows from multiple worksheets, rng

rng: Try this Sub Find_UNKNOWN() Dim sh as Worksheet Dim rng As Range Dim what As String what = UNKNOWN For Each sh in Activeworkbook sh.select Do Set rng = ActiveSheet.UsedRange.Find(what) ...

Excel: RE: Deleting unwanted rows from multiple worksheets, blank cell, rng

blank cell, rng, aidan: Sub DeleteRows() dim currentcell,nextcell,looper dim DeleteVar as boolean for each sh in worksheets sh.activate Set currentCell = sh.Range( A1 ) Do While Not IsEmpty(currentCell) you may wish to amend this test as it will stop if there is a blank cell...

Excel: RE: Retrieving data from multiple worksheets, sheet names, retrieving data

sheet names, retrieving data, a13: after this line: ActiveCell.Offset(iSheet - 1, 0) = & Worksheets(iSheet).Name put this code: Dim N as integer On error resume next N=0 N=Application.Match( Methane ,Sheets(iSheet).Columns(2),0) if N 0 then Activecell.offset(isheet - 1,1).Value =...

Excel: REVERSE CONCATENATION IN EXCEL, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Use the RIGHT function to get the right X characters, and use LEN to see how long it is: =RIGHT(A1,LEN(A1)-1) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL...

Excel: Rectangles, format cells, thick border

format cells, thick border, text boxes: Unfortunately, you ve found a (nasty) bug, and I m afraid I don t know of a workaround. Simply creating a no-fill rectangle in 03, open in 07, save, reopen in 03 and it s filled, even tho it says the property is no fill. All I can do is report it to Microsoft...

Excel: Refrences to OmniPage in Excel 2007 add-ins ribbon, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: I don t mean to make light of your situation, but that screen shot you sent me is kind of funny. You re not kidding, there really are a LOT of references to OmniPage. Unfortunately, you ve already taken all of the steps I would have taken to get rid of...

Excel: Regarding vba programming, excel vba programming, excel sheet

excel vba programming, excel sheet, complete project: Cells.SpecialCells(xlCellTypeLastCell).Row would pick up the last used row - so With .Range(.Cells(4, 1), .Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1)) would work. To have something apply to just one cell, take it out of the WITH Cells(4,1).Orientation...

Excel: Retrieving password from different VBA project, vba project, autoconnect

vba project, autoconnect, excel workbook: In the personal.xls Function Essbase_Info_Uname() EssBase_Info_Uname = MYUSER End function Function EssBase_Info_UPass() EssBase_Info_UPass = MYPASSWORD End Function to retrive the information in the other file Sub GetInfo() uName =...

Excel: Rounding, excel, calculation

excel, calculation: Sorry, I am unable to replicate your problem. If B2 contains the number 500 and A2 contains the number 2.68, the result is 13.4. It may be that either A2 or B2 are the results of a formula, and the 2.68 that is showed is in reality a result of 2.676, with...

Excel: Rounding numbers, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Brent, in order to help you, I d need to know under what circumstances you ll be rounding up vs. rounding down. You say half the time, but when exactly? Do you need an even split like at 0.625? Let me know if you have any other questions or comments....

Excel: Rounding down a sum, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: How about the ROUND function? You can specify the number of decimal places to round to. See: http://www.599cd.com/site/courselist/excel/excel231?key=AllExperts Let me know if you have any other questions or comments. Also, be sure to visit my web...

Excel: A real stickler, excel user, countif

excel user, countif, advanced excel: Dani, In the cell in Sheet2, format the cell as Windings and put in this formula =REPT(CHAR(233),COUNTIF(Sheet1!A1:A10, UP )) Worked fine for me. If I had 5 cells with UP in sheet1 in A1 to A10, it produced 5 up arrows in the cell with the formula....

Excel: remove first character of cell if alphabet, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: This is going to be tricky. I would isolate the first character and then check to see if it ISTEXT or not. If it is, then use the RIGHT function to separate the rest of it. Now you can perform your lookup. Let me know if you have any other questions or...

Excel: Sales Invoicing on Excel, free microsoft excel, sales invoices

free microsoft excel, sales invoices, question sign: You can use VLOOKUP to pull this information from a summary sheet, but I would strongly recommend considering moving from Excel to MS Access. You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you...

Excel: Sales Invoicing on Excel, sales invoices, vlookup function

sales invoices, vlookup function, cust ref: Sure. You did not ask that before. There are several ways to do this. Here is one way. Create a table somewhere, F1:J2, in this example, that lists ALL of the info for a customer, with the first column being the Cust Ref ID, or whatever it is. Ex:...

Excel: Save As Macro, server name, aidan

server name, aidan, unc: not sure what code you have to handle the save as, but presumably the file name ends up as whatever1.xlt - so replacing 1.xlt with xlt would handle that n=Replace(YourFileName, 1.xlt , .xlt ) would store in n the file name with the 1 removed - was...

Excel: Select last sheet, false selection, cell a1

false selection, cell a1, example sheets: The macro recoreder, while useful and helpful, creates very static and not dynamic code. To make this work the way you want you will have to practically rewrite the whole routine. But you can try: Assuming Sheets( Day 4 ).Select is the last sheet BEFORE...

Excel: Sellecting golfers placement in an event., 7 holes, bob bob

7 holes, bob bob, golf 2: Bob, So you are asking for a macro to do this? If so, I really don t understand your scoring rules. 1. I don t play golf. 2. your explanation isn t definitive. I assume one row has the handicap for each hole I am not sure at what point I...

Excel: Separate numbers from text, caroline reed, ccount

caroline reed, ccount, abcabc: I don t think there is an inbuilt function for this (although another expert may know a trick that I don t!) but a UDF could do it (user defined function using VBA) open the VBA editor and insert a new module, then paste Function TorN(rar As String,...

Excel: Share an excel file using VBA, data integrity, nothing works

data integrity, nothing works, disaster: I don t think you are going to be able to share/unshare through code, but I would caution strongly against sharing workbooks in the first place - the general view of all experts I ve ever spoken to is that they should NEVER be used - they are a recipe for...

Excel: Sort Macro with dropdown?, many different ways, column letter

many different ways, column letter, inputbox: Yes you can use the InputBox function to prompt the user to type a column letter on which to sort the data. Something like, untested Dim SortCol as String SortCol = InputBox(Prompt:= Type the column letter on which the data will be sorted and then click...

Excel: Sorting, dropdown arrow, home tab

dropdown arrow, home tab, first names: You should use filtering - in Excel 2003 use data/filter/autofilter, then select the name you want from the little dropdown arrow; In Excel 2007, it s Home tab, Editing section (at the far right), filter, OR, right-click on the student s name, select Filter/Filter...

Excel: Sorting Alphatical titles not words, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You d need a custom function to have Excel ignore a and the in song titles. It s not a feature built into Excel. You could make a second column that hides those words, and then sort by that column, but again you d have to write the functio for it yourself....

Excel: Splitting excel cells, text fragments, text functions

text fragments, text functions, check email: Jessica, This is a terribly difficult problem to solve with only equations. It could be done by detecting the location of each *-character, which in your case appears to be a separator between phrases. But it wouldn t be easy! The problem is...

Excel: Spread Sheet Formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: OK, so… Col A is X or not. Col D has all the prices Col E is price IF they mark X And you want to sum those up. Not too hard. In Col E say: =IF(A1= X ,D1, ) That will copy the price IF col A has an X in it. Now just sum up Col E. You can...

Excel: If Statement, staff name, a10

staff name, a10, good time: Anytime you need more than 7 nests is a good time to think of alternatives - not always possible, but in this instance you can use a countif statement =if(countif(TableRange,c1) 0, Resp Ther , ) which will find out if the entry exists and if so return...

Excel: If Then Statement in excel 2003 with more than 7 functions, impass, cell a1

impass, cell a1, spread sheet: Ben, Assume that you want to put your solution in cell A1 . You can break up your if statement among several cells as follows: Cell Formula A1 =IF(I5=1,0,IF(I5=2,0.05,IF(I5=3,0.1,IF(I5=4,0.05,IF(I5=5,0.05,IF(I5=6,0.05,A2)))))) A2 ...

Excel: Student Address Excel Database, free microsoft excel, yearbook class

free microsoft excel, yearbook class, question sign: How are you matching the students with the teachers? I would need to see a sample of how the sheets are setup in order to assist you. For example, if you have a student ID in both the student and teacher tables (sheets) then we could link them. Let me know...

Excel: Styles and formatting, range names, google

range names, google, trivial task: There is no builtin way to get a list of the number formats, unfortunately. You d be better to try searching google or bing to see if someone has written a program to do that. For Styles, if you use Excel 2007, then it d be easy to see the styles defined...

Excel: SUMIF function, sumproduct, pivot table

sumproduct, pivot table, g2: easiest way is via sumproduct, which allows a multiple condition sumif function =sumproduct(--(g2:g10600 =date(etc etc)),--(g2:g10600 =date(etc etc)),--(a2:a10600= branch 1 ),12:I10600) in this instance, the date functions will be the end date and start...

Excel: SUMPRODUCT formula, search values, louise louise

search values, louise louise, commas: Louise, The problem you re having is that you have assumed that putting the search values in commas within one of the --(.....) terms means to OR those terms, i.e. to look for any one of them. This is a case of making up your own syntax, and so of...

Excel: HOW TO GET SUMS ON AN EXCEL COLUMN, column sums, click method

column sums, click method, click control: POSSIBLY set to manual calculation - although this wouldn t explain if the FORMULA isn t going across- anyway, tools, options, calculation tab first - then ensure set to automatic. Also check the general tab to ensure that it allows cell drag and drop etc....

Excel: Subtraction, using microsoft office, deepika

using microsoft office, deepika, office excel: Hi Deepika, There should be absolutely no problems with a formula like =5-3. 1. Ensure that you did not type a space before the = sign 2. Ensure that the cell, where you are entering the formula is formatted to General 3. Check if the formulae like...

Excel: Sum in excel, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, question sign: You would need VBA code to read the color of the cell. I don t have any code on hand to give you, but this tutorial goes the OTHER way: setting the color based on the value (like conditional formatting) which should get you started. http://599cd.com/tips/excel/extend-conditional-formatting/?key=AllExperts...

Excel: Sum, Index, Match, conduit sizes, conduit size

conduit sizes, conduit size, match 2: Aimee, Assume Conduit type is in column A, conduit size in column B and conduit in column C Data shown is then in A2:C5 Assume you have a list of type and size in columns G and H G2: EMT H2: 100 in I2: =Sumproduct(--($A$2:$A$5=$G2),--($B$2:$B$5=$H2),$C$2:$C$5)...

Excel: Summarizing data, tab labels, column headings

tab labels, column headings, b productions: I assume you want all information on one sheet, which can be achieved using this macro: Sub CopyAll2One() Dim oSh As Worksheet Dim oTargetSht As Worksheet Workbooks.Add xlWorksheet Set oTargetSht = ActiveSheet oTargetSht.Range(...

Excel: Summing outputs from self-referencing if-statements, time differences, emai

time differences, emai, aidan: All i see in the picture is the data and one formula,so it s a littl tricky to resolve - is it possible to see the actual sheet - my emai being aidan.heritage@virgin.net - please note I m in the UK so time differences may mean my response seems slower than...

Excel: before save, colomn, field thanks

colomn, field thanks, cell font: Press Alt/F11, double-click ThisWorkbook in the project window, put in this code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) For Each cell In Intersect(Sheet1.Range( B:B ), Sheet1.UsedRange) If cell.Font.Bold...

Excel: selectively updating, excel workbooks, data changes

excel workbooks, data changes, oysters: You can change formulas to values by selecting the cells, using edit/Copy, then edit/Paste special and select Values. They will no longer update. Then you can create a new totals worksheet. You should probably have 2 total worksheets -- one with the formulas...

Excel: seperating cell contents, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: This is not going to be easy, but it can be done. You re going to have to use the FIND, LEFT, and RIGHT functions to manually break apart that string. It s a little too much detail to cover here, but I do show you how to do it in this tutorial: http://599cd.com/site/courselist/excel/excel232?key=AllExperts...

Excel: show all filtered data by vba, copy paste, karel

copy paste, karel, cells: In that case you need to remember the filtered data and do the copy after removing the filter. One way might be (untested): Dim oFilterRange as Range Code that does the filtering is here Set oFilterRange=Sheets( Sheet1 ).Range( A1 ).CurrentRegion.cells.SpecialCells(xlCellTypeVisible)...

Excel: If then, sort of, o5, p5

o5, p5, aidan: Much easier than you made it, and indeed than I tried to make it when I started working on it - so it must be the process that causes us all to think in overcomplicated steps. =IF(MAX($N$2:$P$2)=N2,1,0) in N5 and then dragged across will give you what...

Excel: specific formula for sum, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: The strikethrough is just a matter of cell formatting. For the running total portion of your question, see this tutorial: http://599cd.com/tips/excel/running-total/?key=AllExperts Let me know if you have any other questions or comments. Also, be...

Excel: standard deviation, standard deviation formula, t statistic

standard deviation formula, t statistic, array formula: Barry, Assume the data is in rows 2 to 500 with job title in column A, gender in column B and salary in column C. Assume column B holds an F for female and a M for Male. Then for females =stdev(if($B$2:$B$500= F ,$C$2:C$500)) Entered with CTrl+Shift+Enter...

Excel: A stickler, vlookup, excel user

vlookup, excel user, advanced excel: Dani This could be accomplished by VBA but I don t know VBA well enough to help. However if you have a spare sheet you could use a vlookup and a table to give you what you want. ie Arrows Table Count from sheet 1 A b ...

Excel: subtracing dates in column to report in the next, serial number

serial number: Joyce, Dates are stored as a serial number. The serial number is the number of DAYS since Jan 0, 1900. Therefore, since dates are DAY-BASED, to subtract a number of days from a date, you just take date-cell minus number-of-days. Since...

Excel: sum selected cells by vba, f10, a3

f10, a3, c3: Like this: Sub MakeFormula() Dim oCell As Range Dim sFormula As String sFormula = = For Each oCell In Selection sFormula = sFormula & + & oCell.Value Next Selection.Cells(Selection.Rows.Count, 1).Offset(1).Formula...

Excel: sumproduct function, array formula, pivot table

array formula, pivot table, ogilvy: Michelle, to check for a number entry use =SUMPRODUCT(--(G3:G50= OPERATING ),--(IF(ISERROR(O3:O50),0,O3:O50=5))) however, this now must be entered as an array formula using Ctrl+Shift+enter rather than just enter. If you do that you will get the...

Excel: Tab linked to cell on different worksheet, target value, target address

target value, target address, sheet names: Brian, OK. To recap the assumptions: In the tab order Master 50 sheets Summary sheet so the 2nd sheet in the tab order is linked to E6 3rd sheet in tab order is linked to F6 The formula you speak of in the summary sheet will change automatically...

Excel: Tab reference name based on cell reference, cell reference, column c

cell reference, column c, project 1: Daniel, the indirect worksheet function will do that for you: =sumproduct((Indirect( &B1& !A1:A15 )= Data $A1)*Indirect( &B1& !B1:B15 )) Since A1:A15 and B1:B15 are string until they are evaluated by Indirect, you don t need the $ signs - then...

Excel: Text to speech in Excel, installation speech, office microsoft

installation speech, office microsoft, alternative user: Donna, http://office.microsoft.com/en-us/excel/HP052741691033.aspx Install or remove individual components in Office Select the speech component to install However, if you used the default/typical installation, speech is set up as install on first...

Excel: Time difference calculation, cell a1, time column

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

Excel: Total of a line & conditional highlighting, format cells, cell a2

format cells, cell a2, menu format: Select the range that begins cell A2 (your first time) and extends down col A as far as your data goes. From menu, choose Format, Cells, Patterns, [Green], OK. With same range still selected, choose from menu Format, Conditional Formatting, Condition 1...

Excel: Transfer of data from one spreadsheet to another in the same work book, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: I m not sure I understand what you mean. You want to send out a spreadsheet with data, but stop people from opening it after 30 days? I haven t done this before, but I m sure you could write a macro to do it. Let me know if you have any other questions...

Excel: Transform Excel table into Access Table format, column headings, b 90

column headings, b 90, copy and paste: As this would seem to be a one off, I d do it manually - insert a column and type Product A and auto filldown - then EITHER copy the appropriate results directly to access and then replace Produce A with ProductB OR copy and paste below the data in excel and...

Excel: Travelling allowance, rs 4200, english sentences

rs 4200, english sentences, test ct: Rajan, Thanks for the clarification, that s much better. The logic you want is like this: = IF(city=MUMBAI,IF(salary 4200,0.22*600,IF(salary 5400,0.22*1600,IF(salary 12000,0.22*3200))), IF(salary 4200,0.22*400,IF(salary 5400,0.22*800,IF(salary...

Excel: Trim text, string manipulation functions, free microsoft excel

string manipulation functions, free microsoft excel, string concatenation: Use the LEFT() function. =LEFT(A1) I cover the various string manipulation functions in my EXCEL 232 tutorial: http://599cd.com/site/courselist/excel/excel232?key=AllExperts In this tutorial we get into custom functions and techniques for manipulating...

Excel: time delay, correct parameters, external software

correct parameters, external software, worksheet functions: Whatever technique you are using that causes EXCEL to ...the worksheet functions are checking this other program every 400th of a second... should be incorporated it seems to me to fix your problem. Not sure I can really help without seeing the entire process...

Excel: total time, target value, sheet tab

target value, sheet tab, mm ss: right-click the sheet tab, select View Code, enter this: Dim tm As Double Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True If IsEmpty(Target) Then Target.Value = CDbl(Now) - tm Else Target.Value =...

Excel: trace dependence, dependents, mkt

dependents, mkt, precedents: The only answers I ve found are answers you may already have seen http://www.bing.com/search?q= The%20Trace%20Dependents%20command%20found%20no%20formulas%20that%20refer%20to%20the%20active%20cell &mkt=en-gb&FORM=TOOLBR&DI=2883&CE=14.0&CM=SearchWeb sorry...

Excel: trying to creat 2 specfic formulas, free microsoft excel, million different ways

free microsoft excel, million different ways, question sign: Sounds like you need to learn how to use the OR() function: =IF(OR(A1=1,B2=5), Joe , Sue) Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to...

Excel: Unable to refresh pivot table, pivot table, vba macro

pivot table, vba macro, table tools: Kathy, the command is refreshtable Dim pvtTable as PivotTable Set pvtTable = Worksheets( Sheet1 ).Range( A3 ).PivotTable pvtTable.RefreshTable so in your case ActiveSheet.PivotTables( PivotTable1 ).RefreshTable -- Regards, Tom Ogilvy...

Excel: User form field, medium priority, high priority

medium priority, high priority, combobox: John, Assume the textbox next to the combobox (on the userform) where you want the description is named SkillType Private Sub Combobox1_Click() Dim s as String if me.Combobox1.ListIndex = -1 then me.SkillType.Value = exit sub End...

Excel: Using a look up function, date function, c6

date function, c6, ogilvy: In my tests, Edate(C6,-1) returns 3/30/2009. I can only guess, but I would suspect your table contains 3/31/2009. Instead of Edate(C6,-1) you might try Date(Year(C6),Month(C6)-0,0) to go back one month to go back two months use Date(Year(C6),Month(C6)-2,0)...

Excel: Using the Results of a Formula as Part of a Cell Address in Another Formula, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You could use the OFFSET function to move down a number of rows equal to the value of your first formula. =OFFSET(A1,3,0) This will move down 3 rows from A1. See if this helps you. Let me know if you have any other questions or comments. Also,...

Excel: Using Sumproduct with 2 criteria, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: How about creating a second column? In that column, put a 1 if that record meets your criteria, then count up all of those 1 at the bottom. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE...

Excel: Using VBA, group field, document status

group field, document status, internet explorer: Yes it is possible and VBA can talk to an IE page but this but will require a lot of coding and may not be the best way overall. You can use WORD to add and subtract numbers but EXCEL may be the better vehicle. SO, while EXCEL CAN do this it may not be the...

Excel: update from another workbook, startup message, vesion

startup message, vesion, ogilvy: Dave, Here is all you need to know about it. Information on both xl2007 and xl2003 and earlier since I didn t see you specify a vesion. http://support.microsoft.com/kb/826921 How to control the startup message about updating linked workbooks in Excel...

Excel: using a count with an And statement, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: The easiest solution would be to set column E to: =IF(AND(C2= Confirmed ,D2=910),1,0) Then just add up column E. There s your count. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other...

Excel: using rank function, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: See my tutorial on how to use the RANK function in Excel: http://www.599cd.com/tips/excel/rank-function?key=AllExperts Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: VB, target value, target address

target value, target address, macro name: You didn t answer my question: What should happen if BOTH L34 and K13 change as a result of the calculation? Maybe you should send me a copy of the workbook... send to bobumlas@Yahoo.com, use subject of AllExpertsQ and remind me what you re trying to...

Excel: VBA, macro recorder, test data

macro recorder, test data, singapore: It s distributing them to new sheets only if they don t exist already, based on the name. Clearly, you cant have 2 sheets with the same name, so the ones created by the macro are using the accurate names and perhaps the existing sheets need to be renamed...

Excel: VBA, isnumeric, cell a1

isnumeric, cell a1, rng: You don t say what sheet A1 is on, so I ll assume it s the active sheet. You don t say which row to delete, so I ll assume it s row 1. The vba code is: Sub example() Dim rng As Range Set rng = ActiveSheet.Range( a1 ) If (rng = Jim ) Or (rng...

Excel: VBA to copy same range from different workbooks and to put in master worksheet, xls files, sname

xls files, sname, bk1: Joseph, Set bk = Workbooks( Master.xls ) change to reflect your master workbook name Set sh = bk.Worksheets( Master ) change to reflect your master worksheet name sPath = C:myfolder1Myfolder2 change to reflect your path that holds the...

Excel: VBA: Deleting unwanted rows from multiple worksheets, rng, ogilvy

rng, ogilvy, rasheed: Rasheed Sub Find_UNKNOWN() Dim sh as Worksheet Dim rng As Range Dim what As String what = UNKNOWN for each sh in worksheets sh.Activate Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then ...

Excel: VBA-Excel2003, vba procedure, excel2003

vba procedure, excel2003, tbox: In design mode, right-click on the textbox to determine its name (which will appear in the name box). Let s say the name is TextBox1. To refer to this textbox in a vba procedure, use code like this. Sub tboxExample() Dim tbox As OLEObject Set...

Excel: VBA help, cell a1, index function

cell a1, index function, time student: Hi Peter, One way might be by creating a list of all levels and using the INDEX function to get the position in the list. Suppose your list is on the sheet Sheet3: and cell A1 contains the current level, then this formula returns the position in the list...

Excel: VBA - Image in userform, mauri, shift key

mauri, shift key, d20: Hi Mauri, - Assuming Excel 2003 You normally cannot save a picture of a range, but using a little trick you can. - Insert a chart with no data - Select the range - Hold the Shift key and select Edit, Copy Picture (select as shown on screen) - click...

Excel: VBA for each query & data validation, data validation, control names

data validation, control names, smsg: Well, you could use the change event I suggested with some modification: Private Sub TextBox1_Change() Dim sOldText As String sOldText = TextBox1.Text If IsNumeric(sOldText) Then Exit Sub TextBox1.Text = UCase(sOldText) If sOldText like...

Excel: VBA Script, cell a2, cell a1

cell a2, cell a1, orng: Hi Jaye, You can use this macro (select a cell in the table first): Sub RemoveEmptyAnd1() Dim oRng As Range With Selection.CurrentRegion .AutoFilter 1, , xlOr, 1 .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete...

Excel: VLOOKUP, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vlookup formula: I would have to recreate the sheet to tell you for sure. There is a fourth argument for VLOOKUP which indicates whether or not you want an EXACT match. Perhaps that will help you. In the mean time, here s a tutorial on VLOOKUP that might help you: You...

Excel: VLOOKUP duplicated numbers, logic functions, number column

logic functions, number column, staff records: Ian, Of course, it is possible to add 0.1 to each occurrence of the number, to make it unique. But then, VLOOKUP would have to know what the new unique numbers are. If that s okay with you, then proceed. You could sort the original table by...

Excel: VLOOKUP between files, free microsoft excel, vlookup

free microsoft excel, vlookup, excel tips tricks: Kyle, this feature still works, but Microsoft changed the implementation a little. If you just open two Excel workbooks you can t easily copy and paste links between them anymore. Why? I don t know. You have to open the workbooks in the same workspace...

Excel: VLOOKUP and IF statments, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You do have too many parameters for your VLOOKUP statement. It won t accept all of those different IF functions. You need to NEST them. IF(a=b,1,IF(a=c,2,IF(a=d,3,4))) You can use the IF function to have Excel place a value in a cell based on one or...

Excel: Vlookup with IF function, vlookup function, price column

vlookup function, price column, food items: Usman, Sounds like my answer stands: =IF(NOT(ISERROR(VLOOKUP(parameters))),VLOOKUP(parameters),your-equation-goes-here) This equation should go into the final-price column of Table B. In place of your-equation-goes-here ,...

Excel: (if(and vlookup???, pairs, cells

pairs, cells, fraction: It looks like the criteria is always dependent on D6 being 8, so you can put all the other pairs of values in a table and use this: =IF(D6=8,VLOOKUP(F230,TheTable,2), ) where TheTable is defined to be the cells which look like this: 0 1.9E Microlam...

Excel: vlookup, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: Sure, you answered the question yourself. Use the MATCH function to see if the last name from column A is in column B. =MATCH(A1,B1:B100) If that comes back TRUE, you know you have a match. Let me know if you have any other questions or comments....

Excel: can vlookup return a cell reference?, cell reference, ogilvy

cell reference, ogilvy, match: Jeffery, You can use Match to return the row A &match(DateValue( 1/15/2008 ),Sheet1!A:A,0) would return say A35 you can turn that into a cell reference with Indirect Indirect( Sheet1!A &match(DateValue( 1/15/2008 ),Sheet1!A:A,0)) as...

Excel: vlookup in vba - what's wrong?, vlookup function, ogilvy

vlookup function, ogilvy, cel: Kuba, the first argument to vlookup should be a single value. You are trying to use a range of values form J34:J400 try something like this Sub vlookup() Dim cel As Range, r as Range Dim res as Variant ####################### Running vlookup...

Excel: Working hours calculation, free microsoft excel, excel tips tricks

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

Excel: Back up work on Excel, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: You generally can t just save file to the CD drive. Assuming you have a CD-R or CD-RW drive (that you can write to) you need to go into My Computer or Windows Explorer and copy the files to the CD-R drive (drag and drop is fine). Then click on the BURN CD...

Excel: XL SUM formula, sumproduct, sum formula

sumproduct, sum formula, performance report: Sorry about that - I wrote the formula in excel but somehow in copying it managed to replace an = with a , - the correct version should be =SUMPRODUCT(--(NOT(ISERROR(SEARCH( high prospect , Performance Report !L3:L300)))),--( Performance Report !M3:M300=...

Excel: And / OR, excel formula, issa

excel formula, issa, g2: Issa, here s how: =IF(OR(AND(C2 ,G2 ),H2 ),1,0) This would be written in cell L1, in order to make L1 = 1 or 0 I also assume that this term has a typo, the extra at the end: H2 , so I left it out. Bil...

Excel: Add Specific Cells In Excell Spreadsheet, macro button, cell a1

macro button, cell a1, cell b1: Rod, 1) See Chip Pearson s page: http://www.cpearson.com/Excel/clipboard.aspx the first topic is putting text on the clipboard with VBA and includes sample code. 2) instead of Range( A1 ).Select I would guess you want to have cells(rows.count,...

Excel: Animating font in Excel VBA, vba code, excel vba

vba code, excel vba, scrolling marquee: * Actually i am realizing, that you aren t going to be able to run continuous VBA code, while the user can still work with the worksheet. Excel will basically freeze as long as the code runs. This is a limitation of VBA. However, you might be able...

Excel: #Div/0! switch to "<0", cell interior, g34

cell interior, g34, formating: Paul, You are missing an end if statement. It is unclear exactly where it should be, so I guessed: Sub Conditional_Formating_IRR_Table() Set MyPlage = Range( E23:G34 ) For Each cell In MyPlage If IsError(cell.Value) Then If cell.Value...

Excel: adding text to certain cell in multiple excel workbook sheets, excel workbook

Excel: adding text to certain cell in multiple excel workbook sheets, excel workbook

Excel: Close 2 Excel Files at the same time, answer the question

Excel: Close 2 Excel Files at the same time, answer the question

Excel: Macro protect sheets issue, activex, boolean

Excel: Macro protect sheets issue, activex, boolean, parameters

Excel: PDF to Excel Macro, business associates

Excel: PDF to Excel Macro, business associates

Excel: Problem with a Goal Seek Macro, cells, cell range

Excel: Problem with a Goal Seek Macro, cells, cell range

Excel: permutation calculation for excel 2007

Excel: permutation calculation for excel 2007

Excel: Sum with Vlookup

Excel: Sum with Vlookup

Excel: time format in chart, mid night, graph

Excel: time format in chart, mid night, graph

Excel: VB Question with Pivot Tables, sum sql, filter criteria

Excel: VB Question with Pivot Tables, sum sql, filter criteria, excel sheet

Excel: hi again !plz help!!:(, active x control, range sort

active x control, range sort, combobox: I would still go with a listfill range – if the user is adding data, presumably you would want this kept for later anyway? So have a dynamically named range, sort this every time data is added (as the data is on the workbook this is an easy sort) and that...

Excel: AND Statement (same question), Sixthsense

Sixthsense: Mention where is the pull down menus are present (cell reference) and regarding this [Ex. if single and makes $40K (which is $33K - $65K range) it equals 25% tax bracket] here how I know how much amount he is getting that is $40K. In which cell the $40K...

Excel: CAPS LOCK, engravers

Excel: CAPS LOCK, engravers

Excel: Excel crashing with macro, false application, true application

Excel: Excel crashing with macro, false application, true application, cobo

Excel: excel nested if, and, or

Excel: excel nested if, and, or

Excel: removing parentheses and hyphen, amravati, atul

Excel: removing parentheses and hyphen, amravati, atul, 1111

Excel: mean functions, outliers, seattle usa

outliers, seattle usa, arithmetic: Katy I m thinkin that you want to use the plain old average. If you are not familiar with the others trimmean,geomean,harmean these require adjustments of the calculation to allow for unusual deviations. It looks to me like you simply want to know the...

Excel: merge (add) data from several rows into one., question data, blank row

question data, blank row, pivot table: George After you apply the subtotal and have the results, you can select your data (or all your data where you want the data filled in) including the new subtotal rows and do F5 (Go to Dialog), then select special and select blanks. Click OK now look...

Excel: #VALUE issue ++ ???, issue question, b100

issue question, b100, null string: Still can t see the screen shot. P100 is not blank. If it were, then the formula would return blank (null string). If it s returning the sum, then it s simply not blank. Maybe there s a space in it, or some character. I guarantee that if you go to P100 and...

Excel: #VALUE issue, lookup database, dxxx

lookup database, dxxx, issue question: I don t follow, sorry. The formula I gave you will be blank if P100 is blank. And your VLOOKUP formula should probably contain the 4th parameter, by the way: =IF(ISNA(VLOOKUP(A1489, [CUSTOMER DATA BASE.xls]Database !$A$2:$AM$501,22,FALSE)), ,VLOOKUP(A1489,...

Excel: copy/past based on value, test sheet, excel workbook

test sheet, excel workbook, value question: Yes, but why? Sub DoAsMacro() LRow=Sheets( Ownership ).Range( A60000 ).end(xlup).Row Sheets( OwnerShip ).Range( E1:F & Lrow).Formular1c1 = =IF(rc[-1] 0,IF(ISNA(MATCH(rc2,Yesterday!c2,0)), ,INDEX(Yesterday!c,MATCH(rc2,Yesterday!c2,0)))) End Sub...

Excel: Creating school report cards, excell, sanjay

Excel: Creating school report cards, excell, sanjay, architecture

Excel: DDE link in Excel with macro to work with ThinkORSwim, dynamic data exchange, excel dde

Excel: DDE link in Excel with macro to work with ThinkORSwim, dynamic data exchange, excel dde, thinkorswim

Excel: Tab Order, format cells, vba excel

Excel: Tab Order, format cells, vba excel, macros

Excel: Color Palette, default color palette, xlstart folder

default color palette, xlstart folder, startup folders: Tim, I created a Book.xlt and saved it in the xlstart folder and it worked fine for me. I couldn t reproduce the behavior you cite. I suspect you just renamed your workbook as Book.xlt and didn t save it as a template. Read this extract: Excel...

Excel: Extract 4 digit number from a text cell, array formula, cos d estournel

array formula, cos d estournel, digit number: Gregory, Assume the first entry is in D1 then =MID(D1,SMALL(IF(ISNUMBER(MID(D1,ROW(INDIRECT( 1: &LEN(D1))),1)*1),ROW(INDIRECT( 1: &LEN(D1)))),1),4)*1 entered with Ctrl+Shift+Enter rather than just enter (since it is an array formula) produced 1982...

Excel: Formatting with formula, vba code, logical test

vba code, logical test, cell reference: Hi Dave How are you? Please consider the following VBA code: Sub formattingwithFormula() Dim R As Range Set R = ActiveSheet.Range( A14 ) If R 100 Then R.Interior.ColorIndex = 6 Else R.Interior.ColorIndex...

Excel: Calculation of Tax, irs info, gross salary

Excel: Calculation of Tax, irs info, gross salary, w4 form

Excel: Collate printing

Excel: Collate printing

Excel: DATEDIF returns #value! error, value error, excel 2000

Excel: DATEDIF returns #value! error, value error, excel 2000, tch

Excel: Excel 2007 Formulas, ip addresses

Excel: Excel 2007 Formulas, ip addresses

Excel: Excel Weekday function, excel function, cell a1

Excel: Excel Weekday function, excel function, cell a1, weekday

Excel: hyperlink to a picture in the same worksheet, tap

Excel: hyperlink to a picture in the same worksheet, tap

Excel: VBA Copy Multiple Workbooks to one Sheet, folders

Excel: VBA Copy Multiple Workbooks to one Sheet, folders

Excel: Macro: Change sheets when > 0, target value, target address

target value, target address, chip pearson: Greg, Right click on the sheet tab and select view code. Put in a procedure like this Private Sub Worksheet_Change(ByVal Target As Range) If target.address = $E$200 then if len(trim(Target.Value)) 0 then if isnumeric(Target.value) then ...

Excel: am/pm breakout on 24 hour time, time question, c30

time question, c30, allexperts: This will require knowing how to do two things: 1. Getting a value from another sheet, and 2. Using the IF function The first item is easy. You can refer to a value on another sheet like this: =SheetName!FieldName So if you want to get F4 from...

Excel: VBA - Rows to Columns, csv files, vba macro

csv files, vba macro, screen button: Phil, Received the file. So the file will already be in a workbook when you want it processed? I will write the code to do that. You have 4 categories in your input, but you only show 3 for the output - plus the output look pretty random as far...

http://en.allexperts.com/q/Excel-1059/2010/2/f/addition-time-stamp.htm

http://en.allexperts.com/q/Excel-1059/2010/2/f/Date-Format-Excel.htm

Excel: excel overtime formaulas, correct answer, correct outcome

Excel: excel overtime formaulas, correct answer, correct outcome, time and a half

Excel: Label Scatter Plot Points from a Filtered Worksheet, instr, subroutine

Excel: Label Scatter Plot Points from a Filtered Worksheet, instr, subroutine, b2

Excel: Nested If, And, Or Functions, dixon, suggestion

Excel: Nested If, And, Or Functions, dixon, suggestion

Excel: subtracting multiple cells

Excel: subtracting multiple cells

Excel: Horizontal Autofilter, equipment segments, data validation

equipment segments, data validation, format columns: Taylor, You would need to unhide the columns and then just apply new criteria - I believe that is what you are asking: you would need to select row1 and do format= Columns= Unhide (or in Excel 2007, choose the format button on the home tab, then select...

Excel: horizontal line—cannot change the color, page borders, drawing tools

page borders, drawing tools, color option: The first question needs to be - how did you insert the line - IF I was doing it, I would go to the insert ribbon, click on shapes, the the line and draw it - doing this SHOULD indeed bring up the options you want. If you have done it another way, you may...

Excel: Macro for pasting text., macro button, cell a1

macro button, cell a1, stage2: Yes, I would be able to modify but I can t, mainly because you did inlcude the code for me to modify. I suggest you re-contact the volunteer who wrote the code for you since that person should be familiar with his own code and familiar with the specifics...

Excel: Macros assigned to buttons changing after running program, program question, funky things

program question, funky things, vb 6: I m sorry, I meant to provide my email: ipisors@gmail.com Unless you need to refer to a Workbook Event, you shouldn t need to have the code in the Workbook module. You can (and should always) still refer to things as explicitly as possible, i.e.: ...

Excel: Merging text data from two spreadsheets, free microsoft excel, school district names

free microsoft excel, school district names, excel tips tricks: You could use VLOOKUP to pull that information into your 2nd sheet (assuming you still have the original). Then you could copy and paste-special the DATA into a third sheet. This would prevent you from having to select all of those items again. You can...

Excel: merged cells problem, shbox, m360

shbox, m360, vba: Ah, no it won t - it should - I admit to NOT working with merged cells very often as they aren t good for vba - and I personally find they aren t good design - merge tends to be for labels, so I use drawing objects for these instead. However, see http://www.vbaexpress.com/kb/getarticle.php?kb_id=391...

Excel: merging and consolidating, kind cooperation, employee ids

kind cooperation, employee ids, employee id: Dear Sreekanth, Please send me the sample file so that I can test your program. email: ariful.mondal [a] gmail.com you can also refer : ‘ Code by Tom Ogilvy - 6/29/2007 See Example at http://en.allexperts.com/q/Excel-1059/Combining-data-multiple-workbooks.htm...

Excel: merging and consolidating, kind cooperation, sh1

kind cooperation, sh1, true range: Difficult to debug from the code alone – my method for debugging this would be to single step through the macro to see if it is possible to pick up the error – there isn’t anything technically wrong with the code as it will run fine for me on a blank sheet...

Excel: merging and consolidating, kind cooperation, sh1

kind cooperation, sh1, true range: Hi I tried to run the VBA code and get the same error message as you have on the following statement. .Range( A1 ).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _ C1 ), Unique:=True Could you please click on debug...

Excel: merging and consolidating, kind cooperation, employee id

kind cooperation, employee id, true range: Sreekanth if you are copying a contiguous rectangular range with no merged cells in it, then the below code should work. ThisWorkbook.Sheets(1).Cells(i, 1).PasteSpecial xlPasteAll I don t know what is in the clipboard when you execute your command...

Excel: Percentage Formula – Excel 2003, target date, percentage formula

target date, percentage formula, gmail: Hi Raz, Using a simple Date 2 - Date 1 gives you the number of total days that elapsed between that reported by supplier and returned from supplier. Since you need to show the achievement relative to target, you will need a target date right? What...

Excel: Said another way............., pmm, mmp

pmm, mmp, mpp: If I understand, you just need to remove it from the statement: =IF(ISNA(MATCH( X ,CA6:CC6,0)),IF(OR(CA6&CB6&CC6= PPP ,CA6&CB6&CC6= MPP ), S ,IF(OR(CA6&CB6&CC6= MMM ,CA6&CB6&CC6= MMP ,CA6&CB6&CC6= PMM ), L ,IF(OR(CA6&CB6&CC6= PMP ,CA6&CB6&CC6= MPM ), Z ))),...

Excel: combine two VBA codes, outlook application, code bits

outlook application, code bits, rng: The easiest way to combine the two is to write a third routine that calls each one, like: Sub RunBoth() Call mailbanking1 Call sendtoHO End Sub and then just run RunBoth. I suggest you contact the person who wrote these rountines for you to explain...

Excel: Excel - Duplicate Values, question column, destination area

question column, destination area, mrs brown: I would still probably use a countif function - put this in a helper column, then filter on all cases were the result is NOT 1 - copy this to the destination area. This would seem to be the quickest way round it. You have my email if that helps with any...

Excel: Conditional formulas???, b20, cells

Excel: Conditional formulas???, b20, cells

Excel: Converting a formula to a macro?, array type, type evaluation

Excel: Converting a formula to a macro?, array type, type evaluation

Excel: Data Manipulation, paste values, text sort

Excel: Data Manipulation, paste values, text sort, home tab

Excel: Excel 2007 Sharing Problem

Excel: Excel 2007 Sharing Problem

Excel: Excel - Daily Data Auto Extraction

Excel: Excel - Daily Data Auto Extraction

Excel: Excel MACRO, step 1

Excel: Excel MACRO, step 1

Excel: excel cell color copy paste, home tab, color button

Excel: excel cell color copy paste, home tab, color button, 2 steps

Excel: Formula help - splitting text in a cell into multiple cells limited by character length, istart, return string

Excel: Formula help - splitting text in a cell into multiple cells limited by character length, istart, return string, last word

Excel: hide hyperlink tooltip, tooltip, programmatically

Excel: hide hyperlink tooltip, tooltip, programmatically, hyperlink

Excel: Peak detection (Maxima and Minima)

Excel: Peak detection (Maxima and Minima)

Excel: Sum function not working - returning zero, string function

Excel: Sum function not working - returning zero, string function

Excel: unusual file size, googling

Excel: unusual file size, googling

Excel: Weekend Dates, cell reference, weekday

Excel: Weekend Dates, cell reference, weekday

Excel: Macro Protection in Excel 2007, question thanks, compatability

question thanks, compatability, aidan: I ve used windows 7 on my machine to do this and had no problem - I ve saved the file as a macro enabled workbook, I got a security warning when opening it with normal security settings) but was able to select to enable macros - so it SHOULD work OK for you...

Excel: Merge 2 rows of data into 1 - variation, proper solution, ogilvy

proper solution, ogilvy, thanks in advance: Erik, If you can send a sample workbook to me at twogilvy@msn.com I will take a look. In your example, I am not sure why 11 in the first column suddenly becomes 10 and 22 becomes 11 and 33 becomes 12. So if you could explain that and for the sample...

Excel: macros automation, target range, timer functions

target range, timer functions, correct syntax: Hi Mike, Not sure if asking for help is a mistake! To have Excel do something every hour, you basically have two options. 1. Use Windows scheduling software to open an Excel file every hour. 2. Have an Excel file that is open all the time which has...

Excel: convert [h]mm to number, cell a2, good luck

cell a2, good luck: Wioletta: Your formula provides the #name error because the function name is =HOUR instead of =HOURS; however, making that change may not provide the answer you are looking for (because you have more than 24 hours). I suggest the following function:...

Excel: Anniversary Date Recognition?

Excel: Anniversary Date Recognition?

Excel: Automatically insert different filename in a formula, rerun, filenames

Excel: Automatically insert different filename in a formula, rerun, filenames, attachments

Excel: autosum a large column of calculated times, a2, typo

Excel: autosum a large column of calculated times, a2, typo

Excel: how to not chart blank columns, ucase, vba

Excel: how to not chart blank columns, ucase, vba

Excel: Macro, data validation

Excel: Macro, data validation

http://en.allexperts.com/q/Excel-1059/2010/4/f/macro-Delete-Row.htm

Excel: pop up alerts in excel

Excel: pop up alerts in excel

Excel: Repeat sorting, waste time, apologies

Excel: Repeat sorting, waste time, apologies

Excel: VBA code for Excel 2007 Userform to select a printer, class word, default printer

Excel: VBA code for Excel 2007 Userform to select a printer, class word, default printer, vba macro

Excel: MACROS FOR EXCEL, sheet1, arya

sheet1, arya, excel sheet: Pramod Arya, the easiest is to use the built in vlookup worksheet function. You could use it directly in your cells, but since you asked for a macro, I can have the macro do it. Sub CopyData() Dim r2 As Range, r As Range Dim s As String, sForm...

Excel: MACROS FOR EXCEL, arya, excel sheet

arya, excel sheet, sheet1: I wouldn t do this with a macro, though if it s essential one could be used - instead, I would use the vlookup worksheet function - see http://www.aidanheritage.byethost3.com/excel/ExampleVlookup.xls and http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls...

Excel: Macros and drop down lists, list of departments, drop down list

list of departments, drop down list, spreadsheet: Chadd If the dashes are part of the format, then you should be able to filter for non-blanks. If the cells actually contain the -, then it may make more sense to get rid of them. You could do a search and replace to get rid of the dashes; but that could...

Excel: macros continuation, close application, jkp

close application, jkp, pieterse: Open the file with the macro that needs to be run. Insert a new module in the VBA editor Paste this code into that module, close and save the file. Now open the file in an Excel session and wait until the hour has passed. Option Explicit Dim mdNextTime...

Excel: If else multiple cases macro, code5, code column

code5, code column, code3: Hi, Thanks for the further explanation. The following code will determine the last row in column D and save it to variable LastDataRow. ============================= Dim LastDataRow as Long With Worksheets( Sheet1 ) LastDataRow =...

Excel: Excel Formula, sumproduct

Excel: Excel Formula, sumproduct

Excel: Excel name box, imagination, vb

Excel: Excel name box, imagination, vb

Excel: excel left function can it return numerical values, simple solution, copy and paste

Excel: excel left function can it return numerical values, simple solution, copy and paste, numeric numbers

http://en.allexperts.com/q/Excel-1059/2010/5/f/Statement-13.htm

Excel: VBA Code, simple solution

Excel: VBA Code, simple solution

Excel: VLOOKUP returns value from cell above, vlookup function, search term

Excel: VLOOKUP returns value from cell above, vlookup function, search term

Excel: vba macro [filter], fred fred fred, sam tom

fred fred fred, sam tom, vba macro: Preparation: I have entered various names in Column A of Sheet1 (e.g. JOHN, SAM, TOM....) I have lots of data in Sheet2 but names appear in ColumnA. Row1 has headers. Now, you can place your cursor on any name in ColumnA of Sheet1 and run following Macro....

Excel: ABSENTEE TRACKING, question pool, answer mark

question pool, answer mark, column c: Mark, that is what my formulas do. the arguments for offset are Offset(base cell, row offset from base, column offset from base, number of rows, number of columns) so Offset($C$3,0,0,1,7) so I don t offset from C3, but I expand the range...

Excel: Aggregative summation, excel user, udf

excel user, udf, summation: Please send the file to bobumlas@yahoo.com, use subject of AllExpertsQ and repeat what you re trying to do. I didn t follow your current explanation. ----------------------------------------------------------------------------- Please check out the Excel...

Excel: Excel 2003, vlookup, google

vlookup, google, numeric values: Not sure what has gone on with that file - if you email I can send you a real copy of it - as to the formula, try =sumproduct(--($D$2:$D$5000= Alchemy 89 ),--($F$2:$F$5000=date(2010,6,3)) (date takes the arguements year, month, day - I m not sure if...

Excel: Charts in Excel 2007

Excel: Charts in Excel 2007

Excel: Copy Excel Row Value In To Notepad Using VBA...@, asp files, alistair

Excel: Copy Excel Row Value In To Notepad Using VBA...@, asp files, alistair, cheers

Excel: Excel Event Code with Data Validation, mac office, drop down list

Excel: Excel Event Code with Data Validation, mac office, drop down list, monday tuesday

Excel: find and replace macro

Excel: find and replace macro

Excel: Simple > or < in an if statement, logic problem, best guess

logic problem, best guess, datevalue: When asking a question, please always include a precise example. What exactly is in cell G2? What exactly is in cell F4? My best guess is that that Excel isn t recognizing the data in G2 as a date. You can correct this by changing your formula to: =IF(DATEVALUE($G$2)...

Excel: Custom Rounding if 3rd decimal <= 5, cell a1, first glance

cell a1, first glance, decimal places: Mark You were correct it was a bit more difficult than it appears at first glance, but this formula should work for you. =IF(AND(A1-ROUND(A1-0.005,2) 0.005,A1-ROUND(A1-0.005,2) 0.006),ROUNDDOWN(A1,2),ROUND(A1,2)) 1.495 in cell A1 will result in 1.49...

Excel: auto scroll results

Excel: auto scroll results

Excel: colorize cells with same values, arrow, cells

Excel: colorize cells with same values, arrow, cells

Excel: Dragging a formula down but letting the the sheet refrence change each time, cell a1, sheet names

Excel: Dragging a formula down but letting the the sheet refrence change each time, cell a1, sheet names, tab sheet

Excel: Excel - Unusual Double Click Behaviour, sheet tabs, data sheets

Excel: Excel - Unusual Double Click Behaviour, sheet tabs, data sheets, raw data

Excel: Negative time calculation, thanks a bunch

Excel: Negative time calculation, thanks a bunch

Excel: pasting in filtered cells in excel 2007

Excel: pasting in filtered cells in excel 2007

Excel: VBA List Filenames in a Folder into an array, matter of fact, array

Excel: VBA List Filenames in a Folder into an array, matter of fact, array

Excel: Is it possible to have multiple splits in one worksheet?, Sixthsense

Sixthsense: Have a look in the below links for more details about split screen:- http://www.bettersolutions.com/excel/EDO113/NE215138332.htm http://gaetc-ejournal.org/tutorials/excel/teachexcel/organization/split.htm Don t hesitate to ask again, if your requirement...

Excel: Another toughie, Sixthsense

Sixthsense: Please clarify me some of the below queries. Question-1 For example today 31/07/2010 my closing stock is 40, my opening stock on 1/07/2010 should also reflect as 40 In this please clarify how you will be getting the 31/07/2010 Opening Stock? Question-2...

Excel: Append Data to a sheet from another workbook, Sixthsense

Sixthsense: Hi Shiv, 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: Changing cell background colors for printing, mickie

Excel: Changing cell background colors for printing, mickie

Excel: Grid Lines Not Printing, border print, gridline

Excel: Grid Lines Not Printing, border print, gridline, running office

Excel: Macro to delete columns (by column name), switch statement

Excel: Macro to delete columns (by column name), switch statement

Excel: How to remove space between numbers

Excel: How to remove space between numbers

Excel: How to remove space between numbers

Excel: How to remove space between numbers

Excel: Seeing formulas, quotes, ogilvy

Excel: Seeing formulas, quotes, ogilvy

Excel: Vlookup within a Perimeter, hi bob, data return

Excel: Vlookup within a Perimeter, hi bob, data return, perimeter

Excel: Combo box with a selection, combo box, question thanks

combo box, question thanks, combobox: Sanjay If combobox1 is on userform1, when it initializes it will be set to the third item in the list with the following code: Private Sub UserForm_Initialize() ComboBox1.ListIndex = 2 End Sub The first item in the combobox is listindex 0. ...

Excel: conditional formating, conditional formatting, cell c1

conditional formatting, cell c1, cell b1: Sorry for not being clearer - you had asked for conditional formatting, so I gave you a formula to be used with the conditional formatting option - it s not to be entered as a cell formula - if you look in the options for conditional formatting you will probably...

Excel: Excel 07 (max date <= today), array formula, maxa

array formula, maxa, calendar dates: Not clear why you need the actual date - wouldn t something like =IF(MAXA( IF( R_Values = U , R_Dates + 45 )) TODAY(), Overdue , OK ) as an array formula handle it for you? I m guessing you don t need to actually SEE the date, just see if it s...

Excel: Adding a search box to search one row, search word

Excel: Adding a search box to search one row, search word

Excel: Converting to upper case, cell a1, cells

Excel: Converting to upper case, cell a1, cells, text strings

Excel: Emailing worksheets, magnifying glass, email

Excel: Emailing worksheets, magnifying glass, email

Excel: Excel 2007 - CUSIPs as text, xls extension, cusip numbers

Excel: Excel 2007 - CUSIPs as text, xls extension, cusip numbers, cusip number

Excel: Excel problem, wrong answer, b2

Excel: Excel problem, wrong answer, b2

Excel: Filters in Pivot Charts Excel 2003 vs 2007

Excel: Filters in Pivot Charts Excel 2003 vs 2007

Excel: Transforming text into columns excel 2003, functional trial version, leonel

Excel: Transforming text into columns excel 2003, functional trial version, leonel

Excel: If X then Y style formula, b9

Excel: If X then Y style formula, b9

Excel: menus in Excel, richard florida, good answer

richard florida, good answer, florida usa: Nitin Sorry I am not a VBA expert--I suggest you reask the question of another allexperts person and check the profiles for someone who is profficient in VBA. I m sure you can get a good answer. Sorry i couldn t be of more help. Richard Florida...

Excel: 10ths in Excel, incriment, lunch period

incriment, lunch period, vlookup function: Heidi Something must be going on with your formula or the way you have input the data. I tried the roundup formula with every part of the range and it worked fine on every minute of the range from 1 thru 60, so check your data input or your formatting...

Excel: 7th Order Polynomials in Excel, line of best fit, polynomial equation

line of best fit, polynomial equation, allexperts: The coefficients from the LINEST function can create up to a 16th-degree best fit curve! For 3rd degree, select 4 columns, ctrl+shift+enter this: =LINEST(A1:A100,B1:B100^COLUMN(A:C),TRUE,TRUE) and you ll have your coordinates (assumes y-values in A, x-values...

Excel: Acknowledge a pattern Excel, book2, cell a1

book2, cell a1, a3: If you want cell A1 then A3 then A5, etc, first make a link to the A1 in the first sheet & a link to A3 in the next cell down, so you have something like this in A1:A2: =[Book2]Sheet1!$A$1 =[Book2]Sheet1!$A$3 Then change both = to x= and you ll have...

Excel: Add & Subt. of time, a2, 30 minutes

a2, 30 minutes, fraction: Excel treats time as a fraction - so 12 hours is 0.5, 6 hours 0.25 etc - therefore to take time away from one cell (lets say the value in A2 from the value in A1) =a1-a2 with the cell formatted as time will give the right result. IF the 30 minutes to...

Excel: Application.Workbooks.Open in Excel 2003, wscript shell, cell a1

wscript shell, cell a1, documents and settings: Shane, Here is what I think you want: Sub Tester15() Dim wsh As Object, sID As String Dim DesktopPath As String Set wsh = CreateObject( wscript.shell ) DesktopPath = wsh.SpecialFolders.Item( Desktop ) If Right(DesktopPath, 1) Then DesktopPath...

Excel: Array returning function, array formula, array function

array formula, array function, 99999: =IF(SMALL(IF($B$1:$B$30 0,ROW($A$1:$A$30),99999),ROW())=99999, ,INDEX($A$1:$A$30,SMALL(IF($B$1:$B$30 0,ROW($A$1:$A$30),99999),ROW()),1)) as an array will do it for you - I ve assumed your data is in A1:B30 - enter this formula in any column, cells 1 to...

Excel: Asset Storage, storage agreement, storage firm

storage agreement, storage firm, storage unit: Hi Maximus, Do you require a deposit? Is there a running balance based on the number of months on the storage agreement? How do you calculate the monthly charge? Do you charge by the month or by the size of the storage unit? All of these factors are...

Excel: Assign Value to go directly to sheet 2, target value, sheet1

target value, sheet1: Jun The following will activate sheet2 if a 1 is entered on sheet1. It takes and enter to trigger it. I don t know how to pick up the action until you hit enter. This goes in the code module for sheet1. Private Sub Worksheet_Change(ByVal Target...

Excel: Assigning values and Hiding them, sum function, question thanks

sum function, question thanks, e1: Dustin The count function just counts each cell (unlike the sum function which counts whats in the cell and only for numbers). When you use the countif function it is restricting its count to the parameters that you set in the formula,in this case it will...

Excel: Auto Insert/Delete rows and update last rows, as also set printer area update, time and patience, time constraints

time and patience, time constraints, cell values: Tom Katz If you want to find the the last cell in your table stargin in cell A3 (change the code to use the cell you want to start in) you can just loop down rw = 3 do while len(trim(cells(rw + 1, A ))) 0 rw = rw + 1 Loop I don t know where...

Excel: Auto numbering in excel, good afternoon, stanton

good afternoon, stanton, spreadsheet: Stanton I don t really understand what you are trying to do. When you hit return you want all the PO numbers to appear? By hit return, I guess you mean you enter data in a particular cell. Would that cell be the PO number for a row or some other column...

Excel: Averaging Multiple Cells, wb, cells

wb, cells, vb code: Hi Will, Your code calculates the max of MyRange, but MyRange has not been assigned a range of cells as far as I can see. Without any further changes, I guess you could change this line: Range( J11 ).Value = WorksheetFunction.Max(MyRange) to: ...

Excel: absolute, excel 2002, b1

excel 2002, b1: A formula will display the result of the formula - so if you change A1, anything that references A1 will also change. You can delay the change by turning calculation to manual, but it will update - IF it doesn t need to change, it needs to NOT be a formula...

Excel: add and minus function, run time error 13 type mismatch, run time error

run time error 13 type mismatch, run time error, run time error 13: Without seeing the file I can only be general, but EITHER one of the vlookups will have returned an n/a error or the sumif will have returned an error - there is nothing wrong with the code as such (though the variables should be defined as double or long...

Excel: adding of cells regardins, instructional technologist, cell a1

instructional technologist, cell a1, kindest regards: Hellor Rajendran, On the workbook that you want the results to show and in the cell (i.e., cell-A1), type the equal sign (=), then go to the workbook1 and Cell 1 and click the data you want to be added; then type plus sign (+), then go to workbook 2 and...

Excel: array filter spreadsheet, index column, scenerio

index column, scenerio, snap shot: As the formula gets more complicated, it is sensible to keep the array part and the returned value part seperate - so for example =SMALL(IF($B$1:$B$30 0,IF($A$1:$A$30= a ,ROW($A$1:$A$30),99999),99999),ROW()) would give you any cases greater than zero...

Excel: How do I assign macro codes to multiple checkboxes, value case, cell contents

value case, cell contents, chec: Hi Sam, There s good news and bad news. I ll start with the bad news. I sincerely think having a thousand checkboxes is not a good idea. The risk of your file gaining corruption is simply too big. I would suggest a completely different approach. The...

Excel: Calculating a formula directly to a VBA variable, dear mrs, excel vba

dear mrs, excel vba, a10: Dear Fabricio ... Olá Como está? ((How are you?) Experimente por favor o seguinte código que tem 3 maneiras de atribuir uma fórmula a uma célula. Please try the following code which demonstrates 3 different ways of assigning a formula to a cell Sub...

Excel: Calculating time between multiple cells, target cell, time intervals

target cell, time intervals, ao3: See this tutorial on calculating the difference between two times. The same technique can also be applied to ADDING time intervals: http://www.599cd.com/tips/excel/time-differences/?key=AllExperts If you still have problems after that, let me know...

Excel: Calculating times in excel 2007, decimal integers, chocolate coffee

decimal integers, chocolate coffee, coffee pot: KEVIN With time calcualtions it is usually all about the formatting. Check out this short article and I believe your question will be answered http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=670 Hope this helps Richard Florida US...

Excel: Calulating averages within a standard deviation, array function, standard deviation

array function, standard deviation, spreadsheet: Darrell You could probably do it all in one messy array function; but, I would recommend you start with a step by step method. Somewhere on your spreadsheet you need to calculate the standard deviation, with the STDEV function. In another cell you calculate...

Excel: Cell line color, Sixthsense

Sixthsense: Hi Joe, Just select the Data by holding the shift and arrow key to extend selection and once your selection is over then File Print Area Click Set Print Area. Now File Click Print Preview and adjust the zoom size to adapt to your paper size. ...

Excel: Changing a cell value subject to a worksheet being hidden or not., mil std 461, worksheet changes

mil std 461, worksheet changes, measurement standards: Tom It sounds like you put it in the wrong place. It goes in the code for the workbook, not the specific worksheet, sheet1. This is a workbook event so any time a sheet is activated or deactivated the value in cell A1 on sheet 1 is adjusted depending...

Excel: Changing colors to certain fields., conditional formatting, changing colors

conditional formatting, changing colors, home tab: This is what CONDITIONAL FORMATTING is for. See this tutorial: http://www.599cd.com/tips/excel/conditional-formatting/?key=AllExperts If you re using Excel 2007 or 2010, you can find Conditional Formatting on the Ribbon on the Home tab under the Styles...

Excel: Chart Column Width, Sixthsense

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

Excel: Chart Columns Width, axis labels, gap width

axis labels, gap width, data cells: Thanks for the update - it s always hard when all I have to go on is the text of the question to guess what the problem is - hopefully the link to Peltiertech will be of use for you - there are some excellent charting ideas there - and if you ever need me...

Excel: COUNTIFS, direct email, pivot tables

direct email, pivot tables, pivot table: The word MONTH is a function, so should be left in place. Additionally, SumProduct won t take a column reference - it needs to be specified (so c2:C40000 would be fine for example). =SUMPRODUCT(--(MONTH(Data!C1:C40000)=9),--(year(data!c1:c40000)=2010))...

Excel: Colouring cells, cell d1, conditional formatting

cell d1, conditional formatting, ogilvy: Gail, Here is a macro. I assume you know how to use and run macros since you asked for a macro (a formula won t do what you want). Select the range of cells in column D where you want this to be applied. Sub Color_E_Based_on_D() cells in D selected...

Excel: Column/cell counting issue, jul aug sep, jun jul aug

jul aug sep, jun jul aug, may 1: Hi Ian, This may not be the best method, but it will accomplish the task. It may take a little bit of work initially to set up, but it will work. Using the Countifs function for multiple conditions, you would need to set your formula up to look at the...

Excel: How to Compare Two Columns, c cells, cell a2

c cells, cell a2, grape fruit: Dilshad First I would copy the names of the fruits from one of the sheets to the sheet named table C, and then sort them in Alpha order. Assume you copy them to table C cells A2 to A10. Second I would use this formula in table C to look up the values...

Excel: Conditional Formatting, conditional formatting, excel sheet

conditional formatting, excel sheet, complete training: Not sure which of my replies you have read - but I would use a formula option to handle this - you would need to have them in the OPPOSITE order to the one you have specified and the formula would be along the lines of =ReCertDate-now() 30 as the first...

Excel: Conditional Formatting with Text, conditional formatting, g14

conditional formatting, g14, word text: Not clear what you mean here - if the cell colour is orange, then you can only use this IF it was itself set with conditional formatting - at which point, the same format rule applies (albeit using a formula to reference G14). If the cell contains the word...

Excel: Consolidate data from multiple workbooks, uuu uuu, sotf

uuu uuu, sotf, mm mm: Try this (have the Roolup file active): Sub Combine() Dim Fl As String Dim Main As Worksheet Application.EnableEvents = False ChDrive ThisWorkbook.Path ChDir ThisWorkbook.Path Sheets( CJSOTF ).Activate Set Main = ActiveSheet...

Excel: Convert Excel 2007 column numbers to column letters, column numbers, ogilvy

column numbers, ogilvy, cells: Fabricio, Public Function ColLetter(colNumber As Long) ColLetter = On Error Resume Next ColLetter = Replace(Cells(1, colNumber).Address(0, 0, xlA1, False), 1, ) On Error GoTo 0 End Function Should work fine with any version of excel --...

Excel: Converting text to number excel formula, a1, job

a1, job: Justin If the different cell is, say A1 and the set of words are in A2, you could use in A1 =IF(A2= Great job ,5, ? ) where ? is what you want A1 to be if A2 is anything but Great job . I suspect there is more information about this problem that...

Excel: Converting to time without typing in a colon, target value, excel user

target value, excel user, sheet tab: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If IsNumeric(Target.Value) Then If Target.Value 1200 And Target.Value = 600 Then ...

Excel: Copy all data rows from multiple workbooks, scripting runtime, consolidated data

scripting runtime, consolidated data, menu tools: in Rollup workbook, from within any module in the Visual Basic Editor, choose from the main menu Tools, References and make sure that Microsoft Scripting Runtime is checked. Then run this sub from with the Rollup workbook. Sub copyFromFiles() ...

Excel: Copy and Paste Excel Macro, lattice model, value options

lattice model, value options, binomial: Sub Rearrange() i = 7 For col = 11 To 14 Cells(3, col).Resize(100, 1).SpecialCells(xlCellTypeFormulas).Copy Range( Q10000 ).End(xlUp).Offset(2).PasteSpecial Selection.Offset(, -1).Value = i i = i + 1 Next...

Excel: Count with 2 Criteria- Excel 2007, array formula, countif

array formula, countif, sumif: Jewel, the only reason I would see you getting #N/A in that formula is if there is a #N/A in column B or D. The solution would be to remove the #N/A from the data in Sheet1 That said, Excel 2007 introduced a new set of functions named Sumifs and Countifs....

Excel: Count if Conditions, negative signs, question pool

negative signs, question pool, mitie: Possibly the easiest way of doing this is to not use formulas at all but to use a pivot table - this would summarize the data automatically. IF you need to use formulas, you can use COUNTIFS in Excel 2007 onwards, or for any version, sumproduct can be a...

Excel: Countif function not ignoring hidden values, www google co uk, google co uk

www google co uk, google co uk, google search: Not sure if this is possible without changing the formula or using VBA - to change the formula, this becomes a muliple condition CountIF - which can be done with sumproduct =sumproduct(--(d1:d5= Single ),--(f1:f5= A )) which would display the number...

Excel: Counting non-adjacent cells in a row, array formula, starting a new job

array formula, starting a new job, ak2: Gary, enter this as an array formula =SUM(IF(CHOOSE({1,2,3,4,5,6,7,8,9,10,11,12},I2,M2,Q2,U2,Y2,AC2,AG2,AK2,AO2,AS2,AW2,BA2) 0,1,0)) and it should give you your count. The formula shouldn t be affected by merged cells if the cells shown actually...

Excel: Cummulative Tiered Bonus Structure, gross fees, quarterly bonus

gross fees, quarterly bonus, bonus structure: David I couldn t read your excel sheet you transmitted so I couldn t tell what cells contained the gross fees, but where A1 is the gross fees, the formula would look like this. =IF(A1 =500000,((+A1-500000)*0.15)+12500+25000,0)+IF(AND(A1 250000,A1 500000),((+A1-250000)*0.1)+12500,0)+IF(A1...

Excel: How to calculate a commission in excel, cell b1, gross amount

cell b1, gross amount, a1: John The IF function will return whatever you want it to return. If your sales amount is in A1 and the commission rules are as you described, the following should work (assume it is the commission calculation in cell B1) B1=IF(A1 =500,A1*.05,0) ...

Excel: calculating $ of time per call in excel 2007, example sheets, cell b1

example sheets, cell b1, mm ss: BUT it is the same question, and the same answer - IF you want to know the percentage of calls in the time it is ONE unit of time divided by X units of calls - so 1/488, which is still 1/b1. IF you mean the average call duration expressed as time then the...

Excel: cell contents auto-change...., auto change, cell contents

auto change, cell contents, follow ups: The pictures on here are always hard for me to read - probably my failing eyesight at 50 years of age! That said, everything you want should be possible to achieve - using the DateValue worksheet function you can convert text into a date - so =datevalue(...

Excel: Getting a cell reference from a number, dynamic step, cell c5

dynamic step, cell c5, cell reference: Hi Phil, You have two options when using the Value Function and one is including text in quotation marks while the other is entering the cell reference; unfortunately you cannot do both. So your formula should be: =value( K ) or =value(k128). Good...

Excel: code to search a workbook and remove $ symbol, numberformat, loop through

numberformat, loop through, daunting task: Brandon I thinking building a user interface to handle that would be to bulky and difficult. How about if you have worksheet with two columns of information; one with an existing number format and one with a replacement format? Then you could go through...

Excel: more =IF() in same column?, r2 r3, way over my head

r2 r3, way over my head, secon: =IF(ISERROR(VLOOKUP(K2,Q2:r3,2,FALSE)),I2,VLOOKUP(K2,q2:R3,2,FALSE)) is what you actually need - so the LookUpTable is the full reference of the table (q2:R3) - lookup will look DOWN the list until it finds an exact match, then across to the second column...

Excel: command button, legal programming, instructional technologist

legal programming, instructional technologist, command button: Hi Bruce, The recipients must have a Rule (setting) on their computer that tells them an email was received. There is no legal programming that can be done to notify a recipient of an email unless it is set by the recipient and not the sender. Good...

Excel: compare two columns in excel 2007, instructional technologist, column c

instructional technologist, column c, kindest regards: Hello Xavier, What are you hoping to accomplish with this? What result are you looking for? Please help me understand more about this project. Kindest Regards, Mary A. Lindsey Instructional Technologist eDoc Support LLC www.edocsupport.co...

Excel: conditional subtraction, sum formula, dsum

sum formula, dsum, sumif: Colin I am not really sure what you are trying to do, but, if this is your formula in cell T2 =SUM(U1:X1)-SUMPRODUCT(--($R$2:$R$5= Y )*($Q$2:$Q$5)) it might help you get started. The original sum formula is SUM(U1:X1), obviously you will have to...

Excel: consolidate all worksheets into a years end rollup, sorry bob, l data

sorry bob, l data, cell a1: To add up cell E3 in all 12 tabs, let s say they re named Sheet1 thru Sheet12, then you create a new summary sheet with this formula in cell E3: =SUM(Sheet1:Sheet12!E3) and this can be filled right, down, left, etc. If the tabs are named Jan, Feb,... Dec,...

Excel: converting decimal hours to mintues, punch clock, cell a1

punch clock, cell a1, cell a2: Please refer to my original answer: On a new excel sheet, enter in cell A1 .25 Enter in cell A2 =A1*60 and the result in A2 will be 15. (see your follow-up .25 is 15 minutes. ) Then change A1 to .5. The result in A2 changes to 30. (see your...

Excel: countif and if statements, e mail address, input formulas

e mail address, input formulas, countif: Uma If you send me a copy of what you have done, with some more explanation of what you are trying to do I will give you some formulas to work with. With what I have I can t give you much helpful information on how to set it up. E-mail address is RIRSTRESS@aol.com...

Excel: Always create backup, backup command, backup option

backup command, backup option, fileformat: Art, I would expect your code is making the change. If your code contains the line ActiveWorkbook.CreateBackup = True or similar, then at the end of the macro, put in a line like this ActiveWorkbook.CreateBackup = False Activeworkbook.Save...

Excel: Data Form Button, excel help, current software

excel help, current software, spreadsheet: I m assuming you re not using Excel 2007 or 2010, but are using 2003. The ability to customize toolbars is so vastly different in 2007/2010, and you re training others, I would suggest you train using the current software, and instead of customizing the toolbar...

Excel: Date difference, format cells, instructional technologist

format cells, instructional technologist, accurate response: Hello Kent, You simply need to format the cell with the formula that calculated the difference between the dates to mm:dd. To accomplish this, go to Format Cells / Custom and type mm:dd..the result should be 09:14. Please let me know if this helps....

Excel: Date format in Excel 2003, format cells, locale settings

format cells, locale settings, two different ways: Hello huynh, The way dates are entered and displayed in Excel depends on cell formatting and locale settings. For example, if the locale setting is United States, January 5, 2011 would be displayed as 01/05/2011. But if the locale setting is United Kingdom,...

Excel: Date to reflect the date sheet was saved, www google co uk, google co uk

www google co uk, google co uk, google search: There isn t (as far as I know) an inbuilt function that will do this but you could build a userDefinedFunction in VBA Public Function filedate() filedate = ActiveWorkbook.BuiltinDocumentProperties(12).Value End Function This google search has other...

Excel: Distribution Of Contents, fifth column, permutations

fifth column, permutations, lable: Assuming the column containing these permutations is L, this macro does the job: (the 12 in the code is for column L -- change accordingly) Sub distribute() For i = 1 To Range( L10000 ).End(xlUp).Row For j = 1 To 6 If InStr(Cells(i,...

Excel: Dynamic Folder Lookup, fldr, sitem

fldr, sitem, redim: My code works, I assure you. Here is a modified version combining mine with your file selection method: Sub directlist() Dim fldr As FileDialog Dim FileList() As String Dim DataInRange As Boolean Dim Direct As String Dim Counter...

Excel: date range count function, excel, dates

excel, dates, countif: The DATE function needs all the parameters, try this minor change in the last part of the formula: =COUNTIF( Amendments Or Restmt 2010 !A11:A387, = &DATE(2010,1,31))-COUNTIF( Amendments Or Restmt 2010 !A11:A387, = &DATE(2010,1,1)) If it still doesn t work,...

Excel: Go directly to sheet2, target cells, target address

target cells, target address, cell a1: Hi Jun, Rightclick the tab of sheet1. Paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address $A$1 Then Exit Sub If LCase(Target.Cells(1, 1).Value) = done Then Application.Goto ActiveSheet.Next.Range(...

Excel: Eliminating weekend dates in bar chart, education technologist, weekend dates

education technologist, weekend dates, monday thru friday: Hi Scott, Can you send me a sample of what you are trying to create? I will recreate and provide you with instructions on how I accomplished the task. Kindest Regards, Mary A. Lindsey Education Technologist eDoc Support LLC www.edocsupport.com...

Excel: Error Handling in VBA, random number generation, dear tom

random number generation, dear tom, google: Kuba, maybe something like this: Sub RandomDate() On Error Resume Next Dim x As Date, y As Date, tempDate As Date Dim s As String, l As Long, yr As String Dim mth As String, d As String s = Application.InputBox( Enter starting Date for Random Number...

Excel: Error in pivot table, Sixthsense

Sixthsense: Hi Geeta, Try the below:- Hide error values in a PivotTable report 1. Click the report. 2. On the PivotTable toolbar, click PivotTable, and then click Table Options. 3. Do one or more of the following: Change error display Select...

Excel: EXCEL, k11, two windows

k11, two windows, two areas: you can freeze columns to the left and rows above, but you cannot have a floating range of cells which always appear even if the spreadsheet is being viewed from another area - you COULD have two windows on the worksheet and have them tiled to enable viewing...

Excel: EXCEL VBA, Sixthsense

Sixthsense: Hi Beth, 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: Excel 2000 formula?, excel, dates

excel, dates: If you want to calculate the difference between two days based on the regular calendar, you may use a direct subtraction: =A14-A13 Make sure that the format is numeric, and it will give you the number of days between the two dates. Hope this helps, Migue...

Excel: Excel 2000 help?, irregular payments, interest calculations

irregular payments, interest calculations, richard florida: Richard You can use a regular subtraction formula to calculate the days based on 365. +A14-A13 The days 360 formula was specially created since it is the formula used in some interest calculations, but if using the standard of 365 then the regular...

Excel: Excel 2003, paste values, home tab

paste values, home tab, 1000 names: In D1 put in this formula =if(countif(A:A,A1) 0,B1,na()) in E1 put in this formula =if(countif(A:A,A1) 0,C1,na()) (you can use a different column. Also, you can start in row 2 if you have headers in row 1 =if(countif(A:A,A2) 0,B2,na()) as...

Excel: Excel 2007 formula help, current sheet, b tab

current sheet, b tab, allexperts: This is exactly what VLOOKUP is used for. See this tutorial: http://www.599cd.com/tips/excel/vlookup/?key=AllExperts In your case you want to use the EXACT MATCH option. So your formula for B1 would be: =VLOOKUP(A1,Sheet2!A1:Sheet2!B2,2,FALSE) ...

Excel: Excel 2007 JOINS, int value, cols

int value, cols, aidan: Yes it s possible, though I m not sure it can be done with formulas (at least, not easily using an auto-fillable formula - PROBABLY with the use of OFFSET and perhaps an INT value of the row divided by 5 you would be able to get something working (I m not...

Excel: Excel 2007 JOINS, stop gap, pivot table

stop gap, pivot table, real option: Al Apart from Access the only real option is an Addin for Excel such as the one below. http://www.digdb.com/excel_add_ins/join_merge_tables_lists/ You could also use the pivot table to combine the tables however this is a stop gap measure depending...

Excel: Excel 2007 return data from combo box to chosen cell, sheet1, direct email

sheet1, direct email, combo box: It SOUNDS as though the code on the combo box needs to use the change event to update the ACTIVE CELL on sheet1 - though I m not 100% happy about trusting the user, so I d be inclined to use cells(sheets( Sheet1 ).activecell.row,SpecifiedColumn) to set...

Excel: Excel 2007 worksheet reference, project names, exclamation point

project names, exclamation point, a100: For some reason this question is still in my queue even though I thought I had answered it - so please accept my apologies for the late reply - you are correct in your description of the method - easiest way would be to type (for example) =sum( and...

Excel: Excel, target value, target address

target value, target address, cell a1: Jun The following code should do it. Private Sub Worksheet_Change(ByVal Target As Range) If Application.And(Target.Address = $A$1 , Target.Value = Done ) Then Sheet2.Activate End Sub This is the worksheet_change event code and it must go in...

Excel: Excel, conditional formatting, colums

conditional formatting, colums, richard florida: Matt In your example you give three conditions that you want to be met for the formatting to be applied. Excel will accept 3 criteria for each cell using the conditional formatting. You may have not understood that the 3 conditions limit in conditional...

Excel: Excel 97 Macros, Sixthsense

Sixthsense: Hi Steve, 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: Excel bonus question 2, cell a1, bonus question

cell a1, bonus question, countif: Curt, if the monthly values are in A1:A3, then your total bonus would be calculated by =IF(A1 =35000,0.02*A1)+IF(A2 =35000,0.02*A2)+IF(A3 =35000,0.02*A3)+IF(COUNTIF(A1:A3, =35000 ) =2,IF(A1 =35000,A1*0.02)+IF(A2 =35000,A2*0.02)+IF(A3 =35000,0.02*A3))+IF(COUNTIF(A1:A3,...

Excel: Excel: Change cell value based on another cell, target value, status column

target value, status column, vba: IF it is always Yes when it is accepted, then an IF statement would do it - eg =if(a1= Accepted , Yes, No ) otherwise you could use the change event of the workbook to pick up changes to column A using VBA Private Sub Worksheet_Change(ByVal Target...

Excel: Excel Code Stopped working., elseif instr, bcheck

elseif instr, bcheck, excel code: I m GUESSING but possibly some formatting has taken place (or other action) that has set the end of the worksheet to the REAL end - where do you get to if you press Ctrl and End at the same time - also happy to look at a sample file to see if that sheds any...

Excel: Excel doubt-Reg., richard florida, florida usa

richard florida, florida usa, parameters: Hari I am not sure I am understanding the question but based on my understanding, you can use a formula like this. =IF(AND(A2 0,A2 6),0.06)+IF(AND(A2 5,A2 13),0.02) This formula says if A2 is greter than 0 days but less than 6 days then put 2% in...

Excel: Excel IF formula, vlookup function, c7

vlookup function, c7, multipliers: =IF(C7 1,0,IF(C7 4,C7*13.5,IF(C7 10,C7*13,IF(C7 20,C7*125,IF(C7 40,C7*12,C7*11.5))))) will do it for you - as the FIRST test that is true will be actioned you don t need to worry about greater than part of the text - as you can see, I ve ordered it in ascending...

Excel: Excel formula help please, excel formula, free tutorials

excel formula, free tutorials: IF lets you check between two conditions. VLOOKUP lets you look up a value in a list. SUM adds up a bunch of numbers. COUNTIF will count the number of items IF a condition is met. I ve got FREE tutorials explaining ALL of these functions on my...

Excel: Excel formulas, ms office 2000, cell entries

ms office 2000, cell entries, excel formula: Sorry for the delay -- can you send a file for me to look at and I ll put the formulas you request in -- please repeat the above description of what you want done, and indicate where on the sheet(s) you need this calculation. send to bobumlas@hotmail.com...

Excel: Excel function, excel function, vba

excel function, vba, aidan: I ve never heard of this function - I m GUESSING it s probably a user defined function written in VBA? I would need a fuller description of how this function works to come up with an alternative - I m happy to look at a sample file if that helps - my email...

Excel: Excel Hyperlink, www google co uk, adobe pdf reader

www google co uk, adobe pdf reader, excel hyperlink: This problem relates to the operating system and hyperlinks - I would GUESS there is some issue with Adobe Reader (perhaps taking longer to respond for some reason) but I m not familiar with that program. A google search indicates that others may be having...

Excel: Excel Lookup question, excel lookup, vlookup

excel lookup, vlookup, d6: I would do this using vlookup as the easiest method =VLOOKUP(F11-1,C1:D6,2,TRUE) C1 contains the formula =DATE(YEAR(NOW())-1,11,15) C2 contains =EDATE(C1,3) And this is filled down to C6 D1 contains =C2 and this is also filled down F11 in...

Excel: Excel lookup based on 2 criteria one being is the date listed equal to or less than today, simplicity sake, excel lookup

simplicity sake, excel lookup, array type: Example: you want to count the # of rows 2:9 in which col B is Medic and col A is today or earlier. Enter the formula =SUM(IF(A2:A9 =TODAY(),IF(B2:B9= Medic ,1,0))) as an array (type in formula, then instead of Enter key, hit Ctrl-Shift-Enter). ...

Excel: Excel Macro, keyboard shortcut, cell position

keyboard shortcut, cell position, word position: Sub Macro1() Macro1 Macro Keyboard Shortcut: Ctrl+w Range( A1 ).Select set kw=cells.find( TEAM TOTALS ,lookat:=xlwhole) kw.offset(1).entirerow.resize(7).copy Sheets( Summary ).Select ActiveSheet.Paste Sheets( Raw Data...

Excel: Excel Macro, excel macro, cheers

excel macro, cheers, cells: Hi Ahmed How are you? Please have a look at the following code: Sub MorethanOne() Dim R As Range, T As String Set R = ActiveSheet.Range( A1 ) T = For I = 1 To 3 T = T + + Str(I) Next I R.Cells(1, 1) = T End Sub Cheers Adelaid...

Excel: Excel question, invoice number, open range

invoice number, open range, increment: Sure, have a look at he Workbook_BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then User does a Save-as, maybe increment invoice number here Else User does a normal...

Excel: Excel "Read only", copy of a copy, macro security

copy of a copy, macro security, medium security: To be honest, not easily - vba could stop the saving, BUT if users have macro security set high then the macros won t run, and a bug in excel can mean that medium security becomes high by accident. Although microsoft introduced Shared workbooks, this is...

Excel: Excel Survey Analysis, twitter, pivot tables

twitter, pivot tables, survey analysis: Joe You could input the information (depending on the layout of the form and whether it is a paper or online form) and get excel to give you good range of analysis using pivot tables. The only problem is time for inputting if not in a file. I could certainly...

Excel: Excel Survey Analysis, worst case scenario, twitter

worst case scenario, twitter, survey analysis: * Sure it s possible to put data into Excel to get some counts, percentages, and other useful calculations that you may find gives some meaning to the survey results. but I ve no idea what format your data is in currently? Can you export it from...

Excel: Excel IF statement, max score, workouts

max score, workouts, spreadsheet: Michael this formula is for 1 second, i noticed your example is 10 seconds. Get back to me if it is wrong. If you don t go over 59 seconds this formula will work =IF(A1= 0:18:00 ,100,100-SECOND(A1)) if it goes over 59 seconds get back to me and...

Excel: Excel Transpose Macro, excel, vba

excel, vba, copy: Transpose it is not going to work in your case, as that takes the full range and changes rows for columns, and you need a different layout. You can try this code, it should work with the specifications that you stated in your post: Sub PrepareData() ...

Excel: Excel - VBA, excel vba, irow

excel vba, irow, text comparison: Likely you re doing a text comparison. You Dimmed dtDate as String and should have been as Dagte or as Long. Try changing this: If (Me.txtLastPM.Value) dtDate Then to this: If (Me.txtLastPM.Value)*1 dtDate*1 Then OR, try it with changing the Dim...

Excel: Excel - VBA, excel vba, irow

excel vba, irow, spreadsheet database: Tim If you always get the message, then the following must always be true: Me.txtLastPM.Value dtDate It might have something to do with dtDate being declared as a String. I am not sure exactly what happens when you assign a Date to a string variable....

Excel: Excel VBA, excel vba, value destination

excel vba, value destination, vb program: I d be inclined to have a list of the connections and locations stored on the spreadsheet Sub test() test Macro dim Looper as long for looper= 1 to 100 With ActiveSheet.QueryTables.Add(Connection:= _ sheets( Reference...

Excel: Excel VBA Array Sum Help, vba array, excel vba

vba array, excel vba, sum function: AQUA, this code should execute instantaneously. Looping through an array is blindingly fast - so I doubt any other approach would be more efficient. Just because you have to loop doesn t mean it isn t fast. But there is no built in function that will...

Excel: Excel VBA - combine list of columns into on list, state zip code, vba macro

state zip code, vba macro, zips: Yes, very do-able – it’s a nested for next loop basically Sub writer() Dim N As Long, O As Long, counter As Long counter = 2 For N = 2 To 4 N is being used for the number of ROWS of data in the original data set For O = 5 To 256 O...

Excel: Excel VBA Help, ubound, dynamic range

ubound, dynamic range, ogilvy: Aqua This worked for me as an illustration of what I understand you to want. Sub abc() Dim strArr As Variant Dim rng() As Range Dim lb As Long, ub As Long Dim sh As Worksheet Set sh = ActiveSheet strArr = Array( Str1 , Str2 , Str3 ) lb = LBound(strArr)...

Excel: Excel VBA Increment row number & Loop code, excel vba, selection formula

excel vba, selection formula, vehicle c: So if you run this 100 times you want the formula to be B113 and C113? If that s the case, then you need a place to store the starting place, row 13, to increment. Maybe something like this: In the very beginning of your macro: Sub OfficeData() StartRow...

Excel: Excel VBA Programm Issue, excel vba, sheet tabs

excel vba, sheet tabs, slashes: I m not sure I understand the issue -- sheet tabs can t contain slashes, hence the dashes are needed -- but how it could have worked in 2010 is unclear -- slashes still didn t work then either! Feel free to send a sample wb to me at bobumlas@hotmail.com,...

Excel: Excel vba: Combine 2 worksheets together with only 1 header, script errors, g thanks

script errors, g thanks, hdr: Doug Did you change the name consolidated which I used in the code to instr combined which is apparently the real name? That should get it working, but, it is set up to grab all the sheets, that do not have that name. If it is to be automated,...

Excel: Excel2003-VBA, available memory, excel sheet

available memory, excel sheet, question thanks: Excel 2003 help literally states: Sheets in a workbook: Limited by available memory (default is 3 sheets). Your description is confusing to me. First you say you have one record*set* per sheet, but after that you say each record is a single sheet, which...

Excel: Excel2003-vba, xl 1, copy source

xl 1, copy source, sheet1: Hi again Srikar, Here is the modified code: _______________________________ Sub WScopy() Dim SourceWB As Workbook the source workbook Dim SWBfile As Variant the file name of the source workbook SWBfile = Application.GetOpenFilename(...

Excel: Excel2003-vba, irow, input sheet

irow, input sheet, q1: Hi again Srikar, Here is the modified code. I hope this does what you want. I didn t understand if you wanted me to do something with Defer Report , but if so please clarify and I will add it. Sub MakeReport() Dim InputWS As Worksheet ...

Excel: eliminate weekends in bar chart, yahoo mail, axis type

yahoo mail, axis type, kindred spirit: * I am not sure why Excel would do something this seemingly stupid (although I hesitate saying that cuz of course all computers only do what they are told, is what people tell me!) but in Excel 2007, I right-click on the x-axis to format it. then...

Excel: empty a single cell once the value has been recorded elsewhere, Sixthsense

Sixthsense: Hi Alan, You can t able to do it using the excel functions. Excel Functions never force other cell get changed when there is no functions are used. You should go for VBA (Macro) solution for achieving the above. As I stated in my profile, I am not proficient...

Excel: excel 2007, z thing

z thing: * It s not clear exactly what your question is. Are you talking about Sorting data? it s a formatting issue. if it s saying 189, 17 then the nu9mbers are stored as text, not numbers. I mean if you only have 17, then that s 17 - not 170. if you successfully...

Excel: excel, richard florida, florida usa

richard florida, florida usa, different things: Deb You can use a formula like this. =IF(Sheet2!A1= yes ,1,0)+IF(Sheet3!A1= yes ,1,0))+IF(Sheet4!A1= yes ,1,0)+IF(Sheet5!A1= yes ,1,0) etc, etc.for as many cells and sheets that you need to count. Hope this helps Richard Florida USA ...

Excel: excel, form c6, blank cell

form c6, blank cell, d14: Andy There is no problem putting it there. The offset function will be almost the same =OFFSET(Sheet2!C6,MATCH(1,Sheet2!g7:g14),0) So, column G will show which of the rows in column A is the one of interest. We get a match on that row, and offset...

Excel: excel, cell reference, sheet1

cell reference, sheet1, richard florida: Gina Of course if you delete the data without deleting the cell in the main data list an error won t appear. If you delete the cell in the main, an error will appear because the cell reference is no longer valid. If you are eliminating the cell in the...

Excel: excel, reminder message, excel sheet

reminder message, excel sheet, openning: apa I don t believe you can do that without opening the file. If you could, you have a date column. Aren t there multiple dates in the column? You can use an OnTime method in VBA, and that will continue to run after the file is closed and at the designated...

Excel: excel-finding totals of monthly rate, table a2, b15

table a2, b15, a15: I don t quite follow, and the image is not really helpful -- pls send a sample wb to me at bobumlas@hotmail.com, use subject of AllexpertsQ and repeat what you want to do. If you could also supply the correct answers in col D, I can come up with the formula...

Excel: excel - formatting, nature of the beast, patent number

nature of the beast, patent number, excel spreadsheet: Unfortunately, it looks quite buggy to me. Using that font, when I reduce the font size, the row height shrinks as well, hiding the last galvanized . Clearly, that shouldn t happen. You would do better to use a text box if that s possible, or change fonts...

Excel: excel formula for attendence, Sixthsense

Sixthsense: Hi Neeraj, 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=0B2TMxNyEPQ2rNDJhYmI4ZGYtOTBlZi00ZWIyLTk5MmEtNGNiMTMyM2RiYTVk&hl=en click Download...

Excel: excel help, Sixthsense

Sixthsense: Hi Ajit, This was discussed several times and you can find the solution in the below link http://www.eggheadcafe.com/software/aspnet/35179181/combining-text-from-several-cells-into-1-cell.aspx Don t hesitate to ask again, if your requirement is not...

Excel: excel spreadsheet, nightly backup, backup folder

nightly backup, backup folder, excel spreadsheet: Nope. Sorry. Once Excel overwrites that file, it s pretty much gone. It never hits the Recycle Bin, so there s nowhere to get it back FROM. This is why you should have a NIGHTLY backup running for all of your important data. Even a simple little program...

Excel: excel vba, excel vba, csv files

excel vba, csv files, best guess: Kushal, that code should have been Private Sub CommandButton2_Click() Dim r as Range, rw as Long rw = me.Cells(me.rows.count, B ).End(xlup).Row set r = me.Range( A & activecell.row & :A & rw) set r = r.Resize(,6) r.Sort Key1:=me.Range( B ...

Excel: excel vba - combine columns into one list, state zip code, vba macro

state zip code, vba macro, zips: try this: Sub Rearrange() n = 2 Set this = ActiveSheet Set answer = Worksheets.Add For i = 2 To this.Range( A60000 ).End(xlUp).Row For j = this.Cells(i, 5).Column To this.Cells(i, 256).End(xlToLeft).Column Cells(n,...

Excel: expired date in excell, Sixthsense

Sixthsense: Hi Thomas, It is pretty hard to explain and give solution without seeing your data and the placement of your data. So if possible send your file to the below mail id so that I can do work out and send it to you. Email Id: Ms.Exl.Expert@gmail.com ...

Excel: extract the data without opening excel file and paste it in new file, blank row, target

blank row, target, aidan: The only macros that I can think of would begin by opening the file so that they could read the data - it MAY be possible for some other expert to think of a way round this, but I m afraid I cannot - the basic process for a macro is easy enough open the...

Excel: Filter Master Sheet to Additional Sheets, southwest airlines, christmas travel

southwest airlines, christmas travel, data validation: In a new Excel workbook, name the sheets Expenses, Travel, and Food. On Expenses, enter Category in B1, Date in C1, and Amount in D1. Format the Date and Amount columns as Date & Currency. Fill B2:B6 with category entries, each cell being either ...

Excel: Find number in Data Entry Sheet, cell reference, data entries

cell reference, data entries, autofilter: EASIEST way would be to use a blank column and use the COUNTIF worksheet function =countif(SmallSpreadsheet,YourNumber) is the syntax, where SmallSpreadsheet relates to the range of cells that contain the number you are looking for and yourNumber the...

Excel: Find number in Data Entry Sheet, Vlookup by Sixthsense

Vlookup by Sixthsense: Hi Khurram, Use Vlookup and have a look in the below links to have better understanding about how to use vlookup. http://www.experiglot.com/2007/11/19/how-to-use-vlookup-in-excel-a-simple-tutorial-part-i/ http://www.msofficeworld.net/2009/08/04/how-to-use-vlookup-vlookup-example/...

Excel: Finding A Date, time component, finding a date

time component, finding a date, search cell: Hi Will, Your code looks perfect to me, so I tried it and it worked perfectly for me. Here are two things that might be causing it not to work for you: 1. Firstly, you should Dim HDD1 As Date. Otherwise if the cell you are searching for is formatted...

Excel: Flagging dates, spreadsheet column, excel spreadsheet

spreadsheet column, excel spreadsheet, different color: Barb Use Conditional Formatting. Create a rule for the area of interest, where the conditional format is based on a formula and the formula is =A1 TODAY() assuming your data is in column A. Set the format to be the different color of your choice....

Excel: Format Year Wise, dd mm yy, column section

dd mm yy, column section, instructional technologist: Hi Omeir, Based on my understanding of what you are hoping to accomplish: Format the cell (column) in the Pivot Table to show exactly as you want it by changing the format to yyyy and see if this works for you. Kindest Regards, Mary A. Lindsey Instructional...

Excel: Formula, allexperts, time differences

allexperts, time differences, a1: Sounds like a job for the IF function and some date subtraction. You can subtract 5 days from A1 by saying: =A1-5 You can have Excel make a decision like this: =IF(A1 10,A1-5,A1+5) This says, if A1 is greater than 10, then set the current cell...

Excel: Formula question, Excel, vlookup

Excel, vlookup: You can use VLOOKUP for this. To set it up, create a table like this in Sheet2, in range A1:B7: 0 0 1 14 1.01 13.5 4 13 10 12.5 20 12 40 11.5 Then you can use a formula like this: =C7*VLOOKUP(C7,Sheet2!A1:B7,2) Hope this helps, Migue...

Excel: Freezing Panes, vertical scrollbar, column c

vertical scrollbar, column c, happy new years: You can t do exactly what you re looking to do; after all, how would row 3 look if you re looking at A3 then B3 then C77 then D77...?? You might try splitting rather than freezing panes. Click in cell C7, then click on the Split icon near the freeze panes....

Excel: IF Function, mike lisa, boy henry

mike lisa, boy henry, girls name: Maj, How about Pat - boy or girl? There is nothing distinctive in a boy s name or a girls name. You would need an exhaustive list on another page that establishes what result you want for each name. For illustrative purposes, assume on sheet2 you...

Excel: 7th Order Polynomials in Excel, level statistics, eyebrow

Excel: 7th Order Polynomials in Excel, level statistics, eyebrow, pawn

Excel: How to calculate a commission in excel, online resource, commission reports

Excel: How to calculate a commission in excel, online resource, commission reports

Excel: converting decimal hours to mintues, 15 minutes

Excel: converting decimal hours to mintues, 15 minutes

Excel: Eliminating weekend dates in bar chart, bar graphs, weekend dates

Excel: Eliminating weekend dates in bar chart, bar graphs, weekend dates

Excel: VBA - Autofit merged cells causing protection issue

Excel: VBA - Autofit merged cells causing protection issue

Excel: formula question, format cells, zero values

format cells, zero values, checkbox: You can format cells as General;General; or you can mark the sheet to not show zero values (method depends on the version of Excel) -- in 2007, use Office button, excel options, Advanced, then about 60% of the way down is a checkbox for show zero values......

Excel: Hyperlink, Hyperlink Solution By Sixthsense

Hyperlink Solution By Sixthsense: Hi Imad, Upto my knowledge it is not possible in the inbuilt functions of excel and you should go for User Defined Function (UDF) for achieving this. Have a look in the below link for more details about the UDF. http://www.ozgrid.com/forum/showthread.php?t=10278&page=1...

Excel: Hyperlink, hyper link, quick test

hyper link, quick test, company names: So let me get this straight... you ve got a column of hyperlinks that are formatted as clickable hyperlinks, and you just want to break it down and get the URL address WITHOUT it being a clickable hyperlink? A quick test in Excel shows me that if you have...

Excel: help with lookup from a range, Sixthsense

Sixthsense: Hi John, I couldn t able to avoid the helper column and make it as single formula. So I have given solution with the combination of two formula s. I have created a sample file for you, Click or Copy and paste the below link in web browser to download...

Excel: hyperlink, Sixthsense

Sixthsense: Hi Travis, Thanks for your followup question, but in fact i don t know anything about VBA (Macro) and the same is mentioned in my profile also. You might try one of the other Excel experts that claim expertise in Excel VBA such as Bob Umlas, Tom Oligvy,...

Excel: Ignore hidden rows, excel sheet, kindest regards

excel sheet, kindest regards, therow: Hello David, This is the best I have come up with since I am not familiar with the spreadsheet you are working with. I hope this helps. Code: Private Sub UserForm_Activate() TheRow = Application.InputBox(prompt:= Please select the record number that...

Excel: Ignors hidden values with help of function or formula, best regard, hidden values

best regard, hidden values, inv: It doesn t come across very clearly in the typed format I can see here, but I m not sure you are going to be able to do it - it SOUNDS as though you want to see a different result in the cell depending upon whether the data is filtered or not, and there is...

Excel: INDEX linking to a sharepoint hosted .xls taking URL 'parts' from other cells., formula index, test xls

formula index, test xls, cell reference: Andy, the good news first: the Indirect function will build a cell reference such as you describe A1: Test.xls A2: Summary!A1:G50 =Index(Indirect( [ &A1& ] &A2),17,3) should work if Test.xls is open in the same instance of Excel as the workbook...

Excel: Insert 000 before digitas, best regard, digitas

best regard, digitas, apostrophe: 4 basic ways: 1 - Format the cell as text, THEN enter the value 2 - first type a leading apostrophe then the number. the apostrophe won t display. Like 0002553987 3 - Format the cell as 0000000000, but this will only work with 7-digit #s to add the 3 0...

Excel: Insert rows in excel and increment cell value, blank rows, excel spread sheet

blank rows, excel spread sheet, column c: Carol This is a pretty complicated task; it is also an old questions, so maybe you already have it taken care of; but, the following code will do what you want for macro 1. There is no error checking and there is a lot that can go wrong. If you enter...

Excel: identifying month change in excel, ogilvy, a3

ogilvy, a3, a2: Jose, Your request lacks definition on how you want to implement your requirement - so I will give you and example of one type of approach you could use. I will show your first two sample values are in cells A2 and A3. Then in B3 put in the example formula...

Excel: index formula errors, index formulas, excel asp

index formulas, excel asp, absolute references: Perhaps: http://www.amazon.com/gp/reader/0470475366/ref=si_aps_sup?p=random&ie=UTF8&qid=1294671618#reader_0470475366 or in a few months, my new book Excel outside the box should be out. Meanwhile, I also recommend my current book This Isn t Excel,...

Excel: To insert e-nmail address from outlook contacts, e mail addresses, excel vba

e mail addresses, excel vba, outlook address book: Your code has the SAME destination for the phone number and email address, and as phone number is second it is overwriting the email address - try destRng(1, 2).Value = .List(.ListIndex, 1) destRng(2, 2).Value = .List(.ListIndex, 2) although you may...

Excel: Keeping leading 0's in excel, variable accounts, pranay

variable accounts, pranay, xml file: I ve not worked with XML data, but possibly the easiest fix is to have the cells formatted as a custom number - I presume the accounts are a fixed length with zeros to pad out, so if it was a length of 10 the format would be 0000000000 Under custom format...

Excel: Lines, cell settings, header row

cell settings, header row, solid gray: Joe the only option is to highlight all the cells you want the border around and manually insert the border using the border toolbar. When you import from outside excel it places these borders automatically. (I think this has changed in 2010). Richar...

Excel: to learn the formulas, last question, text string

last question, text string, cells: My suggestion is to create a blank series of cells as wide as the original data, and to apply the formulas to these - so if you had data in A to H, you could use I to P with formulas being =proper(a2) being the formula in I2 =Proper(b2) being the formula...

Excel: linking multiple cells to a master spreadsheet, master spreadsheet, cell a1

master spreadsheet, cell a1, workbook sheets: Katy Yes it is possible to link the cells and it is fairly easy to do. A linking formula looks like this. Assume you are in cell A1 of sheet1 and you want to link the A1 cells from the other sheets in your workbook, sheets 2 thru 5. =Sheet2!A1+Sheet3!A1+Sheet4!A1+Sheet5!A1...

Excel: To make a list of sheet names, count cells, data validation

count cells, data validation, sheet names: I gave you more than one version of code which isn t helping I suspect, but basically you need an if...end if test to STOP it processing the rdbmailoutlook situation if sheets(x).name RDBMailOutlook then the bit that stores the value goes here ...

Excel: MACRO, excel, macro

excel, macro: This macro will change the data from Sheet1 in the new format in Sheet2: Sub PrepareData() Dim rngOrg As Range, rngDest As Range Dim i, j, k Set rngOrg = ThisWorkbook.Worksheets( Sheet1 ).Range( A1 ).CurrentRegion Set rngDest = ThisWorkbook.Worksheets(...

Excel: Macro: Copy to a different worksheet, cells, jobs

cells, jobs, worksheets: Hi Esther, Well, you did say you wanted to copy them, not to cut them... The following procedure cuts the items with b in column D to the other worksheet: Sub ArchiveJobs() Dim oCell As Range With Worksheets( ongoing_jobs ).ListObjects(1)...

Excel: Macro to create a new column based on values from other column., position text, excel sheet

position text, excel sheet, plan description: Open a new Excel sheet. In cell I2, enter XHP in K2, enter 335.15 in P2, enter =IF(K2=300, BDQ ,I2) P2 displays XHP since that s the value in I2. Now change K2 to 300 P2 now displays BDQ , because your over-ride logic says to always...

Excel: Macro that opens excel files, student workbook, inner loop

student workbook, inner loop, message box: Gail It is definitely possible; but, there a quite a few options you need to address. The following code is similar to something I use to extract information from student files and copy to a master workbook. It may be more complicated than you need, because...

Excel: Macros, vba tools, example macros

vba tools, example macros, hidden folder: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox Saving disabled for this workbook End Sub Sub general() Dim mybk As Workbook Set mybk = Workbooks( Book11 ) mybk.Sheets( sheet1 ).Range( D1 ).Value...

Excel: Macros not working when sheet is protected, row selection, sht

row selection, sht, constants: Emily ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowDeletingRows:=True I don t do much protecting and unprotecting; but, it is easy enough to record doing it. After you record a macro, you can cut and paste...

Excel: Modification in VBA Code, automatic email, code question

automatic email, code question, header cell: Abdul jaleel, I put in code to pick up the cc address - but I see that I overlooked changing one attribute. The code I put in/added is below: ccmailAddress = Application.WorksheetFunction. _ VLookup(Cws.Cells(Rnum, 1).Value,...

Excel: Multiple SUMPRODUCT with wildcard, sumproduct, excel spreadsheet

sumproduct, excel spreadsheet, asterix: I tested my little bit of the formula and it did work - search is NOT case sensitive, whereas FIND is - hence the use of SEARCH. The search would return the N/A error if there isn t a match, so the not(iserror( method is my preferred one. Your formula has...

Excel: macro question for working with an external data source, external data source, windows based software

external data source, windows based software, external windows: Hi Bradly, Depends. Some programs expose their object model to the outside world so you can address them from any programming language (like VBA). Open Excel, open the VBA Editor and click Tools, References. Then click the browse button and navigate to...