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

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

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

Excel: Conditional Formatting, conditional formatting, bottom choice

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

Excel: Conditional Formatting, conditional formatting, hilow

conditional formatting, hilow, many thanks: Highlight the cells with the 1-1500 numbers in them, let s assume the range is H1:R144. Assuming the first cell highlight is H1, open the conditional formatting and apply these settings: Condition1: Formula Is: =ISNUMBER(MATCH(H1, HiLow, 0)) Format......

Excel: Consolidating multiple user's accounts in a single Master File, Sixthsense Excel

Sixthsense Excel: Hi Jay, 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: Copy & Auto Update, jcw, pivot tables

jcw, pivot tables, adnan: Sub Answer() On Error Resume Next Sheets( JCW ).UsedRange.Offset(1).Resize(Sheets( JCW ).UsedRange.Rows.Count - 1).Copy Sheets( Table ).Range( A1 ).End(xlDown).Offset(1).PasteSpecial For Each sh In Sheets sh.PivotTables(1).PivotCache.Refresh...

Excel: Copy data based on selection from data validation list, data validation, state capitals

data validation, state capitals, state birds: Sub PostToSheet2() Dim n As Integer On Error Resume Next n = 0 n = Application.Match(Range( B1 ).Value, Sheets( Sheet2 ).Range( A:A ), 0) If n 0 Then With Sheets( Sheet2 ) .Cells(n, 2).Value = Range( I5 ).Value...

Excel: copy function, vlookup, blank value

vlookup, blank value, initial questions: I m not clear what it is you want to do - is there a choice of answers, if so a vlookup would be best - use the IF statement to handle the blank value, then the vlookup to lookup the selected choice and return the appropriate value. MAY help to see a sample...

Excel: Copy info from one cell to another cell, cell a1, birth month

cell a1, birth month, spread sheet: Richard, standard excel format so for illustration, I will assume that cell A1 contains a date serial number representing a Date stored as an excel Date (date serial number) =text(A1, mmm ) would produce Jul or =text(A1, mmmm ) would...

Excel: Copy rows based on date to new sheet, target value, target address

target value, target address, cell a2: Assuming FileA is called FileA.xlsx, then right-click the sheet tab of FileB, sheet1, select View Code. Paste this in: Private Sub Worksheet_Change(ByVal Target As Range) Dim OtherWS As Worksheet, n As Integer If Target.Address = $A$2 Then ...

Excel: Copy text in cells from one sheet to another, array formula, ogilvy

array formula, ogilvy, a3: Bill, apparently you don t understand what I am suggesting or I wasn t clear in my description. If you want to send me a sample workbook with just the two sheets and a little bit of data in the daily sheet in column A, I will put in the formula and demonstrate...

Excel: Copy XLS Filenames, Sheetnames, and Data in Each Cell to a Single XLS file, version ms, xls file

version ms, xls file, macro code: Uche, it has the following data in cells B3:B7 with blue color fill which format I would like to retain: Filename, Sheetname, Item A, Item B, Item C that statement makes no sense to me since you picture shows those entries (in the template) all being...

Excel: Count criteria within date range, Sixthsense Excel

Sixthsense Excel: Hi Jessica, Try the below formula. =SUMPRODUCT((Detail!K1:K100 =DATE(2011,1,1))*(Detail!K1:K100 =DATE(2011,1,31))) Have a look in the below link to get a better understanding about SUMPRODUCT. http://www.xldynamic.com/source/xld.SUMPRODUCT.html#classic...

Excel: Count criteria, sumproduct, ogilvy

sumproduct, ogilvy, signs: Jessica, If you are using Excel 2007 or later, you can use the new countifs formula which allows you to specify multiple criteria. if you are using Excel 2003 or earlier, then you will need to use sumproduct. It sounds like you know how to do that,...

Excel: Counting # of days formula, second date, days of the week

second date, days of the week, notations: The formula for start date in A2, end date in B2 and MWF notations in C2 would be: =SUM(INT(((WEEKDAY(A2 - {1,2,3,4,5,6,7}) + B2 - A2) * (--ISNUMBER(MATCH({ *Su* , *M* , *Tu* , *W* , *Th* , *F* , *Sa* }, C2, 0)))) / 7)) Here s a link to a page that...

Excel: Counting Text, richard florida, florida usa

richard florida, florida usa, using wildcards: Michael I believe you can do what you want with a couple of countif formulas using wildcards, for example =COUNTIF(B1:V100, *ARH* ) and =COUNTIF(B1:V100, *ARHH* ) The first count--The first formula will count all of the instances of ARH, including...

Excel: Criterion Cell as Partial of Target Cell, target cell, target values

target cell, target values, d green: Kyle D. Green My email address is kyledg at gmail dot com. Looks like you are half way there. This should work: =COUNTIFS($I:$I, * &A2& * ,$J:$J, * &C1& * ) A2 and C1 seem like odd targets - so make sure they hold the values you need to check...

Excel: data validation list how to view hidden sheets, data validation, pieterse

data validation, pieterse, resnik: Hi Jayden Senna, The best method for achieving both the queries is that going for Macro (VBA) Solution. 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...

Excel: Date from Text to date format, chip pearson, manoj

