You are here:
| Subject | Date Asked |
| If Statement | 2/10/2012 |
| Q: I have a cell that contains DLV TECO PRT CNF SYS all in one cell. I need an If Statement that ... A: Debbie Brown, say the value is in C3 in another cell ... | |
| Comparing Two Columns Of Dates And Getting A Percentage | 2/9/2012 |
| Q: I am creating a maintenance report in another software application and exporting it into Excel. I am ... A: Nick Rupp assume the dates are in A1:A26 then =(countif(A1:A26,">=Jan 1, ... | |
| Conditional formula to return a "Yes" or a "No" | 2/8/2012 |
| Q: I think this might actually be two separate questions. Hopefully I am not making this too ... A: Jess =IF(COUNTIF(Sheet2!$A$1:$A$5,A2),"YES","NO") if the value is not there countif returns a ... | |
| Consolidating hours tracked over several projects | 2/8/2012 |
| Q: Tom, I am trying to consolidate some data in Excel which is being used to track coverage over ... A: Neal, assume your project names can be in A2:A12 and there there will be no empty rows in that ... | |
| Userform Cell Fill | 2/8/2012 |
| Q: I have created a button that unhides several rows and opens a user form which allows the user to ... A: Keegan, If I can depend on column C to be able to tell me the next place to put the data Private ... | |
| Need a macro to close file without saving | 2/8/2012 |
| Q: Now I'm using this coding "ActiveWindow.Close" to close the file. So whenever I run the Macro there ... A: Jayendran Activeworkbook.Close Savechanges:=False as the last command should not elicit a prompt. ... | |
| Sum of total within date? | 2/8/2012 |
| Q: I have a list of data, example as below 01/01/2011 $50 01/01/2011 $60 02/02/2011 $70 03/02/2011 ... A: Chan, Sumif is the best function from my perspective =Sumif(A:A,">="&C1,B:B)-Sumif(A:A,">"&D1,B:B) ... | |
| excel macro | 2/6/2012 |
| Q: I need to write a line of code in vba, that would perform a certain action in a row, where the cell ... A: Kuba, You can use this to get the last cell in a column that is not blank Dim r as Range With ... | |
| Using End(xlDown) to select a range | 2/5/2012 |
| Q: I have this macro which selects data beginning in range A1 of each worksheet in CurrentWB and copies ... A: Carry, Sorry - I wasn't focused on anything but the question at hand. ... | |
| Top 20 Data Return | 2/5/2012 |
| Q: I have a range of data, which has people's scores for competitions, for the overall competition i ... A: david Dates in A1:A5000 ID's in B1:B5000 Scores in C1:C5000 ... | |
| Using End(xlDown) to select a range | 2/5/2012 |
| Q: I have this macro which selects data beginning in range A1 of each worksheet in CurrentWB and copies ... A: Carry, Unless copying an entire column or even a blank entire column is causing a problem, then I ... | |
| Top 20 Data Return | 2/5/2012 |
| Q: I have a range of data, which has people's scores for competitions, for the overall competition i ... A: david, Assume names are in A1:A5000 and scores are in B1:B5000 next to the corresponding name. ... | |
| Copy row | 2/3/2012 |
| Q: We have below macro which is copying the row if Column H has any cell colored in ... A: Dev, Probably something like this: Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, ... | |
| Nested IF, AND, OR Statements | 2/3/2012 |
| Q: Tom, I'm trying to figure out the syntax for nested IF, AND, OR logical statements. I have five ... A: Tony, This meets your stated requirement ... | |
| Macro- Msg Box with Yes/NoOption | 2/2/2012 |
| Q: I need a macro help from you. If I run the macro it should pop-up with the msg box with Yes/No ... A: Jayendran Looks like I had a typo in my code. vbYeNo should be vbYesNo. Corrected code below. ... | |
| VBA Lookup to return Multiple Values Horizontally | 1/31/2012 |
| Q: My apology for not been clear earlier. Please ignore my info about the pivot table which was meant ... A: Yogi, I will assume the data is sorted on the Unique ID Sheet Data Column A Column B ... | |
| Sumproduct with multiple criteria across multiple tabs | 1/31/2012 |
| Q: I am working on a spreadsheet where I need to create a Total tab (the spreadsheet is 95% finished ... A: Vlad, based on your description, you want to have a summary sheet that is specific to sheets that ... | |
| Transfer to another sheet | 1/31/2012 |
| Q: Please help. I have the below information from Sheets("Sheet1").Range("C4:C11"). I need this to be ... A: Hudas, I had to restructure my if statement and when I did, I left out part of one of the commands ... | |
| Transfer to another sheet | 1/30/2012 |
| Q: Please help. I have the below information from Sheets("Sheet1").Range("C4:C11"). I need this to be ... A: Hudas, this should do what you describe. Test it on a copy of your workbook until you are ... | |
| Save Unmatch Data | 1/30/2012 |
| Q: please see this my example and help me. in my vba form - txtname is text box - cmdEnter is command ... A: Eran, I set up a userform that matched you description. I had a sheet in the workbook named ... | |
| macro | 1/30/2012 |
| Q: tom, i use a spreadsheet to keep track of a fleet of aircraft and their flying hours. column A would ... A: bruce, First, open a blank workbook and test my suggestion on a meaningless sheet in a thorough ... | |
| When range is selected thats the only time the macro can run | 1/29/2012 |
| Q: I have a macro that inserts another row, my problem is I want that macro to run only when a merge ... A: Hudas, Here is a sample routine that loops through cells a1:A11 and displays a msgbox when it finds ... | |
| VBA Inputting time without semicolons into multiple columns | 1/27/2012 |
| Q: I am attempting to input times in 24hr format into columns 6 and 7 without using the semicolon. I ... A: Jesi, You would just add it to your conditions > columns 6 and 7 so for that (columns 6 and 7) ... | |
| Copy row | 1/27/2012 |
| Q: We have below macro which is copying the row if Column H has any cell colored in ... A: Dev, Untested, but probably something like the additional code I have applied below: Sub ... | |
| Macro to replace Return characters with HTML tag | 1/26/2012 |
| Q: No doubt you already have something to do this :) I don't know the first thing about VB ... Anyway, ... A: Pat, You can actually do this manually. I assume by Return you mean when you do Alt+Enter within ... | |
| D'hondt method | 1/26/2012 |
| Q: before it is too late, happy New year ! Here is where i am : I work for a company, and one of my ... A: Marc, I got it and returned it to you with the first cut of using the function at the web site you ... | |
| D'hondt method | 1/26/2012 |
| Q: before it is too late, happy New year ! Here is where i am : I work for a company, and one of my ... A: Marc, I actually read your posting with Richard (as I recall) - at least two postings. Do you want ... | |
| Using 2 functions in 1 cell | 1/26/2012 |
| Q: I am using MS Excel 2007. I have the following in my spreadsheet: A1: 55.385 A2: 50% A3: 110.77 A1 ... A: A ---------------------- see bottom of post ---------------------- =ROUND(A2*A3,2) will show ... | |
| Macro which uses filtered data | 1/25/2012 |
| Q: I have written a macro that I want to delete some old data, then pick up some new,filtered data on ... A: Gail, If you only have data out to column L, then you can use this Sub ABC() 'Delete all old data ... | |
| Usinf a variable in a worksheet formula with vba | 1/25/2012 |
| Q: I have a macro, where I set the value from Range BZ11 to the variable NewF and then want to put a ... A: Carry, Dim NewF As String NewF = Range("BZ11").Value ... | |
| Usinf a variable in a worksheet formula with vba | 1/24/2012 |
| Q: I have a macro, where I set the value from Range BZ11 to the variable NewF and then want to put a ... A: Carry, It depends what kind of value is in NewF. If you were manually entering the formula, you ... | |
| Add Month to a new line | 1/24/2012 |
| Q: Here's my problem, January is in Cell A1,when i insert a new row below A1 i want the next month to ... A: Hudas, You would need to have a version of excel that fires the change event when a row is ... | |
| Excel formula (Excel 2003) | 1/23/2012 |
| Q: I am using an excel spreadsheet someone else created and I am trying to copy/transpose a formula. ... A: Tanya, there are two ways you could do it. One is to make the refereces to cells in row 5 as ... | |
| problem with editing your macro | 1/22/2012 |
| Q: Sorry to bother you again, Tom. I had to make some minor changes in the cell formula, and I tried ... A: Paul, The macro gets run one time. You can ask as much as you want, but not sure why you are ... | |
| What-if macro? | 1/21/2012 |
| Q: Tom, for your reply just a few minutes ago. Yes, I would appreciate advice on a macro that would do ... A: Paul, here is a macro that does what you describe you want (as I understand it) I generates data ... | |
| setting up automated calculation table | 1/21/2012 |
| Q: How do I make col. D into and average of B+C down the whole list and the same question for col. F, ... A: Mark, Send the file to me an I will take a look. As far was what cells to select, that would ... | |
| What-If analysis | 1/21/2012 |
| Q: I have Excel2007 on a PC and am not familiar with What-If but I suspect that is what I need, ... A: Paul, the way you would need to do this is to generate every combination in your worksheet which ... | |
| auto fill dates/formulae | 1/17/2012 |
| Q: i recorded a macro then tried to improvise, can you please tell me why this code doesnot work? Sub ... A: Nissar, this worked for me: Sub autofill() Dim rng as Range ' autofill Macro ' Keyboard Shortcut: ... | |
| how to pick multiple values vertically corresponding to a cell in MS Excel 2007? | 1/17/2012 |
| Q: Sir, I have the below mentioned data in the excel workbook "Sheet1" A1:A10 - ... A: Jassi, In D1 of Sheet2 I put in this formula ... | |
| Excel | 1/17/2012 |
| Q: How r u ? Hope fine ! Sir When i right click on sheet2 tab and then view code and paste the below ... A: Aman, there is a typo in the code. the correction is below. That is a good reason not to put ... | |
| Delete rows with empty cells from table | 1/17/2012 |
| Q: I wrote a function to delete rows from table if any of a cell is blank. Is there a better way to do ... A: Kumar, Knowledge is key. What you are saying is you delete an entire row if any cell in that ... | |
| VBA event change code doubt | 1/16/2012 |
| Q: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = ... A: Kumar Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = ... | |
| Excel | 1/16/2012 |
| Q: "Hi Mr Tom, First I apologize for not reaching the question to u that i asked. Now below is the ... A: Aman, well, your question has my response which is the correct answer for your first question. ... | |
| setting up automated calculation table | 1/15/2012 |
| Q: How do I make col. D into and average of B+C down the whole list and the same question for col. F, ... A: Mark Select cells D3, D5, D7, etc with D3 as the active cell in the multicell selection in D3 put ... | |
| Excel 2010 Macro/VBA Question | 1/13/2012 |
| Q: I am trying to create a macro/routine that will look at 'cell 1' in 'sheet a', take the value in ... A: Scott, I would see code like this being what you want. Sub ABC() Dim sh1 As Worksheet, sh2 As ... | |
| Excel 2010 Macro/VBA Help | 1/13/2012 |
| Q: I am trying to create a macro/routine that will look at 'cell 1' in 'sheet a', take the value in ... A: Scott, Is this a duplicate question. I believe it is the same question, so here is the routine I ... | |
| convert number to text | 1/12/2012 |
| Q: how to convert a number to text in indian english language A: haleem, There is no built in way to do that (no function provided in Excel for english or any other ... | |
| Log User Activity | 1/12/2012 |
| Q: Happy New Year. Hope 2012 is prosperous for you. I Have a question logging user activity in Excel. ... A: Paul, You can put in code in the activate event in each sheet's code module Private Sub ... | |
| using Lookup command | 1/11/2012 |
| Q: I have Excel 2007 on a PC. I'm not familiar with the reference commands but I think I want LOOKUP. ... A: Paul, I will assume your formula in cell C1 will return exactly a number that is found in column A ... | |
| Tab Color in Excel 2003 | 1/11/2012 |
| Q: I created an excel 2003 workbook with 8 worksheets. I have color coded the tabs to make them easier ... A: Lacy, the ability to color tabs was added in Excel 2003 as I recall. So I suspect that the person ... | |
| formula help | 1/8/2012 |
| Q: I have Excel 2010 I'm working on a spreadsheet that I'm using to help investing. What I need is a ... A: assume that there are headers in row 1 and your data starts in row 2 then A2: a B2: 1234 c2: ... | |
| how to fix a warning and change .xls back to .xlsx | 1/7/2012 |
| Q: First concern, one is the security warning:" data connections have been disabled" pops up every time ... A: Mark, Does the file have a data connection? I assume it must because that is what this message is ... | |
| removing bulk amount of text | 1/7/2012 |
| Q: I am trying to find a way to remove a significant part of a paragraph from several columns. I tried ... A: Michael, assume tweaked sheet is [Webbook.xls]Data so it is in a workbook named Webbook.xls on a ... | |
| Excel | 1/7/2012 |
| Q: How r u ? Hope in pink of health. Sir in A1 when i enter Birth Date i.e 1976 it should give age in ... A: Aman, the undocumented datedif function should do this for you =DATEDIF(Date1, Date2, Interval) ... | |
| Can't figure this out | 1/5/2012 |
| Q: Tom, let's see if I can explain this in a way you can understand. I have a large table of ... A: Dante/Tom assume your data starts in row2 with headers or blank cells in row 1. then in B2 put in ... | |
| using a formula to show results in a certain format | 1/2/2012 |
| Q: I am using Excel 2007 and have done this before but can't remember. Have a column "C" that sums ... A: Dana, Since your in the US, you should be able to select that option by selecting the cells and ... | |
| Copy range to new workbook | 1/1/2012 |
| Q: first off, I wish you a happy New Year, may things important to you go your way! It is the first ... A: Carry, source refers to a specific range in a specific sheet of a specific workbook. So you don't ... | |
| how to fix a warning and change .xls back to .xlsx | 12/31/2011 |
| Q: First concern, one is the security warning:" data connections have been disabled" pops up every time ... A: Mark, I don't get that error message and your statement about a button would indicate this is ... | |
| Creating a search box to search the same column in 4 different sheets | 12/30/2011 |
| Q: What I am trying to do is create a sheet (called Search) with a search box that allows the user to ... A: Ritch, Macros have to be enabled. If you want me to look at it, send the workbook to ... | |
| Creating a search box to search the same column in 4 different sheets | 12/30/2011 |
| Q: What I am trying to do is create a sheet (called Search) with a search box that allows the user to ... A: Ritch, assume in the sheet named Search, you will have cell B9 where the user can enter something ... | |
| Changing picture in image control | 12/29/2011 |
| Q: I have an image control on Sheet1 named iPhoto. I use cell A2 for name input, "Tom Jones". I have ... A: Ed, If I put an image control on a worksheet and wanted to load a picture in it and the image ... | |
| Find and kill VBA | 12/28/2011 |
| Q: Please help me with a vba code to find log.txt file and kill(delete) it. There may be more than one ... A: Raja, Here is some code I adapted from code found on Microsoft Site. It will prompt you for the ... | |
| Deleting blank areas within a range | 12/28/2011 |
| Q: So I have this code that duplicates a range depending on which button is checked and pastes that ... A: Tim, I put some data in row 16 on a worksheet and pasted in your code (with a command button on the ... | |
| Function in excel | 12/22/2011 |
| Q: I have a file in excel.please find the attachment below.Here, the numbers which are in red are ... A: FIRDOSE, You didn't ask me this question. I found it in the question pool. You can easily get the ... | |
| converting time h:mm to whole number | 12/21/2011 |
| Q: I want to calculate how much it cost to do a certain function in excel 2007. I have set up the ... A: pobrien, You didn't ask me this question. I found it in the question pool. Let us say your 0:16 ... | |
| Formatting a number embedded within text cell | 12/21/2011 |
| Q: First of all, my brother in Thailand put your name forward as a friend :) Excel 2003 I have a cell ... A: Pete ="Application Cost ("& Text(D19/D20,"0.00") &" %)" But I would think it should be something ... | |
| how to calculate a formula in excel for schedules | 12/20/2011 |
| Q: This is pertaining to an excel format that we have for schedules. The time range that the employee ... A: Lisa, assuming the value you show is in cell C5 then ... | |
| VBA macro | 12/20/2011 |
| Q: i am very beginer to the excel macros and i have buitd a calculator but when i press digit button ... A: rahul, You didn't ask me this question - I found it in the question pool. Since you provided only ... | |
| Working with Date Names | 12/19/2011 |
| Q: I am trying to create a dynamic spreadsheet for my office that we must use as a check list for ... A: Barbara, Assume in A1 you put in an excel date like 1-Jan-2011 to specify what the month and year ... | |
| VBA for function | 12/18/2011 |
| Q: Please help me with a vba function for the following formula that generates consecutively number ... A: Raja, you didn't say what you wanted to name the function so I named it what you see: Public ... | |
| Number formatting in Excel 2007 | 12/16/2011 |
| Q: I wonder if you can help - is there any way of automatically formatting numbers in all workbooks ... A: Gail, there are a couple of ways you could go on this. One way is to create a new template that ... | |
| Search for and open file VBA | 12/15/2011 |
| Q: I'm currently using Office 2007. I have a macro that opens a word doc based on the file path and ... A: Brian, this is a forum for questions on Excel. When you ask a VBA question about how to do ... | |
| VBA - Find Next with Variable (Loop?) | 12/15/2011 |
| Q: I appreciate you taking the time to read my question. I'm trying to code a macro for work. I've got ... A: Jonathan, untested, but I would be it being something like this: Sub Find2() ' ' Find Macro ' ... | |
| Search for and open file VBA | 12/15/2011 |
| Q: I'm currently using Office 2007. I have a macro that opens a word doc based on the file path and ... A: Brian, I copied your code from your posting and ran it in the immediate window. It put up the file ... | |
| Apply Filter Based on Mutliple Item Selection in Data Validation List | 12/15/2011 |
| Q: created a data validation list to select multiple items. These items get accumulated with a comma ... A: Navaneeth, I don't have this problem. I tested the code in Excel 2007 with several kinds of values ... | |
| Apply Filter Based on Mutliple Item Selection in Data Validation List | 12/15/2011 |
| Q: created a data validation list to select multiple items. These items get accumulated with a comma ... A: Navaneeth Private Sub Worksheet_Change(ByVal Target As Range) Dim v As Variant Dim s As String, ... | |
| Search for and open file VBA | 12/15/2011 |
| Q: I'm currently using Office 2007. I have a macro that opens a word doc based on the file path and ... A: Brian, fName = Application.GetOpenFileName() would return "C:\Documents and Settings\Sample.doc" ... | |
| Unhide multiple rows each time the macro is run | 12/14/2011 |
| Q: I am trying to create a macro that unhides rows each time it is run. If the hidden range is ... A: Keegan, if that works as you want except for expanding the number of rows, then Sub ... | |
| sum formula | 12/11/2011 |
| Q: i have a sheet that as alphanumeric data. and i need a formula to sum this. however, there are same ... A: bruno, I entered this formula =SUMPRODUCT(--IF($B$2:$B$31<>"",SUBSTITUTE($B$2:$B$31," ... | |
| Passing Variable From Main Module to Form Module | 12/9/2011 |
| Q: I have been trying to develop like a time input control form for future reuse. This example is just ... A: Damian --------- added info ---------------- using code that changes the code in the VBE will ... | |
| format to dd/mm/yyyy | 12/9/2011 |
| Q: When i run following code it gives dates like 48938, 52819, etc. Help me to correct the following ... A: Raja, that is what is should give. You then need to format the cell to display the way you want. ... | |
| How to get comma delimited text file into excel using vba | 12/8/2011 |
| Q: I have a question about how to take the text file with several comma delimited tabs and put it into ... A: Mallory, if it has a .csv extension, then you can just open it Sub OpenCSV() workbooks.Open ... | |
| Microsoft Office Excel 2007 | 12/8/2011 |
| Q: I don't know if this is possible or not but below is myWe currently have an incentive that if an ... A: Angie, You said 4 workbooks, but you then use a sample that appears to be 4 worksheets- I assume ... | |
| How to add Years | 12/8/2011 |
| Q: How to add 40 year as shown below to the list of dates i have in Column A? Say, if A1 =25/01/1950 ... A: Raja, A1: =25/01/1950 will divide 25 by 1, producing 25 which will then be divided by 1950 ... | |
| excel formula with E | 12/7/2011 |
| Q: How do I properly translate the following equation into excel? y = 183.01e^4E-06x A: Mark, I am not sure I understand exactly what your formula is. I will guess it is 183.01 times ... | |
| Excel spreadsheet w/Windows explorer filenames | 12/7/2011 |
| Q: appreciate your time and knowledge My question is regarding an answer I located when googling this ... A: Joe, ====================================== added information ... | |
| Formula disappears in listbox linked cell | 12/7/2011 |
| Q: I am working in Excel 2007. You've helped me in the past (thank you very much!) with setting up ... A: Barbara, No setting that you can change to alter the behavior. My perception is that when you ... | |
| Excel spreadsheet w/Windows explorer filenames | 12/7/2011 |
| Q: appreciate your time and knowledge My question is regarding an answer I located when googling this ... A: Joe, This is code that Bill Manville wrote some years ago does a recursive walk through a folder ... | |
| Pass a list of distinct values to cell as a list | 12/7/2011 |
| Q: One of my requirement is to create a distinct list of values from a column (for eg, collect all ... A: Navaneeth, Assumptions you are using the list option for data validation in a cell on one sheet ... | |
| Question on Auto Filter | 12/7/2011 |
| Q: I read your post on how to apply filter based on multiple values on a cell. Please see you code ... A: Navaneeth the code would go in the code module of the sheet containing the C2 referenced in the ... | |
| Retirement date | 12/5/2011 |
| Q: Please help me with a macro to calculate a remaining date to be retire in years, month and date from ... A: Raja, the original worked fine for me. I modified it to only accept a retirement date per your ... | |
| Retirement date | 12/5/2011 |
| Q: Please help me with a macro to calculate a remaining date to be retire in years, month and date from ... A: Raja, Public Function TimeUntil(dtStart As Date, dtEnd As Date) Dim lStart As Long, lEnd As Long ... | |
| Retirement date | 12/5/2011 |
| Q: Please help me with a macro to calculate a remaining date to be retire in years, month and date from ... A: Raja, let me guess that you don't really need a macro to do this. If not, then you can use the ... | |
| vlookup and an if I think | 12/4/2011 |
| Q: I have two spreadsheets that I need to pull information from to create a third spreadsheet to ... A: zfde01 I would copy all of the data on one of the sheets (say the A sheet) and paste it to the new ... | |
| Unique entries from 4 million alpha-numerical numbers VBA | 12/3/2011 |
| Q: I have 4-million alpha-numerical entries from which I need to extract the unique population only. ... A: Omar, You can use the advanced filter to extract a list of unique values in a column. Just choose ... | |
| Reinstating Formula | 12/2/2011 |
| Q: Seeking your help again! Following worksheet change event code works fine when a single cell value ... A: Yogi Dim r as Range, cell as Range set r = Intersect(Target, Me.Range("H11:H55")) If Not r Is ... | |
| auto filling data | 12/1/2011 |
| Q: I have a massive spreadsheet of pieces of equipment for a campus of buildings. Some of the data ... A: Ainsley, assume in the second sheet, you have Column A Manufacturer Column B Vendor Column C ... | |
| insert blank row after row of existing Data | 12/1/2011 |
| Q: Sir please help me with a macro to insert a blank row after every row of existing Data in the ... A: Raja, Sub Addblankrow() Dim r As Range, rw1 As Long, rw2 As Long, i As Long Set r = ... | |
| VBA to insert blank Row | 11/30/2011 |
| Q: Hope you are fine, I have many data in sheet3 from A3:K200. in the L3 to L200 i have a number and i ... A: Abdul Jaleel Sub AddRows() Dim i As Long, cnt As Variant For i = 200 To 3 Step -1 cnt = Cells(i, ... | |
| Update Value with Functions | 11/30/2011 |
| Q: I think this might challenge you a little. I have an issue where I need to update a value but data ... A: Tae Assume the cell with the red arrow is cell C19 so you are looking for the last non-zero value ... | |
| VBA(macro) for Functions | 11/30/2011 |
| Q: I need VBA(macro) Module code for the following Function for adding into my excel 2003 Addin(which i ... A: Raja, Here are the basics. You can add your own error checking. Public Function Something(d As ... | |
| Freeze a result from constantly changing data and execute secondary code when a figure appears in a specified cell | 11/30/2011 |
| Q: I am using Excel 2003 and draw data from an outside source into various cells on a spreadsheet. I ... A: Burt, While this might be possible with intentional circular references, I would generally do this ... | |
| Copy data to new workbook | 11/28/2011 |
| Q: again a question hoping for a solution from you. The macro you see here is based largely on a ... A: Carry, Since C6 never changes, there is no need to use an array to hold the destination. You could ... | |
| Lookups to other Worksheets | 11/28/2011 |
| Q: I'm hoping you can clarify whether the following is plausible? I sm wanting to complete a vlookup ... A: Nick, It is possible only if the workbook 1000.xls or 1001.xls is open when the formula is ... | |
| VBA Adjustment | 11/28/2011 |
| Q: Sorry to bother you AGAIN! But the macro that you have helped me create works perfectly. However ... A: Vish, since we are looping through column B, then I can only guess that you are talking about using ... | |
| How to Auto-Update Macro on Moving Reference Cells | 11/27/2011 |
| Q: Hope this meets you well. Writing to ask if there is a way to have a macro update when its ... A: Maxime, No, vba has no cognizance of what actions you are taking on a worksheet as Excel itself ... | |
| Automate summing number from various time periods | 11/26/2011 |
| Q: I'm using Excel 2010. In files with thousands of rows, generally 10-20 columns I want to automate ... A: Cort, I am not following the description nor do I understand how you determine what is to be ... | |
| VBA Adjustment | 11/26/2011 |
| Q: I need your assistance with the below code you created. I need to add more patterns in the array 1 ... A: Nishant, I made a mistake dividing up your arrays. The correction below works. But don't depend ... | |
| VBA Adjustment | 11/26/2011 |
| Q: I need your assistance with the below code you created. I need to add more patterns in the array 1 ... A: Vish, If you had a regular pattern, perhaps this could be done much easier - but since you skip ... | |
| VBA Adjustment | 11/26/2011 |
| Q: I need your assistance with the below code you created. I need to add more patterns in the array 1 ... A: Vish, Sub ABC() Dim wsData As Worksheet, sh As Worksheet Dim v1 As Variant, v2 As Variant, i As ... | |
| Copy & Paste Macro | 11/23/2011 |
| Q: I have 1 million rows of data that I need to analyse, mostly by using sumif on the whole data range ... A: omar, this worked for me as I understand your requirement (the first method described). I have ... | |
| how to go to a cell in a different worksheet | 11/23/2011 |
| Q: It is a simple question, but I forgot how should I do that. I wanna go to a specific cell which is ... A: Silvio, it depends on whether you want to actually make the cell visible on the screen and select ... | |
| call sheets | 11/23/2011 |
| Q: I have follwoing sheets . summary . NI . Extra . SS What code do i need to have to do the following ... A: Faris, There isn't anyway to put in one formula and have it dynamically pick up the sheet names ... | |
| call sheets | 11/23/2011 |
| Q: I have follwoing sheets . summary . NI . Extra . SS What code do i need to have to do the following ... A: Faris, say your dropdown is in cell A1 of the summary sheet. then in A2 put in this formula ... | |
| Random Generator | 11/23/2011 |
| Q: I want to ask excel to pick a number or character at random from a custom list. I want it to do this ... A: Lilliana in say C10 put in this formula =Index($A$1:$A$7,trunc(rand()*7+1),1) then drag fill it ... | |
| Frequency of Pairing | 11/22/2011 |
| Q: MS Excel 2010 - I have a long list of information and I need to know how frequently one item in a ... A: Cindy, Not sure what you find problematic about my answer. I even offered to do it for you - but ... | |
| Frequency of Pairing | 11/22/2011 |
| Q: MS Excel 2010 - I have a long list of information and I need to know how frequently one item in a ... A: Cindy, See the attached picture for my layout and the formulas. I put this formula in E2 ... | |
| ranking | 11/22/2011 |
| Q: if i have to give the rank to the data, i get the following result. Marks Rank 50 ... A: dei, assume you have 25 numbers starting in A2 and going down to A26. Further assume these numbers ... | |
| Formula Problem | 11/21/2011 |
| Q: I am using excel 2010. The setup I am working with is lets say: Column A is formatted as a drop down ... A: Lorna, IN B1 put in a formula ... | |
| insert space | 11/19/2011 |
| Q: My name is John and i want your help. I have hundreds cells with 13 numbers each one. For example: ... A: John, then my approach wouldn't work - but you didn't ask how to handle letters. You said: >I ... | |
| the consolidation code | 11/19/2011 |
| Q: sorry for bothering you again, the macro runs fine, when i select the workbooks, it open and closes ... A: nissar, the format was used to demonstrate how the other functions worked - it just formatted the ... | |
| insert space | 11/18/2011 |
| Q: My name is John and i want your help. I have hundreds cells with 13 numbers each one. For example: ... A: John, select all the cells, then right click an choose format cells, then choose the number tab and ... | |
| the consolidation code | 11/18/2011 |
| Q: sorry for bothering you again, the macro runs fine, when i select the workbooks, it open and closes ... A: Nissar, you originally said you file names were like 1-1-10.xls and you wanted to match each file ... | |
| VBA question in Excel 2007 | 11/18/2011 |
| Q: This is driving me crazy. I've been trying all day to make a user entry form in VBA. It will not ... A: Jim, I looked at you code and it looked good to me. So I named a worksheet "1" and inserted a ... | |
| Delete string between two characters using VBA | 11/17/2011 |
| Q: Using Excel 2003. I’m trying to delete any string found between a hyphen and Left bracket ... A: Anthony, this code snipped does what your formula does. The results are held in the variable s. ... | |
| If statement using true/false | 11/17/2011 |
| Q: You have helped me before and I am stuck again. I am needing a formula to provide me with a true or ... A: Dana Wylie, Vlook doesn't require that the data be sorted ... | |
| your excel program doesnt work | 11/17/2011 |
| Q: i saw your solution on Excel/Combining data from multiple workbooks into one worksheet but the ... A: the line you cite set sh = Workbooks("Master.xls").Worksheets("Consolidate") would produce that ... | |
| Excel List Comparison | 11/16/2011 |
| Q: I am working with XL 2010 but under compatibility mode(for 2003 etc). I have, two lists. A ... A: RR, ===== Only revision was I changed the last comment since it was originally copied from the ... | |
| VBA question - how to loop through a range | 11/16/2011 |
| Q: I'm trying to figure out how to take data in a worksheet and then use a macro to concantenate the ... A: Kathleen Sub loopdata() ' assume you have headers in row 1 so data starts in row 2 (A2) of sheet1 ... | |
| Adding variables in user forms? | 11/16/2011 |
| Q: I am constructing a multipage userform with a variety of textboxes which require input from a user. ... A: Lee, Here is the situation. The information in a textbox has a data type of string. The "+" ... | |
| Offset and match | 11/16/2011 |
| Q: i have a sheet where i would like it to do the following. c15 has the criteria- which says what it ... A: Faris, go ahead and send the file to twogilvy@msn.com Instructions like "etc" and "..." are ... | |
| missing data | 11/16/2011 |
| Q: I am working in an excel spreadsheet file where I keep a separate spreadsheet for each fiscal year. ... A: Dave, You didn't ask me this question - I found it in the question Pool I assume that each year is ... | |
| VBA copy col to next empty column | 11/15/2011 |
| Q: Hope you can help, I have the following VBA code to copy col F from Sheet 3 to sheet 1. ... A: Alannah, Sub CopyColFtoSheet1() Dim sh1 As Worksheet, sh3 As Worksheet, col As Long Set sh1 = ... | |
| Budget Formulas YTD | 11/15/2011 |
| Q: Im using Excel 2007. I created a simple budget sheet, with months in the columns and departments in ... A: Tony Month(Datevalue(June 1, 2011)) returns 6. so ... | |
| VBA copy col to next empty column | 11/15/2011 |
| Q: Hope you can help, I have the following VBA code to copy col F from Sheet 3 to sheet 1. ... A: Alannah, I will assume if the first row of a column is blank, it is available Sub ... | |
| Budget Formulas YTD | 11/14/2011 |
| Q: Im using Excel 2007. I created a simple budget sheet, with months in the columns and departments in ... A: Tony Paolillo, You didn't ask me this question. I found it in the question pool Say your data is ... | |
| Search & fill | 11/14/2011 |
| Q: with my form i want to add data in sheet.for below example i want to write a code. ex:- 'check A ... A: Eran, the code I gave you should do what you describe. The only new information I see is that you ... | |
| Delete rows between two different strings | 11/14/2011 |
| Q: I am trying to delete all the rows between two different strings. I am using Excel 2003. All my ... A: Anthony, Sub CleanUp() Dim r1 As Range, r2 As Range Application.ScreenUpdating = False ... | |
| converting cells | 11/14/2011 |
| Q: Good morning, Is it possible to convert a cell which mentions "16000 KG" into "16000"? So deleting ... A: Bas, in the menu you can use the replace function (similar to the Find function). First you would ... | |
| Monster headphones | 11/14/2011 |
| Q: Are you going to buy the monster headphones on http://www.bmonsterheadphones.com as the thanksgiving ... A: The quick brown fox jumped over the lazy dog's back. The quick brown fox jumped over the lazy dog's ... | |
| Search & fill | 11/13/2011 |
| Q: with my form i want to add data in sheet.for below example i want to write a code. ex:- 'check A ... A: Eran this is some psuedo code that should show you an approach. It is offered without knowing ... | |
| date and time in excel 14.1.3 | 11/12/2011 |
| Q: I have a lot of data entered as : 11/11/11 17:30 . When displayed it is in a col. that is headed ... A: Mark, if you have the date in a1 and the time in b1, then in c1 put in a formula =a1+b1 and then ... | |
| IF statement help | 11/11/2011 |
| Q: Tom, I am trying to reference a cell on one tab of my spreadsheet to match and sum up cells from ... A: Dana If you want to sum the corresponding values in column D if the value in column A (both on ... | |
| Nested Ifs | 11/10/2011 |
| Q: Not sure if this is your forte or not, but banging my head against the wall with this simple nested ... A: Mike, I think you want your formula this way: ... | |
| VBA Help | 11/8/2011 |
| Q: I am in extreme need of VBA assistance. I am currently trying to create a macro that enables me ... A: Vish, This code loops through 10 rows of data starting in row 3 of the data sheet. For each row, ... | |
| Range Select | 11/7/2011 |
| Q: Excel 2010 I wrote a macro a week ago, that opens another workbook/sheet (WBout / WSout1) and ... A: Craig, First, your code causes an error for me. Selection is a property of the Window or ... | |
| convert text to valuxe | 11/5/2011 |
| Q: I am displaying the value I want to use in a formula as text in one cell and then I would like to ... A: Gary, there is no built in support for what you want to do. I know of a free addin that will ... | |
| Combine VLOOKUP with OR | 11/5/2011 |
| Q: Excel 2003 - I have a worksheet with two columns. Column A is a combination of 2-letter state ... A: Robert, your discription made it sound like expecting all states would be reasonable. Nonetheless, ... | |
| Combine VLOOKUP with OR | 11/5/2011 |
| Q: Excel 2003 - I have a worksheet with two columns. Column A is a combination of 2-letter state ... A: If you can assume that all of DE, MD and PA will be in the list somewhere and you want the first ... | |
| returning part of a cell's contents | 11/3/2011 |
| Q: I am trying to extract only part of a cell's information into another cell, based on what's in that ... A: Stephanie, =IF(COUNTIF(A2,"*regulating*")>0,"REG",MID(SUBSTITUTE(SUBSTITUTE(A2," ... | |
| Returning only part of a cell's contents | 11/3/2011 |
| Q: I have a part number description (in one cell) that is lenghty and I need to be able to orgainze ... A: Stephanie, =IF(COUNTIF(A2,"*PSI*")>0, ' if PSI is in the cell then ... | |
| Returning only part of a cell's contents | 11/2/2011 |
| Q: I have a part number description (in one cell) that is lenghty and I need to be able to orgainze ... A: Stephanie, if the number that you want to retrieve is always 1 or 2 digits followed by a space and ... | |
| Insert Columns with code | 11/2/2011 |
| Q: I have a workbook which is contingent upon the number of years of data (usually between 3-6) I ... A: Bill, You subject says insert columns and the body of the email says number of rows. ... | |
| Followup to Copy and Paste Macro | 11/2/2011 |
| Q: However, the Macro isn't doing much at all. If you could please take a look again, it would be much ... A: Tae, Looks like there was a typo in the code where I used Sh1 where I should have used Sh. I have ... | |
| Find - Loop Once Only When Clicked | 11/2/2011 |
| Q: I have some code shown below that performs a perfectly good find loop to find all blanks. I am ... A: Paul, you say a button named CurrRecNew holds the value of the row number - I am not sure what that ... | |
| working with email addresses | 11/2/2011 |
| Q: You assisted me a long time ago with some very complex (they were to me) excel problems and your ... A: Mike Castlehouse, If I have house@email.com in A1 then in B1 ... | |
| Multiple potential inputs | 11/2/2011 |
| Q: I'm using Excel 2010, although I have 2007 available if that's more familiar. I have a file with 4 ... A: Bill, with just formulas, you won't be able to do what you want if you only want to use 4 columns. ... | |
| Transpose multiple rows into 2 columns | 11/1/2011 |
| Q: I have table which contains a header on top of each column (can be x amount of columns) with data ... A: Nathan, I believe this is what you want: Sub abc1() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 ... | |
| Transpose multiple rows into 2 columns | 11/1/2011 |
| Q: I have table which contains a header on top of each column (can be x amount of columns) with data ... A: Nathan, You can do it with a macro or you could do it with formulas. Since you asked for a macro, ... | |
| Excel Macro / Sequential Numbering | 10/31/2011 |
| Q: I have a form in Excel that is great and is used to document data regarding a single item. I use a ... A: Paul, since you asked for a macro, I assume you know how to use macros. Assume the worksheet to ... | |
| Copy the first 30 words from a cell | 10/31/2011 |
| Q: my problem is that I am trying to copy the first 30 words from a cell with information pertaining to ... A: David, Actually, you can use any character in place of the 1. You just have to make sure that ... | |
| Excel Formulas | 10/29/2011 |
| Q: I will try to explain the best I can. Sheet 1 Cell F3, I want information from Sheet 3 Cell H3 I ... A: Malcolm, since you only want the link on every other line, the IF statement makes that conditional ... | |
| Excel Formulas | 10/29/2011 |
| Q: I will try to explain the best I can. Sheet 1 Cell F3, I want information from Sheet 3 Cell H3 I ... A: Malcolm, IN F3 put his formul =IF(MOD(ROW(),2)=1,INDIRECT("'Sheet 3'!H"&(ROW(A1)/2)+2.5),"") then ... | |
| Conditional range copy | 10/28/2011 |
| Q: (vba question using Excel 2010) I have a workbook with two sheets, “sheet1” and “sheet2”. Both have ... A: Carry ----- line wrap problem corrected - but this macro assumes that the same items are on the same ... | |
| Conditional range copy | 10/28/2011 |
| Q: (vba question using Excel 2010) I have a workbook with two sheets, “sheet1” and “sheet2”. Both have ... A: Sub ABCEFG() Dim r1 as range, r2 as range, cell1 as range, cell2 as range set r1 = ... | |
| VBA Code to print pdf. | 10/26/2011 |
| Q: this is an addition question to the question i asked today(I have PDF file name with path address ... A: Abdul Jaleel, I understand you want to email each of the PDF files listed in C3:C1500. I will ... | |
| Add [X] points to Auto-fitted Row height | 10/26/2011 |
| Q: Please help here!: I am working with a database about 30 columns and 5,000 rows; some rows are ... A: Ryan, My name isn't TIM. It is TOM. I can't think of any situation that would cause that - but ... | |
| list and count how many times each unique value is repeated | 10/25/2011 |
| Q: In column F, I have 6 digit numbers (random auto assigned person ID #. Each row is for a separate ... A: Dave, this explains in great detail how to get your unique list. No use me repeating what already ... | |
| Add [X] points to Auto-fitted Row height | 10/25/2011 |
| Q: Please help here!: I am working with a database about 30 columns and 5,000 rows; some rows are ... A: Ryan Ochs, here is a macro. Change the 7 to whatever you want it to be. (it isn't pixcels - it is ... | |
| how to do the arrow goes down before copying in VBA | 10/24/2011 |
| Q: how to do the arrow goes down before copying in VBA Hi Tom, I did the copy and paste stuff and tks ... A: Silvio, that is the first thing I gave you becuase it wasn't clear what you wanted to do. It ... | |
| how to do the arrow goes down before copying in VBA | 10/24/2011 |
| Q: how to do the arrow goes down before copying in VBA Hi Tom, I did the copy and paste stuff and tks ... A: Silvio, No, it is not very clear what you want. Here is another guess. Sub resultados() Dim ... | |
| Data Entry Macro | 10/24/2011 |
| Q: I would like to create a macro to enable quickly data entry for a significant amount of data. With ... A: Wayne, Macros don't support this type of activity. However, you can select say C2:G10 with C2 as ... | |
| how to do the arrow goes down before copying in VBA | 10/24/2011 |
| Q: how to do the arrow goes down before copying in VBA Hi Tom, I did the copy and paste stuff and tks ... A: Silvio, Here is a possibility - I don't know why you are going down or what code you want to ... | |
| Macro to Copy and Paste | 10/24/2011 |
| Q: I have about 30 worksheets all named Tab#1, Tab#2, etc. And each worksheets are identical in the ... A: Tae, this macro should do that Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, r1 ... | |
| how to change a whole column of C | 10/23/2011 |
| Q: I need to convert a whole column of centigrade #s (about 1200 of them) to fahrenheit . I know that ... A: Mark, assume your data is in C1:C1200 assume you next empty column is column M in the name box to ... | |
| VBA for shapes renaming based on 2 lists combination | 10/23/2011 |
| Q: I have VBA that changes shape name based on 2 cells value that's are validation lists from sheet ... A: Shue, Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim shp As Shape ... | |
| VBA for shapes renaming based on 2 lists combination | 10/23/2011 |
| Q: I have VBA that changes shape name based on 2 cells value that's are validation lists from sheet ... A: stue, I changed you data validation so I could type in a different value in the Sheet named Text. ... | |
| Need an Macro for Down Arrow. | 10/23/2011 |
| Q: I need a macro for down arrow. I used this macro. "ActiveCell.Offset(1, 0).Select". but it is not ... A: Jay, you can try this single line command Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ... | |
| extracting usable info from a text string | 10/21/2011 |
| Q: Now I need to pull a subset of an alphanumeric text string into a separate cell. Here's what they ... A: Barry, assume that your first sample string is in cell A1 (and second in A2, etc), so in B1: ... | |
| Excel formula troubles | 10/21/2011 |
| Q: I'm trying to create a formula that would sum extracted mid data from a column. Example : Cell A1 ... A: Gennadi, =SUM(IF(LEFT($A$1:$A$10,1)="A",MID($A$1:$A$10,2,255),0)*1) entered with Ctrl+shift+enter ... | |
| copy just numbers | 10/21/2011 |
| Q: Tom, I forgot to ask this. If I wanna copy just numbers (not the formula at a cell), how can I do ... A: Silvio, Range("A1").copy Range("B1").PasteSpecial xlValue or Range("B1").Value = ... | |
| question copy and paste VBA | 10/21/2011 |
| Q: I am trying to do a copy and paste values.I need to do that simustaneous to different cells. I will ... A: Silvio, You can't do in code what you can't do manually. Generally, you should do each copy and ... | |
| Macro | 10/20/2011 |
| Q: Me again... I am using Excel 2007. I want to create a macro that would sum all the amounts formated ... A: Tom, VBA doesn't have a way to get conditional formatting colors or to work with them. Chip ... | |
| Macro | 10/20/2011 |
| Q: Me again... I am using Excel 2007. I want to create a macro that would sum all the amounts formated ... A: Tom, both routines in the same module - yes but they should be in a general module which you get by ... | |
| Macro | 10/20/2011 |
| Q: Me again... I am using Excel 2007. I want to create a macro that would sum all the amounts formated ... A: Tom, Public Function SumInitialReturnsFunction(rRange As Range) As Double Application.Volatile True ... | |
| Conditional formating based on formula | 10/20/2011 |
| Q: I am using Excel 2007. I am trying to format (green font)a dollar amount in cell C3 based on cell ... A: Tom, conditional formatting is a format - so if you copy and paste (within excel), you overwrite ... | |
| Lists | 10/20/2011 |
| Q: In cells a1:a6 I have a list of names In cells a7:a13 I have a list of numbers in cells b1:b6 I have ... A: Peter, Apparently Edward didn't understand or for whatever reason couldn't address your question ... | |
| Excel 2003 - Make cells in two columns required | 10/20/2011 |
| Q: Tom! I have two columns of an Excel spreadsheet that I would like to require data to be entered ... A: Stephanie, Your code right now gives the impression that something must be in every cell in columns ... | |
| Excel 2003 Macro | 10/20/2011 |
| Q: After reading what info I could get on radio buttons I understand that I need macros. I made a ... A: Jim, If you want to send me a sample sheet, I can take a look. twogilvy@msn.com tell me exactly ... | |
| Accessing a User form | 10/19/2011 |
| Q: I have a userform and would like to know if it is possible to call the userform by clicking a cell? ... A: Mike, it is best to keep things simple. Load Reply Reply.fpolicy.Value = Target Set ... | |
| Accessing a User form | 10/19/2011 |
| Q: I have a userform and would like to know if it is possible to call the userform by clicking a cell? ... A: Mike, when you do reply.show the code stops executing at that command and the reply.show userform ... | |
| largest number in variable-sized range | 10/18/2011 |
| Q: In one column (A below) are one to multiple unique ID numbers, sorted. In another column (B below) ... A: Barry, the easiest solution would be to use a pivot table and choose the max option for your ... | |
| Change date when specific cell changes | 10/18/2011 |
| Q: I have a simple spreadsheet in which one column has status descriptions listed and another column ... A: Karen, lightly tested, but see if this works for you Private Sub Worksheet_Change(ByVal Target As ... | |
| Change date when specific cell changes | 10/18/2011 |
| Q: I have a simple spreadsheet in which one column has status descriptions listed and another column ... A: Karen, yes, it can be programmed via VBA. you would right click on the sheet tab where you want ... | |
| Offset Non Active Sheet | 10/18/2011 |
| Q: hope you are well, i seem to have hit another snag. My userforms were working but i needed to use ... A: Paul, > My userforms were working but i needed to use activex so i added all my controls to a ... | |
| Conditional formating | 10/17/2011 |
| Q: Hope you are fine! I have data as follows origin Dest. Season Amt CPH WAS H 769 CPH WAS H 832 ... A: Rakesh ghole, Assume your first data point is in row 2 in columns A:D and A1 is the active cell ... | |
| Userform Autosize Text Box | 10/16/2011 |
| Q: i know im pressing my luck here. I know there are countless desperate excel users ask you questions ... A: Paul, In light testing, this worked fine for me: Private Sub TextBox1_Enter() TextBox1.Height = 50 ... | |
| Accessing a User form | 10/16/2011 |
| Q: I have a userform and would like to know if it is possible to call the userform by clicking a cell? ... A: Mike, you can use the ChangeSelection event, but then you would not be able to edit the cell ... | |
| Giving an option to user to name the file | 10/16/2011 |
| Q: I am using this module that save an excel that comes as a copy of old file to a fixed location and ... A: Navin, Here is a correction. Sub ABC() Dim sPath As String, sName As String Dim s As String, s1 ... | |
| Userform Autosize Text Box | 10/16/2011 |
| Q: i know im pressing my luck here. I know there are countless desperate excel users ask you questions ... A: Paul, If you look at the help on autosize, it says the designed behavior is exactly as you ... | |
| Giving an option to user to name the file | 10/16/2011 |
| Q: I am using this module that save an excel that comes as a copy of old file to a fixed location and ... A: Navin, for some reason, your example doesn't have any back slashes in it so I can not separate the ... | |
| If value of cell in column f = m, then lookup value of cell in same row, different column, then sum | 10/15/2011 |
| Q: I hope you can help. I have to recreate accounting data which was lost due to computer crashing, and ... A: Dory, -- I have added some thoughts after I read you subject - as I say in my intro or at least I ... | |
| If value of cell in column f = m, then lookup value of cell in same row, different column, then sum | 10/15/2011 |
| Q: I hope you can help. I have to recreate accounting data which was lost due to computer crashing, and ... A: Dory, I would try =Sumif($F$2:$F$9999,$F7,$C$2:$C$9999) I don't have a mac, but that is what I ... | |
| Macros | 10/15/2011 |
| Q: Sir, a1 = 01/05/09 b1 = 01/09/09 Macro have to be required is : Formula to be copied: ... A: Sub copydown() Dim i As Long i = 2 If isdate(Range("B1")) and isdate(Range("A1")) then Do ... | |
| VBA question | 10/15/2011 |
| Q: Hope you are fine. I have two question 1)I have following recorded code for inserting subtotal which ... A: Abdul Jaleel Sub SubTotal() Dim r as Range set r = ... | |
| sequential numbering | 10/14/2011 |
| Q: Excel 2007 Want to have a template document and fill in some standard info on it and then indicate ... A: Lisa, You would need a macro to do this. I will hypothesize a situation to illustrate. sheet ... | |
| VBA - Userform Multiple Row Insert | 10/14/2011 |
| Q: I am hoping you can give me some advice on my not so elegant and not working well code. In a ... A: Paul, -- That is kind of vague to say it isn't working. if you want to send me a simple copy of ... | |
| Formula to calculate what percentage of groups have completed a course | 10/14/2011 |
| Q: Please see the screenshot so you can understand the information provided below Total Number of ... A: =sumproduct((B7:B23="Admin")+(B7:B23="Resource Scheduler")+(B7:B23="Ops Support ... | |
| Extracting values from another worksheet | 10/14/2011 |
| Q: Apologize for not making myself clearly understood earlier. I am using excel 2007 and I need to use ... A: Tithee, ------------- Why the extremely negative rating. I can assue you that based on what you ... | |
| Excel Sorting Terms | 10/13/2011 |
| Q: I have MS Excel 2007. I have 4 columns that I want to sort. Each column has a heading. The left ... A: Kee, select all your data including row 1 and on the home tab in the ribbon, go to the right side ... | |
| Excel 2007 | 10/13/2011 |
| Q: I have the following data in cell A1: 5418-9900-9999-8888 5418-9800-8888-9999 The user want to ... A: Enid, It sounds like you want to have a data validation dropdown box in cell A1 (use the list ... | |
| Blank cell merging | 10/12/2011 |
| Q: I have a macro that merges empty cells to the cell above it that has data. I need help making ... A: Von, As to the 1st issue. It sounds like the macro is doing exactly what you want with respect to ... | |
| excel control/activeX toolbox | 10/12/2011 |
| Q: I have been searching this on the web and all by myself in excel but i'm still yet to find the ... A: William, In Excel 2007, if I go to the Developer Tab, then I can insert Form Controls or ActiveX ... | |
| Copy sheet by vba | 10/12/2011 |
| Q: I want to copy mutliple sheets to new file using array. at present i am using this code. ... A: vimal, Yes, it can be done. It depends on how your list of sheet names is oriented. If they are ... | |
| Generate invoice Numbers in Userforms | 10/12/2011 |
| Q: I am generating a userform that needs a ID number for each row of the database spreadsheet. I would ... A: Mike, say your msgID is found in column A of sheet Data then Private Sub CommandButton1_Click() ... | |
| Sumproduct in VBA | 10/11/2011 |
| Q: I tried the code that you gave me and it worked! myCount = Application.Evaluate("SUMPRODUCT((A2:A10 ... A: Jun Same way you would in if you wrote the formula in a cell manually myCount = ... | |
| Formulas spanning multiple sheets | 10/11/2011 |
| Q: Is it possible to create a formula that includes data from more than one sheet? For example, if I ... A: Karen, Yes. =Sheet1!B30-Sheet2!B22 or Sheet1!B30-Sum(Sheet2!B5:B21) or ... | |
| Sumproduct in VBA | 10/10/2011 |
| Q: I tried the code below it doesn't work the result is always 0. Sub Jun() myCount = _ ... A: Jun, did you try that formula directly in your worksheet. It doesn't work there either. The ... | |
| How to get the higher and the lower number? | 10/9/2011 |
| Q: 1st QUESTION I have a list with number in a column from which I need to get the higher number and ... A: Silvio, If you are looking at the entire column, why would you care about the last used row. ... | |
| Clearing content of duplicate text | 10/8/2011 |
| Q: How to write vba code to clearing content of duplicate text in same column? i just want ,only one ... A: Sub ABC() Dim lastrow As Long, rStart As Range Dim rng As Range, rng1 As Range, cell As Range ... | |
| Excel Data Combinations | 10/7/2011 |
| Q: I have an worksheet with 9 columns. The first column is the ID (unique values)and 4 columns are ... A: Sam, OK. J2: =((F2-1)*36+1)+((G2-1)*12)+((H2-1)*6)+I2 then drag fill down the column J. (I ... | |
| Data sorting | 10/7/2011 |
| Q: You are simply great! Without testing you solved my problem. 99.9999% its working perfectly. Only ... A: Zaina, Sorry - I think I got myself confused. It is difficult to write code with no ability to ... | |
| Data sorting | 10/7/2011 |
| Q: You are simply great! Without testing you solved my problem. 99.9999% its working perfectly. Only ... A: Zaina, Sub abc() Dim sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As ... | |
| Conditional Formating Color values | 10/7/2011 |
| Q: I am running Excel 2007 and am quite new. I am trying to find the formula or conditional formatting ... A: JP, Since you rated me the worst expert in the group, what kind of answer were you looking for???? ... | |
| Excel Data Combinations | 10/6/2011 |
| Q: I have an worksheet with 9 columns. The first column is the ID (unique values)and 4 columns are ... A: Sam, is this a VBA question or are you looking for a formula. please explain how you group the ... | |
| Tab Names | 10/6/2011 |
| Q: Is there any macro code using which we can pull out just the names of tabs of a particular excel ... A: Fareed, I thought I initialized rw to be 1, but guess I was just thinking that I needed to do that ... | |
| Tab Names | 10/6/2011 |
| Q: Is there any macro code using which we can pull out just the names of tabs of a particular excel ... A: Fareed, Is the file open in the same instance of excel. Assume it is Make the destination sheet ... | |
| Data sorting | 10/6/2011 |
| Q: You are simply great! Without testing you solved my problem. 99.9999% its working perfectly. Only ... A: Zaina If the header isn't in the first row, then where would it be? I need to know how to find it. ... | |
| Where two cells cross what is their value | 10/6/2011 |
| Q: Sorry, made a typo - I am looking for it to show the value in cell H25 - where both the row and ... A: Francis, I assume 12345 is in cell A25 on say Sheet1 and you have a date in H5 on Sheet1. ... | |
| Compare two worksheets using VBA in Microsoft Excel code not working | 10/5/2011 |
| Q: I tried the code mentioned in this link, ... A: Pam, You are in this loop when you get the error: For c = 1 To maxC Application.StatusBar ... | |
| Auto import data into a 2nd sheet | 10/5/2011 |
| Q: Working on another sheet for the Fire Department :) I am going to be entering data into the main ... A: Mike, I think it is the same amount of code. set sh = Worksheets(me.TextboxK.Text) rw = 20 ' ... | |
| Auto import data into a 2nd sheet | 10/5/2011 |
| Q: Working on another sheet for the Fire Department :) I am going to be entering data into the main ... A: Mike R. I think I would use the submit button on the userform so you don't risk a premature writing ... | |
| Compare two worksheets using VBA in Microsoft Excel code not working | 10/4/2011 |
| Q: I tried the code mentioned in this link, ... A: Pam, the code worked fine for me without modification. It is designed to run without modification. ... | |
| Macro | 10/4/2011 |
| Q: I am using the following macro to copy down a row of formulas in (A1:O1). The number of rows to ... A: Wayne You don't need to do a copy to do a fill down - so I don't think you need the first ... | |
| VBA Macro to move one cell to sheet2 continually adding data | 10/4/2011 |
| Q: Hey Tom, I would like to create a macro that will allow me to collect information (cell phone data) ... A: Jackie, I would suggest a macro for this. If you want to send a simple workbook I can put a macro ... | |
| vlookup in 2d | 10/4/2011 |
| Q: I am looking for a way to apply vlookup on more than one column in one go. If vlookup function ... A: Kevin B2: =vlookup($A2,TableA,column(B$1),False) will do that. Tested and worked fine for me. ... | |
| Recognising dates as dates rather than numbers | 10/4/2011 |
| Q: I wonder if you can help me again. I have created a pivot table which adds up costs that were ... A: Gail, If I look at your dates in your example, they are in descending order based on a text sort. ... | |
| formula returning a blank | 10/3/2011 |
| Q: I am using a formula that pulls data from a cloumn if the a particular cell is blank then i want ... A: Mike, the best you can do is something like this =if(H5="","",1.35*(h5*.15)) the cell won't ... | |
| Number present as string and not able to perform mathematical operations | 10/3/2011 |
| Q: I am working on an excel sheet in which some of the data is present as numbers in the form of string ... A: Naveen, this worked for me. I don't know if it will be faster. Sub ConverttoNumbers() Dim sh As ... | |
| Vlookup Formula | 10/3/2011 |
| Q: I have two tables. One is Database & the other one is Result. In the Database Table, i have three ... A: Sivaraja, Vlookup doesn't work with two columns (although in your sample data, the lookup doesn't ... | |
| Excel Date range and "now" | 10/3/2011 |
| Q: I have a question regarding excel date format I have a group of people where they will have rotation ... A: dr. Eugene, I don't know where you live, so I don't know whether your dates are expressed as ... | |
| MVB question | 10/2/2011 |
| Q: I have just started Microsoft Visual Basic learning and I have - I think - a very easyI created an ... A: Gabor, the error message does tell you what the problem is. You have named your module Timesheet ... | |
| rounding a number almost like INTEGER | 10/1/2011 |
| Q: It is my first time in this site....let´s see if it really works...I need to round the variable "x" ... A: Silvio instead of Dim x as Integer use Dim x as Long Long is a long integer. It uses four ... | |
| Recording iterations of a call date by updating a single cell | 9/30/2011 |
| Q: I have Excel 2007. I'd like to track details of my phone calls by changing a single cell while ... A: Paul, Sorry for the delay, but the code doesn't write itself. If this isn't what you want, then ... | |
| Recording iterations of a call date by updating a single cell | 9/30/2011 |
| Q: I have Excel 2007. I'd like to track details of my phone calls by changing a single cell while ... A: Paul, The easiest way to do this is to use a macro. In general you would use the change event. ... | |
| Blank cell merging | 9/30/2011 |
| Q: I have a macro that merges empty cells to the cell above it that has data. I need help making ... A: Von, Lightly tested, but this worked for me as I understand your requirement. There is no ... | |
| vlookup in 2d | 9/29/2011 |
| Q: I am looking for a way to apply vlookup on more than one column in one go. If vlookup function ... A: Kevin, as an example, you can select 3 cells in a row and then put this type of formula in the ... | |
| Fill Blanks | 9/29/2011 |
| Q: hope you are well - Im trying to write a macro that copies the cell above to the cell below. The ... A: Paul, try this Sub FillBlanks() Dim r As Range, r1 As Range, r2 As Range Set r = ... | |
| Combining Rows and Adding the Balances | 9/27/2011 |
| Q: Let me start by thanking you for your consideration on this topic, I hope this will be an answerable ... A: Justin, without seeing our macro it is hard to say if a change can be incorporated in your macro. ... | |
| countif problem | 9/27/2011 |
| Q: I have two colums Column A has date and Column B has names. I need to know by formula in Column C if ... A: Umesh, assume 23-Sep-11 is in A2 and Jack is in B2, then in C2 put in this formula ... | |
| Compile Error: Argument Not Optional | 9/26/2011 |
| Q: Hope you are well. Once again another problem that I'm sure you'll solve in no time. I have the ... A: J, dim r as range set r = Activesheet.Usedrange set r = r.offset(2,0) set r = r.resize(r.rows.count ... | |
| Excel Copy Question | 9/26/2011 |
| Q: I am using Excel 2003. On my main sheet, called "203" I use column "F" to enter specialty team ... A: Mike R. Put this formula in Row 10 on sheet 205 ... | |
| now function | 9/26/2011 |
| Q: I have a excel time clock all set up and all formulas work the only thins I need to figure out is I ... A: Jimmy, Your original code used the Now() worksheet function ActiveCell.Formula = "=now()" which ... | |
| Compile Error: Argument Not Optional | 9/26/2011 |
| Q: Hope you are well. Once again another problem that I'm sure you'll solve in no time. I have the ... A: J, the most obvious problem I see is you have this command Call Archive_Data but the declaration ... | |
| Fill up a range | 9/26/2011 |
| Q: Here's the scenario I manage to write the code below but it is incomplete. Private Sub ... A: Jun, this worked for me Private Sub CommandButton1_Click() Dim rw As Long, r As Range, cell As ... | |
| Increment Row Numbers in a 'SUM' using VBA | 9/25/2011 |
| Q: I have a largish sales spreadsheet - 16000 rows x 60 columns. I need to summarise it. I have spent ... A: Seb, you have defined a situation, but I don't know what you are asking me - I don't know what your ... | |
| Macro for Filtering based on special criteria | 9/24/2011 |
| Q: I hope you can help on this, I am a crew coordinator for offshore workers and I control their ... A: Diogo, Your picture is too small to make out much detail. Select the column with the E and D's ... | |
| inventory to shopping list spreadsheet | 9/24/2011 |
| Q: I have a spreadsheet to generate a shopping list from an inventory list. On Sheet 1, I have the ... A: Wanda, the autofilter is static - it filters at the moment you tell it to filter and then it stays ... | |
| Excel VB: Macro not working. | 9/24/2011 |
| Q: I was wondering if you can help. The macro is supposed to work when I enter a value in a cell, the ... A: Polo, this worked for me: =====<See revised comment below... Private Sub ... | |
| Data sorting in Excel | 9/24/2011 |
| Q: I am using Excel 97. I received an Excel spreadsheet with a list of companies. Each company on the ... A: Wing, I will make the assumption that the SIC codes are numeric in nature such as 2411. If that ... | |
| Find common values in 2 columns only | 9/24/2011 |
| Q: I am quite new to excel. Basically, I want to find common values in 2 columns only and not a single ... A: Deepak, ============<revision/added information>========== since you are not having success, this ... | |
| How to format cells of a pivot table based on the row and column labels | 9/24/2011 |
| Q: I created a pivot table that has pertange values from 40% to 120% (40%, 60%, 80%...) as the column ... A: Pam, it doesn't sound like your row and column headers would change. So if you just mean that the ... | |
| Conditional formula in Excel 2003 | 9/23/2011 |
| Q: I have a list of birth dates In column D descending from D5. What I want to do is highlight the ... A: Peter, Conditional formatting supports three different conditions. so you would do select the ... | |
| now function | 9/23/2011 |
| Q: I have a excel time clock all set up and all formulas work the only thins I need to figure out is I ... A: Jimmy, Since you don't show you macro, the onlything I can guess is that it does something like ... | |
| Userform code | 9/22/2011 |
| Q: I am creating a userform for a cutomer contact information(all text boxes). I am have trouble with ... A: assume you have Private Sub Workbook_Open() Userform1.show End Sub now it would be Private ... | |
| Userform code | 9/21/2011 |
| Q: I am creating a userform for a cutomer contact information(all text boxes). I am have trouble with ... A: Andre, Are you talking about the sheet or the entire excel application. I am guessing you mean to ... | |
| sum a table with condition in vba | 9/21/2011 |
| Q: I have a table in excel like below: name region sales Amount varun India 10 mayank India 20 ... A: Varun the basic code would be: Sub abc() dim cell as range, tot as double tot = 0 for each cell in ... | |
| Userform code | 9/21/2011 |
| Q: I am creating a userform for a cutomer contact information(all text boxes). I am have trouble with ... A: There is no standard code. basically you have to keep track of the row where you are writing the ... | |
| Macro to Copy Non Blank Rows within A Range | 9/20/2011 |
| Q: G'day from Australia. Seeking your help again! Need: A macro that will: 1. loop through a folder ... A: Yogi, Here is some untested pseudo code that should get you started. Sub ABC() Dim sh As ... | |
| Autopopulate sections from seperate sheet-+ | 9/19/2011 |
| Q: I hope you can help me with this. I am trying to figure out how to design a master sheet that auto ... A: Matthew, your image just looks like a bunch of colored boxes - it is too small to make out any ... | |
| Absolute Reference Help or another work around | 9/16/2011 |
| Q: I have been working on a very big spreadsheet and I need some help. I get a .csv file from my boss ... A: BTO The easiest would probably be to write a macro the brings over the data. If you want to ... | |
| Using "selection" | 9/16/2011 |
| Q: (Vba question in Excel 2010) I want to select a sheet and in column A select a range from Cell A6 ... A: Carry, Sub EFG() Dim ar As Variant, i As Long, r As Range, r1 As Range ar = Array("Client1", ... | |
| Date Calculations w/in Conditional Formulas | 9/15/2011 |
| Q: I want to average the numeric values in one column when the dates values in a second column fall ... A: Cathy, by including the cell address (U9) of the cell with the conditional date within the quoted ... | |
| Removing month out of a date field function needed | 9/15/2011 |
| Q: Could you please tell me an EXCEL function to pull the month out of a date field? Thank you, Donna A: Donna, A1: =today() B1: =Text(A1,"mmm") ' this would return "Sep" or B1: =Text(A1,"mmmm") ' ... | |
| Insert picture in excel sheet | 9/12/2011 |
| Q: could you help me with this? How can I insert multiple pictures at once into specific range of sheet ... A: Juha, You can try this code. It should do what I understand you to want. To stop selecting ... | |
| VBA Code | 9/11/2011 |
| Q: this is clarification to the question i previously asked, pasted below "I have excel file, in the ... A: Abdul jaleel, I believe this is what you want Sub copyData2() Dim r As Range, sh As Worksheet Dim ... | |
| Data Copy | 9/10/2011 |
| Q: Need your help in macro which can copy data which I’m looking-for from all sheets. I will try to ... A: Fareed, This should do what you want Sub copydata() Dim sh As Worksheet, sh1 As Worksheet, rA As ... | |
| Data Copy | 9/10/2011 |
| Q: Need your help in macro which can copy data which I’m looking-for from all sheets. I will try to ... A: Fareed, Tested with respect to my understanding of your requirement and this worked for me. ... | |
| Excel Macro to Merge Empty Cells | 9/9/2011 |
| Q: I am using Excel 2007. I need an Excel Macro that will go through each column in the range of ... A: Ken, If I understand the requirement correctly, this worked for me: Sub MergeCells() Dim cell As ... | |
| slowing down the update | 9/9/2011 |
| Q: Tom, thank you for the quick response. Your macro looked like it ran through the sequence but it ... A: Todd, OK. Unless you are going to leave the workbook open, I would remove the update every two ... | |
| Looping through a variable amount | 9/9/2011 |
| Q: I want to loop through to a variable amount. For example, I want x to loop to y, but y can change ... A: Hebron, in a for next loop, the loop limits are set when the loop is entered. So you are correct - ... | |
| slowing down the update | 9/9/2011 |
| Q: Tom, thank you for the quick response. Your macro looked like it ran through the sequence but it ... A: Todd, the code does not continue until the query is complete. If the code I sent solves the ... | |
| enter in a numerical value that returns text | 9/9/2011 |
| Q: I am in need of a formula where I can enter in a 3 digit code from 000 to 907 that once entered will ... A: Justin, Vlookup is the correct function. It sounds like you have a mismatch on data types. If I ... | |
| issue with getting data to download | 9/9/2011 |
| Q: I have been working with Bob Umlas on an issue that he has helped me with greatly however, I've come ... A: Todd, that code had an error in it. Try this instead: Sub OpenandUpdate1() Dim fl As String Dim ... | |
| Activating Object with a Range | 9/9/2011 |
| Q: I have a basic question but I have no clue how to do this stuff. I have this macro saved under a ... A: Ramsey, actually, as written, the macro will play the wave object if you edit any cell and P ... | |
| IF AND FUNCTION | 9/8/2011 |
| Q: I need to create a formula that gives me the following result: if cell B19=YES and cell B20=YES, ... A: Esther Cabezos, You didn't ask me this question, I found it in the question pool. It looks like ... | |
| Snaking columns on a page | 9/7/2011 |
| Q: I am on Windows XP and using Office 2007. I have a two column list of codes (column A) and ... A: Mark, David McRitchie has a snakecolumns macro at his site. He talks about it at ... | |
| number comparison returning false when should be true | 9/7/2011 |
| Q: Version: MS Excel 2003 SP3 Have written a VBA macro, that compares the values in two cells. Both ... A: Mike, I can't look at your sheet - so I don't know how your numbers are stored. But is sounds like ... | |
| Data Validation using Excel 2003 | 9/6/2011 |
| Q: Is it possible to set up a data validation check to allow only a list of text values or a numeric ... A: Jim, well, you can't type in your list directly in the data validation by choosing the list option. ... | |
| VBA Code | 9/6/2011 |
| Q: this is clarification to the question i previously asked, pasted below "I have excel file, in the ... A: Abdul Jaleel, sorry for the typo. Trying to get you an answer before I left for work. If you ... | |
| VBA Code | 9/6/2011 |
| Q: this is clarification to the question i previously asked, pasted below "I have excel file, in the ... A: Abdul Jaleel, right click on the sheet tab of Sheet master and select view code then paste in code ... | |
| Last Data Entry in Row | 9/5/2011 |
| Q: Hoping your can help. I have built a program to keep the golf handicap for our group and everything ... A: Glen, You can do what you ask. There are several ways and not everyone agrees on the best. I ... | |
| Excel - Transparent "Sheet" to achieve auto form fill | 9/3/2011 |
| Q: I'm using Excel 2003. In a workbook there are many sheets. The basic question is : is it possible ... A: Pete, -------------- thanks for the feedback. However, your approach is so looney that I would ... | |
| Need to modify code to recognize fields with text data | 9/2/2011 |
| Q: I am trying to identify the field names for items in a reconciliation, but what I have is not ... A: Pam, I don't know of a formula that will do what you want. I assume you were getting the number of ... | |
| Copy XLS Filenames, Sheetnames, and Data in Each Cell to a Single XLS file | 9/2/2011 |
| Q: I would appreciate your help with the following: I need to write Macro code in Excel to copy data ... A: Uche, >it has the following data in cells B3:B7 with blue color fill which format I would like to ... | |
| Getting Data into Userform ListView | 9/2/2011 |
| Q: Been playing with recorded macros followed by slight modifications for some time. I now need to try ... A: Paparazzi, You use the term listview and the term listbox. I dont' see anything you are doing that ... | |
| Unique count based on criteria returning incorrect count | 9/2/2011 |
| Q: I am using Excel 2007 I have the following formula that I was hoping would return the number of ... A: Pam, Without knowing much about what you were doing, I would suggest using a pivot table perhaps as ... | |
| IF Problem | 9/2/2011 |
| Q: (Please see attached image) What I need to do is search for the key words from C5:C11 within the ... A: Alison, this is set up to be entered in B2, then drag fill down to B3. It worked for me. ... | |
| VBA to move data, clear contents of cells, and place cursor in first cell for next entry | 9/1/2011 |
| Q: Tom, I have a few questions. First, I have the following code to take information from certain ... A: Joey Assume sheet Entry Form is the one you want to clear and selet a cell in. Public Sub ... | |
| Formula or VBA to list items pulled from multiple Excel sheets | 9/1/2011 |
| Q: It's me again. I am using MS Excel 2007. I am trying to pull varied data from multiple sheets to ... A: Tom, same limitation as yesterday. Formulas can't read the strikethrough unless you build a custom ... | |
| Unique count based on criteria returning incorrect count | 9/1/2011 |
| Q: I am using Excel 2007 I have the following formula that I was hoping would return the number of ... A: PAM, the &"" allows the function to work even if there are empty cells in the range Sumproduct is ... | |
| Comments Question | 8/31/2011 |
| Q: Excel 2007 Is it possible to move comments added to the worksheet to the main worksheet. A: Sherri, you can copy or cut the cell it is associated with and paste that in the master sheet. A ... | |
| Sumif number greater than zero and not strikethrough | 8/31/2011 |
| Q: I have a question. I am using Excel 2007. I want to create a formula that add numbers in a column ... A: Tom, just add that condition in your custom function Public Function ... | |
| Conditional Formatting | 8/31/2011 |
| Q: Running Excel:mac 2008. I have a list of numbers (50) named HiLow and a table of numbers e.g. 1-1500 ... A: Julian, You didn't ask me this question - I found it in the question pool First - I don't use a ... | |
| top 5 values including its name sorted out. | 8/31/2011 |
| Q: I want to find the top 5 value in a given range and will be sort out from highest to lowest in a ... A: Jeffrey, there are several complexities to what you want to do. First, you want to sort you ... | |
| top 5 values including its name sorted out. | 8/31/2011 |
| Q: I want to find the top 5 value in a given range and will be sort out from highest to lowest in a ... A: Jeffrey just copy the data to the new columns and sort it on the center column as the top key and ... | |
| Excel Formula Q | 8/30/2011 |
| Q: I need to write this formula in Excel 2003: IF A1>=B1, and A1<C1, then fill D1 with"P"; IF A1>=C1 ... A: Greg Tilkins, =if(or(B1="",C1=""),"N",if(And(A1>=B1,A1<C1),"P",if(A1>=C1,"E","N"))) >In this ... | |
| Copy XLS Filenames, Sheetnames, and Data in Each Cell to a Single XLS file | 8/30/2011 |
| Q: I would appreciate your help with the following: I need to write Macro code in Excel to copy data ... A: Uche, put this code in the workbook (in a general module such as Module1) where you want the ... | |
| Excel - vlookup | 8/30/2011 |
| Q: Here is an example of what I'm trying to compare and formula I'm using -vlookup(a1,data,1,fase): ... A: Joann, this formula worked for me =VLOOKUP("*#"&A2&"*",$F$6:$G$10,2,FALSE) A2: 508 and the table ... | |
| Status update as macro run | 8/30/2011 |
| Q: I am working in excel in which i have to run 10 macros.I want to put a user form that increase from ... A: Neha, it would be difficult to give a good explanation in this format. However, John Walkenbach ... | |
| Criterion Cell as Partial of Target Cell | 8/30/2011 |
| Q: I am using MS 2010. I have a spreasheet with multiple values in both a Country column (I) and a ... A: Kyle D. Green My email address is kyledg at gmail dot com. Looks like you are half way there. ... | |
| Excel Formula Q | 8/29/2011 |
| Q: I need to write this formula in Excel 2003: IF A1>=B1, and A1<C1, then fill D1 with"P"; IF A1>=C1 ... A: Greg Tilkens, the formula would need to be placed in D1 then it would be ... | |
| Date from Text to date format | 8/29/2011 |
| Q: I have data with date with "62511" for June 25,2011 and "122811" for Dec 18,2011. Could you please ... A: Manoj, assume your dates are in column A beginning in cell A1. Then in B1 put in the formula ... | |
| truncating at steps in macro | 8/26/2011 |
| Q: I wish to create a macro that will truncate the answers at each step of a calculation: Start with ... A: Pete, assume your Years are in C5:Q5 and your corresponding inflation rates are in C6:Q6 of the ... | |
| Using radio buttons to change images in a sheet (with VBA) | 8/26/2011 |
| Q: What I am trying to do is a simple macro formula to change images depending on radio button ... A: Fabricio, There is no image attached. But even it there was, it would not be helpful as I need to ... | |
| Reading multiple csv files | 8/26/2011 |
| Q: Tom I used you code as the reference in VBA about how to read multiple txt file. But I am quite ... A: Nikhil, ======================< revision to my answer >============== PS: I revised my answer - I ... | |
| Replacing space with dash | 8/26/2011 |
| Q: I have a list of part numbers (in column A) in Excel where there are spaces in between numbers. I ... A: Michael select column A. go to the Replace command and select it. Put a space in the first text ... | |
| VBA variable storage | 8/26/2011 |
| Q: Tom, I am trying to set up a reminder in one of my workbooks to alert the user when it is time to ... A: John Wolford, You could use a defined name or you could write it to a cell. In either case, the ... | |
| Reading multiple csv files | 8/26/2011 |
| Q: Tom I used you code as the reference in VBA about how to read multiple txt file. But I am quite ... A: Nikhil, I have made some changes to the code you show. Unfortunately I can't test the code to make ... | |
| Using radio buttons to change images in a sheet (with VBA) | 8/26/2011 |
| Q: What I am trying to do is a simple macro formula to change images depending on radio button ... A: Fabricio, >Of course, it is a VBA task and that is why I am looking once more for your kind help! ... | |
| Need a macro[VBA solution] to auto fill a cell based on drop-down selection | 8/26/2011 |
| Q: Tom am sorry if i couldn't make myself clear the techniques that Debra Dalgleish has documented in: ... A: Sourabh, well, I will have to make some assumptions. I will assume that any value in B1 will also ... | |
| concatenate text with cell values | 8/25/2011 |
| Q: I am running Excel 2007. I have a column of numbers. I need to concatenate that column with the word ... A: Gail, that isn't too precise a description but I will assume you have A1: 1 A2: 2 A3: 3 A4: 4 ... | |
| Excel Formulas | 8/25/2011 |
| Q: I hope you can help me. I use Excel 2007 and I have to create a spreadsheet that shows total hours, ... A: Carol, if you want to send me a sample file with some representative data, I can try to help you. ... | |
| Need Combine Data | 8/25/2011 |
| Q: I have 6 excel sheet containing data more than 20000 rows. All excel having same format. i.g. in ... A: Rocky/Vaibhav, > like pivot table so why not use a pivot table. See Debra Dalgleish's discussion ... | |
| Copy text in cells from one sheet to another | 8/24/2011 |
| Q: I simply wish to copy text from cells in one sheet to another sheet on the same workbook in Excel ... A: Bill, apparently you don't understand what I am suggesting or I wasn't clear in my description. If ... | |
| Copy text in cells from one sheet to another | 8/24/2011 |
| Q: I simply wish to copy text from cells in one sheet to another sheet on the same workbook in Excel ... A: Bill, Once the array formula is entered, it is fixed. if you wanted to do what you describe, you ... | |
| Excel Formula | 8/23/2011 |
| Q: I am creating a log that keeps track of my 20 employees hours worked, overtime, etc. I would like a ... A: Mike, if I understand the true need, you need to count the number of cells in the range D8:D42 that ... | |
| print macro with pdf | 8/23/2011 |
| Q: using your post from March 2010 (see sub saveas pdf below), i want to allow user to name the pdf ... A: Diane, Sub SaveAsPDF() Dim sh as worksheet, bk as workbook Dim sPath as String set sh = ... | |
| Formual using { } | 8/22/2011 |
| Q: How do I get this formula to "stick" in Excel? {=OR(A18=T17,U17:AA17=C18:I18)} I did it once but I ... A: Esther =OR(A18=T17,U17:AA17=C18:I18) type it in like the above, but instead of finishing off the ... | |
| Excel 2007 moving contents of cells | 8/22/2011 |
| Q: I'm using Microsoft Excel 2007. I have a list of names in which I'm trying to be able to move a ... A: Brian, It can be done with a macro. If you want to send me a sample file, I can write a macro to ... | |
| Excel VBA Message Box with checkboxes | 8/22/2011 |
| Q: Tom, I am looking to create a macro that upon clicking a command button, a message box would appear ... A: Henry, Unfortunately a message box only does what a message box does - it display a message with ... | |
| The Weekday function | 8/22/2011 |
| Q: if cell a1 is blank and in cell b1, I enter the formula: =weekday(a1) then click the Enter ... A: Chris, you didn't ask me this question - I found it in the question pool. If I put 40545 in a ... | |
| Excel Formula | 8/21/2011 |
| Q: I am creating a log that keeps track of my 20 employees hours worked, overtime, etc. I would like a ... A: Mike, You didn't ask me this question, but I found it in the question pool. If a single cell will ... | |
| Comparig the Cell values | 8/21/2011 |
| Q: I am using one formula in Excel 2007 to compare once cell values with rest of cell like..... " ... A: M. Sarwar, I tested the formula for every condition it is structured to handle ... | |
| Comparig the Cell values | 8/21/2011 |
| Q: I am using one formula in Excel 2007 to compare once cell values with rest of cell like..... " ... A: M. Sarwar, regardless of what you say, you formula would not work for what you say you want to do. ... | |
| sum from 5 to 15 (vba) | 8/20/2011 |
| Q: I want the macro that can perform the following task. k = 5 + 6 + 7 + ... + 14 + 15 the value of ... A: clarkson, you didn't ask me this question. I found it in the question pool if you want a macro ... | |
| print macro with pdf | 8/19/2011 |
| Q: using your post from March 2010 (see sub saveas pdf below), i want to allow user to name the pdf ... A: Diane, sorry for the late response. I was under the weather yesterday. you can use ... | |
| Formula- poss match/vlookup/lookup? | 8/18/2011 |
| Q: I have data that looks like this (except way longer): Worksheet 1 A B Dr. X Family Practice ... A: Rachelle, I can show you how to do it, but it is mildly complex (easier to give an example). If ... | |
| Excel VBA Find Function | 8/18/2011 |
| Q: Tom! I have a macro that searches all pages of a workbook for the word "LATER" using the Find ... A: Elizabeth, this worked for me. Check the parameters of the FIND method to make sure it is set up ... | |
| Copy info from one cell to another cell | 8/18/2011 |
| Q: On one of my spread sheet I have a column with birthdays in the standard excel format. How can I ... A: Richard, >standard excel format so for illustration, I will assume that cell A1 contains a date ... | |
| addition with a look up and a criteria | 8/18/2011 |
| Q: I would like to know how to calculate the TOTAL PVR for a fund but only include the PVR amount in ... A: Anthony in general: =sumifs(CrntPVR,PCC,"No",Fund,942) assume PCC is in column B and crntPVR is in ... | |
| Close workbook or make read only the username is administrator | 8/17/2011 |
| Q: Dear Expert, I made one Excel sheet for some data entry for certain users, if this data is being ... A: Sarwar, If the user doesn't enable macros then any macro related solution you design either for ... | |
| Close workbook or make read only the username is administrator | 8/17/2011 |
| Q: Dear Expert, I made one Excel sheet for some data entry for certain users, if this data is being ... A: Sarwar, I would try using code like the added lines below. Private Sub ... | |
| Names | 8/16/2011 |
| Q: I have a list of Names that look like this: A1: ABNER KAYLA NICOLE A4: ADAMS ANDREA CARLA MARIA ... A: Matt, I have attached an image to show that it works fine for me. I can reproduce your problem if ... | |
| Rearrange List Between Sheets | 8/15/2011 |
| Q: Mr. Ogilvy; I need help arranging a list into a different format between two sheets. Sheet ONE ... A: Christian, Assume your data starts in A1 of the source sheet and the first row is in A1:C1 in A1 ... | |
| Names | 8/15/2011 |
| Q: I have a list of Names that look like this: A1: ABNER KAYLA NICOLE A4: ADAMS ANDREA CARLA MARIA ... A: Matt, this worked for me with your test data: =PROPER(LEFT(A1,IF(ISERROR(FIND(" ... | |
| Sum values by month | 8/15/2011 |
| Q: I am trying to analyse a large sheet of data concerning invoices. In Column E, I have dates in ... A: James, two ways (examples are for values in March of 2011) ... | |
| Day notation | 8/15/2011 |
| Q: Version of Excel: "Excel 2003" I am looking for a formula which can express a day in a different ... A: Bas, the Month function only takes one argument. so it should be ... | |
| Day notation | 8/15/2011 |
| Q: Version of Excel: "Excel 2003" I am looking for a formula which can express a day in a different ... A: Bas, You could do it like this: A2: ... | |
| multiple formula in one cell | 8/12/2011 |
| Q: Is it possible to have more than one formula in a cell to calculate dependant on a value in another ... A: Mike, >there will only ever be one number in C20 to I20 so if I did ... | |
| multiple formula in one cell | 8/12/2011 |
| Q: Is it possible to have more than one formula in a cell to calculate dependant on a value in another ... A: Mike, each of cells C22 E22 G22 I22 would need their own formula Now I see you think you have ... | |
| 2007 Application.FileSearch | 8/12/2011 |
| Q: Long time no "see"! How are You? I just would like to confirm a little thing related to FileSearch ... A: attis, You really can't use anything specific to the filesearch object. for example ... | |
| Excel data sorting/filtering | 8/11/2011 |
| Q: and thanks for taking the question. One of the columns contains multiple words in each cell, and I ... A: Farhad, I assume you are using the autofilter on your data. In the dropdown for that cell, you ... | |
| Time sheet spreadsheet | 8/11/2011 |
| Q: I am creating a spreadsheet to track my time worked each day -- I can get it to calculate time ... A: Jennifer, You didn't ask me this question. I found it the question pool. send me a small sample ... | |
| Count criteria | 8/11/2011 |
| Q: I have a template in excel that has a summary and detail tab. On the summary tab, I need to have a ... A: Jessica, If you are using Excel 2007 or later, you can use the new countifs formula which allows ... | |
| Pop up window alert in excel | 8/11/2011 |
| Q: Hope you can help me to resolve my query regarding the pop up message/window alert. I got some codes ... A: Karthick, the code If Len(Trim(s)) > 4 Then MsgBox s Else MsgBox "No PO Creation due" End If ... | |
| Pop up window alert in excel | 8/11/2011 |
| Q: Hope you can help me to resolve my query regarding the pop up message/window alert. I got some codes ... A: Karthick, you picture doesn't show where your cells are, so I can't say anything about whether the ... | |
| VBA Duplicate Removal and Merging | 8/10/2011 |
| Q: I have an interesting question I hope you can help with. I’m not up to speed on Excel VBA and I ... A: James, --------<Revision>------------ I just realized I copied the wrong formula into the ... | |
| Excel Image | 8/10/2011 |
| Q: I am using a drop down menu located on Sheet1 that uses data stored on Sheet2 with the goal of ... A: Brian, Me refers to the sheet that contains the code so you would need to have Me changed to refer ... | |
| Excel splitting names in one column | 8/10/2011 |
| Q: I have a list of 100 names fist and last with no commas. I have tried using the icon Data then text ... A: You didn't ask me this question. I found it in the question pool. Your question is little bit ... | |
| Excel data selection | 8/10/2011 |
| Q: I have a running time value in seconds in Col. A, and a corresponding temperature reading in col B. ... A: David, remember I stated: >assuming the time values in column D each match one time value in ... | |
| Excel data selection | 8/9/2011 |
| Q: I have a running time value in seconds in Col. A, and a corresponding temperature reading in col B. ... A: David, if the time value is unique, you can use the vlookup function Assume D1 has the extracted ... | |
| Excel VBA conditional copy and paste | 8/8/2011 |
| Q: Tom. Thanks for all your help in the past. Right now, I am trying to execute a macro that checks ... A: Elizabeth, this does a case insensitive check if the substring "prl" exists in the cell: If ... | |
| Re-cutting rotas | 8/8/2011 |
| Q: Use of macros on re-cutting my rotas is fine. The assistants understand macros so if you have a ... A: Tanya Warnakulasuriya, this macro worked as I expected when processing your sample data. It adds a ... | |
| Identifying duplicate number combinations | 8/8/2011 |
| Q: I have a spreadsheet which identifies a point in field IN_ID and its nearest neighbour in field ... A: Katherine, so you are looking for any instance where the two locations reappear in a previous row ... | |
| Commissions Calculation | 8/8/2011 |
| Q: I want to calculate incentives for my Sales Team like below based on how soon they collect their ... A: Dilshad, if C2 has the 65days entry (as an examle) and D2 has the money collected, then ... | |
| adding and subtracting from the same total | 8/7/2011 |
| Q: I kind of figured out what I need but My new question is I need a function like "sumifs" that you ... A: Sheila, =sumif($F$5:$F$1000,"<>Sheila",$C$5:$C$1000) that sums everyting in column C where column ... | |
| Excel vba to ensure font size must be 12. | 8/6/2011 |
| Q: Could you please write me the macro that can perform the following task? task: all words (texts, ... A: clarkson, --------< revision to answer>--------- I am not sure why you would need to change the ... | |
| formula in excel 2007 | 8/5/2011 |
| Q: I learned from one of your answers to question how to add cells from different worksheets and put ... A: Tiffany, You don't show an example of the formula you are using to do the sum. I will assume it is ... | |
| How to Format a Column in Order of Date | 8/4/2011 |
| Q: So i am creating a spreadsheet that will keep track of the last time that a client has been ... A: Nichole, you didn't ask me this question - I found it in the question pool. (one reason why the ... | |
| embedded if statement in excel | 8/4/2011 |
| Q: I am trying to unsuccessfully write the following: If cell E34 is <1, then enter 1, if cell e34 is ... A: Margie, You didn't ask me this question - I found it in the question pool (that is why it may not ... | |
| Filter Row Contains Merge Cell | 8/4/2011 |
| Q: By refer to attachment, I want to write a macro to Filter Row 2, is it possible? Because I try ... A: Xeon, this worked for me: Assuming you want to start filtering in row 2 and your headers are mixed ... | |
| IF Function problem... | 8/4/2011 |
| Q: I'm trying to use the IF function to deterime if a time frame is <30mins, between 30-60mins and ... A: Erin, "00:30:00" is just a string - it isn't a time value. I assuem J2 holds a time value. you ... | |
| Importing Multiple .txt Files (contain numbers and words that are comma separated) | 8/3/2011 |
| Q: I have several text files (which contain words and numbers separated by a comma) in one directory ... A: Utkash, this worked for me using 3 files made from your sample data. It writes the data for the ... | |
| Max if | 8/3/2011 |
| Q: I am trying to find the last entry in the list of same date entries in a month. For Eg: 08/07/2011 ... A: Ben, the formula I just sent you should work for what you describe if the dates are sorted and you ... | |
| Max if | 8/3/2011 |
| Q: I am trying to find the last entry in the list of same date entries in a month. For Eg: 08/07/2011 ... A: Ben, because of the ranges I chose to illustrate, perhaps the formula was not clear. Since I am ... | |
| Max if | 8/3/2011 |
| Q: I am trying to find the last entry in the list of same date entries in a month. For Eg: 08/07/2011 ... A: Ben, assume you dates are in A1:A500 and your values are in B1:B500. then you could use a formula ... | |
| delete a inserted picture by micro | 8/3/2011 |
| Q: In morning, I asked you about insering signature (in form of pic) by using password protected ... A: Jonathan, you can protect the project. IN the visual basic editor, with your workbook as the ... | |
| Sorting data with a macro | 8/2/2011 |
| Q: Tom, I have data in 3 columns (C:E) and Rows 1 and 2 are header rows, with the actual data I want ... A: Shawn, try something like this: Dim r as Range set r = range("D3", ... | |
| Excel | 8/2/2011 |
| Q: i use excel for enter many number and when i enter a number that is 20 digit the excel automatically ... A: amir, you didn't ask me this question - I found it in the question pool (thus it is rather old, but ... | |
| Comparing several column values in a row | 8/2/2011 |
| Q: Mr. Ogilvy, I am seeking a VBA solution. I have an excel spreadsheet where the rows and columns ... A: Mr. Ogilvy, The sub Main() works fine but my issue is more with the VBA code logic. The function ... | |
| delete a inserted picture by micro | 8/2/2011 |
| Q: In morning, I asked you about insering signature (in form of pic) by using password protected ... A: Jonathan, you can give the picture a name when you enter it. Then you will know what to delete. ... | |
| ListBox Rowsource | 8/1/2011 |
| Q: I am trying to display some data from excel into a ListBox and It is working only that in the ... A: Andres, I guess I am not following your question. 1) B2 and C2 are in the rowsource range so I am ... | |
| ListBox Rowsource | 8/1/2011 |
| Q: I am trying to display some data from excel into a ListBox and It is working only that in the ... A: Andres Cevallos, can I assume that populated cells are contiguous and once I hit an empty cell in ... | |
| Find & Replace | 8/1/2011 |
| Q: I found this code in the internet, Sub findrep() Dim target, cell As Range Dim i, k As ... A: Jun, no, I guess I didn't understand that as the desired result. Here is a correction to the code ... | |
| Go to Today's date | 8/1/2011 |
| Q: Re Excel 2010 - used your reply to Jim for "going to today's date", as follows, with my range and ... A: Niggo, you added the select to the goto statement. The select is invalid. the goto statement ... | |
| Find & Replace | 8/1/2011 |
| Q: I found this code in the internet, Sub findrep() Dim target, cell As Range Dim i, k As ... A: Jun, you seem to misunderstand variable declarations in VBA. Each variable must be typed ... | |
| question regarding serial number drag cursor | 7/30/2011 |
| Q: i have a problem with my excel sheet. i am not able to drag down the serial number list because i am ... A: vidya sharma, the ability to drag is an option setting. You didn't say what version of excel so ... | |
| Macro to Delete all rows that do not include "" | 7/29/2011 |
| Q: I am trying to construct a macro to delete all of the rows that do not contain a certain text which ... A: Steven, if the cells are actually empty, you can do this Sub DeleteRows() Dim SrchRng As Range ... | |
| Macro | 7/29/2011 |
| Q: I have a computed value in a cell called "linecount". The cursor is in cell A:6. I need a macro to ... A: Wayne, I assume your A:6 notation means cell A6 Range("A6:V" & Range("Linecount").Value + ... | |
| Nested IF statement | 7/29/2011 |
| Q: Each of the following formulas work individually ... A: Michael, you didn't ask me this questions. I found it in the question pool. The missing ... | |
| E-mail problem | 7/27/2011 |
| Q: I'd like to ask you a question about excel 2007. I'm trying to attach pictures in excel worksheet, ... A: Mikko, have you tried doing it manually. Does it work when you do it manually. If so, you can try ... | |
| Insert picture in excel 2007 worksheet and send it thru e-mail | 7/26/2011 |
| Q: I got few questions, and I've been searching for hours an answer but haven't found nothing. My ... A: Peter, Do you mean when you bring the images into your worksheet, add some instructions to your ... | |
| Insert picture in excel 2007 worksheet and send it thru e-mail | 7/26/2011 |
| Q: I got few questions, and I've been searching for hours an answer but haven't found nothing. My ... A: Peter, your code positions the picture at pictcell, but I don't see any code in your routine that ... | |
| Macro | 7/26/2011 |
| Q: I had two requests Request 1 I want to insert the following If condition using VBA code, but the ... A: Sudhir, -- added information --- you can delete this line from the code: ... | |
| on add picture function | 7/25/2011 |
| Q: its community members like you who really make a difference ~!~ i was able to use your code to ... A: Joe, sorry for the delay. I wanted to be able to test the code before I sent it since you seem to ... | |
| on add picture function | 7/25/2011 |
| Q: its community members like you who really make a difference ~!~ i was able to use your code to ... A: Joe, >if it is physically possible in excel - i would like to be able to re-size the height/width ... | |
| Need vba help | 7/23/2011 |
| Q: Pl. Answer in VBA EXCEL Form.I am working in Excel 2007 I have data as below In a Excel sheet No1 ... A: Avinash, This worked for me with you test data copied to several rows starting in row 1. Sub ... | |
| Retrieving sheets name | 7/23/2011 |
| Q: Hope you are well!. There is a case I recieve an excel file every week which hold 100 above sheets ... A: Mohammad Ali, Sub GetData() Dim sh as worksheet, sh1 as worksheet Dim cnt as long, r as range ... | |
| insert picture to cell from source is in adject cell | 7/21/2011 |
| Q: i am using excel 2007/windows 7. searched the internet for hours and hours for this... maybe you ... A: Joe, sub GetPictures() Dim r as Range, cell as Range, pic as variant Dim r1 as Range set r = ... | |
| insert picture to cell from source is in adject cell | 7/21/2011 |
| Q: i am using excel 2007/windows 7. searched the internet for hours and hours for this... maybe you ... A: Joe, there is nothing wrong with that line of code. I tested it with a picture out on the internet ... | |
| insert picture to cell from source is in adject cell | 7/21/2011 |
| Q: i am using excel 2007/windows 7. searched the internet for hours and hours for this... maybe you ... A: Joe, Here is some basic vba code you can adapt to your situation. sub GetPictures() Dim r as ... | |
| VBA Code to Restrict Entry | 7/21/2011 |
| Q: Trust you are fine. Need help to modify the following code: Private Sub Worksheet_Change(ByVal ... A: Yogi, Here is my second guess. You said nothing about column E, so that has been removed Private ... | |
| VBA timer | 7/21/2011 |
| Q: How are you? I'm currently doing a project and I'll be needing a vba code for a timer. We have 2 ... A: irish, take a look at Chip Pearson's page on this topic. I would guess that what is written there ... | |
| vlookup multiple values | 7/20/2011 |
| Q: I have been trying to find ways to use vlookup formulas to output an entire row that starts with the ... A: Ammon, Ashish Mathur has written up this article on how to do this: ... | |
| Pivot Macro | 7/20/2011 |
| Q: I have a macro wherein it selects all the 3 months in field "Per" for 10pivots, but i dont want it ... A: Dev, cnt = 0 For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables cnt = cnt + 1 ... | |
| VBA Code | 7/19/2011 |
| Q: Hope you are fine. I have below VBA code working Fine to filter the data and send Automatic Email ... A: Abdul Jaleel, Hopefully this altered code will do what you want. Sub ... | |
| copy paste from worksheet to many workbooks | 7/19/2011 |
| Q: Please go through the below given scenario. I have 27 Excel files in a folder. All files have got ... A: Kevin, Although I have never run into a problem with the DIR loop in that code, since the code is ... | |
| CountA Range | 7/19/2011 |
| Q: Tom, Can you show me a way to make the following work with a variable length range. Some of the ... A: Alan, this would be the approach I would use since you haven't listed any constraint on data below ... | |
| VBA Code | 7/19/2011 |
| Q: Hope you are fine. I have below VBA code working Fine to filter the data and send Automatic Email ... A: Abdul Jaleel, you explanation doesn't make sense to me. You build a separate file by copying all ... | |
| Pulling data from cells on one spreadsheet to another | 7/19/2011 |
| Q: For excel 2007: Do you know of anyone who can help me? Or do you know how I pull data from one ... A: Candace, if the data in column E are numbers and the values in columns B, C and D combine to ... | |
| Dependent Data Validation | 7/18/2011 |
| Q: Tom, I'm working with the data validation function in excel 2007 and I was wondering if there is ... A: John, sorry, but I don't see anything in you code that makes any sense. The dropdown itself does ... | |
| Question | 7/18/2011 |
| Q: I import a long list of date. I have a column similar to this format rtu hed 4tb u75 243 4e3 ... A: Ammon, Assuming the table where you do the look up has both text and numbers, and anything that ... | |
| VBA Code | 7/18/2011 |
| Q: Hope you are fine. I have below VBA code working Fine to filter the data and send Automatic Email ... A: Abdul Jaleel .Attachments.Add NewWB.FullName appears to be the ... | |
| copy paste from worksheet to many workbooks | 7/18/2011 |
| Q: Please go through the below given scenario. I have 27 Excel files in a folder. All files have got ... A: Kevin, Your code would look something like this - adjust the code to reflect the workbook name with ... | |
| DDE in Excel | 7/16/2011 |
| Q: Ogilvy, I am trying to write a VBA module that takes live data from a financial program called ... A: Patrick, I do have such an account. So if you want to send a sample workbook I can take a look at ... | |
| Excel 2007 - report macro filtering on dates | 7/15/2011 |
| Q: I hope you can help. I run a report out of a system which I then need to filter by using a macro to ... A: Sue, in this command r.AutoFilter Field:=1, _ Criteria1:=">=" & dStartDate, _ Operator:=xlAnd, ... | |
| Excel 2007 - report macro filtering on dates | 7/14/2011 |
| Q: I hope you can help. I run a report out of a system which I then need to filter by using a macro to ... A: Sue, You are in the United Kingdom and I am in the US. The problem with doing this is that VBA is ... | |
| Text formula | 7/14/2011 |
| Q: Tom, I am writing some code and I have included the following in it - Selection.FormulaR1C1 = ... A: Steve Davis, Revision: Sorry - My focus was on the flawed formula you provided. I know how to use ... | |
| Pulling data from other workbooks | 7/13/2011 |
| Q: I have a master workbook which needs to pull in data from three other workbooks. I could not figure ... A: Katie, the message you show is not refering to the length of the link/formula. It is telling you ... | |
| Pulling data from other workbooks | 7/13/2011 |
| Q: I have a master workbook which needs to pull in data from three other workbooks. I could not figure ... A: Katie, there is no difference in the ability of Excel 2003 and Excel 2007 to have a formula that ... | |
| cell selection | 7/13/2011 |
| Q: In cells B5,D5,F5,H5,J5,L5 there are drop down lists. in cell C7 ... A: Mike, >If the drop down lists are not used the sumproduct cell will i suppose produce an error as ... | |
| Excel pick last 10 numbers out of row | 7/13/2011 |
| Q: I want an Excel formula that selects the last 10 numbers out of a row of 10 to 50 numbers. It will ... A: Assume the values are in column A to AX. Formula written for row 2 ... | |
| excel problem | 7/12/2011 |
| Q: I have 5 cells B10, D10, F10, H10, J10. These cells may have values imported from another sheet if ... A: Mike, =IF(AND(B5<>"",D5<>""),SUMPRODUCT(--(M!G11:G1002=B5),--(M!I11:I1002=D5),M!Q11:Q1002),0) ... | |
| text to numbers calculation | 7/12/2011 |
| Q: Tom: Seems I cant quite do this. I appreciate your help and patience in the past. i have text in ... A: Pete, Sub CountText() Dim cell As Range, v As Long, tot As Long tot = 0 For Each cell In Selection ... | |
| excel problem | 7/12/2011 |
| Q: I have 5 cells B10, D10, F10, H10, J10. These cells may have values imported from another sheet if ... A: Mike, Your formulas will always produce a value. Zero is a value. You originally said you wanted ... | |
| Desire to streamline recorded Macros | 7/12/2011 |
| Q: Tom, I hope my question is not too difficult since I my question is about having a better grasp of ... A: Pam, The way I would do it is to make the immediate window visible in the VBE and then I would do ... | |
| text to numbers calculation | 7/12/2011 |
| Q: Tom: Seems I cant quite do this. I appreciate your help and patience in the past. i have text in ... A: Pete, you either use a case statement or use an Array for a case statement assume you are looping ... | |
| Range Macro | 7/12/2011 |
| Q: I need another variation to the macro that you previously provided. (it works great!!!) I have ... A: Wayne, Here is a guess at what you want I assume the cell named count means you have a defined ... | |
| numbers into text | 7/12/2011 |
| Q: sir i want to know how to convert numbers into text like as at the end of invoices and quotation we ... A: imran, vba is pretty much the only solution. If you can't get your vba to work and it is written ... | |
| excel function prb | 7/12/2011 |
| Q: i m stucked in a problem here is the detail i have some names in cell A1 with data validations like ... A: imran, assume in M1 to M6 you have this imran Micheal sameer shahrukh john Fernandez and next to ... | |
| Counting hours | 7/12/2011 |
| Q: Tom, I want to calculate the number of hours between two times. It's a snap to do, of course, ... A: Brin, time is stored as a decimal value between 0 and 1 representing the fraction of a full day. 8 ... | |
| Excel vb to detect first and last entry in sequence | 7/11/2011 |
| Q: I believe I need some VB code to help with this problem. In column B (code), I have text values ... A: Andy, see below ChangeValueinColumnBandSaveValue() Dim r As Range, lastrow As Long, i As Long ... | |
| Excel vb to detect first and last entry in sequence | 7/11/2011 |
| Q: I believe I need some VB code to help with this problem. In column B (code), I have text values ... A: Andy, Your picture is too small for me to see any details. Best I can make out is you have 11 ... | |
| Excel vb to detect first and last entry in sequence | 7/10/2011 |
| Q: I believe I need some VB code to help with this problem. In column B (code), I have text values ... A: Andy, sounds like you want the row deleted every time there is a change in the value in column b ... | |
| Adding and multiplying in one formula | 7/8/2011 |
| Q: I have columns of numbers representing course registrations per month. A year ago we changed the ... A: Carol, =sumproduct(a3:af3*100)+sumproduct(ag3:av3*200) 100 and 200 can be replaced by cell ... | |
| Using Excel VBA to change workbook name automatically | 7/8/2011 |
| Q: Good day Tom, I am an user of Excel 2007. I am trying to rename a workbook (let's call it "A.xlsm") ... A: Eugene, Here is my guess at what you are trying. You are using the workbook_open event of ... | |
| Write an Excel macro | 7/8/2011 |
| Q: I'm sorry for my unclear question. Now I try to describe again. I want to write an Excel macro in ... A: Quang, I will assume that each cell will contain one of the 16 directions and that is all the cell ... | |
| matching columns from a drop down list | 7/7/2011 |
| Q: I have 2 drop down lists of names on one sheet (sheet1) cells C5 and F5, and these are destinations ... A: Mike, in E7 of Sheet1 ... | |
| Emailing workbook w/hyperlinks | 7/7/2011 |
| Q: I have created a workbook (in 2003) that includes cells with hyperlinks to pages I have saved off ... A: Adam, the drive letter is a mapping set up on each individual computer. In most companies, this is ... | |
| Macro for Weekly Dated Worksheets | 7/6/2011 |
| Q: Tom, I have a spreadsheet for a weekly and I would like to create a macro that will automatically ... A: Tim, I can take a look at it, but your description so far has not been very illuminating. You need ... | |
| VBA Clearing Duplicate Entries | 7/6/2011 |
| Q: I have 3 columns of data containing contact details Column A Column B Column C Row ... A: Fay, this worked for me with your test data and a few other data configurations I added. Test it ... | |
| Emailing workbook w/hyperlinks | 7/6/2011 |
| Q: I have created a workbook (in 2003) that includes cells with hyperlinks to pages I have saved off ... A: Adam, My understanding is that hyperlinks are stored relative to the location of the file that has ... | |
| Finding a list of values | 7/6/2011 |
| Q: I need to sieve out the names of the people belong to a department in another sheet. Say in Sheet ... A: Justin, You didn't ask me this question, but I found it in the question pool. Perhaps this will ... | |
| Copying Formula in Excel | 7/6/2011 |
| Q: I'm using Excel as part of Office for Mac 2011 and have a question about copying a formula. I have a ... A: Mike, First let me say that I don't have a MAC, but I will tell you what I would do in the Windows ... | |
| Excel VBA - Vlookup for Array? | 7/5/2011 |
| Q: Version : Excel 2007 Hello, I will get right to it. Column A - List of 8 different states. ... A: Cris, here is a way to accumulate the values in column D at the same time you are populating the ... | |
| Working with Data filters | 7/4/2011 |
| Q: Tom, I use data filters for many things. I may be filtering on one or more columns. What I would ... A: Gary, 1) ans = MsgBox("Yes to Continue, No to Print?", vbYesNoCancel) If ans = vbno Then ' CALL ... | |
| Delete Duplicates Using 2 criteria | 7/4/2011 |
| Q: I found your VBA solution for the above, and have tried to modified the codes to suit my data. My ... A: Charles, It was unexpected that you would run the first routine on data that did not require any ... | |
| Delete Duplicates Using 2 criteria | 7/4/2011 |
| Q: I found your VBA solution for the above, and have tried to modified the codes to suit my data. My ... A: Charles, Sub RetainLatest() Dim rng As Range, rng1 As Range Dim s As String, bKeep As Boolean Dim ... | |
| Delete Duplicates Using 2 criteria | 7/2/2011 |
| Q: I found your VBA solution for the above, and have tried to modified the codes to suit my data. My ... A: Charles, Once you insert the date values in column L, the macro will run the same way it would run ... | |
| converting a 2007 VBA script into a 2003 compatible | 7/1/2011 |
| Q: Hey Tom, Long time no see. Thank you for the help on my last VBA script, it worked wonders and ... A: Ryan, No, that wasn't the only change. All these lines changed Added: ... | |
| If a cell is blank use another cell | 7/1/2011 |
| Q: I have excel 2003 and i'm trying to an if statement. I have the following fields EOT Required C7, ... A: Ricardo, =if(EOT Date is blank,if(Start Date<Due Date,"No","Yes"),if(Start Date<EOT ... | |
| converting a 2007 VBA script into a 2003 compatible | 6/30/2011 |
| Q: Hey Tom, Long time no see. Thank you for the help on my last VBA script, it worked wonders and ... A: Ryan, Microsoft did some major enhancements to conditional formatting in Excel 2007. So some of ... | |
| Working with Data filters | 6/30/2011 |
| Q: Tom, I use data filters for many things. I may be filtering on one or more columns. What I would ... A: Gary, Of course it can be done with a macro. However, there is no built in support for having a ... | |
| Graph changes size after refresh | 6/30/2011 |
| Q: Long time no speak, hope you are well. I'm not quite sure how to explain this so feel free to ask ... A: J, I haven't run across this problem. I did some research to see if there is a known bug - but ... | |
| Two BoundColumn - (VBA question) | 6/30/2011 |
| Q: I have a UserForm with a ComboBox1 which shows 4 columns (from sheet1, using the ColumnCount and ... A: Mauri, you can just access the information from the list property and write it in the click event. ... | |
| Advanced list management | 6/29/2011 |
| Q: Tom, I've tried array formulas, SUMPRODUCT, complex IFs, but can't seem to get quite the right spin ... A: Sandy, ... | |
| multiple macvros | 6/29/2011 |
| Q: I have a lot of information that i cut from a certain program to allocate information to a ... A: Billy, If I understood you requirement correctly, this worked for me. I understand you want to ... | |
| Advanced list management | 6/29/2011 |
| Q: Tom, I've tried array formulas, SUMPRODUCT, complex IFs, but can't seem to get quite the right spin ... A: Sandy, Month("June") won't return a month number If you actually have June in cell S21, then you ... | |
| update data | 6/29/2011 |
| Q: I would like to update the data in a excel cell continuously, i.e the data must be changed in the ... A: swathi say you want to increment the value of a cell Sub ABC() Dim i as Long i = 1 do while i < ... | |
| Solver Macro help. | 6/28/2011 |
| Q: I need help writing a Solver macro for Excel. Essentially what I want to do is make the AC4 (the ... A: Russ, for your sample, goal programming gave me I7= 17353.6794551978 The value in AC7 was 0. I ... | |
| Selective copying in Excel 2011 | 6/28/2011 |
| Q: I have an Excel 2011 sheet that has two columns: The B Column is a list of video files that we have ... A: Morgan Before A2: QRF9366TR TEMP queen mary los angeles harbor flight 004-C003R 3d [rf] ... | |
| Worksheet Formula | 6/28/2011 |
| Q: this seems like it would be pretty basic, and I have found similar questions on this forum, but none ... A: Nick, the only methods that will do that are to manually rename the sheets or run a macro to do it. ... | |
| Solver Macro help. | 6/28/2011 |
| Q: I need help writing a Solver macro for Excel. Essentially what I want to do is make the AC4 (the ... A: Russ, you didn't ask me this question - I found it in the question pool. I don't think you need ... | |
| Excel Formula | 6/27/2011 |
| Q: I need a formula for a summary table that takes all items in column i that equal or less than 7 to ... A: Christine Shores if you have excel 2007 or later =countifs(I:I,"<7",E:E,"Monday") note that the ... | |
| Dependent Variables Through Macros | 6/27/2011 |
| Q: Tim, I'm trying to write a tutorial for an excel program I just wrote. What I was hoping to have ... A: John, you would declare the variable at the top of ONE of your modules only and make it public ... | |
| Data Validation Formula | 6/26/2011 |
| Q: Good morning Tom, I’m chasing a rather difficult formula (for me) and I wonder if you could offer ... A: Graham, In Excel 2007, Microsoft added the Countifs and Sumifs formulas. These are similar to the ... | |
| Randomly distribute a list 6 times with no repeats... | 6/24/2011 |
| Q: I have a list of 90 attendees to a function that has 10 tables, 9 chairs per table. I need to ... A: Steve, think about what you have to do if you were to assign the rounds manually. Everytime you ... | |
| Book recommendations | 6/24/2011 |
| Q: I currently use Excel 2007. I have Excel 2007 VBA, however, I find it hard to find answers to my ... A: Pam, you can't go wrong with John Walkenbach's books - he has books for Excel VBA (power ... | |
| Randomly distribute a list 6 times with no repeats... | 6/24/2011 |
| Q: I have a list of 90 attendees to a function that has 10 tables, 9 chairs per table. I need to ... A: Steve, based on the limitations you have stated (no repeats for table assignment), you could ... | |
| Using the last row in a user defined function | 6/24/2011 |
| Q: I am trying to get a subtotal to populate in a header row along with some text, rather than the ... A: Mark, you didn't ask me this question - I found it in the question pool you could create a defined ... | |
| Calling protection from Workbook_Activate event | 6/24/2011 |
| Q: I have created the following procedure which sets or unsets protection for certain worksheets in my ... A: Mark SetProtection True don't use run. Private Sub Workbook_Activate() SetProtection True ... | |
| change event of a cell containing formula | 6/24/2011 |
| Q: I have to perform something on the value change of a cell which contain a formula...when i keep the ... A: shravya, everything you say is true. So there is no built in support for what you want to do. If ... | |
| VBA Loop question | 6/23/2011 |
| Q: I'm creating a fairly long loop formula in excel VBA, and I've run into a problem in a certain part ... A: as written, this is used as a demo. have your values in column B starting in B2 with some cells in ... | |
| Finding a cell with specific text in 2-d range of cells. | 6/23/2011 |
| Q: I'm very familiar with the vlookup and hlookup functions. In researching a solution for my problem, ... A: Tim match will work across multiple columns but only one row or it will work across multiple rows ... | |
| Excel | 6/23/2011 |
| Q: I have a list of names in Col-A, and another list in Col-B. I want Col-C to indicate, preferbly Yes ... A: Evan, in C1 =if(countif(A:A,b1)>0,"Yes","No") then drag fill that formula down the column (or copy ... | |
| 1 more question on Excel changing date in cell upon reaching a specific date | 6/22/2011 |
| Q: Sorry to bug you again. I'm applying this formula to my spreadsheet and, for the most part, it's ... A: Martha, the formula was designed to only work with one year time periods. You date is beyond one ... | |
| Transforming a letter grade to a numerical equivalent | 6/22/2011 |
| Q: I have been keeping grades in a letter ( A+, A, A-, etc.) format but the school is asking me to ... A: Fer, you can use a formula like this ... | |
| follow-up to "Need Excel to update dates upon reaching a specific date' | 6/22/2011 |
| Q: So, for every cell that needs to update yearly, I will need to enter the formula you created into ... A: Martha, If you type in my first formula, then you specify the day and month when the expiration ... | |
| SUMIFS QUESTION | 6/21/2011 |
| Q: I am running excel 2007, and the following formula does not work: ... A: Cal, My guess would be that GP_CATA_01 contains numbers (such as you show 3333) rather than a ... | |
| Need Excel to update expiration dates upon reaching a specific date | 6/21/2011 |
| Q: Working in Excel 2003. I have a spreadsheet to track the life of various contracts and ... A: Martha, you didn't ask me this question - I found it in the question pool. This is the general ... | |
| Changing Range Selection in a Macro | 6/20/2011 |
| Q: I would like to know if possible how to set up a macro that would highlight a range (ie. A4:AL70) ... A: Ted, you didn't ask me this question - I found it in the question pool. If your still looking for ... | |
| Excel VBA Macro - searching text in range | 6/20/2011 |
| Q: You gave me the following code for me to search a string (text) in a column (H). Is it possible to ... A: Irma, You can use the Inputbox to get the string you want to use/check for. Sub Process_Data() ... | |
| Finding Max of date in Excel for a Ticket Number | 6/20/2011 |
| Q: In Excel I have the fields in the following format : NODEID CLOSEDDATE ... A: Nazeeb, I put the first node ID in cell D4. You can list all the unique node IDs below that in D5 ... | |
| Search, Find, and Display on Seperate Page "VBA" | 6/20/2011 |
| Q: I'm trying to search a worsksheet for terms beginning with "bhas" and display those terms in another ... A: Elizabeth, you have to combine the Find with FindNext to find them all Here is some sample code: ... | |
| Finding Max of date in Excel for a Ticket Number | 6/20/2011 |
| Q: In Excel I have the fields in the following format : NODEID CLOSEDDATE ... A: Nazeeb, it sounds like you have data problems. Some or all of your data must be stored as strings ... | |
| excel formulas | 6/19/2011 |
| Q: Using Excel workbook to populate, from a "info page", a number of forms within the workbook or into ... A: Bill Lang, You didn't ask me this question - I found it in the Question Pool. If you still need ... | |
| Excel 2010 | 6/19/2011 |
| Q: Im trying to create a tab in an excel 2010 worksheet which will populate, row to row, from another ... A: Joelle, You didn't ask me this question; I found it in the question pool. first, it isn't totally ... | |
| Excel | 6/18/2011 |
| Q: Sir, I have started learning VBA in excel. I have created a simple function which works perfectly ... A: Talha Khan, I believe the number of modules would only be limited by memory. I suspect you have ... | |
| Excel VBA Macro - searching text in range | 6/17/2011 |
| Q: You gave me the following code for me to search a string (text) in a column (H). Is it possible to ... A: If the cell will contain a single word "apple" then Sub Process_Data() Dim lastrow As Long, i As ... | |
| Excel VBA Macro - SUMIF | 6/17/2011 |
| Q: I would like to create a VBA Macro Excel code (2003) for the following functions for the sample data ... A: Irma, I will assume your data is always sorted on the values in column A since that is what you ... | |
| Excel | 6/17/2011 |
| Q: Sir, I have started learning VBA in excel. I have created a simple function which works perfectly ... A: Talha Khan, first, your function should be in a general module. In the VBE go to the menu and do ... | |
| Backfilling Data | 6/16/2011 |
| Q: I am building a financial model and I know some VBA could save me a lot of time. I have a workbook ... A: Ty, you didn't ask me this question - I found it in the question pool. But I have to wonder why ... | |
| Kind of a Max IFS question | 6/16/2011 |
| Q: I would like to list on Sheet 10 the highest and smallest test scores for a subset of my large ... A: Carol, =MAX(IF((Sheet1!$M$2:$M$500="ABC Major")*(Sheet1!$P$2:$P$500="Yes"),Sheet1!$J$2:$J$500)) ... | |
| Finding Max of date in Excel for a Ticket Number | 6/16/2011 |
| Q: In Excel I have the fields in the following format : NODEID CLOSEDDATE ... A: Nazeeb, You can do this with an array formula. assume your nodeID is in cells A2:A8 and ... | |
| VBA Code | 6/16/2011 |
| Q: there was no more change for followup question, so i am here again. Code you provided you is working ... A: Abdul Jaleel, this is the line that copies the data into the new workbook r2d.Copy sh4.Range("A1") ... | |
| Combining data from multiple workbooks into one worksheet | 6/16/2011 |
| Q: I have been using your code for selecting multiple workbooks and copying a specific sheet from those ... A: Anthony, the problem lies in the fact that workbooks can have 1 million rows or 65535 rows. I have ... | |
| Loop through Userforms | 6/15/2011 |
| Q: Tom, Using Excel 2003 in Windows XP How can I: 1. loop through each Userform in a workbook (I ... A: Steve, the useforms collection is the collection of loaded userforms. So you need to load your ... | |
| Excel crashes when saving, after running large macro | 6/15/2011 |
| Q: Tom, I have been writing a large excel macro for several months now. In its newest form it opens a ... A: Mike, as a followup to my previous response to modify you main routine to put the sheets in the ... | |
| VBA Code | 6/15/2011 |
| Q: Good Day.You helped me a lot in the past,i request your help again. I have agreement number Sheet1 ... A: Abdul Jaleel, =============<revision>======= I found one more glitch in the code which I have fixed ... | |
| Rounding question | 6/15/2011 |
| Q: I am looking for a fomula that will only round up the last digit of a number to 9. Example 3.02 to ... A: Mary, You didn't ask me this question. I found it in the Question Pool but will attempt to help. ... | |
| VBA Code | 6/15/2011 |
| Q: Good Day.You helped me a lot in the past,i request your help again. I have agreement number Sheet1 ... A: Abdul Jaleel, Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet ... | |
| VBA Code | 6/15/2011 |
| Q: Good Day.You helped me a lot in the past,i request your help again. I have agreement number Sheet1 ... A: Abdul Jaleel, this is way beyond technical assistance which is offered on this site. I will give ... | |
| Calling a function in a Sub | 6/14/2011 |
| Q: I'm trying to write a function to search for a date and then provide the user with a couple of ... A: John Wolford, One problem is you have rDestination misspelled in the code in your function. ... | |
| Calling a function in a Sub | 6/14/2011 |
| Q: I'm trying to write a function to search for a date and then provide the user with a couple of ... A: John Wolford, You don't say what the actual problem is so this is my best guess at what you need to ... | |
| possible IF function questions | 6/14/2011 |
| Q: I have a 200+ line report in which 3 columns when there is a population (it wont always be populated ... A: Andrea, Your posting is not very clear as to what you want which is probably why it was put in the ... | |
| Simple Stock inventory counting 2 | 6/14/2011 |
| Q: It was helpful to try the code you sent to other enquirer few months ago. I'm just curious in how ... A: Wan, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, r1 As Range, res As ... | |
| VB- filtering and copying | 6/13/2011 |
| Q: I am using Excel 2003. I have a document with twelve worksheets in total from which I would like to ... A: Brenna, the code worked fine for me. It copied rows that just had PRL out in column AP as well as ... | |
| VB- filtering and copying | 6/13/2011 |
| Q: I am using Excel 2003. I have a document with twelve worksheets in total from which I would like to ... A: Brenna, macro assumes that copied values will always have data in column C - column C is used to ... | |
| VB- filtering and copying | 6/13/2011 |
| Q: I am using Excel 2003. I have a document with twelve worksheets in total from which I would like to ... A: Brenna, this worked for me as I expected based on your limited description. I will assume I am ... | |
| Excel-Formula | 6/13/2011 |
| Q: I am working in a excel database using excel 2007. I would like to know how a row can be ... A: Deep, You didn't ask me this question - I found it in the question pool. It sounds like you know ... | |
| VBA Code for hiding row with condition | 6/12/2011 |
| Q: Hope you are fine. I recorded a macro and i got the following Code which is working Fine, i need to ... A: Abdul Jaleel I assume bye no value, you mean the cell is blank. so the answer depends on whether ... | |
| Find word | 6/10/2011 |
| Q: I have been ripping my hair out trying to figure this one out on my own. I am trying to create a ... A: TJ, I replaced your sum formulas with subtotal formulas subtotal does not include hidden ... | |
| Permutations | 6/10/2011 |
| Q: I am using Office 2010 and I was wondering; how might I have excel find and highlight all ... A: Randy, the only change would be to change the formula for the defined name Permutations. It would ... | |
| excel 2007 | 6/10/2011 |
| Q: i want to ask if i can count the incidence (e.g. of a disease in a specific region) with excel 2007 ... A: Maria, You didn't ask this question of me - I found it in the question pool. but I would think it ... | |
| Find word "Total" (multiple of them) and do a few actions in VBA | 6/9/2011 |
| Q: I have been ripping my hair out trying to figure this one out on my own. I am trying to create a ... A: TJ not sure whether the blank row should be blank row 2 rows total row or blank row 3 rows total ... | |
| Modify from Record Macro | 6/8/2011 |
| Q: Using the Record Macro function, I've recorded a macro to sort the data in a worksheet. I've put the ... A: Derrick Wong, Yes Private Sub CommandButton1_Click() Dim rr As Range, sc As String Set rr = ... | |
| Modify from Record Macro | 6/8/2011 |
| Q: Using the Record Macro function, I've recorded a macro to sort the data in a worksheet. I've put the ... A: Derrick Wong, when you modified your ranksort procedure, you didn't include an argument list. so ... | |
| Auto Populate Data Validation List | 6/8/2011 |
| Q: I have a table where rows are filled with dates and columns are filled with "customer names". I ... A: Gigi, I could see doing this two ways. One way would be to construct the list of dates in another ... | |
| Modify from Record Macro | 6/8/2011 |
| Q: Using the Record Macro function, I've recorded a macro to sort the data in a worksheet. I've put the ... A: Derrik Wong, you didn't provide for the range you are going to sort in your subroutine declaration. ... | |
| Formula question | 6/8/2011 |
| Q: Is there a short way to express =(F1-F2)+(F2-F3)+(F3-F4)+(F4-F5)etc? A: Tom You are basically adding the first 4 cells and subtracting the last 4 cells in you example. So ... | |
| Permutations | 6/7/2011 |
| Q: I am using Office 2010 and I was wondering; how might I have excel find and highlight all ... A: RANDY, I will assume the master number you enter is in cell A1. You can adjust the formula below ... | |
| Tracking individual changes in mutipal cells | 6/6/2011 |
| Q: Tom, I have questions for you. So I have this code that looks at a worksheet and triggers other ... A: Shawn, Just list them sequentially with the proper If statement to test if the cell for that ... | |
| copy data from multiple workbook in a single sheet | 6/6/2011 |
| Q: i have ur code for copy data from multiple workbook in a single sheet. Sub ABC() Dim sPath As ... A: Jain Varun, This should only copy the header from the first sheet where data is copied. It assumes ... | |
| Macro Help - Find the cell with the highest value in a column | 6/6/2011 |
| Q: Using Excel 2010. I am trying to write a macro to go to the cell in column Q that has the highest ... A: Wayne, assume you have a named cell with the name that holds the number of rows to fill. Assume ... | |
| vba excel 2007 | 6/5/2011 |
| Q: In sheet 1 col A I have my working data and in col B is a master.If the cell containing data in col ... A: solman, this should do what you describe. I have assumed your data starts in row 1. Change the ... | |
| left and find function | 6/5/2011 |
| Q: i have following format data in column A Alliance Mgt. Ltd--137 AM Services Ltd.--205 i want to ... A: UPOrcut assume the first entry is A1, then in b1 put in the formula =Left(A1,find("-",A1)-1) then ... | |
| unhide multiple row by vba | 6/3/2011 |
| Q: I am using below mentioned macro for unhiding rows. Sub Unhide_Rows() Application.ScreenUpdating = ... A: Vimal, If they enter the data in the same format as you have which would be 1:1,5:5,10:10,36:36 ... | |
| Excel question | 6/3/2011 |
| Q: I use Excel 2007. If I have a word or series of words in one cell, say A1, and this word or series ... A: Tom, in c1: =(countif(A1,"*word*")>1)*24 will display 24 if the value in A1 constains the ... | |
| User Form to Worksheet | 6/2/2011 |
| Q: Tom, I have a similar problem as another question you have answered in the past with a small ... A: Harry, this would be my take on the answer to your question. Private Sub CmdRemovePS_Click() ... | |
| List from data base | 6/2/2011 |
| Q: please help me with this problem, I want to extract a list (matching a specific characteristic) ... A: Ines, Ashish Matur had documented how to do this in much more detail (and clearer) than I could ... | |
| Specific Column Reference | 6/2/2011 |
| Q: On my previous question you gave me this answer, Sub StartTime() With Cells (ActiveCell.Row, "F") ... A: Jun Sub StartTime() Dim r as range, res as variant set r = Range("A1",Cells(1, ... | |
| Specific Column | 6/2/2011 |
| Q: I have this codes, Sub StartTime() ActiveCell.FormulaR1C1 = "=NOW()" ActiveCell.Select ... A: Jun Sub StartTime() With cells(ActiveCell.row, "F") .Value = Now .NumberFormat = ... | |
| Macro runs really slow. Better process? | 6/1/2011 |
| Q: My code is taking a little under two mins to run. It is basically the same code ran for all 10 ... A: TJ, You seem to quite when one of the entries you search for has been found and then search for the ... | |
| Excel | 6/1/2011 |
| Q: Hey Tom, Thanks for the quick response on my previous question. Everything between MAC's and PC's ... A: Matt, If A1 contains total hours then =min(A1,40) will give the number of regular hours ... | |
| Macro code error | 6/1/2011 |
| Q: With regards to a macro I've created in an activeX button, it managed to run the first two ... A: Derrick Wong, MsgBox "List Generated", vbInformation, "Transfer Done" would become MsgBox ... | |
| Macro code error | 6/1/2011 |
| Q: With regards to a macro I've created in an activeX button, it managed to run the first two ... A: Derrik Wong, That would be a similar problem which I didn't notice when I was looking previously. ... | |
| Macro code error | 6/1/2011 |
| Q: With regards to a macro I've created in an activeX button, it managed to run the first two ... A: Derrick Wong, assuming you do have a sheet named "Alloc2NA" then I assume this line is working ... | |
| Specific column | 6/1/2011 |
| Q: I need help, I have a macro that format a cell, but I want that macro to run on a specific column ... A: Jun, if you only want the macro to run if the activecell is in column C (as an example) Private ... | |
| Need Help with Dates | 5/27/2011 |
| Q: Tom, Was wondering if you can point me in the right direction on a date issue? I have a worksheet ... A: Shawn, You just left out an End IF for the test if r is a date. This complied for me. Sub AAAA() ... | |
| Import selected txt on Macro | 5/27/2011 |
| Q: I saw you answered a similar question previously so I think you can help me. I need to import some ... A: Alvaro, assuming FICHERO contains the information you need, then it would be With ... | |
| Need Help with Dates | 5/26/2011 |
| Q: Tom, Was wondering if you can point me in the right direction on a date issue? I have a worksheet ... A: Shawn, I am not sure what your example table represents. It doesn't appear to represent the bug ... | |
| VBA: Find row containing text, copy and paste into new sheet or workbook | 5/26/2011 |
| Q: Good afternoon Tom, I have a huge report and I am trying to copy rows containing certain text. ... A: TJ, Sub ABC() Dim sh as worksheet Dim lastrow as Long, i as Long set sh = ... | |
| label (form control) to cell without vba | 5/26/2011 |
| Q: Is it possible to link label (form control) to cell without using vba? thanks A: Clarkson, ==============<start of revision>========= whoops - I misread your post. Disregard all ... | |
| VBA: Find row containing text, copy and paste into new sheet or workbook | 5/26/2011 |
| Q: Good afternoon Tom, I have a huge report and I am trying to copy rows containing certain text. ... A: TJ, originally I wrote that line to use column A to find the bottom of the existing data, but then ... | |
| Count unique triplicates in column | 5/25/2011 |
| Q: I use Office Excel 2003, SP3. Thank you in advance for your time/help. I found and used the below ... A: Dave, I imagine you could add whatever parameters you want in the conditions of the second if ... | |
| VBA: Find row containing text, copy and paste into new sheet or workbook | 5/25/2011 |
| Q: Good afternoon Tom, I have a huge report and I am trying to copy rows containing certain text. ... A: tj This does a lot of what you want. I will search for one or more words if you enter the "more ... | |
| 1904/1900 Date options automate in a macro? | 5/25/2011 |
| Q: I have two spreadsheets in MAC EXCEL. The date value 39217 exists in both spreadsheets. In one ... A: Gregg, also, I believe I still have some windows help files that explain those macro 4.0 commands. ... | |
| 1904/1900 Date options automate in a macro? | 5/25/2011 |
| Q: I have two spreadsheets in MAC EXCEL. The date value 39217 exists in both spreadsheets. In one ... A: Gregg, this is what I recorded in Microsoft 2007: ActiveWorkbook.Date1904 = True ... | |
| VBA | 5/24/2011 |
| Q: Excel 2003. English I have the code to prompt for a file and open said file (FileA). I have created ... A: Ian Fan Workbooks.Open (strInFile) strInfile = Activeworkbook.name prevously, strInFile would ... | |
| removing bulk amount of text | 5/24/2011 |
| Q: I am trying to find a way to remove a significant part of a paragraph from several columns. I tried ... A: Michael, I put your test string in cell A1 as you see in the picture I then put it in A3 as well, ... | |
| Insert chr(10) every after ever n number of characters in string | 5/23/2011 |
| Q: I've hit a dead end figuring something out, hoping you can help. I have an XLS file that contains a ... A: Kevin, basically yes - however, the way my macro works, it is doing everything character by ... | |
| Excel | 5/23/2011 |
| Q: firstly we have used your sum by font colour many times, thank you. I would like help on creating a ... A: Dan, Function AverageByBlackFont(InRange As Range) Dim Rng As Range Dim sumByBlackFont as ... | |
| Insert chr(10) every after ever n number of characters in string | 5/23/2011 |
| Q: I've hit a dead end figuring something out, hoping you can help. I have an XLS file that contains a ... A: Kevin, I don't know if this will be helpful or not. This routine is lightly tested and just ... | |
| Data Validation | 5/23/2011 |
| Q: I was wondering if you could kindly help me with a data validation problem in Excel 2007. I have a ... A: Joe, If you want to send me a sample file with your restrictins - what you don't want to do, I am ... | |
| Data Validation | 5/23/2011 |
| Q: I was wondering if you could kindly help me with a data validation problem in Excel 2007. I have a ... A: Joe, as you might guess, this is fairly complicated. Fortunately, Debra Dalgleish has documents ... | |
| concatenate two columns having date values | 5/23/2011 |
| Q: I was trying to concatenate two columns having date values to a third column but it often displays ... A: Navin, =concatentate(text(c11,"hh:mm"")," to ",text(d11,"hh:mm")) the bad results you were getting ... | |
| Hide and Unhide Combo Box | 5/22/2011 |
| Q: You have explained about hiding all the drop down combo box using a macro. Now I'm having 3 combo ... A: Jay, well, again, you haven't told me what type of combobox or what version of excel for activeX ... | |
| Hide and Unhide Combo Box | 5/21/2011 |
| Q: You have explained about hiding all the drop down combo box using a macro. Now I'm having 3 combo ... A: Jay, with no more information than you have given, all I can say is to set the visible property to ... | |
| Macro Help - Find the cell with the highest value in a column | 5/20/2011 |
| Q: Using Excel 2010. I am trying to write a macro to go to the cell in column Q that has the highest ... A: Wayne, Sub ClearData() Dim r As Range, r1 As Range, r2 As Range Dim r3 As Range, maxval As ... | |
| Macro Help - Find the cell with the highest value in a column | 5/20/2011 |
| Q: Using Excel 2010. I am trying to write a macro to go to the cell in column Q that has the highest ... A: Wayne, Your picture never appeared - perhaps it is too large. but I would use code like this (I ... | |
| Enable grouping activity on protected sheet | 5/20/2011 |
| Q: I'm in 2007, and I have a sheet that I want to protect, but the data is grouped. What can I do to ... A: Daniel Douglas, read this article Some of the properties of worksheets are not preserved in Excel ... | |
| Remove Duplicates Based on Date Criteria | 5/20/2011 |
| Q: greetings from Australia. Thank you for all your help toasted. I need to trim large number of sales ... A: Yogi, you example shows your rows being sorted so that the last occurance of a LocNum is in the ... | |
| Excel Address to Word Labels Help | 5/19/2011 |
| Q: I was given a huge excel spreadsheet full of names, streets, and ZIP-CODES, and I need to take these ... A: Matthew, Word has the built in capability to print address labels. You just walk through the ... | |
| Excel 2007 VBA: Inserting Rows and joining range | 5/19/2011 |
| Q: Tom, I have my own answer to this coding problem but I need your help how to implement it with ... A: Mark, >only a section of my range down first, the code doesn't shift things down. It inserts 4 ... | |
| Excel 2007 VBA: Inserting Rows and joining range | 5/19/2011 |
| Q: Tom, I have my own answer to this coding problem but I need your help how to implement it with ... A: Mark It doesn't appear that you have a named range "Existing Range" since named ranges can't have a ... | |
| VBA | 5/19/2011 |
| Q: long time no question. I'm just trying to get my head around some VBA code whenever I try and step ... A: J What you show is the declaration for a subroutine. It is designed to be called from another ... | |
| pivot table value lookup | 5/19/2011 |
| Q: I have created a pivot table based on a master table which has columns A, B and C. C is a number. In ... A: mark twain, Assume cells A5 and B5 contain the ID and maximum value combination that you want to ... | |
| Excel 2007 VBA: Inserting Rows and joining range | 5/18/2011 |
| Q: Tom, I have my own answer to this coding problem but I need your help how to implement it with ... A: Mark, that code inserted a row/rows near the top of the named range. I was under the impression ... | |
| Date values wrong in EXCEL | 5/18/2011 |
| Q: I have two spreadsheets in MAC EXCEL. The date value 39217 exists in both spreadsheets. In one ... A: Gregg, As you have demonstrated, Excel stores dates (and times) as the elapsed number of days from ... | |
| Counting highlighted cells in column after using conditional formatting | 5/18/2011 |
| Q: I am having a problem with CountColor function. It does not count highlighted cells in a column ... A: Dale, You didn't ask me this question - I found it in the question pool. Are you using Chip ... | |
| HELP: SEARCH function using range instead of single value | 5/18/2011 |
| Q: I currently have a worksheet that has a bunch of reasons in column H. (return of merchandise to a ... A: marcel, this worked for me: =IF(OR(ISNUMBER(SEARCH($O$2:$O$5000,H2))),"YES","NO") this must be ... | |
| vlookup w/ AUTO column index number | 5/18/2011 |
| Q: I am getting myself into trouble when I hard code the column index number in vlookup because every ... A: Joe, as I understand it you have a range of data named Table1 as an example. and you want to do a ... | |
| Excel calculations | 5/17/2011 |
| Q: I wanted to know if it is possible to add a numeric value to a cell containing any information in ... A: Diane, a cell can only have the value it displays for the most part. but here are a couple of ... | |
| Excel 2003 XP Cannot get rid of Shared Worksheet | 5/17/2011 |
| Q: I have Excel page with text box and text in upper half of page; lower half has 4 columns of names. ... A: John, Hopefully you have the original shared workbook. You should close all workbooks and open ... | |
| VBA read a record, write a new (conditional on content) | 5/16/2011 |
| Q: Excel 2007 (Danish version). I have a sheet (named lessons) with > 200 lines and 6 cells in each ... A: Uffe, This worked for me if the 0 is in cell A1 on sheet named lessons. you have given two ... | |
| VBA - Conditional Formatting | 5/16/2011 |
| Q: Good day Tom, I'm trying to generate a Conditional Formatting using VBA. Is there a way to set the ... A: Daniel, Here is a list of the xlformatConditionalOperator constants which you can use. ... | |
| COUNTIF Function in EXCEL 2007 | 5/16/2011 |
| Q: I need help with a countif formula: I have a table: - In column A (A2:A25): The days of the week ... A: Tom If I correctly understand the situation and the cells A2:A25 and B2:M2 actually contain strings ... | |
| Excel 2000 | 5/13/2011 |
| Q: I have an exisitng document in Excel 2000 which I now want to capitalize all the text. How can I do ... A: Joey, Add a sheet to your workbook. Assume the original sheet is named Sheet1 in the new sheet, ... | |
| Excel 2007 Match columns | 5/13/2011 |
| Q: I now have a fully functioning spreadsheet filtering data from different sheets. I use the formula: ... A: Simon, I guess I am not following. If you could send a small sample workbook (a little bit of data ... | |
| Excel VBScript & Conditional formatting Question | 5/12/2011 |
| Q: I am running Excel 2003 SP3 and have four conditions I want to use in my conditional formatting, ... A: Mike, the only way to do what you want is to use the Calculate event. Unfortunately, that event ... | |
| VBA: Find word | 5/11/2011 |
| Q: Is there a way to make this macro FIND a row with "Page" in it, then check to see if column A (one ... A: TJ, Sub abc() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, "A").End(xlUp).Row For i = ... | |
| VBA: Find word "*PAGE*" and delete row and 9 rows after, need help with conditional statement | 5/11/2011 |
| Q: Is there a way to make this macro FIND a row with "Page" in it, then check to see if column A (one ... A: TJ, I believe this will do what you describe. It appeared to work in my test with some dummy data: ... | |
| Finding MIN and MAX | 5/11/2011 |
| Q: Tom, on a sheet called Recap, a list of dates is in column A, and a list of values is alongside in ... A: Pete both min and max work as array formulas ... | |
| Auto loading data from a code number | 5/11/2011 |
| Q: I have a spreadsheet where a list of codes in column A correspond to specific data to each number ... A: Greg, since you have class codes in column A and you want to enter class codes in column A, then I ... | |
| compare | 5/8/2011 |
| Q: I want to compare two columns C & E and check difference, if there is difference of 0.01 then it ... A: Zaina Fatima, Sub comparevalues() Dim r As Range, cell As Range Dim e As Double e = 0.01 Set r = ... | |
| Decrease Decimal | 5/8/2011 |
| Q: I have data in the form of numbers in Col M from M10, M11, M12... so on. It has 2+ digits after ... A: Rahman, This worked for me as I understand the requirement. Sub Roundto2() Dim r As Range, cell As ... | |
| Problems with a data sorting macro | 5/8/2011 |
| Q: I'm having difficulty solving a problem with a macro. I have a sort macro (created by recording an ... A: Paul, instead of using a hard coded range in your code, you will have to make the range dynamic. ... | |
| Handling #N/A error when taking AVG( ) | 5/6/2011 |
| Q: I am trying to do this: ... A: Sarah, Here is another way to attack the problem (that I forgot to mention) if your sheet names are ... | |
| Fast or Slow? | 5/6/2011 |
| Q: I am feeling a little exhalted as I have just written my first VBA procedure that works, thanks in ... A: Steve, Just because you don't hide a row that contains a formula with a totals doesn't really mean ... | |
| Fast or Slow? | 5/6/2011 |
| Q: I am feeling a little exhalted as I have just written my first VBA procedure that works, thanks in ... A: Steve, It took about 5 to 10 seconds on my system with the 18 columns and 1,862 rows (I misread the ... | |
| Data types for Variables | 5/5/2011 |
| Q: I notice that when someone has written a procedure for say, deleting or hiding rows, I have seen ... A: Steve Davis, both integer's and Long's are whole numbers. The long requires twice as much storage ... | |
| Weighted averages | 5/5/2011 |
| Q: My question is more about how to correctly calculate something, versus how to use Excel. Hope you ... A: Nate, assume the first product is in column B from Row 2 to row 26. the weights in column D from ... | |
| Combining If statements | 5/5/2011 |
| Q: I am trying to combine 2 formulas in one cell, but keep receiving errors. Can you help? Here are ... A: Stacy, one problme is that you second formula if(sum(k15:k26)=8, " ",8,8-sum(k15:k21) is invalide. ... | |
| Ignore Zeros in Top 10 Reoccuring Text | 5/5/2011 |
| Q: Hey Tom, I have a series of functions used to calculate the top ten reoccuring text values from a ... A: Stephen, I just got errors in the formulas in E1, F2, and G2. So I got rid of those and put this ... | |
| Follow on from last question | 5/5/2011 |
| Q: Tom, On my last question, if you were counting columns then you would put .column instead of ... A: Steve Davis, yes and no for i = cells(1,columns.count).End(xltoLeft).column to 1 step -1 the ... | |
| Merge Madness | 5/5/2011 |
| Q: no macro is ever easy as it seems but for the life of me i have been struggling with this macro for ... A: Paul Sub ABC() Dim bk1 As Workbook, bk As Workbook Dim spath As String, sname As String Dim sh As ... | |
| Countifs | 5/5/2011 |
| Q: I'm using Excel 2007. I need to count items with 3 different criteria. the first criteria is "a" ... A: Lena, =COUNTIFS($A:$A,"a",$B:$B,"segment",$C:$C,">=0",$C:$C,"<=1000") that worked for me. I ... | |
| Excel Query | 5/4/2011 |
| Q: I would just like to get a plain English explanation of what the following code means:"For i = ... A: Steve Davis, go to you worksheet in column D and go all the way to the bottom (row 1048576). Now ... | |
| Countif Multiple Criteria | 5/4/2011 |
| Q: I have a column for time "B", IP address "C", and status "D". I am trying to run a countif statement ... A: Jason, sorry for the delayed response - I teach a 5 hour class on Wed nights. I pasted in your ... | |
| Countif Multiple Criteria | 5/4/2011 |
| Q: I have a column for time "B", IP address "C", and status "D". I am trying to run a countif statement ... A: Jason, In Excel 2007 or later, you could use the new Countifs formula to check for multiple ... | |
| Merge Madness | 5/4/2011 |
| Q: no macro is ever easy as it seems but for the life of me i have been struggling with this macro for ... A: paul, Lets take the activesheet as an example with activesheet lastcolumn = .cells(1, ... | |
| Incorporating pivot tables in VBA Macro Excel (2) | 5/3/2011 |
| Q: Regarding your advice to use the macro recorder to create pivot tables, I have actually attempted ... A: Irma, You show the source of the data for your Pivot Table as "LIWD!C1:C5". That is 5 cells in a ... | |
| Data copy in a variable | 5/3/2011 |
| Q: This is regarding the question I asked yesterday. Below is the link. ... A: Masood, >I cannot make the text SNo to sno (lower case) as it is pulled up dynamically from ... | |
| Data copy in a variable | 5/3/2011 |
| Q: This is regarding the question I asked yesterday. Below is the link. ... A: Masgood, you can change this line If InStr(1, sh2.Cells(1, i), "SNo", vbTextCompare) Then to If ... | |
| Hiding cells based on cell format | 5/3/2011 |
| Q: first of all I appreciate your time, thank you. Ok, I have a spreadsheet that has a formula in a ... A: Steve Davis, When you make that comparison, the value of the cell is coerced into a number to make ... | |
| obtaining formula | 5/3/2011 |
| Q: I don't even know if there is such a function, but is there a way in excel to obtain a formula given ... A: Manuel Say data is in rows 1 to 100. Lets say I have names in column A and ages in column B and ... | |
| VBA? Macro or Formula to Group rows and either border it in groups or shade | 5/2/2011 |
| Q: I have been working on this report and keep running into roadblocks. I have a ton of info and I am ... A: TJ, It sounds like you want to have one row per entry/Cable code. If you do that then you can ... | |
| Data copy in a variable | 5/2/2011 |
| Q: I'm looking for a macro which will store different cell values in one variable and show it in Sheet1 ... A: Masood based on the change you made: Sub gatherdata() Dim sh1 As Worksheet, sh2 As Worksheet Dim ... | |
| VB macro to find cells containing Page and then delete entire row | 5/2/2011 |
| Q: I have a text file that I am changing to a spreadsheet. Only problem is when I import it into the ... A: TJ Sub DeleteRows() Dim rng as Range do Set rng = cells.Find(What:="page", _ ... | |
| Hyperlink and Compare | 4/30/2011 |
| Q: I have two questions. Please assist me with the macro code for the same. First----------------- ... A: Ahmed, Sub Mylink() Dim r As Range, cell As Range Dim linkIt As String, Linkform As String Set r = ... | |
| Pop up warning box | 4/30/2011 |
| Q: I have a worksheet at work contain pieces of equipment in cloumn A dates of last service in coloumn ... A: Richard, If you want a box to pop up, then you would need a macro to do that. make your workbook ... | |
| Copy Data | 4/30/2011 |
| Q: I have a very simple query for you but it is very tricky for me. I have data in Sheet2 from that i ... A: Khaja, Sub Macro1() Dim r as Range With Worksheets("Sheet1") set r = ... | |
| Vlookup | 4/29/2011 |
| Q: Do you know of a good site to see a reference on using the vlookup function in excel. Mainly I want ... A: Rubin, this is rather involved - but since Ashish Mathur has documented how to do it, let me refer ... | |
| 2003 to 2007? | 4/29/2011 |
| Q: Long time now "see". I hopw You are fine and helping the people out there with your knowledge... I ... A: attis, I guess I didn't understand your intent. Then all you need to do is remove the shNew.copy ... | |
| 2003 to 2007? | 4/29/2011 |
| Q: Long time now "see". I hopw You are fine and helping the people out there with your knowledge... I ... A: attis, this is what the code does: Sub test() Dim sh As Worksheet Dim rng As Range Dim c ... | |
| Variable 2010 sort code | 4/28/2011 |
| Q: Tom, I have recorded the following sort macro in Excel 2010. Range("A2:P42").Select ... A: Alan, Range("A2",cells(rows.count,"A").End(xlup)).Resize(,16).Select ... | |
| VBA Feet & Inch Conversion & Calculation | 4/27/2011 |
| Q: Good day Tom, Perhaps you can help me with a very small issue in programming VBA in Excel 2007. I ... A: Mark, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Set r = Range("Convert") ... | |
| VBA validate in between time | 4/27/2011 |
| Q: I need your help with VBA to validate time frame. Assuming I have a random Time value in activecell ... A: Damian, You have divided a day into 4 pieces ----|----|----|---- so the first vertical line is ... | |
| VBA validate in between time | 4/27/2011 |
| Q: I need your help with VBA to validate time frame. Assuming I have a random Time value in activecell ... A: Damian, Here is a code snipped that might give you some ideas: so the cell only contains a time ... | |
| VBA Feet & Inch Conversion & Calculation | 4/27/2011 |
| Q: Good day Tom, Perhaps you can help me with a very small issue in programming VBA in Excel 2007. I ... A: Mark, here is the basic approach to convert on entry. You would right click on the sheet tab and ... | |
| Data set | 4/27/2011 |
| Q: I was wondering if you could help me. I have a data set: column A is the date (Sept 27-Oct 5), ... A: Kim, there are many different approaches you could use to attack this problem. One is you could ... | |
| Variable 2010 sort code | 4/27/2011 |
| Q: Tom, I have recorded the following sort macro in Excel 2010. Range("A2:P42").Select ... A: Alan, Range("A2",cells(rows.count,"A").End(xlup).Resize(,16).Select ... | |
| VBA Solution - Copy Specific Sheet from all workbooks ina folder | 4/27/2011 |
| Q: Good day! Please assist with a vba to achieve the following: 1. Copy worksheet"Report Data" from ... A: Yogi, assume the master workbook is the workbook that contains the code Sub ABC() Dim ans As ... | |
| collecting data in arrays using if statements | 4/27/2011 |
| Q: VBA, Excel 07 and 03 I am trying to write a program to solve certain problems in engineering ... A: Jennifer, here is a guess values in B1:B200 with x markers in column A in the same row Dim ... | |
| Ignoring blank lines | 4/26/2011 |
| Q: I'm using Excel 2007. I'm importing responses from a survey from .csv to Excel. The comments fields ... A: Nancy Marsh, here is the way I would approach it. Just use a loop and some arrays to hold the ... | |
| Ignoring blank lines | 4/26/2011 |
| Q: I'm using Excel 2007. I'm importing responses from a survey from .csv to Excel. The comments fields ... A: Nancy, Sub InstructorComments1() ' ' InstructorComments1 Macro ' ' Dim r As Range Dim r1 As ... | |
| comparing two columns returning multiple matching rows from third column | 4/25/2011 |
| Q: Do you have a macro or a function to compare column a with column b to return all matching cells ... A: Jose, This is reasonably complex, so let me refer you to this site where the author has taken some ... | |
| excel 2000 | 4/25/2011 |
| Q: What I am trying to do is have a phone number pop up in a different box from a name that I select ... A: Kris, if by box you mean a cell and by popup you mean to have the cell display the phone number, ... | |
| Data copy in a variable | 4/24/2011 |
| Q: I'm looking for a macro which will store different cell values in one variable and show it in Sheet1 ... A: Masood Sub gatherdata() Dim sh1 As Worksheet, sh2 As Worksheet Dim cell As Range, s As String Dim ... | |
| Data copy in a variable | 4/24/2011 |
| Q: I'm looking for a macro which will store different cell values in one variable and show it in Sheet1 ... A: Masood, this worked for me with your sample data. I placed "SNo" in cell A1 on Sheet2 just to ... | |
| Count if with two conditions | 4/23/2011 |
| Q: I´m doing a lotto statistics file, and i want a formula that counts of a line of numbers A5:E5 , the ... A: Francisco, in Excel 2007 and later you could use =Countifs(A5:E5,">10",A5:E5,"<=20") (countifs ... | |
| Adding Certain Cell entries and totaling them | 4/23/2011 |
| Q: Tom, This will hopefully be an easy question to answer. I can't figure out how to word this so I'll ... A: Bill On way would be to use a pivot table if you want to use formulas, you can put your list of ... | |
| Copy Cell.value | 4/23/2011 |
| Q: I have a question which is very difficult for me. I have some data in Sheet2. I am looking for ... A: Mohammed I assume you don't have blank cells mixed in with your data in column L Sub ABC() Dim sh2 ... | |
| A stickler | 4/22/2011 |
| Q: Tom, I've pondered this for a while, but cannot find a way to do accomplish what I want. Let me see ... A: Taylor, in D1 put in the formula ... | |
| excel | 4/22/2011 |
| Q: if the sum of a column of numbers is a positive number then the number, but if the sum is a negative ... A: Bobbie, They should give the same results. The other person's (Bob Umlas?) is more efficient (it ... | |
| Thanks! One more question! | 4/21/2011 |
| Q: ORIGINAL QUESTION AND ANSWER: I am trying to combine the two workbooks together for one solid ... A: TJ, basically you would have to countif against each sheet/book combination ... | |
| Ignoring blank lines | 4/21/2011 |
| Q: I'm using Excel 2007. I'm importing responses from a survey from .csv to Excel. The comments fields ... A: Nancy, just select the column with the comments. hit F5 choose special in the dialog select ... | |
| MACROS: saving 2 files in different locations + have only ONE of them protected | 4/21/2011 |
| Q: I have a master excel workbook in which I do changes. I want that everytime that I save the file, a ... A: Mary, If you are using xl2003 or earlier, do Alt+F11 to go into the Visual Basic Editor. in the ... | |
| MACROS: saving 2 files in different locations + have only ONE of them protected | 4/21/2011 |
| Q: I have a master excel workbook in which I do changes. I want that everytime that I save the file, a ... A: Mary, Just reverse the order: Sub Savingtwofiles() chDir1 = "D:\mary\" chDir2 = "C:\My ... | |
| More Help for inventory. | 4/21/2011 |
| Q: Your last solution worked perfectly, thanks again. However, now I have another scenario. Barcoded ... A: Brendan, I think you need to scan into an inputbox rather than into the cell directly. So this ... | |
| Workbook object | 4/21/2011 |
| Q: Tom, In Excel 2007, I have an Open File dialog box which allows the user to multi-select files for ... A: Terry, addendum: Terry, if you think the syntax you posted was correct - then I did a poor job of ... | |
| Import to Excel with VB Script | 4/20/2011 |
| Q: Question #1: I am trying to automatically import a data file into excel 2007 using a VB Script. The ... A: Steven, Sub Auto_Open() Dim sPath as string, sName as String Dim bk as Workbook sPath = ... | |
| Countif with time criteria | 4/20/2011 |
| Q: I am having a lot of problems to set function for my requirements. I need to calculate sum based ... A: Damian, =COUNTIFS(A:A,"a",B:B,">="&TIME(6,0,0),B:B,"<="&TIME(22,0,0)) Note that I am using ... | |
| Triggering macros with DDE linked cells. | 4/20/2011 |
| Q: First of all, i want to thank you for this thread: ... A: Sergio, I assume you are using this macro: Sub Test() Dim wbLinks As Variant Dim i As ... | |
| Workbook object | 4/20/2011 |
| Q: Tom, In Excel 2007, I have an Open File dialog box which allows the user to multi-select files for ... A: Terry, If you selected the files in a file open dialog, then I assume the workbooks are at least ... | |
| Finding the difference from two drop down lists | 4/19/2011 |
| Q: I have created drop down lists to enter the start time and ending time of a work day. Can a cell or ... A: Lee, dates are stored as the elapsed number of days from a base date - likewise, time is the ... | |
| Excel 2003 VBA Loop | 4/19/2011 |
| Q: I'm fairly new to VBA and macros and mostly use the record function in excel to do what I require ... A: Steve, Here is a possibility, but no guarantees: I assume you are always searching beyond where ... | |
| looking up data from one list, and pulling it into another | 4/18/2011 |
| Q: i'm in excel 2007 now, and my issue is as follows: i have two lists of data: one which contains a ... A: Sandy, Since you didn't give any cell locations, I will make some up. I assume both lists are on ... | |
| Graphing percentage changes | 4/18/2011 |
| Q: This is Jeanne again. I wrote you over the weekend about comparing two figures and finding ... A: Jeanne, I was a little confused by this posting - but I went back and looked at my sent items and ... | |
| VBA Code | 4/18/2011 |
| Q: I wanted a VBA code to make following work to be automated. Copy B2of Sheet2 and Paste in B2 of ... A: Abdul Jaleel that was a typo. I misspelled address. and an omission - printarea is an attribute ... | |
| display time greater than 24 hours in label | 4/18/2011 |
| Q: I have a worksheet which the data inside is formatted as [h]:mm:ss.I want it to appear in the format ... A: Xin, the format statement does not support all the formatting options a cell supports. Since your ... | |
| VBA Code | 4/18/2011 |
| Q: I wanted a VBA code to make following work to be automated. Copy B2of Sheet2 and Paste in B2 of ... A: Abdul Jaleel, I would see it being something like this. Since your print area is so large, you ... | |
| barcode scanner find in excel | 4/17/2011 |
| Q: Is there any way that I can get my motorola symbol ls1902t barcode scanner to "find" the ... A: Mary, I have no knowledge of your specific scanner, but most bar code scanners provide an interface ... | |
| Graphing percentage changes | 4/17/2011 |
| Q: This is Jeanne again. I wrote you over the weekend about comparing two figures and finding ... A: Jeanne, Dates and times are stored as the number of days from a base date. For Windows, the ... | |
| Conditional Statements | 4/16/2011 |
| Q: Tom, I want to have two IF functions in one cell. The problem is that the second IF statement ... A: Craig, this is what I understand you to want If 'Doe, John'!A2 is true then you want to display an ... | |
| returning values in adjacent cells | 4/16/2011 |
| Q: I'm using excel 2003. I have a column of dates (A1:A39)which in some cases are the same date, how ... A: Ron, in your formula you first check whether the Index function is successful by including it ... | |
| Regrouping Worksheets | 4/15/2011 |
| Q: I am sure there must be a simple answer to this, I hope you have it! To better explain I will give ... A: Ana, if the folder is not open, then that would be an indication that the folder is marked as read ... | |
| returning values in adjacent cells | 4/15/2011 |
| Q: I'm using excel 2003. I have a column of dates (A1:A39)which in some cases are the same date, how ... A: Ron, Ashish Matur has written an article that describes how to do what I believe you are trying to ... | |
| HLOOKUP | 4/15/2011 |
| Q: Excel 2002,I'm using HLOOKUP, and it works perfect except, when I don't have an item in my data, it ... A: Michele, Hlookup is fine. You just have to understand its capabilities. By making the 4th argument ... | |
| Variable Offset (VBA) | 4/14/2011 |
| Q: Hope you are well, Wondering if you could take a look for me. This VBA does pretty much everything ... A: J, if you have 7 alternatives and you need to assign unique values based on the alternative being ... | |
| VB Macro to delete a character in a cell, then move on | 4/14/2011 |
| Q: I have a column in Excel with a list of numbers with an "s" at the beggining. For exampe "s01". I ... A: Adam Sub removeS() Dim cell As Range, r As Range, r1 As Range Dim lastrow As Long, col As Long Set ... | |
| Excel, Inserting Checkbox (form control) linked to fields with VBA | 4/13/2011 |
| Q: first, I'm using Office (Excel) 2007 SP2, on a windows XP machine. What i am trying to accomplish ... A: Allen, Make sure the text is right justified and the column width is bigger than normal to give ... | |
| Import Hyperlinked File List on open | 4/13/2011 |
| Q: Good morning Tom, I use MrExcel & Excel Banter all the time, and have seen your expertise many ... A: Ky, I am neither on Mr Excel nor Excel Banter (which I have never heard of actually), so I am not ... | |
| Excel | 4/13/2011 |
| Q: SIR PLS CHECK MY QUESTION AT END OF THIS PAGE TYPED IN CAPITL LETTERSHi and thanks Mr Tom. It worked ... A: Aman, >SIR THE ABOVE MACRO WORKS ONLY IN B1 AND D1 I WANT IT TO GET WORKED IN WHOLE COLUMN You say ... | |
| Excel | 4/13/2011 |
| Q: I have a tiny doubt, hope u can hlp me. i have a number 32 in a1 and when i put value 3 in b1 then ... A: Aman, if you want to have G1 contain the date when either an entry is made in B1 or D1 then ... | |
| Select characters to the left of VBA | 4/13/2011 |
| Q: I'm wondering if you're aware of any way that I could using VBA select all of the Characters to the ... A: J, http://www.allexperts.com/central/awards/topcat_index.htm shows that I have not received it ... | |
| Select characters to the left of VBA | 4/13/2011 |
| Q: I'm wondering if you're aware of any way that I could using VBA select all of the Characters to the ... A: j dim iloc as long dim s as string dim s1 as string s1 = "6a_national" iloc = instr(1, s1, "_", ... | |
| Excel | 4/13/2011 |
| Q: I have a tiny doubt, hope u can hlp me. i have a number 32 in a1 and when i put value 3 in b1 then ... A: Aman Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, cell As Range Set r = ... | |
| VBA Macro for Excell 2003 | 4/12/2011 |
| Q: I am a novice at VBA but trying really hard to automate some of my processes. I need help with a ... A: Maria, assume the last row can be determined by checking column A Sub AddaBlankRow() With ... | |
| Excel 2007 Comparing Data | 4/12/2011 |
| Q: Excel 2007: I am trying to see if there is a single formula I can use to do the following as I am ... A: Dave, if all control numbers are in column E in sheet 2, then you say if the control number from ... | |
| email excel sheet | 4/12/2011 |
| Q: Is there a way for me to email a selection say on my excel workbook sheet A1 to H46 and pick up cell ... A: billy with the sheet active that has the information you want to send. I am assuming you want to ... | |
| email excel sheet | 4/12/2011 |
| Q: Is there a way for me to email a selection say on my excel workbook sheet A1 to H46 and pick up cell ... A: billy, Ron de Bruin has written a broad array of code samples that have to do with mailing ... | |
| checkboxes | 4/12/2011 |
| Q: i have a sheet called faxsheet in excel and on this sheet i have a drop down box C17 that has ... A: billy, Place 1, then you can copy it by holding down the control key and dragging down. That is ... | |
| if and look up regarding several sheets | 4/12/2011 |
| Q: i have a sheet called faxsheet in excel and on this sheet i have a drop down box C17 that has ... A: Billy, I would see the formula in the cell where you want the results being: ... | |
| More on charting | 4/12/2011 |
| Q: Tom, I'm still amazed by the chart tricks you showed me a few days ago. Now I'd like to get a bit ... A: Alana, you would have to change your 2nd and 3rd series to be constant values such as 20 and 40 ... | |
| vba merging values in cells | 4/12/2011 |
| Q: Hoping to seek you help in creating a macro, I am absolutely novice at this. I just want to create ... A: Dave, why not just use a formula say in C1 =if(And(len(trim(A1))>0,len(trim(B1))>0),A1&B1,"") ... | |
| Regrouping Worksheets | 4/11/2011 |
| Q: I am sure there must be a simple answer to this, I hope you have it! To better explain I will give ... A: Ana, this would be a possibility. I assume you workbooks are named 2001.xls through 2011.xls and ... | |
| Formula to compare, then calculate on two worksheets | 4/10/2011 |
| Q: I sent this question to another expert last night, but his turnaround time is long and didn't ... A: Jeane, If I copied and pasted you explanation of what you want then > For the sake of clarity, A2 ... | |
| Charting | 4/10/2011 |
| Q: Tom, I'm an advanced Excel user, but am stumped on how to chart this particular data. Haven't run ... A: Alana, I won't argue the point, but the time values were spaced at .05 intervals - they may have ... | |
| Charting | 4/10/2011 |
| Q: Tom, I'm an advanced Excel user, but am stumped on how to chart this particular data. Haven't run ... A: Alana, I guess you misunderstood my answer or didn't look at the chart closely enough. I have ... | |
| Charting | 4/10/2011 |
| Q: Tom, I'm an advanced Excel user, but am stumped on how to chart this particular data. Haven't run ... A: Alana, you need to use an XY-Scatter plot. This is the only excel graph that does not use a ... | |
| Formula to compare, then calculate on two worksheets | 4/10/2011 |
| Q: I sent this question to another expert last night, but his turnaround time is long and didn't ... A: Jeanne =abs(May!A2-April!A2)/April!A2 format the cell as a percentage if you want a long ... | |
| VLookup | 4/9/2011 |
| Q: abc = ... A: Harry, you didn't ask me this question - I found it in the question pool. if you only have one ... | |
| Quarterly count of all active cases | 4/8/2011 |
| Q: I use Office Excel 2003, SP3. Thanks for your time/help. I have a worksheet with the “date in” in ... A: Dave, I assume you still want to restrict the count to the specified quarter: ... | |
| excel question | 4/8/2011 |
| Q: Tom, I am compiling some scientific data in excel 11 and my first col. is date time entered 04/08/11 ... A: Mark, your picture just shows times and no dates. I will assume you have dates and times as you ... | |
| writing a macro | 4/8/2011 |
| Q: I am using Excel 2003 I need to produce a monthly summary document (every month). The data for the ... A: Ralph, If you want to send me a workbook with an explanation of what you want me to advise you on, ... | |
| set week number using vba | 4/8/2011 |
| Q: i have a pivot table with column label is the dates start from 20.02.2011 til 24.12.2011. row label ... A: xin, you don't need vba code. just take you date and divide by 7. That will give you the number ... | |
| Excel 2007 IF or SUMPRODUCT 2 | 4/7/2011 |
| Q: Sorry, I confused you. In writing it out again, I now see more clearly what I want. I have three ... A: Simon Just as simple would be to change the formula from ='2010-11'!C2-'2009-10'!C2 to ... | |
| extracting text from multiple excel files | 4/7/2011 |
| Q: First of all, thanks for taking the time to read this and trying to help me out. If you can you'll ... A: nate, open the workbook where you will record the information. Make the sheet where you want the ... | |
| Excel | 4/7/2011 |
| Q: How can I subtract dates and times in a spreadsheet like: "3/25/2011 1:37:50 PM" from "4/05/2010 ... A: David, A1: 3/25/2011 1:37:50 PM B1: 4/05/2010 2:30:00 AM C1: =A1-B1 gives me 354.463773148149 ... | |
| Dynamic Date In Pivot Table | 4/7/2011 |
| Q: Here i am with a crucial question once again. I am preparing KPI Dashboard for my department, in ... A: Sami, I can only give you a general answer since I have no knowledge of your data or your pivot ... | |
| Adding Additional Sheets to your macro | 4/7/2011 |
| Q: Your macro (4/4- Macro to copy 2 worksheets..)worked like a breeze.Thank you so much. How to add ... A: Yogi, Sub AddSheets() Dim shts As Object Dim shA As Worksheet, shB As Worksheet Dim sh As ... | |
| VBA 2003 | 4/6/2011 |
| Q: Trying to reference the ranges from the first sub in the second but it doesn't seem to want to pick ... A: j I can't really debug a runtime error looking at static code. I don't see anything specifically ... | |
| Excel formual | 4/6/2011 |
| Q: I'm working on ss in Excel (ver 2002). colum B tells me what grades children are in, ranging from ... A: Helen, I don't know who you asked on 3/29, but it wasn't me. If you want to send a sample file ... | |
| return value of the index of column | 4/6/2011 |
| Q: Cell A2:A21 =RANDBETWEEN(10,27), CELL B2:P2 = A,B,C....O respectively, cells B2:O21 ... A: Sahil, As you can see from the attached picture, the formula I gave you works exactly as I ... | |
| return value of the index of column | 4/6/2011 |
| Q: Cell A2:A21 =RANDBETWEEN(10,27), CELL B2:P2 = A,B,C....O respectively, cells B2:O21 ... A: sahil, YOu say the A, B, C....O is in B2:P2, but you also say you have random numbers there. So I ... | |
| Excel | 4/6/2011 |
| Q: How r u ? Sir i tried the answer u sent regaring the drop down list and i created and succeeded ... A: Aman, Answer really hasn't changed. Here I propose a specific range for your table of products and ... | |
| Excel 2003 VBA | 4/5/2011 |
| Q: Bet you thought you'd gotten rid of me. Any idea why I'm getting a compile error here? I need to ... A: j you need to remove these declarations: Dim tot_Name_Ranges As Variant Dim ann_Name_Ranges As ... | |
| XL pivots w/ limits, non-calendar quarters | 4/5/2011 |
| Q: I've got payroll information including dates, names and gross amounts for my employees. My ... A: Greg, If I was doing it, I would go to my source data and enter an additional column with the ... | |
| Macro to Copy 2 Worksheets from masterworkbook to all the Workbooks in a folder | 4/4/2011 |
| Q: Please help with a macro that will copy 2 specific worksheets ("MIF Data" & "AMS") from a master ... A: Yogi, This assumes Master Inspection Form has an extension of xlxm and it is open in the same ... | |
| find number in a value | 4/4/2011 |
| Q: my question is if there is a way to find exact number in numeric value in a cell. For example, if ... A: Kole, If B2 contains the value to be checked (1234 in your example) ... | |
| Sum of values in a range selecting interior color green only | 4/4/2011 |
| Q: Tom, Is there a way to use a macro that would select a range of cells, total the value of only the ... A: Barry S Yes a macro could do that - but best I can do is give you a macro with some assumptions and ... | |
| Excel | 4/4/2011 |
| Q: Mr Tom how r u ? hope sailing in pink of health. Thanks for solving my difficulties for the last ... A: aman, it sounds like you want to pick from a list of products in the cells in column A. You don't ... | |
| Combine two sets of data into one | 4/4/2011 |
| Q: I have two sets of data: Set #1 Set #2 A 1 A 5 B 5 B 8 C 8 C 10 D 14 E 28 How can a ... A: Michael for you example, I would put M1: A M2: B M3: C M4: D M5: E hopefully you can do that by ... | |
| Derive high frequency character within same column | 4/4/2011 |
| Q: i need a formula that derive high frequency character from text string within same column,with ... A: CK ----------------------------<revision>------- OK - I got it to work with UNICODE!! See the ... | |
| Excel | 4/3/2011 |
| Q: I have a tiny doubt, hope u can hlp me. i have a number 32 in a1 and when i put value 3 in b1 then ... A: Aman 32 in cell a1 3 in b1 now a1 must come to 29 put my code in a worksheet and followed you ... | |
| Excel | 4/3/2011 |
| Q: I have a tiny doubt, hope u can hlp me. i have a number 32 in a1 and when i put value 3 in b1 then ... A: Aman, right click on the sheet tab where you want this behavior and select view code put in this ... | |
| Auto pop up of result | 4/2/2011 |
| Q: Please help me in one more situation: I have column A,B&C. A contains $ amount, B is the %. C is A&B ... A: Trang in the situation you describe, you would need an IF statement - but I assume you want the ... | |
| Paste Link Excel 2010 | 4/1/2011 |
| Q: My paste link code will not run in Excel 2010. It works perfectly in 2003 & 2007 versions of Excel. ... A: Ian, I don't have excel 2010 here, but I have access to it at home. If you want to send the file I ... | |
| Paste Link Excel 2010 | 4/1/2011 |
| Q: My paste link code will not run in Excel 2010. It works perfectly in 2003 & 2007 versions of Excel. ... A: Ian, run you code on a new workbook with no formatting - just perhaps some numbers in the cells. ... | |
| Conditional Formating | 3/31/2011 |
| Q: I have two columns in excel, A&B. Whenever I type in "Y" in column A, I would like the same row in ... A: Trang, select column B with B1 as the active cell in the selection xl2007 and later: on the home ... | |
| Excel formula syntax using AND and OR in the logical | 3/31/2011 |
| Q: In my IF statement, I am trying to satisfy 3 logicals and I cant figure out the correct syntax. ... A: Sherra, You really haven't told me the conditions you are trying to check and what you want to do ... | |
| VBA Macro to split data into batches & subtotal them | 3/31/2011 |
| Q: Starting Position: I have a continuous (i.e. no rows between) list of numeric data where each row of ... A: Omar, Sub SplitData2() Dim rw As Long, cnt As Long, start As Long Dim lastrow As Long, i As Long ... | |
| Nested if function | 3/30/2011 |
| Q: I hope you doing well. Here is my query- Is there a way to use nested if function to test more than ... A: Mohammad, If you have excel 2007, the nesting restriction is something like 56 levels - so you ... | |
| Array formula: Daily Index Returns | 3/30/2011 |
| Q: Excel 2007 I would like to convert a series of daily index returns (i.e. 2.0%) into a monthly ... A: Steve, how about sending a sample workbook with your data and showing where (what cell) and how you ... | |
| variable off set figure | 3/30/2011 |
| Q: Same vba again, but I'm trying to change it slightly, basically the offset will increase each month ... A: j, OK. Glad you got it working. Everything isn't always clear right out of the box (in both ... | |
| averageif with multiple criteria | 3/30/2011 |
| Q: Column A has various colors entered in each cell. Column B has types of automobiles. Column C has ... A: John my answer was =Iferror(averageifs(C:C,A:A,"Red",B:B,"Truck),"") that is all you need. I ... | |
| variable off set figure | 3/30/2011 |
| Q: Same vba again, but I'm trying to change it slightly, basically the offset will increase each month ... A: j, is you are picking up the month from a cell and that cell will contain something like Mar or Feb ... | |
| variable off set figure | 3/30/2011 |
| Q: Same vba again, but I'm trying to change it slightly, basically the offset will increase each month ... A: J, You can get a number from the date sMonth = "Mar" ' since I only want to get the month number, ... | |
| Excel | 3/30/2011 |
| Q: Values are whole numbers (no decimals) Criteria A1 may have data B1 may have data C1 MUST have data ... A: Barry, Do you want to treat fl as equivalent to Null? the rules are the same except fl is ... | |
| Searching text in cells for words | 3/30/2011 |
| Q: I have a need to look at a column and see if certain words are present. I would like to have the ... A: Chris, Yes. In Excel 2003 and earlier, formulas could be 1024 characters in length. -< departed ... | |
| Excel | 3/30/2011 |
| Q: Values are whole numbers (no decimals) Criteria A1 may have data B1 may have data C1 MUST have data ... A: Barry - that is the rub. Formulas recalculate. You can turn calculation to Manual in Excel ... | |
| userform with 2 combo boxes and a textbox | 3/29/2011 |
| Q: I have a worksheet named planned hour. In the worksheet, column A contains the names, column B3:AS3 ... A: As i understand it you have a rectangle of data so you need to use the results of both comboboxes to ... | |
| Searching text in cells for words | 3/29/2011 |
| Q: I have a need to look at a column and see if certain words are present. I would like to have the ... A: Chris, =IF(ISERROR(SMALL(IF(ISNUMBER(SEARCH( ... | |
| VBA Macro to split data into batches & subtotal them | 3/29/2011 |
| Q: Starting Position: I have a continuous (i.e. no rows between) list of numeric data where each row of ... A: Omar, Sub SplitData1() Dim rw As Long, cnt As Long, start As Long Dim lastrow As Long, i As Long rw ... | |
| VBA Macro to split data into batches & subtotal them | 3/29/2011 |
| Q: Starting Position: I have a continuous (i.e. no rows between) list of numeric data where each row of ... A: Omar, Assume dates are still in column C. Seems easiest to continue to let the code put in the 3 ... | |
| Excel | 3/29/2011 |
| Q: Values are whole numbers (no decimals) Criteria A1 may have data B1 may have data C1 MUST have data ... A: Barry, You can have a formula that does that but it will recalculate whenever the sheet ... | |
| userform with 2 combo boxes and a textbox | 3/29/2011 |
| Q: I have a worksheet named planned hour. In the worksheet, column A contains the names, column B3:AS3 ... A: xin You can't set the rowsource property to a horizontal range (data in a single row). (well ... | |
| VBA Query, do in Loop | 3/29/2011 |
| Q: I'm getting an error that there is no 'DO' for my Loop, this isn't, making sense to me as, as far as ... A: j FindPatch = ActiveCell.Value Do Until ActiveCell.Value = "" Workbooks(strDt & ... | |
| Trying to set a dates font to change 30 days later | 3/29/2011 |
| Q: I have Excel 2007 and am trying to track "expirations" by changing the font color. Say I enter a ... A: Barrett, you mean you want to individually format 3 different cells or you want cell G5 to change ... | |
| Trying to set a dates font to change 30 days later | 3/28/2011 |
| Q: I have Excel 2007 and am trying to track "expirations" by changing the font color. Say I enter a ... A: Barrett, The conditional formatting formula would be =(today()-$G$5)>=90 would be what I would ... | |
| averageif with multiple criteria | 3/28/2011 |
| Q: Column A has various colors entered in each cell. Column B has types of automobiles. Column C has ... A: John, for multiple criteria, you can use the averageifs function (notice the "s" on the end of the ... | |
| coloring duplicates | 3/28/2011 |
| Q: I have a long list of part numbers and in that list there are many duplicates. I need to fill the ... A: Mercy I can't think of any easy way to do that - at least not by coloring in the cells with ... | |
| Microsoft Excel 2003 VBA | 3/28/2011 |
| Q: Hopefully the vba should make more sense now. As I said there are circumstances where findpatch will ... A: j if I assume that you want to copy no data when findpatch is not found in the second workbook then ... | |
| VBA code to locate last cell in column | 3/28/2011 |
| Q: I am looking for some VBA code find the last cell in column B that has data in it and then autofill ... A: John, go to the bottom of your worksheet (all the way to the bottom). That is what ... | |
| Excel 7 ignore empty cell | 3/27/2011 |
| Q: How can I get the simple formula shown here: =(32-B4) to ignore an empty cell as in the table ... A: Bill, your table is all jumbled together so it shows me nothing. So what do you want to show if ... | |
| Mulitply letters and numbers + sum totals | 3/27/2011 |
| Q: Tom, Hi. I have the following Data, comma's seperate col's. Col> Row1 v, v, .5v, s, , ,.5p, ... A: Ork AA1: =countif($A1:$Z1,"v")+.5*countif($A1:$Z1,".5v") AB1: ... | |
| Import files | 3/27/2011 |
| Q: I am using Microsoft excel 2003, I need help in importing files from another spreadsheet. I have ... A: Jn, this assumes that all the files in the specified directory are processed. put in the correct ... | |
| Efficient manipulation of hyperlinks while updating a Master spreadsheet | 3/27/2011 |
| Q: Tom, I have a Master spreadsheet that lists on each row a separate project, and in column B, ... A: David, you should use workbooks open. then you have control of the workbook. If you are using ... | |
| If cell values match then display value from a different cell | 3/27/2011 |
| Q: I'm trying to set up a function that checks for matching cell values on two different worksheets and ... A: Brent You can't show 40 different values in one cell. So your formula doesn't really convey to me ... | |
| VBA Macro to split data into batches & subtotal them | 3/26/2011 |
| Q: Starting Position: I have a continuous (i.e. no rows between) list of numeric data where each row of ... A: Omar, this assumes you data is sorted on column C - the Date column. Sub SplitData1() Dim rw As ... | |
| simple interest formula | 3/26/2011 |
| Q: I have a simple interst car loan, I pay it early. I need to figure out how to much interest I should ... A: Patsey, I am not an accountant or a financial advisor, so I wouldn't want to advise you on how ... | |
| question | 3/25/2011 |
| Q: the formula to extract the last name did not work. It also takes the middle name. Is there a way in ... A: Masha >the formula to extract the last name did not work >the last formula you suggested ... | |
| question | 3/25/2011 |
| Q: I have two excel spread sheets in which I need to find out whether or not the names listed on ... A: Masha, everywhere you have R11, replace it with Trim(r11) and the leading space will be removed for ... | |
| excel 2007 concatenate within a vlookup? | 3/25/2011 |
| Q: I am using excel 2007 I have two worksheets. One has company details including usage stats. The ... A: Simon, You can take advantage of the Subtotal worksheet function. It can detect visible data in a ... | |
| Excel usage for end users | 3/25/2011 |
| Q: How are you? Is there a way that a worksheet that includes macros could be transformed into a .exe ... A: Angel Resendiz, the simple answer is no. There is no way to just take a workbook or the code in a ... | |
| copy cells automatic | 3/25/2011 |
| Q: Cell B1:B13 i need to enter the values which i put in Cell A1:A13, but now as i continue putting ... A: Sahil in B1 put in the formula ... | |
| Looking an option in Data Validation to highlight if wrong code typed. | 3/25/2011 |
| Q: I’m looking an option in Data Validation to highlight if wrong code typed, for an example I have a ... A: Jamil A.Z. this would be done using conditional formatting - not data validation. You can have ... | |
| simple interest formula | 3/25/2011 |
| Q: I have a simple interst car loan, I pay it early. I need to figure out how to much interest I should ... A: Patsy Interest is assessed against the unpaid balance. So just take your monthly payment and ... | |
| question | 3/24/2011 |
| Q: I have two excel spread sheets in which I need to find out whether or not the names listed on ... A: It just counts the values in the range specified in the employee sheet to see if any match the name ... | |
| Autofilter Trigger Event | 3/24/2011 |
| Q: Tom, I have aa autofiltered range. I have a macro that utilizes the filtered data from the ... A: Steve, I tested to be sure, but I didn't find any event that triggered when the criteria was ... | |
| macro help | 3/24/2011 |
| Q: I think I'm running into problems when creating a range, as there is only one item in this ... A: Alison, Yes, you can not offset(1,0) if you are on the last row. I don't understand why you are ... | |
| excel 2007 concatenate within a vlookup? | 3/24/2011 |
| Q: I am using excel 2007 I have two worksheets. One has company details including usage stats. The ... A: Simon, In Excel 2007, they have a new function called IFERROR which handles this type of situation ... | |
| excel 2007 concatenate within a vlookup? | 3/24/2011 |
| Q: I am using excel 2007 I have two worksheets. One has company details including usage stats. The ... A: Simon, =VLOOKUP(A2,csdsearch!$A$2:$N$7,9,0)&" "&VLOOKUP(A2,csdsearch!$A$2:$N$7,10,0)&" ... | |
| macro help | 3/24/2011 |
| Q: I think I'm running into problems when creating a range, as there is only one item in this ... A: Alison, first, my code should have been Sheets("New33_3").Select Range("A1", ... | |
| Compare cells | 3/24/2011 |
| Q: Sir, How to compare the cells in different worksheets(2 worksheet) using for loop A: KLN, Sub ABC1() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, cell As Range, r2 As Range ... | |
| excel 2007 concatenate within a vlookup? | 3/24/2011 |
| Q: I am using excel 2007 I have two worksheets. One has company details including usage stats. The ... A: Simon, If you think vlookup by itself is ugly, you will hate this but this would be one way to do ... | |
| pulling data | 3/23/2011 |
| Q: The information in this picture is what I am working with. I need to pull info from this page and ... A: Cody, This is about what I can offer based on your description and picture. Sub ABC() Dim sh1 as ... | |
| average | 3/23/2011 |
| Q: My name is Bill LaMarch. I am using Excel 2003 SP3 to find an average. Here is my question. I have ... A: Bill, in B1 put in the formula =Average($A$1:A1) then select B1 again and there should be a ... | |
| Trying to find a conditional formula for copying/pasting above cell | 3/23/2011 |
| Q: Tom, I am using Excel 2003, and have been attempting to figure out a formula that if in column E ... A: Amanda, this was tested and worked exactly as I understand you to describe in your test situation. ... | |
| Trying to find a conditional formula for copying/pasting above cell | 3/23/2011 |
| Q: Tom, I am using Excel 2003, and have been attempting to figure out a formula that if in column E ... A: Amanda, you are using "cell" in your code, but it is not defined. also, I understand you to want ... | |
| Dates in VBA | 3/23/2011 |
| Q: Tom, I have a column of dates in a spreadsheet, in chronological order, formatted as dd/mm/yy. I ... A: Terry, open a new workbook and run this code on that workbook Sub ABC() Dim i As Long, dt As Date ... | |
| Save an active worksheet in a workbook to another workbook | 3/22/2011 |
| Q: Its me again, Is it possible to have a macro that will save an active worksheet in a workbook to ... A: Jn if you just want to save it as a workbook ActiveSheet.copy ' this makes the copy a one sheet ... | |
| Group Boxes for Option Buttons | 3/22/2011 |
| Q: How do I make the lines of Group Boxes invisible? I have about 10 on a work sheet vertically ... A: Ron, the lines of a group box on not configurable. If you don't want to have a square around ... | |
| Delete the rows containing L in Column O | 3/22/2011 |
| Q: Here's my problem, I need to delete the rows containing L in Column O. Column A1 to S1 are ... A: JN test this on a copy of your worksheet until you are sure it works as you want. Sub ... | |
| VBA Solution | 3/20/2011 |
| Q: I am wondering if you could help me write a macro, even just to get started. Basically what i want ... A: Dominica, Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim rw As Long Dim r As Range, r1 As ... | |
| making macros immune from sheet name changes | 3/20/2011 |
| Q: Tom, I have often wondered about this, and your two recent answers have made me wonder yet ... A: Bill, If you go to the Visual basic editor and look at the project manager, for each sheet you will ... | |
| trigger macro on cell change | 3/20/2011 |
| Q: I want a simple macro to run whenever a spefic cell on a specific sheet is changed. All the macro ... A: Bill, worksheets("Sheet5").PivotTables("PivotTable1").RefreshTable Of course you will need to ... | |
| trigger macro on cell change | 3/20/2011 |
| Q: I want a simple macro to run whenever a spefic cell on a specific sheet is changed. All the macro ... A: Bill, right click on the sheet tab of the sheet with the cell in question. select view code. in ... | |
| macro to copyand paste valid data range | 3/20/2011 |
| Q: My workbook have a range of datas in a range which are results of formulas entered in cells. But ... A: Hari, change Set r = sh1.Range("A5:G29") to identify the source range change Set r2 = ... | |
| macro to copyand paste valid data range | 3/20/2011 |
| Q: My workbook have a range of datas in a range which are results of formulas entered in cells. But ... A: Hari, I tested this in the situation I could garner from your description and what was written in ... | |
| linking multiple sheet totals to one cell | 3/18/2011 |
| Q: I am working with excel 2007. myi have several (4) worksheets in a workbook, each line in each ... A: Marie, Not really clear what you have already done - but you can link to other sheets in the same ... | |
| excel 2007 | 3/18/2011 |
| Q: You have kindly helped me before. How can i associate a hyperlink that will take me to a tab cell ... A: pravin makwana, =HYPERLINK("C:\Data\cox_adva_profitablity.xls#Sheet1!F9","go to other book) or ... | |
| excel 2007 | 3/18/2011 |
| Q: You have kindly helped me before. How can i associate a hyperlink that will take me to a tab cell ... A: Pravin makwana, to create a hyperlink to a cell in another worksheet in the same workbook, you can ... | |
| VBA code in excel 2007 | 3/18/2011 |
| Q: Good Evening Tom, I was wondering if you would be able to assist with the following? I have a ... A: Andy, Your code looked fine to me. Just to test: I pasted in your code in a sheet module and ... | |
| Filter based on cell value | 3/18/2011 |
| Q: I would like to filter Column A, starting from row 11 based on the value of cell J574. Column A ... A: Jim, I guess I don't understand the question. select your data (starting with the header in A10) ... | |
| Need an option to disable and enable the cell depend on code | 3/18/2011 |
| Q: I’m looking an option to disable and enable the cell depend on code, for an example I have a list ... A: Jamil A.Z. You can't really disable a cell just using formulas. That would take VBA code and you ... | |
| Help with Excel Formula | 3/17/2011 |
| Q: I am hoping you can assist me. I am doing a bonus payment spreadsheet (system size reflects the ... A: Julie h5: =if(D5<>"",vlookup($D5,$J$5:$J$58,2,False),0) then drag fill H5 down to the last filled ... | |
| Data validation for an evolving list of items | 3/15/2011 |
| Q: Tom, Please let me ask if you can suggest a way for me to set up a tracking spreadsheet that: (1) ... A: David, You can use data validation using the list option. > I have looked at options such as using ... | |
| VBA Solution | 3/15/2011 |
| Q: I am wondering if you could help me write a macro, even just to get started. Basically what i want ... A: Dominica, this worked for me: Sub Allocate_invigilators() Set sh = ActiveSheet Set rC = ... | |
| Macro to paste a selected excel worksheet range into PowerPoint | 3/15/2011 |
| Q: I have anotherI am using Excel 2007 and would like to create a macro to generate a PowerPoint slide ... A: Barbara, Most of this is the powerpoint object module - not my area of expertise, but I will try to ... | |
| Copy to bottom of variable # of rows | 3/15/2011 |
| Q: I use Excel 2007. I am new to Excel macros. I get a daily file with variable # of rows. I modify ... A: Norene, Dim lastrowD as Long, lastrowA as long lastrowD = cells(rows.count,"D").end(xlup).row ... | |
| if and question | 3/15/2011 |
| Q: Column A has entries that are either "OP" or "IP". Column B has various entries one of which is ... A: John, c2: =if(A2="OP",if(B2="Mod","Good","Bad"),if(A2="IP","","Neither OP or IP")) you said A2 ... | |
| Copy to bottom of variable # of rows | 3/15/2011 |
| Q: I use Excel 2007. I am new to Excel macros. I get a daily file with variable # of rows. I modify ... A: Norene, You can find the last used row in column A with lastrow = ... | |
| Excel Formula | 3/15/2011 |
| Q: I have a spreadsheet which monitors approximately 50 HR cases. Column A contains a person's name, ... A: Gill, in Excel 2007 and later =COUNTIFS($B$1:$B$50,"red",$C$1:$C$50,"Attendance") (note this is ... | |
| want to display data from bottom to top | 3/14/2011 |
| Q: I have a data in one column like this rakesh deepak santosh rekha sonu monu pratham i want to ... A: Rakesh, assume A1: rakesh . . . A7: pratham then in B1 B1: =Offset($A$1,CountA(A:A)-row(),0) ... | |
| SUMIF returns most frequent text string | 3/14/2011 |
| Q: I have a spreadsheet with two columns in - column A is a type of staff (trained, unqualified, ... A: Laureen, if you have excel 2007 or later, then this worked the same way as your formula (array ... | |
| Count of non-specific strings | 3/13/2011 |
| Q: I'm at the end of my wits. Trying to determine whether all orders (column B) forming a shipment ... A: Martin, It doesn't sound like the count is required - only to know if some combination of ... | |
| 2 Questions: Need to block a cell & Copy text from other cell. | 3/12/2011 |
| Q: I’m looking solution for 2 things, 1st I want to block a cell from being selected, not just edited ... A: Jamil, each cell has the option of being locked or unlocked. By default, all cells are locked. ... | |
| Filtering buttons on only certain columns | 3/12/2011 |
| Q: I would like to allow the users of my spreadsheet for a work project to: --Be able to use filter ... A: Scott, and that is what I told you how to do. However, when using an autofilter with data on a ... | |
| Filtering buttons on only certain columns | 3/12/2011 |
| Q: I would like to allow the users of my spreadsheet for a work project to: --Be able to use filter ... A: Scott, when you filter data, the entire row is filtered. You can select just columns B:E and apply ... | |
| Follow up to VBA Search | 3/12/2011 |
| Q: Apparently I've asked too many follow up questions... sorry about that. I modified the code as you ... A: Brian, the test search is mostly just code copied from the original macro. I have moved a couple ... | |
| Excel Lookup: Find Specific Column with Multiple Row matches | 3/11/2011 |
| Q: I'm working with a text import from an email survey. The order/number of columns change with each ... A: Kristen, the offset command has these parameters Offset(base cell,offset n rows, offset n columns, ... | |
| Follow up to VBA Search | 3/11/2011 |
| Q: Apparently I've asked too many follow up questions... sorry about that. I modified the code as you ... A: Brian, set this line of code: Set rngData = sh.Cells.Find(What:=strMySearch, _ ... | |
| Macro Modification - Search | 3/11/2011 |
| Q: I am using Excel 2003. I have a macro that I've been using but cannot figure out how to get it to ... A: I would change Set rngData = sh.Cells.Find(What:=strMySearch, _ After:=sh.Cells(sh.Rows.Count, ... | |
| new to excel and data | 3/11/2011 |
| Q: I hope I can explain my dilemma. Excel 2003 version. I was assigned a task by manager of creating ... A: olga, If you want to send me a sample file with a clear explanation of what you want to do, you can ... | |
| new to excel and data | 3/11/2011 |
| Q: I hope I can explain my dilemma. Excel 2003 version. I was assigned a task by manager of creating ... A: Olga, It sounds like you have a data table with information about each employee such as in a sheet ... | |
| Sheets names | 3/11/2011 |
| Q: I have a timesheet workbook with construction trades as sheet names. How do I get the sum of hours ... A: bobert, very hard to give you any type of a comprehensive answer with such a question. I know ... | |
| Excel 2007 calculation bug | 3/11/2011 |
| Q: i got this with excel 2007 debug.Print 86.7-86.3 0.400000000000006 debug.Print 86.7-86.9 ... A: AQUA, let me answer your question with a question? If you divide 1 by 3 on your calculator, is the ... | |
| Excel Lookup: Find Specific Column with Multiple Row matches | 3/10/2011 |
| Q: I'm working with a text import from an email survey. The order/number of columns change with each ... A: Kristen: once found, will the Option columns all be contiguous. ... | |
| Display a value based on data validation list selection | 3/10/2011 |
| Q: I am trying to construct a basic currency conversion utility. I have constructed a data validation ... A: MJ C3: =if(B21="","",HLOOKUP(B21,$C$7:$K$9,3,FALSE)) see the picture to see it in action: I ... | |
| Finding cell content in another workbook | 3/10/2011 |
| Q: I wanted to know coding to find a cell value in another workbook and hopefully copy another cell in ... A: Vikram, Sub ABC() Dim sh1 as worksheet, bk2 as workbook, sh as Worksheet Dim cell1 as Range, r1 as ... | |
| Link listbox selection to checkboxes? | 3/10/2011 |
| Q: I am working in Excel 2007. I have ActiveX checkboxes and listboxes. I would like to be able to have ... A: Barbara, You can do it without a macro. You would use the Linked Cell property to tie each control ... | |
| count of shared occurrences within a table | 3/10/2011 |
| Q: I'd like to count the shared times there is a shared occurrence among a table. I've got a list of ... A: Patrick in you example worksheet, enter this formula in cell E3 ... | |
| LEN & SUBSTITUTE function | 3/10/2011 |
| Q: I have this formula =IF(LEN(D4,1)="-"),D4+D5,D4-D5)&" Nos". But give error if D4 & D5 number is ... A: =IF(LEN(D4,1)="-"),D4+D5,D4-D5)&" Nos" and =C4-C3)&" Nos" both give me errors because you have ... | |
| Using VB code to extract selected value from autofilter | 3/10/2011 |
| Q: I hope you can help me with my small problem since I could not find any bit of code on the internet ... A: Justin, this would be the basic approach if only a single criteria is selected. Sub ... | |
| Microsoft Map Add-In | 3/9/2011 |
| Q: I'm trying to find a simple way to create maps in Excel. I regularly use MapPoint, but that is ... A: Carrie, Excel 98 was a macintosh version of Excel. I don't have any experience with that. I will ... | |
| Microsoft Map Add-In | 3/9/2011 |
| Q: I'm trying to find a simple way to create maps in Excel. I regularly use MapPoint, but that is ... A: Carrie, I don't recall that as an addin that excel could use, but if it wasn't distributed with ... | |
| barcode scanning for inventory control | 3/9/2011 |
| Q: Ogilvy, I run a small business and would like to keep track of inventory through excel. However, I ... A: Jaden, I am not familiar with the templates you describe. Nonetheless, perhaps this will work. It ... | |
| If Formula Problem | 3/9/2011 |
| Q: Survey Professional ... A: Pat, My interpretation is that if C193 and C194 both equal zero, then make the cell C195 appear ... | |
| Zeros | 3/9/2011 |
| Q: How do I get the zeros to show in the column when I type it instead of it disappearing once I move ... A: Xavier, when Excel stores a number and is allowed to determine how it is displayed, it does not ... | |
| Display a value based on data validation list selection | 3/8/2011 |
| Q: I am trying to construct a basic currency conversion utility. I have constructed a data validation ... A: MJ, With no details on the cells that hold any of the above, here is a suggestion: Look at the ... | |
| Hide Rows with cell value of -0- | 3/8/2011 |
| Q: I have a spreadsheet (Sheet1) with 600 rows, I would like to hide all rows in sheet2 (Sheet2 is a ... A: Jim Initial> I would like to hide all rows in sheet2 (Sheet2 is a duplicate of Sheet 1, but is ... | |
| Hide Rows with cell value of -0- | 3/8/2011 |
| Q: I have a spreadsheet (Sheet1) with 600 rows, I would like to hide all rows in sheet2 (Sheet2 is a ... A: Jim, since your results are being produced by a formula, there is a possibility that zero won't ... | |
| Protecting Formulas in Excel 2010 | 3/8/2011 |
| Q: I am having some trouble with an excel sheet and hope you can help! I have created a workbook for ... A: Lori, each cell is individually marked as locked or not. If you lock a cell, then when you protect ... | |
| Excel 2007 IF statement help | 3/8/2011 |
| Q: For item 64005150 thru 64005154 they receive 5.00, 64005155 thru 64005157 they will receive 10.00 ... A: Ken, I assume you want to use quantity as a multiplier of the payout amount. M2: ... | |
| LEN & SUBSTITUTE function | 3/8/2011 |
| Q: I have this formula =IF(LEN(D4,1)="-"),D4+D5,D4-D5)&" Nos". But give error if D4 & D5 number is ... A: Nabam, Len only takes one argument. It will return a number (not a hyphen) Len(D4)=0 Now is it ... | |
| Ignoring Zeros-Excel XNPV | 3/8/2011 |
| Q: Tom, I am trying to create an XNPV formula for a range that will change with every location (real ... A: Greg, > (I have zeros set to be blank instead of $0) since zero doesn't cause a #Num, assume that ... | |
| Excel requirement | 3/7/2011 |
| Q: I have a problem and I can´t solve it. I have a series of cells in this way: A B C ... A: Arthur, this assumes columns D:F are available to build the split data. Columns A:C are then ... | |
| VBA: insert rows for missing dates | 3/7/2011 |
| Q: Tom: I have several spreadsheets which track the itinerary of cruise ships. The data starts on row ... A: Bob, that would be an indication that you have Text in one of the cells in the column being ... | |
| Expenses workbooks summarize | 3/7/2011 |
| Q: I create a folder every year giving it the year name, in which I store my company expenses sheet ... A: Roberto, use a linking formula like these ='C:\myfolders\My ... | |
| Co-ordinates- Nearest 3 matches | 3/7/2011 |
| Q: In this thread http://www.excelforum.com/excel-programming/537429-closest-neighbour-coordinates.html ... A: Mark, for the same setup as listed in the example you found, I had data in A1:C20. I put values ... | |
| importing data from txt file to excel | 3/6/2011 |
| Q: I want to import 3 lines of data after word DATE from notepad file to excel sheet2. Notepad file is ... A: Navin, You can send the file if you want, but I am not sure it will tell me much. Your original ... | |
| Macro To Find Max Values Within Column | 3/6/2011 |
| Q: Could you please help a macro that can do the following? Column A: contains pricing information ... A: Paul, the macro runs one time. Formulas recalculate everytime the sheet recalculates. So ... | |
| Macro To Find Max Values Within Column | 3/6/2011 |
| Q: Could you please help a macro that can do the following? Column A: contains pricing information ... A: Paul, You can do this so easily with formulas, I don't see any point with using a macro. Your ... | |
| count text in multiple sheets | 3/6/2011 |
| Q: Tom I want to find absenteeism in a workbook in which multiple sheets are there and sheet is renamed ... A: Shell, the general form of the equation is ... | |
| a Kind of double v look up | 3/5/2011 |
| Q: I have a master sheet contains data upto 7500 rows in following manner.say column A,B, and C A B ... A: Rakesh Typo in the original formula corrected below M2: ... | |
| Macro modification | 3/5/2011 |
| Q: It's ok. I will tell you from the begining. on 25-APR-2010 i asked you question as below. I have a ... A: Rakesh, OK, so I would see it being this way: Sub BuildTable() Dim v(1 To 8), v1(1 To 8), vv1, vv2 ... | |
| VBA: insert rows for missing dates | 3/4/2011 |
| Q: Tom: I have several spreadsheets which track the itinerary of cruise ships. The data starts on row ... A: Bob, this assumes your first date starts in F2 or later. Also it assumes only dates appear in ... | |
| Large(IF()) = #NUM! Result - Excel '07 | 3/4/2011 |
| Q: Response / answer you provided 9/25/7 "Formula with Multi criteria, multiple cells" is resulting in ... A: Steven, I get #Num if I2 contains a number larger than the total quantity of numbers returned by ... | |
| Conditional formatting | 3/4/2011 |
| Q: Using excel 2010 conditional formatting I would like to highlight cells contain female names. They ... A: Alan, I assume you know how to use conditional formatting and you are just looking for a formula ... | |
| formulas | 3/4/2011 |
| Q: I am trying to get information to automatically populate from 1 spreadsheet into another based on a ... A: Sabrina, the information is very general so I can't give you a specific example, but I can explain ... | |
| Average values of various headings | 3/3/2011 |
| Q: Excel 2007. My sheet is as follows: UTkr ExDt Strike P/C PercMny IVM 1D% Ivol 1D ... A: Matt, Sounds like you just need to create a pivot table form your data. One of the summary options ... | |
| counting | 3/3/2011 |
| Q: I'm stuck here.. please help. I need to count the total session # per person. If session # = 0 ... A: Grace, this part of the solution ... | |
| counting | 3/3/2011 |
| Q: I'm stuck here.. please help. I need to count the total session # per person. If session # = 0 ... A: Grace, If you have Excel 2007 or later, you could use the countifs function to do most of your ... | |
| VBA link to specific worksheet (part 2) | 3/2/2011 |
| Q: I hope you are well. In follow up to a previous probelm you heped me with, I wondered if you could ... A: Stuart, this is untested, but unless I missed something, this should do it. Sub ExportInbox() On ... | |
| VBA To Remove Data | 3/2/2011 |
| Q: Please could you help with a Macro? I have a list of items that have been rented and there is a ... A: James so you want to loop through all the cells in column B and if there is an entry in the same ... | |
| Simple Stock inventory counting | 3/2/2011 |
| Q: Sir, thanks before I begin. I have excel 2007. I have a barcode reader for EAN codes. I have bought ... A: Brendan, right click on the sheet tab where you are doing this entry. Select View Code. Paste in ... | |
| Simple Stock inventory counting | 3/1/2011 |
| Q: Sir, thanks before I begin. I have excel 2007. I have a barcode reader for EAN codes. I have bought ... A: Brendan, Excel doesn't really have anything to do with your bar code reader. You bar code reader ... | |
| Calculating time interval to include blank rows | 3/1/2011 |
| Q: Sir, I am using Excel 2007 and have worksheet for recording my working, driving, and rest hours as a ... A: Steve, You lost me. >there would not be an entry in any of the cells in row 11. (A11 and D11 ... | |
| Calculating time interval to include blank rows | 3/1/2011 |
| Q: Sir, I am using Excel 2007 and have worksheet for recording my working, driving, and rest hours as a ... A: Steve You can find the last filled cell in the range E1:E10 with with this formula ... | |
| various a | 3/1/2011 |
| Q: please find the file for easy reference in sheet 2 i want the various detials linke to the name ... A: Mukesh on sheet2 assume the letter C appears in cell C3 You can find where the letter entered in ... | |
| count multiple occurences | 3/1/2011 |
| Q: I need a help on a matter in Excel... I have a 2000 entries in column with 15 numbers each, what I ... A: Sasa so assuming the first number is in A1, then the only modification to my macro would be Sub ... | |
| count multiple occurences | 3/1/2011 |
| Q: I need a help on a matter in Excel... I have a 2000 entries in column with 15 numbers each, what I ... A: Sasa, >I have a 2000 entries in column with 15 numbers each I don't really understand how your data ... | |
| Finding more than One Value | 3/1/2011 |
| Q: I have following table A 10 B 20 C 50 D 30 A 60 B 80 A 50 C 40 A 20 B 10 I want all Value of 'A' ... A: Rajiv, Assume you data is in A2:B11 in another location ... | |
| Excel requirement | 2/28/2011 |
| Q: I have a problem and I don´t know solve it. I need to change the color of a cell based in the text ... A: Arthur you would use a formula for the format condition for cell A1 You would have two conditions: ... | |
| Macro Modification | 2/28/2011 |
| Q: I am using Excel 2003. I have a macro that I've been using but cannot figure out how to get it to ... A: Brian, If your workbook is 27MB, then I suspect you either have a lot of data or your usedrange is ... | |
| Macro Modification | 2/28/2011 |
| Q: I am using Excel 2003. I have a macro that I've been using but cannot figure out how to get it to ... A: Brian, I have modified it to search mulitple pages. Just modify the list of sheet names you want ... | |
| formula query | 2/27/2011 |
| Q: I have a query I hope you may be able to help me with, I have hit a dead end. I am trying to figure ... A: Andy, say you have 1 in L1, 2 in L3 down to L26 in M1 put in this formula ... | |
| Formatting a cell to show distinct lines | 2/26/2011 |
| Q: This is probably stupid and really simplistic (nothing like the usual questions you respond to), and ... A: Jana, type in Psych101 hit Alt+Enter then continue typing Rm. 102 and hit alt + Enter and so ... | |
| Overwrite existing workbook | 2/25/2011 |
| Q: I have two questions for you today. The code I am using for this appears at the end of the ... A: Bill Application.DisplayAlerts = False bk.SaveAs filename:=sName, Fileformat:=xlWorkbookNormal ... | |
| How to: Optimum Cash Drawer Calculator | 2/25/2011 |
| Q: I am brainstorming how to create an excel 2007 work sheet that can calculate the optimum combination ... A: Thatcher, send me a workbook with a worksheet set the way you want it (where the user puts in the ... | |
| Accuracy difference b/w goal seeker and Solver | 2/25/2011 |
| Q: I am trying to solve a non-linear equation with only one variable. What I did is putting all the ... A: Bob, to the best of my knowledge, accuracy of goal seek is controlled in the calculation tab in ... | |
| Macros Conditional Formatting Question | 2/25/2011 |
| Q: I am working on an Excel 2003 spreadsheet, columns D and E particularly. Column D has spouse first ... A: Amanda, there was a typo on that line. An extra right parentheses Here is the correction. Sub ... | |
| Macros Conditional Formatting Question | 2/25/2011 |
| Q: I am working on an Excel 2003 spreadsheet, columns D and E particularly. Column D has spouse first ... A: Amanda, I assume you know how to use macros. here is what I would propose: Sub colorCells() Dim r ... | |
| List of values in Excel 2003 | 2/25/2011 |
| Q: Could I just ask what you mean by setting references - see below: 'This *REQUIRES* VBAProject ... A: Gail, In the visual basic editor in Excel (alt + F11), with your workbook as the active project, go ... | |
| Excel Restrictions | 2/24/2011 |
| Q: I am trying to recommend a solution for a customer. They have an excel book to control financial ... A: Angel Resendiz, I am not sure of the real intent of your question. Here are some answers. excel ... | |
| VBA | 2/24/2011 |
| Q: I need to find all possible combinations of 4 numbers, running from 0-100, but in multiples of 10 ... A: Carmen, I assume you want a macro and that you know how to run macros. If so, paste the below code ... | |
| List of numbers in excel 2003 | 2/24/2011 |
| Q: I have been given a really tedious task to do every month - it involves matching up receipts of ... A: Gail, as a matter of fact, Harlan Grove has written code to do what you describe Copy the code ... | |
| VBA if then macro for conditional NUMBER format | 2/24/2011 |
| Q: I'm not sure if completed all the steps to submit my original question, so please pardon if this is ... A: Cassaundra, Note that I have edited these macros for the following reason. I thought your ... | |
| how to link validation criteria to another criteria? | 2/23/2011 |
| Q: my criteria in column A are countries, column B the criteria are provinces. How can I link the ... A: Jojit, Your question is hard to follow, but I think you are talking about using the list option in ... | |
| importing data from txt file to excel | 2/22/2011 |
| Q: I want to import 3 lines of data after word DATE from notepad file to excel sheet2. Notepad file is ... A: Navin, Private Sub Workbook_Open() Dim LineofText As String Dim v(1 To 20) As String Dim idex As ... | |
| Paste Special to other workbook with macro | 2/22/2011 |
| Q: I have two questions for you today. The code I am using for this appears at the end of the ... A: Bill, there is a command Application.CalculateBeforeSave you could set this to false at the top of ... | |
| Paste Special to other workbook with macro | 2/22/2011 |
| Q: I have two questions for you today. The code I am using for this appears at the end of the ... A: Bill, Here is the revision (two lines modified are marked) this assumes the filenames in the next ... | |
| Paste Special to other workbook with macro | 2/22/2011 |
| Q: I have two questions for you today. The code I am using for this appears at the end of the ... A: Bill, No, that won't work. The reason is that bk refers to Summary Template.xls when do it the ... | |
| Paste Special to other workbook with macro | 2/22/2011 |
| Q: I have two questions for you today. The code I am using for this appears at the end of the ... A: Bill I assume that you want to save the workbook in the same location as Summary Template.xls I ... | |
| Macro for copying sheet | 2/22/2011 |
| Q: I have around 13-14 tabs in my excel workbook. I want a macro which could directly copy the tab and ... A: Farist sub Makefiles() Dim sh as worksheet, sPath as String sPath = "D:faris\1011\individual ... | |
| importing data from txt file to excel | 2/22/2011 |
| Q: I want to import 3 lines of data after word DATE from notepad file to excel sheet2. Notepad file is ... A: Naveen, very difficult to write such code without a file to test with. While your data looks like ... | |
| In producing bar chart, how to show weekly dates in chart | 2/21/2011 |
| Q: I use 2007 excell. I have a simple spreadsheets with 4 subject of information that will simply ... A: Debbie, I created a stacked bar chart using your data, (I changed 2/18 and 2/15 so they actually ... | |
| last non empty cell | 2/21/2011 |
| Q: I'm using excel 2010, and would like how I can display the contents of the last non-empty cell in a ... A: Peter, this worked for me if I understand the requirement correctly: K30: ... | |
| importing data from txt file to excel | 2/21/2011 |
| Q: I want to import 3 lines of data after word DATE from notepad file to excel sheet2. Notepad file is ... A: Navin, Hard to know the exact structure of your file, but this would be my best guess on how to do ... | |
| Question in Excel | 2/21/2011 |
| Q: I have 2 seperate files with a huge list of data.Here i would like to add a column from first sheet ... A: Firdose, Yes, you can use vlookup or a combination or Index and match. It depends on where the ... | |
| Create Dynamic Text List From Column Featuring Blanks | 2/20/2011 |
| Q: Using Excel 2007 I'm creating a spreadsheet with a rows of Customer details (one row for each ... A: David, your situation isn't totally clear to me. I believe I understand what you want, but any ... | |
| 3D Array multiple sheets Excel 2002/03 | 2/20/2011 |
| Q: this is urgently required at work please could you help.. Trying to use IF function with 3d array ... A: Tom K. You already asked something like this in a follow-up and I answered it. Here is the answer ... | |
| Look up formula | 2/20/2011 |
| Q: i am attempting to set up a work book that can be dynamically updated Ideally i would like it if ... A: Samual ferguson, I can't make out the details of your sheets - they are too small in your picture. ... | |
| Optimization Macro | 2/19/2011 |
| Q: I had problem with the below macro, This Macro is detecting the duplication in invoices. The problem ... A: Hamdy, Added: also, note in the original answer I did add code to restrict the macro to only work ... | |
| Linking Information from several excel sheets to one master sheet | 2/18/2011 |
| Q: I need to link several excel files with relative information to one master sheet. Whenever the ... A: Shane, put in a link to the sheet ='C:\myfolder\myfiles\[Myworkbook.xls]Data Sheet 1'!A1 would be ... | |
| Protecting workbook | 2/18/2011 |
| Q: My home Excel has Microsoft 2007 and at work the version is 2003. At home I created the following ... A: Art, as long as you have a named range "pw" in the activeworkbook when you rn the code, it should ... | |
| Sum of totals between a time range | 2/18/2011 |
| Q: I have a list of data that I want to create outputs from. It has times and I want to add the totals ... A: Nick, you can use sumif for times as well ... | |
| Counting unique text values in sumproducts | 2/18/2011 |
| Q: I need help with counting unique text elements in an sumproduct formula. Basically I have four ... A: Joe, Sorry - I missed that requirement. Here is how you would alter your formula: ... | |
| Counting unique text values in sumproducts | 2/17/2011 |
| Q: I need help with counting unique text elements in an sumproduct formula. Basically I have four ... A: Joe, =SUM(IF(FREQUENCY(IF(LEN(B2:B20)>0,MATCH(B2:B20,B2:B20,0),""), ... | |
| Macro Question | 2/17/2011 |
| Q: Reason For Downtime Times % of Total Cumulative % Maintenance(cell O91) 2 14.29 ... A: Carlo Graffeo, the change event fires when you edit a cell. It now appears that you will not be ... | |
| Macro Question | 2/17/2011 |
| Q: Reason For Downtime Times % of Total Cumulative % Maintenance(cell O91) 2 14.29 ... A: Carlo Graffeo, delete the previous macro. It was in the selectionchange event by mistake. It ... | |
| Macro Question | 2/17/2011 |
| Q: Reason For Downtime Times % of Total Cumulative % Maintenance(cell O91) 2 14.29 ... A: Carlo Graffeo Make a copy of your workbook go to the copy. right click on the sheet tab where you ... | |
| formula for text | 2/17/2011 |
| Q: How to write a formula if A1+B1 = say 10+10 = 20 but if A1 contain text then NA. i.e. A1+B1 = NA A: Nabam, if you try to add A1+B1 and neither are empty, then you will get an error if they don't both ... | |
| Using Macros instead of Formulas | 2/17/2011 |
| Q: I have a rather large spreadsheet that I am using to reference a large data dump in a second ... A: Cat when you say for an entire column, your sample fomrula shows two cells being concatentated. So ... | |
| non blank cell | 2/16/2011 |
| Q: How to find "SECOND LAST" non blank cell in range A1:A50 A: sahil If you want to know what row it is in: =LARGE(IF(A1:A50<>"",ROW(A1:A50)),2) this is an array ... | |
| 3D Array multiple sheets Excel 2002/03 | 2/15/2011 |
| Q: this is urgently required at work please could you help.. Trying to use IF function with 3d array ... A: Tom K, the reason the single sheet example works is because are using implicit intersection. So ... | |
| excel 2007 | 2/15/2011 |
| Q: is it possible to have excel 07 populate a cell and then once there is data in that cell move to the ... A: Lance Assume you put an activeX Commandbutton on your sheet and it is named Commandbutton1. Right ... | |
| Excel | 2/15/2011 |
| Q: What formula do I use to count the total number of words in a a few cells A1 A2 A3? Thanks for you ... A: Syd, I am not sure I understand the question. a) don't use a range that contains empty cells b) ... | |
| Excel | 2/15/2011 |
| Q: What formula do I use to count the total number of words in a a few cells A1 A2 A3? Thanks for you ... A: Syd, TRIM should do that. Try this solution: ... | |
| Excel | 2/15/2011 |
| Q: What formula do I use to count the total number of words in a a few cells A1 A2 A3? Thanks for you ... A: Syd, =len(A1)-len(Substitute(A1," ",""))+1 If you wanted to count all 3 cells at once you could ... | |
| VBA Sort | 2/14/2011 |
| Q: I am trying to create VBA code for the following sort situation. I need to sort four columns that ... A: Dave, assume the data is in A1:D100 and there are headers in column 1 Range("A1:D100").Sort ... | |
| Average Colum that excludes Bold cells | 2/14/2011 |
| Q: Excel 2003. I am trying to create a function or maybe develop some VBA code that will allow me to ... A: Robert do Ctrl+Alt+F9 also you can make the function volatile, but that can cause it to ... | |
| Matching columns | 2/14/2011 |
| Q: I'm trying to match data from 2 columns with 2 other columns. Column A is an Invoice # and Column B ... A: Matt, Notice the formula is enclosed in curly brackets in the picture {} when you select your ... | |
| Excel Help | 2/14/2011 |
| Q: I'm James, 18 and I need help on excel. I am currently designing an excel file for me to manage my ... A: James, I would suspect your data in the table is not sorted ascending on bill number. If that is ... | |
| excel transfering data | 2/14/2011 |
| Q: I have a little problem with moving data in excel from one file to another,In one table i have data ... A: Ognjan Despot, the only additional information I see in your enlarged picture (and it is still not ... | |
| excel transfering data | 2/13/2011 |
| Q: I have a little problem with moving data in excel from one file to another,In one table i have data ... A: Ognjan Despot, I can't read your picture. The text is so small I can't read it. I think I get the ... | |
| excel sorting | 2/13/2011 |
| Q: I am trying to sort column A by ascending number order: 1,2,3,4,etc. Instead it is sorting as ... A: Jodie, see the revision at the bottom: your cells don't contain numbers. They contain text values ... | |
| Matching columns | 2/13/2011 |
| Q: I'm trying to match data from 2 columns with 2 other columns. Column A is an Invoice # and Column B ... A: Matt in E2: ... | |
| Excel Help | 2/13/2011 |
| Q: I'm James, 18 and I need help on excel. I am currently designing an excel file for me to manage my ... A: James, If you have Excel 2007 or later, you can use the sumif function assume in your data sheet ... | |
| Excel capabilities | 2/12/2011 |
| Q: Tom, Is there a way to move a range of row cells according to a percentage that changes with data ... A: Scott, you can use the small and large functions to find the most home runs - but that would mean ... | |
| Excel capabilities | 2/12/2011 |
| Q: Tom, Is there a way to move a range of row cells according to a percentage that changes with data ... A: Scott, If you mean automatically, not directly. Generally to achieve what I understand you to ... | |
| Excel | 2/12/2011 |
| Q: When working with an Excel spreadsheet I used a color to cover all unused cells. When I discovered ... A: Scott, Excel has a setting called usedrange which specified the rectangular area of cells that ... | |
| VBA Macro to do VLOOKUP (Excel 2007) | 2/11/2011 |
| Q: I have been given a group of spreadsheets and each one is a specific part number, along with all the ... A: Mich, >group of spreadsheets and each one is a specific part number that is pretty ambiguous. In ... | |
| text to integer conversion | 2/11/2011 |
| Q: Need a small help! If Cell A1 has some string in it.. example: "HELLO" and i have assigned each ... A: Sirajuddin, every character is actually stored as a number. This is usually called its ascii code. ... | |
| Followup question | 2/11/2011 |
| Q: OOps! I could not make it work. The code you provided at this link ... A: Nabam, the code was tested and worked fine for me. You said nothing about merged cells which ... | |
| Locking Rows | 2/10/2011 |
| Q: I'm using Excel 2007. I'm looking for a way to lock rows together for sorting purposes. I have a ... A: Terri, there is no setting or anything like that. I usually use a dummy column and reproduce the ... | |
| Average Colum that excludes Bold cells | 2/10/2011 |
| Q: Excel 2003. I am trying to create a function or maybe develop some VBA code that will allow me to ... A: Robert, I am not aware of any FontBold function I wrote one: Public Function FontBold(r As Range) ... | |
| Advanced CountIf for Resource forcasting | 2/10/2011 |
| Q: "Forecast" sheet has Equipment, Location, Warranty Start and Warranty End columns(10 different kinds ... A: Steve, OK, but that doesn't answer the question of how you count the warranty equipments in a month ... | |
| excel and notepad saveas .xls | 2/10/2011 |
| Q: Long time no "hear". How are You? I am facing a small problem, hope You can help me out. I can ... A: attis, You can't manipulate notepad with automation. If you can't just open the .xml file in ... | |
| Advanced CountIf for Resource forcasting | 2/10/2011 |
| Q: "Forecast" sheet has Equipment, Location, Warranty Start and Warranty End columns(10 different kinds ... A: Steve, what are your rules for having equipment under warranty on a month by month basis. A month ... | |
| Worksheet_Change macro for formula | 2/10/2011 |
| Q: Please provide me a Worksheet_Change macro for the following conditions: If a cell in Col. C ... A: Nabam, You say less than 100 and greater than 100, but not what happens when it is equal to 100. I ... | |
| Excel 2010 | 2/9/2011 |
| Q: I create a lot of basic purchase order sheets for my small business. I normally use a code to ... A: Jon, in another sheet you would need a table of all your abbreviations. It is unclear if our ... | |
| assign multiple values to multiple options in a column | 2/9/2011 |
| Q: I need to assign score to multiple options of the same answer by different respondents. The question ... A: Swati, if you have a number in A2, then in B2 you could have ... | |
| Autopopulating a cell with a date based on entries in other cells | 2/9/2011 |
| Q: I am trying to have Excel automatically enter a date in a column, based on when that particular row ... A: Megan Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range For Each Cell In ... | |
| Formula Autofill | 2/8/2011 |
| Q: I'm trying to combine data of different sheets into one new sheet. I'm trying to do so by using a ... A: martin, A2: =Indirect("Sheet1!A"&(row(A1)-1)*4+1) then drag fill down the column A2 should have ... | |
| Inventory item codes | 2/8/2011 |
| Q: using Excel version 2007 Right now our inventory item codes in Quickbooks are a hodge podge. I ... A: Jeff, you can use columns to the right of your data to pull out characters. for example, if you ... | |
| Vlookup & ? | 2/8/2011 |
| Q: I have a table I need to fill, which using vlookup I can do but in the instance where the lookup ... A: Shaun, assume sheet2 is where you are looking up your data. assume on sheet2 you have your two ... | |
| Time Conversions | 2/8/2011 |
| Q: Tom, Got a quickI have a column on my worksheet that hold time values in seconds. I need to ... A: Shawn, Excel times are stored as a fraction of a day. So 6 hours would be 6/24 = .25 so ... | |
| Excel 2003 VBA Multiple Selection List Box to Fileter Pivot Fields | 2/7/2011 |
| Q: I have one worksheet with several charts from pivot tables that are hidden on the same worksheet. ... A: Chris, Excel 2003 doesn't support multiple selections in pagefields - which appears to be what your ... | |
| Excel formula to track inventory prices by day? | 2/7/2011 |
| Q: I am working on an excel database to track inventory prices by day. I have 8 items in my inventory ... A: Erica, Assume your Price Reference Tab has a sheet name of "Price" Daily Inventory Tab a2: Date ... | |
| How to mask the last two digits of a cell | 2/7/2011 |
| Q: I have a cell that has from 0 to 6 numbers in it. I need to mask/delete the last two numbers, then ... A: Mike, Only a macro would be able to change your cell to hold the values you display however, you ... | |
| VBA - Excel 2003 | 2/7/2011 |
| Q: Email: I have created a pop up box for the user to input information, however when the information ... A: Nicole, you either have to ignore the error with (I will use your original code to illustrate - ... | |
| VBA - Excel 2003 | 2/7/2011 |
| Q: Email: I have created a pop up box for the user to input information, however when the information ... A: Nicole ActiveWorkbook.Application.Goto Reference:="R4C6" ActiveCell.FormulaR1C1 = txtToDate should ... | |
| GPA | 2/6/2011 |
| Q: I was wondering how to lookup a GPA when I type in a Percentage. E5:F31 are examples only, I can't ... A: Polo, since your interpolating, I would put this formula in F2 f2: ... | |
| Excel | 2/6/2011 |
| Q: I am attempting to calculate some end of the year information. Using my spreadsheet, I need to ... A: Diane, that is the email where I received notification of your/this follow-up question. it is ... | |
| Excel | 2/6/2011 |
| Q: I am attempting to calculate some end of the year information. Using my spreadsheet, I need to ... A: Diane, Are you familiar with Pivot Tables? A pivot table will give you the flexibility to get ... | |
| if | 2/6/2011 |
| Q: ,,i need your help in translating this words into excel language (if statement),, on column a ... A: aezzat, You haven't said where the expenses are located that you want to sum. So I will assume ... | |
| date related | 2/5/2011 |
| Q: Sir, In an excel sheet 5 fields are there ... What I would like to do is to subtract two dates i.e. ... A: Anu, as previously suggested I used Datedif and it gave me the results you show in the top part of ... | |
| Percentage | 2/5/2011 |
| Q: Please help me to create a formula for following conditions in cell M5: if J5=G5 then show 100% if ... A: Nabam, ... | |
| Copy variable rolls of data | 2/3/2011 |
| Q: I am trying to copy a variable roll and paste the contents in another part of the sheet. To begin ... A: Art, It looks like you want to select 9 contiguous cells in the same row starting in column B. ... | |
| excel date | 2/2/2011 |
| Q: Sir, In my excel sheet, at column A field name at row 1 is DOB (<- Date of Birth DD/MM/YYYY) and at ... A: Anu, you can do this with the Datedif formula. See Chip Pearson's page for a thorough explanation ... | |
| Question about Using xlUp and xlDown | 2/2/2011 |
| Q: Tom, You have been a great help in the past explaining how things work, so I'm hoping you can help ... A: Shawn, in my answer I said: >as long as you have data in F14 or in F14 and below, it should not ... | |
| Question about Using xlUp and xlDown | 2/1/2011 |
| Q: Tom, You have been a great help in the past explaining how things work, so I'm hoping you can help ... A: Shawn, see this line set r = .range("F14",.cells(.rows.count,"F").End(xlup)) it starts in F14. ... | |
| excel VB | 2/1/2011 |
| Q: Sir, Is VB really difficult ? How should I do programs like you ? Should I able to get on line ... A: Anu, >Is VB really difficult ? that is such an open ended question, it really doesn't have an ... | |
| Question about Using xlUp and xlDown | 2/1/2011 |
| Q: Tom, You have been a great help in the past explaining how things work, so I'm hoping you can help ... A: Shawn, This should do what you want: Dim r as Range with worksheets("Detail Report") set r = ... | |
| Always create backup | 1/30/2011 |
| Q: I have a macro VBA file that performs a scheduled backup every 14 days. The backup command function ... A: Art, I would expect your code is making the change. If your code contains the line ... | |
| match text in 2 columns, insert T-F in third | 1/30/2011 |
| Q: In Excel 2007, I have 2 columns of data, like this: 6a 8h 93c 33k 3b 6a 88z 81x 57e 2y ... A: Paul, In C1 put in =if(countif($B:$B,$A1)>0,True,False) then drag fill down column C (or copy ... | |
| excel vba | 1/30/2011 |
| Q: 1)I wanna firstly convert 12-15 text files into one excel one by one. It means all text files should ... A: Kushal, that code should have been Private Sub CommandButton2_Click() Dim r as Range, rw as Long ... | |
| Auto Insert/Delete rows and update last rows, as also set printer area update | 1/30/2011 |
| Q: I'm trying to achieve the above and thereby learn more about VB (but for the time constraints to get ... A: Tom Katz If you want to find the the last cell in your table stargin in cell A3 (change the code to ... | |
| excel vba | 1/30/2011 |
| Q: 1)I wanna firstly convert 12-15 text files into one excel one by one. It means all text files should ... A: Kushal, This is my best guess at what you want. No guarantees. If you open one of these files in ... | |
| reg. Alram in excel | 1/30/2011 |
| Q: I have heard a lot about you ... I have an excel sheet where a few dates are mentioned in a ... A: Ananya, well, it depends on how you want to be reminded. You must open the workbook for any ... | |
| Modification in VBA Code | 1/30/2011 |
| Q: Hope you are fine and keeping good health,hope u had nice vacation, u helped me a lot before, i am ... A: Abdul jaleel, I put in code to pick up the cc address - but I see that I overlooked changing one ... | |
| Modification in VBA Code | 1/29/2011 |
| Q: Hope you are fine and keeping good health,hope u had nice vacation, u helped me a lot before, i am ... A: Here is my best guess at what you want: >with which filtering is done. it is sheet FilterExample ... | |
| Auto Insert/Delete rows and update last rows, as also set printer area update | 1/29/2011 |
| Q: I'm trying to achieve the above and thereby learn more about VB (but for the time constraints to get ... A: Tom K, So this is your question? >is this possible please? I would think some or most of it is ... | |
| Excel bonus question 2 | 1/18/2011 |
| Q: Sorry, for not explaining it well previously. In a given calendar quarter, I'm trying to create an ... A: Curt, if the monthly values are in A1:A3, then your total bonus would be calculated by ... | |
| Count with 2 Criteria- Excel 2007 | 1/17/2011 |
| Q: I have 2 sheets. On the first one, there are nearly 100k records of businesses, including the ... A: Jewel, the only reason I would see you getting #N/A in that formula is if there is a #N/A in column ... | |
| INDEX linking to a sharepoint hosted .xls taking URL 'parts' from other cells. | 1/17/2011 |
| Q: I'm currently trying to pull together the results from several .xlsx files in a folder on my ... A: Andy, the good news first: the Indirect function will build a cell reference such as you describe ... | |
| multipage in excel 2007 | 1/17/2011 |
| Q: if you are building a form with multipage, what is the maximum amount of pages could you get on the ... A: Winner, I haven't seen any published limit. (Many limits in excel are expressed as limited by ... | |
| IF Function | 1/17/2011 |
| Q: I have a list of names e.g. James Liam Mike Lisa Kelly Rose what formula do i need in order to ... A: Maj, How about "Pat" - boy or girl? There is nothing distinctive in a boy's name or a girls ... | |
| Convert Excel 2007 column numbers to column letters | 1/16/2011 |
| Q: What I am trying to do is quite simple: in the VBA environment, I want to code the simplest way to ... A: Fabricio, Public Function ColLetter(colNumber As Long) ColLetter = "" On Error Resume Next ... | |
| Excel VBA Array Sum Help | 1/15/2011 |
| Q: i got a data like this (All Numeric) A B C D E F Row1 1 2 3 4 5 6 Row2 2 3 4 5 5 7 Row3 3 4 5 6 7 8 ... A: AQUA, this code should execute instantaneously. Looping through an array is blindingly fast - so I ... | |
| Trying to create a variable formula | 1/14/2011 |
| Q: .. I have been trying to create a "variable" formula but I don't know if this is possible or valid. ... A: Robert, =Indirect(G15&"!A1") the problem here is that A1 is a string and if you drag fill this ... | |
| Excel VBA Help | 1/12/2011 |
| Q: Q1.i want to set dynamic range like this StrArr=Array("","Str1","Str2","Str3") for i=1 to 3 ... A: Aqua This worked for me as an illustration of what I understand you to want. Sub abc() Dim strArr ... | |
| Pulling Dynamic Top Five List | 1/10/2011 |
| Q: So I have a simple list of our top donors (column headings are Region, Name, Expected $$). I also ... A: Sandy, the below, entered as an array formula and drag filled down - worked for me for the problem ... | |
| Application.Workbooks.Open in Excel 2003 | 1/10/2011 |
| Q: RE: Application.Workbooks.Open in Excel 2003 Hi Tom, I have a workbook that collects and stores ... A: Shane, Here is what I think you want: Sub Tester15() Dim wsh As Object, sID As String Dim ... | |
| identifying month change in excel | 1/9/2011 |
| Q: I have data for prices for the last 4 years. Only for working dates. I want to identify when there ... A: Jose, Your request lacks definition on how you want to implement your requirement - so I will give ... | |
| Colouring cells | 1/9/2011 |
| Q: I have Excel 2003 version, and I want to colour each cell in a long list in column E the same colour ... A: Gail, Here is a macro. I assume you know how to use and run macros since you asked for a macro (a ... | |
| Sum Single Column in 2 dimensional array | 1/7/2011 |
| Q: Tom, MyArray is a 2 dimensional array in Excel with 12 columns (one for each month) and 100 rows ... A: Steve, there are no VBA functions that will do anything special with arrays. You pretty much have ... | |
| Excel 2003 | 1/6/2011 |
| Q: I have a list of about 2000 names (lets call it list X) in column A and another list of about 1000 ... A: In D1 put in this formula =if(countif(A:A,A1)>0,B1,na()) in E1 put in this formula ... | |
| Error Handling in VBA | 1/5/2011 |
| Q: I'm having a problem with the following statement. I would like to make sure, that if a user does ... A: Kuba, maybe something like this: Sub RandomDate() 'On Error Resume Next Dim x As Date, y As Date, ... | |
| Pulling Dynamic Top Five List | 1/4/2011 |
| Q: So I have a simple list of our top donors (column headings are Region, Name, Expected $$). I also ... A: Sandy, you would need an array formula assume your data is in columns A, B and C of sheet1 ... | |
| Summing across multiple worksheets | 1/2/2011 |
| Q: In Excel 2003 I designed an "Operator Scheduler" workbook which has separate worksheets for each ... A: Robert, If I had to do what I understand you to describe, I would put in two sheets in the workbook. ... | |
| Counting non-adjacent cells in a row | 1/2/2011 |
| Q: Tom, I want to count the number of non-adjacent cells in rows that have a value greater than zero. ... A: Gary, enter this as an array formula ... | |
| renaming files | 1/1/2011 |
| Q: Using macros I am able to rename and relocate my file with the following command: Name ... A: Art, revised - here is a revision - I missed that B5 would only hold xyz, not the entire new name ... | |
| Easy Way to Combine All WorkBooks In certain order Into Single WorkBook | 12/31/2010 |
| Q: You mentioned that "...By the way, if you group the chart and the source data and copy or move it ... A: Newbie-san, sheets.copy copies all sheets to a new workbook set bk = Activeworkbook set bk2 = ... | |
| Color Palette | 12/31/2010 |
| Q: I'm trying to change my default color palette in excel 2003. I tried adding a spreadsheet to my ... A: Tim, I created a Book.xlt and saved it in the xlstart folder and it worked fine for me. I couldn't ... | |
| Color Palette | 12/31/2010 |
| Q: I'm trying to change my default color palette in excel 2003. I tried adding a spreadsheet to my ... A: Tim, If you save a workbook called Book.xlt and place it in a startup folder location, that ... | |
| Extract 4 digit number from a text cell | 12/31/2010 |
| Q: I have a long list of wines with an entry in each case in this format and in the "D" column: [Cos ... A: Gregory, Assume the first entry is in D1 then ... | |
| Summing columns within filtered list | 12/30/2010 |
| Q: I have a worksheet with a header row containing 1000 customer records. I have applied an auto ... A: Dave This worked for me: Sub ABC() Dim sh1 As Worksheet, sh2 As Worksheet Dim r As Range, r1 As ... | |
| Display weekday followed by calendar date | 12/30/2010 |
| Q: Tom, I want to display the current day of the week followed by the calendar date today in one cell. ... A: Gary, Weekday just returns a number between 1 and 7 inclusive. You can get what you want with this ... | |
| summing non-adjacent columns based on conditions | 12/30/2010 |
| Q: I have a business that send bills each month and receives payments each month. Sometimes we receive ... A: Gary, this worked for me ... | |
| Excel formula | 12/30/2010 |
| Q: I have copied the table I am using to work on the formula. You see one column with ID and another ... A: Ana, =max(if($A$2:$A$500=1,$B$2:$B$500) entered with Ctrl+shift+enter (rather than just enter ... | |
| Find "." within text for each cell by using macro | 12/30/2010 |
| Q: Tom, I have data such as 404C050900.3640.3000 and I need to vlookup a text that is before dot "." ... A: Hamdy, .Find(".", Cells(cell.Row, "J").Text should be Application.Find(".", Cells(cell.Row, ... | |
| Format condition | 12/30/2010 |
| Q: I should have clarify my questions, I need a macro that can help me to do the following: D E ... A: Hamdy, put this in the sheet module associated with sheet Invoice Private Sub ... | |
| Format condition | 12/30/2010 |
| Q: I should have clarify my questions, I need a macro that can help me to do the following: D E ... A: Hamdy, This is how I would see it. I am just coloring the cell - not applying conditional ... | |
| Match and find values in Excel | 12/30/2010 |
| Q: I have two excel documents. In one of them I have patients with their unique ID and cycle numbers ... A: Ana, sice you don't give specific information that would be needed to write formulas, I will give ... | |
| Excel | 12/30/2010 |
| Q: Please help me on how to make an statement out of these conditions 5 – Exceeded target (more than ... A: Reynon, I assume you want to return the numbers you show (1 through 5) based on percent ... | |
| Removing Links from Chart | 12/29/2010 |
| Q: You mentioned that "...By the way, if you group the chart and the source data and copy or move it ... A: Newbie-san Ron de Bruin has written some very good code that handles a variety of these type ... | |
| if lookup formula | 12/29/2010 |
| Q: Mr. Ogilvy, I'm trying to create a formula that will read a value from a ranking. I have a drop down ... A: Travis, I could probably do something for you, but not based on just what you have told me. I ... | |
| Modification of Macro to Reference Subfolders | 12/29/2010 |
| Q: I am successfully using the following code in Excel 2003 to open all .xls files in a folder and ... A: Andrew, Paste all this in a new module. Run the "Start" macro. < revision - removed directory ... | |
| Sumproduct incorpating two criteria involving same references | 12/23/2010 |
| Q: Just a follow up question from the last solution you posted. I'm trying to count the number of ... A: Mike, the only thing I see wrong is in the check for < 0.95, you are looking at column AL instead of ... | |
| VBA Copy Multiple Workbooks to one Sheet | 12/22/2010 |
| Q: There are multiple workbooks with one sheet each in a directory. All sheets within each workbook ... A: Mike, Sub ABC() Dim sPath As String, sName As String Dim bk As Workbook, r As Range Dim r1 As ... | |
| VBA Copy Multiple Workbooks to one Sheet | 12/22/2010 |
| Q: There are multiple workbooks with one sheet each in a directory. All sheets within each workbook ... A: Mike, Set r1 = sh.Cells(sh.Rows.Count, 1).End(xlUp)(1) should be Set r1 = sh.Cells(sh.Rows.Count, ... | |
| Specific value returns a date & time | 12/22/2010 |
| Q: In my spreadsheet I want to get the date & time when that particular invoice is paid. For example in ... A: Rikin, must have been a typo. "Else If" should be one word The below is tested and worked for me. ... | |
| Time Sheet calculation in Excel 2003 | 12/22/2010 |
| Q: "Hi Tom, I intend to creat a time sheet in MS Excel 2003 for my staff as follows: Start Time End ... A: Kiran, it depends on how you want to enter the time. What you show would suggest text strings. So ... | |
| Macro to import | 12/21/2010 |
| Q: I would like a macro which imports a table from another workbook to a specific portion of a ... A: Bill here is a code snippet that should get you started. Dim r as Range, r1 as Range, bk as ... | |
| Macro To Open Workbook In Default Path | 12/21/2010 |
| Q: Tom OS: MS XP Pro Excel: 2003 I have ( 2 ) two workbooks in the same directory. One workbook ... A: Kenneth, I too set up two workbooks. One was named WorkbookA.xls. The second was Named ... | |
| Specific value returns a date & time | 12/21/2010 |
| Q: In my spreadsheet I want to get the date & time when that particular invoice is paid. For example in ... A: Rikin, apologies for the column = 5. I was answering another question that dealt with column E and ... | |
| Specific value returns a date & time | 12/21/2010 |
| Q: In my spreadsheet I want to get the date & time when that particular invoice is paid. For example in ... A: So I am guessing you want that if you select any cell in column L and you select Paid from the ... | |
| Macro to save the selected part of a sheet | 12/21/2010 |
| Q: Tom, I don't even have any starter code for this because I don't know how to do it. I want ... A: Bill, Assume the named range is workbook level and located in the activeworkbook >I want the macro ... | |
| Macro to hide & unhide columns. | 12/20/2010 |
| Q: Tom, Macro-minimind-me has a 'recorded' macro whose purpose is the hide and unhide a specific ... A: Bill this will toggle the hidden status of column E Sub ToggleColumnE() ... | |
| Remove duplicate cells from range | 12/20/2010 |
| Q: Tom, I have a macros that creates a Range of cells named rng. I then use the set rng=rng.entirerow ... A: Steve, this worked for me Sub abcd() Dim rng As Range, r As Range, rw As Range Set rng = ... | |
| Detecting filter in VBA | 12/20/2010 |
| Q: This is Bill, you've answered questions expertly for me before, which is why I keep coming back to ... A: Bill, first, I have never heard of the clearfilters command. When you say a table, is it actually ... | |
| Macro To Open Workbook In Default Path | 12/20/2010 |
| Q: Tom OS: MS XP Pro Excel: 2003 I have ( 2 ) two workbooks in the same directory. One workbook ... A: Kenneth, I believe this is what you want: Private Sub Workbook_Open() Dim sPath As String ... | |
| Macro two logic events | 12/19/2010 |
| Q: I have tried to write the below macro but I got massage of "Run-time error '424': Object required". ... A: Hamdy, If that is your actual code, you misspelled the word application in every case. That would ... | |
| Macro does not work properly if sheet isn't active | 12/19/2010 |
| Q: I hope you're fine. The below macro does work properly when make Sheet1 active by clicking sheet1, ... A: Hamdy Looking a the code: With Worksheets("Sheet1") res2 = .Cells(cell.Row, "M").Value + _ ... | |
| Contains | 12/18/2010 |
| Q: I've figured out how to determine if a cell contains specific text. What I'm trying to do is come ... A: Jeff, wouldn't it be If(b2=False =if(b2="false",a2,Substitute(UPPER(a2),"AT ","")) and for A1 ... | |
| VBA-Running Macro from Personal.xlsb in different worksheets | 12/18/2010 |
| Q: I have a macro stored in my Personal.xlsb file, and try to run this macro in a different workbook. ... A: Justin, It sounds like the macro is designed to work on the workbook that contains the macro. Does ... | |
| Returning a single value based on multiple criteria | 12/18/2010 |
| Q: Tom, I want to lookup and return the value from an array to a separate sheet after satisfying ... A: David, the easiest way to do this is with a Pivot Table. However, that will not account for the ... | |
| SUMPRODUCT function with #DIV/0! result | 12/17/2010 |
| Q: I'm trying to count the number of entires (row of information) that matches the following 3 ... A: Mike, the easiest fix it so fix the formula that shows #DIV/0! instead of have =R13/S13 change ... | |
| Calculation of Tax | 12/17/2010 |
| Q: What formular can I use excel to calculate the tax of an employee supposing his taxable income is ... A: Augustine, Assume the taxable income is in cell I4, this formula ... | |
| VBA Function | 12/17/2010 |
| Q: I am need helping in setting up a formula/function that has a large number of variations. I believe ... A: Chase, Sounds like you have a data problem. Perhaps in Data in column A of sheet Data you have 3 ... | |
| VBA Function | 12/17/2010 |
| Q: I am need helping in setting up a formula/function that has a large number of variations. I believe ... A: Chase, the traditional way to do this would be to dedicate a sheet to hold you data "column A" ... | |
| Resizing a Range to Contain the entire row | 12/16/2010 |
| Q: Tom, I created a range (Rng) that contains three cells such that msgbox Rng.Address = $H$4,$H$6, ... A: steve set rng = rng.EntireRow here is a demonstration from the immediate window in the Visual ... | |
| hyperlinks | 12/16/2010 |
| Q: In excel 2007, I need a way to edit multiple hyperlinks. Basically I have a column with the desired ... A: Dustin, Yes. There is a Hyperlink worksheet function HYPERLINK(link_location,friendly_name) ... | |
| Subtracting Time in Excel | 12/16/2010 |
| Q: I have two times that I need to subtract, for instance I need to subtract 11/30/10 8:00 from ... A: Tawnie, >I know that excel will not show a negative amount of time, but I need it to do so so you ... | |
| Calculate retirement date | 12/16/2010 |
| Q: I want your help to calculate the retirement date. The rule is that the retirement will be effected ... A: this formula does what you describe: ... | |
| Checking login and logout times during 30 minute intervals | 12/16/2010 |
| Q: I'm hoping you're going to be able to help with this. I have attached an image of the excel file. ... A: Greg, I see the picture, but I am not sure I understand the question. Do you want to build the ... | |
| How to check data during entry | 12/15/2010 |
| Q: While entering data in excel can allow to enter only certain values? For example; in a column i ... A: Sumanta Neupane, This capability is provided by Data => Data Validation (in the menus, go to data, ... | |
| nested Vlookups and Ifs | 12/15/2010 |
| Q: I have a spreadsheet which tracks individual's annual leave, and I need to use this to create a ... A: Sarah, I have attached an image showing a formula that does what you describe. I suggest you have ... | |
| Hex data formating in Excel. | 12/15/2010 |
| Q: I am reading bulk amount of data from a txt file, which contains hexadecimal numbers in the ... A: Anil, Sub ReadHexfile() Dim sPath As String, sName As String Dim line As String, rw As Long, icol ... | |
| Lookup with Blank Spaces Question | 12/14/2010 |
| Q: I am trying to do a lookup, using a string on one sheet (Sheet1), and looking for the same string on ... A: Tom Lets start with the easiest fix =INDEX(INDIRECT("'" & $C$1 & "'!" & ... | |
| VBA printing in Excel 2003 | 12/14/2010 |
| Q: I have this page in my workbook, there is a column of names. On the same page is a form. One name ... A: Ken using a number in the current context would not work unless you wanted to add a loop counter ... | |
| Macro does not work properly if sheet isn't active | 12/14/2010 |
| Q: I hope you're fine. The below macro does work properly when make Sheet1 active by clicking sheet1, ... A: Hamdy, if you qualify the cells reference as shown below, then it should work. Sub ABC_Q4() Dim ... | |
| Inventory | 12/14/2010 |
| Q: Tom, Thank you for the quick response. I am thinking that I will simplify my approach. I looked for ... A: if the total is in the same cell on each sheet you can use 3D references =Sum(Start:End!Z21) where ... | |
| Matching Data on One Excel Sheet with Another | 12/13/2010 |
| Q: I need to bump a list of serial numbers on one excel sheet with a list of serial numbers from ... A: If you have excel 2010 or later or you are willing to create a defined name you can do that. ... | |
| vba time format | 12/13/2010 |
| Q: Tom I have the following listbox code that works fine apart from displaying the time values in the ... A: Alan, demo'd from the immediate window: ? TimeValue("00:00:01") 12:00:01 AM ? ... | |
| VBA printing in Excel 2003 | 12/13/2010 |
| Q: I have this page in my workbook, there is a column of names. On the same page is a form. One name ... A: Ken, No, the list and the "form" can be on the same worksheet. You would just need to set up your ... | |
| Looking up multiple values on excel | 12/13/2010 |
| Q: I'm lookng for a function that can look up 3 values across columns (the date, location, and location ... A: if it is a unique combination then Values being sought M1: date N1: location O1: location activity ... | |
| VBA printing in Excel 2003 | 12/12/2010 |
| Q: I have this page in my workbook, there is a column of names. On the same page is a form. One name ... A: Ken, Don't know what you did, but you code is all screwed up with some type of HTML formatting I ... | |
| Split Function | 12/10/2010 |
| Q: Tom, I am using the split function to parse a cell value into multiple strings. The range may ... A: Steve, I am sure you are familiar with the immediate window. I will demonstrate that split worked ... | |
| Sum | 12/10/2010 |
| Q: I am starting with the example data A B 100.00 1-Aug 200.00 2-Aug 240.00 3-Aug 230.00 4-Aug ... A: Reena, I am confused by what you say. I put your data in a worksheet starting with 100.00 in A2 ... | |
| Creating an excel macro with IF functions | 12/10/2010 |
| Q: I'm actually asking for a follow up. I did not receive the usual email confirmation & answer this ... A: Irma, this version of the code worked as I expected given your further guidance: Sub ProcessData() ... | |
| Creating an excel macro with IF functions | 12/9/2010 |
| Q: I would like to create a macro by writing it's VBA code for the example data below: A B ... A: Irma, from you example, it looks like all the dx entries are in column C and if I put under in ... | |
| Excel formula | 12/9/2010 |
| Q: In column C a person could enter a number -2,-1,0,1,2. In column D I want to multiply the value in ... A: Ron, Say in D2 you put in the formula =if(C2="","",if(C2<0,C2*75,0*C2)) of course 0 times C2 ... | |
| Dividing Time | 12/9/2010 |
| Q: I am trying to find the average work time for a project. There is 3 hours, 42 minutes, and 51 ... A: a1: 3:42:51 a2: =(A1/57)+time(0,1,51) so when I divide 3 hours, 42 minutes and 51 seconds by 57 I ... | |
| Referencing value contained in another cell withing a formula | 12/9/2010 |
| Q: Is there a way to reference a date in a cell when using DATEVALUE in a formula? For example in the ... A: A1 has the string/Text 1-Dec-10 then =SUM(IF('2010'!$A$2:$A$5400=DATEVALUE(A1), ... | |
| REMOVE DUPLICATES | 12/8/2010 |
| Q: My remove duplicates is a bit different from all the others I have seen. I have Office 2007 Service ... A: WENDY, If I understand the question correctly, I would go to column O or the next blank column. ... | |
| Macro: Change sheets when > 0 | 12/7/2010 |
| Q: I have a template in Excel 2007 for which I need a macro, I think. I would like the workbook to ... A: Greg, Right click on the sheet tab and select view code. Put in a procedure like this Private Sub ... | |
| excel | 12/7/2010 |
| Q: My name is John and i want to know how can i find the cells that have the same values in a worksheet ... A: I have attached an image showing the formula I copied out of your email applied to a worksheet to ... | |
| code to submit data from a survey | 12/7/2010 |
| Q: Hey Tom, I used some code you had suggested that would use a Submit button to copy responses from ... A: Shelby, I can't reproduce that. I selected a cell, I selected all cells in the list and each time ... | |
| code to submit data from a survey | 12/6/2010 |
| Q: Hey Tom, I used some code you had suggested that would use a Submit button to copy responses from ... A: Shelby, I just tested it again and it worked fine for me. It did all the things you say it doesn't ... | |
| Get a random sample | 12/6/2010 |
| Q: Wow! I can't believe it when I run the macro it really worked you are really amazing, but I have ... A: Jn, when I tested the code previously, I had the same problem because I had not changed the formula ... | |
| Get a random sample | 12/6/2010 |
| Q: I have a worksheet with an Analyst name in it from 1 to 10...I want to get a random sample among ... A: JN, I believe I have made all the changes you requested: My tests confirmed that the code ... | |
| VBA Excel - Repeat forumla on multiple cells | 12/6/2010 |
| Q: Just looking for a bit of assistance with performing the same operation on multiple cells using vba. ... A: Phil, Dim cell as Range for each cell in Range("G14:G21,I14:I21,B25:B28,D25:D28") if cell.Value > ... | |
| excel | 12/4/2010 |
| Q: My name is John and i want to know how can i find the cells that have the same values in a worksheet ... A: --------< revised which feature to use >------ Select the rectangular area of cells that you want ... | |
| 2003: find email addresses | 12/4/2010 |
| Q: I have a file with about 300 rows and 14 columns. One of the columns has a teacher name. For each ... A: MS, Other than the vague description of the table, not much I can tell you. If you have excel ... | |
| Macro help | 12/4/2010 |
| Q: I have a table. In this table I want to delete the rows that have data. If a row has data in all ... A: assume you have headers in row 1 > In this table I want to delete the rows that have data. If a ... | |
| Get a random sample | 12/4/2010 |
| Q: I have a worksheet with an Analyst name in it from 1 to 10...I want to get a random sample among ... A: Jn I assume Analyst A as shown in your sample size specification above is in cell A1, the 2 is in ... | |
| Get a random sample | 12/3/2010 |
| Q: I have a worksheet with an Analyst name in it from 1 to 10...I want to get a random sample among ... A: Jn, this worked for me. Change the value assigned to the variable "n" to how many samples that ... | |
| Category on X -axis of XY scatter graph | 12/3/2010 |
| Q: //peltiertech.com/ but I can't get it to work by following their instructions. I can use category ... A: Mike, Mike can you send me a sample workbook with your data and the chart as it is now. Also in ... | |
| VBA link to specific worksheet | 12/3/2010 |
| Q: I'm trying to alter a Macro which exports data from outlook to excel. Would you happen to know if ... A: Stuart, Here is a revision that will work if you add Folders to the list of folders in the array: ... | |
| VBA link to specific worksheet | 12/3/2010 |
| Q: I'm trying to alter a Macro which exports data from outlook to excel. Would you happen to know if ... A: Stuart, I ran it from Outlook 2007 and it worked fine for me - so that wouldn't be consistent with ... | |
| VBA link to specific worksheet | 12/3/2010 |
| Q: I'm trying to alter a Macro which exports data from outlook to excel. Would you happen to know if ... A: Stuart, I assume these folders are not in your public folders. ---<Revision>-- forgot to comment ... | |
| Worksheet and Codename handling | 12/3/2010 |
| Q: Tom, I have a worksheet with a worksheet CodeName=wksDatabase and worksheet name="Database". (shows ... A: Steve, I have a workbook with a sheet with a tab name of "AAA" and the codename is "Sheet1" Here ... | |
| VBA to save files with different format | 12/2/2010 |
| Q: I have a folder with a number of files, some of which are .xls, some of which are .xlsx, and some of ... A: Justin, I like to use the Old Dir command rather than use the scripting commands Sub Modifyfiles() ... | |
| VBA link to specific worksheet | 12/2/2010 |
| Q: I'm trying to alter a Macro which exports data from outlook to excel. Would you happen to know if ... A: Stuart, It depends on what you are doing. If you are going to open the file, then you have to open ... | |
| Dependent dropdown list | 12/2/2010 |
| Q: I am trying to fix my issue already for quite a long time but cannot solve it. I arrived on this ... A: Jan, If you want to send me a sample workbook with the dropdown boxes already on sheet Overview and ... | |
| Compare 2 unsorted & unequal columns | 12/2/2010 |
| Q: I am trying to automate a task in a reconciliation report. the user runs a report that returns 5 ... A: Crystal, this worked for me: Sub MarkData() Dim sh As Worksheet Dim rA As Range, rB As Range Dim ... | |
| lookup data in a table | 12/1/2010 |
| Q: I have what I believe must be an easyI have a large table of data in which I would like to search ... A: James, I couldn't duplicate your problem. But I am not sure why you are using Lookup if you are ... | |
| Data analysis | 12/1/2010 |
| Q: My question is, I have an array of data as shown in the attached picture Example1. I want to be able ... A: John, this is the way I would approach it. Sub SrchArr() Dim r As Range, cell As Range, r1 As ... | |
| VBA - Rows to Columns | 12/1/2010 |
| Q: Tried to post on AllExperts but got a “Too many follow-ups, please ask a new question” error. ... A: Phil, You would have to explain the output you want. On the sheet you sent me qty has merged cells ... | |
| VBA - Rows to Columns | 12/1/2010 |
| Q: Looking for a bit of assistance with transposing some data. I am importing CSV files which have ... A: Phil, OK, got the additional information I will incorporate this information. and send you ... | |
| VBA - Rows to Columns | 12/1/2010 |
| Q: Looking for a bit of assistance with transposing some data. I am importing CSV files which have ... A: Phil, Received the file. So the file will already be in a workbook when you want it processed? ... | |
| Check Boxes in a Form | 11/30/2010 |
| Q: I have created a Form in Excel 2007, and have a number of check boxes to answer an array of ... A: Scott, Private Sub CheckBox1_Click() If CheckBox1.Value = True Then CheckBox2.Visible = True ... | |
| use formatting more times | 11/30/2010 |
| Q: Sir, On an excel sheet normally we can use the conditional formatting 3 times. But is there any way ... A: Udyad, I assume by three times, I assume you mean three conditions for any individual cell. In ... | |
| Check Boxes in a Form | 11/30/2010 |
| Q: I have created a Form in Excel 2007, and have a number of check boxes to answer an array of ... A: Scott, what do you mean by form? Do you mean a worksheet set up to look like a paper form or do you ... | |
| copy data from .dbf files | 11/30/2010 |
| Q: Sir, I have a file stands with dbr1931.dbf (Foxpro / Visual Foxpro files). Each time I need the ... A: Udyad, Instead of asking for a date, why not just have you select a folder with the mouse from a ... | |
| Identify number ranges | 11/30/2010 |
| Q: I have one column that contains multiple number ranges. I need to find the beginning and ending ... A: Tony, that is a bit vague. When you say beginning and ending numbers, it sound like you want the ... | |
| ROUND UP to nearest 5 | 11/30/2010 |
| Q: I have some selling prices, which may end up as: €1,425 €388 €1,651 €1,323 €110 I want them ... A: Andy, I found you question in the Question pool so whomever you asked apparently placed it there. I ... | |
| Excel 2000 IF function | 11/30/2010 |
| Q: I'm trying to use Excel 2000 to write the words Adult Services in a box if the box in the next ... A: Gary, I guess by box you mean cell? Assume if cell A1 holds any of the values -1, 0, or 1, you ... | |
| Excel - Protecting SOME cells | 11/29/2010 |
| Q: how do you only protect SOME cells in Excel? ANSWER: Mariah, Cells are either locked or not ... A: Mariah As I said, all cells are locked by default. If you open a new workbook, then all the cells ... | |
| Excel - Protecting SOME cells | 11/29/2010 |
| Q: how do you only protect SOME cells in Excel? A: Mariah, Cells are either locked or not locked. this setting is pretty muche meaningless until you ... | |
| How to add in VBA Word | 11/28/2010 |
| Q: I am trying to create a userform in Word using VBA. I have 4 yes/no questions, giving a total of 8 ... A: Justin, sorry - I was confused and thought you were going to have this userform in Excel now. I ... | |
| How to add in VBA Word | 11/28/2010 |
| Q: I am trying to create a userform in Word using VBA. I have 4 yes/no questions, giving a total of 8 ... A: Jason, assume you have a worksheet with scores in column B (some type of identifier in column A) ... | |
| How to add in VBA Word | 11/28/2010 |
| Q: I am trying to create a userform in Word using VBA. I have 4 yes/no questions, giving a total of 8 ... A: Justin, Let's assume that checkbox1 and checkbox2 are paired with checkbox1 being Yes checkbox3 ... | |
| Converting written month into number month | 11/27/2010 |
| Q: .. I work on cemetery transcriptions where sometimes a date is coded 10 Jan 1855 in 3 separate ... A: Robert, If the cell only contains a number, then the macro should do nothing since it is in the ... | |
| Converting written month into number month | 11/27/2010 |
| Q: .. I work on cemetery transcriptions where sometimes a date is coded 10 Jan 1855 in 3 separate ... A: Robert, Select the cells you want to convert and then run this macro Sub ... | |
| excel question | 11/27/2010 |
| Q: I found your answer to Yuri's question on 8/24/06 most helpful. It was the formula to read/skip ... A: Mark, So if each cell in A1 had its cell address such as A1 A2 A3 A4 A5 then in column C C1: ... | |
| Combinations | 11/27/2010 |
| Q: Using excel 2007 - I want a complete list of combinations of three variables, color, shape and size ... A: Ann Are you looking for a macro to generate the combinations? Sub GenerateCombinations() Dim v1, ... | |
| VBA Shell Function | 11/25/2010 |
| Q: I wrote some code to open a software using Shell function.Every time I open the workbook it asks if ... A: ROBERTO, without knowing anything about that program, I would assume it has some type of visible ... | |
| Creating a car racing game with VBA | 11/25/2010 |
| Q: I am a year 9 student constructing a car racing game in excel using VBA and i need some help. There ... A: Amy You can have two cells selected, but only one of the cells will be active. You can move the ... | |
| Time Conversion of minutes to hours | 11/24/2010 |
| Q: Im looking for a simple formula to convert minutes into hours. When I'm dividing minutes / 60 im ... A: Sirajuddin, what exactly do you have in each cell. What formula are you using. 700 minutes is ... | |
| Excel-Toolbars | 11/23/2010 |
| Q: I use Mac 2004 Excel and have always used three toolbars at the top, Standard, Formatting, and the ... A: Lindsey. Sorry, I don't use a Macintosh - so I don't have first hand experience on this issue. (but ... | |
| counting unique and duplicate records with variables/filters from other columns | 11/23/2010 |
| Q: I’m using Excel 2003, SP3 I’m trying to do a juvenile justice recidivism study Entire worksheet ... A: Dave, all this could easily be done using a Pivot Table. A pivot table is primarily used for ... | |
| Excel- Display Date range | 11/23/2010 |
| Q: I'm a novice working on a payroll time sheet. I want to display a pay period of two weeks as a ... A: Caleb if you put a starting date in say A1, then =TEXT(A1,"m/d/yyyy")&"-"&TEXT(A1+13,"m/d/yyyy") ... | |
| Pivot table filter automation | 11/23/2010 |
| Q: Sorry I was unclear in my last message, let me try and explain myself a little better. I have a ... A: Kurt, in almost every version of excel, they have changed the object model for the pivot table. It ... | |
| VBA | 11/23/2010 |
| Q: If I have a link typed into a cell as text, how can I automate the link being opened in a ... A: Jason, Not totally clear what you are asking, but if you have a URL in in cell A1 of sheet1 With ... | |
| Find & replace data from two different worksheets | 11/22/2010 |
| Q: I am trying to get my head around VBA and would appreciate your advice on the best way of achieving ... A: Paul, > return a new value back to O1, O2 & O3 in sheet1 You mean O1, P1, Q1? -- correction - I ... | |
| Excel Macro | 11/22/2010 |
| Q: I'm looking to create a macro that searches a column (first 1000 rows) for blanks; if a blank is ... A: you don't care where the blank cell is in the row. I assume if the cell above is blank, then you ... | |
| rank function | 11/22/2010 |
| Q: While using the rank function in an excel sheet some positions/ranks skips. For ex. the entries are ... A: See the attached picture (I revised my answer to explore another possiblity. Hopefully the picture ... | |
| drop-down with filter-like options | 11/22/2010 |
| Q: I have a large list of data where a there are a lot of duplicates in certain columns. I am building ... A: James, You can go to your new page where you will have the list of unique items. This is ... | |
| rank function | 11/21/2010 |
| Q: While using the rank function in an excel sheet some positions/ranks skips. For ex. the entries are ... A: UP I tested the formula with your data. It worked fine for me. It sounds like you have ... | |
| conditional copy for value only | 11/21/2010 |
| Q: i need a macro that copy and then paste value only. action1 if"sheet1 A1"is copythis,copy "sheet1 ... A: ck Sub copydata() Dim sh1 As Worksheet, sh2 As Worksheet Dim sh3 As Worksheet, i As Variant Set sh1 ... | |
| conditional copy 2 data from different cell | 11/21/2010 |
| Q: i need a macro copy 2 data from different cell in same sheet and paste in different place. ... A: ck, Sub copydata() Dim sh1 as worksheet, sh2 as worksheet Dim sh3 as worksheet, i as long set sh1 = ... | |
| Excel | 11/20/2010 |
| Q: I am trying to add information in one column based on the answer, if no information is in the ... A: Diane, first, I have no idea what you mean by 09:021 I will assume you meant the letter O and 9 and ... | |
| rank function | 11/20/2010 |
| Q: While using the rank function in an excel sheet some positions/ranks skips. For ex. the entries are ... A: UP, you have a tie for 1st. 100 and 100. so it gives them both a rank of 1 and skips the 2nd ... | |
| conditional copy 2 data from different cell | 11/20/2010 |
| Q: i need a macro copy 2 data from different cell in same sheet and paste in different place. ... A: ck, I assume you will run this macro; i.e. You don't want it to be an event macro. Sub copydata() ... | |
| Excel formula | 11/20/2010 |
| Q: I'm very new to Excel but have been a .NET programmer for 10 years. I have been given an assignment ... A: If cell L3 = "Special Handling" or "Public Sector CBN" or _ "Get CBN" or "US Service ... | |
| conditional copy | 11/20/2010 |
| Q: i need a macro that copy a data based on the change of random generator RANDBETWEEN(0,1) function ... A: ck, I understood you to say you had formulas in column A that produced numbers. this selects all ... | |
| conditional copy | 11/20/2010 |
| Q: i need a macro that copy a data based on the change of random generator RANDBETWEEN(0,1) function ... A: ck, in a general module (insert=>module in the Visual Basic Editor) put in this code Sub copydata() ... | |
| CountIF | 11/19/2010 |
| Q: I am making a risk assessment spread sheet where Solders are asked a questions and then respond "Y" ... A: Dawn, In you lastest post, Your rules are inconsistent with each other and inconsistent with what ... | |
| CountIF | 11/19/2010 |
| Q: I am making a risk assessment spread sheet where Solders are asked a questions and then respond "Y" ... A: Dawn, for purposes of illustration, I assume the "y" responses will be in column D in rows 5 to ... | |
| SHOW MISSING NUMBERS IN EXCEL 2003 SPREADSHEET | 11/19/2010 |
| Q: I have a spread sheet with a thousand rows. These rows are numbered sequentially by part number. ... A: Cheryl Add an additional sheet. On the new sheet, put the lowest numbered part number in A1 and ... | |
| Ratio conversion | 11/19/2010 |
| Q: I am trying create a if - then ratio conversion for a dollar-weighted risk weighting, such as "If ... A: James, I found this in the question pool where it was placed for whatever reason by whomever you ... | |
| VBA - switch sheets between | 11/19/2010 |
| Q: Tom, please, help me with my macro issue. My macro doesn't work as I want, because when I am going ... A: Martin, if you commented out that line, then the old worksheet should still be visible. this ... | |
| VBA - compare all values in two column | 11/19/2010 |
| Q: I would like to ask you for help. I am trying to find some values from my source sheet in target ... A: Lucy, so you want to compare say C2 to column Y and D2 to column Z and they must match cells in ... | |
| VBA - switch sheets between | 11/19/2010 |
| Q: Tom, please, help me with my macro issue. My macro doesn't work as I want, because when I am going ... A: Martin This assumes the button is not on a worksheet. if it is on a worksheet, then you can't ... | |
| VBA - compare all values in two column | 11/19/2010 |
| Q: I would like to ask you for help. I am trying to find some values from my source sheet in target ... A: Lucy, VBA would be overkill for this. You can do it with a simple formula in A2 of Madox ... | |
| Excel Macros - Copy sheet | 11/18/2010 |
| Q: I have a master sheet (Sheet1) that has predefined headings, with one formula, columns A through K. ... A: David, you can replace Sheet1 with just the number 1 Sub ABC() Dim sh as worksheet, sh1 as ... | |
| Excel Macros - Copy sheet | 11/18/2010 |
| Q: I have a master sheet (Sheet1) that has predefined headings, with one formula, columns A through K. ... A: David, If you already have a macro to create the daily sheets, I am not sure what you are exactly ... | |
| VBA - worksheet change | 11/17/2010 |
| Q: Tom, I would like to ask you for help. What I would like to get with my macro - when I insert the ... A: Martin, On thing you have to be careful of is that you event does not fire when your code changes a ... | |
| VBA - worksheet change | 11/17/2010 |
| Q: Tom, I would like to ask you for help. What I would like to get with my macro - when I insert the ... A: Martin so on sheet Budget, if someone makes an entry in the Range AL10 to AL50, then in column AZ ... | |
| Excel progressive drop down lists | 11/17/2010 |
| Q: I would like to create a project calculator to assist in my work. I know how to create a drop down ... A: Neeti, I can refer you to a web site that documents how to create dependent dropdowns. Debra ... | |
| Excel | 11/16/2010 |
| Q: Mori, I would be utmost thankful if you could advise me with my problem ? I run a café in Norway. ... A: Itai, Looks like Mr. Mori, for whatever reason, relegated your question to the question pool where I ... | |
| Excel % Inc/Dec with a Zero value | 11/15/2010 |
| Q: I need the % increase or decrease for prior year to day to current year to date however when I enter ... A: Jessica you can't calculate a % increase or decrease from zero. > I get a true increase in my % ... | |
| Formatting | 11/15/2010 |
| Q: i.e. remove all the dashes. I tried text to columns and then combining the three columns but the ... A: Edith, You didn't ask me this question, but I found it in the question pool. Excel doesn't normally ... | |
| transform number to letter | 11/15/2010 |
| Q: My name is John and I'm writing you from Greece. My question might look a bit bizarre to you. I have ... A: Follow the below link and download the MoreFuction Addin from that and run the setup. ... | |
| Macro/VBA | 11/15/2010 |
| Q: I am trying to record a Macro to organize data for me, including creating Pivot Tables. The data I ... A: Laura, you can use code like this to determine the number of rows lastrow = ... | |
| Some excel macro button images not available to be displayed on Quick access tool bar | 11/15/2010 |
| Q: I want to add some macro button images on the quick access toolbar which is not available when i try ... A: Athul, If I understand what you want, Ron de Bruin documents how to do this: ... | |
| VBA - data validation problem | 11/14/2010 |
| Q: Tom, sorry for a previous data mismatch. The first section was discussed with you some month ago. ... A: Martin to do what you say, if it isn't "FC-POSUXXX" then do nothing you would use ElseIf jj = 11 ... | |
| Distribute Data from Master sheet to other sheets | 11/14/2010 |
| Q: Can you help me in a simple code. I have prepared a simple master task sheet putting the name of ... A: Raghave, I would say create pivot tables based on the master sheet on the individual sheets and ... | |
| cell value to footer | 11/12/2010 |
| Q: I am back to You again. I hope You can help me out this trouble too. I have one excel file (a kind ... A: attis, In the code you show, you do Set sh = wbCodeBook.Worksheets("Evaluation Sheet") ... | |
| Search Formula | 11/11/2010 |
| Q: Hope you're doing good. I have below data in Sheet2 Drilling Contractor Rig Name Weatherford Rig04 ... A: Mohan, If in A1 of sheet1 you type in Weatherford, then in B1 put in the formula ... | |
| lookup with Excel | 11/11/2010 |
| Q: =VLOOKUP("black",$D$4:$E$37,2,FALSE) Providing "black" can be found in the columns D4 to E37 the ... A: Eric, You need to trap for the error first. This worked for me: ... | |
| DDE link in Excel with macro to work with ThinkORSwim | 11/11/2010 |
| Q: I use ThinkORSwim (ToS) as my stock trading platform. They provide a DDE link so I may stream data ... A: Ninh, you can use the setlinkonData property of the range to assign a macro to fire/execute when ... | |
| adding time sheet hours | 11/10/2010 |
| Q: BREAK TIME TIME HOURS (hrs) IN OUT 1:00 7:45 AM 5:00 PM 8:15:00 1:00 8:00 AM 5:30 ... A: Reuben, time is stored as a fraction of a day. 42 hours and 15 minutes is stored as 42.25/24 = ... | |
| Counting Congruent Entries. | 11/10/2010 |
| Q: It has been a long time since I have sought help here. Last time, your answer was of great help and ... A: Sujan Sanju, ... | |
| Match Value and paste row on different sheet | 11/9/2010 |
| Q: I hope you are well.I have data on two sheets. I am looking to write a macro which can match value ... A: Mohammed, Sorry for the late response. Allexperts kept failing on me today when I tried to answer ... | |
| cell value to footer | 11/9/2010 |
| Q: I am back to You again. I hope You can help me out this trouble too. I have one excel file (a kind ... A: attis, your code doesn't make much sense to me. You want to change the footer in the "Evaluation ... | |
| VBA - extention of macro (error dump of different values) | 11/8/2010 |
| Q: Tom, I would like to ask you for help. I have a macro that was discussed/prepared by you. As you can ... A: Marek, your picture was a little hard to read, but this appears to do what you describe/show. Sub ... | |
| VBA to perform calculation | 11/8/2010 |
| Q: You had recently helped me with a VBA program that would automatically add sheets to a workbook. The ... A: BJ, yes the code can be modified to add the formulas in your summary sheets when the sheets are ... | |
| Reset Excel Workbook | 11/8/2010 |
| Q: I'm looking for VBA code for a macro which will return the open Excel workbook to it's last saved ... A: V Not sure what your level of experience is with macros or what help you are looking for. I can ... | |
| TextBox Loop | 11/8/2010 |
| Q: Tom, I have the following procedure. Private Sub UserForm_Activate() TextBox1.Text = ... A: Alan, Private Sub UserForm_Activate() Dim i As Long For i = 1 To 8 Me.Controls("Textbox" & ... | |
| Excel - One click Copy and Paste | 11/8/2010 |
| Q: I want to be able to click a command button, that once clicked "retains" cetain text for me on ... A: Paul, Chip Pearson has a page that lays out the information you need to do what I understand you ... | |
| Interest rate formula | 11/7/2010 |
| Q: Tom, there are a host of financial formulas in Excel, but I can't seem to find the exact one I need. ... A: Brett, I used a sample to calculate the future value. Then I used the PV, FV and number of periods ... | |
| Interest rate formula | 11/7/2010 |
| Q: Tom, there are a host of financial formulas in Excel, but I can't seem to find the exact one I need. ... A: Brett, take a look at this articule to make sure we are talking about the same things: ... | |
| Search and then Copy & Paste Formula | 11/7/2010 |
| Q: I have a large excel database and am looking for a formula that will help me to search for duplicate ... A: Dawn, with no specifics, the best I can do is refer you to a comprehensive discussion of working ... | |
| VBA - how to count several values in xls and display a result | 11/6/2010 |
| Q: I would like to ask you for help. I am a VBA beginner and try to load several values in xls colmumn ... A: Marek, Sub ABC() Dim v2010(1 To 12) Dim v2011(1 To 12) Dim r As Range, cell As Range Dim mnth As ... | |
| VBA - msgbox in case of some vaule are not same | 11/6/2010 |
| Q: I would like to ask you for help. I need to display a msgbox in case of two cells are not same. The ... A: Martin, you can get the date of end of the current month with ... | |
| Pivot table | 11/5/2010 |
| Q: I have a table of data in which each row represents a date-stamped record for a login failure for ... A: David, why not send me a sample data set with the above explanation and what version of excel you ... | |
| Charting in Excel 2007 | 11/5/2010 |
| Q: I am trying to create a chart using data from 2 separate spreadsheets. My first spreadsheet has ... A: Robert, I used an XY scatter plot. I put the plot in one of the workbooks. In the same workbook ... | |
| VBA - how to count several values in xls and display a result | 11/5/2010 |
| Q: I would like to ask you for help. I am a VBA beginner and try to load several values in xls colmumn ... A: Marek, this worked for me: I assume the data you show starts in A2 with headers in the first row. ... | |
| acending decenting | 11/5/2010 |
| Q: In A1 cell i type the ''c'' letter. In B1 i type the ''a'' letter, and in the C1 the ''b'' letter. ... A: John, Excel supports sorting left to right although you have to select this option specifically ... | |
| Convert Incorrect Date Format | 11/5/2010 |
| Q: I need to convert incorrectly fomatted date in column A to properly coded date in column b. Date is ... A: Joe, If you used my exact formula, the it should work. If it doesn't it would be an indication ... | |
| Convert Incorrect Date Format | 11/5/2010 |
| Q: I need to convert incorrectly fomatted date in column A to properly coded date in column b. Date is ... A: Joe, assume the first date is in A1. In b1 ... | |
| Marcos to seperate different names | 11/5/2010 |
| Q: I'm relatively new to the world of excel having just required a job has an intern in a top ... A: Mike, would it be possible to do with macros. I would think it would be possible but not by using ... | |
| Counting Congruent Entries. | 11/5/2010 |
| Q: It has been a long time since I have sought help here. Last time, your answer was of great help and ... A: Sujan Sanju, ... | |
| Deleting all characters before or after a number | 11/4/2010 |
| Q: hope you can help me. I'm trying find a way to separate a single text string into two columns. ... A: Mike, if all names will be two words then assume the first name is in A1. In B1 ... | |
| Counting Congruent Entries. | 11/4/2010 |
| Q: It has been a long time since I have sought help here. Last time, your answer was of great help and ... A: Sujan Sanju You data is a mangled up in this post. When I say the notice in my email, it lined up ... | |
| excel macro | 11/3/2010 |
| Q: In my column K I have a bunch of reference numbers. Some of them are listed as follows: 1RP4-5RP4 ... A: Dan, this worked as I designed it to work. Hopefully that is consistent with what you want. ... | |
| excel macro | 11/3/2010 |
| Q: I have a table in excel with a bunch of part numbers in column A, and "find numbers" in column C. ... A: dan This worked for me. At least it worked as I designed it to work. Option Explicit Sub ABC() ... | |
| VBA - open dbf file and save as xls | 11/3/2010 |
| Q: I have several named .dbf files which are downloaded to a folder each month. The names are always ... A: Laureen, I assume you are in Excel 2003 or earlier - since I don't believe Excel 2007 and later ... | |
| VBA to | 11/3/2010 |
| Q: I've been trying to "adjust" a vba script I found on Google to fit my needs but so far have had no ... A: Graham, try this revision which worked for me Private Sub Worksheet_Change(ByVal Target As Range) ... | |
| How to make a macro go past the "do you want to save this file" message | 11/3/2010 |
| Q: I have written a macro that opens and closes many excel files, but for some files, I get the "do you ... A: Gail, you can tell it in the command ActiveWorkbook.Close SaveChanges:=False adapt that to the ... | |
| VBA to "clear" associated cells when data input in column S | 11/3/2010 |
| Q: I've been trying to "adjust" a vba script I found on Google to fit my needs but so far have had no ... A: Graham, right click on the sheet tab and select View Code. Put in the code below in the resulting ... | |
| Automatically Copying Rows With A Particular Attribute To A Different Worksheet | 11/2/2010 |
| Q: Since I do not have a detailed knowledge on many aspects of excel (especially on how to create ... A: Scg right click on the sheet tab with your data and choose view code. Put in code like this: ... | |
| Extraction from payroll | 11/2/2010 |
| Q: I have prepared a payroll and want to extract individual payslips for each employee. How do I go ... A: Augustine, Based on what you show, my opinion does not change that this can be done with Vlookup. ... | |
| Lookup Question | 11/1/2010 |
| Q: Here is a small sample of the data I have A04-1 A08-1 A08-2 A12-1 ... A: I put the table of data you show in A1 and the value to lookup in G1 I used this formula ... | |
| merge (add) data from several rows into one. | 11/1/2010 |
| Q: Data set in excel: deptA employee1 hours-worked. If employee and dept are the same, i want to end ... A: George After you apply the subtotal and have the results, you can select your data (or all your data ... | |
| Followup to Previous | 11/1/2010 |
| Q: I tried to post a followup to your response, but apparently there is a limit. So I am having to post ... A: BJ, Your code doesn't incorporate the previous code I provided. So I will provide it again and ... | |
| Excel 2007 - VBA question | 11/1/2010 |
| Q: Tom, I am working a sheet in excel which I can use to keep track of my daily deliveries on a ... A: BJ, sorry for the late response. I was having trouble posting an answer in allexperts - don't know ... | |
| FileName as a parameter causing Run-time 1004 error | 11/1/2010 |
| Q: I've inherited the following code, which fails in Sub 'Save_File' at the line asterixed. The error ... A: Mark, You have four routines that call routines that call Save_File. Each of the routines that ... | |
| Excel 2007 - VBA question | 11/1/2010 |
| Q: Tom, I am working a sheet in excel which I can use to keep track of my daily deliveries on a ... A: BJ, >I pasted this code into my worksheet the workbook_Open event must be in the Thisworkbook ... | |
| Excel 2007 - VBA question | 11/1/2010 |
| Q: Tom, I am working a sheet in excel which I can use to keep track of my daily deliveries on a ... A: BJ Private Sub Workbook_Open() Dim New_Sheet_Name As String Dim sh As Worksheet, sh1 as Worksheet ... | |
| Excel filtering/pivot tabling? | 10/31/2010 |
| Q: I'm stuck trying to filter the results from a workbook. Essentially, for each project, I want to ... A: David Say your data is in A2:C13 E1: 1 E2: 2 E3: 3 E4: 4 in F1 put in = ... | |
| Better use of Excel | 10/31/2010 |
| Q: I have a small "one man" business. I have always used excel, but on very basis applications, ie; ... A: Lin, there are a lot of free excel templates on the internet. Microsoft has a template gallery at ... | |
| Reconciliation Problem | 10/30/2010 |
| Q: Is it possible to write some code to make excel take reference from a user input cell and then look ... A: Copy the code below (written by Harlan Grove) into a code module, and set the references as ... | |
| merge (add) data from several rows into one. | 10/29/2010 |
| Q: Data set in excel: deptA employee1 hours-worked. If employee and dept are the same, i want to end ... A: George, A macro is one way to approach it. But you have other options. You can select the data ... | |
| problem with adding a conditional format programmatically | 10/29/2010 |
| Q: I am running into an issue while trying to add a conditional format to a spreadsheet. The format is ... A: Ellis, You said you are doing it with VBA code - correct. conditional formatting is entered ... | |
| Header value in a variable | 10/29/2010 |
| Q: I would like to place only the text of the Left Header in a variable. What it does is it places the ... A: Daniel, There is no built in attribute/property that will give you just the text string that would ... | |
| Problem with hyperlinks after running Macro | 10/29/2010 |
| Q: I've written several macros to create an error reporting database in Excel. Users enter the ... A: Chris, Beyond telling me you run a macro, you haven't told me what you are doing - so I can't tell ... | |
| Excel | 10/28/2010 |
| Q: Here I am again. Please help me, and here are my data. A1 B1 C1 ... A: Victorino B1: =LEFT(A1,FIND(", ",A1)-1) C1: =MID(SUBSTITUTE(A1,B1&", ... | |
| Copying text from ALL ActiveX textboxes to a new worksheet | 10/27/2010 |
| Q: I have an excel file with 10 worksheets. Each worksheet has about 5 to 10 activex textboxes. How do ... A: Reeko assume the summary sheet is named summary (location to place the data) Sub copydata() Dim sh ... | |
| Function Help. | 10/25/2010 |
| Q: "Hi, so I'm doing this project and I'm stuck on a part of it because I don't know which function to ... A: Megan, You can use sumif if you use a separate range for your scores. Sumif can only work with ... | |
| Function Help. | 10/25/2010 |
| Q: "Hi, so I'm doing this project and I'm stuck on a part of it because I don't know which function to ... A: Megan, in the cell on the same row where you want the total, for row 3 put in this formula ... | |
| Excel charting | 10/24/2010 |
| Q: I'm using Excel 2003 (Standard Edition for Students and Teachers) on a PC. I have created a smooth ... A: John, If you right click on your graph line, the popup should show Format Data series as the first ... | |
| Extraction from payroll | 10/24/2010 |
| Q: I have prepared a payroll and want to extract individual payslips for each employee. How do I go ... A: Augustine, If you have your data in sheet1 in columns A to F, say, and the unique identifier for ... | |
| Computation of interest rates | 10/24/2010 |
| Q: Sir, I have a problem setting up a formular for calculating interest rates for a range of values e.g ... A: Augustine, Assume the amount is in A1 =if(or(A1="",A1<=0),"",if(A1<31,10%,if(A1<100,12%,14%))) ... | |
| find and highlight duplicate in excel 2003 sheet | 10/22/2010 |
| Q: sir i have a huge list of websites, but i am unable to find the duplication in my list of websites, ... A: If you only have those to words to look for, then you can replace the formula in B1: ... | |
| Find, If and | 10/22/2010 |
| Q: i want to upgrade any two or one grade (A1,A,B1,B,C1,C,D,E1,E2)from cell range B2:B7 to next higher ... A: sunil send me a sample workbook such as the one you show and I will set it up. Tell me what ... | |
| copying 3-4 cells onto end of next worksheet | 10/22/2010 |
| Q: I have a printer "parts" worksheet with 750+ rows showing model, part #, alternate part #, ... A: Jim, ============= revision ================ the macro doesn't have to look in column A. It can ... | |
| find and highlight duplicate in excel 2003 sheet | 10/21/2010 |
| Q: sir i have a huge list of websites, but i am unable to find the duplication in my list of websites, ... A: If you only want to check against the first string to the right of the period, then (assuming your ... | |
| copying 3-4 cells onto end of next worksheet | 10/21/2010 |
| Q: I have a printer "parts" worksheet with 750+ rows showing model, part #, alternate part #, ... A: Jim Sub copydata() Dim r as Range, sh as worksheet Dim destsh as worksheet, rw as Long set r = ... | |
| Excel VBA Delete Sheets not User Named | 10/20/2010 |
| Q: I have several workbooks in a directory. I want to read each workbook, and delete each sheet named, ... A: I assume no user named sheet will include the substring "Sheet1". You may need to make the code ... | |
| Saving .csv file | 10/20/2010 |
| Q: it is me again from Wales in the UK. With you previous help my workbook is doing exactly what I ... A: Bert, If you just want to save an existing workbook (that is already a .CSV file), then you would ... | |
| And Or Statement question | 10/19/2010 |
| Q: I have a formula that is not working because it keeps giving me a #VALUE! message. The formula is ... A: Debbie, it is obvious I don't understand what your rules are. I have attached a picture showing ... | |
| And Or Statement question | 10/19/2010 |
| Q: I have a formula that is not working because it keeps giving me a #VALUE! message. The formula is ... A: Debbie, =IF(OR(ISERROR(AB279),ISERROR(T279)),"N",IF(OR(AB279>1,T279>1),"N","Y")) worked for me. ... | |
| re: | 10/19/2010 |
| Q: The code compiles but it doesn't copy anything, no matter what rep name I input. I have removed ... A: Dave, If you want to send it you can send it to twogilvy@msn.com Note that I didn't change any of ... | |
| lookup and copy/paste VBA | 10/19/2010 |
| Q: I need to create a workbook that will look sales data up by the salesperson's name and return it in ... A: corrected code: (untested, but compiles) Test it on a copy of your workbook Sub CopyActivations() ... | |
| Macro Recorder Does not record Find and Replace | 10/19/2010 |
| Q: Here i am with a aching problem once again. I have a huge data set with some error values in it #N/A ... A: Sami, So you don't want to use a macro. Then click in your sheet and hit the F5 key to get the ... | |
| lookup and copy/paste VBA | 10/18/2010 |
| Q: I need to create a workbook that will look sales data up by the salesperson's name and return it in ... A: Dave, I gave you the code you asked for. It loops through the sheets. I adjusted the determination ... | |
| lookup and copy/paste VBA | 10/18/2010 |
| Q: I need to create a workbook that will look sales data up by the salesperson's name and return it in ... A: Dave, so you want me to add a loop to the code that loops through sheets? It seems like I have ... | |
| write a custom function (with VBA) to use to return multiple values | 10/18/2010 |
| Q: the name of the person I will add manually corresponding to the function. ... A: Salma, Must have misread your picture. A little difficult to try to write code flipping back and ... | |
| automatic database subset | 10/18/2010 |
| Q: I own a small (micro) business and am trying to develop a way to subdivide a database I have with ... A: Daniel Sub splitData() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, cell As Range Dim ... | |
| VBA query - adding 6 months to a date | 10/17/2010 |
| Q: I have a spreadsheet which is used to monitor the on-time lodgement of forms for a large number of ... A: Alison, I can certainly show you how to check and make it the end of the month. But what if the ... | |
| @ speak cells on enter | 10/16/2010 |
| Q: Sir , i know about the option 'speak cells on enter' But sir i want to ask you that --- on ... A: prathamesh, speak on enter is one of the settings of Text-to-speech. It only speaks the value of ... | |
| macro | 10/16/2010 |
| Q: i had created one macro in excel as Personal , which worked on the desktop i created. after which ... A: Mukesh, there is nothing unique about naming a macro as personal. If you record a macro, you have ... | |
| Excel- Dictionary-style Header/Footer | 10/16/2010 |
| Q: I would like to know how, or if it is possible, to create a footer that lists the first word/last ... A: Lisa, the only way you could do that is to design a macro to do that. This would be extemely ... | |
| VBA Adjustment | 10/15/2010 |
| Q: Hey Tom, I have a question regarding Excel 2007 macro. I have a macro that splits worksheets into ... A: Vish, Excel 2007 is version 12 so For Each sh In OldBook.Worksheets If sh.Visible = True ... | |
| Macro interference | 10/15/2010 |
| Q: Tom, I am having trouble with macros in an .xlsm spreadsheet interfering with other macro-enabled ... A: Terry, If your code makes a change and you don't want the change to trigger the change event then ... | |
| Macros to convert number to text | 10/15/2010 |
| Q: I'm trying to write a macros that will convert a string of numbers into text. The numbers I want to ... A: Chris, say you have the data you show in columns A and B of a sheet named Data. now in the ... | |
| VBA Save Adjustment | 10/14/2010 |
| Q: I'm currently using Excel 2007, and i have a very large macro that loops through 312 files within a ... A: Vish where you have 'save the changes done to center the worksheet horizontally and vertically for ... | |
| VBA - Detecting distance between fields | 10/14/2010 |
| Q: I know you're an excel expert but there are no word experts willing to take on VBA questions so I ... A: Jo, I don't know the word object model. If you can't get anyone to help, I will try to get you an ... | |
| Using the "Go To" to find Conditional Formatting that has been applied via copy and paste | 10/14/2010 |
| Q: When I copy and paste a conditonal format, the [Go To, conditional format, same]only selects the ... A: Nicolette, If you have Excel 2007 or Excel 2010, then select one of the pasted cells and go to ... | |
| Macro interference | 10/14/2010 |
| Q: Tom, I am having trouble with macros in an .xlsm spreadsheet interfering with other macro-enabled ... A: Terry, It sounds like (and based on a previous question you asked) that you are using the OnEntry ... | |
| adding two macros to the same spreadsheet | 10/14/2010 |
| Q: Good day Tom. I have a workbook with 6 named tabs. I have set up a 'front page' with 12 command ... A: Bernard Barry, You say command buttons (which reside on the control toolbox toolbar) but it sounds ... | |
| Using TreeView in a Form | 10/13/2010 |
| Q: Tom, I was wondering if you could help me out with something. I'm not very experienced in VB so for ... A: Shawn, one way would be to use the beforesave event. It has an argument called Cancel. If you ... | |
| Using TreeView in a Form | 10/13/2010 |
| Q: Tom, I was wondering if you could help me out with something. I'm not very experienced in VB so for ... A: Shawn, Just to add, I have seen Jan Karel Pieterse answer questions in this forum on treeview ... | |
| excel attachments in Outlook | 10/13/2010 |
| Q: I work for a healthcare company, and receive approx 700 emails each week, each one being from a ... A: Gail, take a look at this site and see if this will work for you: ... | |
| Updating inventory file | 10/13/2010 |
| Q: I created an excel file for my entire catalog of products that I routinely add/change/update and ... A: Stephan, the link to your biggest warehouse is probably the best supported by Excel. You can set ... | |
| Create synchronized combo boxes | 10/13/2010 |
| Q: My name is Roee Bicher. I'm a 25 years old student from Israel. I have a problem as follows: I have ... A: Roee Debra Dalgleish shows how to do dependent dropdowns Here entry point is: ... | |
| Copying data from several workbook to one workbook | 10/12/2010 |
| Q: I have another question. Is there any way that I can write macro which can copy data from several ... A: mohammad, this is syntacically correct. I have stated some of the assumptions I have had to make ... | |
| how do I create a formula to track changing values | 10/12/2010 |
| Q: I'd like to track the changing values in a wide set of tables by simply changing the values in the ... A: Teresa, I gave you the correct formula but had a typo. Here is the corrected formula: ... | |
| write a custom function (with VBA) to use to return multiple values | 10/12/2010 |
| Q: the name of the person I will add manually corresponding to the function. A: salma, With only this workbook open, go into the VBE (alt+F11) and then choose Insert=>Module ... | |
| how do I create a formula to track changing values | 10/12/2010 |
| &n | |