| Subject | Date Asked | Expert |
|
| Macro to enter and fix date | 11/23/2009 | Tom Ogilvy |
Q: I have a spreadsheet with 2 columns. In column A I enter the date and against it in column B I enter ...
A: Tony, You would need a macro to do this Right click on the sheet tab where you want this behavior. ...
|
| Macro to move rows between worksheets | 11/23/2009 | Tom Ogilvy |
Q: I am trying to create a macro that will cut (remove) all rows that contain “Resolved” in column M, ...
A: Claudia, If Resolved is found in column M as your code seems to indicate, then this worked for me: ...
|
| Looping in Macro | 11/23/2009 | Richard Roberts |
Q: can the macro look in a set of folders for a specific name based on a specific cell value and copy ...
A: Khaled I am definatly not a expert in macros--but I do believe you can do what you want--I suggest ...
|
| Excel Spreadsheet | 11/22/2009 | Aidan Heritage |
Q: I am creating a mileage chart in Excel with same cities horizontal and vertical - is there a way to ...
A: Hopefully that's a different question - the original question seemed to relate to how to put the ...
|
| Excel Spreadsheet | 11/22/2009 | Aidan Heritage |
Q: I am creating a mileage chart in Excel with same cities horizontal and vertical - is there a way to ...
A: if you are doing this as a one off, then you can copy the results and use edit, paste special to ...
|
| Count/Countif | 11/22/2009 | Richard Roberts |
Q: ... I am trying to do a input sheet for a questionnaire. Currently having trouble trying to get a ...
A: Matthew It sounds as though you need the countif() formula, where the formula only counts the cells ...
|
| Date computation excluding Sundays | 11/22/2009 | Tom Ogilvy |
Q: I need to computed for the expected date, i have a data of actual date and lead time. the straight ...
A: Apple, For 9 days, the formula would be Assume A1 contains the start date ...
|
| Using INDEX with multiple criteria | 11/21/2009 | Tom Ogilvy |
Q: On Feb 22 of this year you answered someone's question who was looking to insert names in a table ...
A: Norm, This isn't my site. This belongs to Allexperts. I am just one of the volunteers here. ...
|
| #DIV/0! error | 11/21/2009 | Tom Ogilvy |
Q: I have a spreadsheet that I have the coloums set as accounting so all the $ line up. But when I put ...
A: Angela, my formula produces a zero stored as a number. how it looks depends on how you format the ...
|
| Open workbook without defining the path | 11/21/2009 | Tom Ogilvy |
Q: I was wondering if there is a way to open a workbook without having to define the path, I do not ...
A: steven, first, you asked for code to open a file without the path. But I don't know where that ...
|
| delete row where cell has specified text | 11/21/2009 | Bob Umlas |
Q: I need to develop a macro that will search through a worksheet and delete any row with the word ...
A: Sub DeleteNotDue() 'untested Dim Rg as Range On error resume next For ...
|
| VBA search using inputbox | 11/20/2009 | Tom Ogilvy |
Q: I am trying to use inputbox to search a sheet for specified text e.g "town" and when it finds the ...
A: Andreas, Sub FindMultiple() Dim tgt As Variant, sh As Worksheet Dim r As Range, sAddr As String Dim ...
|
| macros that automaticly calculating values through a workbook system based on values entered into a cell | 11/20/2009 | Aidan Heritage |
Q: I have developed a workbook in excel that calculates production costs for circuit boards based on ...
A: The macro sounds fairly easy- it's basically going to be a for...next loop which puts in the values ...
|
| Creating column macro (criteria based) | 11/20/2009 | Aidan Heritage |
Q: appreciate your time. MyIs there a formula/macro that can create column D below? Basically, ...
A: Not a problem - you can use a COUNTIF function to determine the first row, and a sumif to give you ...
|
| Vlookup based on several drop downs | 11/20/2009 | Aidan Heritage |
Q: I am trying to show an answer based on several selections in drop downs. Using this: ...
A: Not sure I have enough to go on in that at the MOMENT it doesn't sound like a lookup, simply an IF ...
|
| Excel 2007 - auto-updating number | 11/20/2009 | Aidan Heritage |
Q: I have a spreadsheet that keeps track of employee hours. What I'd like to add is a cell that takes ...
A: Excellent- your formula has a SLIGHT problem in that presumably the holiday continues in January, ...
|
| To Lookup and return values | 11/20/2009 | Aidan Heritage |
Q: I have a sheet which has data. In column a there are book names and in column B there shelf numbers. ...
A: multiple lines is always going to be a problem as no function can program the lines, but you could ...
|
| To Concatenate and display in multiple lines | 11/20/2009 | Bill |
Q: In my sheet cell A1 has candidate's Name and cell B1 has Father's Name. I have concatenated them in ...
A: Try this - adjust ranges and number of names (1000) accordingly. Sub WrapNames() Dim x as Integer ...
|
| To Concatenate and display in multiple lines | 11/20/2009 | Aidan Heritage |
Q: In my sheet cell A1 has candidate's Name and cell B1 has Father's Name. I have concatenated them in ...
A: It's going to be difficult as although you can do it manually, there isn't a direct way of doing it ...
|
| Variable Tiers in Excel | 11/20/2009 | Miguel Zapico |
Q: I have a complex variable tier that I want to reference in a formula to calculate a total cost. ...
A: You can use the VLOOKUP formula for this. To do so, craft a table with the following layout: ...
|
| WorkbookBeforeSave | 11/20/2009 | Bob Umlas |
Q: Here at work, I use Excel Professional 2003 (11.8307.8221) SP3. I write Workbooks, containing ...
A: If the original folder is a fixed path, you can test for that path and not allow it. Also, if you're ...
|
| Mouse over to show images | 11/20/2009 | Bill |
Q: When a cell has a comment, it will show on a mouseover, correct? Is there any way to have an image ...
A: The following line of code will put an image in a cell comment in A1 ...
|
| application.match function | 11/20/2009 | Bob Umlas |
Q: The code below matches values and perform cell updates from two differents WBs, "Master.xls" and ...
A: Sub MatchValues() Dim N As Integer, WB1 As Workbook, WB2 As Workbook, LRow as Long Set WB1 = ...
|
| application.match function | 11/20/2009 | Bob Umlas |
Q: The code below matches values and perform cell updates from two differents WBs, "Master.xls" and ...
A: Sub MatchValues() Dim N As Integer, WB1 As Workbook, WB2 As Workbook, LRow as Long Set WB1 = ...
|
| Creating List of Document Hyperlinks | 11/20/2009 | Aidan Heritage |
Q: I am trying to create a list of file names in Excel corresponding to the documents in a folder - we ...
A: The basic bit of the process boiled down is:- Set fs = Application.FileSearch Dim newbook, ...
|
| Conditional Formatting on a formula | 11/20/2009 | Douglas M. Smith |
Q: I am using this formula: =IF(G7="Closed","Closed",NETWORKDAYS(C7,TODAY()))to do aging on outstanding ...
A: That's very strange, I think it should work as you have it. However, if you re-order the testing ...
|
| delete macros in a copy file | 11/20/2009 | Stuart Resnick |
Q: I have a workbook called Expenses Sheet.xls located in R:\LGS\FINANCES\ losts of people refer to it ...
A: The following code should be entered onto the VBE ThisWorkbook object. Then, whenever a Save As is ...
|
| Linking cells or mirroring worksheets | 11/20/2009 | Richard Roberts |
Q: I would like to have what I do in the first worksheet appear in the next 6 worksheets. I linked the ...
A: Arturo Place your cursor at the bottom of the worksheet where the sheet tabs are and right ...
|
| To Remove duplicate rows | 11/20/2009 | Bob Umlas |
Q: I have an excel sheet which has data. Column B contains Name, Column C contains cell phone numbers ...
A: Sub NoDupes() 'using col EA n = ActiveSheet.UsedRange.Rows.Count With Range("EA1").Resize(n, ...
|
| Converting different times into slots | 11/20/2009 | Douglas M. Smith |
Q: I need a formula to convert different times into slots. e.g. I have different times start from ...
A: Time values are really only a decimal value representing a point in a day, with a whole day being ...
|
| Code that displays the Date at which a cell/cells Changed. | 11/20/2009 | Tom Ogilvy |
Q: I am working on a book check-in/out Excel file. I currently have the relevant sheet working with the ...
A: Sujan, Based on what you have said, I would do this: Private Sub Worksheet_Change(ByVal Target As ...
|
| Excel Macro | 11/20/2009 | Stuart Resnick |
Q: I'm trying to make a macro that goes to the last row and compare part numbers found in column C. ...
A: Sub sample() Dim nRow As Long Dim MaxRow As Long Dim Ws As Worksheet Set Ws = ...
|
| Excel Macro | 11/20/2009 | Miguel Zapico |
Q: I'm trying to make a macro that goes to the last row and compare part numbers found in column C. ...
A: It looks like the error may be on the For statement, you need to have two numbers instead of the ...
|
| Array Formula in VBA | 11/20/2009 | Tom Ogilvy |
Q: Tom you have helped me out in the past several years ago actually and glad I was able to finds you ...
A: Hans J Hamm, this worked for me: Sub abc() Dim s As String s = ...
|
| VBA search using inputbox | 11/19/2009 | Tom Ogilvy |
Q: I am trying to use inputbox to search a sheet for specified text e.g "town" and when it finds the ...
A: Andreas, This worked for me. Sheet1 is the hidden sheet where the data is. Sheet2 is where the ...
|
| Multi email sender | 11/19/2009 | Aidan Heritage |
Q: How can I send personalized email to many (ex 40) customer with same subject and a bit different ...
A: This is a standard mail merge - you didn't specify the version of word, but basically set your ...
|
| Excel LOOKUP? | 11/19/2009 | Aidan Heritage |
Q: I need a formula that would search the value of A2 in a array C:E and return the group "Joe" is in. ...
A: I think you would need to do this with an array formula, which is entered by pressing ctrl shift ...
|
| Adding formulas worksheet to worksheet | 11/19/2009 | Miguel Zapico |
Q: I've been able to figure out how to add a cell in one work sheet to a cell in another worksheet. ...
A: This can be because of the use of absolute references. Check that the row number in the reference ...
|
| Excel Macro | 11/19/2009 | Bill |
Q: I am trying to build an agent-based model in Excel. I need agents to talk to each other. The agents ...
A: Yes, I know how. But, HOW do you want the code to display, mark, identify, flag, highlight, etc., ...
|
| ranking and pivot tables | 11/19/2009 | Aidan Heritage |
Q: Is there a function that allows you to work out the ranking of a particular column (ie. fees ...
A: A good question- I THINK from research that this might be included in Excel 2010, but for now try ...
|