chip pearson, manoj, thanks in advance: Manoj, assume your dates are in column A beginning in cell A1. Then in B1 put in the formula =IF(LEN(A1)=5,DATE(( 20 &RIGHT(A1,2))*1,LEFT(A1,1)*1,MID(A1,2,2)*1),IF(LEN(A1)=6,DATE(( 20 &RIGHT(A1,2))*1,LEFT(A1,2)*1,MID(A1,3,2)*1), Unknown ) now format...

Excel: Day notation, monday tuesday wednesday, cell a1

monday tuesday wednesday, cell a1, format cells: Bas, the Month function only takes one argument. so it should be =IF(ISNUMBER(A1);CHOOSE(MONTH(A1); January ; February ; March ; April ; May ; June ; July ; August ; September ; October ; November ; December ); ) -- Regards, Tom Ogilvy ...

Excel: delete a inserted picture by micro, left pic, hello tom

left pic, hello tom, documents and settings: Jonathan, you can protect the project. IN the visual basic editor, with your workbook as the Active Project (selected in the project manager), go to Tools= VBAProject Properties and in the resulting dialog, choose the protect tab. Now VBAProject is...

Excel: Disable-ing instantiating Excel, open macro, ctrl keys

open macro, ctrl keys, correct time: In your Excel, open the VBA editor and find your personal project, the project that loads automatically each time Excel is started. Add an empty module and add this function: ==================== http://excel.tips.net/T009451_Finding_Other_Instances_of_Excel_in_a_Macro.html...

Excel: How to edit data returned from VLOOKUP and dropdown, vlookup formula, desk sergeant

vlookup formula, desk sergeant, column c: Jason I suggest that you put your lookup formula in another column (not column C) and then use copy/paste special/values to copy the result to column C. Now that you have only a value in column C you can edit it without disturbing the vlookup formula....

Excel: Embedded IF Statement in Excel 2003, q3, max number

q3, max number, many thanks: Combine all the conditions which yield 1, all that yield 2, all that yield 3, & default none to NoPriority, and you come up with: =IF(OR(Q3=0,Q3 48,AND(Q3 24,P3=TRUE),AND(Q3 24,H3=14)), 1 ,IF(OR(AND(Q3 12,P3=TRUE,Q3 =24),AND(Q3 12,H3=14,Q3 =24)), 2 ,IF(OR(AND(Q3...

Excel: embedded if statement in excel, question pool, timely fashion

question pool, timely fashion, margie: Margie, You didn t ask me this question - I found it in the question pool (that is why it may not be answered in a timely fashion). I assume you want this formula in another cell (not E34) =if(E34= , ,if(isnumber(E34),if(E34 =1,1, 1 ), )) that...

Excel: Excel 2007 moving contents of cells, bottom of the list, list of names

bottom of the list, list of names, ogilvy: Brian, It can be done with a macro. If you want to send me a sample file, I can write a macro to demonstrate. the sample file should include you list of names (you can make a dummy workbook - I don t need to see anything proprietary). send it to...

Excel: Excel 2010 Formula Question, e mail address, max number

e mail address, max number, additional detail: Yes, that is a very complex need , more like a project you hire someone for. My first blush thought though is you should flatten your design so you aren t inserting/deleting rows all the time, which WILL break most complex formulas. If you set your design...

Excel: Excel, question pool, addins

question pool, addins, ogilvy: amir, you didn t ask me this question - I found it in the question pool (thus it is rather old, but you still might need an answer). Unfortunately, the answer is not positive. Excel has a limitation of storing only 15 digits of precision. This is the...

Excel: Excel, Sixthsense Excel

Sixthsense Excel: Hi Dee, This can be done using conditional Formatting. Have a look in the below link to know about conditional Formatting Using Formula. http://www.cpearson.com/excel/cformatting.htm http://www.techrepublic.com/blog/msoffice/use-a-formula-to-trigger-excels-conditional-formatting-feature/831...

Excel: excel, richard florida, florida usa

richard florida, florida usa, cursor: Peter With excel open, click on the top left office button then click on new then click on blank workbook. this opens a new workbook, To add a worksheet place the cursor on the tabs at the bottom of the workbook then right click and select insert then...

Excel: EXCEL, excel objects, score card

excel objects, score card, dropdowns: So the files which are linked to the master file each have their own password? You could do it like so: - In your master file, insert a worksheet. Call it Settings. Place this table on that sheet, starting from A1: Name FileName Password John...

Excel: Excel array issue - sumproduct within sumproduct?, size array, sumproduct

size array, sumproduct, variable size: it will work BUT only if you have both halves of the formula referring to the same size array - I d go with the j2:J6666 method myself as it s going to be a slow formula, plus I was always told that this method of SUMIFS doesn t work on whole columns. You...

Excel: excel creating temp files, files excel, temp files

files excel, temp files, temporary file: It sounds like a failed save - the save process is write the file to a temporary file once done delete original file rename temporary file to original You didn t say if your files were shared - this can often happen with that type of file (shared workbooks...

Excel: Excel data selection, vlookup function, temperature reading

vlookup function, temperature reading, time column: David, remember I stated: assuming the time values in column D each match one time value in column A You are showing me that that is not the case. You can try an approximate match with =vlookup(D1,A:B,0,True) note the last argument is...

Excel: Excel data sorting/filtering, custom criteria, text filters

custom criteria, text filters, autofilter: Farhad, I assume you are using the autofilter on your data. In the dropdown for that cell, you want to use a custom criteria. In Excel 2007 and later, when you do the dropdown, then choose Text Filters and one of the resulting choices should be contains....

Excel: Excel data sorting/filtering, drop boxes, custom filter

drop boxes, custom filter, autofilter: This is what the AutoFilter was designed for. Click in your data. Turn on the Data Filter (autofilter) A series of drop boxes will be in your column headers now. Use the product column drop box to do a custom filter for contains: xxxxx When...

Excel: Excel data sorting/filtering, filter feature, text filters

filter feature, text filters, richard florida: Farhad You can use the filter feature to isolate the rows in the column that contain the key word. I do not know what features the 2004 MAC version of excel contains so you may need to work with someone who knows that version. However in the 2007 PC...

Excel: Excel Formula, array formula, curly brackets

array formula, curly brackets, ot hours: Mike, if I understand the true need, you need to count the number of cells in the range D8:D42 that contain L, LS or TDY. the easiest and my first guess would be =SUM(COUNTIF(D2:D42,{ *L* , *TDY* }))/22 the problem here is if there could be an...

Excel: Excel Formula Q, excel formula, question thanks

excel formula, question thanks, ogilvy: Greg Tilkins, =if(or(B1= ,C1= ), N ,if(And(A1 =B1,A1 C1), P ,if(A1 =C1, E , N ))) In this case, presumably whenever the conditions are not met D would be blank. no, that isn t true. If you use an if statement it will return a value. If...

Excel: Excel Formulas, car movement, sum formula

car movement, sum formula, excel formulas: Carol, if you want to send me a sample file with some representative data, I can try to help you. I need to know how your data is laid out. for instance, if I wanted a total mileage, I would just use a sum formula to add up the cells that contain...

Excel: Excel Formulas, car movement, excel formulas

car movement, excel formulas, six days: I m not certain that I ve got enough information to go on Question 1 - surely the totals are totals regardless of number of days? question 2 - effectively it s total X divided by days, but I m not clear how we determine days question 3 - same answer as...

Excel: Excel help., Sixthsense Excel

Sixthsense Excel: Hi A., Have a look in the below link for more detail. http://www.cpearson.com/excel/overtime.htm To download a sample file:- http://www.cpearson.com/Zips/overtime.zip Hope that helps! Feel free to post a follow-up, if your requirement is not...

Excel: Excel Image, target value, target address

target value, target address, sheet1: Brian, Me refers to the sheet that contains the code so you would need to have Me changed to refer to the worksheet where you want the picture inserted Private Sub Worksheet_Change(ByVal Target As Range) Dim sh as Worksheet If Target.Address ...

Excel: Excel Macro question, k200, field 3

k200, field 3, excel macro: Hi Brad, You can use code like this to loop through all cells in a range, using the criteria1 values in the cells in your code like this: Dim Criteria1 As String Dim C As Range a single cell in the range to be looped ...

Excel: Excel recognizing user selections in drop down boxes, combo boxes, cell a1

combo boxes, cell a1, export summary: You don t specify exactly what you mean by Drop Down Box. You can have in-cell drop-downs, or ActiveX combo boxes, or Form combo boxes. Best is to use in-cell dropdowns. Select cell A1 of Sheet1, and then choose Data, Data Validation, Allow: List, etc....

Excel: excel sheet, hyper link, sheet1

hyper link, sheet1, excel sheet: Hi kumar, When clicking that cell the hidden sheet should be activated and for doing this you should go for macro, 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: Excel splitting names in one column, sally lou, cell a1

sally lou, cell a1, question pool: Hello Rochelle Little, You didn t ask me this question. I found it in the question pool. Your question is little bit confusing. You show =Concatenate but it sounds like you want to split the data in one column into two separate columns based on first...

Excel: Excel VBA Checkbox, excel vba, a73

excel vba, a73, activex object: I ll assume that the checkbox is on the sheet named QUESTIONS . I ll assume it was inserted onto the sheet as an ActiveX object. In the code below, chkBox.Object will be TRUE if the checkbox is checked, and FALSE if it s unchecked. Dim chkBox As OLEObject...

Excel: Excel VBA conditional copy and paste, cell a2, excel vba

cell a2, excel vba, ogilvy: Elizabeth, this does a case insensitive check if the substring prl exists in the cell: If Instr(1,Sheets( CopyFrom ).Range( AJ39 ).Text, PRL ,vbTextcompare) Then Sheets( CopyFrom ).Range( AJ39 ).copy Sheets( Pasteto ).Range( A2 ) End If if...

Excel: Excel vba to ensure font size must be 12., colorindex, sheet tab

colorindex, sheet tab, target: clarkson, -------- revision to answer --------- I am not sure why you would need to change the background color on every selection change, but if that is what you want, then I would suggest you do it this way: Private Sub Worksheet_SelectionChange(ByVal...

Excel: Excel VBA Find Function, excel vba, range c

excel vba, range c, ogilvy: Elizabeth, this worked for me. Check the parameters of the FIND method to make sure it is set up as you want. Sub FindMultiple() Dim r As Range, r1 As Range Dim sh As Worksheet, sAddr As String, s As String Dim List() As String ReDim List(1 To...

Excel: Excel VBA Message Box with checkboxes, john walkenbach, excel vba

john walkenbach, excel vba, checkbox example: Henry, Unfortunately a message box only does what a message box does - it display a message with possibly some icons and you can configure it to provided several different button sets or combinations of buttons for the user to respond to. (there is also...

Excel: Excel - vlookup, excel vlookup, g10

excel vlookup, g10, ogilvy: Joann, this formula worked for me =VLOOKUP( *# &A2& * ,$F$6:$G$10,2,FALSE) A2: 508 and the table was in F6:G10 (I added a second column to return additional information). In the formula, the lookup value 508 becomes the string *#508* I...

Excel: Exporting more than 65K rows of data, xlxm, format xls

xlxm, format xls, compatibility mode: Lloyd Sorry it should have read xlsx. When it downloads in compatibility mode it will truncate the file to 65000. According to my research it should open all the rows when saved as the xlsx though. If I had a file that large I could test it but I don...

Excel: If formula working for some lines but not others, ipt

Excel: If formula working for some lines but not others, ipt

Excel: Filter Row Contains Merge Cell, autofilter, ogilvy

autofilter, ogilvy, xeon: Xeon, this worked for me: Assuming you want to start filtering in row 2 and your headers are mixed in rows 1 and 2 as you show in your picture. Sub AAA() Dim r As Range, r1 As Range Set r = Range( A2 ).CurrentRegion Set r = r(r.Count) Set r1...

Excel: Find & Replace, target cell, cell d2

target cell, cell d2, variable declarations: Jun, no, I guess I didn t understand that as the desired result. Here is a correction to the code to do what I now understand you to want. Sub findrep() Dim target As Range, cell As Range Dim i As String, k As String, s As String i = Birthday...

http://en.allexperts.com/q/Excel-1059/2011/8/find-replace-word-copy.htm

Excel: follow up question, a73, activex object

a73, activex object, checkbox: In his first answer, he clearly demonstrated that you identify the Parent sheet object that the OLEOBjects are located on. You left that out. you also completely changed the syntax, no need to do that either. I ll assume this macro code is located in...

Excel: followup on updating work books, investor stock, work books

investor stock, work books, computer c: The routine I sent you needs to be executed via Alt/F8, as you did. I have little experience with getting data in from the web, but this should work: In the routine I sent you, before the line: Calculate add the line: ActiveWorkbook.RefreshAll This...

Excel: How to Format a Column in Order of Date, question pool, ogilvy

question pool, ogilvy, nichole: Nichole, you didn t ask me this question - I found it in the question pool. (one reason why the response is not timely). this would require a macro. If you want a macro, then send me a sample workbook and explain what you want and I will show you how...

Excel: Formual using { }, array formula, curly brackets

array formula, curly brackets, paste function: Esther =OR(A18=T17,U17:AA17=C18:I18) type it in like the above, but instead of finishing off the enter key, Hold down the CTRL and SHIFT keys together and then hit enter. This will Array enter the formula. then if you reselect the cell and look...

Excel: Formula, Sixthsense Excel

Sixthsense Excel: Hi Jan, Have a look in the below links which will give you a better idea on how to protect the cells in excel. http://www.officetooltips.com/excel/tips/allow_specific_users_to_edit_ranges_in_a_protected_worksheet.html http://www.lytebyte.com/2009/06/15/how-to-protect-only-certain-cells-columns-or-rows-in-excel/...

Excel: formula, blank answer, usable range

blank answer, usable range, j6: You can t apply this formula to the entire column J, but you can pick a usable range to cover your needs for awhile. Let s assume your column J gets one new value per day, you could set the range to 500 cells and that would take care of you for a year and...

Excel: formula in excel 2007, sum formula, sheet1

sum formula, sheet1, ogilvy: Tiffany, You don t show an example of the formula you are using to do the sum. I will assume it is a 3D formula. If so, then the average would be the same =Average(Sheet1:Sheet5!A1) Average will also work with a 3D formula. If that wasn t...

Excel: formula needed, blank cells, question example

blank cells, question example, rg2: This function assumes there are never more than 2 numbers in a tie: Alt/F11, Insert/Module, Enter the code below, then put this in a worksheet cell: =sum4(B1:I1,B2:I2,B5:I5) Function Sum4(rg1, rg2, rg3) Dim cols(1 To 4) As Integer, n As Integer ...

Excel: Formula- poss match/vlookup/lookup?, cell a2, practice column

cell a2, practice column, medicine name: Rachelle, I can show you how to do it, but it is mildly complex (easier to give an example). If you want to send me a sample workbook to twogilvy@msn.com with say 10 or so dummy names and specialties in sheet1 and the the specialty headers in sheet2 I...

Excel: If formula working for some lines but not others, y6, u6

y6, u6, formula one: All pictures uploaded to this website are resized and yours was too big to be legible after the resize. If you want to update your picture to show fewer columns, perhaps it will be legible. Without seeing the data, I d suggest you watch your formulas...

Excel: IF Function problem..., j2, time function

j2, time function, time values: Erin, 00:30:00 is just a string - it isn t a time value. I assuem J2 holds a time value. you can use the time function =IF(J2 TIME(0,59,0), 60mins , IF(J2 TIME(0,31,0), 30mins , IF(AND(J2 TIME(0,30,0),J2 TIME(1,0,0)), 30-60mins , NIL...

Excel: Help filtering calendar dates., target range, max range

target range, max range, calendar dates: Maybe this will work for you: Option Explicit Private Sub Calendar1_AfterUpdate() Range( C2 ).Value = Calendar1.Value End Sub Private Sub Calendar2_AfterUpdate() Range( C3 ).Value = Calendar2.Value End Sub Private Sub Worksheet_Change(ByVal...

Excel: Hours, excel expert, mm ss

excel expert, mm ss, maintenance team: If the 23:30 is in A1 and the 30:00 in A2: = THE MAINTENANCE TEAM HAS WORKED &TEXT(A1, hh:mm )& OUT OF &TEXT(A2, hh:mm ) That assumes the 23:30 is 23 hours & 30 minutes, not 23 minutes & 30 seconds. In the latter case, use mm:ss instead of hh:mm ...

Excel: Identifying duplicate number combinations, nearest neighbour, number combinations

nearest neighbour, number combinations, first instance: Katherine, so you are looking for any instance where the two locations reappear in a previous row and that would constitute a duplicate. Assume 27098 in your example is in A2. So in D2 you could put the formula =IF(SUMPRODUCT(--($A$1:$A1=$A2),--($B$1:$B1=$B2))+SUMPRODUCT(--($A$1:$A1=$B2),--($B$1:$B1=$A2))...

Excel: Importing data from sheet to sheet based on cell value, personal observations, assignee

personal observations, assignee, thanks in advance: Hi Owen, Thanks for sending your question my way, sorry to say that I am out of expertise and I recommend you to contact other Excel experts that claim expertise in Excel VBA such as Bob Umlas, Tom Oligvy. I know they have provided excellent solutions...

Excel: Importing Multiple .txt Files (contain numbers and words that are comma separated), iloc, numbers and words

iloc, numbers and words, rbw: Utkash, this worked for me using 3 files made from your sample data. It writes the data for the first file starting in A1:B1, second file starting in C1:D1 and the third file starting in E1:F1 Sub ABC() Dim v As Variant, l As Long, i As Long, icol...

Excel: Incentive Calculation, tiered structure, question thanks

tiered structure, question thanks, incentives: Ok, this is the exact same exercise twice. Make sure you spend some time examining these functions and grasp what s going on here, you should be able to edit this formula for your next scenario. Build your lookup table the same way again, from smallest tier...

Excel: input device, jeff conrad, barcode scanner

jeff conrad, barcode scanner, keyboard entry: Hi Jeff Conrad, Thanks for sending your question my way, sorry to say that I am out of expertise and I recommend you to contact other Excel experts that claim expertise in Excel VBA such as Bob Umlas, Tom Oligvy. I know they have provided excellent solutions...

Excel: This Isn't Excel, It's Magic!, magic book, ribbon

magic book, ribbon, holes: Thanks. Glad you like it. I m coming out with another book early next year: Excel Outside the Box - a very high-end technique book... There is a 2nd edition for the Magic book for excel 2007, but it doesn t address how to manipulate the ribbon. For that,...

Excel: Issues using a sub, run time error, subscript out of range

run time error, subscript out of range, csv file: Hi Hebron, 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: Issues using a sub, run time error, subscript out of range

run time error, subscript out of range, csv file: Hebron I am def not a VBA expert. I suggest you reask the question of another and ck the resumes for someone who is proficient in VBA. You will get a quicker and better answer than I can give you. Sorry I couldn t be of more help Richard Florida...

Excel: ListBox Rowsource, listindex, blank fields

listindex, blank fields, cevallos: Andres, I guess I am not following your question. 1) B2 and C2 are in the rowsource range so I am not sure why you would write the data there. 2) was something wrong with your original code? I was under the assumption that was working. 3) ...

Excel: The Lottery Code, a 400 page ebook (free), mathematical probabilities, adequate job

mathematical probabilities, adequate job, numerical sequence: Hello Martin, I apologize in advance that I cannot answer your question. I have downloaded the book (I m grateful that Lorraine H-S made it available for free) and looked at it briefly, and can see that it would take months of my time to do an adequate...

Excel: Macro copy and paste, spreadsheet column, formula 3

spreadsheet column, formula 3, pastes: First, I hope you didn t manually create those 360 different formulas! The function you need is COUNTIF() not COUNTIFS(), different critters. You can put this formula in B3 and copy down, it will adjust automatically: =COUNTIF( WEEK4 11Jul to 17Jul...

Excel: macro for opening file.., filename changes, format date

filename changes, format date, four letters: Assuming the date format is YYDMM, so the filename on September1 would be hdf11109, and the filename on December 31 would be hdf113112, something like this: Dim WB as Workbook Set WB = Workbooks.Open( hds & Format(Date, YYDMM ) & .xls ) ...

Excel: Macro status check, left hand corner, neha

left hand corner, neha, easiest thing: Neha The easiest thing to do is probably to use the status bar to display the progress. For example your code could follow the format below: Sub Macro1() Application.Statusbar= Calling macro 1, 10% Call Macro1 Application.Statusbar= Calling...

Excel: Max if, array formula, curly brackets

array formula, curly brackets, visual cue: Ben, the formula I just sent you should work for what you describe if the dates are sorted and you want the value in the last row that contains a date for that month. here it is again =INDEX($H$1:$H$124,MAX(IF(MONTH($C$6:$C$124)=MONTH($D$2),ROW($C$6:$C$124))),1)...

Excel: Max-If Statement with two Conditions, richard florida, max value

richard florida, max value, a10: Joe I m not sure if this is exactly what you are asking but try this formula. =IF(AND(A1:A10 =5,A1:A10 =9),MAX(B1:B10), XX ) If all of the cells in A1 thru A10 meet the two criteria then the highest value in B1 to B10 will be returned, otherwise XX...

Excel: Min if and max if statements, array formula, zero values

array formula, zero values, number present: max shouldn t be a problem as a max of a range containing zeros would ONLY return zero if that was the only number present. For min though (I m doing this without a copy of excel 2010 in front of me so don t know if there is a neater solution) I d do =MIN(IF(F343:F345=0,99999,F343:F345))...

Excel: Modifying A File List Macro, xdirect, extension ie

xdirect, extension ie, question option: Yes there s always a way, but let me say at the outset it s an aesthetic I hope you abandon. Data should be reviewed accurately, and trimming off the extension from a list that is giving the path and filename is... well... inaccurate. Anyway, you can...

http://en.allexperts.com/q/Excel-1059/2011/8/move-info-2-worksheets.htm

Excel: How to move specific alphabets onto another column, instrrev, instr function

instrrev, instr function, explicit function: Gerard The best way to do this is probably with a userdefined function. In VBA we have a function which makes it pretty easy; unfortunately the function is not available on worksheets. The function is INSTRREV, which works like the INSTR function which...

Excel: moving filtered data from one tab to another, auto filters, paste data

auto filters, paste data, header row: Hi V, 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, Tom...

Excel: multiple formula in one cell, input cells, none of those things

input cells, none of those things, g20: Mike, there will only ever be one number in C20 to I20 so if I did =sumproduct((C22 0)+(E22 0)+(G22 0)+(I22 0)) it would always equal 1 given that then C22: =if(C20 0,2075-C20,if(E20 0,E22-D7,if(G20 0,E22-D7,if(I20 0, I20 0 ,0)))) E22: ...

Excel: Names, carla maria, trim right

carla maria, trim right, cell references: Matt, I have attached an image to show that it works fine for me. I can reproduce your problem if I have 3 spaces between Wynn and Elizabeth. You can fix this by selecting your column and doing a find and replace. Have it search for two spaces and replace...

Excel: Need Combine Data, contextures, pivot table

contextures, pivot table, column c: Rocky/Vaibhav, like pivot table so why not use a pivot table. See Debra Dalgleish s discussion of using multiple consolidation pivot tables at her site: http://www.contextures.com/xlPivot08.html Is there any solution in which I need to select...

Excel: Need a macro[VBA solution] to auto fill a cell based on drop-down selection, cell b1, cell c1

cell b1, cell c1, names of fruits: I don t use VBA for things like this, I use a single tall list of values then use a dynamic named FORMULA to capture the options in the second column of the long list. First setup your tables in simple two-colum format like shown in the attached picture....

Excel: Need a macro[VBA solution] to auto fill a cell based on drop-down selection, cell b2, target address

cell b2, target address, cell a1: Sourabh, well, I will have to make some assumptions. I will assume that any value in B1 will also be a named range as this is consistent with using =Indirect(B1) as the source for a data validation in C1 and otherwise, would specify what value goes in...

Excel: Need a macro[VBA solution] to auto fill a cell based on drop-down selection, cell b1, cell c1

cell b1, cell c1, names of fruits: Hi Sourabh, 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: Please help me in simplifying a function, vlookup formula, exact formula

vlookup formula, exact formula, d49: Abdul I do not knwo how your data in the two worksheets is set up, so it is a bit difficult ot give you an exact formula, but I will suggest that you mauy be able to find your information by using a vlookup formula. The vlookup formula can search the sheets...

Excel: Pop up window alert in excel, excel spread sheet, tom hope

excel spread sheet, tom hope, message alert: Karthick, the code If Len(Trim(s)) 4 Then MsgBox s Else MsgBox No PO Creation due End If produces a pop up regardless of the state of your sheet (a sheet named Sheet1). It will either list the offending cells or it will tell you ...

Excel: All possible combination, number combinations, irow

number combinations, irow, proficiency: Hi Eduardo, This macro will generate all the combinations of 25 numbers taken 15 at a time. _______________________________ Sub GenCombins() Generates all the combinations of 25 numbers taken 15 at a time Concatenates numbers within a...

Excel: print macro with pdf, weather yesterday, spath

weather yesterday, spath, adobe print: Diane, Sub SaveAsPDF() Dim sh as worksheet, bk as workbook Dim sPath as String set sh = Activesheet sh.copy set bk = ActiveWorkbook sName = Application.GetSaveAsFileName(fileFilter:= PDF Files (*.pdf), *.pdf )) sName = bk.worksheets(1).Range(...

Excel: Problem erasing empty cells and edit text., vacuum line, empty cells

vacuum line, empty cells, cg 25: 1-Empty spaces bewteen each cell?? Not sure what you mean. Sounds like you have unused columns. If that s true, select each column and delete it (ctrl/-). If you mean something else, please be more precise. 2-I also don t understand what you want here --...

Excel: to provide only write access to users, sanjay, checkbox

sanjay, checkbox, cells: If you FIRST select ALL the cells for which it s OK to make changes or enter data, then, use ctrl/1, select the Protection tab, and UNcheck the Locked checkbox. Now, when you protect (with or without a password) the sheet, there will not be a problem with...

Excel: to provide only write access for users, Sixthsense Excel

Sixthsense Excel: Hi Sanjay, Have a look in the below links which will give you a better idea on how to protect the cells and rows in excel. http://www.officetooltips.com/excel/tips/allow_specific_users_to_edit_ranges_in_a_protected_worksheet.html http://www.lytebyte.com/2009/06/15/how-to-protect-only-certain-cells-columns-or-rows-in-excel/...

Excel: to provide only write access for excel sheet, cell content, excel sheet

cell content, excel sheet, richard florida: Sanjay I do not know of a way to protect cells from data being deleted while at the same time allowing data input. You might try asking the question of other allexperts, and they might have an idea for you. Sorry I could not be of more specific help...

Excel: Quick links page, hyperlink type, team folder

hyperlink type, team folder, hunt and peck: James In the cell you want the hyperlink type in the name of the document you want to open then press enter and go back to the cell and right click and select hyperlink, go to the directory that has the document and click on it. this will insert the hyperlink...

Excel: Re-cutting rotas, reformatted data, sheet cells

reformatted data, sheet cells, cell a1: Tanya Warnakulasuriya, this macro worked as I expected when processing your sample data. It adds a new sheet and puts the output there. It expects the data you show to start in cell A1 of the activesheet when you run the macro. Sub ReCuttingRota()...

Excel: Reading multiple csv files, csv files, compilation errors

csv files, compilation errors, compilation error: Nikhil, ====================== revision to my answer ============== PS: I revised my answer - I forgot to remove the path statement I was using - it was pointing to my test location. ====================== End of Revision ==================== ...

Excel: Rearrange List Between Sheets, sigma 5, vlookup function

sigma 5, vlookup function, arbitrary values: Christian, Assume your data starts in A1 of the source sheet and the first row is in A1:C1 in A1 of the destination sheet, put his formula =INDIRECT(ADDRESS((ROW()-1)*3+1+TRUNC((COLUMN()-1)/3),MOD(COLUMN()-1,3)+1,,, Sheet1 )) (in the formula, change...

Excel: replace char(13) from string, Sixthsense Excel

Sixthsense Excel: Hi Vijay, Have a look in the below link. http://www.pcreview.co.uk/forums/replace-paragraph-return-t935208.html Hope that helps! Feel free to post a follow-up, if your requirement is not fulfilled. Don t forget to rate the posts, since feedback...

Excel: Replacing space with dash, home tab, ogilvy

home tab, ogilvy, dashes: Michael select column A. go to the Replace command and select it. Put a space in the first text box and a dash in the second text box and choose replace all. In excel 2007 and later, the replace command should be on the right side of the Home tab...

Excel: require macro code for vlookup range change automatically in excel 2003, vlookup function, macro code

vlookup function, macro code, daily basis: The VLOOKUP() function does not require you to set specific rows, it will operate off of the entire column of product codes just fine. Sheet1 & Sheet2 A = product code B = value C = type On sheet2 in D2 you can use this VLOOKUP() to get the value...

Excel: Restrict Data entry with time limit, data validation, activity details

data validation, activity details, limit 24: If the dates are in column A starting in A2, for example, select ALL the Activity details cells, then assuing the ACTIVE cell is B2, use Data Validation, select Custom Formula, enter this: =AND($A2+0.27083333333 =B2,$A2+1.2708333333 =B2) then click the Error...

Excel: row sorting in excel, sorting in excel, long time

sorting in excel, long time: I would suggest doing this like so: Next to your table add two columns. First column contains numbers 1, 2, 3, ..., indicating the original sort order. Let s assume this is column E. In the second column, enter this formula: =IF(MOD(E1,6)=0,0,E1)...

Excel: Running Macro, Sixthsense

Sixthsense: Hi John Arbie, 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...

Excel: Running Total, monthly budget, expert level

monthly budget, expert level, allexperts: Just copy and paste a LINK to last month s total to the next month s sheet. This will keep a live, updated value for you to work with. I cover this in my Excel 2010 Expert Level 1 class, lesson 4. http://599cd.com/site/courselist/excel2010/expert/x1/?key=AllExperts...

Excel: Searching for a formula to add data based on criteria, and display one result in last row, payroll report, pivot table

payroll report, pivot table, pivot tables: The pivot table is very simple. - Select the entire columns covering your data table - From the insert tab, click the pivottable button - CLick OK - In the task pane, drag the Name item to the row labels section - Drag the hours to the Values section...

Excel: Separating word from numbers, verticle lines, double clic

verticle lines, double clic, richard florida: Stephen You should be able to do this with the text to columns feature in excel. First paint the data that you need to change. Then go to toolbar/data/text to columns. Select fixed width, then next. A screen will pop up that will show where the data...

Excel: Sorting data with a macro, blank cells, header rows

blank cells, header rows, header row: Shawn, try something like this: Dim r as Range set r = range( D3 , Range( D3 ).End(xldown)).Resize(,3) r.Sort Key1:=Range( D3 ), Order1:=xlAscending, Header:=xlNo assumes column D is completely filled with data (no blank cells) for as far down as...

Excel: IF Statements, lookup functions, period range

lookup functions, period range, google: When you need to find a single value in a list of other possible values, you need to use lookup functions, not a never-ending stack of IFs. Excel offers many lookup functions... LOOKUP() - when the data is sorted ascending and exact matches are not needed...

Excel: If Statements, Sixthsense Excel, Excel Sixthsense

Sixthsense Excel, Excel Sixthsense: Hi Carol, Try the below formula s. Q10 as stable Value and No of days worked in R10. =IF(R10=5,$Q$10/5,IF(R10=4,$Q$10/4,IF(R10=3,$Q$10/3,IF(R10=2,$Q$10/2,IF(R10=1,$Q$10, ))))) Q Column as rotating reference and No of days worked in R10. =IF(R10=5,Q10/5,IF(R10=4,Q10/4,IF(R10=3,Q10/3,IF(R10=2,Q10/2,IF(R10=1,Q10,...

Excel: Status update as macro run, john walkenbach, progress bar

john walkenbach, progress bar, site tip: Neha, it would be difficult to give a good explanation in this format. However, John Walkenbach has laid out the procedure in a web page with illustrations and complete instructions - so I will refer you to that page: http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/...

http://en.allexperts.com/q/Excel-1059/2011/8/sum-5-15-vba.htm

Excel: Sum values by month, value error, ogilvy

value error, ogilvy, invoices: James, two ways (examples are for values in March of 2011) =sumif(E:E, =01/03/2011 ,AH:AH)-Sumif(E:E, =01/04/2011 ,AH:AH) or =sumproduct(--(month($E$2:$E$500)=3),($AH$2:$AH$500)) (for completeness: generally you don t want to use entire...

Excel: Sumif number greater than zero and not strikethrough, double application, worksheet functions

double application, worksheet functions, vba code: Tom, just add that condition in your custom function Public Function SumNotStrikeGTZeroFunction(rRange As Range) As Double Application.Volatile True Dim cl As Range, tosum As Double For Each cl In rRange With cl If Not .Font.Strikethrough...

Excel: sumifs??, share expenses, richard florida

share expenses, richard florida, pocket expense: Sheila Perhaps the reason your question was not answered satisfactorily was because it is really difficult to understand. What I believe the question is, is this--- How do I create a formula that will ignore numbers in column E, (which apparently contains...

Excel: Template/Hyperlink, Sixthsense Excel (Readonly)

Sixthsense Excel (Readonly): Hi Thomas, Have a look in the below link for details. http://office.microsoft.com/en-us/excel-help/customize-how-excel-starts-HP005199391.aspx http://office.microsoft.com/en-us/excel-help/save-a-workbook-as-read-only-or-remove-read-only-status-HP005201902.aspx...

Excel: Time sheet spreadsheet, time lunch, question pool

time lunch, question pool, time sheet: Jennifer, You didn t ask me this question. I found it the question pool. send me a small sample file with times already entered - the file should illustrate the problem. There are so many things that need to be considered here, I really need to see...

Excel: Go to Today's date, range b21, goto statement

range b21, goto statement, rng: Niggo, you added the select to the goto statement. The select is invalid. the goto statement causes the range to be selected. so remove the .select from Application.Goto rng(res).Select, True so it is Application.Goto rng(res), True...

Excel: top 5 values including its name sorted out., class column, column c

class column, column c, autofilter: Jeffrey, there are several complexities to what you want to do. First, you want to sort you numerical data in column B descending for which the Large function will do a fine job. However, you say if you have a tie in column B, you want to sort ascending...

Excel: truncating at steps in macro, inflation factors, inflation rates

inflation factors, inflation rates, dval: Pete, assume your Years are in C5:Q5 and your corresponding inflation rates are in C6:Q6 of the activesheet when you run the macro then Sub CalcValue() Dim L1YR As Long, L2YR As Long, LVAL As Long, L1VAL As Long Dim dVal As Double, FY As Range,...

Excel: Trying to find a way to reduce entering amounts in the wrong column, data validation, correct column

data validation, correct column, wyne: Right-click the sheet tab, select View Code, and assuming MEDMAC, etc is in row 1, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Left(Target.Value, 1) Left(Cells(1, Target.Column), 1) Then...

Excel: update workbooks in a folder, external data source, vba

external data source, vba, time consuming: Sub OpenandUpdate() Dim fl As String fl = Dir( *.xl* ) On Error Resume Next Application.DisplayAlerts = False While fl Workbooks.Open fl, True Calculate DoEvents If ActiveWorkbook.Name = fl Then...

Excel: updates, rerun, xls files

rerun, xls files, xl: Sorry - yes, it had a looping bug. Use this: Sub OpenandUpdate() Dim fl As String ChDrive ThisWorkbook.Path ChDir ThisWorkbook.Path fl = Dir( *.xl* ) On Error Resume Next Application.DisplayAlerts = False While fl ...

Excel: updating all workbooks in a folder from external data source, Sixthsense Excel (Merging Workbooks)

Sixthsense Excel (Merging Workbooks): Hi Todd, 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: How to use macro to fill a cell based on two drop-down selections, cell b1, cell c1

cell b1, cell c1, names of fruits: SOURABH It does appear that you will need a macro to do what you are asking. Unfortunately I am not a Macro expert. I suggest you reask the question of another allexperts person. Check the resumes for one who is proficient in Macros, and I am sure you...

Excel: Using radio buttons to change images in a sheet (with VBA), button option, dear tom

button option, dear tom, radio button: Fabricio, There is no image attached. But even it there was, it would not be helpful as I need to set up your sample workbook to show you how to do it. I gave you my email address in my first answer so you could email the sample workbook and pictures...

Excel: Using VBA to remove a row, therow, jane smith

therow, jane smith, sam adams: Sub DeleteARow() Rows(2).Delete End Sub or, if you don t know it s row 2 but it IS Jane Smith: Sub DeleteARow() Dim TheRow as Integer TheRow = Application.Match( Jane Smith* ,Columns(1),0) If TheRow 0 then Rows(TheRow).Delete End Su...

Excel: VBA Code to Mark Eligible Record When Selected, target cells, target range

target cells, target range: Try this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim sRange As Range Set sRange = Range( EG2:EG & Cells(Rows.Count, 1).End(xlUp).Row) If Target.Cells.Count 1 Or Target.Column 137 Then Exit Sub ...

Excel: VBA Duplicate Removal and Merging, example john doe, mail system

example john doe, mail system, jane doe: James, -------- Revision ------------ I just realized I copied the wrong formula into the response. the formula should actually be (entered in A2 - assuming your have headers in row 1) =Countif($A$1:$A2,$A2) then drag fill that down the column...

Excel: VBA variable storage, boolean value, true sets

boolean value, true sets, variable storage: John Wolford, You could use a defined name or you could write it to a cell. In either case, the workbook would have to be saved after the value was changed. You could have your button code create a worksheet or delete it and make that sheet hidden....

Excel: VLOOKUP, vlookup formula, friendly time

vlookup formula, friendly time, time formats: You re always going to have these problems with time data. Just guessing, at the breaks from one hour to the next, you might need to tweak that one cell to bring back the correct row, then the next group will be OK for awhile. The technique I m showing...

Excel: Vlookup/Index/Match ... Which one to use, vlookup function, employee names

vlookup function, employee names, excel spreadsheet: It s not clear what s where, but assuming the first name is in A, last name in B in the main sheet, and the first names are in B on the Totals sheet and last names in M in the totals sheet (odd), and that the earnings are in U in the Totals sheet, then this...

Excel: Vlookup Variation, workable formula, mycell

workable formula, mycell, column number: still sounds like a normal vlookup to me! I would use the COLUMN worksheet function to determine which column to return =vlookup(MyCell,Sheet1!$a$1:$z$200,column(),false) will return the value from whichever column number the function is in - so dragging...

Excel: Vlookup Variation, index function, a33

index function, a33, hth: Let s say the value you re looking up is in Sheet2!A1. The best way to do what you want is to just return the ROW of the answer and then use that as input to the INDEX function in other cells. For example, if you want to find IBM , in A1 enter =MATCH( IBM...

Excel: The Weekday function, cell a1, true dates

cell a1, true dates, question pool: Chris, you didn t ask me this question - I found it in the question pool. If I put 40545 in a cell and use the weekday function, it returns a 1. Why? Because 40545 is how Excel stores the Date Jan 2, 2011. It is the number of days from the default...

Excel: Word to Excel, paste link, macro code

paste link, macro code, word files: The basic concept then would be (I think) to have the macro in word. This macro to open each file in given location and to set the contents of cells in excel to the values of the fields as found - which version of office are you using, and is it possible...

Excel: If and then, 1 match 1, direct email

1 match 1, direct email, pivot table: Seems possible, though not sure what you wanted to happen if you had MORE than one rota d day (as is the case for Geoff in your example). Also not clear WHICH name you d want to pick up as Jim doesn t exist on row 8. That said, =MATCH(1,B2:D2,1) will...

Excel: Xcel, dataset, aidan

dataset, aidan, b1: =OFFSET($A$1,(ROW()-1)*10,0) A1 is the starting cell with your data - so amend this as required row()-1 if the formula is entered into B1, then this line is correct - otherwise, change the -1 to another minus value eg B2 would become -2 The row()...

Excel: Absolute Reference Help or another work around, csv file, 6 cells

csv file, 6 cells, absolute reference: BTO The easiest would probably be to write a macro the brings over the data. If you want to send me a sample workbook where you want the data and a sample CSV file and explain clearly what you want where, I can try to give you a hand. I can...

Excel: Activating Object with a Range, target value, array object

target value, array object, ogilvy: Ramsey, actually, as written, the macro will play the wave object if you edit any cell and P remains in cell A15 since the change event fires on every edit of any cell. But to do what you now request Private Sub Worksheet_Change(ByVal Target As Range)...

Excel: Add/Delete Button, box button, double click

box button, double click, stock market: How about we don t add extra plumbing and put the action right on the sheet itself. If your LIST1 is in Column A. If your LIST2 is in Column B. We can make this work by simply double-clicking on a value in either list. If you double-click a cell in...

Excel: Autopopulate sections from seperate sheet-+, fitness column, class priority

fitness column, class priority, colored boxes: Matthew, your image just looks like a bunch of colored boxes - it is too small to make out any details, text or cells. If I have this on a sheet named fitness: supplier name, class, priority, production stage, and status starting in A1 (data...

Excel: Balance, richard florida, e2

richard florida, e2, f2: MUHAMMAD you have to determine which criteria should be first in order, because some of them are exclusive of others, for example If E2 = 4000 then F2 = 0 If A2 = 0 then, E2 = 3000 And F2 = 1000 Which is more important or which comes first the values...

Excel: Blank cell merging, cell b3, cell c3

cell b3, cell c3, empty cells: Von, As to the 1st issue. It sounds like the macro is doing exactly what you want with respect to which columns are processed but you are asking why it works with columns A:Q regardless of where the data is located. That is because those locations are...

Excel: cell formats changing when pasting from SAP, conditional formatting, authoritative answer

conditional formatting, authoritative answer, time formats: * I don t work with SAP or SAS, so I don t have an authoritative answer, but if I were you I would check these 3 things: 1. Directly after you paste it in Excel, you should see a little flag that can be used as a drop-down, Paste Options button, near...

Excel: change cell color based on a formula, color shading, conditional formatting

color shading, conditional formatting, allexperts: You can do this with conditional formatting: http://www.599cd.com/search/?Q=conditional+formatting&key=AllExperts If you want B1 to be highlighted if it s more than 75% of A1, you d just say this in your greater than or equal to rule: =$A$1*.75...

Excel: Clearing Cells after printing, format cells, preferred method

format cells, preferred method, spreadsheet: There isn t an after print event, but the easiest fix would be to create your own macro that printed the sheet and then cleared the data - or indeed, just cleared it. You can record the macro to clear cells - just select them (as a block would be my preferred...

Excel: Combining Rows and Adding the Balances, answerable question, redundant data

answerable question, redundant data, e mail: Justin, without seeing our macro it is hard to say if a change can be incorporated in your macro. If I was doing it I would do it this way (assuming acc# is in A1) Sub CompressData() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, 1).End(xlUp).Row...

Excel: Compare and pull out the data, rush rush, richard florida

rush rush, richard florida, thsi: Rush A couple of suggetions might help. You can isolate the data you want with a formula, for example =IF(AND(H1=1,L1= M ),+K1, ) This will bring the data over to the cell the formula is in only when the both conditions are met. You can then sort...

Excel: Compile Error: Argument Not Optional, cell a2, blank cell

cell a2, blank cell, optional question: J, dim r as range set r = Activesheet.Usedrange set r = r.offset(2,0) set r = r.resize(r.rows.count - 2) r.Sort Key1:=Range( A3 ), Header:=xlYes if that causes problems (but it shouldn t) then dim r as range set r = Activesheet.Usedrange set...

Excel: Complex Macros, sheet names, ws2

sheet names, ws2, asw: * Here is a start, for SBO types. Maybe you can take & tweak from here? This is untested air code I typed out, let me know if it doens t work. Change sheet names of course to suit. Sub Move() on error goto errhandler dim ws as worksheet set ws = thisworkbook.worksheets(...

Excel: Conditional Formatting., sheet c, excel workbook

sheet c, excel workbook, spread sheets: Start with a new Excel workbook. Name the first sheet DAILY and the second sheet C-CERT. Follow my instructions from the original answer, and range A6:D6 of DAILY will get yellow fill conditional format. If you can t follow these instructions, please ask...

Excel: Conditional formula in Excel 2003, conditional formatting, cell backgrounds

conditional formatting, cell backgrounds, time note: Peter, Conditional formatting supports three different conditions. so you would do select the date cells (assume B2:B500 with B2 as the active cell) then in the menu do format= conditional formatting chance cell value is to formula is and put...

Excel: Convert a number to text, target value, sheet tab

target value, sheet tab, value case: You can t enter 5 and have it show Rams without using VBA. (Actually, you could for up to 3 different values, but you clearly have more than 3 here) Right-click the sheet tab, select View Code, enter this (and you ll have to put in the other code substitutions...

Excel: Copy data entry form into a master databse, database range, c nr

database range, c nr, input sheet: I can answer any specific question you have about the code you ve written/write. A couple hurdles I see are: 1) spot the next empty row in the database to enter data To find the next empty row, assuming column A is always used for each row of data,...

Excel: Copying fields if >0, bill of lading, aa1

bill of lading, aa1, aa2: In the sales sheet out to the right in the unused columns you could add a helper column to flag which rows have quantities added and to index those rows with incrementing numbers. Let s assume your QTY column is column B, and out in column AA in AA2 you...

Excel: Countdown timer for Excel 2007, countdown timer, allexperts

countdown timer, allexperts, many thanks: Hi Owen, Are you referring to this one: http://en.allexperts.com/q/Excel-1059/Countdown-Timer-Cells-Excel-1.htm If so, did you perhaps overlook the factt hat there are tow VBA items needed: - a Class module - a normal module The post indicates...

Excel: countif problem, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Umesh, Click or Copy and paste the below link in web browser to download the Revised Example file. https://docs.google.com/leaf?id=0B2TMxNyEPQ2rMWUzNzRlNjUtODJkOS00OGUzLWEzZWEtZTZjZGFjOTVjMWE2&sort=name&layout=list&num=50 Feel free to post a follow-up,...

Excel: countif problem, column c, ogilvy

column c, ogilvy, b2: Umesh, assume 23-Sep-11 is in A2 and Jack is in B2, then in C2 put in this formula =IF(COUNTIF($B$2:B2,B2)=1, , revisit - &COUNTIF($B$2:B2,B2)-1) and drag fill down the column. It produced the results you show for me. -- Regards, Tom Ogilvy...

Excel: =COUNTIF question, result changes, cell reference

result changes, cell reference, richard florida: Jay Maybe this will work for you Use a formula that references a cell as the criteria. Then copy the formula down thus changing the cell reference in the formula (because of relative referencing) and in each of the subsequent cells of the referenced criteria...

Excel: Create an application file, empty workbook, owb

empty workbook, owb, lct: Hi Vials, Something like this perhaps: Sub CreateFile() Dim oWb As Workbook Dim lCt As Long Prepare,Ranges,Launch,List,Index MST,PCV,LCV,TCV,JBX,SWH,TPT,PRT,LVT,GAG,FLE,RTC,AAA,BBB,DCS Add empty workbook with one sheet...

Excel: How to create a sort list, must be a way, specific city

must be a way, specific city, list of cities: You can create a sort sequence, and once you sort by this sequence, the unwanted ones would sort to the bottom where you can easily remove them. Assuming the list of wanted cities are in A1:A75, you select A1:A75, then use custom lists (details when I know...

Excel: Create test schedule, test duration, public holidays

test duration, public holidays, analysis toolpak: HI Lee, Excel has a built-in function that can handle holidays (which you can put into a separate list somewhere in your sheet) called NETWORKDAYS. Look up in Help. IN Excel 2003 and older, this function is part of the Analysis toolpak addin, in Excel 2007...

Excel: Creating cells with either-or results and no circular references?, data validation, circular references

data validation, circular references, blank cell: I m assuming that the drop down lists are datavalidation, which makes it a little tricky - because I d use data validation to stop the entries in all three cells =AND(ISBLANK(B1),ISBLANK(C1)) entered in A1, which the ignore blank unchecked, would NOT...

Excel: data connection, web button, excel spreadsheet

web button, excel spreadsheet, usa culture: email address is aidan.heritage@virgin.net - no problem with screen shots! data ribbon far left, use the from web button in address box enter the URL click enter/hit ok select the data table you want (the url you gave currently gives an error) click...

Excel: Data Copy, cell model, ra1

cell model, ra1, string set: Fareed, This should do what you want Sub copydata() Dim sh As Worksheet, sh1 As Worksheet, rA As Range, rB As Range Dim rA1 As Range, rB1 As Range Dim sA As String, sB As String Set sh = Worksheets( SheetNew ) sB = sh.Range( B1 ) sA = sh.Range(...

Excel: Last Data Entry in Row, conditional formatting, golf handicap

conditional formatting, golf handicap, ogilvy: Glen, You can do what you ask. There are several ways and not everyone agrees on the best. I think you will be using numeric numbers, so this should work =INDEX($B5:$Z5,0,MATCH(1E307,$B5:$Z5)) to return the number. =Match(1E307,$B5:$Z5) or...

Excel: Data sorting in Excel, standard industry classification, excel spreadsheet

standard industry classification, excel spreadsheet, code column: Wing, I will make the assumption that the SIC codes are numeric in nature such as 2411. If that is the case, then I suspect that the ones you entered by hand are being stored as numbers and the original codes are being stored as text values. Excel...

Excel: Getting Data into Userform ListView, listview control, column listbox

listview control, column listbox, subject marks: Paparazzi, You use the term listview and the term listbox. I dont see anything you are doing that would require a listview control so I will assume you are using a listbox. You could us a two column listbox and then just use additem and the list property...

Excel: Data Validation using Excel 2003, cell a1, validation check

cell a1, validation check, data validation: Jim, well, you can t type in your list directly in the data validation by choosing the list option. You can put your list on the same sheet in another location (for this demo, in AZ1:AZ20 - I used a list of 20 text values, but you get the idea). then...

Excel: Date Calculations w/in Conditional Formulas, alpha character, datevalue

alpha character, datevalue, cell address: Cathy, by including the cell address (U9) of the cell with the conditional date within the quoted string, you are not referring to the cell with the date - are making the comparison literally to the cell address as a string value. Try it this way (which...

Excel: De-duping a car dealership customer list, zip home phone, column titles

zip home phone, column titles, city state zip: * do you have Access? the easiest way to do this is in Access. it s manual , but works real easy. Here is the concept. I do this occasionally. dump all your data in a simple access table. Now, with the table closed, highlight the table in the...

Excel: Per Diem Calculations Response, format a1, format a4

format a1, format a4, sum formula: Format A1 and A2 as date/time. Format A4 as number with 2 decimal places. In A1:A2 enter 9/12/11 9:00 PM 9/14/11 8:00 AM In A4 enter =ROUNDUP((INT(A1+1)-A1)*4,0)/4+INT(A2)-INT(A1)-IF(INT(A1)=INT(A2),ROUNDDOWN((INT(A2+1)-A2)*4,0)/4,1-ROUNDUP((A2-INT(A2))*4,0)/4)...

Excel: Dropdown Option, b1 and b2, source dates

b1 and b2, source dates, data validation: First, I would expect there to be two drop downs, one to indicate the beginning date, and another to indicate the ending date. Then the macro would leave all the columns visible within that range, hiding the others. 1) Move the FINAL RESULT to B4. 2)...

Excel: Dropdown Option, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Bill, Request you to attach a sample file in any of the below data sharing websites and provide me the link to give you the exact solution. Windows Live Skydrive: http://skydrive.live.com MediaFire: http://www.mediafire.com FileFactory: http://www.filefactory.com...

Excel: enter in a numerical value that returns text, text result, number 0

text result, number 0, digit code: Justin, Vlookup is the correct function. It sounds like you have a mismatch on data types. If I look up the number 123 and column A contains the string equivalent of 123 , then the match won t be made. You must lookup 123 to get your results....

Excel: Excel 07 Macro To Find Non Duplicates, zero values, array

zero values, array: I would do this with formulas personally - a COUNTIF formula will enable you to get the entries that are not in one or the other list =countif(ColumnA,Bcell) replacing ColumnA with the actual range of A, and BCell with the cell the row is on Any zero...

Excel: Excel 2003, cell a1, version thanks

cell a1, version thanks, company symbol: I have no idea why you can t fill down. No reason you shouldn t be able to. Once you can get past that hurdle, wherever you see the word different , then that cell indicates a difference... for example, in the 3rd sheet if you see the word different in...

Excel: Excel 2007, data validation, vlookup formula

data validation, vlookup formula, cell c3: Aman Usually the way I would handle this would be to create a code based on the options that have been selected. For example Assume we are using our list to look up and classify houses(I don t have a clue if this is right because you didn t include...

Excel: Excel 2007; dependent combobox help, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi James, Have a look in the below link for more detail. http://www.contextures.com/xldataval02.html Feel free to post a follow-up, if your requirement is not fulfilled. Don t forget to rate the posts, since feedback is important. --- Sixthsens...

Excel: Excel 2007 Macro, cell b3, empty cells

cell b3, empty cells, false application: This should do it: Option Explicit Sub Merger() Dim LR As Long, Col As Long Dim AR As Range LR = Cells.Find( * , Cells(Rows.Count, Columns.Count), _ SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row On Error Resume...

Excel: excel, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Imran, For your First Query:- Follow the below link and download the MoreFuction Addin and save it in your Local Drive. https://docs.google.com/leaf?id=0B2TMxNyEPQ2rOTE4MjEwZTgtYjk5OC00MmZhLWJkMTktMjhjMWFkZWU5Mjc4&hl=en_US Now run the setup....

Excel: Excel, cell b2, cell a2

cell b2, cell a2, cell a1: Gloria I am not exactly sure wht you are doing to fill the column, but here is a formula that will automaticaly insert the number when you add something to the next cell. =IF(B2 0,+A1+1, ) The formula is in cell A2, it makes reference to cell B2...

Excel: Excel check mark in drop down, wingdings font, color rgb

wingdings font, color rgb, design mode: It requires a fair amount of setup. For each ActiveX combobox, you need to make the font Wingdings, have the listfill range point to 2 cells, say K1:K2, which consist of =CHAR(251) and CHAR(252) and both using Wingdings font, and you need to pick a linked...

Excel: excel combo box, excel combo box, reference column

excel combo box, reference column, employee id: You need to combine them in another cell then use that in the combobox. If you have Employee ID, Salary, Departments in column A,B,C, then in som available column, say J, you need to put this in J2: =A2& &B2& &C2 then fill down then reference column...

Excel: Excel for consignment shop, consignor, richard florida

consignor, richard florida, software options: Dina You can develop a spreadsheet on which you can keep track of the consignors, the items consigned by each consignor,the amounts of the sales by consignor and the amounts due to each consignor. Of course if I developed the spreadsheet for you it...

Excel: Excel Copy Question, blank row, blank spaces

blank row, blank spaces, team names: Mike R. Put this formula in Row 10 on sheet 205 =IF(ISERROR(INDEX( 203 !$F:$F,SMALL(CHOOSE({1,2,3,4,5,6,7,8},IF( 203 !$F$4 ,4),IF( 203 !$F$7 ,7),IF( 203 !$F$10 ,10),IF( 203 !$F$14 ,14),IF( 203 !$F$18 ,18),IF( 203 !$F$22 ,22),IF( 203...

Excel: Excel drill down to pivot table data issue, vlookup, direct email

vlookup, direct email, raw data: The vlookup, or rather excel, is functioning as designed here - you are seeing the dependant cells in the formula - I m not clear why the lookup is used as a sumary when the pivot table would seem to already be a summary - if you can clarify I m happy to help...

Excel: Excel Flag, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Moazam, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. Click and Download the File from the below link:- https://docs.google.com/leaf?id=0B2TMxNyEPQ2rZGU4MDEzMDctMDFlZC00ZDU4LTg2ZjMtZTQzZTQ0MTI1NDZk&hl=en_US...

Excel: Excel Formula.., shashikala, sahana

shashikala, sahana, saroja: There are a number of useful tutorial sites (search for excel 2007 pivot table) but this one looks quite friendly http://www.homeandlearn.co.uk/excel2007/excel2007s7p7.html This has pictures which I cannot easily include in an answer, hence the referral...

Excel: Excel formula in days and by category, ticket dates, excel formula

ticket dates, excel formula, excel spreadsheet: You could use the NETWORKDAYS function to determine the number of business days between your two dates. Then use an IF function to determine whether or not each of those numbers falls within your goals based on the category. Then just add those up with a COUNT...

Excel: Excel - Function for time format, world time zones, different time zone

world time zones, different time zone, timecell: To excel, time and date is simply a series of numbers - with 1 being 1 January 1900, 2 being 2 January 1900 etc. Time is a fraction of a day, so 12 hours is .5, 6 hours is .25 etc - SO to add 2 hours you would simply add 2/24 (which factors down to 1/12)...

Excel: Excel - increasing an average, cell b2, mathematical terms

cell b2, mathematical terms, generalities: In cell A2, enter 7 (current rating). In cell B2, enter 4 (# of submitted ratings). In cell C1, enter 8 (to get to rating of 8 ). In cell D1, enter 9 (to get to rating of 9 ). In C2 enter =$B2*(C1-$A2)/(10-C1) and copy formula from C2 to D2. ...

Excel: Excel Inventory Mgmt, widget name, clean slate

widget name, clean slate, inventory mgmt: * As far as I am concerned, there are 2 general ways to approach this. Probably best to see what you are comfortable with before proceeding. And then I have one other general thought, and that is, for this need, use Access, not Excel . But anyway, back...

Excel: excel linking with source sheet, corporate planning department, source sheet

corporate planning department, source sheet, term deposits: This is a solution to what you asked for, but what you asked for only copies one row from sheet 1, and pastes it into ROW 1 of the correct sheet. Is that what you want? If so: Right-click the tab of Sheet1, select View Code, enter this: Private Sub Worksheet_Change(ByVal...

Excel: excel linking with source sheet, target data, branch profile

target data, branch profile, source sheet: If the target data is multiple sheets, then this is (as far as I know) going to need VBA to achieve - I can write a suitable series of VBA statements, but not without knowledge of the source and target data structures. If it helps for any follow ups my...

Excel: Excel Macro, blank cells, empty cells

blank cells, empty cells, excel macro: Untested: Sub Test3() lrow = ActiveSheet.UsedRange.Rows.Count - 2 On Error Resume Next Application.DisplayAlerts = False For Each ar In Range( A3 ).Resize(lrow).SpecialCells(xlCellTypeBlanks).Areas for Col=0 to 16 With...

Excel: Excel Macro to Merge Empty Cells, empty cells, excel macro

empty cells, excel macro, ken ken: Ken, If I understand the requirement correctly, this worked for me: Sub MergeCells() Dim cell As Range, r As Range, r1 As Range, r2 As Range Dim ar As Range For Each cell In Range( A2:Q2 ) Set r = cell.Resize(66, 1) Set r1 = Nothing On...

Excel: Excel pie charts, card stock paper, pipe chart

card stock paper, pipe chart, excel center: I don t understand how would I make the transfer? I don t know of a problem with off-centered pipe chart numbers in Excel 2010, but I don t know of a problem with earlier versions either. You don t convert from 2003 to 2010 Excel -- you buy Excel 2010...

Excel: Excel Problem, major stumbling block, aidan

major stumbling block, aidan, formatting: Either is fine - direct does allow the attachment of files (which you have done!), via here gives you tracking (which you also have effectively). As regards the question, I think it can ONLY be done via a macro, but even then I m not certain it will be...

Excel: Excel IF statement, f13, column c

f13, column c, d13: Steve I thought I answered this but it is still here so I will try again. I think your QA manager did more than plug in some metric numbers. The numbers you provided above all work fine for me in the original formula. Check to make sure a formula was not...

Excel: Excel Substraction, Sixthsense

Sixthsense: Hi Uos, Place the cursor in Sheet3 A1 cell and press = and take the mouse pointer to sheet2 A1 cell (by using keyboard after pressing = press Cntrl+Pageup go to the previous sheet and use Arrow keys to go to A1 cell) and type minus sign in keyboard that...

Excel: Excel - Transparent "Sheet" to achieve auto form fill, workbook sheets, source sheet

workbook sheets, source sheet, transparent sheet: Pete, -------------- thanks for the feedback. However, your approach is so looney that I would never suggest that to anybody. If it works for you, great - but putting a picture on top of the cells so the user has to use the arrow keys to fill it in underneath...

Excel: Excel VB: Macro not working., target range, excel vb

target range, excel vb, ab1: Polo, this worked for me: ===== See revised comment below... Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error GoTo ErrHandler If Not Intersect(Target, Sh.Range( F:F )) Is Nothing Then Set r = Intersect(Target,...

Excel: EXCEL VBA CODE, excel vba code, microsoft site

excel vba code, microsoft site, office microsoft: Hi chakravarthy, The link below is from Microsoft site and allows you to choose many options for learning Excel. I am not experienced in VBA code so I don t know if more comprehensive sources are available. http://office.microsoft.com/en-us/excel-help/...

Excel: Excel - working with half cent formulas, irs reimbursement rate, richard florida

irs reimbursement rate, richard florida, decimal places: Noreen When I tell you you are gonna say why didn t I think of that. You enter it as .555. If its formatted as currency with 3 decimal places it will appear as $0.555. Now I know you said why didn t I think of that, and I m guessing that you already...

Excel: Excel2007-filter-unlocked cells-no vba, autofilter, aidan

autofilter, aidan, cells: As long as the auto-filter is set (to show the drop down choices) BEFORE protecting the sheet, you will be able to use it as long as you check the box to allow the use of the autofilter - but you won t be able to add the auto-filter (as far as I know) to a...

Excel: How to exclude public holidays in weekday function, necessary logic, holiday range

necessary logic, holiday range, public holidays: Lee I would have a list of public holidays somewhere that it can be updated. Then I would add to your IF statement the necessary logic, including a ISERROR(MATCH(date,holiday range, false)). I don t completely understand what your if statement is doing...

Excel: Exctracting string in cell, string answer, cell a1

string answer, cell a1, carvalho: Hi Rush How are you? Please write do the following: 1. Type your string in A14 2. Type in B14 =LOCATE( X ,A14,1) 3. Type in LOCATE( Y ,B14,1) 4. Type in =SEG.TEXTO(A14,B14+1,C14-1) Later you can merge all the formulas in one Cheers Adelaide...

Excel: Extract and display multiple text, separate by commas, cat table, colour shape

cat table, colour shape, shape size: yes, there is definitely a limit to how far your formula can stretch with this custom manual testing of every possible variation. The only idea i have that might help your adjectives not have the false positives is to include the comma in the search test....

Excel: Extracting a part of value from a cell, cell b3, excel formula

cell b3, excel formula, a3: I did this as follows: In Cell A3, A4, A5 : your codes In these cells: B1: X B2: Y C1: Y C2: W D1: W D2: V E1: V E2: Z F1: Z F2: P In cell B3 I entered this formula: =MID($A3,FIND(B$1,$A3)+1,FIND(B$2,$A3)-FIND(B$1,$A3)-1) Copy right...

Excel: Excel 2003, baw

Excel: Excel 2003, baw

Excel: Excel - Transparent "Sheet" to achieve auto form fill, glass2k, soft solution

Excel: Excel - Transparent "Sheet" to achieve auto form fill, glass2k, soft solution, bacground

Excel: Multiple word search in excel, false positives, e4

Excel: Multiple word search in excel, false positives, e4, substantial number

Excel: Searching for textboxes using vba, google search, ms courses

Excel: Searching for textboxes using vba, google search, ms courses, case text

Excel: Show value on tiered information., aidan, spreadsheet

Excel: Show value on tiered information., aidan, spreadsheet, heritage

Excel: Fill Blanks, r1 r2, exact range

r1 r2, exact range, range selection: Paul, try this Sub FillBlanks() Dim r As Range, r1 As Range, r2 As Range Set r = ActiveSheet.UsedRange If r(1).Row = 1 Then Set r = r.Offset(1, 0).Resize(r.Rows.Count - 1) End If Set r1 = Range( A:E,G:G ).EntireRow Set r2 = Intersect(r, r1) r2.Select...

Excel: Fill up a range, rcell, ogilvy

rcell, ogilvy, cells: Jun, this worked for me Private Sub CommandButton1_Click() Dim rw As Long, r As Range, cell As Range, cnt As Long rw = 2 Set r = Range( A2 , Cells(Rows.Count, A ).End(xlUp)) For Each cell In r cnt = cell.Offset(0, 1).Value Cells(rw, C ).Resize(cnt,...

Excel: Same filter in two work sheets, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Senthil, Just refer the sheet1 to start date to Sheet2 Start Date cell. For example assume that in Sheet1 the Start Date Data is in F1 cell. Now assume that you would like to refer that Sheet1 F1 cell data in Sheet2 A1 cell. Place the cursor...

Excel: Filtering, direct email, examination results

direct email, examination results, aidan: it sounds as though it needs an array filter - did you want to display MULTIPLE dates? See http://en.allexperts.com/q/Excel-1059/2011/2/Filters-Drop-selections.htm for a starting point aidan.heritage@virgin.net if you need my direct email for any reason...

Excel: Find common values in 2 columns only, format button, home tab

format button, home tab, common values: Deepak, ============ revision/added information ========== since you are not having success, this page at Chip Pearsons site talks about a similar although not identical situation. It has pictures and a much more information. It might help you to understand...

Excel: Finding duplicate values in 2 columns only, conditional formatting, b1000

conditional formatting, b1000, cell a1: I would use Conditional Formatting to accomplish this. Let s assume the columns are A and B. 1) Highlight A1:B1000 (or however far down is needed). Just make sure cell A1 is the primary cell highlighted. 2) Apply the following conditional formatting...

Excel: Finding duplicate values in 2 columns only and not rows, conditional formatting, countif

conditional formatting, countif, common values: Deepak That is something you can do pretty easily with Conditional Formatting. You insert a condition based on a formula and the formula will be something like =COUNTIF($B$1:$B$6,A1) 0 where you are counting occurences of the values in column A in...

Excel: Finding duplicate values in 2 columns only and not rows, vba programming, common values

vba programming, common values, color c: You can use the MATCH function to determine if a value in column B appears in column A. I cover that in this class: http://599cd.com/site/courselist/excel2010/expert/x4/?key=AllExperts It would look something like this: =MATCH(B1,A:A,0) That will...

Excel: Finding duplicate values in 2 columns only and not rows, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Deepak, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. Click and Download the File from the below link:- https://docs.google.com/leaf?id=0B2TMxNyEPQ2rOWMwNjQ5OTctMWRjMi00MjgyLTg5YTgtNTIzY2NiMDdkZTAw&hl=en_US...

Excel: finding the lowest/highest number in a row or column, richard florida, florida usa

richard florida, florida usa, a1: Brian The following will find the smallest or the largest number. to find the largest =SMALL(A1:A100,+COUNT(A1:A100)) to find the smallest =LARGE(A1:A100,+COUNT(A1:A100)) No I didn t put the formulas in in reverse, the formula to find the smallest...

Excel: finding a sheet, target value, sheet names

target value, sheet names, list of names: Billy The code I provided goes in the sheet module. Right-click on the sheet name, then click view code and paste the code there. If your sheet names are in column L then you need to change Columns(12) to Columns(12). If there is a sheet with the name...

Excel: How to format cells of a pivot table based on the row and column labels, pam pam, format cells

pam pam, format cells, column labels: Pam, it doesn t sound like your row and column headers would change. So if you just mean that the values in the value area of the table will update, then I don t think you would lose your conditional formatting. When I look at your formatting criteria,...

Excel: Formatting a chart, vertical bar chart, horizontal axis

vertical bar chart, horizontal axis, vertical axis: It s really not enough information (even w/the picture, sorry) to answer your question fully. I can t really tell which data you want on the horizontal axis, and I m not familiar with what your data is. But generally speaking, if you have 2 columns of...

Excel: Formula for adding dates, countif, pivot table

countif, pivot table, spreadsheet: I d have a cell which had the formula =repairdate-day(repairdate)+1 where repairdate is the cell with the repair date - this will return the first of the month the repair was done in, and you can then do a countif or a pivot table to get the numbers...

Excel: formula to copy and modify a portion of text, cell b1, cell b2

cell b1, cell b2, cell a2: Let s create a table off to the right and use that to lookup your code values. That way they re easy to edit. In E1 put AB, in E2 put CD, in E3 put EF, in E4 put GH, etc... In F1 put Penguin, in F2 put Seal, in F3 put Walrus, etc... With your text...

Excel: formula being over-ridden, conditional formatting, number 0

conditional formatting, number 0, speculation: * there isn t any way in the world for me to answer this question except with speculation. do you mean the formula actually isn t there and the number 0 is instead, or do you mean the formula is still there, and it displays 0. either way, and even...

Excel: Formula or VBA to list items pulled from multiple Excel sheets, vba code, array formula

vba code, array formula, double application: Tom, same limitation as yesterday. Formulas can t read the strikethrough unless you build a custom formula. You could build a custom formula that you put in each row next to column H (or in some other column) that returns true or false if column H contains...

Excel: IF AND FUNCTION, decimal separator, question pool

decimal separator, question pool, semi colon: Esther Cabezos, You didn t ask me this question, I found it in the question pool. It looks like you are very close to having the answer yourself - you just have some formula construction issues. In US English, this modification of your formula worked:...

Excel: now function, mmam, time clock

mmam, time clock, spread sheet: Jimmy, Your original code used the Now() worksheet function ActiveCell.Formula = =now() which produces the date and time. If I put =now() in a cell then I get 9/26/2011 12:27 My code does the same ActiveCell.Value = now() the cell...

Excel: IF and AND function to handle blank fields, m19, blank fields

m19, blank fields, allexperts: The formula worked fine for me, so it s probably a language issue. If you started a new workbook, enter =A1:D1 in cell A1 , then highlight the formula in the formula bar and press F9, what do you see? In English it s ={0,0,0,0} and it s those curly braces...

Excel: Hidding/Unhiding Spinners in Excell, spinners, excell

spinners, excell, spinner: * I am sorry, but the best answer/advice I can give you is, don t use activeX controls embedded on a worksheet. I love VBA and use it constantly, from small ways to large ways, but I have stayed away from embedeed controls on a worksheet entirely. They...

Excel: IFAND, first test

first test: Your formula looks almost right, but you have the structure slightly wrong =IF(AND(B19= YES , B20= YES ), -1, IF(AND(B19= YES , B20= NO ), 0, IF(AND(B19= NO , B20= NO ), 1, IF(AND(B19= NO , B20= YES ), 0.5, )))) which says IF the first test is true,...

Excel: Increment Row Numbers in a 'SUM' using VBA, iloc, sum formulas

iloc, sum formulas, cell reference: Seb, you have defined a situation, but I don t know what you are asking me - I don t know what your question is. You have shown one sample file but talked about spending a whole day creating a summary table - there is no way I could even suggest how...

Excel: Insert picture in excel sheet, open dialog, cancel button

open dialog, cancel button, rowheight: Juha, You can try this code. It should do what I understand you to want. To stop selecting pictures, click the cancel button in the file open dialog and the macro will stop.: It starts at A50. Change the code if you want to start in a different row....

Excel: inventory to shopping list spreadsheet, false values, inventory sheet

false values, inventory sheet, autofilter: Wanda, the autofilter is static - it filters at the moment you tell it to filter and then it stays that way until you re-apply the filter. You could use a macro to reapply the filter. (since it sounds like you don t want to do that manually). You...

Excel: issue with getting data to download, open filename, notme

open filename, notme, bk: Todd, that code had an error in it. Try this instead: Sub OpenandUpdate1() Dim fl As String Dim bk As Workbook Dim qt As QueryTable Dim sh As Worksheet ChDrive ThisWorkbook.Path ChDir ThisWorkbook.Path fl = Dir( *.xl* ) On Error Resume...

Excel: Limiting validated list items, conditional formatting, data validation

conditional formatting, data validation, teachers names: The data validation would have to use a list, which would not allow you to have a rule relating to the number of times a name is used. You COULD use conditional formatting to flag rows which break the rules you wish to apply - this doesn t STOP the rule being...

Excel: Looping through a variable amount, loop the loop, loop through

loop the loop, loop through, ogilvy: Hebron, in a for next loop, the loop limits are set when the loop is entered. So you are correct - you can t change the bounds. You would need a Do - loop type loop to do this Rows = 100 CurrentRow = 1 do while CurrentRow = Rows If Worksheets(...

Excel: Macro to Copy Non Blank Rows within A Range, source sheet, blank rows

source sheet, blank rows, spath: Yogi, Here is some untested pseudo code that should get you started. Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim rw As Long, r As Range, r1 As Range, cell As Range Dim r2 As Range, r3 As Range, sPath As String, sName As String Dim bk As...

Excel: Macro creating copy of a workbook, destination application, sheet1

destination application, sheet1, fname: Try this: ============ Sub Test() Dim fName As String Dim shName As String Dim newWB As Workbook Application.ScreenUpdating = False shName = Range( B2 ).Value Sheet1.Copy With ActiveWorkbook .Sheet1.Name =...

Excel: Macro to Enter current Date and Time., current date, plain english

current date, plain english, b1: * the code you posted isn t what you described, the code you posted has to do with column 18, not column 1 (a). Anyway, here s the answer to your question in plain english, you can tweak to suit coding needs. This does literally what you described...

Excel: Macro for Filtering based on special criteria, crew change, drill ship

crew change, drill ship, offshore workers: Diogo, Your picture is too small to make out much detail. Select the column with the E and D s then run a macro like this (sheet should not be filtered - all rows should be visible and in the column with the data, you have all the rows selected. Change...

Excel: Merge Cells, empty cells, excel macro

empty cells, excel macro, headings: Yes, sorry; 2nd Next should have been End Select. I had understood you said that I,L,N,O,P,Q had no data, so I didn t include them in the macro. Try this: Sub Merger() lrow = ActiveSheet.UsedRange.Rows.Count - 2 On Error Resume Next Application.DisplayAlerts...

Excel: merging spreadsheets, caseload, case number

caseload, case number, daily basis: You could do it that way. There is a REMOVE DUPLICATES feature in Excel 2010. Something like this would be much better handled in a database, like Access, however. If you have a unique case number, you could just exclude those cases when you import the new...

Excel: min if statements, array formula, zero values

array formula, zero values, number present: =MIN(IF(F343:F345=0,99999,F343:F345)) says take the minimum value from the following list IF the value in F343 to F345 is zero, use 99999, otherwise use the value from F343 to F345 In other words, it doesn t ignore the zeros, it converts them to 99999,...

Excel: Moving contents of a cell, current date, accounts receivable

current date, accounts receivable, b2: Hi Jerry, I m not aware of a formula that will move the contents to another cell without a macro, but an IF THEN formula could probably accomplish the task. Assuming Today s Date is A2 and the invoice date is B2 and amount is C2. In D2 for 0 to 30 days....

Excel: moving data from multiple worksheets to 1worksheet, excel spreadsheet, microsoft excel

excel spreadsheet, microsoft excel, dashes: Hi Brian, I think something has gone wron with pasting in the code. Below is the code (below the dashes). Copy all of it and paste it into the module, after emptying that module. ------------------------------------------ Option Explicit Dim msSheet...

Excel: moving data from multiple worksheets to 1worksheet, microsoft excel, len

microsoft excel, len, error message: I think something has gone wrong with pasting in the code. Empty the module you have and replace its contents with what is below the dashe: ----------------------------------------- Option Explicit Dim msSheet As String Dim msRange As String Sub GetDataFromFiles()...

Excel: moving data from multiple worksheets to 1worksheet, visual comparison, sum function

visual comparison, sum function, d17: Something like this perhaps? Sub GetDataFromFiles() Dim lCount As Long Dim vFilename As Variant vFilename = Application.GetOpenFilename( Microsoft Excel files (*.xls),*.xls , , Please select the file(s) to open , , True) If TypeName(vFilename)...

Excel: Multiple word search in excel, column c, trues

column c, trues, a10: Let s say your keywords are in Sheet3!A1:A10. In an unused column in Sheet1, enter this formula in row 1, but enter via ctrl+shift+enter: =ISNUMBER(MATCH(TRUE,ISNUMBER(FIND(Sheet3!$A$1:$A$10,C1)),0)) Wherever there is TRUE, the text in column C has a keyword...

Excel: Need to modify code to recognize fields with text data, microsoft sql server, blank version

microsoft sql server, blank version, vendor systems: Pam, I don t know of a formula that will do what you want. I assume you were getting the number of unique values for each version using the formulas from yesterday and then comparing if they were equal. I am not sure that is reliable or even you numeric...

Excel: need to resolve runtime error 1004, problem occured, macro code

problem occured, macro code, false flag: That s a non-trivial step up in question difficulty, several orders of magnitude more difficult and several orders magnitude less specific. You can see the difference between your first question ( How do I correct this code I use to do the thing it was intended...

Excel: Nested Level, j2, microsoft excel

j2, microsoft excel, j1: If you save-as your file to a native Excel 2007 format, you re allowed to nest more levels. However, if you need more than 7 levels of nesting, pretty often there is a better function or method to achieve your goal. This function does the same: =INDEX(J2:U2,MATCH(H2,J1:U1,0)...

Excel: Non-Military Time to Tenths of Hours, e11 e12, conversion chart

e11 e12, conversion chart, military time: Format range E11:E12 as Time. Format E14 as Number with 1 decimal. In E11:E12, enter 11:00 AM 4:26 PM In E14, enter your formula: =IF((OR(E12= ,E11= )),0,IF((E12 E11),((E12-E11)*24)+24,(E12-E11)*24)) The time difference is 5 hours and 26...

Excel: number comparison returning false when should be true, numeric value, vba macro

numeric value, vba macro, version ms: Mike, I can t look at your sheet - so I don t know how your numbers are stored. But is sounds like one cell is stored as numbers and one cell is stored as text strings that look like numbers. If that is the case, you can use a construct like the below...

Excel: organising a numbering system from a master copy, cell b2, furniture company

cell b2, furniture company, numbering system: Press Alt/F11 Doubleclick ThisWorkbook on the left side in the Project Window. Paste this code in: Private Sub Workbook_Open() Sheets(1).Range( B2 ).Value = Sheets(1).Range( B2 ).Value + 1 End Sub This assumes the purchase order # is in the first...

Excel: Parsing strings with VBA, income dividend, vba code

income dividend, vba code, isub: In cell A1 enter 03/22 Income dividend.127 $7.41 125.97 0.059 58.422 Select cell A1 and run following procedure Sub tokens() Dim orig As String, bStr As String, oneChar As String Dim tokens(6) As...

Excel: Pick out data in column 'B' according to its description in column 'A.', question pool, mba programs

question pool, mba programs, msis: (question found in the question pool) 2 methods spring to mind - the first would be a pivot table, which would give you the easiest analysis, bringing all the data into one place. The second would be to use the COUNTIFS function which allows multiple...

Excel: Populate Data in Several Excel Sheets Using a Given Template, string fname, outpath

string fname, outpath, inpath: CODE 1 - the code looks correct. From my previous post, I instructed: You don t have to just let it run , you can use F8 in the VBeditor to step through this macro one line at a time and check what s happening at each event to make sure it does what...

Excel: IF Problem, c11, ogilvy

c11, ogilvy, key words: Alison, this is set up to be entered in B2, then drag fill down to B3. It worked for me. =TRIM(IF(ISNUMBER(SEARCH(C5,A2)),C5& , )&IF(ISNUMBER(SEARCH(C6,A2)),C6& , )&IF(ISNUMBER(SEARCH(C7,A2)),C7& , )&IF(ISNUMBER(SEARCH(C8,A2)),C8& , )&IF(ISNUMBER(SEARCH(C9,A2)),C9&...

Excel: Recording iterations of a call date by updating a single cell, target value, time duration

target value, time duration, person change: Paul, Sorry for the delay, but the code doesn t write itself. If this isn t what you want, then send me a sample file explaining what you want where to twogilvy@msn.com Replace Results1, Results2 and so forth with your text entries. I put in a hyphen...

Excel: How can i reference cells in a particular worksheet depending on the Worksheet names, cell a1, sheet names

cell a1, sheet names, reply thanks: SWAPNA The formula looks like this =JAN 11 !A1+ FEB 11 !A1+ MAR 11 !A1 of course I have referenced cell A1 in the above formula and that may not be the cell you wish to reference, but you can change it accordingly. additionally you will want to...

Excel: Reminders in Excel, birthday reminder, reminders

birthday reminder, reminders, maturity: I m not sure what you mean by off-line? I googled for a bit and came across these: http://www.memotome.com/ http://www.remindeo.com/ http://www.freminder.com/ Maybe these help? ANd an Excel version: http://chandoo.org/wp/2010/10/26/birthday-reminder-template...

Excel: Remove spaces in cells after some text, richard florida, hari hari

richard florida, hari hari, florida usa: Hari Assuming you have a column of text that has spaces after the text that you want to leave. An easy way would be to use the text to columns feature. First paint or select the column where you want to eliminate the spaces behind your text. Next go...

Excel: Removing month out of a date field function needed, donna donna, ogilvy

donna donna, ogilvy, a1: Donna, A1: =today() B1: =Text(A1, mmm ) this would return Sep or B1: =Text(A1, mmmm ) this would return September or b1: =Month(A1) this would return 9 for today s date these would be a start. It depends on what you want to know....

Excel: save time copy and pasting, columbs, time copy

columbs, time copy, column headers: Yes, it s possible, but i have a few questions • are these numbers (1131,...) the column headers in row 1 or are the column headers name , address , city , postal code ? • Where are these numbers? Might I find 1135 in cell H73, for example? and/or 1137...

Excel: Searching for textboxes using vba, line shapes, meaningful name

line shapes, meaningful name, text contents: #s 1 & 2 seem to be the same #3 I don t understand There are 2 separate macros -- first finds text inside a textbox, 2nd locates an object Sub Findtext() Dim FindWhat As String On Error Resume Next FindWhat = InputBox( Find what? ) ...

Excel: how to set formula:choose sheet3 if sheet2 not exist, johor malaysia, richard florida

johor malaysia, richard florida, question thanks: Rashid The following should work for you. IF(ISERROR(Sheet2!A1),+Sheet3!A1,Sheet2!A1)+IF(ISERROR(Sheet3!A1),Sheet4!A1,0)+IF(ISERROR(Sheet4!A1),Sheet5!A1,0)+IF(ISERROR(Sheet5!A1),Sheet6!A1,0) If any of a sequesnce of sheets is missing the formula will...

Excel: Shared workbook, Macro's, run time error, run time error 1004

run time error, run time error 1004, richard florida: TAMARA Sorry I am not a macro expert. I suggest you reask the question of another all experts person. Check the resumes for someone who is expert in macros and I am sure you can get a good answer. Sorry I couldn t be of more help. Richard Florida...

Excel: Show value on tiered information., percentage discount, sumif

percentage discount, sumif, tier 3: Not clear if you have one spreadsheet per customer (at which point it s a simple sumif) or if you have multiple customers, at which point it s a sumiffs (if you have excel 2007 or later, else sumproduct can be used to do the same). Additionally, do you need...

Excel: slowing down the update, pivottables, query table

pivottables, query table, pivot table: Todd, OK. Unless you are going to leave the workbook open, I would remove the update every two minutes. I don t know if that is part of the problem, but it might be. However, if you believe there is no problem now, then you would not need any changes....

Excel: Snaking columns on a page, excel code, sheet1

excel code, sheet1, half that amount: Mark, David McRitchie has a snakecolumns macro at his site. He talks about it at http://dmcritchie.mvps.org/excel/snakecol.htm and there is a link to the macro on that page, but it may not be obvious. Here is the link to the macro http://dmcritchie.mvps.org/excel/code/snakecol.txt...

Excel: sum a table with condition in vba, value india, mayank

value india, mayank, thanks in advance: Varun the basic code would be: Sub abc() dim cell as range, tot as double tot = 0 for each cell in Range( A2:A8 ) if cell.value = varun and cell.offset(0,1).value = India then tot = tot + cell.offset(0,2) end if Next msgbox Total...

Excel: Sum Values under merged cell, assembly schedule, visual result

assembly schedule, visual result, header row: Rather than working this hard, I d add another row below the Week 38 row, and put a set of formulas in that row that displays the correct week number for each column properly. Then you can use that row for your formula test. Once it is in place and working,...

Excel: table tennis, table tennis league, table tennis player

table tennis league, table tennis player, new game: I would need to know the complete layout of your worksheet. Send a sample file to me at bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what you need to do. P.S. I m also a table tennis player! Took a few lessons from the #1 US player - David...

Excel: Tracking an iterations of an event by updating a single cell, person change, sheet tab

person change, sheet tab, value date: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False With Cells(Target.Row, 256).End(xlToLeft).Offset(, 1) .Value = Date .EntireColumn.AutoFit...

Excel: Transferring information from a monthly schedule to a daily schedule, trivial functions, conditional formatting

trivial functions, conditional formatting, empty cells: After examining the sheet you sent, I can state pretty certainly that builtin Excel functions (formulas) would not be able to do what you want. There is no inherent color-based coding of any kind. Custom functions would need to be created for your project,...

Excel: Unique count based on criteria returning incorrect count, array formulas, empty cells

array formulas, empty cells, question thanks: Pam, Without knowing much about what you were doing, I would suggest using a pivot table perhaps as an intermediate step. You can also use adjacent column to do row by row determinations say next to column E you put in this formula in F8 =if(countifs($A$8:$A8,$A8,$E$8:$E8,$E8)=1,1,...

Excel: Userform code, code question, assignment statements

code question, assignment statements, meaningful names: assume you have Private Sub Workbook_Open() Userform1.show End Sub now it would be Private Sub Workbook_Open() application.visible = false userform1.show application.visible = True End sub use that pattern to adjust your existing code....

Excel: Using "selection", range selection, h6

range selection, h6, a6: Carry, Sub EFG() Dim ar As Variant, i As Long, r As Range, r1 As Range ar = Array( Client1 , Client2 ) For i = LBound(ar) To UBound(ar) With Worksheets(ar(i)) Set r = .Range( A6 ) End With Set r1 = r.Parent.Range(r, r.End(xlDown)) r1.ClearContents...

Excel: Validation, loop command, space key

loop command, space key, open application: Try this in the workbook Open event: Private Sub Workbook_Open() Application.OnKey , End Sub Leading spaces will not do anything. User CAN type any other space. That is, user can type: a b c d but cannot type a b c d -- the leading...

Excel: Variable Range, Variable Worksheet Sort Macro, macro execution, color sort

macro execution, color sort, keyboard shortcut: Sub Color_Sort() Dim WS As Worksheet, Stg As String Set WS = Worksheets( we-11-09-10 ) WS.Sort.SortFields.Clear Stg = Selection.Address WS.Sort.SortFields.Add(Range(Stg), _ xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color...

Excel: VBA Code, target value, columm

target value, columm, code question: Abdul jaleel, I believe this is what you want Sub copyData2() Dim r As Range, sh As Worksheet Dim r1 as Range Set r = Worksheets( Master ).Range( O5:O2000 ) Set sh = Worksheets( DLS ) set r1 = sh.UsedRange r1.offset(4,0).ClearContents for each...

Excel: VBA code for conditional coding, laptop 1000, compaq sales

laptop 1000, compaq sales, compaq laptop: I stay away from macros when normal formulas accomplish the same thing. You can use Sheet1 of File A to give you your totals. In C2 put a SUMIF() formula against the other workbook. =SUMIF( [File B.xls]Sheet1 !B:B, B2, [File B.xls]Sheet1 !D:D)...

Excel: VBA code to go to specific worksheet, input range, sheet1

input range, sheet1, search functions: What kind of listbox? Control Box Control Listbox? If so, what is the INPUT RANGE? What Sheet? What is the Cell Link? What Sheet? I m not a huge fan of Forms for this kind of thing. I typically use a Sheet1 navigation list with hyperlinks. It...

Excel: VBA coding!!, laptop 1000, compaq sales

laptop 1000, compaq sales, compaq laptop: you Sorry I am not an expert in VBA coding. I suggest tyou reaskt he question of another allexperts person. check the resumes for someone who is proficient in VBA and I am sure you can get a good answer. sorry I could nto be of more help. Richard...

Excel: VBA to move data, clear contents of cells, and place cursor in first cell for next entry, place cursor, sh2

place cursor, sh2, sh1: Joey Assume sheet Entry Form is the one you want to clear and selet a cell in. Public Sub StainMetrics() Dim sh1 As Worksheet, sh2 As Worksheet, v As Variant, i As Long, j As Long Dim rw As Long Set sh1 = Worksheets( Entry Form ) Set sh2 = Worksheets(...

Excel: vlookup in 2d, cell b2, array formula

cell b2, array formula, vlookup: Kevin B2: =vlookup($A2,TableA,column(B$1),False) will do that. Tested and worked fine for me. Select B2 and drag it down, then across. (or across and then down). In column B, the function column(B$1) will return a 2 which is used as the third...

Excel: Vlookup Formula, richard florida, florida usa

richard florida, florida usa, specifics: Meenakshi I do not have enough specific information to answer your question. For the details of how a vlookup formula works check the help feature in excel. Oherwise I will be happy to help but I do need some more specifics relative to your problem. ...

Excel: VLOOKUP with MIN, corresponding cell, employee numbers, vlookup

employee numbers, vlookup, employee names: Pam If the list of employee numbers is unique, then something like =VLOOKUP(MIN(emp number list column 1), emp number list, 3, false) where emp number list column 1 is the list of employee numbers and emp number list is the list of employee numbers...

Excel: Vlookup Multi search, sheet1, boolean value

sheet1, boolean value, multi search: It will be a bit messy, but =IF(NOT(ISERROR(VLOOKUP(A1&A4,Sheet2!$A$1:$B$4,2,FALSE))),VLOOKUP(A1&A4,Sheet2!$A$1:$B$4,2,FALSE),IF(NOT(ISERROR(VLOOKUP(A2&A4,Sheet2!$A$1:$B$4,2,FALSE))),VLOOKUP(A2&A4,Sheet2!$A$1:$B$4,2,FALSE),VLOOKUP(A3&A4,Sheet2!$A$1:$B$4,2,FALSE)))...

Excel: vLOOKUP multiple values, index formula, sheet1

index formula, sheet1, index values: The easiest way to do this is to add another column to your sheet1 to create UNIQUE index values based on your column A order numbers. So, in E2, for instance, put this formula: =IF($A2= , , $A2 & - & COUNTIF($A$2:$A2, $A2)) Copy that cell...

Excel: vLOOKUP multiple values, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Sampath, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. Click or copy and paste the below link to Download the File:- https://docs.google.com/leaf?id=0B2TMxNyEPQ2rYzZlMWQ2YmUtZTM2Yi00OWIxLTg3NDQtZjk4NmIyN2EyZmYx&sort=moddate&layout=list&num=50...

Excel: Words with number value, colour names, excel sheet

colour names, excel sheet, color names: You mentioned 5 colours and 6 values, but apart from that.. I d use VLOOKUP to translate the colours into a value - have a table containing the colour names in the first column and the equivalent value in the second. In my example, the table is in a1:b5....

Excel: words with values, vlookup, parent signature

vlookup, parent signature, signature line: vlookup would form the basis as it s the means of translating the colours into numbers - I m assuming you are entering the text of the colour in the cell. It s basically =average(vlookup(MondayColour,List,2,false),vlookup(TuesdayColour,List,2,false),etc,etc,etc)...

Excel: Working with changing prices per unit, data validation, column c

data validation, column c, basic knowledge: Because the column with the prices would most likely have more cells in it with formulas than I would want flattened. . I would only want to flatten some cells, so it seems to me it would simple to highlight the cells I want flatten, then COPY, EDIT PASTE...

Excel: How to write code for multi level commission payout, level referral, spreadsheet program

level referral, spreadsheet program, excel spreadsheet: Not enough information here to posit a suggestion. You can use the website in my profile to send me your file to examine. Something like this is greatly impacted by your layout and desired results. Mockup your desired results in your sample workbook before...

Excel: XL2007 , combine 2 worksheets, Excel Sixthsense Excel

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

Excel: Add Month to a new line, target address, cell a1

target address, cell a1, january february march april: Hudas, You would need to have a version of excel that fires the change event when a row is inserted. I don t recall when this feature was added, but I believe it was in Excel 2003. I tested this in Excel 2007 and it worked as I expected (based on what...

Excel: How to make Addin Expire, cell b1, document property

cell b1, document property, custom document: Hello again Raja, This time I m answering the question about expiration date. As I mentioned, this is more complicated. This is because you must save the date somewhere that the user doesn t have access to and therefore can t change or reset. There are...

Excel: How to make Addin Expire, path specification, library folder

path specification, library folder, vacation dates: Hello Raja, I apologize for my long delay in answering your question. I made a mistake in not setting vacation dates to prevent questions from being submitted while I was away. Regarding your Kill command, the reason why it is not working is that it...

Excel: Adding Cells that contain the result of formula, column c, sum function

column c, sum function, c27: The correct formula would be: =IF(B35= YES , 10, ) Notice I took the quotes off the number 10? NOTE: you do not need to do this at all. If you want to give 10 points for each YES answer in cells B27:B52, this formula alone will do this...

Excel: Adding cells containing the results of formula, column c, sum function

column c, sum function, c27: Try removing the quotes from around the 10 in the IF formula, does that help? PS (edition/revision to answer): The difference is that when you tell the IF formula to return 10 , the quotations are turning it into Text. When you just say, return 10, it...

Excel: Adding cells form multiple worksheets, thom, cells

thom, cells: Thom In excel 2007-2010 you are not limited to 30. I don t recall if there is a limit in prior versions. Write the formula like this =SUM(Sheet2:Sheet122!A1) Hope this helps Richard Balmy Florida (should be 78 degrees today) USA ...

Excel: Adding values across sheets, column c, tools group

column c, tools group, sq meters: It can, and I would suggest recording using the condolidate feature, then modifying the code to suit varying # of sheets. Look at Data tab, Data Tools group, Consolidate. In the consolidate dialog, check the Top row and Left column checkboxes to ensure the...

Excel: What-If analysis, max max, ogilvy

max max, ogilvy, max 2: Paul, the way you would need to do this is to generate every combination in your worksheet which would require 10,077,696 rows of data. since excel 2007 and later only have 1 million rows, you would need to make 10 sections to produce all the...

Excel: Assigning a word to a number, richard florida, apostrophe

richard florida, apostrophe, number 1: Chris The formula should refer to another cell and would look like this =If(A1=001, Lean Pro Matrix , ) The formula says if cell A1 has the number 1 in it then put Lean pro Matrix in this cell otheerwise put nothing in the cell. Part of what...

Excel: Assigning a word to a value, vba code, accurate results

vba code, accurate results, spreadsheet: try this formula: =if(A1= 001 , Lean Pro Matrix , ) you can use this anywhere EXCEPT do not use it in cell A1. if you want to check all of column A for 001 , try: =if(countif(A:A, 001 ) 0, Lean Pro Matrix , ) for most accurate results, enter...

Excel: auto fill dates/formulae, keyboard shortcut, blank row

keyboard shortcut, blank row, tom hope: Nissar, this worked for me: Sub autofill() Dim rng as Range autofill Macro Keyboard Shortcut: Ctrl+e Set Rng = Range( a65536 ).End(xlUp) Rng.autofill Destination:=Rng.Resize(2, 1), Type:=xlFillSeries End Sub -- Regards, Tom Ogilvy ...

Excel: Auto Generation of Invoice number, visual basic for applications, question thanks

visual basic for applications, question thanks, year 2012: Private Sub Worksheet_Change(ByVal Target As Range) Dim changedCells As Range Set changedCells = Intersect(Target, Me.Range( a1 ).EntireColumn) If Not (changedCells Is Nothing) Then Application.EnableEvents = False changedCells.Offset(0,...

Excel: auto increment when printing, auto increment, invoice number

auto increment, invoice number, printing copies: This isn t possible in Excel without some serious VBA programming. I would recommend using a database such as Microsoft Access to handle your invoices. If you have Access and want to learn how to get started with it, here s a free tutorial: http://599CD.com/X1F4Y...

Excel: Better way to write code..., data cells, wp data

data cells, wp data, target: Sure: Dim lCt As Long With Workbooks(Fname2).Sheets( All_WP_Data ) .Cells(pt_rdt, 9) = Workbooks(Fname).Sheets(onglet).Cells(14, pt_cd) DateEnd .Cells(pt_rdt, 10) = Workbooks(Fname).Sheets(onglet).Cells(pt_rd, 1) ...

Excel: Cell contents in Excel 2010, cell contents, richard florida

cell contents, richard florida, apostrophe: Nancy The problem is that the number really isn t a number to excel because of two things it starts with a zero without a decimal point in front of the zero, and it has an E in it. If you Put an apostrophe in front of the number as you enter it excel...

Excel: cell=any other sheet name, reply thanks, sheet1

reply thanks, sheet1, aa1: In Sheet2 let s assume cell AA1 is empty, so put this formula in cell AA1: =MID(CELL( filename ,A1),FIND( ] ,CELL( filename ,A1))+1,256) That formula causes the current sheetname to appear in the cell. Now back sheet1, click on F1 and press the equal...

Excel: How to change a cell background if there are triplicate values in a row?, conditional formatting, cell background

conditional formatting, cell background, excel sheet: In your conditional formatting you would use a COUNTIF() function. Assuming the names start in A2 and go downward, Assuming the scores go across from column B:Z The CF formulas for A2 would be: Green: =COUNTIF(B2:Z2, U )+COUNTIF(B2:Z2, T )=2 Red:...

Excel: Clear contents, sheet tab, target

sheet tab, target, z10: This could be done with a sheet event macro. Right-click the sheet tab and select VIEW CODE. Paste in this macro: Private Sub Worksheet_Change(ByVal Target As Range) Range( A & Target.Row +1, Range( A & Rows.Count)).ClearContents End...

Excel: Client Promotion - Follow up, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Issa Mahshi, Thanks for your acknowledgement and your requirements are easily attainable in excel. I need some more information for my better understanding for giving exact solution. 1st Query I understand that the client s are eligible for discount,...

Excel: Client Promotion, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Issa Mahshi, I have created a sample file for you, follow the link and take your mouse pointer to CLIENT MASTER file and do right click and select Download and save it in your computer. a href= https://skydrive.live.com/?id=171326DFD4986084!104&cid=171326dfd4986084&...

Excel: Closest Rank from Middle of a Section, Excel Sixthsense Excel

Excel Sixthsense Excel: Dear Parantapkumar, Thanks for your question and I request you to attach a sample file in somewhere in the web and provide me the link. It will be helpful if i have the sample data. You can find the file uploading links at the time of asking the question....

Excel: Comparing two columns to find duplicates in B based on A, array formula, orange numbers

array formula, orange numbers, purple numbers: I m going to guess your actual data set is MUCH longer. As such a fancy array formula that does the whole job in one cell could result in a worksheet that is snail to use. So I would recommend adding a helper column to the data to make the calculations easier....

Excel: Compile Error - Ambiguous Name Detected, target address, worksheet changes

target address, worksheet changes, ambiguous name: Hi Lisa, First of all, your code can be shortened quite a bit, look at this example that replaaces the firswt two steps of your code: Private Sub Worksheet_Change(ByVal Target As Range) Dim lCt As Long On Error GoTo ErrHandler Hide extra...

Excel: Conditional Format, text numbers, hi bob

text numbers, hi bob, formate: I dn t understand the 3rd condition. You want white text in A1 if there s a formula in B1 that references a different sheet? What happens if the text is 4 characters? or if the number returned is 4 characters(but if B1 is text, numbers and text are treated...

Excel: Conditional Formatting, conditional formatting, excel user

conditional formatting, excel user, city details: bobumlas@hotmail.com Use subject of AllExpertsQ ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30, Atlantic City Details here: See http://www.bobsexceluserconference.com ----------- Check out my new book here: http://www.mrexcel.com/outsidethebox.html...

Excel: Conditional saving in Excel, cell c1, performance concerns

cell c1, performance concerns, initial data: Antoine Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim n As Double Dim i As Double n = ActiveSheet.UsedRange.Rows.Count For i = 1 To n If Application.And(Cells(i, 2) , Cells(i, 3) = ) Then ActiveWorkbook.Saved...

Excel: convert number to text, indian rupees, thailand baht

indian rupees, thailand baht, bob phillips: haleem, There is no built in way to do that (no function provided in Excel for english or any other language except the Thailand Baht introduced in Excel 2002 as I recall). You would have to write your own VBA code to do it or find someoneelse...

Excel: Converting CSV files, colombo stock exchange, csv files

colombo stock exchange, csv files, csv format: Ok, revising this answer. Yes, there IS a hidden character in your data (char(28)) that is delimiting that data. We can feed that into a TEXTTOCOLUMNS command and split the data that way. So, assuming 5 folders all with the files of the same name, we...

Excel: Copy colored cells, colored cells, yellow cells

colored cells, yellow cells, copy worksheets: Is this what you want? Sub Yellow() For Each thing In ActiveSheet.UsedRange If thing.Interior.ColorIndex = 6 Then n = n + 1 Sheet8.Cells(n, 1).Value = Cells(thing.Row, 1).Value Sheet8.Cells(n, 2).Value...

Excel: Copy range to new workbook, source copy, sheet names

source copy, sheet names, happy new year: Carry, source refers to a specific range in a specific sheet of a specific workbook. So you don t need to use CurrentWB in the copy command Now you have not specified a location in NewWB. If it has the same sheet names, then I will offer a suggestion...

Excel: Copy row, r2 r3, cell1

r2 r3, cell1, yellow interior: Dev, Probably something like this: Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, rw As Long, cell As Range Set sh = Worksheets( Expl ) Worksheets.Add After:=Worksheets(Worksheets.Count) Set sh1 = ActiveSheet Set r = sh.Range(...

Excel: Copy values from non-adjacent cells to a new workbook, range areas, correct syntax

range areas, correct syntax, e5: Sub MyVersion() For each x in Array( A1 , B2 , C3 , D4 , E5 ) sheets( Sheet1 ).Range(x).value = Sheets( Sheet2 ).Range(x).Value Next End Sub If that gives an error, then you either don t have a Sheet1 or a Sheet2. Make the appropriate changes...

Excel: Create all possible combination, question thanks, combinations

question thanks, combinations, ideia: Copy the text between Sub ... and End Sub above. Open Excel, alt+F11 to the VBA editor. Insert, Module from the menu. Then paste the code you just copied from here. Now close the VBA editor, select or insert an empty sheet, hit alt+F8 and run the macro...

Excel: creating a formula?, cell b10, cell b1

cell b10, cell b1, value error: Zhesketh Check the data in cells B1 and B10 and in your formula cell. Excel is probably reading one of these as text and therefore can t do a calculation so it is giving you the VALUE error. Also you may try entering your formula as follows +B1-B10....

Excel: Custom views and protected worksheets, worksheet menu bar, vba project

worksheet menu bar, vba project, allexperts: How do you want to use the Custom Views feature? And how is that related to protection? In Excel 2010 there is no Worksheet Menu Bar, so this no longer applies. It was meant for Excel 2003 the latest. I don t really understand what you re trying to do...

Excel: D'hondt method, manual input, excel formulas

manual input, excel formulas, happy new year: Marc, I got it and returned it to you with the first cut of using the function at the web site you provided. If that produces the results you want, I can modify the function to work with the way your data is oriented. (the function is written to work...

Excel: Data entry issue in Excel 2010, scientific notation, apostrophe

scientific notation, apostrophe, text string: The string: 002556434E53 ...to Excel it is a scientific notation of a very long number. In truth, it s not a number at all, it s a text string since it s alphanumeric. Put an apostrophe ahead of the string, or format the column as TEXT before...

Excel: Date Calender, company holidays, a1

company holidays, a1, business days: Interesting challenge. If you have your holidays in a range of cells named holidays , it looks like this works: You MUST enter this formula by holding ctrl+shift, then press enter: =SMALL(IF(WEEKDAY(ROW(INDIRECT(A1& : &A1+50)))=1, ,IF(WEEKDAY(ROW(INDIRECT(A1&...

Excel: Dealing with Blank Values, b4, ym

b4, ym, chad: Hi Scott, Thanks for using AllExperts. The following formula should work for you. It checks column A, if it is blank then it will return a blank. Otherwise it returns the result of your formula. =IF(A4= , ,DATEDIF(C4,B4, y )& years, & DATEDIF(C4,B4,...

Excel: Delete rows with empty cells from table, parent cells, empty cells

parent cells, empty cells, frow: Kumar, Knowledge is key. What you are saying is you delete an entire row if any cell in that row in the range specified by Table is blank. That seems strange to me. If that is what you want, then Function DeleteTableEmptyrows() on error resume...

Excel: deleting digits on right, zeros, digits

zeros, digits: I m guessing you would actually want it to be a date as well - if so you would need to use the date and mid functions - but depending what error you are getting, you might try =left(trim(a1),10) this would remove any spaces before and after the date...

Excel: Disable CUT entire row, sheet tab, table rows

sheet tab, table rows, table structure: Easiest would be to prevent them from SELECTING a whole row. Right-click the sheet tab, select View Code, put this in: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Columns.Count = Cells.Columns.Count Then Target.Resize(1,...

Excel: Disabling the memo box in Excel, keyboard shortcuts, vba

keyboard shortcuts, vba, ribbon: Protect the sheet. If you first select all the cells and use ctrl/1, then the protection tab and mark all cells as NOT locked, then you ll be able to use the sheet as you wish, but comments (or other objects) will not be able to be added. Aside from that,...

Excel: displaying sheet1, sheet2, sheet3 tabs on worksheet, sheet tabs, windows button

sheet tabs, windows button: James If all the sheet tabs are missing, then you probably have them suppressed. In 2007 you should be able to toggle them visible/not visible by Windows button-Excel Options at the bottom-Advanced then scroll down to Display Options for this Workboo...

Excel: dynamic and dependent formulas, stock stock stock, sales stock

stock stock stock, sales stock, week3: see https://www.yousendit.com/download/T2dlWUhpZ2dLVlZESjhUQw for a file explaining some of the above! For the February to January bit, =EDATE(DATEVALUE( 01 &A1&YEAR(NOW())),-1) Where A1 contains February would do the job - though it would be easier to...

Excel: Dynamic Named Range, static name, range names

static name, range names, input box: Hi Charlie, The easiest is to first convert the range to a table (Excel 2007/2010) or List (Excel 2003) and then define a static name referring to the Table/List. Excel will then handle the expansion/contraction of the range name for you. See http://www.jkp-ads.com/articles/Excel2007TablesVBA.asp...

Excel: editing continous numbers, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Werner, Assume that your below data is in A2 cell A2 cell 302000001 In B2 cell copy and paste the below formula B2 cell formula =LEFT(TRIM(0&A2),2)& &MID(TRIM(0&A2),3,255) Drag the B2 cell formula to the remaining cells of column B2 based...

Excel: editing a space between numbers in an excell sheet, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Werner, I understand that you have not read my another answer provided for your same type of query. Anyway assume that your below data is in A2 cell Solution-1 A2 cell 413000133 B2 cell formula =LEFT(TRIM(0&A2),2)& &MID(TRIM(0&A2),3,255)...

Excel: Entire number for percentage, deferent, decimal place

deferent, decimal place, whole number: Marc I tried the formula on several allocations of about 3000 units with orders of about 8000 with 150 clients, and it worked perfectly try it again, I can t believe that it wouldn t work with your allocation. I believe that in the case of smaller amounts...

Excel: entry in one sheet get entry in another sheet/book, cell a1, customer entry

cell a1, customer entry, sheet1: NITIN It will be difficult to give you a specific answeer without knowing what your data looks like, for example what cells it is in and what the cells contain. I can give you some general thoughts on an approach to your problem. I believe you can create...

Excel: error massage, vlookup formula, asap thanks

vlookup formula, asap thanks, drop down list: Assuming your VLOOKUP formula is using the value you enter in A1, it probably looks something like this: =VLOOKUP(A1, Sheet2!A:Z, 2, 0) So, add test at the start to make sure A1 is not empty: =IF(A1= , VLOOKUP(A1, Sheet2!A:Z, 2, 0)...

Excel: Excel 2003 - Mandatory Fields, mandatory fields, msgbox

mandatory fields, msgbox, allexperts: Activate the specific worksheet first: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Sheets( TheOneWithMandatoryFields ).Activate For i = 1 To Range( A65536 ).End(xlUp).Row If Application.CountA(Cells(i, 1).Resize(1,...

Excel: Excel 2010 Conditional Formatting, vba code, word balance

vba code, word balance, fly down: Ok, now that I ve seen your sheet, your layout isn t as consistent as the eye thinks. Fortunately, every one of your table groups does have one thing in common, the word Balance appears next to each one in the same position. So, using that, we can simply...

Excel: Excel 2010 Macro/VBA Question, r2a, r1a

r2a, r1a, blank cell: Scott, I would see code like this being what you want. Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r1a As Range, r2 As Range Dim r2a As Range, cell As Range, rr As Range Set sh1 = Worksheets( Sheet a ) Set sh2 = Worksheets(...

Excel: Excel 2010 Macro/VBA Help, summation formula, target cell

summation formula, target cell, variables change: Sorry it took awhile to get back to you. This is asking quite a lot, and it d be easier if I had a file to look at. Please send a sample to me at bobumlas@hotmail.com, use subject of AllExpertsQ , and repeat the question in the email. I ll look at it over...

Excel: Excel 2010 Macro/VBA Help, r2a, r1a

r2a, r1a, blank cell: Scott, Is this a duplicate question. I believe it is the same question, so here is the routine I think you need . Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r1a As Range, r2 As Range Dim r2a As Range, cell As Range, rr As Range...

Excel: Excel 2010 - show formulas, cells, formula bar

cells, formula bar: As far as I know, this has remained constant throughout all versions of excel - you can see them all or non at all in the body of the sheet - though you can use the formula bar to display the formula for the active cell - not sure if this helps though?...

Excel: Excel, datedif function, chip pearson

datedif function, chip pearson, cell c1: Aman, the undocumented datedif function should do this for you =DATEDIF(Date1, Date2, Interval) =Datedif(A1,today(), y ) Chip Pearson has written up the documentation of for the function on his site: http://www.cpearson.com/Excel/datedif.aspx...

Excel: Excel, target value, target address

target value, target address, cell a1: Aman, well, your question has my response which is the correct answer for your first question. Here it is again If you want to add any number placed in sheet2 cell A1 to sheet1 cell A1 then you would put code like this in the code module for sheet2...

Excel: Excel, run time error, target value

run time error, target value, run time error 438: Aman, there is a typo in the code. the correction is below. That is a good reason not to put multiple complex questions in a posting. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $A$1 Then With Worksheets( Sheet1...

Excel: How to make excel addin useless after 30 days of installation?, level programming, programming knowledge

level programming, programming knowledge, programming skills: Hi Raja, There is a good discussion on this with he solution at http://www.ozgrid.com/forum/showthread.php?t=56282 It requires some more advanced level programming skills but I assume if you created an add-in you have the programming knowledge. All...

Excel: excel conditional saveas, file1, double click

file1, double click, cancel: In the VBE, double-click the Thisworkbook object, enter this code: Public DidSaveAsOnce Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) If SaveAsUI Then If DidSaveAsOnce Then Cancel = True DidSaveAsOnce...

Excel: Excel drop down list/fill other cells, excel drop down list, excel vba

excel drop down list, excel vba, utteraccess: * Referred to as cascading validation . (of course there are also ways to directly code this in excel vba). But here is the precise answer, and even a sample, of what you are looking for. Courtesty of Martin at UtterAccess.com: http://www.utteraccess.com/forum/Cascaded-Validation-Lists-t1761212.htm...

Excel: EXCEL FORMULA, cell b1, excel formula

cell b1, excel formula, richard florida: Julia There are a couple of answers depending on what your question is. I am a bit confused with what sheet we are bringing the data from and to. I can be read a couple of diferent ways. Also I am assuming that you want the data from the same cell row...

Excel: Excel Formula, cell a1, billing schedule

cell a1, billing schedule, excel formula: Chris Chris Assuming you have a cell you can look at to determine the number of items purchased-- The formula might look like this-- =IF(A1 =1000,3000,0)+IF(AND(A1 =1001,A1 =2500),4000,0)+IF(A1 2500,5000,0) Where cell A1 contains the number...

Excel: Excel Formula, excel formula, direct email

excel formula, direct email, j4: It depends how many options you have as to the best route - if it s as simple as the three, then an IF statement would do it =E4*if(d4=70,10,5) which in your example fits the situation - if D4 was 70 it multiplies by 10 else it multiplies by 5. If...

Excel: Excel Formula, www irs gov, taxable wages

www irs gov, taxable wages, gov pub: Hi spencer This should fix the problem. When excel does formulas that have a division or multiplication it does those first ie if you have 4+5*6 it would equal 54 when not using excel, but using excel it will equal 34 it multiplies the 5*6 first than adds...

Excel: excel formula., data validation, drop down boxes

data validation, drop down boxes, excel formula: These are called DEPENDENT DROP DOWN boxes, created through Data Validation, typically. If you Google that you ll find 100s of tutorials on these. My favorite starts here: http://www.contextures.com/xldataval02.html Also, on my site (link in my profile)...

Excel: Excel formula (Excel 2003), excel formula, excel spreadsheet

excel formula, excel spreadsheet, j7: Tanya, there are two ways you could do it. One is to make the refereces to cells in row 5 as absolute =(D6*$D$5)+(E6*$E$5)+(F6*$F$5)+(G6*$G$5)+(H6*$H$5)+(I6*$I$5)+(J6*$J$5)+(K6*$K$5)+(L6*$L$5)+(M6*$M$5)+(N6*$N$5)+(O6*$O$5)+(P6*$P$5)+(Q6*$Q$5)+(R6*$R$5)+(S6*$S$5)+(T6*$T$5)+(U6*$U$5)+(V6*$V$5)...

Excel: Excel program to categorize on bases of GPA, chart wizard, gpas

chart wizard, gpas, affirmative response: In Excel 2007+ you can use the COUNTIFS() function. Assuming those GPAs are in column B, then to get the first tier: =COUNTIFS(B:B, =0 , B:B, 1 ) The second tier: =COUNTIFS(B:B, =1 , B:B, 2 ) ...etc. Not sure about the pie...

Excel: excel query, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi B.Waqas, When you use the below formula two things are important. 1) The workbook should have sheet2. 2) The workbook must have been saved. Before attempting the below formula check the above two things and try the below one... =REPLACE(CELL(...

Excel: Excel Spreadsheet Autopopulation, necessary input, excel spreadsheet

necessary input, excel spreadsheet, vlookup: I m not sure I ve got enough information here - but I would use an input form of some description (a worksheet in it s own right, section of worksheet, or userform) to gather the data, and either macros to populate the results based on these inputs or formulas...

Excel: Excel timesheet question, excel timesheet, cell d2

excel timesheet, cell d2, cell c2: Joseph You are correct, excel will recognize the 10:30-3:30 as text and won t calculate it as a number. There are a couple of possibilities, What I would do is create a time in and a time out column, in other words a start and finish column. I would...

Excel: Extra image, question screen, second image

question screen, second image, images: Troy There was no second image and the first one was not very valuable. I don t understand why the attached images are so poor, but, they always are. Do you have some code that works? If so, please paste into the question screen. Also, if you just paste...

Excel: Data entry issue in Excel 2010, scientific notation

Excel: Data entry issue in Excel 2010, scientific notation

Excel: macro, tweaking

Excel: macro, tweaking

Excel: "Farhat Ali" name count as two words, farhat, microsoft word

farhat, microsoft word, word count: There s no word count function in Excel like there is in Microsoft Word. I typically count words by counting the spaces between words. To do that, we run two formulas to check the LENGTH of the string, one formula checks the original string, the seconds...

Excel: Fill diferent columns, minuto minuto, refrence

minuto minuto, refrence, vba: Hi Luis, Some code seems to be missing from what you posted here, correct? Please post the entire routine. For example, you seem to be using a variable called ws, but the code you posted does not show where ws gets its refrence to an actual worksheet...

Excel: Fill sheet, workbook sheets, thename

workbook sheets, thename, activex objects: Assuming the textbox and commentbutton are ActiveX objects inserted on the worksheet: Private Sub cmdEnter_Click() Dim theTextBox As OLEObject Dim enterHere As Range Dim theName As String Set theTextBox = Me.OLEObjects( txtname ) ...

Excel: Fill sheet, workbook sheets, command button

workbook sheets, command button: Private Sub cmdEnter_Click() Dim n As Integer On Error Resume Next n = Application.Match(Me.txtname.Text, Sheets( Student Name ).Range( A:A ), 0) If n = 0 Then Sheets( Student Name ).Range( A60000 ).End(xlUp).Offset(1).Value =...

Excel: find, copy and form-fill, question submissions, vacation dates

question submissions, vacation dates, slow response: Hi Cam, I apologize for my slow response. I forgot to set vacation dates to prevent question submissions while I was out of town. Yes, I will try to help. Can you tell me what parts of the relevant row you want to use for the form-fill? Also, do you...

Excel: Format Function for Excel, invalid property assignment, wrong number of arguments or invalid property assignment

invalid property assignment, wrong number of arguments or invalid property assignment, fileformat: This macro sub tester MsgBox Format(Now(), ddmmyy ) end sub will run fine - at least, it will on excel 2003 (which is the version I have to hand) though I know it works in earlier versions and I ve programmed in this method before using later versions....

Excel: formual needed, d smith, b1

d smith, b1, a1: Hi Dennis, I hope this is not too simplified, but I think this should work: in C1, type =IF(A1=B1, Y , N ) If a1 is equal to b1, then leave a Y, if not then leave an N. You can then drag this formula down for all values. Hope this helps, ...

Excel: Formula, coloured cells, figure a1

coloured cells, figure a1, visual reference: The coloring is for your visual reference. You won t use that in your further calculations. So, it sounds like you want the AVERAGE of the cells with a value greater than zero. In Excel 2007+ you would use the AVERAGEIF() function. =AVERAGEIF(A1:A5,...

Excel: formula help, brain cramp, column c

brain cramp, column c, b cell: Hello Carl Fuglein, assume that there are headers in row 1 and your data starts in row 2 then A2: a B2: 1234 c2: =INDEX($B:$B,MATCH(A2,$A:$A,0),1) now select C2 and drag down column C as far as you have data in columns A and B. Using your...

Excel: Hiding the command buttons, run time error, case command

run time error, case command, command buttons: If it s a regular button on the sheet, you need to know its name (ctrl/click on the button, look at the name in the name box). Assuming it s Button 15 : Private Sub Workbook_Open() Sheets( HORIZON ).Select ActiveSheet.Buttons( Button...

Excel: hyperlinks, e56, excel workbook

e56, excel workbook, creation tool: JON I do not work with hyperlinks much, so someone else might be able to give you a better answer. Having said that, I can t read your screenshot well enough to see wht might be the problem with the formula (usually in allexperts the screenshot resolution...

Excel: identify date in a range, spring bank holiday, public holiday dates

spring bank holiday, public holiday dates, summer bank holiday: Can you send an actual workbook... send to Bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what needs doing. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30, Atlantic City Details here: See http://www.bobsexceluserconference.com...

Excel: Importing in Excel 2007, column headings, import method

column headings, import method, database queries: It s not a method I ve used, and it does look as though it may have been changed in later versions - I do use the import external data for web and database queries, but for other workbooks I simply set up links to them =[schemesToLookAt.xls]Schemes!$A$12...

Excel: Incrementation of non last numbers in excel, unit numbers, incrementation

unit numbers, incrementation, column c: Andy Sorry there isn t a built in way to denote by digit what you want to fill in a fill series. A couple of suggestions--- You can put your floor numbers in a separate column and your unit numbers in another column then use the fill series for the...

Excel: Inputting time without punctuation in multiple columns, target value, value int

target value, value int, input time: Jesi If that works for column 6, then the following should work for columns 6 asnd 7: Private Sub Worksheet_Change(ByVal Target As Range) If Application.Or(Target.Column = 6, Target.Column = 7) And Target.Row = 1 And Target.Row = 1000 Then ...

Excel: Log User Activity, sheet changes, question sheet

sheet changes, question sheet, happy new year: Paul, You can put in code in the activate event in each sheet s code module Private Sub Worksheet_Activate() Dim Evnt As String Evnt = Activate Sheet1 Call Elog(Evnt) End Sub or you can go to the Thisworkbook module and use the Workbook level...

Excel: Mac Excel -- Dialog Boxes, custom dialog boxes, spreadsheet cell

custom dialog boxes, spreadsheet cell, custom dialog box: MSGBOX statements would return 2 for cancel or 1 for blank, as in msgbox( hello ,vbokcancel) But dialog boxes might return different values -- it s best to test the return value for cancel vs entering a blank. They MAY be the same, in which case you re going...

Excel: What-if macro?, c cells, b cells

c cells, b cells, max 2: Paul, here is a macro that does what you describe you want (as I understand it) I generates data down to row 532 (so 531 combinations that produce a 9 plus row 1 which is a header/label row). Sub GenCombo() Dim A, B, C, D, E, F, g1, h1, i1, G, H,...

Excel: macro, target value, sheet tab

target value, sheet tab, ogilvy: bruce, First, open a blank workbook and test my suggestion on a meaningless sheet in a thorough fashion to insure it actually does what you want - don t put real data at risk. So with no other information, then right click on the sheet tab for the sheet...

Excel: Macro to replace Return characters with HTML tag, ascii code, return character

ascii code, return character, work thanks: Pat, You can actually do this manually. I assume by Return you mean when you do Alt+Enter within text in a cell and if you word wrap the cell, then you get multiple lines, broken at the return character. This character is actually an ascii code 10...

Excel: Macro which uses filtered data, range selection, autofilter

range selection, autofilter, capex: Gail, If you only have data out to column L, then you can use this Sub ABC() Delete all old data Sheets( Capex for Sarah ).Select ActiveSheet.Range( $A$2:$L$65536 ).AutoFilter Field:=10 Range( A3:H3 ).Select Range(Selection, Selection.End(xlDown)).Select...

Excel: mass transfer row info to new columns, access queries, access query

access queries, access query, application name: Christopher The degree of difficulty depends a lot on the consistency of your data. If every application name has exactly two associated rows of data below you could put formulas in columns b and c to retrieve the data associated with the application;...

Excel: Multiple worksheets w/ same info, spreadsheet, thanks for your help

spreadsheet, thanks for your help: You can do it. Excel can reference values on other sheets or even in entirely different workbook files. The EASIEST way to do it is to open both sheets, COPY the data you want to have appear in the other book, then PASTE SPECIAL LINK the data. That will...

Excel: Naming worksheet, current sheet, g1

current sheet, g1, yana: On the first sheet go to cell G1 and format the cell as date with your suitable view. Make sure that the same format is being done in the cells of G1 in all other sheets. Now in sheet 2 click on cell G1 and type in: =Sheet1!G1+1 and then press ENTER. You...

Excel: Need to deselect a row of data incorrectly selected out of many, empty column, ctrl button

empty column, ctrl button, dana: Hi Dana, Well, I tried doing this in both Excel 2003 and 2010 and failed, so I guess it can t be done this way. Maybe there is a different approach? Such as using an empty column and putting a 1 in each row that you want selected, then sort and filter...

Excel: Need Validation that allows typing only the numbers in a Cell, alpha numeric characters, number option

alpha numeric characters, number option, type numbers: I d simply go with a number option rather than a formula - whole numbers only between certain bounds would be the easiest if that fits with your rule. If it doesn t I m not 100% sure how one would prevent it as 1/1 isnt a fraction, it s a date, and that equates...

Excel: Need Validation that allows typing only the numbers in a Cell, alpha numeric characters, data validation

alpha numeric characters, data validation, type numbers: You can stop the fractional results by changing your data validation to ALLOW: Whole Number Between: 1-30000 Unfortunately, if you go much above 30000 your numbers are now in the range of Excel s real-date-codes, so you would still see the dated errors...

Excel: if nested, cells, g14

cells, g14: Hi Samer, You need an entirely different function called VLOOKUP. Put your numbers in two columns next to each other, say cells A1:B11 : 0 0.013 7 0.013 8 0.013 9 0.025 10 0.0675 11 0.3925 12 0.3325 13 0.165 14 0.013 15 0.013 16 0.013...

Excel: Next empty row, good solution, dynamic range

good solution, dynamic range, aesthetics: Troy This is a very common task and the solution depends a lot on how your data is arranged. If you are adding to a an existing block of data you can go to the bottom of your worksheet and do the programmatic equivalent of control up arrow to get to the...

Excel: how to pick multiple values vertically corresponding to a cell in MS Excel 2007?, formula index, excel formulas

formula index, excel formulas, excel workbook: Jassi, In D1 of Sheet2 I put in this formula =INDEX(Sheet1!$B$1:$B$10,MATCH(C1,Sheet1!$A$1:$A$10,0)+COUNTIF($C$1:C1,C1)-1,1) I then drag filled it down to D10. It produced the results you say you want. I can t say it is a general solution, but it...

Excel: Pivot table Recalculate, table wizard, pivot table

table wizard, pivot table, table changes: By changes if you mean you have the same number of columns, all you will need to do is refresh the table after you repopulate your data sheet. If your number columns change, you will need to reset the range in your pivot table using the pivot table wizard...

Excel: problem with editing your macro, b cells, header row

b cells, header row, max 2: Paul, The macro gets run one time. You can ask as much as you want, but not sure why you are asking less than the best. Seems like you should be asking whomever you think is Expert of the Month. Here is the revision to the macro: It generated...

Excel: Protect multiple column, mohammad ali, data validation

mohammad ali, data validation, formula c: You can use Data Validation for this. 1) Select C2:N10000 2) Open Data Data Validation and apply these settings Allow: Custom Formula: =C$1 Trigger Now, if the word Trigger is in row 1, that column will not allow cells to be edited...

Excel: Random Numbers without repeating, sheet numbers, hi bob

sheet numbers, hi bob, bottom number: OK, using another helper column. F1:F500 contains 1 thru 500. E1:E500 contains =RAND() D1 contains: =INDEX(F:F,MATCH(SMALL(E:E,ROW(A1)),E:E,0)) then fill down to D500. Every time you calculate (F9), column D will contain another permutation of the values...

Excel: When range is selected thats the only time the macro can run, count column, msgbox

count column, msgbox, a11: Hudas, Here is a sample routine that loops through cells a1:A11 and displays a msgbox when it finds a row where there are merged cells that extend from column A to column I. Sub ABC() Dim r As Range, r1 As Range, cell As Range For Each cell In Range(...

Excel: re: pretty basic question, speadsheet, excel expert

speadsheet, excel expert, excel spreadsheet: 1) Enter the formula as shown into the first cell. 0001-0025 should appear. 2) Highlight that cell again, then press CTRL-C to copy. The cell should have o circling box appear around it. 3) Highlight the next cell down and press ENTER. 0026-0050...

Excel: reply to your email, cell b2, census number

cell b2, census number, functions in excel: Hi Peggy, It s good to hear from you again. This can be done by using a few nested IF functions in Excel. So if the census number is in cell B2, copy the following into B3, B4 & B5 to get the RN, PCT and UC respectively: B3, RN: =IF(B2 12,2,IF(B2 15,3,IF(B2...

Excel: two reports from pivottable (VBA), pivot table, vba

pivot table, vba: Yes, but I need a lot more info -- seems like just recording a macro making a pivot table of your data would be all you d need. If you need more help, send a file t me at bobumlas@hotmail.com and use subject of AllExpertsQ and repeat what needs doing...

Excel: Rounding A Value in Same Formula, rounding numbers, vba code

rounding numbers, vba code, decimal points: Rajeev Usually people are okay with rounding numbers in an adjacent or remote cell. You can always hide the unrounded cells. Alternatively, you can write some VBA code to go through your data and change numbers to rounded versions of the same numbers....

Excel: Save function, sales target, target folder

sales target, target folder, cell b2: This should get you started. It presumes a sales folder name in cell B2, a date in B3, it will also make sure the folders all exist in the resulting string, it will even create them if necessary. =============== Option Explicit Sub SaveSpecial() ...

Excel: save & search function in macro, sales target, target folder

sales target, target folder, cell b2: This code will save the activeworkbook in the appropriate subfolder (and create one if needed), but the structure assumes that the folder Sales Target exists directly under C . That is, C:Sales Target... Sub SaveActiveWorkbook() Dim ThePath As String,...

Excel: Save Unmatch Data, workbook sheets, command button

workbook sheets, command button, rw 1: Eran, I set up a userform that matched you description. I had a sheet in the workbook named Student Name. I tested the below code and it worked as you say you want your routine to work. Private Sub cmdEnter_Click() Dim sh As Worksheet, r As Range,...

Excel: Save a workbook with search function, sales target, target folder

sales target, target folder, cell b2: The basic structure for a save as macro is ActiveWorkbook.SaveAs Filename:= C: emp est.xls In your case, the TEMP folder is a variable, as is the date so ActiveWorkbook.SaveAs Filename:= C:Sales Target & range( B2 ).value & & format(now(),...

Excel: setting up automated calculation table, answer mark, something simple

answer mark, something simple, ogilvy: Mark, Send the file to me an I will take a look. As far was what cells to select, that would depend on where you want the values to appear. You will have to tell me that as my previous assumption must have been wrong. send it to twogilvy@msn.com ...

Excel: Spread sheet merge to word document, spreadsheet formulas, extra digits

spreadsheet formulas, extra digits, document problem: Probably a bit of both. Excel can store a really long decimal value number in a cell and you will only see the number the way the cell is FORMATTED to display it, it is still a longer decimal unless you use a ROUND() function to actually change the value...

Excel: IF statement in another cell, spreadsheet functionality, d6

spreadsheet functionality, d6, logic: Jessica It doesn t make sense yet. Does B6 have a formula, or an entered number? If it has a hard entered number, then to have it change, you will need VBA code. I doubt you really need that, as I am pretty sure that you can do what you want with basic...

Excel: Subtotals in Excel 2011, Excel Sixthsense Excel (Notepad Method)

Excel Sixthsense Excel (Notepad Method): Hi Yana, Good method suggested in John Walkenbach website and the same is given below for your reference. *********************************************************************************** When you copy a range of formulas and paste them to a new location,...

Excel: subtracting military time, ed discharge, ed registration

ed discharge, ed registration, registration times: Example: on a new sheet, in cell A1, enter 1/30/2012 15:30 and in B1 enter 1/31/2012 4:00 and in C1 enter =(B1-A1)*24 C1 will display 12.5, which means that the length of time between 2:30 pm on Jan 30 and 4:00 am on Jan 31 is 12 and a half...

Excel: Sumproduct with multiple criteria across multiple tabs, cell a1, sumproduct

cell a1, sumproduct, control tab: Vlad, based on your description, you want to have a summary sheet that is specific to sheets that have a specific name in them in cell A1 (where the name is in B2 of the summary sheet) and for each of those sheets you want to sum up values in a specific...

Excel: Can't figure this out, blank cells, countif

blank cells, countif, information column: Dante/Tom assume your data starts in row2 with headers or blank cells in row 1. then in B2 put in this formula =IF(COUNTIF(C:C,$C2) 1,IF(COUNTIF($C$1:$C1,$C2)=0,INDEX($A3:$A$5000,MATCH($C2,$C3:$C$5000,0),1),INDEX($A$1:$A1,MATCH($C2,$C$1:$C1,0),1)),...

Excel: Tab Color in Excel 2003, color tabs, ogilvy

color tabs, ogilvy, previous version: Lacy, the ability to color tabs was added in Excel 2003 as I recall. So I suspect that the person you mailed the workbook to was using a previous version and opened and saved the file before sending it back to you. Since that older version did not support...

Excel: Transfer to another sheet, sh2, sh1

sh2, sh1, msgbox: Hudas, I had to restructure my if statement and when I did, I left out part of one of the commands Set r2 = r2.Offset(r2.Rows.Count, 0) was and should have been Set r2 = r2.Offset(r2.Rows.Count, 0).Resize(1, 1) I didn t test it enough to detect...

Excel: Usinf a variable in a worksheet formula with vba, newf, bx

newf, bx, ogilvy: Carry, Dim NewF As String NewF = Range( BZ11 ).Value Range(“C4”).Formula= =SUMPRODUCT(($BX$10:$BX$405= & NewF & )*($AD$10:$AD$405)) End Sub You can dim it as string or integer - it doesn t make any difference. The difference is whether the...

Excel: Using 2 functions in 1 cell, cell a1, general solution

cell a1, general solution, exact numbers: A ---------------------- see bottom of post ---------------------- =ROUND(A2*A3,2) will show 55.39 if A2 contains 50% and A3 contains 110.77. Tested with those exact numbers and the formula above and it displayed 55.39. Now if the number are not...

Excel: using a formula to show results in a certain format, format cells, column c

format cells, column c, negative numbers: Dana, Since your in the US, you should be able to select that option by selecting the cells and right clicking, then choose format cells. Go to the number tab and then in the Category Box, again choose number. You should have a display on the right labelled...

Excel: using Lookup command, cell c1, cell b1

cell c1, cell b1, math formula: Paul, I will assume your formula in cell C1 will return exactly a number that is found in column A of sheet Score - so something like 30 and not something like 25.2134285231 then in C2 =VLOOKUP(C1,SCORE!A1:D60,IF(B1= B ,2,IF(B1= C ,3,4)),FALSE)...

Excel: Using Text-to-Columns with Cells Containing Alt+Enter, csv file, quantity price

csv file, quantity price, line break: If your data examples are correct, the formatting is an issue as well, in that the .69 relates to the .59 above it, and not (as it would normally be) to the Lettuce value in column A. That aside, text to columns does indeed do what you show. Possibly saving...

Excel: VBA count help in a range across multiple sheets, basketball stat sheet, points rebounds

basketball stat sheet, points rebounds, game percentage: Hi Bill, You are very close. The problem is that you are looking at the range, but you need to look at individual cells or a formula that will consolidate the range. My guess is that the second option will be the best, but I will give both below and you can...

Excel: VBA event change code doubt, target address, p55

target address, p55, ogilvy: Kumar Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = Target.EntireRow.Address Then Application.EnableEvents = False Target(1).Resize(1,16).Select Application.EnableEvents = True End If End Sub or ...

Excel: VBA Excel, q3

q3: Put this code behind the userform: Private Const MCSDEFAULT As String = Please enter your name Private Sub TextBox12_Enter() If TextBox12.Value = MCSDEFAULT Then TextBox12.Value = End If End Sub Private Sub TextBox12_Exit(ByVal...

Excel: VBA - Goal Seek, mssage, ins

mssage, ins, guess: NCA, That error means that VBA cannot load something that is referenced in the code. Unfortunately, when it happens the line that is highlighted usually isn t the one causing the error. My guess is that the Solver add-in was not reinstalled. To get it...

Excel: VBA Inputting time without semicolons into multiple columns, target value, input times

target value, input times, value int: Jesi, You would just add it to your conditions columns 6 and 7 so for that (columns 6 and 7) Private Sub Worksheet_Change(ByVal Target As Range) If (Target.Column = 6 or Target.Column = 7) And Target.Row = 10 And Target.Row = 20 Then...

Excel: VBA Lookup to return Multiple Values Horizontally, row headings, storage sizes

row headings, storage sizes, storage size: Yogi, I will assume the data is sorted on the Unique ID Sheet Data Column A Column B Column C Unique ID Storage Size NO. === row 1 BC 120 1 BC 90 5 BC 45 10 ...

Excel: Web Query Macro Stopped Working, wsr, wss

wsr, wss, thanks in advance: Hi Andy, I don t see anything wrong with your code. Of course, there could be something wrong on the URLs worksheet that I can t see. I think it might help to add a debug print statement to your code like this: iLastRow = wSU.Cells(wSU.Rows.Count,...

Excel: absolute reference, cell a1, column references

cell a1, column references, absolute reference: Hello Daniel, Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For...

Excel: Add button, worksheet menu bar, button menu

worksheet menu bar, button menu, new menu: Jack, Sorry for not understanding your request. This does what you want (I am at home so have access to a version of Excel between xl97 and excel 2003 where the commandbars construct is used. Basically you need to maintain the reference cbcCutomMenu...

Excel: add button, worksheet menu bar, button menu

worksheet menu bar, button menu, new menu: In reusing cbcCutomMenu again you lost you access to the menu you wanted. I added cbcCutomMenu2 & this works: Sub AddMenus() Dim cMenu1 As CommandBarControl Dim cbMainMenuBar As CommandBar Dim iHelpMenu As Integer Dim cbcCutomMenu As...

Excel: How to add multiple IF function, true condition, reply

true condition, reply: As you have discovered, the first true condition will be the one that is executed - so the solution is to reverse the order of the tests =IF(A1 =60,2, IF(A1 =55,1.7, IF(A1 =50,1.3, ))) which NOW means that they will be processed in order. An alternative...

Excel: adjust table structure, wizard button, table wizard

wizard button, table wizard, graphic chart: Hoi Petra, Leuk eens een Nederlandse vragensteller te zien :-) - antwoord in Engels zodat anderen het kunnen volgen... I expect you can get close by using a pivot table. - Make sure the pivot table wizard button is available on the Quick Access...

Excel: Array Formula, array formula, sumproduct

array formula, sumproduct, ogilvy: Raja, that is how you would do it. I believe your problem is that Evaluate will only handle a string of 255 characters and your string is 266 characters in my tests (it would depend on what the length of your address is). for example, if I change your...

Excel: Auto-Coloring a Cell Contingent on the Adjacent Cell's Number Value, conditional formatting, value question

conditional formatting, value question, relative cell: India Note you could have selected multiple cells. But in any selection of multiple cells, one cell is the Activecell (highlighted differently). You would enter your formula relative to the ActiveCell. You would use absolute and relative cell references...

Excel: Auto filling a column, vba code, video tutorial

vba code, video tutorial, bottom right corner: Well, AutoFill is the process of clicking the AutoFill Handle (the little dot in the bottom right corner of a selected cell) and dragging it down. You have to manually stop where you want to stop AutoFilling. To have AutoFill automatically stop at the...

Excel: auto scrolling a list, ogilvy, answer the question

ogilvy, answer the question, gentleman: Malcolm If you run the macro, it appears the intent is to scroll the scores. If you start the macro and then type in Stop in J1, then the macro should stop when you type it in - the code should be well past the instructions to clear J1. If you don t want...

Excel: Automation error, automation error, drop down menus

automation error, drop down menus, text boxes: Hi Ross, There are a lot of things that can cause this error. Very often this error resolves by adding Option Explicit to the top of every code module. Of course, you will then have to explicitly declare (Dim) every variable. If this doesn t solve...

Excel: bi-weekly & additional monthly mortgage Calculations, biweekly mortgage payment, target date

biweekly mortgage payment, target date, mortage calculator: Hey U Go to this site http://office.microsoft.com/en-us/templates/results.aspx?qu=mortgage%20loan%20calculator&av=zxl&queryid=c3bc6cd9%2D9518%2D4a31%2D8348%2D8e62ef22b12f#ai:TC001056618| and download the biweekly mortgage payment template. You can...

Excel: Blanks inserted by, blank cell, d11

blank cell, d11, a11: Alan, Because it is a null string and not empty. I can t tell you much beyond that. I have looked at how information is stored in the old file format and there are types of records that store information about cells. I suspect the record for the...

Excel: Cells & lines, spreadsheet table, blank row

spreadsheet table, blank row, horizontal lines: 1) to insert an entire row in the spreadsheet, right-click the row number on the left and select INSERT 2) to insert a row just inside the table, not the entire sheet, highlight the range of cell that make up the table row where you want a blank row inserted,...

Excel: change color, conditional formatting, current date

conditional formatting, current date, expiration date: Raymond, You will need to set up Conditional Formatting with the b Now() /b formula which returns the current Date & Time of your system. Please see my article - a href= http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_10750-The-...

Excel: Change color by date, video tutorial, microsoft excel

video tutorial, microsoft excel, cordially: See this tip on a href= http://599cd.com/blog/display-article.asp?ID=343&Thread=Conditional+Formatting&ExpandAll=YES&key=AllExperts Conditional Formatting /a . Be sure to visit my web site and watch my B FREE /B 90-minute A HREF= http://www.599cd.com/Excel/AllExperts...

Excel: Change the column reference in a formula based on selected cell, array formula, countif

array formula, countif, v34: =Cell( col ) returns the column of the active cell when you do a calculate (you will need to press F9) So instead of COUNTIF(V:V,$BT$8) you could do this: COUNTIF(OFFSET($A:$A,,CELL( col )-1),BT7) instead of V7:V34 you could use OFFSET($A7:$A34,,CELL(...

Excel: Checkboxes are Combo Boxes, dropdown arrow, data validation

dropdown arrow, data validation, combo boxes: Noonie, You can use data validation to do this select all the cells where you want a yes or no answer then choose the Data Menu (xl2003 down to xl97) or the Data Tab (xl2007 or later) choose data validation in xl2003 and earlier for Excel 2007...

Excel: COLOR TRANSFER, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Larry, It s possible by using VBA, if you dont have any issues in using VBA then describe about your data with the column and row references for giving exact solution. At the same time please spend some time to describe about your data in detail so...

Excel: COMBINATION OF DATA, Excel Sixthsense Excel Combing of Data

Excel Sixthsense Excel Combing of Data: Hi Larry, Assume that CAPT is in A1 cell and JONES JOHN PAUL in B1 cell A1 CAPT B1 JONES JOHN PAUL In C1 code =TRIM(A1& &IF(ISNUMBER(FIND( ,TRIM(B1))),LEFT(TRIM(B1),FIND( ,TRIM(B1))-1),TRIM(B1))) /code Result will be CAPT JONES ...

Excel: comparing 2 sets of numbers to find same, countif, column c

countif, column c, match: Marcus, Say your master list is in column A of sheet1 and your other list is in column A of sheet2 in column B of Sheet2 you could do this Sheet2, B2: =countif(Sheet1!$A:$A,A2) then drag fill down column B this will indicate which items in...

Excel: compute date and change color, Excel Sixthsense Excel Creating Conditional Formatting in Excel 2007 Excel 2010 Excel 2003

Excel Sixthsense Excel Creating Conditional Formatting in Excel 2007 Excel 2010 Excel 2003: Hi Hasina, Thanks for sending your question on my way. If you want to apply the conditional formatting for A1 to C1 cell then select A1:C1 and select conditional formatting. OR If you want it for only C1 cell then just select C1 cell and no need...

Excel: conditional formatting, conditional formatting, double quotes

conditional formatting, double quotes, ogilvy: Gail, I assume you know how to conditional format and you are just looking for a formula to use as a condition =countif($D6, *left* ) zero will be interpreted by the conditional formatting algorithm as false and anything else as true so if cell D6...

Excel: Conditional formatting, format option, cells

format option, cells, a1: Amanda Highlight all the cells that you want the conditional formatting to apply too and then go to conditional formating, new rule, use formula and in the formula box type in =($A1=$b1)select your format option and this will go into all the cells at once....

Excel: Conditional Formatting comparison, relative reference, direct email

relative reference, direct email, absolute reference: Assuming your formula is exactly as typed, the issue is related to the type of reference - e1:e491 is a relative reference so will refer to other cells if the formula is moved - it should be the absolute reference $E$1:$E$491 - and should then work. HOPEFULLY...

Excel: Conditional Formatting question, Excel Sixthsense Excel Conditional Formatting For Past Due Dates in Excel 2010

Excel Sixthsense Excel Conditional Formatting For Past Due Dates in Excel 2010: Hi Denise, Thanks for sending your question on my way. Assume that the Due Dates are in Column E Anything from Jan 2012 to Aug 2012 to return a color =AND($E1 =DATE(2012,1,1),$E1 =DATE(2012,8,31)) Date(Year,Month,Day) I m seeking a rule to...

Excel: conditional increments perpetually, algebraic solution, head htm

algebraic solution, head htm, increment: Muza: I suggest the following... =(ROUNDDOWN((G17-1)/20,0)+1)*0.5 This is an algebraic solution instead of using a series of IF functions. This should work no matter what positive value you enter into G17. If you are satisfied with this solution...

Excel: How to convert input data, cell reference, column number

cell reference, column number, msgbox: Try this: Option Explicit Sub StandardInputBox() Dim strName As String, strRNG As Range Do strName = Application.InputBox( Enter a cell , Cell Properties , B6 , Type:=2) If strName = Or strName = False Then Exit Sub ...

Excel: CONVERTING EMPTY CELLS, blank cells, empty cells

blank cells, empty cells, zeros: There s no formula to automatically make blanks be 0000, but after F5/Special/Blanks, you can enter 0000 but hold the ctrl key down before pressing enter and it ll fill them all at once. Put the leading quote in (which won t show) or 4 zeros will show as...

Excel: Copy column values with formatted clour and not formulas, conditional formatting, background colours

conditional formatting, background colours, clour: You need to copy the entire range (cols A and B) from sheet1 to sheet2. Then the conditional formatting will get copied along with the values/formulas and you ll get the same colors. If you don t copy the whole range and use conditional formatting, then it...

Excel: Copy Formula until Last Row, data cells, answering the question

data cells, answering the question, w2: Sub CopyFormula() Dim LastRow As Long With Worksheets( Data ) LastRow = Worksheets( Data ).Cells(Rows.Count, A ).End(xlUp).Row .Range( H2 ).AutoFill Destination:=.Range( H2:H & LastRow) _ , Type:=xlFillDefault .Range( V2 ).AutoFill Destination:=.Range(...

Excel: Copy paste, blank cells, man dub

blank cells, man dub, last destination: Rakesh Assume this data BOM LON WRTIN 4523 BOM LON BRTIN 4575 BOM LON CRTIN 4627 BOM LON JRTIN 8456 BOM LON RRTIN 8508 starts in cell A2 assume the data in column G starts in G2 and there are no blank cells in column G down to the last destination...

Excel: copy rows containing specific text in Excel, dropdown arrow, course names

dropdown arrow, course names, blank rows: Gaurav, If you select your data and then on the home tab, go to sort & filters on the right side of the tab. Then click on the dropdown arrow and select Filter. This will put dropdown arrows in the top row of the selection. Go to the column for course...

Excel: Copy values from macro without source formatting, cell1, r2

cell1, r2, r1: if it is specifically values, then I wouldn t use copy (personally) cell1.Offset(0, 3).value=cell2.Offset(0, 11).value would do the same thing IF it needs to be copy, then cell2.Offset(0, 11).Copy cell1.Offset(0, 3).PasteSpecial Paste:=xlPasteValues...

Excel: copying lookup formula, index number, increment

index number, increment, a2: The ROW() function can be used to increment in this manner. A way to use this to equal 2 is ROW(A2), so your first formula would change to: =HLOOKUP(N$5, A$5:N$30, ROW(A2), FALSE) As you copy that down, it would change itself to A3, A4, etc,...

Excel: How to count cells with certain letters between two dates (2-2-3 schedule, tracking FMLA hours), sumproduct, sheet1

sumproduct, sheet1, column c: Becka, I could only test with the data you provided and the answers were correct with that data. You can certainly send the workbook to twogilvy@msn.com and I will put in a formula that I believe will work as I understand the requirement. Also...

Excel: COUNTBLANK and COUNTA not autofilling, formulas in excel, shift keys

formulas in excel, shift keys, click options: Christine, If you are filling and all the cells have the same results and you then edit a cell and it calculates correctly, that sounds like you have calculation set to manual. Try doing Ctrl+Shft+F9 (hold down the ctrl and shift keys and hit F9). ...

Excel: CountIf non-contiguous filtered range, autofilter, google

autofilter, google, formulae: James, in your example, you want to count the entries in DataRange. So each cell either contains Test or blank. you can count the filled cells with =SUBTOTAL(3,DataRange) Now if Datarange will have other values rather than Test, then that wouldn...

Excel: Counting number of rows between columns, ch cx, column addresses

ch cx, column addresses, column address: Gail, =COLUMNS(CH:CX) returns 17 but includes the two columns listed in the count. You can do =COLUMNS(CH:CX)-2 if you want the number between. If you have a base column address and you want to get the address for a specific number of columns...

Excel: Counting the number of words in a cell, cell a2, cell a1

cell a2, cell a1, last question: Gail =len(A1)-len(substitute(A1, , ))+1 so this takes the length of the cell and then takes the length with the spaces removed. That should be one less than the number you want, so add 1 to it. if you have problems with leading or trailing spaces...

Excel: How to create a macro in Excel 2007, de bruin, macro in excel

de bruin, macro in excel, repetitive work: Tammy, go to Ron de Bruin s site and look at this page: http://www.rondebruin.nl/code.htm after reading that, at the bottom of the page are two links. On to David McRitchies site and one to Debra Dalgleish s site. I will reproduce the links here:...

Excel: Creating a formula with multiple inputs/outputs, cell b10, margin formula

cell b10, margin formula, h10: Sukhi You can do it with a series of IFAND formulas and add the results together. Since only one of your formulas will yield a value and the others will yield a zero they can all be added together to get your answer. For example =IF(AND(B3= james...

Excel: Creating a Macro to Find a Unique Identifier in a Column, cell a2, macro code

cell a2, macro code, reference number: How do you want to be taken there. You could use the hyperlink worksheet function in conjunction with the MATCH function, for example suppose the item to look for is in cell F2 and the column you are looking at is the range A3:A1000. Then a direct link to...

Excel: Creation of 2 different validationlists from 1 dropdownlist, dropdownlist, indirect method

dropdownlist, indirect method, pricelists: If you want to use the INDIRECT method, the trick isn t to name the range after the country, name if after the country+anotherword. For instance, for England, you name the range EnglandCountry, Austria would AustriaCountry, etc. Then your INDIRECT()...

Excel: dashboard, conditional formatting, performance measures

conditional formatting, performance measures, dashboard: How are you reporting the percentages? Using conditional Formatting, you can set values of your choosing to be represented as either Good or Bad based on your specific criteria. Using Charts, you can format specific colors to represent Good or Bad data....

Excel: Data Breaks in a Line Chart, peltiers, trendlines

peltiers, trendlines, trend line: Answer coming from Hitchin, and I m based in Stevenage - small world! Depends on the chart, but I TEND to refer charting questions to Jon Peltiers excellent site - http://peltiertech.com/WordPress/plot-two-time-series-and-trendlines-with-different-dates/...