You are here:
| Subject | Date Asked |
| Fit to Page not working | 2/9/2012 |
| Q: See below: Sub SaleList() Range("D:G,N:P").Copy Worksheets.Add.Name = "SaleList" ... A: D:G and N:P winds up being 7 columns (DEFGNOP), not 6 -- my bad? or yours? Change to Sub SaleList() ... | |
| Delete a Value in a field, based on a value in another field. | 2/9/2012 |
| Q: Good Morning, I think I'm trying to do a simple request, but can't seem to figure it out. I have ... A: (Sorry -- had a mistake)... Instead of having them put an X in column H, why not simply clear ... | |
| Number to Words | 2/9/2012 |
| Q: I have a macro query. if I type 123 in A1, it should automatically Display "ONE TWO THREE" in B1. it ... A: That's now what you originally asked. you said 123 should be ONE TWO THREE, not One Hundred Twenty ... | |
| Using IF statement | 2/9/2012 |
| Q: Cells A2:A299 contain product codes. Cell E2 contains the following formula: ... A: Something else is going on, so I'm not sure ... Try this to see that your assumption is not ... | |
| Number to Words | 2/8/2012 |
| Q: I have a macro query. if I type 123 in A1, it should automatically Display "ONE TWO THREE" in B1. it ... A: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| Macro to sort columns | 2/7/2012 |
| Q: I am in need of a macro that will select several columns in one sheet, create a new sheet and copy ... A: Sub Shawn() Range("A:C,F:F,J:K").Copy Worksheets.Add.Name="ENTER NEW NAME HERE" '<====answer ... | |
| Conditional Format | 2/7/2012 |
| Q: Have you any ideas how this could be done? A: Can easily be done with conditional formatting. If you don't know how to do that, it's pretty hard ... | |
| Macro to sort columns | 2/7/2012 |
| Q: I am in need of a macro that will select several columns in one sheet, create a new sheet and copy ... A: This macro assumes you want to copu columns A:C,F,J:K -- adjust to suit. It will put them together, ... | |
| Copy values from non-adjacent cells to a new workbook | 2/4/2012 |
| Q: I’m trying to copy (only the values) of a number of non-adjacent cells to another workbook. At ... A: Sub MyVersion() For each x in Array("A1","B2","C3","D4","E5") ... | |
| Copy values from non-adjacent cells to a new workbook | 1/30/2012 |
| Q: I’m trying to copy (only the values) of a number of non-adjacent cells to another workbook. At ... A: Sub test() Dim i As Long Sheet2.Select '<=== Activates Sheet2 With ActiveSheet.Range("A1, B2, C3, ... | |
| Fill sheet | 1/30/2012 |
| Q: please see this my example and help me. in my vba form - txtname is text box - cmdEnter is command ... A: Private Sub cmdEnter_Click() Dim n As Integer On Error Resume Next n = ... | |
| excel conditional saveas | 1/29/2012 |
| Q: I would like to have a file open as read only and then use SAVE AS once only. After the file is ... A: In the VBE, double-click the "Thisworkbook" object, enter this code: Public DidSaveAsOnce Private ... | |
| Copy colored cells | 1/27/2012 |
| Q: We have the below macro which copies the colored(yellow) cells(all the colored cells will be ... A: Is this what you want? Sub Yellow() For Each thing In ActiveSheet.UsedRange If ... | |
| Excel 2003 - Mandatory Fields | 1/26/2012 |
| Q: I have a problem were I need mandatory fields but only for a specific work sheet. I found an answer ... A: Activate the specific worksheet first: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, ... | |
| Conditional Format | 1/26/2012 |
| Q: Here we go...I need a conditional format that will format my text (A1) white if any one of the ... A: I dn't understand the 3rd condition. You want white text in A1 if there's a formula in B1 that ... | |
| Disabling the memo box in Excel | 1/24/2012 |
| Q: I need to disable the memo box in Excel using VBA. Is there a parameter I can set to false for ... A: Protect the sheet. If you first select all the cells and use ctrl/1, then the protection tab and ... | |
| Random Numbers without repeating | 1/23/2012 |
| Q: Can you please suggest formula (Without Macro or VBA,)which can generate Random numbers in D1:D500 ... A: OK, using another helper column. F1:F500 contains 1 thru 500. E1:E500 contains =RAND() D1 contains: ... | |
| two reports from pivottable (VBA) | 1/20/2012 |
| Q: I have three columns to create this reports. Timing, Position, Region. One report that has timing on ... A: Yes, but I need a lot more info -- seems like just recording a macro making a pivot table of your ... | |
| Mac Excel -- Dialog Boxes | 1/18/2012 |
| Q: I am working with older macros (Excel 2004) that use custom dialog boxes to enter values into a ... A: MSGBOX statements would return 2 for cancel or 1 for blank, as in msgbox("hello",vbokcancel) But ... | |
| Excel 2010 Macro/VBA Help | 1/17/2012 |
| Q: I am attemptingto write a Macro/Routine for the following problem: I have a listing of data in ... A: Sorry it took awhile to get back to you. This is asking quite a lot, and it'd be easier if I had a ... | |
| Adding values across sheets | 1/17/2012 |
| Q: Good day! I hope you are fine. I have an vba excel problem that I needed help with. I am using ... A: It can, and I would suggest recording using the condolidate feature, then modifying the code to suit ... | |
| Disable CUT entire row | 1/16/2012 |
| Q: I need to disable users from cut copy paste entire row. I mean cutting an entire row from table1. ... A: Easiest would be to prevent them from SELECTING a whole row. Right-click the sheet tab, select View ... | |
| save & search function in macro | 1/16/2012 |
| Q: Kindly I have a folder called "Sales target", inside this folder there are many sub folders "Jamal, ... A: This code will save the activeworkbook in the appropriate subfolder (and create one if needed), but ... | |
| Date Calender | 1/13/2012 |
| Q: I have date values in column A and need a function that will calculate 30 business days from that ... A: Interesting challenge. If you have your holidays in a range of cells named "holidays", it looks like ... | |
| Hiding the command buttons | 1/11/2012 |
| Q: Have Excel 2007. When I activate the macros on my spreadsheet I want in this case Command Button15 ... A: If it's a regular button on the sheet, you need to know its name (ctrl/click on the button, look at ... | |
| 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: Sorry I was so long in answering... If you're comparing 2 lists, this formula will find the items ... | |
| identify date in a range | 1/5/2012 |
| Q: I am working on an annual leave planner. I need to identify when a public holiday falls within a ... A: Can you send an actual workbook... send to Bobumlas@hotmail.com, use subject of "AllExpertsQ" and ... | |
| Custom views and protected worksheets | 1/4/2012 |
| Q: Bob, I read the following question string from 5/11/2005: ... A: How do you want to use the Custom Views feature? And how is that related to protection? In Excel ... | |
| Conditional Formatting | 1/4/2012 |
| Q: I have excelsheet contains start date, expected end date, actual date followed by duration (i.e. jan ... A: bobumlas@hotmail.com Use subject of AllExpertsQ ---------- Reserve the dates for BOB'S EXCEL USER ... | |
| identify date in a range | 1/4/2012 |
| Q: I am working on an annual leave planner. I need to identify when a public holiday falls within a ... A: I'm missing some logic here... ALL HOLIDAYS fall between April and March. All holidays fall between ... | |
| Totaling the hours on a 24/7 Employee Work Schedule | 12/17/2011 |
| Q: I relied on a similar question/answer on this subject from a previous questioner but it doesn't seem ... A: Your data layout makes it quite difficult (not impossible) to get the real times. First, in Excel, ... | |
| Excel | 12/16/2011 |
| Q: i am creating a spreadsheet with a list of different codes e.g CPCA0103, CPCA0345 and each one of ... A: Easier to show than explain -- send sample wb to me at bobumlas@hotmail.com, use subject of ... | |
| Excel Question | 12/14/2011 |
| Q: Is the anyway to "SUM" several values inside of a text box? A: I need more info. The formula would really reside in a cell, and the results of that cell can be in ... | |
| Excel 2011 and Worksheet_Change | 12/13/2011 |
| Q: I am having difficulty getting any Worksheet_Change macros to run. I opened a new workbook and ... A: Worksheet_Change macro code is NOT written in a module. You must right-click the sheet tab, select ... | |
| Macro - Populating Explorer files in directory | 12/8/2011 |
| Q: My question pertains to the following macro that has just about accomplished my goal. I would like ... A: Try this: Dim FSO As Scripting.FileSystemObject, Locn As Range Sub StartListing() Dim ... | |
| Popup only once | 12/6/2011 |
| Q: The below code Removes workbook if date > Edate and wrong password is entered. How to change the ... A: Are you saying you didn't want to see the INPUTBOX popup??? If that's true, then you should not have ... | |
| Popup only once | 12/6/2011 |
| Q: The below code Removes workbook if date > Edate and wrong password is entered. How to change the ... A: Const pw As String = "xxx" Private Sub Workbook_Open() Dim Edate As Date On Error Resume ... | |
| Mailing Lists | 12/5/2011 |
| Q: I'm using Excel 2003. I've been asked to create a 'mailing list' for a previously existing database, ... A: I don't see the issue -- works fine as is. Perhaps you can email me a sample which would illustrate ... | |
| Popup only once | 12/5/2011 |
| Q: The below code Removes workbook if date > Edate and wrong password is entered. How to change the ... A: Inputbox becomes hidden (or closes) when the user responds to it. No need to try to set something to ... | |
| Excel - deleting rows whit specific data | 12/5/2011 |
| Q: I have a huge data in Column A. I would like to delete all the rows where in the cell in column A ... A: In a helper column, say H, enter: =IF(LEN(A1)<9,1,"") and fill down. Then calculate (F9), press ... | |
| Copying data from one workbook to another | 12/2/2011 |
| Q: I am trying to enter text on one worksheet and have it transfer to all the other sheets in my ... A: That's not "having it transfer to all the other sheets". That's entering a formula to pick up the ... | |
| Loading Value | 11/30/2011 |
| Q: I want to ask my question with below example. -in A column student name -in B column Student age -in ... A: Private Sub txtName_Change() Dim n As Integer On Error Resume Next n = 0 n = ... | |
| How To Run Form in VBA | 11/30/2011 |
| Q: Sir, I am developing excel 2003 addin . The addin have a MyAddin button on menu bar which have ... A: Sub Form1() Userform1.Show End Sub or, if you've given it a name: Sub Form1() MyForm.Show ... | |
| index formulas | 11/26/2011 |
| Q: Bob, I'm using Excel 2003 with Windows XP3. I tried to create some "Index" formulas based on ... A: Sure - sent to me at bobumlas@hotmail.com, use subject of "AllExpertsQ" and repeat the issue. ... | |
| Pivot Table - Matching / Duplicate Numbers | 11/22/2011 |
| Q: I think this is simple but...: Using a Pivot Table I have 2 columns with about 5,000 numbers. The ... A: Same answer -- perhaps you didn't enter with holding ctrl+shift before pressing enter: ... | |
| Check Boxes | 11/16/2011 |
| Q: I have 3 check boxes, 3 macros already and 3 tabs in my workbook all of which correspond one to one. ... A: send a sample to me at bobumlas@hotmail.com, use subject of AllExpertsQ, repeat what you're trying ... | |
| Locking specific cells | 11/16/2011 |
| Q: I am making a worksheet for my company and will be uploading it to my SharePoint site, where members ... A: Cells are all locked by default on a new worksheet. You unlock the ones you want users to be able to ... | |
| Date formatting | 11/15/2011 |
| Q: I need to have a date field whereby it records the date of the last update vs. the last date on ... A: The code as initially given will put the time of the last change made to the sheet in cell A1. If ... | |
| Bold If Macro | 11/15/2011 |
| Q: I tried the below, not sure why it isn't working. Could you assist? Goal is to make either range ... A: You're setting B1 and B2 to Strings, not ranges. You need: Set B1=Range("B36:J36") Set ... | |
| Macro to printing page | 11/14/2011 |
| Q: I am using excel 2000. The 'fit to page code' in my macro for printing is like this: Zoom = False ... A: Probably you should hide the columns with no data. But if you have 300 columns, and insist on ... | |
| Excel Formula | 11/14/2011 |
| Q: I am responsible for creating a single salary schedule for teachers in my district. This schedule ... A: Maybe I'm not understanding -- have each formula reference the cell above * 1.03, like =M1*1.03 then ... | |
| Macros | 11/14/2011 |
| Q: Bob, Dummy here again. I have tried everything you told me to have my macro accessible by everyone ... A: If the macro resides in your personal macro workbook on the S drive, they do need to open it. They ... | |
| Excel lookup and if (v2010) | 11/13/2011 |
| Q: I am trying to write a formulat to determine the withholding amount with a v or h lookup and an if ... A: If your data is in A1:C10, for example (name in A, ded in B, Sal in C), and your tax table is in ... | |
| Closest Rank | 11/12/2011 |
| Q: A1 contains Base Number 22. B1 = Limit 1 = 5 C1 = Limit 2 = 100 D1:D11 = Object 1 to 11 = 24, -4, 1, ... A: You need a helper column (which you can hide). Say that's G. In G1 enter: ... | |
| Macro Addins | 11/10/2011 |
| Q: Bob, I unhid the Personal Workbook that contains my macro; File Saved as an Excel Add-In (*.xlam) ... A: Macros from an Addin don't show up in the list, but they're available. If you know the macro name, ... | |
| logical test on large data sets | 11/10/2011 |
| Q: I am wondering how to test the value in all rows (about 18000 rows)which contains one of five ... A: If your 5 codes are stored in H1:H5, for example, and the 18000 zip codes are in A1:A18000, enter ... | |
| macros | 11/9/2011 |
| Q: Bob: I have a macro saved on my Personal Macro Workbook in Excel 2007. However, I want everyone in ... A: You can do addins anywhere. In your save as, choose Excel addin (*.xlam). Doing so will change the ... | |
| Date formatting | 11/9/2011 |
| Q: I need to have a date field whereby it records the date of the last update vs. the last date on ... A: You're supposed to put this code exactly as it is (with perhaps the exception of A1 -- if you want ... | |
| Date formatting | 11/9/2011 |
| Q: I need to have a date field whereby it records the date of the last update vs. the last date on ... A: Go to the VBE (Alt/F11), double-click the "ThisWorkbook" in the Project Explorer, then insert this ... | |
| macro to change cell color based on content | 11/8/2011 |
| Q: I run an excel2003 report weekly that I have to manually color code a cell based on its content. ... A: To use the macro, press Alt/F8, then double-click "Colorize". You can put a code in column U ... | |
| Combo box runtime error 13 | 11/5/2011 |
| Q: I have been working with the code below for several days. I have a combo box that pulls information ... A: If it clears the textbox, along with the error, then just suppress the error. Put this in the ... | |
| macro to change cell color based on content | 11/4/2011 |
| Q: I run an excel2003 report weekly that I have to manually color code a cell based on its content. ... A: You don't need a macro to run this, this IS the macro. I no longer have Excel 2003, but you need to ... | |
| macro to change cell color based on content | 11/4/2011 |
| Q: I run an excel2003 report weekly that I have to manually color code a cell based on its content. ... A: Sub Colorize() For Each cl In Range("L4:L250") Select Case cl.Value Case 1, 2, ... | |
| Prompts for Sources | 11/4/2011 |
| Q: I'd like it so if a person enters data in a cell, say A1, the program will prompt for the source of ... A: Sorry, I misunderstood. Private Sub Worksheet_Change(ByVal Target As Range) If ... | |
| Prompts for Sources | 11/3/2011 |
| Q: I'd like it so if a person enters data in a cell, say A1, the program will prompt for the source of ... A: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| side by side floating bars | 11/2/2011 |
| Q: I am trying to make a gnat/bar chart wherein two bars will float side side having dates as x-axis ... A: It's Gantt chart, not Gnat chart. Send sample data to me at bobumlas@hotmail.com, use subject of ... | |
| macros | 11/2/2011 |
| Q: Bear w/ me Bob, I have a macro to be used in worksheets that are extracted from a huge database. ... A: Using AM1:AM3 as a place to hold these values: Sub FillExplanation() Range("AM1").value="Cash ... | |
| Prompts for Sources | 11/2/2011 |
| Q: I'd like it so if a person enters data in a cell, say A1, the program will prompt for the source of ... A: Where is the information for the prompt coming from -- or is it ALWAYS the exact text "Source: ... | |
| day fromand to total | 11/1/2011 |
| Q: column B "month" is displayed, i will have 1 year data with me.. so as the mnth changes the total ... A: You lost me. Please send me a file + a complete description again (I don't keep all the files i ... | |
| vlookup | 10/29/2011 |
| Q: I would like to do a vlookup and return the first non-blank cell in a row. what I mean is instead ... A: Not at all trivial. But this works. Enter this formula via ctrl+shift+enter: ... | |
| Macros if & ands | 10/28/2011 |
| Q: Scenario: Column A has Expenditures; Column C has Cash on Hand; Column F has # of days Cash on ... A: Select all the cells from A:F, use conditional formatting with this formula (assuming row 2 was the ... | |
| Vlookup with two variables | 10/27/2011 |
| Q: I am using Excel 2003. Sheet one has several columns: column C is artist name, D is song title, F is ... A: Sheet1, F2: enter this formula by first holding Ctrl+Shift, then pressing enter: ... | |
| Formula for Top5 & Bottom5? | 10/25/2011 |
| Q: My manager wants to grab top 5 & bottom 5 scores (range of scores on a row = 24) from Excel2003 ... A: The adaptation is incorrect on a few counts. =LARGE(100$g:100$am,ROW(G100)) isn't right. 100$g is ... | |
| Formula for Top5 & Bottom5? | 10/25/2011 |
| Q: My manager wants to grab top 5 & bottom 5 scores (range of scores on a row = 24) from Excel2003 ... A: You can get the top 5 items from a range with this formula, filled down 5 rows: ... | |
| Copy/Paste/Concatenate Macro | 10/24/2011 |
| Q: I am hoping you can assist me. I am looking for a way to copy text from multiple cells into one cell ... A: Sub Attacher() Dim stg As String, n As Integer n = 1 Range("C60000").End(xlUp).Offset(1, ... | |
| Formulas | 10/24/2011 |
| Q: Is it possible to combine a DCOUNT formula with a COUNTIF formula? I'm currently using a DCOUNTA ... A: SUMPRODUCT (or SUMIFS) is better. For example, if A, S, P are in column G and M/F in column H, these ... | |
| Sort a selection of data obtained from pivot table details | 10/22/2011 |
| Q: I was recently upgraded from Excel 2003 to Excel 2007 at work and I am having trouble doing ... A: After you double-click, Excel automaatically makes a Table out of the result and the sort you're ... | |
| hide columns depending on the month | 10/20/2011 |
| Q: I copied one of your answers and tried to modify it to my needs. If a cell changes the period of the ... A: It's possible that Excel would prevent this manually as well, under certain circumstances, like ... | |
| Merge two or more spreadsheets in Excel 2007 | 10/18/2011 |
| Q: I'm trying to merge into a single sheet a group of sheets. the first 3 have 65536 rows. I'm using ... A: Change Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp) to Selection.Copy ... | |
| IF & AND function | 10/16/2011 |
| Q: could you help me with the formula using "IF&AND" fuction for below data; IF(Cell=>dd/mm/yy)& ... A: In general, the syntax is: ... | |
| Message box appear if a value of a cell changes | 10/15/2011 |
| Q: I need a macro to show a message box if a value of cell changes due to formula. i have a cell C4 ... A: Not an easy task -- the events which MIGHT be responsible are Worksheet_Change event or ... | |
| Cell Separation for Database Mailing | 10/13/2011 |
| Q: I have a database that has been imported into Excel and in Column B is the Address (which is okay), ... A: You can separate city, state, zip by selecting the column and using Data/Text to columns, using ... | |
| Pivot Table filter in Excel 2007 | 10/12/2011 |
| Q: I saw you answered a question and follow-up for Al concerning a pivot table filter. I see he has ... A: He was asking about Windows Live Skydrive. Question and my answer follows: I have a simple Pivot ... | |
| Collection of Data | 10/12/2011 |
| Q: Im in need of your help in solving issues. Hope you will be able to help me with it. Im using an ... A: This does what you requested: Sub ToDUMP() Dim d As Worksheet, sh As Worksheet, n As Integer, ... | |
| Collection of Data | 10/12/2011 |
| Q: Im in need of your help in solving issues. Hope you will be able to help me with it. Im using an ... A: Question first: You say Row 1, but mention A1 and A2. Don't you mean A1 and B1? You say "copy all ... | |
| Locating textboxes on a worksheet | 10/12/2011 |
| Q: Sorry but I am taking the liberty of forwarding this request as a new question. I’ve already ... A: I think one of the best resources is the macro recorder (especially in xl 2010). Or, be on the ... | |
| Macros | 10/10/2011 |
| Q: Sir, I entered a date 01/05/2009 (1st may of 2009) in "a1" cell and entered another date 01/08/2010 ... A: 1 - it seems you already have written the macro to do what you want -- is it not working or is it ... | |
| Macros | 10/10/2011 |
| Q: Sir, I entered a date 01/05/2009 (1st may of 2009) in "a1" cell and entered another date 01/08/2010 ... A: I don't know what you mean by the last comment you made... You have an End Sub in the middle of the ... | |
| Resize a range | 10/9/2011 |
| Q: I have client worksheets with dates starting in row 9 in column A. The macro selects the range where ... A: The statement Set R2 = ... is fine, and sets a VARIABLE called R2 to the wanted range. However, ... | |
| Excel Macro Date Weekly | 10/7/2011 |
| Q: So the macro I'm looking for is just complicated and I can't seem to get a mix of them to work. Is ... A: In order to save it Friday at 7PM the workbook MUST be open at that time. In the workbook's Open ... | |
| Excel | 10/5/2011 |
| Q: Sir, I have a query in excel. for e.g: I am using formulae in cells as follows. A1= ... A: Neither Goal Seek nor Solver are appropriate for this task. However, it seems like it's straight ... | |
| Convert base 58 to 10 and around in Excel 2010 macro | 10/3/2011 |
| Q: Building a list of base 58 and base 10 which include 34,729,333,548 of number would be impossible ... A: Function Base58(rg As String) As Double Dim x As String x = ... | |
| Convert base 58 to 10 and around in Excel 2010 macro | 10/3/2011 |
| Q: Building a list of base 58 and base 10 which include 34,729,333,548 of number would be impossible ... A: Still not enough info. Base 16, for example, has A,B,C,D,E,F aside from the digits 0-9. What does ... | |
| excel linking with source sheet | 10/1/2011 |
| Q: I am working for a natinalised bank in a corporate planning department. We have 70 branches in our ... A: This is a solution to what you asked for, but what you asked for only copies one row from sheet 1, ... | |
| Tracking an iterations of an event by updating a single cell | 9/30/2011 |
| Q: Hey Bob, I'd like to track details of my phone calls by changing a single cell while creating a ... A: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| save time copy and pasting | 9/29/2011 |
| Q: I have an important question regarding saving time copy and pasting. Here is my scenario: - I have ... A: Yes, it's possible, but i have a few questions • are these numbers (1131,...) the column headers in ... | |
| Excel Macro | 9/28/2011 |
| Q: The First row of the spreadsheet where the data starts is row 3. I need any empty cells below a row ... A: Untested: Sub Test3() lrow = ActiveSheet.UsedRange.Rows.Count - 2 On Error Resume Next ... | |
| Excel Macro | 9/28/2011 |
| Q: The First row of the spreadsheet where the data starts is row 3. I need any empty cells below a row ... A: The macro I gave you did just what you asked: Sub Test3() lrow = ... | |
| organising a numbering system from a master copy | 9/27/2011 |
| Q: I work for a furniture company who supply businesses. Each time we quote a business we send a ... A: Press Alt/F11 Doubleclick "ThisWorkbook" on the left side in the Project Window. Paste this code in: ... | |
| Excel check mark in drop down | 9/26/2011 |
| Q: I have created a list of roles and responsibilities in a worksheet which then requires a tick or a ... A: It requires a fair amount of setup. For each ActiveX combobox, you need to make the font Wingdings, ... | |
| excel linking with source sheet | 9/24/2011 |
| Q: I am working for a natinalised bank in a corporate planning department. We have 70 branches in our ... A: Again, I need more info -- so far, it SEEMS like you can set up your formulas to link directly to ... | |
| Convert a number to text | 9/24/2011 |
| Q: If I have various numbers on the spreadsheet creating a football schedule for example: 5 vs. 8 or ... A: You can't enter 5 and have it show Rams without using VBA. (Actually, you could for up to 3 ... | |
| Searching for textboxes using vba | 9/22/2011 |
| Q: Bob I am trying to put some “smarts” into a fairly large SS which is used to display network ... A: #s 1 & 2 seem to be the same #3 I don't understand There are 2 separate macros -- first finds text ... | |
| IF and AND function to handle blank fields | 9/20/2011 |
| Q: I've been trying for a while now, but somehow the function keeps on going wrong when I try to ... A: The formula worked fine for me, so it's probably a language issue. If you started a new workbook, ... | |
| Excel 2003 | 9/20/2011 |
| Q: I'm using excle v2003. I have 2 spreadsheets with 4 columns each using the same criteria. (Date, ... A: I have no idea why you can't fill down. No reason you shouldn't be able to. Once you can get past ... | |
| Multiple word search in excel | 9/20/2011 |
| Q: I am trying to filter out rows with multiple keywords. For example, I have sheet1 with lets say ... A: Let's say your keywords are in Sheet3!A1:A10. In an unused column in Sheet1, enter this formula in ... | |
| IF and AND function to handle blank fields | 9/19/2011 |
| Q: I've been trying for a while now, but somehow the function keeps on going wrong when I try to ... A: Assuming you have Excel 2007 or 2010: in L19: ... | |
| Excel pie charts | 9/19/2011 |
| Q: Bob, I am using Excel to create pie charts. I use the Options tab and change the degrees to center ... A: I don't understand "how would I make the transfer?" I don't know of a problem with off-centered pipe ... | |
| table tennis | 9/18/2011 |
| Q: I am doing an project for college on excel about table tennis league table and I'm wondering how to ... A: I would need to know the complete layout of your worksheet. Send a sample file to me at ... | |
| excel linking with source sheet | 9/18/2011 |
| Q: I am working for a natinalised bank in a corporate planning department. We have 70 branches in our ... A: I don't have enough information to tell you what to do, but it seems you can do what you're looking ... | |
| excel combo box | 9/16/2011 |
| Q: i need a little guide on how to make a combo box using more thn 1 column..i need to relate the ... A: You need to combine them in another cell then use that in the combobox. If you have Employee ID, ... | |
| Merge Cells | 9/15/2011 |
| Q: I am using Excel 2007. I need an Excel Macro that will go through each column in the range of ... A: Yes, sorry; 2nd Next should have been End Select. I had understood you said that I,L,N,O,P,Q had no ... | |
| Merge Cells | 9/14/2011 |
| Q: I am using Excel 2007. I need an Excel Macro that will go through each column in the range of ... A: Sub Merger() lrow = ActiveSheet.UsedRange.Rows.Count - 2 On Error Resume Next ... | |
| Variable Range, Variable Worksheet Sort Macro | 9/11/2011 |
| Q: I want to record/create an Excel (2007/2010) macro that will use a variable manually selected range ... A: Sub Color_Sort() Dim WS As Worksheet, Stg As String Set WS = Worksheets("we-11-09-10") ... | |
| How to create a sort list | 9/8/2011 |
| Q: Each month I receive data from a state and I sort it A-Z by city. I send information to individuals ... A: You can create a sort sequence, and once you sort by this sequence, the unwanted ones would sort to ... | |
| Validation | 9/7/2011 |
| Q: I have a list of names in cells “N3” through “N108.” I have created a program where the users may ... A: Try this in the workbook Open event: Private Sub Workbook_Open() Application.OnKey " ", "" End ... | |
| updates | 8/22/2011 |
| Q: Sub OpenandUpdate() Dim fl As String fl = Dir("*.xl*") On Error Resume Next ... A: Sorry - yes, it had a looping bug. Use this: Sub OpenandUpdate() Dim fl As String ChDrive ... | |
| updates | 8/22/2011 |
| Q: Sub OpenandUpdate() Dim fl As String fl = Dir("*.xl*") On Error Resume Next ... A: The macro doesn't run by itself. With a small change, you dno't have to do anything except open the ... | |
| Copy & Auto Update | 8/22/2011 |
| Q: I have a workbook, which has 4 sheets. Sheet "Table" has the data and sheet "JCW" has the data. ... A: Sub Answer() On Error Resume Next ... | |
| Move info between 2 worksheets in one workbook | 8/19/2011 |
| Q: I need to move a row of information from Worksheet1 to Worksheet2 and the move would be based upon a ... A: This routine will move the rows with N from sheet1 to sheet2: Sub MoveRows() Dim ColumnWithN As ... | |
| Using VBA to remove a row | 8/19/2011 |
| Q: Hey Bob, I am very new to VBA (been using it for a few hours now) and I need to build a report ... A: Sub DeleteARow() Rows(2).Delete End Sub or, if you don't know it's row 2 but it IS Jane Smith: ... | |
| updates | 8/17/2011 |
| Q: Sub OpenandUpdate() Dim fl As String fl = Dir("*.xl*") On Error Resume Next ... A: If you pressed File/Open, are you pointing to the directory in which all your file are? If not, ... | |
| followup on updating work books | 8/17/2011 |
| Q: The directory I have this in goes like this:Computer>C>.....>2011. Under the 2011 folder is the "all ... A: The routine I sent you needs to be executed via Alt/F8, as you did. I have little experience with ... | |
| update workbooks in a folder | 8/17/2011 |
| Q: I have several workbooks in a folder named "All list". I would like to be able to update all these ... A: Sub OpenandUpdate() Dim fl As String fl = Dir("*.xl*") On Error Resume Next ... | |
| Hours | 8/14/2011 |
| Q: Hop that all is great with you , I am running into a trouble to place the time ( Hours) in a cell ... A: If the 23:30 is in A1 and the 30:00 in A2: ="THE MAINTENANCE TEAM HAS WORKED "&TEXT(A1,"hh:mm")&" ... | |
| Problem erasing empty cells and edit text. | 8/12/2011 |
| Q: First let me say you I have 2 questions: 1) How could I eliminate the empty spaces between each cell ... A: 1-Empty spaces bewteen each cell?? Not sure what you mean. Sounds like you have unused columns. If ... | |
| Embedded IF Statement in Excel 2003 | 8/11/2011 |
| Q: I'm having some difficulty trying to create a system to rank a set of criteria. I finally decided ... A: Combine all the conditions which yield 1, all that yield 2, all that yield 3, & default none to ... | |
| Copy data based on selection from data validation list | 8/11/2011 |
| Q: I have a data validation list in cell B1. Once a value is selected by the user, they will enter a ... A: Sub PostToSheet2() Dim n As Integer On Error Resume Next n = 0 n = ... | |
| formula needed | 8/10/2011 |
| Q: Example: B1=10,C1=4,D1=9,E1=6,F1=8,H1=5,G5=(Blank),I1=8. ... A: This function assumes there are never more than 2 numbers in a tie: Alt/F11, Insert/Module, Enter ... | |
| This Isn't Excel, It's Magic! | 8/10/2011 |
| Q: I bought and love your book "This Isn't Excel, It's Magic."Is there a version that addresses excel ... A: Glad you like it. I'm coming out with another book early next year: "Excel Outside the Box" - a very ... | |
| Restrict Data entry with time limit | 8/10/2011 |
| Q: I want to restrict data entry by a user on a excel sheet with time limit 24 hrs i.e morning 6:30 AM ... A: If the dates are in column A starting in A2, for example, select ALL the Activity details cells, ... | |
| Trying to find a way to reduce entering amounts in the wrong column | 8/5/2011 |
| Q: Our 8 column headers are as follows: MEDMAC, NRVHC, WYNE, CIC, VNA, DYE, HVC, PCV CHECKING We enter ... A: Right-click the sheet tab, select View Code, and assuming MEDMAC, etc is in row 1, enter this: ... | |
| Vlookup/Index/Match ... Which one to use | 8/4/2011 |
| Q: I have an excel spreadsheet that has has a list of employees and I need to get their weekly earnings ... A: It's not clear what's where, but assuming the first name is in A, last name in B in the main sheet, ... | |
| to provide only write access to users | 8/3/2011 |
| Q: Good Day!! I have a excel file in out cental database which is used by 20 users. I want to give ... A: If you FIRST select ALL the cells for which it's OK to make changes or enter data, then, use ctrl/1, ... | |
| Vlookup Variation | 8/2/2011 |
| Q: I have two sheets of data. I want to lookup a value from sheet 1 on sheet 2. If it matches then I ... A: Let's say the value you're looking up is in Sheet2!A1. The best way to do what you want is to just ... | |
| Copy rows based on date to new sheet | 8/1/2011 |
| Q: I have data is File A with date in column A, Is it possible to create a macro in such a way that, ... A: Assuming FileA is called FileA.xlsx, then right-click the sheet tab of FileB, sheet1, select View ... | |
| adjust text wrap with row spacing | 8/1/2011 |
| Q: Sir, I have long working sheet i used text wrap in it and i inclreasd row height but when i ... A: Now either I don't understand the question, or yes, you do need to size each row individually. When ... | |
| total on each sheet | 7/31/2011 |
| Q: sir, i have long work sheet having more than 900 rows total in end when i select the sheet and take ... A: Not really possible to know, since this could depend on the printer driver you have, the page setup ... | |
| Combining multiple rows with different data into one | 7/30/2011 |
| Q: I would like to combine multiple rows (with same customer contact ID's) having different data in the ... A: Try this routine: Sub Reorganize() Dim Comp As String, curr As Worksheet, n As Long Dim ColB As ... | |
| import date from csv file | 7/29/2011 |
| Q: i came to you with a problem. it seems to be hard while i try to do with vba. i have a csv file. ... A: First, you're going to have to import all the data, then manipulate what you need from within Excel. ... | |
| To generate Sheets | 7/28/2011 |
| Q: I have a work book, sheet 1 contains a list of candicates with columns, In A1 contains Name, B1 ... A: Sub SheetCreation() Dim Curr As Worksheet Set Curr = ActiveSheet On Error Resume Next ... | |
| To generate Sheets | 7/27/2011 |
| Q: I have a work book, sheet 1 contains a list of candicates with columns, In A1 contains Name, B1 ... A: Sub SheetCreation() Dim Curr As Worksheet Set Curr = ActiveSheet On Error Resume Next ... | |
| To generate Sheets | 7/26/2011 |
| Q: I have a work book, sheet 1 contains a list of candicates with columns, In A1 contains Name, B1 ... A: Sub SheetCreation() Dim Curr As Worksheet Set Curr = ActiveSheet On Error Resume Next ... | |
| Excel: comparing values in cells | 7/25/2011 |
| Q: Mr. Umlas, I am trying to check cell values and find instances when there are increases of sets of ... A: Press alt/F11, then Insert/Module, then paste in the code below. Once that's done, enter something ... | |
| Selection Between two dates | 7/20/2011 |
| Q: How to count the values with the below criterias, 1. calls made on 7/20/2011 between 7/20/11 12:01am ... A: You shouldn't really work with workbooks with #REF! errors. But there is a workaround. Define a ... | |
| array, variant operations in vba | 7/19/2011 |
| Q: hopefully simpleassume two 10x1 ranges of integers in A1:A10 and B1:B10 of Sheet1. i'd like to write ... A: Try this (turned off calculation & screen updating), used with/end with blocks... see if this helps: ... | |
| matching | 7/18/2011 |
| Q: i tried to use the following codes to delete matching row from 'wat' sheet. but it is not working ... A: Sub Deleteit() Application.ScreenUpdating = False Dim i As Integer Dim j As Integer For i = ... | |
| matching | 7/17/2011 |
| Q: i tried to use the following codes to delete matching row from 'wat' sheet. but it is not working ... A: You're ALWAYS comparing den!B2 to wat!B2. The comparison never changes. Change If ... | |
| Inventory Formulas | 7/15/2011 |
| Q: Is there aleady a free downloadable spread sheet that will keep track of inventory, such as for ... A: Say the initial length is in A2 and your lengths are in A3:T3. in B5 enter =LARGE($A$3:$T$3,ROW(A1)) ... | |
| Combining hundreds of cell values into one cell | 7/15/2011 |
| Q: I need a VBA routine to make cell WA5 to show the concatenated values of B5:VZ5. Then make WA6 show ... A: Function Combine(Rg As Range, Optional tf As Boolean = False) As String Dim Stg As String, Stg2 As ... | |
| Inventory Formulas | 7/15/2011 |
| Q: Is there aleady a free downloadable spread sheet that will keep track of inventory, such as for ... A: Say A1 has Initial Length B1 has Remaining Length C1 has "# Pieces" D1 has "Length" then C1 & D1 ... | |
| VB Timestamp...two codes that won't work | 7/12/2011 |
| Q: I'm trying to do a timestamp when I click in a cell. I found some code for that on this site ... A: ActiveCell.Column = 8 And ActiveCell.Column = 81 is impossible. The active cell's column can't both ... | |
| 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: After you enter these functions in a VBA module, put something like this in the worksheet: ... | |
| Excel Formula | 7/11/2011 |
| Q: Hope you had a great weekend. If D21 is marked with an "x" I want to A21 to say "Paid" is that ... A: The syntax for an IF statement is =IF(condition,What to see if true, what to see if false) If you ... | |
| Enter time in Excel | 7/10/2011 |
| Q: I need to enter time on an Excel spreadsheet, but I want to see if, instead of entering 7:00, is ... A: Sub Worksheet_Change(ByVal Target As Range) If Target.Column >= Range("AJ1").Column Then Exit Sub ... | |
| Undo Macro | 7/6/2011 |
| Q: I'm just looking for some advice; I'm designing an excel sheet for a friend that copies a selected ... A: If you carefully position the button so that the top is clearly in a particular row, then you can ... | |
| Undo Macro | 7/6/2011 |
| Q: I'm just looking for some advice; I'm designing an excel sheet for a friend that copies a selected ... A: There's really no undo macro. You're going to have to write one which pretty much undoes what your ... | |
| on time macro | 7/5/2011 |
| Q: i am using well known ontime event macro in my workbook to fetch sql data. It runs base on ... A: now+timevalue("01:05:00") will run in 1 hout and 5 minutes from now, not 1 hour from now. If your ... | |
| Enter time in Excel | 7/2/2011 |
| Q: I need to enter time on an Excel spreadsheet, but I want to see if, instead of entering 7:00, is ... A: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| Formula | 6/30/2011 |
| Q: Its been a few years since I have done anything in Excel, so I'm pretty rusty... what I am trying to ... A: In M2: =IF(J2="Long",K2-L2,L2-K2) and fill down In Q2: =IF(J2="Long",N2-K2,K2-L2) and fill down In ... | |
| Return cell value once | 6/29/2011 |
| Q: I would appreciate if you could help me on the below issue. I have got a column in MS Excel with ... A: You need a user-defined function. Press Alt/F11, then Insert/Module, then enter this: Function ... | |
| A List that Removes Blank Cells, Duplicate Data, and Automatically Updates | 6/28/2011 |
| Q: I was curious if there is a better solution than this. I am sure there has to be. My code does work ... A: If you have column N available this will work fine: Right-click the sheet tab, select View Code, ... | |
| workbook protection | 6/27/2011 |
| Q: i wrote a macro which works greatly for me. now i want to share it with one of my friends. i want ... A: You can use VBA and protect the vba code so one can't see it... Let's assume your name is "Samuel ... | |
| If formula using vlookup | 6/27/2011 |
| Q: If cell P4 (a negotiated fee)is blank, I need to lookup the value of K4 (the product type) to return ... A: There's nothing wrong with the formula, so maybe there's something wrong with the range named 'fee" ... | |
| Excel - VBA for showing Cell Ref | 6/25/2011 |
| Q: I would like to have a single column in which each cell shows its own cell ref. The function ... A: you can put this in cell A1 and fill down to A100: =CELL("address",A1) then you can right-click the ... | |
| Randomly distribute a list 6 times with no repeats... | 6/24/2011 |
| Q: I was actually hoping for a formula that could pull from my list of 90 attendees and randomly assign ... A: You can have a list of the values in one column, and in the next column you have =RAND(). You sort ... | |
| convert string date to value | 6/24/2011 |
| Q: column A of sheet1 contains 'date' i want to convert this 'string date' to 'value' by using ... A: I assumed since I saw a date like 28/04/2011, that you were using a system with dd/mm/yyyy dates ... | |
| Question | 6/21/2011 |
| Q: Right now I have to click a cell and then run the macro in order for it to run in the cell that I ... A: Try this: Sub SelectDate() Dim i As Integer, cl As Integer, rw As Integer For i = 1 To 300 ... | |
| Creating Simple Forms for Invoice Tracking | 6/20/2011 |
| Q: I will preface my question with a warning that I am beginner excel user, but I think what I'm trying ... A: I don't know what you mean by "like the form to recognize when a particular code is entered." To ... | |
| Template to chart data from csv files | 6/19/2011 |
| Q: I've tried recording a macro to do the following myself but I have limited knowledge of excel and ... A: sure - bobumlas@hotmail.com, use subject of "AllExpertsQ" ================================== Are ... | |
| EXCEL OUTPUT ORGANIZATION | 6/19/2011 |
| Q: I am using EXCEL to analyse data on traffic engineering. A road section divided into segments has ... A: seems like if you use a pivot table, you wouldn't need to reorganize the data at all. If this ... | |
| code works in one workbook but not another | 6/19/2011 |
| Q: I am learning vba on my own so my terminology may sound a bit weird, but ... In excel 2003, in a ... A: My guess is you put the code in the wrong place. Right-click the sheet tab of the sheet which needs ... | |
| Excel 2010: How to automatically update spreadsheets | 6/17/2011 |
| Q: Hopefully you will know the answer to my questions- no one else has. I work with a program which ... A: I think I would need to see the real (perhaps shortened) workbook. It's not clear if this is all one ... | |
| Excel | 6/17/2011 |
| Q: I have a spreadsheet that has 181 detail sheets and one summary sheet. I'd like to link specific, ... A: OK, maybe I'll marry you. Or, you could attend a class or 2: ================================== Are ... | |
| Excel | 6/17/2011 |
| Q: I have a spreadsheet that has 181 detail sheets and one summary sheet. I'd like to link specific, ... A: if you tell me cell addresses to link from (assuming they're the same on each sheet) and the name of ... | |
| Excel | 6/17/2011 |
| Q: Sir, I have started learning VBA in excel. I have created a simple function which works perfectly ... A: make it an addin -- save it with the xlam extension, and either keep it in the directory which is ... | |
| Excel 2010: How to automatically update spreadsheets | 6/16/2011 |
| Q: Hopefully you will know the answer to my questions- no one else has. I work with a program which ... A: does this need to be a formula on the clients spreadsheet pointing to the location of the intake ... | |
| Now formula updates all the Cells, | 6/16/2011 |
| Q: I want to enter a X into a box and then the adjacent cell will fill with the date and time. The way ... A: assuming the column with the X is column C, right-click the sheet tab, select View Code, enter this: ... | |
| Colourformatting cells | 6/15/2011 |
| Q: How do I make cells change their colour (red,yellow and green)based upon dates? What I want to do is ... A: Select the cells which should change color. Use Conditional formatting/New Rule and for each rule ... | |
| Question | 6/14/2011 |
| Q: Right now I have to click a cell and then run the macro in order for it to run in the cell that I ... A: Sub SelectDate() Dim i as integer For i = 1 To 300 If Month(Cells(4, i)) = Month(Date) ... | |
| Question | 6/14/2011 |
| Q: Right now I have to click a cell and then run the macro in order for it to run in the cell that I ... A: Sub SelectDate() For i = 1 To 300 If Month(Cells(4, i)) = Month(Date) And Day(Cells(4, ... | |
| Template to chart data from csv files | 6/14/2011 |
| Q: I've tried recording a macro to do the following myself but I have limited knowledge of excel and ... A: Sub DoIt() sh = ActiveSheet.Name Rows(8).Clear Columns(2).Clear Range("B9").Select ... | |
| Inserting Rows with formula and format between specific location | 6/13/2011 |
| Q: I have a worksheet on which rows 27 to 40 have been assigned for data entery. I have to add data ... A: First, assign the name "Here" to the first row after the formatted cells, then run this code: Sub ... | |
| Inserting Rows with formula and format between specific location | 6/9/2011 |
| Q: I have a worksheet on which rows 27 to 40 have been assigned for data entery. I have to add data ... A: Sub Inserter() norows = Application.InputBox("How many rows to insert?", Type:=1) If norows ... | |
| "XIRR" formula problem | 6/7/2011 |
| Q: Bob, I'm using Excel 2003 with Windows XP3. I extracted and simplified part of a sheet in a ... A: yes - send to bobumlas@hotmail.com, use subject of "AllExpertsQ" ================================== ... | |
| Macro for paste special Transpose | 6/6/2011 |
| Q: Need help ! here's what I am trying to accomplish. I have a file (Workbook) with 2 sheets named as ... A: Sub Nikhil() n = 16 For i = 19 To Sheets("Estimate").Range("IV5").End(xlToLeft).Column Step 2 ... | |
| formulas, pivot tables | 6/6/2011 |
| Q: It wouldn't let me ask any more follow up questions. How would I do a pivot table? Would it ... A: If the pivot table werer based on a table, then all that would be needed is a simple macro that ... | |
| Displaying a Valid Production Task | 6/6/2011 |
| Q: I would like to ask for help on how I can make a formula that show this function. If value of column ... A: Now the original file image is missing. Please send both the sample and the valid lists to me at ... | |
| Macro for paste special Transpose | 6/3/2011 |
| Q: Need help ! here's what I am trying to accomplish. I have a file (Workbook) with 2 sheets named as ... A: Sub Nikhil() n = 16 For i = 19 To Sheets("Estimate").Range("IV5").End(xlToLeft).Column Step ... | |
| Drop down menus- conditions | 6/2/2011 |
| Q: wondered if you could point me in the right direction for instructions on how to do the following. ... A: If the A,B,C list is in A1, then you still need to set up the other lists somewhere and give a name. ... | |
| Creating dynamic 2D charts | 6/2/2011 |
| Q: I'm trying to create a dynamic chart using the OFFSET and COUNTA function in the "Define Name" ... A: Define a name like Companies which is =OFFSET($H$13,0,0,COUNTA($H:$H),1) and define a name for the ... | |
| Mandatory Cells (again) | 5/31/2011 |
| Q: Hope you are well In reference to a previous answer you gave on mandatory fields. ... A: the code as is checks for a "1" in column B. If you want it to ensure there's something in X when ... | |
| Mandatory Cells (again) | 5/31/2011 |
| Q: Hope you are well In reference to a previous answer you gave on mandatory fields. ... A: The code MUST be in the "ThisWorkbook" module which you can access by double-clicking "ThisWorkbook" ... | |
| insert equation into legend | 5/31/2011 |
| Q: iam trying to insert equation into the series name to display on the legend in charts but it does ... A: =SERIES("SERIES NAME GOES HERE, IN QUOTES",...) Seems like sub/superscripts aren't allowed in the ... | |
| Query in excel | 5/31/2011 |
| Q: I have started learning excel 4-5 months back I Have completed several levels in it but I am not ... A: You can do a google search on "useful circular reference excel" for many examples, but here's one: ... | |
| macros tested and works at home but not at work. | 5/27/2011 |
| Q: I have a workbook where the data is export from a database on a daily basic. I created macros to ... A: Can't help without actually seeing the problem workbook. Send me a copy and tell me what cell(s) ... | |
| Zero in Excel Formula | 5/26/2011 |
| Q: I found this post of yours (below) and needed further help. I have used the automatic sum formula ... A: not sure what help you're looking for, but feel free to send me a copy of the wb and indicate which ... | |
| Displaying a Valid Production Task | 5/26/2011 |
| Q: I would like to ask for help on how I can make a formula that show this function. If value of column ... A: Sorry it took awhile to respond, but I've been trying to figure out what you really want and I don't ... | |
| Excel 2007 | 5/25/2011 |
| Q: I have a list of sites (more than 100) that I would like to be able to randomly color code. The ... A: This routine assumes the values are in column A and column B is empty: Sub Coloring() ... | |
| out of spec icon set | 5/25/2011 |
| Q: I want to use the icon sets in Excel 2007 to show a symbol whether a measurement number is within ... A: It seems like you're wanting to use icon sets for column A depending on values in columns B:D, so ... | |
| ms excel 2003 conditional formatting with countif function. | 5/25/2011 |
| Q: could you please help me how to create a macro in excel 2003 to do conditional formatting with ... A: I need more info. First, you really want the conditional formatting formula to be one which returns ... | |
| excel graph/chart | 5/24/2011 |
| Q: H! I'm having trouble switching the x and y axis in my graph. I'm doing line graph for a project but ... A: If you're saying 2008 version you must be using a Mac, and I'm not that familiar with that. But for ... | |
| Creating Specific numbers for specific cell values | 5/24/2011 |
| Q: I work at a radio station and we receive network spots everyday that we must go through and turn ... A: If your sample list starts in B1, enter these formulas and you'll see the answer you want in column ... | |
| Creatting Master Sheet | 5/24/2011 |
| Q: I need help in excel 2007. I have 30 sheets in one excel in which I want to create a master sheet ... A: Make sure the sheet is named Master, then run this macro: Sub CreateMaster() ... | |
| Seeking VB code help for reading a column | 5/23/2011 |
| Q: I am trying to write one macro (or other simpler way) that reads value from colum B and for unique ... A: Based on your description, you cn just copy ALL of column A into AG and apply the formula you gave. ... | |
| Working with pivot tables | 5/23/2011 |
| Q: Sample Red 10 Black 2 White 3 i have data in a table, and have made a pivot table ... A: You need to use the function GetPivotData which has many parameters, but if you click inside the ... | |
| Creatting Master Sheet | 5/22/2011 |
| Q: I need help in excel 2007. I have 30 sheets in one excel in which I want to create a master sheet ... A: What's wrong with Copy/paste? Are you looking for something which will automatically update as the ... | |
| Excel (2007) disabled items list | 5/21/2011 |
| Q: This will probably seem off the wall, but I have found a reason to want to add an excel workbook to ... A: It looks like in that dialog, you can select the disabled items and click the Enable button and then ... | |
| Excel 2007 VBA: View Headings on/off | 5/18/2011 |
| Q: Bob, In Excel 2007 View Tab, there is a checkbox option for viewing all of the row headings and ... A: The only way to permanently remove the row & column headings for that workbook is by using XML code ... | |
| Excel Question | 5/18/2011 |
| Q: I imported the spread sheet for a weather forecast into excel. I am trying to create an offset ... A: If the dates are in A and the item you want to get is in L, you can use either of these 2 formulas ... | |
| Userform Comboboxes Percent Typing | 5/18/2011 |
| Q: I have created a userform, with a number of comboboxes. In these comboboxes they can pick data ... A: can you send me the userform (or workbook) and I'll have a look... send to bobumlas@hotmail.com, use ... | |
| Using formula in Excel file | 5/17/2011 |
| Q: I have a form in which there are 2 cells that should get information by using P\N I have succeeded ... A: 1 - I have no idea what P\N is 2 - "...drop down list entered to cell Card P]N =F8..." seems ... | |
| Auto assign serial number every time Excel file is opened | 5/17/2011 |
| Q: I am trying to create a serial number every time an excel Template file is opened I actually need ... A: Put the starting Serial# in a cell on a new sheet and hide the sheet. Let's say that's Sheet2 cell ... | |
| Cash flow chart | 5/13/2011 |
| Q: Mr. Umlas, I hope you can help me out with this seemingly simple problem: I just want to create a ... A: If you send a sample wb to me at bobumlas@hotmail.com I'll show you what needs to be done (a lot ... | |
| Cash flow chart | 5/13/2011 |
| Q: Mr. Umlas, I hope you can help me out with this seemingly simple problem: I just want to create a ... A: Select the x-axis, format it (windows is ctrl/1), in the Axis options, there's a "Interval between ... | |
| Visibility | 5/12/2011 |
| Q: I have two bitmap pictures I inserted on a worksheet. Can I make the images appear or disappear ... A: Rightclick the tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As ... | |
| Compare Question | 5/10/2011 |
| Q: Bob, I am attempting to find a formula which compares part of one cell to another to check whether ... A: Your 2nd example is a bit misleading, since there is no M123A11 to search for, but they DO match ... | |
| VBA - replace cell value | 5/9/2011 |
| Q: I have about 1,000 rows of data. I need to search a specific range (ie: H1:H1,000) for specific text ... A: Select column H (Or H1:H1000). Use Ctrl/F to find "Call Me". Assuming you have Excel 2007 or 2010, ... | |
| Excel help | 5/6/2011 |
| Q: Sir, I have a range let's say A1:A10, of which each cell is linked to some other cells. At a time ... A: in cell B1, ctrl+shift+enter this formula: ="A"&MATCH(TRUE,ISTEXT(A1:A10),0) -------------- ... | |
| Sort the number of occurences by year | 5/6/2011 |
| Q: I have a list of earthquakes in which column A1 is in YYYY MM DD format (US Geo Survey table). I ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Weighted percentage | 5/5/2011 |
| Q: My question is more about how to correctly calculate something, versus how to use Excel. Hope you ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Sheet2 to Sheet1 | 5/5/2011 |
| Q: I need a little help in coping the data from sheet 2 to sheet1. In sheet2 I have data in the form ... A: On Error Resume Next ColRP=Sheet2.Rows(1).Find("RP",lookat:=xlwhole).Column ... | |
| Sheet2 to Sheet1 | 5/4/2011 |
| Q: I need a little help in coping the data from sheet 2 to sheet1. In sheet2 I have data in the form ... A: ColRP=Sheet2.Rows(1).Find("RP",lookat:=xlwhole).Column ... | |
| Search Functions > 3D VLOOKUP | 5/4/2011 |
| Q: How are you? hope you are doing good. I found a formula designed to find a single instance of a ... A: Easier with VBA. Enter this function into VBA, then use =Sheet("Name A",row(A1)) and fill down ... | |
| Sheet2 to Sheet1 | 5/4/2011 |
| Q: I need a little help in coping the data from sheet 2 to sheet1. In sheet2 I have data in the form ... A: You need to find the columns & the right row: ColRP=Sheet2.Rows(1).Find("RP").Column ... | |
| Cross Referencing | 5/2/2011 |
| Q: My boss has a whole lot of folders with different documents in them. He can never remember what ... A: Put this in a module, then run "Folders" Is that what you're looking for? ... | |
| Need Formula | 5/2/2011 |
| Q: i need a Formula to Count "Maximum" value from 2 different columns, which falls under 2 cells ... A: Sorry, even with the picture, I don't get it. Max of G & H doesn't help, because in your example G & ... | |
| Need Formula | 5/2/2011 |
| Q: Can i have a formula to count the "Maximum" date value from 2 columns and from 2 given dates. i ... A: I'm assuming you're in R1C1 mode, not A1 mode or this won't be valid. Next, I don't know what you ... | |
| need help with IF statement | 4/29/2011 |
| Q: I need a formula to perform the following function and I can’t seem to get it to work. If cell R24 ... A: Right-click the sheet tab for CV001 and put in this code: Private Sub Worksheet_Calculate() If ... | |
| Macros - What if | 4/27/2011 |
| Q: I am an Excel Guru in many ways, but I would love to write macros using what ifs. For example; ... A: On Error Resume Next means that if you encounter an error, don't show that annoying Macro error. ... | |
| Update another sheet from a summary sheet | 4/26/2011 |
| Q: I have a question that has been bothering me for ages! I am using Excel 2008 Mac and I have a ... A: Turn iterations on & to 1. In cell A1, enter =A1+1 (obvious circular reference). Now press F9. Each ... | |
| Index/ match formula | 4/26/2011 |
| Q: I am absolutely stumped on how to customize this formula for my use...basically I need to validate ... A: I don't quite follow what's the criteria for returning Yes/no or True/False -- as you have it, ... | |
| 'curser' customization | 4/26/2011 |
| Q: Can the look of the selected cell be customized? ie: when ever cell a15 is selected, it will ... A: As long as you're not "permanently" using color on other cells, this works: Right-click the sheet ... | |
| Update another sheet from a summary sheet | 4/25/2011 |
| Q: I have a question that has been bothering me for ages! I am using Excel 2008 Mac and I have a ... A: You can avoid the circular reference if you turn on iteration, and use a iteration count of 1. ... | |
| work scedule in excel | 4/25/2011 |
| Q: I am looking for help where my time cell contains a value of 6-2 meaning 6:00 am - 2:00 pm and want ... A: Don't know what your definition is for lunchtime, so you can subtract 1 from this formula to remove ... | |
| comparing info on two lists | 4/21/2011 |
| Q: I have been assigned the task of tracking some shipping information. I have to track order date, ... A: It SEEMS like what you want, from your sample image, is to fill in column C with the routing date if ... | |
| Summary Worksheet | 4/20/2011 |
| Q: Having a workbook with about 120 worksheets (filled in forms I've imported#, I'm trying to grab data ... A: Press Alt/F11 (gets you to the VBE), then use Insert/Module, then paste this in: Function ... | |
| Matching Sheets | 4/20/2011 |
| Q: in my workbooks, there are 4 sheets. sheet 'd&f' contains Fruit Dealers Name in column b and 'types ... A: Your data is organized in such a way that it becomes quite difficult to do what you're asking. It ... | |
| Macros - What if | 4/20/2011 |
| Q: I am an Excel Guru in many ways, but I would love to write macros using what ifs. For example; ... A: It's help if you were more specific, but this will hide the rows within a range named "Larry": Sub ... | |
| Cell block | 4/19/2011 |
| Q: I need to track time utilized by my associates. For which, I have created a spread sheet where my ... A: Select C:D, use Format cells & Unlock them on the Protection tab, then protect the worksheet with a ... | |
| Problem with file | 4/18/2011 |
| Q: I have an Excel file that is a collection inventory. When I click on it to open it, a window pops ... A: Just a guess, since you should be able to drag it from the flash drive to the hard drive, but go to ... | |
| Sheet in a sheet | 4/15/2011 |
| Q: Is it possible to insert a sheet in another sheet? My main goal is to have a sheet that is divided ... A: View tab, click New Window twice. then click Arrange All, and check "Windows of active workbook". ... | |
| VBA Code | 4/14/2011 |
| Q: Hope you are Fine. this question was asked to Mr. Tom Ogilvy, then I am guided to you. Is there any ... A: Usually, workbooks can't delete themselves. But after some digging, I came up with this. In the ... | |
| Vlookup | 4/13/2011 |
| Q: Good Day Bob I need to perform a Vlookup formula to show me multiple values. EX:- look up ... A: It'd be easier to help if I had a sample workbook -- please send such a sample to me at ... | |
| How to create an excel master workbook? | 4/13/2011 |
| Q: I have 4 excel workbooks. Each workbook contains one sheet. I would like to create a master excel ... A: You can easily pick up just the values without formatting by putting references into each sheet, ... | |
| Charting | 4/10/2011 |
| Q: Bob, I'm an advanced Excel user, but am stumped on how to chart this particular data. Haven't run ... A: Excel can handle date-based category axis, but not time-based, it seems. What I would do is ensure I ... | |
| Require a formula to to me when a certain date is reached on a spread sheet. | 4/9/2011 |
| Q: Please help. I have a chart which is designed to me who has a in date ID to enter the security Base ... A: Select L3:L143 (the cells to the right of the dates). Home tab, Conditional Formatting, New Rule. ... | |
| Require a formula to to me when a certain date is reached on a spread sheet. | 4/9/2011 |
| Q: Please help. I have a chart which is designed to me who has a in date ID to enter the security Base ... A: You don't enter that formula into a cell -- you enter it in the Conditional Formatting Dialog -- you ... | |
| Excel Formulas | 4/7/2011 |
| Q: I have an excel spreadsheet with several formulas. Column A is used for the current date, Column N ... A: It's not blank -- it's 14 days after 1/1/1900, or 1/15/1900. To sort this at the bottom you're going ... | |
| Printing charts in office 2007 | 4/6/2011 |
| Q: Is there a fix for the chart issues in Excel and PowerPoint office 2007? My colleagues and I ... A: 1 - I'm not aware of this issue 2 - Excel 2010 has fixed MANY issues with charts, so upgrading is ... | |
| 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: What do you mean, "to get the corresponding values"? I didn't know wht you specifically asked about ... | |
| Excel Formulas | 4/5/2011 |
| Q: I have an excel spreadsheet with several formulas. Column A is used for the current date, Column N ... A: the format I gave you shows simply a dash when column A is blank. You're not being a pain, but ... | |
| printing landscape & portrait in one worksheet | 4/4/2011 |
| Q: I am trying to print a worksheet with 3 different pages - first page should be portrait, 2nd ... A: If it's for someone else, it's be easier to split it into 3 sheets, the 2nd being landscape, and ... | |
| Compare similar and difference formula | 3/31/2011 |
| Q: I wonder if there is formula to create a result to get similar and difference of the IP addresses. I ... A: You need the inexact formula, based on the 1st 6 characters I mentioned above -- repeated here: ... | |
| average if with multiple conditions | 3/30/2011 |
| Q: I use Excel 2007. "Column A has various colors entered in each cell. Column B has types of ... A: Right-click the red cell, select "Filter by selected cell's color" right-click one cell containing ... | |
| Putting expiration date in spreadsheet without using Windows Rights Management | 3/30/2011 |
| Q: I have developed Excel spreadsheets for other insurance agents and I furnish my software to about ... A: Right-click a Sheet tab, select View Code. Double-click the "ThisWorkbook" that you'll see in the ... | |
| Pivot Chart/Tables clearing existing queries | 3/29/2011 |
| Q: I have a number of pivot tables that have data inserted by date. I am trying to create a GUI that ... A: Define what you mean by "clear the pivot table" -- to me, that means doing something like this: Sub ... | |
| To Print data of all sheets of a workbook | 3/29/2011 |
| Q: I have a workbook which has a few sheets. I want to print the data of all sheets in continuity as if ... A: 1 - it CAN'T work in a regular module unless possibly YOU run it. In the thisworkbook, it runs ... | |
| To Print data of all sheets of a workbook | 3/26/2011 |
| Q: I have a workbook which has a few sheets. I want to print the data of all sheets in continuity as if ... A: Put this code in the ThisWorkbook event code -- not in a regular module: Private Sub ... | |
| Copying tabs that reference rows | 3/24/2011 |
| Q: I have a worksheet that has 200 rows of data. On my 2nd tab of the worksheet, I reference some ... A: Yes - requires a bit of code. In the Thisworkbook code, enter this: Private Sub ... | |
| To populate combobox | 3/24/2011 |
| Q: I have a user form which contains two combo boxes. In the row source of one combobox I have inserted ... A: You may have to either have that workbook open, or use a reference in the workbook with the form ... | |
| To open a book with some rows and sheets hidden | 3/24/2011 |
| Q: I have a work book which contains a macro to open another book, it is working ok, below is the code: ... A: Private Sub Set_Calendar_Click() Application.screenupdating = False Workbooks.Open "C:\Documents and ... | |
| Vlookup across multiple worksheets/workbooks | 3/22/2011 |
| Q: Could you please help to create a UDF that would allow a VLookup type function to be performed ... A: Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _ ... | |
| Visual Basic Code for Excel | 3/22/2011 |
| Q: I am a novice at using VB and am having trouble finding an 'idiots guide' answer to my query online, ... A: =AVERAGE('Residual Valuation:residual valuation (3)'!E52) or =AVERAGE('Residual Valuation:residual ... | |
| pass text strings longer than 255 characters | 3/22/2011 |
| Q: I need to pass text strings longer than 255 characters from a cell (A1) to a textbox (TextBox 1) in ... A: Try this (untested) Change ActiveSheet.Shapes("TextBox 1").Select 'On Error Resume Next With ... | |
| pass text strings longer than 255 characters | 3/22/2011 |
| Q: I need to pass text strings longer than 255 characters from a cell (A1) to a textbox (TextBox 1) in ... A: Sub Looper() Dim i As Integer Dim mytxt As String mytxt = ... | |
| To open a book with some rows and sheets hidden | 3/22/2011 |
| Q: I have a work book which contains a macro to open another book, it is working ok, below is the code: ... A: Private Sub Set_Calendar_Click() Application.screenupdating = False Workbooks.Open "C:\Documents and ... | |
| conversion of Excel formula to VBA | 3/21/2011 |
| Q: i want to write the below formula in VBA , could you please help me out? this formula is written on ... A: Excel 2003 doesn't support nested IF's over 7 deep, as you have here, whether it's done directly or ... | |
| conversion of Excel formula to VBA | 3/21/2011 |
| Q: i want to write the below formula in VBA , could you please help me out? this formula is written on ... A: Sub PutInFml() ActiveCell.FormulaR1C1 = "=IF(RC[-60]="""",""01- No SOW received ... | |
| Need Steps to solve | 3/21/2011 |
| Q: Question I have a big workbook with several sheets. Basically, column A contains a list of numerical ... A: for the details you're asking for I'd need to know which version of Excel you're using, as the ... | |
| copy diff range in diff sheets based on cell value | 3/16/2011 |
| Q: May I request your help on below scenerio for excel07 Thanks in advance and with best regards. Sl ... A: Right-click the sheet tab, select View code, put this in: Dim OldValue Private Sub ... | |
| copy diff range in diff sheets based on cell value | 3/16/2011 |
| Q: May I request your help on below scenerio for excel07 Thanks in advance and with best regards. Sl ... A: Sorry, I don't follow at all what you're trying to do. How does a cell have a "previous value"? How ... | |
| Excel help | 3/15/2011 |
| Q: I have four workbook. 1st Workbook Named as Cash Book having 1 Worksheet, 2nd Workbook Named as Bank ... A: The master workbook should have one sheet for each of the other workbooks' sheets. For each sheet in ... | |
| Visual Basic Code for Excel | 3/14/2011 |
| Q: I am a novice at using VB and am having trouble finding an 'idiots guide' answer to my query online, ... A: Sorry, still unclear -- suppose the "designated value" is in cell F4 of each of the relevant sheets. ... | |
| further to earlier question "deleting rows with Vb" | 3/14/2011 |
| Q: I'm after something along these lines, not necessarily exactly it but something as concise ... :) ... A: Private Sub CommandButton1_Click() Activesheet.Unprotect If ... | |
| Auto Insert Delete Rows in Worksheet | 3/12/2011 |
| Q: I've come thus far.. (pls refer code under) ... What I need to understand and know is how to do ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Excel 2003 vba code re: start date and 3 yr alert | 3/10/2011 |
| Q: Need vba code (I'm thinking... person needs to get background check every 3 years and I want to be ... A: You mention your "initial VBA code" but you didn't show me any code. Conditional formatting doesn't ... | |
| Generating dates using Excel 2003 | 3/9/2011 |
| Q: I need to create a spreadsheet to that will generate a date, minus 3, 5, 8 and 10 days past the ... A: Not trivial. You probably need a table of the dates in a year (or 2) which consist only of weekdays. ... | |
| Adding changing value to a time | 3/8/2011 |
| Q: I am stuck.. I would like to add a Changing value to a start time. i.e. for instance start time ... A: 1 - the use of SUM in your example is superfluous -- =F2*1440+I3 is the same as =SUM(F2*1440)+I3. 2 ... | |
| copying values from multiple columns and pasting it to single column | 3/7/2011 |
| Q: hope you can give me a macro for the above... in my worksheet I have several columns with lots of ... A: Sub CopyCols() 'Untested for i=1 to range("IV1").end(xltoleft).column ... | |
| Simple excel question | 3/5/2011 |
| Q: I am trying to work out a formula that will count the people entering and leaving a door within a ... A: What's the criteria for knowing if someone is entering or leaving? Also I'd need to know the way the ... | |
| Excel 2007 - How to compare differents details easy way | 3/2/2011 |
| Q: I have this problem whereby I need to do a comparison each month using different data. for example, ... A: I need more details -- better would be for you to email me the workbook -- send to ... | |
| Excel Toolbars | 2/28/2011 |
| Q: I am having issues keeping my toolbars arranged and visible after I change them around. I have 1 ... A: The security toolbar needs to be hidden using VBA code - needs to be in the auto_open code of your ... | |
| large data | 2/26/2011 |
| Q: "I have a large data of about 700 columns & 4000 rows relating to the sales.Great of columns(fields) ... A: A table as in an excel 2007 or 2010 table? Or an Excel 2003 Data table (which is slow). If a 'real" ... | |
| macro | 2/25/2011 |
| Q: please help me. master.xls will be the master file. new1.xls will be the other file new2.xls will ... A: Now it makes sense! Sub Grace() Workbooks.Open "New1.xls" ... | |
| macro | 2/25/2011 |
| Q: please help me. master.xls will be the master file. new1.xls will be the other file new2.xls will ... A: Why bother pasting the first set if the 2nd overwrites it? I don't get it (makes no sense). Having ... | |
| Hundreds of links in Excel need changed | 2/25/2011 |
| Q: .. I have an several Excel spreadsheets linked together. Hundreds of rows. I need to change a ... A: Press alt/F11, use Insert/Module, paste this code in: Sub Cvt() For Each cl In Selection ... | |
| Batch searching of excel | 2/24/2011 |
| Q: I want to search a large excel 2000 spreadsheet (of the order of 15,000 rows by 25 columns) against ... A: Say your list of words is in L1:L300, for example, select A1:Y25000, use conditional formatting with ... | |
| Unlocked cells automatically lock-Why? | 2/24/2011 |
| Q: Why does excel lock cells when they aren’t lock? I think this is an error on Microsoft’s software, ... A: No idea why it does that, but I have 2 workarounds. 1 - right-click in the cell and select Paste ... | |
| to create AddInn | 2/17/2011 |
| Q: I have 4 different codes to delete empty rows depending upon certain conditions which I have copied ... A: Only if the old-style 2003 addin created a menu item would you see it in the addins tab. .xla or ... | |
| Excel Macro | 2/17/2011 |
| Q: I want a macro which should match a given value in a cell to cells in a column(First column of a ... A: Sub Amol() Dim N as Integer on error resume next ... | |
| Excel How To | 2/17/2011 |
| Q: I'm trying to do several things in excel and I'm stuck on a few points. Bear with me . . . 1) ... A: Answer to Q1 is a user-defined function. If the column of values is in A1:A10 and the frequency is ... | |
| Excel Macro | 2/16/2011 |
| Q: I want a macro which should match a given value in a cell to cells in a column(First column of a ... A: Assuming the given value in a cell is in E1 and the first column of the table is A, and desired new ... | |
| countif | 2/16/2011 |
| Q: Cell A1:A10 Orannge,Blue,Blue,Purple,Pink,Green,Blue,Blue,Blue,Blue respectively, now how do i count ... A: User-defined function: Function CountBackwards(rg As Range, sample) As Integer Dim n As ... | |
| Expression for multiple variables | 2/16/2011 |
| Q: I need to find an equation for Time (T) which varies with variables X, Y and Z. That is, Time T is a ... A: This seems to be a job for Solver, and it also seems that T=ax^2+bx+c does not yield a solution. So ... | |
| Copy / Paste of Drawing | 2/15/2011 |
| Q: I have created a picture of a window screen in Excel using lines and shapes. The picture is on a ... A: A cell formula won't show a picture. However, you can have a picture have a formula which would ... | |
| Span Text Across Columns | 2/15/2011 |
| Q: I have a spreadsheet in which I use IF statments to insert text into the appopriate cell based on ... A: How about Shrink to fit? You can't get it to flow across columns if there are formulas in the ... | |
| to create AddInn | 2/15/2011 |
| Q: I have 4 different codes to delete empty rows depending upon certain conditions which I have copied ... A: You can turn ANY workbook into an addin simply by setting the IsAddin property of the thisworkbook ... | |
| Macro for formula | 2/15/2011 |
| Q: Please provide me a Worksheet_Change macro for the following conditions: If a cell in Col. C ... A: You have conflicting instructions for when Col C >100. Here's the first 2 conditions satisfied -- ... | |
| Excel... if statements or something more? | 2/15/2011 |
| Q: Bob, I am absolutely stumped on something. I have attached a screenshot of what I am working with. ... A: In C5: ... | |
| lookups on separate tabs | 2/14/2011 |
| Q: Here is a simple example of what I'm trying to do. Let's say Cell A1 in Sheet1 Cell A1 has the ... A: Use this user-defined function: Function FindC25(rg As Range) For Each x In Sheets If ... | |
| conditional copy-paste-delete | 2/14/2011 |
| Q: your help on Excel 2007 pls... If sheet1!a1=value v1,copy sheet2!range a1:d3 into sheet3!range ... A: 1 - Still difficult to answer you. I need specifics. If user selects v1 in A1, is that really "v1"? ... | |
| Excel Formua | 2/14/2011 |
| Q: I can't find an answer to this problem online. Column A has 30 cells with a three digit value ... A: The formula is still fine. The problem is, a 3-digit # as the 2nd number looks to Excel like a ... | |
| conditional copy-paste-delete | 2/14/2011 |
| Q: your help on Excel 2007 pls... If sheet1!a1=value v1,copy sheet2!range a1:d3 into sheet3!range ... A: There are a lot of conflicts in what you're asking. 1 - can't copy A4:D9 into A1:D4 -- 6 rows into ... | |
| Running total | 2/13/2011 |
| Q: I have created 2 rows with 4 cells with same data in a excel sheet.(A1=14,B1=5,C1=4, D1=(A1+B1-C1) ... A: Can't be done with a formula because you'd get circular references. You can use a worksheet_change ... | |
| Find and replace a pattern of cells in a single column | 2/12/2011 |
| Q: Basically, all I need is a program that can look through a single column of data, find a pattern of ... A: Sub LookForPatterns() For i = 1 To Range("H65000").End(xlUp).Row If Cells(i, 8).Value = ... | |
| counting cells in XP | 2/11/2011 |
| Q: Please can you tell me of a formula that will add a column of cells together without including ... A: =COUNTIF(A1:A10,"<>0") but this would include formulas. To count cells which are not formulas and ... | |
| Item Inventory Codes | 2/9/2011 |
| Q: Using Excel version 2007 Right now our inventory item codes in Quickbooks are a hodge podge. I ... A: =SUMPRODUCT(N(LEFT($B$2:$B$6,2)="BF"),D$2:D$6)+SUMPRODUCT(N(MID($B$2:$B$6,5,1)="I"),D$2:D$6) In ... | |
| Item Inventory Codes | 2/8/2011 |
| Q: Using Excel version 2007 Right now our inventory item codes in Quickbooks are a hodge podge. I ... A: Given your example, codes in A2:A6, data in B2:K6 (which you should adjust to fit what you really ... | |
| excel column matching | 2/6/2011 |
| Q: I want to match numbers in two columns. For example I have a list of 1200 numbers in column a. In ... A: The 2nd solution is pretty easy. Follow these steps: 1 - in an unused column, say G, enter ... | |
| Export data to .dbf | 2/6/2011 |
| Q: Sir, I am using Excel 2003 and Visual FoxPro 6.0. In an excel file there are a few fields from ... A: Are you saying that some of the fields are longer than the required lengths and you want them ... | |
| Check name | 2/4/2011 |
| Q: Sir, I would like to copy the Duplicate names (which is at column C ) to another sheet along with ... A: Untested: Sub CopyDups Set Curr=ActiveSheet Set Dups = Worksheets.Add Curr.Activate j=1 ... | |
| excel column matching | 2/4/2011 |
| Q: I want to match numbers in two columns. For example I have a list of 1200 numbers in column a. In ... A: Sorry for the delay. I've been toying with this for awhile and there's something I'm missing. If ... | |
| Merge and compare two spreadsheets | 2/3/2011 |
| Q: I have two spreadsheets with price lists from our vendors. They are in separate WBs but I can put ... A: Sub Merger() Dim n As Integer, i As Integer, k As Integer Dim Nw As Worksheet, Od As Worksheet, ... | |
| How i can find numbers | 2/3/2011 |
| Q: I have a excel worksheet, worksheet name data entry, which has thousand of data entries. I have an ... A: I don't know what you mean by "find my value" If it were found, it would return the same value in ... | |
| Duplicates in Excel | 2/3/2011 |
| Q: My spreadsheets have several columns of data but column D represents a job number and column F ... A: Assuming you're looking at D1:D100 (and F1:F100), then in an empty cell, say H1, enter this formula ... | |
| Excel formulas | 2/1/2011 |
| Q: I have MS office 2000 Pro. Win XP I run a small courier service. I want to make a sheet with ... A: Sorry for the delay -- can you send a file for me to look at and I'll put the formulas you request ... | |
| Excel VBA Increment row number & Loop code | 1/31/2011 |
| Q: I'm using Windows Excel 2003. This formula ... A: So if you run this 100 times you want the formula to be B113 and C113? If that's the case, then you ... | |
| excel-finding totals of monthly rate | 1/29/2011 |
| Q: sir, I have rate payable table(A2:B15) in excel where A2:A15 is used for entering date from ... A: I don't quite follow, and the image is not really helpful -- pls send a sample wb to me at ... | |
| excel vba - combine columns into one list | 1/28/2011 |
| Q: I have a spreadsheet with a range of columns of zip codes. There are 4 additional columns with data ... A: try this: Sub Rearrange() n = 2 Set this = ActiveSheet Set answer = Worksheets.Add ... | |
| Excel VBA Programm Issue | 1/28/2011 |
| Q: I have a program written with VBA that i was assisted with through this site last year. However, ... A: I'm not sure I understand the issue -- sheet tabs can't contain slashes, hence the dashes are needed ... | |
| consolidate all worksheets into a years end rollup | 1/27/2011 |
| Q: Bob: How does one take all 12 months of a P & L I created and roll all the data into one years end P ... A: To add up cell E3 in all 12 tabs, let's say they're named Sheet1 thru Sheet12, then you create a new ... | |
| Converting to time without typing in a colon | 1/26/2011 |
| Q: I have to do an annual (fiscal year) spreadsheet that shows the time the doctor was in to see the ... A: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| Can you please help me | 1/26/2011 |
| Q: I'm currently using Excel 2003 and looking for the right formula to help me list all possible 6 ... A: This is not at all trivial. Put the values 5, 8, 11, etc, in F1:F15. then run the first macro shown ... | |
| Distribution Of Contents | 1/25/2011 |
| Q: I have such perumtations as (1:2:5),(2:5:6),(3:4:6) etc in a column, one in each cell. The ... A: Assuming the column containing these permutations is L, this macro does the job: (the "12" in the ... | |
| Excel Macro | 1/14/2011 |
| Q: I have a excel document that I want to run a macro on. The Macro is to 1) Find a certain key word ... A: Sub Macro1() ' ' Macro1 Macro ' ' Keyboard Shortcut: Ctrl+w ' Range("A1").Select set ... | |
| 7th Order Polynomials in Excel | 1/13/2011 |
| Q: I found your post here; http://en.allexperts.com/q/Excel-1059/Excel-charts.htm, to be EXTREMELY ... A: The coefficients from the LINEST function can create up to a 16th-degree best fit curve! For 3rd ... | |
| Validation Macro - I think | 1/13/2011 |
| Q: I would like to make a macro on closing of a workbook that checks that two cells match up. ... A: You want to prevent the save or the close or both? Assuming it's column A and the count is in cell ... | |
| VBA or IF function? | 1/13/2011 |
| Q: I've created a template in excel 2010, which has mulitple columns with values of "1s" & "0s". In ... A: How do you determine whether the 1 becomes a 5 or a 10? A formula solution is probably best & has ... | |
| Using reference pictures within Excel | 1/12/2011 |
| Q: I have created a spread sheet that allows users to select a product/part using a drop down list and ... A: send your email to me at bobumlas@yahoo.com and I'll send you a file which illustrates how it's done ... | |
| Excel Macro | 1/12/2011 |
| Q: I have a excel document that I want to run a macro on. The Macro is to 1) Find a certain key word ... A: let's say the key word is "KEY" Sub Copy7() on error resume next set ... | |
| excel - formatting | 1/11/2011 |
| Q: I have noticed in a lot of excel spreadsheet that when I past in text some of it does not show up ... A: Unfortunately, it looks quite buggy to me. Using that font, when I reduce the font size, the row ... | |
| excel - formatting | 1/11/2011 |
| Q: I have noticed in a lot of excel spreadsheet that when I past in text some of it does not show up ... A: If you're pasting all that into one cell, it may very much depend on the column width. If you widen ... | |
| excel - formatting | 1/11/2011 |
| Q: I have noticed in a lot of excel spreadsheet that when I past in text some of it does not show up ... A: If the row height of the cell you're pasting into has ever been changed, then pasting info into it ... | |
| Using an IF THEN statement to sum | 1/11/2011 |
| Q: Forecasting exercise: I have a data input sheet where users select dropdown menus to identify their ... A: The beginning of your formula doesn't make sense -- =IF('Input!V:V=' ... why the single quotes? ... | |
| index formula errors | 1/10/2011 |
| Q: Bob, I'm using Excel 2003 and Windows XP3. I use a formula repeatedly in one of my workbooks, ... A: Perhaps: ... | |
| Passing Arrays in VBA | 1/7/2011 |
| Q: Bob, I have a procedure that creates a 2 dimensional array. The procedure will be accessed multiple ... A: Not sure what yo9u mean by "...when I ReDim it inside the creating procedure it appears to no longer ... | |
| Excel - VBA | 1/7/2011 |
| Q: Mr. Umlas, I have a userform used within an Excel spreadsheet that collect data form a user that ... A: Likely you're doing a text comparison. You Dimmed dtDate as String and should have been as Dagte or ... | |
| Copy and Paste Excel Macro | 1/7/2011 |
| Q: I have created a binomial lattice model to value options and desperately need help creating a macro ... A: Sub Rearrange() i = 7 For col = 11 To 14 Cells(3, col).Resize(100, ... | |
| formula question | 1/7/2011 |
| Q: Bob, How do I get a cell that has a formula (simple addition) to display as blank, rather than "0" ... A: You can format cells as General;General; or you can mark the sheet to not show zero values (method ... | |
| Data Form Button | 1/5/2011 |
| Q: I created a data form for a spreadsheet that i am going to be training others on how to enter the ... A: I'm assuming you're not using Excel 2007 or 2010, but are using 2003. The ability to customize ... | |
| Consolidate data from multiple workbooks | 1/5/2011 |
| Q: Background Info 1. I am running Excel 2007 on Windows XP 2. I have 10+ “feeder files” and one ... A: Try this (have the Roolup file active): Sub Combine() Dim Fl As String Dim Main As ... | |
| Acknowledge a pattern Excel | 1/4/2011 |
| Q: I have a question about Excel. I am pulling figures from a workbook and entering it into a separate ... A: If you want cell A1 then A3 then A5, etc, first make a link to the A1 in the first sheet & a link to ... | |
| Vlookup formula | 1/3/2011 |
| Q: I am trying to return a value if two criteria are met from two different columns. Example: A2 is ... A: If you have John and Smith in cells E1 and E2, for example, then this is the formula you want -- it ... | |
| Insert 000 before digitas | 1/2/2011 |
| Q: when i write 0002553987 in excel and pree enter, excel remove 000. How i can keep 000 before all ... A: 4 basic ways: 1 - Format the cell as text, THEN enter the value 2 - first type a leading apostrophe ... | |
| Freezing Panes | 1/1/2011 |
| Q: Happy New Years Bob, Is it possible to freeze a pane in Excel? Now I hope you don’t think this is a ... A: You can't do exactly what you're looking to do; after all, how would row 3 look if you're looking at ... | |
| macro to combine projects into one row | 12/30/2010 |
| Q: using the following information; Sheet 1 _ Forecast A1: hangar, B1: customer, C1: start date, D1: ... A: This macro should do it -- assumes the sheets are correctly named Forecast and Plan: Sub ... | |
| save code to save sheet 1 and sheet 2 different | 12/28/2010 |
| Q: I use the following code for our customer invoices. When we X off the order then it automatically ... A: This is a bit long for a simple question, but in general you can save a worksheet with the following ... | |
| excel 2003 | 12/28/2010 |
| Q: I produce an 18 and 24 day schedule for students and instructors. the top half of the form has ... A: In the VBS, insert a module, enter this: Sub CheckBox1_Click() If Range("C30") = True Then ... | |
| Changing file name in macros | 12/27/2010 |
| Q: Is there a way to change the name of a file without going through the “Save” or “Save As” function? A: Sub ReNamer() 'Shows 2 ways - rename and move & rename... Dim OldName, NewName OldName = "OLDFILE" ... | |
| Amortization | 12/23/2010 |
| Q: Let me tell you what I'm trying to accomplish. My mother is considering taking social security a few ... A: in A1 enter Jan 2011 and fill down to A240 using the fill handle in B1 enter 2000 and in C1 enter ... | |
| Amortization | 12/23/2010 |
| Q: Let me tell you what I'm trying to accomplish. My mother is considering taking social security a few ... A: You don't need excel for this -- let's assume her monthly take home were 2000 if she waits, ... | |
| macro variable ranges | 12/22/2010 |
| Q: I have a list of 15 names on the vertical axes and to the right of each name I have 10 blocks of ... A: Sub Sorterdesc() For i = 1 To Range("B50000").End(xlUp).Row Cells(i, 2).Resize(1, 11).Sort ... | |
| Time sheet calculation in Excel 2003 | 12/22/2010 |
| Q: "Hi Bob, I intend to creat a time sheet in MS Excel 2003 for my staff as follows: Start Time End ... A: In your sample, you show 0600 - if you're really seeing a leading zero, then you've either formatted ... | |
| Hyperlinks to Charts | 12/20/2010 |
| Q: I've been reading up on the following literature regarding code for hyperlinking to charts in excel. ... A: The name of the Sub MUST not contain spaces, so you simply can't use Sub GotoWorldGrain Production ... | |
| macro for calculating averages of quartiles of groups | 12/18/2010 |
| Q: I have some problems with an excel sheet that has multiple groups. Each group varies between 0 and ... A: I can do it, but I need to clarify something first. Suppose the group has 5 rows. How do you want to ... | |
| combined nested IF with AND statement | 12/17/2010 |
| Q: I am try to work out this logic. I have been looking at it for quite some time but I don't know why ... A: I'd bet that where you think you have "REAL" you have "REAL " -- note the ending space. And the rest ... | |
| Calculating and adding 2 different sales tax. | 12/16/2010 |
| Q: I'm building an Excel sheet that is to add item prices which have two different sales tax amounts, ... A: Assuming the amounts are in column A and the code in column B and consists of the codes X and Y, ... | |
| Pivot Graphs | 12/15/2010 |
| Q: I have a spreadsheet with a number of pivot tables and pivot graphs. I am trying to find some VB ... A: •I don't see the relationship between the code you supplied and a dropdown list change. •I don't ... | |
| Start & Stop activity | 12/14/2010 |
| Q: I am heading 14member team . I want to know the effective projects they spend in daily basis. I need ... A: There's too much unknown for me to help you completely. Do you have a workbook set up that I can ... | |
| Macro that will create an auto backup. | 12/13/2010 |
| Q: I would like to create a macro that will perform an automatic backup of my file every 4 days (or any ... A: You need one line inserted -- see below: Sub Backup1() Application.DisplayAlerts = False ... | |
| Excel: Conditional formatting in pivot tables | 12/7/2010 |
| Q: I create pivot tables with conditional formatting in the pivot table cells using Microsoft Excel ... A: Pivot tables are so vastly different between 2003 and 2007 as are conditional formatting. I don't ... | |
| Attempted UDF | 12/3/2010 |
| Q: I was very impressed with your "This isn't Excel, It's Magic" book when I read it and I saw you on ... A: Unfortunately, financial aspects of excel aren't my strong point. However, there are a number of ... | |
| Excell scatterplot | 12/3/2010 |
| Q: ive made a scatterplot using excell 2007, but i need to add a line. I know how to add a linear ... A: Let's say your X-data is in column A and Y-data in B. Put slope in D2 and Y-intercept in E2 ... | |
| Pivot Table | 12/1/2010 |
| Q: I am workin as date entry. I will explain my question with an example. I have a table, and i make ... A: This: Row Label Customer # Job Card # Decoder count of Decoder # 1 ... | |
| EXCEL Password entry | 11/30/2010 |
| Q: G'day Bob Me again! This time I am having a proper problem instead of a panic:-) I have made some ... A: Activesheet.Unprotect on a password-protected worksheet will prompt the user for a password. If the ... | |
| switching cell info | 11/29/2010 |
| Q: I love your answer for switching cells ie Sub Switch() If Selection.Areas.Count <> 2 Or ... A: Sub Switch() If Selection.Areas.Count <> 2 Or Selection.Cells.Count <> 2 Then MsgBox ... | |
| EXCEL Password entry | 11/29/2010 |
| Q: G'day Bob Me again! This time I am having a proper problem instead of a panic:-) I have made some ... A: First, you have YES and NO buttons, not OK and Cancel. Next, it seems to work fine for me if Cancel ... | |
| Pivot Table | 11/28/2010 |
| Q: I am workin as date entry. I will explain my question with an example. I have a table, and i make ... A: I don't understand. You are shown a count of the Decoder. If your input were 1 101 19783351 1 ... | |
| Excel | 11/26/2010 |
| Q: I have been a complete idiot ( fairly normal status!!). I was fooling around with some sheets in ... A: Sheets("ENTRY340A").Visible=xlSheetVisible or in the VBE, you can click on the sheet name in the ... | |
| Calculating Radio Buttons on Excel | 11/19/2010 |
| Q: I have a questionnaire for a performance review I am putting together which has options like click 5 ... A: Each GROUP of radio buttons has a linked cell, so clicking on one in the group will make the linked ... | |
| Error in Excel | 11/16/2010 |
| Q: I have a program on Excel that uses a macro to update values throughout the day from a website. It ... A: Without seeing the file I couldn't help, and that kind of error is ofter a lot of trial & error to ... | |
| Date Tracking | 11/11/2010 |
| Q: I am tracking due dates on a project. I want to show a due date in one cell. The second cell will ... A: You can use conditional formatting, not a formula, to do what you want. If the due date cell is A1, ... | |
| Macro Issue | 11/11/2010 |
| Q: I am using Excel 2003 and as I can only have 3 conditional formats I am trying to write a macro that ... A: May not be the most efficient but it works: Sub OrangeWYellowWORD() For Each thing In Selection ... | |
| copy multiple cells and paste into one cell | 11/10/2010 |
| Q: Say cells A1=123456; A2=123457; A3=123458.......and so on. I want to easily get the data from A1-A10 ... A: Follow instructions exactly: 1 - in B1 enter =""&A1 2 - fill B1 down to B10 3 - Copy B1:B10 & paste ... | |
| unprotect won't stay | 11/9/2010 |
| Q: Using Excel 2007, we are opening password protected worksheets, and want to remove that password ... A: SOUNDS like the workBOOK is protected, based on this comment: "However, when opening the file again, ... | |
| Excel 2007 Charts | 11/8/2010 |
| Q: . I have saved a chart template in excel. I want this to be apllied on all the charts that I have ... A: Press Alt/F11 (gets you to the VBE) Use Insert/Module Put the code I gave you in there (you can ... | |
| Excel 2007 Charts | 11/8/2010 |
| Q: . I have saved a chart template in excel. I want this to be apllied on all the charts that I have ... A: You can run this macro -- first, ensure the path to your chart tempate! Sub FmtCharts() For ... | |
| Sorting data, Excel 2002 | 11/8/2010 |
| Q: I would greatly appreciate any help. I have a spreedsheet ‘table’ consisting of 26 columns and 755 ... A: If your original table starts in A1 (header), then set up your new 71-column table somewhere and in ... | |
| Charting in Excel 2007 | 11/7/2010 |
| Q: I am trying to create a chart using data from 2 separate spreadsheets. My first spreadsheet has ... A: Your best bet would be to set up another range which picks its values from the 2 sources as one ... | |
| Reminder alarms from excel | 11/6/2010 |
| Q: I am new to 2007 & coding. I am doing 24HR. work. I have to execute some jobs in a given time ... A: Interesting problem. If you set up your alarms with the text in column A and the time (without the ... | |
| 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: Sub oddRange() For i = 1 To Range("K50000").End(xlUp).Row thing1 = Split(Cells(i, ... | |
| 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: This will put the result in column L: Sub oddRange() For i = 1 To Range("K50000").End(xlUp).Row ... | |
| 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: Run this procedure: Sub Fillerup() Dim first As Boolean, num As Integer, AVal first = True ... | |
| Match - One to Many | 11/2/2010 |
| Q: I have a table that contains the following information: Item - Color - Size Part A - Red - Long ... A: Sub OddMatch() 'assumes table starts in A1 For i = 2 To Range("A60000").End(xlUp).Row ... | |
| VBA Checkbox Question | 11/1/2010 |
| Q: I'm trying to follow another allexpert answer and I'm getting stuck. In the attached spreadsheet, I ... A: I also recommend you use cell links to the checkboxes. Try this (if the linked cell for the fish is ... | |
| copy/past based on value | 11/1/2010 |
| Q: If a value in an excel workbook named “test”, sheet 6, called Yesterday, column B, can be found ... A: Yes, but why? Sub DoAsMacro() LRow=Sheets("Ownership").Range("A60000").end(xlup).Row ... | |
| #VALUE issue ++ ??? | 10/29/2010 |
| Q: The formula you gave still returns the information in column B, which is a date. If there is no ... A: Still can't see the screen shot. P100 is not blank. If it were, then the formula would return blank ... | |
| Formatting Header | 10/29/2010 |
| Q: Good day Bob, I'd like to format the existing text in the header and/or footer. The key word here is ... A: Because it keeps growing. After running it a few times, the new center header, for example, becomes: ... | |
| Copy and Paste | 10/28/2010 |
| Q: I have a workbook in Excel that I would like to do a macro to make my work a lot quicker. I have ... A: Sub Dan() Dim RgToCopy As Range, Dest As Range Set Dest = Application.InputBox("Click on any ... | |
| #VALUE issue | 10/28/2010 |
| Q: I'm trying to get a date for a payment in cell D100, based upon an entered date in cell B100 and a ... A: I don't follow, sorry. The formula I gave you will be blank if P100 is blank. And your VLOOKUP ... | |
| Excel 2007 | 10/27/2010 |
| Q: I am working with Excel 2007 and I have workbook that I am trying to copy the contents from a cell ... A: It can be done with a formula, but I need more info -- can you describe "pull the pipe ID from a ... | |
| Formatting Header | 10/25/2010 |
| Q: Good day Bob, I'd like to format the existing text in the header and/or footer. The key word here is ... A: With ActiveSheet.PageSetup .LeftHeader = "&""Arial,Bold""&26" & .LeftHeader .CenterHeader = ... | |
| excel charts | 10/21/2010 |
| Q: need to do with Excel 2007 a bar chart of the difference of two rows. the first cell of each row is ... A: Adjust these ranges as necessary, but if you have them available this will work. in cell X1: ... | |
| 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: Suppose your list is in A1:A500 In B1 enter: =LEFT(A1,FIND(".",A1)-1) and fill to B500. Select ... | |
| Grouping | 10/21/2010 |
| Q: 2 simple VBA questions: How do I verify if Row or Column grouping existing in a spreadsheet? How do ... A: Sub ClearOTLN() Cells.ClearOutline End Sub Function SheetIsOutlined() As Boolean n = 0 ... | |
| Variable sort and macros | 10/20/2010 |
| Q: I would like to sort a range starting at P5 through S5 and down the value of P4. If the value of P4 ... A: Blanks ALWAYS get sorted to the bottom, no matter what the sequence. You could use this code: ... | |
| Random player | 10/10/2010 |
| Q: I have players names in b5:b210 Their points in c5:c210 which are sorted in ascending order Is there ... A: You have a conflict in your instructions. You say C5 contains a point value and you also say that if ... | |
| Worksheet | 10/10/2010 |
| Q: i want to merge the data from 3 different worksheets to one worksheet. Say Worksheet 1 contains data ... A: I'm under the impression you have 3 sheets, named Sheet1, Sheet2, and Sheet3 and there's varying ... | |
| if,then/lookup? | 10/9/2010 |
| Q: I am creating a budget with several different sub-categories. I want Tab 1 to have the funds and ... A: If I understand you, Tab1 has fund amount fnd1 1000 fnd2 2000 etc. and Tab2 has something like ... | |
| Worksheet | 10/9/2010 |
| Q: i want to merge the data from 3 different worksheets to one worksheet. Say Worksheet 1 contains data ... A: in sheet 4 C1, ctrl+shift+enter: =MATCH(TRUE,Sheet1!A1:A2000="",0) in C2, ctrL+shift+enter: ... | |
| Transfer of data | 10/9/2010 |
| Q: I want to transfer data from sheet one to sheet 2. Eg- From Sheet1 A5 to Sheet2 B1 Sheet2 A10 ... A: then protect the workbook's structure with a password. Excel 2003:Format/Sheet/hide, then ... | |
| if then | 10/8/2010 |
| Q: First, thank you for taking the time to read this. Q1) I created a drop down for a 15 part number ... A: If the drop down value is in cell F2, for example, you can put this in G2 (adjust as needed): ... | |
| @protecting a work sheet | 10/7/2010 |
| Q: Sir , i had problem of protecting my workbook and you gave me answer - Yes, run this code: Sub ... A: The first part is in a regular module - In the VBE: Insert/Module, then put in the Sub HideAll() ... | |
| @protecting a work sheet | 10/6/2010 |
| Q: Sir , i have to protect my work sheet in such a way that Suppose i have made a spread sheet of ... A: Yes, run this code: Sub HideAll() dim NoHide as worksheet orksheets.add set nohide=activesheet ... | |
| @protecting a work sheet | 10/6/2010 |
| Q: Sir , i have to protect my work sheet in such a way that Suppose i have made a spread sheet of ... A: Yes, running the code removes the sheet, but the user can still use the workbook. Once it's been ... | |
| @protecting a work sheet | 10/5/2010 |
| Q: Sir , i have to protect my work sheet in such a way that Suppose i have made a spread sheet of ... A: When YOU send out the workbook, put in new sheet called "Special" and hide it. Press Alt/F11 to get ... | |
| Lists of people | 10/5/2010 |
| Q: Im am managing a little league team. I have 12 players. Only a few are pitchers and catchers. I am ... A: A few assumptions: Your master list is in A The indication that the person is a pitcher is in B with ... | |
| Excel 2007 Sequential Numbers | 10/5/2010 |
| Q: ive been through the past questions on the site and cant find an answer. I am trying to get our ... A: Let's say your data validation cell is D1 and the Sequential # to be returned is in E1. Use Alt/F11 ... | |
| Compare two separate workbooks | 10/4/2010 |
| Q: Bob; i have run the macro you suggested: (2 separate workbooks - Master with sheet Master and Weekly ... A: First, make sure there's something in column A somewhere. I made a very small change to the macro ... | |
| Text controll w/VBA | 10/4/2010 |
| Q: Can parts of text occupying a cell be italicized and not the entire cell contents. For example, a ... A: Select all the cells which might get the italics, then run this macro: Sub AfterComma() On ... | |
| Text controll w/VBA | 10/4/2010 |
| Q: Can parts of text occupying a cell be italicized and not the entire cell contents. For example, a ... A: If the result is frmo a formula (VLOOKUP or other), then no, it can't be italicized, only constants ... | |
| Summing and Sorting in Excel | 10/4/2010 |
| Q: I have a sum formula in a cell prior to sorting my data. I need the formulas to follow the sort not ... A: Not really, but depending on your data you can use a different formula which might stay true after ... | |
| sub not defined | 10/3/2010 |
| Q: I am trying to run the following macro code: Sub Macro9() Dim bk1 As Workbook Dim sh As Worksheet, ... A: I see nothing wrong. Maybe change the name of the macro to something else because perhaps you have ... | |
| VLOOKUP across multiple workbooks | 10/3/2010 |
| Q: I am a wrestling coach trying to keep statistics for multiple wrestlers among multiple workbooks. ... A: No -- if they're in separate workbooks (And I'd suggest putting them into ONE wb so you can do this ... | |
| Compare two excel worksheets and extract data | 10/1/2010 |
| Q: I need to compare two worksheets – they are in one Excell 2003 spreadsheet. My master worksheet ... A: I you really mean 2 separate WORKBOOKS, not separate sheets, then yes, they should both be open and ... | |
| Excel Training | 10/1/2010 |
| Q: I am an Excel power user. I have an extraordinarily complex Excel project that requires skills I do ... A: Look at http://www.iil.com/str_ewizard.asp and in the middle dropdown, select "microsoft Excel ... | |
| Criteria in SUMPRODUCT | 9/30/2010 |
| Q: For the following two SUMPRODUCT statements: In column S, the possible data points are: 0, 1, 2, ... A: Sorry, I'm confused. What do you mean by "etc" in your 2nd question? You specified the possible ... | |
| Vlookup | 9/30/2010 |
| Q: I have a table which looks like this. A1 - ID B1 - AAM Code C1 - Fee Type D1 - Amount A2 - 1234 B2 ... A: You can't have a table underneath in the same sheet which ALSO occupies cells A1:B3 -- So assuming ... | |
| Compare two excel worksheets and extract data | 9/29/2010 |
| Q: I need to compare two worksheets – they are in one Excell 2003 spreadsheet. My master worksheet ... A: Run this macro: Assumes ssn in col A, dept in col E, sheets named Master and Weekly Sub leaveNew() ... | |
| excel, complicated if function | 9/28/2010 |
| Q: Company YEAR TypeA TypeB TypeC #OFHits #ofToT.hits Assgn.# Hits 4thatYr ... A: A few questions: 1 - you say for Company 1, Type A, "there are hits in 1987 so I mark that type for ... | |
| Excel Cells | 9/27/2010 |
| Q: I have a first and last name, street address, city, state and zip all in column A1 (for example, ... A: You've changed the request. In your original post therewas no separation of address and city/state. ... | |
| Formula | 9/27/2010 |
| Q: I have a formula which i am really struggling with but I think im thinking too hard about it 2010 ... A: in sheet "over due", cell I2 (leave I1 blank; and when done, hide column I): ... | |
| Retention analysis | 9/27/2010 |
| Q: I am trying to conduct some retention analysis and produce a survival curve based on the number (or ... A: I need more info as I don't understand how you want to calculate the percentages for T2, T3, or T4. ... | |
| - @ print area | 9/26/2010 |
| Q: Sir, While designing it happens that there are options in front of the designer and each option has ... A: The print_area can adjust itself depending on your choice. For example, assign it this way: Excel ... | |
| Excel Cells | 9/24/2010 |
| Q: I have a first and last name, street address, city, state and zip all in column A1 (for example, ... A: Your data is arranged in such a way that doing what you way is pretty intricate, but this does what ... | |
| querry @ linking any function | 9/24/2010 |
| Q: i want to link a function to a cell Suppose, after doing all calculations i always have to go to ... A: To print by clicking a cell you could do this: right-click the sheet tab, select View Code, enter ... | |
| narrowing a search | 9/23/2010 |
| Q: In my work, i have large spreadsheets of account information. These details are organised by account ... A: If the account #s in your list to search is column A, then if your list of account#s you want to ... | |
| delete duplicate | 9/22/2010 |
| Q: I have a problem... I am using Excel XP. I have the following spread sheet ID Name Value ... A: Select the Name column, use Data/Filter/Advanced Filter, select "Copy to another location", in the ... | |
| narrowing a search | 9/22/2010 |
| Q: In my work, i have large spreadsheets of account information. These details are organised by account ... A: "take all information...and remove the remaining..."? That seems like there'd be no remaining! I ... | |
| Opening Excel from another program. | 9/20/2010 |
| Q: First: Can I hire you for a phone consultation ?? If no: Who would recommend ? Please recommend a ... A: I think you'd be best off checking with Jon Peltier (Peltiertech.com) for what you're trying to do. ... | |
| COUNTIF function | 9/18/2010 |
| Q: Bob, I'm using Excel 2003 and Windows XP3. I have a range (B10:Q28) which includes some dates from ... A: The formula you gave can't really work as is anyway. For 2003: ... | |
| Macro wait command | 9/18/2010 |
| Q: What is the command function for "Wait" in macros? If I wanted the macro to wait for half of a ... A: to wait 1/2 second: Application.Wait NOW+.5/86400 (there are 86400 seconds in a day) to wait the # ... | |
| Transfer of data | 9/18/2010 |
| Q: I want to transfer data from sheet one to sheet 2. Eg- From Sheet1 A5 to Sheet2 B1 Sheet2 A10 ... A: by formula or macro? Formula: in Sheet2 cell B1: =Sheet1!A5 in Sheet2 cell B2: =A10 in Sheet2 cell ... | |
| Excel Random Allocations | 9/16/2010 |
| Q: I know excel can be used to generate random names from a list. However i wish to know how to create ... A: Sorry - wrong formulas. Use this: =INDEX(A:A,MATCH(SMALL($E$1:$E$45,ROW(A1)),$E$1:$E$50,0)) A good ... | |
| Tax Charts | 9/10/2010 |
| Q: I am trying to create a lookup chart that will help me determine a person's tax bracket based on ... A: If your data could be laid out like this: A B C 0 30000 1 ... | |
| Data in to percentages | 9/9/2010 |
| Q: I have to run a report daily that differs in size every day. It has five suppliers with all ... A: If the range of data is in cells A1:A20, for example, then this formula will give the % of ontime: ... | |
| type of chart | 9/7/2010 |
| Q: I have three sets of data that I would like to show graphically in Excel. Ideally, I'd prefer a ... A: Excel doesn't handle Venn diagrams aside from SmartArt (XL 2007, XL2010) which doesn't recognize the ... | |
| Linking to other workbooks | 8/24/2010 |
| Q: In my work, I am constantly building workbooks that link to other workbooks. In the past year or ... A: According to this article (I suggest you read it) http://support.microsoft.com/kb/813977 it's a bug. ... | |
| i need radio buttons with groups but i donot want group box boarder line | 8/24/2010 |
| Q: I created radio buttons in a cell in a MS-Excel 2007.I copied this row to another row also...Right ... A: Each radio button can be assigned to a particular linked cell (in design mode, properties, you can ... | |
| Need some help searching text and counting occurences of that text | 8/23/2010 |
| Q: Apologies in advance, i'm no excel genius or anything so forgive me if this sounds dumb... Here's ... A: I didn't follow your point system - I don't see where you get the 6, 8, & 6 points from your ... | |
| i need radio buttons with groups but i donot want group box boarder line | 8/23/2010 |
| Q: I created radio buttons in a cell in a MS-Excel 2007.I copied this row to another row also...Right ... A: Use ActiveX radio buttons instead of Forms radio buttons, then right-click while in design mode and ... | |
| Formating cells | 8/22/2010 |
| Q: Is it possible to format a cell so that the contents (alpha-numeric) will flash or blink? A: no - not by formatting. You can have a macro run forever which will toggle the cell, but then ... | |
| IRR & VLOOKUP | 8/20/2010 |
| Q: Can I use a VLOOKUP in an IRR formula? I'm trying to build a model where it will pull the cash ... A: The Vlookup returns a value, not an address, so if VLOOKUP(H15,$T$3:$Z$111,7,FALSE) does, in fact ... | |
| Broken Formula | 8/19/2010 |
| Q: my colleague has a spreadsheet set up to calculate a future date and it looks as though she's been ... A: You can simplify matters a lot. First of all, DATE(YEAR(U45),MONTH(U45),DAY(U45)) is identical to ... | |
| Tables - Combining, Removing dups, sorting, etc. | 8/19/2010 |
| Q: Bob, You provided me great techniques on graphing via a Pivot Chart two series with different ... A: Just copy/paste one table under the other (ignore heading rows on 2nd & 3rd tables) and it is all ... | |
| Images in Excel | 8/17/2010 |
| Q: we have some recipe setups to be done in excel. i want the user i.e. chef to be able to place an ... A: There's no such thing as an image placeholder in a sheet. You can force the image to go to a ... | |
| repeat entries on excel worksheets | 8/13/2010 |
| Q: Once I have formatted a cell how do I repeat the result without having to go through the whole ... A: The little icon that looks like a paintbrush is the format painter -- Select the cell with the ... | |
| Filtering | 8/13/2010 |
| Q: I have been thinking what to do next, What I do have right now is how to filter data based on dates. ... A: If the dropdown is in Q1 and the dates are in B1:P1, then right-click the sheet tab, select View ... | |
| Loop | 8/11/2010 |
| Q: I have a simple spreadsheet with 3 columns: employee, FirstDayOff, and SecondDayOff. What I am ... A: You don't need to use VBA, but if you need to, you can record these steps (after setting up the ... | |
| Excel Track changes | 8/10/2010 |
| Q: I'm currently using an excel 2007 spreadsheet however having issues with one file. Basically I have ... A: 1 - I don't understand "...that needs to columns L..." 2 - you want to track changes made to the ... | |
| Protect Unprotect all sheets | 8/10/2010 |
| Q: Please help me to protect locked cells of sheet1, sheet3 and sheet10 of my workbook and a button on ... A: Attach this routine to the button on Sheet1: Sub ProtUnprotToggle() Dim Pw as String Pw="XYZ" ... | |
| Number Sequencing | 8/9/2010 |
| Q: We would like to an excel template we created. I is 1 worksheet that we will put necessary ... A: Press Alt/F11 to get to the VBE, double-click the "ThisWorkbook" on the left, enter this code which ... | |
| VBA Change macro and validated fields | 8/9/2010 |
| Q: arising from my onChange macro firing whilst cell validation is in an error state. This is not easy ... A: You would need to remove the data validation from the cell(s) and validate them from inside the ... | |
| if and or statements | 8/9/2010 |
| Q: I am trying to ask a if and or within same statement, but keep getting #name? (if AK1749=1 or ... A: You have curly quotes around EZ at the end -- you probably copied it from a Word document, right? ... | |
| excel | 8/6/2010 |
| Q: I am trying to create a form that will auto populate on one worsheet from another in the same ... A: Without using macros, you'd have to have the formulas already in place. For example, assuming you ... | |
| Excluding certain rows in the range of functions | 8/6/2010 |
| Q: I have a spreadsheet that I use to keep our bowling league's stats. It has two worksheets - one for ... A: It'd be easier to help if you could send me a wb (or a mockup) - send to bobumlas@yahoo.com, use ... | |
| AutoComplete Table | 8/5/2010 |
| Q: Greetings! Please i have an excel databse of 3 columns, my primary key is email addresses, the other ... A: If you could mock up a sample of what you need (or use the original) & send to me with a more ... | |
| Excluding certain rows in the range of functions | 8/4/2010 |
| Q: I have a spreadsheet that I use to keep our bowling league's stats. It has two worksheets - one for ... A: Oops - had unnecessary lines (high, high1, high2 not needed): if A1:F20 are the scores (without the ... | |
| Vlookup within a Perimeter | 8/4/2010 |
| Q: Afternoon, Could you please advice whether this can be done with excel 2003 vlookup's / if ... A: If in your example the word "ROUTE" is in cell D1, then put this formula in D2 and fill down. ... | |
| compare two excel sheets | 8/4/2010 |
| Q: Mr. Umlas, I am a Sales Analyst, as a nature of our work, we need to update our transaction ... A: I don't know what you mean by "align the rows of the selected cells ranges on both sheets" What if ... | |
| Creating Sheets | 8/3/2010 |
| Q: you wrote some vb code for me to create sheets by broker name. the codes are as follows: ... A: Application.ScreenUpdating = False Application.DisplayAlerts = False Set curr = ActiveSheet ... | |
| How to automatically populate rows from sheet1 to sheet2 based on date in column G. | 7/31/2010 |
| Q: A very good morning to you Sir. I am using excel 2007. In a workbook I am entering data in row 3 ... A: Either remove the first line in the module ("Option Explicit") or change the code do this: Private ... | |
| SUMIFS | 7/30/2010 |
| Q: Good afternoon Here is my formula. =SUM(IF(($A$2:$A$1715="Able Derik ... A: sure - bobumlas@yahoo.com, use subject of "AllExpertsQ", make sure you direct me to the "offending" ... | |
| SUMIFS | 7/30/2010 |
| Q: Good afternoon Here is my formula. =SUM(IF(($A$2:$A$1715="Able Derik ... A: Comparing it against "12/31/2007" is a string comparison, not a date comparison. Try: ... | |
| How to automatically populate rows from sheet1 to sheet2 based on date in column G. | 7/30/2010 |
| Q: A very good morning to you Sir. I am using excel 2007. In a workbook I am entering data in row 3 ... A: Rioght-click the sheet tab (Master Sheet) and select View Code, put this in: Private Sub ... | |
| Netting Statement | 7/30/2010 |
| Q: i have a excel file with 7 different column names in sheet1. firstly i want to sort the entire sheet ... A: Am securities & Fiancial Services Ltd. is more than 31 characters and the sheet tab name is a ... | |
| hi help with my macro | 7/29/2010 |
| Q: Good day sir! How are you? You may have forgotten me but you sure helped me a lot in my excel vba ... A: After this: strFileName = InputBox("Please enter file name", "Create new file") If strFileName = ... | |
| help with a formula | 7/29/2010 |
| Q: I am working on a shift schedule at work and am having trouble. At the bottom of each date on the ... A: I don't follow -also your email didn't come thru. Send me a sample wb to bobumlas@yahoo.com, use ... | |
| Option buttons | 7/27/2010 |
| Q: In the “Design” mode I am able to insert “option buttons”, how do I use these options buttons in ... A: Option buttons should be grouped inside a frame object -- they will then operate independently ... | |
| VBA allocation inquiry | 7/14/2010 |
| Q: I used part of the code of one of your examples to build an allocation macro. The problem I got is ... A: Sorry - one error in my macro. Try this: Sub Allocation() 'all For COL = 4 To ... | |
| VBA allocation inquiry | 7/13/2010 |
| Q: I used part of the code of one of your examples to build an allocation macro. The problem I got is ... A: If I understand what you're trying to do, then this macro will handle ALL your columns -- no need ... | |
| finance | 7/13/2010 |
| Q: please go thru the attachement... i have tried to explain my queries thanks once again for your ... A: If your yellow cells are A2:D5, then enter this in C10 (april spend for icici): ... | |
| IF | 7/13/2010 |
| Q: I have two coluymsn and want to find the transactiosn that qualify for same day delivery to ... A: You're comparing it against the text string, not the time. Use either of these: ... | |
| hello | 7/12/2010 |
| Q: but i think it's impossible by formula.. so if i get VBA it's really good for me so my file is like ... A: Then I don't understand your question, unless you're only concerned about column E (WHich I missed ... | |
| Vba marco | 7/12/2010 |
| Q: Sir I have two question regarding user form.I want to multiply four text box of user form1 with 4 ... A: 1 - you would need both userforms open simultaneously, so they'd need to be shown with the parameter ... | |
| need custom formula | 7/11/2010 |
| Q: I have put formula =randbetween(44,57) in cells A1:A500, now what ever i do these numbers keeps on ... A: I'd hope to understand the question too! But I don't. with randbetween(44,57) how do you expect to ... | |
| automatic Calculation Though VBA | 7/10/2010 |
| Q: I have a excel workbook consisting of 3 sheets. One sheet name is 'importeddata' in which 10 columns ... A: I don't know what you mean by this: "Column E,F,G,H of the Main Form contain some information which ... | |
| Delete excel file | 7/10/2010 |
| Q: I want to delete excel file say ABC.xls on date 25/6/2011 but with an option to extend it another 40 ... A: Define a 2nd name, like "AskedAlready" set to False. Change code to (untested): Private Sub ... | |
| Delete excel file | 7/9/2010 |
| Q: I want to delete excel file say ABC.xls on date 25/6/2011 but with an option to extend it another 40 ... A: Cool -- I learned something -- I didn't think vba could contain code to kill the container file. OK, ... | |
| pasting in filtered cells in excel 2007 | 7/8/2010 |
| Q: In excel 2007, which I am using, I can't paste any value in filtered cells without the value ending ... A: copy the original data set to a new place 2. THEN filter the data in the new place, 3. modify as ... | |
| Delete excel file | 7/8/2010 |
| Q: I want to delete excel file say ABC.xls on date 25/6/2011 but with an option to extend it another 40 ... A: You can't have any VBA code in a workbook which will delete itself. The best you can do is prevent ... | |
| Excel 2007 VBA - Selection of a dynamic range with hidden columns | 7/7/2010 |
| Q: I am building up a database inside an Excel 2007 worksheet and that creates an huge table with a ... A: OK. First, 80% of the info recorded is really not necessary. Perhaps the necessary ones are ... | |
| Dynamic hyperlinks to locations in another sheet | 7/7/2010 |
| Q: I have a spreadsheet that has four worksheets. On the first worksheet, a user enters a client's ... A: How about getting to where you want via a double click? Right-click the sheet tab of Sheet1, select ... | |
| Excel 2007 VBA - Selection of a dynamic range with hidden columns | 7/7/2010 |
| Q: I am building up a database inside an Excel 2007 worksheet and that creates an huge table with a ... A: If the columns are hidden, then including them in the print area is fins, since they won't print, ... | |
| parsing problem. | 7/7/2010 |
| Q: Hope you can help show me how to parse pricing data as in column h-i-j-k-l. Many thanx again, for ... A: image is wayyy too small to read. Send sample wb to me at bobumlas@yahoo.com, use subject of ... | |
| Nested IF with vlookup | 7/6/2010 |
| Q: I am using Excel 2007. I have several IF statements that work for me but I run into a problem when I ... A: ISNA(VLOOKUP(B60,TotalBM,4,0)) returns TRUE or FALSE. If there is no error, the ISNA returns FALSE, ... | |
| Runtime error 13 'Type mismatch' | 7/2/2010 |
| Q: I have built a userform to input data into a spreadsheet. It worked fine yesterday, and suddenly ... A: I'm going to need the actual file. Please send to me at bobumlas@yahoo.com, use subject of ... | |
| Netting Statement | 7/1/2010 |
| Q: i have a excel file with 7 different column names in sheet1. firstly i want to sort the entire sheet ... A: This runs nearly instantly: Sub doit() Application.ScreenUpdating = False ... | |
| Formatting data | 6/30/2010 |
| Q: I have exporting a file out of an accounting prohram. They have made it really difficult for us to ... A: Without seeing the file I offer any suggestions. Send me a sample at bobumlas@yahoo.com, use subject ... | |
| EXCEL FORMULA NOT WORKING | 6/30/2010 |
| Q: I have this nested IF formula which is refusing to work; ... A: =IF(AC5="","",IF(AND(M5="Apple",J5=35),AC5+3,IF(AND(M5="orange",J5=35),AC5+3,""))) ... | |
| formula for finding a months salary | 6/30/2010 |
| Q: Sir, How can I find total salary payable for a month using a formula in a cell next to a cell ... A: In B2: =SUMPRODUCT(N(MONTH(A2)=MONTH(E$2:E$20)),F$2:F$20) and fill down to B13 ... | |
| count function? | 6/29/2010 |
| Q: Suppose I have a list of parts in col. A and their costs in col. B on 3 worksheets (they are in ... A: You can find out how many rows contain a certain text, like; ... | |
| triple series Pivot Chart | 6/28/2010 |
| Q: Bob, You provided me great techniques on graphing via a Pivot Chart two series with different ... A: Pretty much can't be done -- you have at most 2 value axes and what you're looking for is values in ... | |
| to count uniqe itme with date criteria | 6/26/2010 |
| Q: please help in getting how to get uniq item count according to date criteria regards mukesh A: Define a name, like prods, which is equal to: ... | |
| Dividing Worksheets by Unique Values | 6/24/2010 |
| Q: Bob, I have a 3column spreadsheet (columns: 800#, 800-Campaign, phone# - which called this 800#).... ... A: Best would be to make a pivot table, make the AD1 a report filter (page field), then use the show ... | |
| Excel External Row Referencing | 6/24/2010 |
| Q: I have an excel datatable that references another sheet to populate cells. The cell reference ... A: =offset('[GMP Master DataTable.xls]Master DataTable'!$A$1,ROW(A1)*10-10,0) this is effectively ... | |
| Formula | 6/24/2010 |
| Q: I've been trying to write a formula that checks Column A for a word (Match) then checks Column B for ... A: send me the wb - bobumlas@yahoo.com - use subject of "AllExpertsQ" ... | |
| making cell mandatory | 6/23/2010 |
| Q: How to make cell "b3" mandatory in excel sheet User must fill in the cell, without filling it he ... A: Alt/F11, doubleclick the "ThisWorkbook" in the Project window, enter this: Private Sub ... | |
| Matrix Lookup | 6/22/2010 |
| Q: Bob, I have two spreadsheet, the first is in matrix form, it has weeks 1 - 52 going across the ... A: =INDEX(matrix,row,column) will do it. Assuming the matrix is Sheet1!A1:E52 (A1:A52 being the week#, ... | |
| Tab references inside the same spreadsheet getting corrupted | 6/22/2010 |
| Q: I've got as single spreadsheet with tabs and formula references between the tabs. Every time ... A: If it's a corruption issue, the workbook would have to be recreated. If the tabs are getting ... | |
| excel 2003 link error | 6/22/2010 |
| Q: In one worksheet i am pointing a cell to read the data in another worksheet in the same file. ... A: send me the workbook - send to bobumlas@Yahoo.com, use subject of "AllExpertsQ" and redescribe the ... | |
| Truncated Comment Box | 6/21/2010 |
| Q: I have around 200 odd cells with comments (cells with red triangle in the corner) in them. Problem ... A: You can run this macro: Sub MakeCommentsBigger() was = Application.DisplayCommentIndicator ... | |
| Macro | 6/17/2010 |
| Q: Please can you help me create a macro to save & name a tab from the contents of a cell, I will ... A: Sheets have 2 names - the one you see on the tab and the internal name, called a codename. So a ... | |
| Macro Error-"The Item with specified name wasnt found" | 6/17/2010 |
| Q: I have a macro that i'm currently modifying. I've finished adding all my parameters. The macro was ... A: Make sure you have a sheet named "Master", one called "Sheet2", and two dropdowns named Drop Down 8 ... | |
| Toolbars | 6/15/2010 |
| Q: I heavily customize my toolbars and after a re-format of my PC, at times, Excel is losing my ... A: You may have to include system or hidden files in your search. In Vista it's in: C:\Users\<your ... | |
| selecting Data from one workbook to inport to another. | 6/15/2010 |
| Q: This time I have a little more complex question. I have 2 workbooks. The first one is The Monthly ... A: Formula for C1 is =TEXT(NOW(),"MMM") D1 is =TEXT(DATE(YEAR(NOW()),MONTH(NOW())+1,1),"MMM") E1 is ... | |
| Select Data | 6/15/2010 |
| Q: Excel 2007 Suppose, I have an excel file with 5 columns: Student Name, Nationality, Country, City, ... A: It'd be easier to show than explain. Please send a sample workbook to me at bobumlas@yahoo.com, use ... | |
| Excel Custom Sort | 6/14/2010 |
| Q: Prior to switching to Excel 2007 I was able to use a Custom Sort List for our monthly price list. ... A: If you put your entire list in the sequence you want it in some column, then you can assign a ... | |
| Mulitple tabbing | 6/11/2010 |
| Q: I have put a lot of work into an excel worksheet that has option buttons, command buttons, cell ... A: Leave off ALL the Activate statements - unnecessary because the sheet is already active. Don't ... | |
| Aggregative summation | 6/11/2010 |
| Q: I’d be happy to get your help with creating UDF to sum aggregate information as follow: I have n ... A: Please send the file to bobumlas@yahoo.com, use subject of "AllExpertsQ" and repeat what you're ... | |
| Show Automatic additional info | 6/10/2010 |
| Q: "Hi, I am making a database in MS excel of people that are members of my club that are contributing ... A: If your master list is in Sheet1, A1:F500, for example, then in the sheet where you want to pick up ... | |
| text in spreadsheet not converting into chart | 6/10/2010 |
| Q: I am a newbie at Excel and need to be able to convert my spreadsheet into a chart. I have three ... A: Feel free to send me a sample wb at bobumlas@yahoo.com, use subject of "AllExpertsQ" and redescribe ... | |
| text in spreadsheet not converting into chart | 6/10/2010 |
| Q: I am a newbie at Excel and need to be able to convert my spreadsheet into a chart. I have three ... A: I need more info - what's the structure of the data (what cells contain the labels & what cells ... | |
| Mulitple tabbing | 6/10/2010 |
| Q: I have put a lot of work into an excel worksheet that has option buttons, command buttons, cell ... A: In trying to replicate your problem I found no issue. Yes, the macro runs the same code, so it ... | |
| excel help | 6/10/2010 |
| Q: i am working through some data analysis and need some help with Excel. Willing to pay, but I want ... A: What kind of help, and how do you propose doing this? Telephone? GoToMyPc.com? dimdim.com? Other? My ... | |
| Excel Formula Help | 6/9/2010 |
| Q: If you can help me on the attached sample file how and which formula / function I can use to easily ... A: Too small to read - send sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ" and ... | |
| Pulling subsets of rows | 6/8/2010 |
| Q: Bob, I have a job that pulls about a million rows back from SQL into Excel 7. I then want to just ... A: Make a pivot table and use the region as a report filter. Then, click on the report filter and in ... | |
| Charting varying size values | 6/7/2010 |
| Q: In Excel Charts, I have two columns of data I need to chart by year, one column has values from 1 ... A: Select both series, make your column chart. The smaller series is pretty invisible. Select it (from ... | |
| HLOOKUP | 6/7/2010 |
| Q: I AM USING EXCEL 2007. I HAVE A EXCEL SHEET THAT CONTAINS EXPENSES BY CATEGORYWISE (VISA CARD, ... A: Not sure if you want to just not show the zero values or delete the rows containing them. You can ... | |
| Combine cells based on condition | 6/4/2010 |
| Q: I think I have simple problem that I can't seem to find the answer to. I need to be able to rollup ... A: Sub merger() Dim n As Integer On Error Resume Next Err.Clear For i = ... | |
| Combine cells based on condition | 6/3/2010 |
| Q: I think I have simple problem that I can't seem to find the answer to. I need to be able to rollup ... A: This assumes the parts are in column A, sheet2 is empty (it'll contain the answer; if it's not empty ... | |
| loookup and copy | 6/2/2010 |
| Q: I have a list(sentances) in col.B of sheet1 such as: water supply at lekha village W/S at Tomru ... A: Works just fine for me. I even copied the macro from what YOU sent ME above, and ran it. All I got ... | |
| How to use countif/if/average | 6/1/2010 |
| Q: I am trying to get a formula that will give me the average of entire column based on one definite ... A: There's nothing wrong with the formula. You'd get #DIV/0 when the total values for the conditions ... | |
| loookup and copy | 6/1/2010 |
| Q: I have a list(sentances) in col.B of sheet1 such as: water supply at lekha village W/S at Tomru ... A: Sub CopyIfMatch() Dim n As Integer, Sheet3Row As Integer On Error Resume Next For i = 1 To ... | |
| How to use countif/if/average | 5/31/2010 |
| Q: I am trying to get a formula that will give me the average of entire column based on one definite ... A: You have the syntax wrong. First parameter is range to be averaged, which is column AW of the sheet ... | |
| Hourly Weather data Filtering | 5/29/2010 |
| Q: I am working on an energy efficiency recommendation for turning off AC during non-office hours. I ... A: If your hours are in A1:A500, say, where A1 is the column header, you can set up an advanced filter ... | |
| Passwords with macros | 5/28/2010 |
| Q: Is there a book or manual that has a list of macro commands? I had a "Symphony" (long time ago) ... A: If you protect the sheet in VBA (in the workbook_Open event), you can specify a parameter which will ... | |
| Checkbox in Excel | 5/28/2010 |
| Q: I saw your post on the checkboxes in excel and was wondering if you could assist. In your answer ... A: right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_SelectionChange(ByVal ... | |
| Macro to extract specific data from a large spreasdsheet | 5/27/2010 |
| Q: I have a very large spreadsheet which deals in data based upon certain criteria by date. Column A ... A: Have you tried recording using an advanced filter? Assuming A1 has "Date", you can place the date ... | |
| Excel | 5/27/2010 |
| Q: Bob I'm very sorry about yesterday. I didn't even realize the mistake until after your last message. ... A: For 30 days: ... | |
| Excel | 5/27/2010 |
| Q: Bob I'm very sorry about yesterday. I didn't even realize the mistake until after your last message. ... A: You may have to modify this -- it assumes the date in C2 will never be inside the blackout period. ... | |
| Using dates as criteria in SUMIF formula | 5/26/2010 |
| Q: Ulmas, Working in Excel 07, I have a worksheet with months of the year in a row (H1:S1), and monthly ... A: "<F$2$" first of all is incorrect, you may want "<$F$2". However, that's also incorrect because ... | |
| Counting all combinations of a list of entries up to a set of multiple criteria | 5/26/2010 |
| Q: OK, I've looked at dozens and dozens of forums on the net. Not much specifically to this one. Bob I ... A: I may have left out that you need to set B1:B11 as the changing cells (sorry). However, even if you ... | |
| UDF Vlookup | 5/26/2010 |
| Q: I have 5 words in Sheet2!$A$1:$A$5 and I have a string of text Sheet1!A1 what I am trying to do is ... A: Function RngVLOOKUP(Lookup_Range As Range, Lookup_Cell As Variant) As Variant Dim N As Integer, ... | |
| Macros and Sub routines | 5/25/2010 |
| Q: Is there a book or manual that has a list of macro commands? I had a "Symphony" (long time ago) ... A: Any book which has VBA will have a good coverage of the statements, but your best bet is online ... | |
| VBA- copy (replace) workbook | 5/25/2010 |
| Q: Any help you could offer would be greatly appreciated! I need to copy a worksheet from one workbook ... A: You mwy want to consider reusing "copied" by copying CELLS, not the SHEET (this is untested) Sub ... | |
| VBA- copy (replace) workbook | 5/25/2010 |
| Q: Any help you could offer would be greatly appreciated! I need to copy a worksheet from one workbook ... A: Sub GetFile() ' This macro will import a file into this workbook ' Copyright 1999 ... | |
| Clearing Checkboxes | 5/25/2010 |
| Q: I am working on a daily employee accountability worksheet in Excel 2007. I have added checkboxes to ... A: You want option buttons, not checkboxes -- that's their default behavior. To have them work ... | |
| protecting sheet | 5/21/2010 |
| Q: Good day Bob, I want to protect a worksheet but I would like to let some features available to the ... A: You would also need to include contents:=False if you want to allow cells to be changed, otherwise ... | |
| Excel Data | 5/20/2010 |
| Q: I have a question regarding excel. I am working with a large amount of data consolidated into pivot ... A: You might want to look into the builtin function called GetPivotData - it has lots of parameters, ... | |
| protecting sheet | 5/20/2010 |
| Q: Good day Bob, I want to protect a worksheet but I would like to let some features available to the ... A: You have things like .AllowFiltering = True as if "AllowFiltering" is a property or method of the ... | |
| Excel Ranking and Sorting | 5/19/2010 |
| Q: I want to dynamically rank a list based on a criteria in ascending order, select a portion of the ... A: Sorry - still not clear - maybe send a sample wb to me at bobumlas@yahoo.com. If so, use subject of ... | |
| Transpose of cell reference | 5/19/2010 |
| Q: it has been a long time since your last help. This time around I intend to seek your help on cell ... A: Suppose the first cell is C3 that you're referencing in cell F1 - that is, F1 has =C3. You want G1 ... | |
| Counting all combinations of a list of entries up to a set of multiple criteria | 5/19/2010 |
| Q: OK, I've looked at dozens and dozens of forums on the net. Not much specifically to this one. Bob I ... A: There is no possible formula to do what you want since one formula can't supply the multiple answers ... | |
| Excel Formula To Pull Data From Another Sheet? | 5/17/2010 |
| Q: Ive got a spreadsheet where there used to be formulas but they have been deleted,so on one tab they ... A: You would use VLOOKUP To give the exact formula I'd need to know the layout of the data on the first ... | |
| Excel Formula To Pull Data From Another Sheet? | 5/17/2010 |
| Q: I've got a spreadsheet where there used to be formulas but they have been deleted, so on one sheet ... A: You would use VLOOKUP To give the exact formula I'd need to know the layout of the data on the first ... | |
| Cyclic macro execution | 5/17/2010 |
| Q: I would like to import data from OPC server to Excel sheet periodically. Let's say every 5 seconds, ... A: Sub ThisRunsEvery5Seconds() Application.ontime now+5/86400, "ThisRunsEvery5Seconds" 'rest of ... | |
| Macro that can change a cells fill colour using a math formula? | 5/14/2010 |
| Q: is there a way I can write a macro to change a cell's fill colour based on whether the number in the ... A: 1 - it's "Bob", not "Bill" 2 - conditional formatting will do the job. If the 2 cells to be ... | |
| Macro help with importing csv files into a template | 5/11/2010 |
| Q: I am trying to take data from multiple csv files and enter them into a template I already made. ... A: You can't open a text file and have it somehow simply be inside a template -- you do need to ... | |
| Microsoft excel UK dates formula | 5/10/2010 |
| Q: Can you help me please? Background to my problem - Every week i have to write down the amount of ... A: You can format the entire column as dddd dd.mm.yy and then just add 1 to the cell above; when ... | |
| Unprotecting multiple File using VBA | 5/7/2010 |
| Q: Good day Bob, I have tried, with the few knowledge I have with VBA, to create a macro that would ... A: Change Do While sName <> "" Workbooks.Open Filename:=sName ActiveSheet.Unprotect ... | |
| Multiple IF formula | 5/5/2010 |
| Q: I'm using a spreadsheet that shows the cost of goods and if cost is .01 to .99 I'd like to add a ... A: I don't know what you mean by "add a multiplier", but this formula will return the values yuo want ... | |
| don't want to open file.. | 5/4/2010 |
| Q: I have many excel workbooks... which are connected with each other...using SUMIF functions..for ... A: I'm afraid you're going to either have to open workbook B, or pull in ALL the values from workbook B ... | |
| programming in excel | 3/30/2010 |
| Q: I am trying to program a drop down list in a range of individual cells in visual basic. The list ... A: This will create the data validation lists in cells C1:C20: Sub CreateDropdowns() For i = 1 To ... | |
| select excel | 3/30/2010 |
| Q: I have a very strange problem, I tried to google it but actually no article matches. My problem is: ... A: Sorry, I don't quite understand. Is this the case for ALL open workbooks or only one in particular? ... | |
| Custom Views in Excel 2007 | 3/29/2010 |
| Q: My workplace recently upgraded to Office 2007. I made extensive use of Custom Views in Excel 2003, ... A: Unfortunately, this feature seems to have been removed! I wil do further research on workarounds, ... | |
| Changing Font of Dropdown List | 3/29/2010 |
| Q: I have created a data entry form in Excel 2007 to be used by users. I have created some dropdown ... A: Same place you used the combobox from the forms, right below it is the activeX comboboxes. Use one ... | |
| Overtime calculation | 3/27/2010 |
| Q: Bob Can you help me to put formula in excel time sheet format for the following tim;Start time and ... A: 1 - what does "...may differnet on work" mean? 2 - please specify what column contains what data -- ... | |
| A Marco to check if File Exists and Ask to Save Over | 3/25/2010 |
| Q: The code listed below will take a workseet and save it as a PDF based on the text in 3 cells within ... A: IF DIR(TotalName)="" then ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=TotalName ... | |
| Reminder Message box | 3/25/2010 |
| Q: I need to trigger around 35 reconcialition reports daily in differnt timing slots.. Is it possible ... A: The code DOES auto_execute -- needs to be in a module, not in a workbook sheet code or thisworkbook ... | |
| Conditional formulas??? | 3/24/2010 |
| Q: Is it possible to put a condition into a formula based on text colors or weather or not it's bold? ... A: select cell B1 (important part), define a name, like color, to be =GET.CELL(24,Sheet1!A1)+NOW()*0 ... | |
| Transfering data from one sheet to another | 3/24/2010 |
| Q: I have two files; the first is a price list from a manufacturer with over 200,000 horizontal lines ... A: in the 2nd workbook, cell B2 (assuming B1 is a header), enter something like this (I don'thave the ... | |
| Reminder Message box | 3/24/2010 |
| Q: I need to trigger around 35 reconcialition reports daily in differnt timing slots.. Is it possible ... A: Sub Auto_Open() Application.OnTime "7:00:15 AM", "Msg" Application.OnTime "8:45:00 AM", ... | |
| Reminder Message box | 3/24/2010 |
| Q: I need to trigger around 35 reconcialition reports daily in differnt timing slots.. Is it possible ... A: It's possible, I just don't know when you want this message to appear? Is it when a particular ... | |
| How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Columns) | 3/24/2010 |
| Q: I've seen several examples but I cannot make it work, I want the a following Excel Sheet that the ... A: What was wrong with the code I sent you? Sub Rearrange() i = 1 Application.Calculation = ... | |
| command button | 3/23/2010 |
| Q: I have an inventory sheet with over 4500 items and I have a separate file with descriptions and ... A: As long as the top left corner of the button is in the same row as the info from column B, you can ... | |
| Paste | 3/23/2010 |
| Q: In Sheet 1: A1 contain Abc Row 2 is Hidden A3 Contains Xyz A4 Contains Pqr Row 5 is Hidden A6 ... A: Please be accurate with what you're asking. You said "Now I copy A1 and Paste to A2:A6, Hidden Rows ... | |
| Event macro | 3/22/2010 |
| Q: How can I create a macro that will be triggered when a sheet/workbook is unprotected? ANSWER: There ... A: Right-click any sheet tab, select View Code. You'll see a screen with t dropdowns on the top. The ... | |
| Event macro | 3/22/2010 |
| Q: How can I create a macro that will be triggered when a sheet/workbook is unprotected? A: There is no builtin event for this - you'd have to make your own unprotect command instead of ... | |
| Paste | 3/21/2010 |
| Q: In Sheet 1: A1 contain Abc Row 2 is Hidden A3 Contains Xyz A4 Contains Pqr Row 5 is Hidden A6 ... A: I'm confused. You say: "when In sheet 2 I copy A1 and Paste it to A2, formula =Sheet1!A3 should be ... | |
| Sum function not working - returning zero | 3/21/2010 |
| Q: I have been using excel and the sum function for a very long time, but just recently got a new ... A: If the SUM is returning 0, then the data is text (or they actually sum to zero!). In your sum ... | |
| Moving Data | 3/19/2010 |
| Q: I have developed an accounting spreadsheet that is used for property management. I have also set up ... A: You would either need VBA code to do this or pre-existing formulas on each property sheet to pull ... | |
| Bypassing Excel Limitations | 3/18/2010 |
| Q: I am trying to create a calendar that will automatically populate a dynamic set of tasks that can be ... A: That's a lot to digest without seeing the info, but maybe here's a clue, because you CAN get your ... | |
| Excel & VB Solution | 3/18/2010 |
| Q: I'd like to do the following in Excel 2003 and 2007, and I believe it can only be achieved (mostly# ... A: You need to procedures - one is a function to do the average, but the other is a subroutine which ... | |
| Excel & VB Solution | 3/17/2010 |
| Q: I'd like to do the following in Excel 2003 and 2007, and I believe it can only be achieved (mostly# ... A: Sorry for the delay - I don't see where you get 23/7. The values total 28. The yellow values total ... | |
| Help with Macro | 3/17/2010 |
| Q: Some sample data for column A is below 25 45 84 (This cell is blank due to a formula) 15 14 What I ... A: Sub Answer() For i = 1 To 50 If Len(Cells(i, 1).Value) = 0 Then Set ... | |
| excel formula | 3/16/2010 |
| Q: How to get characters within excel formula: Example: Cell "A1" has a formula =100/20 and result "5" ... A: Only way is with a legacy function & defined name. From cell B1 (important step), define a name ... | |
| Macro question | 3/16/2010 |
| Q: I am trying to create a macro to find duplicates in Column A, then look for the value "Active" in ... A: Sub HideDupsWithoutActive() 'assumes column Q available Set rg = Range("A1:A" & ... | |
| Macro to delete tabs based on a List | 3/16/2010 |
| Q: In column A I have a list of names entered row after row, The number of names is based on a formula ... A: Assuming these names are on a sheet which will not be deleted, say "Master", and starts in A1, then ... | |
| excel horizontal scroll bar | 3/14/2010 |
| Q: I have a HP 110 notebook with excel 2007. i cannot get the horizontal scroll bar to display, and i ... A: I would try maximizing the workbook -- it may be showing bigger than the screen! Or, there's an ... | |
| data analysis | 3/12/2010 |
| Q: Bob, I appreciate your help with this. For simplicity, I will use numbers. I am looking for a ... A: in C1: =COUNTIF(G1:K1,TRUE) in G1: ... | |
| Referencing Multiple sheets and Ranges | 3/11/2010 |
| Q: The below code has been posted to you yesterday for different question. The code does this: If ... A: I'm confused. Which sheet is this WorksheetChange event associated with? Why not just expand your ... | |
| Deduping phone numbers | 3/11/2010 |
| Q: 03 8080 7890 03 8407 3001 03-8407-3020 03 8407 3012 03 8407 3013 03 8407 3020 03-8080-7890 the ... A: If your first value is in A1, then enter this formula in B1 (to remove the dashes): ... | |
| two Worksheet_Change macro in a sheet. | 3/10/2010 |
| Q: How i can place following two Worksheet_Change macro in sheet2? Or you may suggest me an alternate. ... A: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim myText, c On Error Resume ... | |
| Master Workbook | 3/10/2010 |
| Q: I am making a questionnaire, and i receive responses from time to time, suppose the questionnaire ... A: This is not a trivial task as you have it set up. You would definitely need to use VBA to search the ... | |
| Pull text field with combination of Text and Date field | 3/9/2010 |
| Q: My Master table shows effective Duty Rates of materials. Hence Transaction Table searches Master ... A: If B13 is not in Valdt, then this part of your formula will never be found: ... | |
| Extracting First Name | 3/9/2010 |
| Q: This is Sami again with an aching problem. I have a long list of Destinations name out of which i ... A: CODE("A") is 65. CODE("B") is 66, etc. CODE("a") is 97. So to avoid the 97-122 range (122 for "z"), ... | |
| Userform | 3/7/2010 |
| Q: If I create a userform with six textboxes, what would I have to do as far as code for textbox6 to ... A: Best to have a button which places the average in textbox6. Here's the code for that button: ... | |
| Using the NOW() function | 3/7/2010 |
| Q: I really am quite stuck with this one... I have a list of dates in one coloum which I wish to ... A: Select A:E, use Conditional Formatting (xl2003: Format/Conditional Formatting, change "Cell Value ... | |
| Sumproduct | 3/7/2010 |
| Q: I have learn your useful answer and would like to ask your help for following problem: I have the ... A: This gets largest: =MAX(IF($C$2:$C$100=$F2,$B$2:$B$100,"")) So does this: ... | |
| Color the value | 3/6/2010 |
| Q: is there any chance to change the cell text color based on value? ex:- A1>1 = cell text color is ... A: You can do this with conditional formatting or regular number formatting. Try formatting the value ... | |
| Array Formula | 3/6/2010 |
| Q: I have an array formula for tracking when calibrations are due. I want the cell to have "Due"and ... A: First, this isn't an array formula -- regular entry is fine. Second, I see no test for being 30 ... | |
| Formula to choose which column to place information under | 3/6/2010 |
| Q: I have a template in Excel that looks like a Timesheet. I have columns for Date, Start Time, Lunch ... A: Your formula can already exist in the appropriate column(s) -- in the columns representing weekends, ... | |
| Run Macro Automatically | 3/5/2010 |
| Q: I have to run macro automatically when i paste any data in Range("A4:AB999") Please help me in this ... A: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| Excel Cells - Forbid usage | 3/5/2010 |
| Q: I have a program that allows me to copy many pieces of information to my clipboard and then paste ... A: You can't copy 26 columns from some source and paste only 3 & have it wrap. You'd have to paste it ... | |
| Sumproduct | 3/5/2010 |
| Q: I have learn your useful answer and would like to ask your help for following problem: I have the ... A: Create a list of the unique categories (if that's a huge list, use Advanced filter with the "unique ... | |
| counting characters | 3/3/2010 |
| Q: im doing a document for work and have limited knowledge in excel. Im a transport supervisor and im ... A: with the "correct" sheet active, shift click on the LAST tab you want to inherit the page setup, or ... | |
| Random addition | 3/3/2010 |
| Q: I have poorly created a spread sheet that adds cell values to give a sum value in a1 or a2 or a3 ... A: You want to add all the red font cells in a particular range? Or in the whole sheet? You need a ... | |
| counting characters | 3/3/2010 |
| Q: im doing a document for work and have limited knowledge in excel. Im a transport supervisor and im ... A: The image is difficult to see, but here's what I think you're looking for. If you want to count the ... | |
| How to use the text in a cell as the filename to find cell contents in | 3/3/2010 |
| Q: I am currently trying to write a master index file for a large number of spreadsheets used as the ... A: What you want to do is doable using the INDIRECT function, but you're still going to have a problem ... | |
| need help | 3/3/2010 |
| Q: Good morning sir, As you told me I record the macro then doing editing on the same. Following is the ... A: Sorry, I don't quite understand what you want different. There's no questino in what you wrote back ... | |
| How To lock a column! | 3/2/2010 |
| Q: I have an excel sheet in which i want to lock a column such way that i can only add data in that ... A: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Resize(1, 1).Column <> 6 ... | |
| automatic progressive invoice number | 3/2/2010 |
| Q: thanks Bert A: Since there's not really an automatic way to do this, you'd need a macro, but I need more details ... | |
| Trying to create a meal list/budget | 2/28/2010 |
| Q: I am trying to keep to a budget and also make life easier by keeping track of how much I am spending ... A: You can get a price by creating a table which contains a list of items in 1 column & the price in ... | |
| Excel time calculations | 2/27/2010 |
| Q: I am having a few problems with some time calculations I am making in EXCEL can you help. I am ... A: Working with time ONLY (without date) is like working with decimals ONLY, ignoring the integer, like ... | |
| Hide & Protect formula with password in excel 2003. | 2/27/2010 |
| Q: I have following code to hide and protect formula but the formula can be unhide(unprotect) by ... A: This sub needs to be run only once. Sub HideFmls() Activesheet.Unprotect ... | |
| Find Partial number from tabel list | 2/26/2010 |
| Q: I am very frustrated to find a partial number from a row to a list of table , For example, i have a ... A: You showed me 4 columns labelled A#, B#, Duration(s) and Price, yet there were only 3 columns of ... | |
| Support | 2/26/2010 |
| Q: I have given the if condition in cell D10,D11,D12,D13,D14 and D15 which is linked from the other ... A: Most likely you're getting text values -- select D16, select inside the formula (drag across the ... | |
| Hide & Protect formula with password in excel 2003. | 2/26/2010 |
| Q: I have following code to hide and protect formula but the formula can be unhide(unprotect) by ... A: Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rng As Range For Each rng In Target.Cells ... | |
| need help | 2/26/2010 |
| Q: Good morning sir, As you told me I record the macro then doing editing on the same. Following is the ... A: Not sure which range isn't working for you. Change the date and activate the correct sheet as ... | |
| How To lock a column! | 2/25/2010 |
| Q: I have an excel sheet in which i want to lock a column such way that i can only add data in that ... A: If you want to be able to select/change only empty cells in that column, you can do this -- this ... | |
| vba code | 2/24/2010 |
| Q: I currently have streaming quotes coming into excel 2003 cell "A1". I need to capture the highest ... A: Private Sub Worksheet_Calculate() 'whenever the worksheet calculates Dim x As Double, y As ... | |
| need help | 2/24/2010 |
| Q: Good morning Sir, I have some text file which I received by email and I am using outlook 2007. On ... A: My expertise lies solely in Excel. Any outside software, including Outlook (Word, powerpoint, etc) ... | |
| nested VLOOKUP and IF functions | 2/23/2010 |
| Q: I have a large excel spreadsheet containing part lists in 1 column and their costs in the next ... A: If D1 contains the lookup value: ... | |
| Combine student data in 1 row | 2/23/2010 |
| Q: Bob, Thanks for taking questions! My data starts out looking something like this: Student ID Exam ... A: Assuming "Student ID" is in A1, you can run this procedure: Sub Rearrange() 'assumes "Student ID" ... | |
| stock | 2/23/2010 |
| Q: I currently have streaming quotes coming into excel 2003 cell "A1". I need to capture the highest ... A: Right-click the sheet tab, select View Code, put this in: Private Sub Worksheet_Change(ByVal Target ... | |
| sumproduct? | 2/22/2010 |
| Q: I created a vlookup formula to find and match data from two worksheets. The last step would be for ... A: The SUMPRODUCT multiplies all results together, so if the intermediate results of a sumproduct were ... | |
| excel formula | 2/12/2010 |
| Q: ihave to calculate the amount in Rupees an employee will get when he works overtime in the ... A: You're not going to like this, but if the start time is in A1 and the end time is in B1, then this ... | |
| Consolidation of data | 2/12/2010 |
| Q: I have 5 excel sheets.I need to merge unique employee ID's from only column A from all workbooks to ... A: The error would happen only if you had no data in column A aside from the "ColA" in A1. I'd have to ... | |
| NEED EXCEL HELP | 2/12/2010 |
| Q: I have 2 tables with 3 columns A,B & C, Column A is the start date , Column B is the End date and ... A: If the first ? is in cell C6, then put this formula in C6: But you MUST enter it via ... | |
| number value associated to drop down list Text entries | 2/10/2010 |
| Q: I am trying to create a drop down list that has a number of items listed in it. Each text item ... A: You should have the same list that appears in your dropdown in a column somewhere, say in W1:W20. So ... | |
| Consolidation of data | 2/10/2010 |
| Q: I have 5 excel sheets.I need to merge unique employee ID's from only column A from all workbooks to ... A: Not sure I totally follow, but this routine will gather all items from column A of all open ... | |
| summation of diffrent base numbers | 2/10/2010 |
| Q: Hay! I m zulfiqar and need help in excel Sir, As when we add the time in excel for example A1 = ... A: A few things: This will work if instead of using 07 2/3, for example, you use 07.6666 Also, the ... | |
| formulars in Excel 2003 | 2/10/2010 |
| Q: I do a bit of work in Excel 2003 where I have to split text from 1 cell to 2 cells . For instance ... A: There's no way to know if AAA BBBB CCCCC should be split like AAA BBBB CCCCC or AAA ... | |
| Develop Database using excel (macro&vba) | 2/10/2010 |
| Q: I have this one problem with excel. I just started working at this one company last week. They give ... A: If you're new to Excel & VBA, you're really not the guy to be given this task. There is not really a ... | |
| Excel and Learning | 2/7/2010 |
| Q: I wanted to ask for a beginner on excel how long would it take to reach a "comfortable" advance ... A: Way too vague a question - I don't know you nor what you consider a "comfortable advanced" level. ... | |
| Hyperlinks | 2/5/2010 |
| Q: Is there such a thing as a function that will give you the hyperlink address of a cell? For ... A: Not builtin, but you can write this user-defined function. Press Alt/F11, use Insert/Module, type ... | |
| how to count a coloured cell? | 2/4/2010 |
| Q: how do i count the number of cells that have been "painted"? eg: ... A: Assuming you're talking about the FONT (not the pattern), then you can use a user-defined function ... | |
| adding rows to Excel 2007 pivot tables | 2/4/2010 |
| Q: Bob, Hi, I am unable to trigger the original pivot table to incorporate the new rows added to the ... A: As long as you're in XL2007, you might as well have the source data be a Table. When it is one, then ... | |
| Excel Macro Needed | 2/3/2010 |
| Q: I am trying to write a macro that does three things: 1. Locates an asterisk in a strings of text ... A: Option Base 1 Sub AsteriskFinder() Dim Ast As Range, LRow As Integer, Temp As Range Dim ... | |
| Said another way............. | 2/3/2010 |
| Q: how do i "suspend" a condition "ppm" in this "if" statement? thanx andrew ANSWER: Sorry, I don't ... A: If I understand, you just need to remove it from the statement: ... | |
| VBA data validation | 2/2/2010 |
| Q: I have one question regarding my macro. I have written below mentioned macro and my expectation is ... A: NO: Sub FinalDeliveryCapex() Dim ECapex As Range, LOpex, SCapex, ColAG, ColAR Dim DateCap As Date, ... | |
| Adding and subracting time in excel | 1/31/2010 |
| Q: I have never worked with adding time in excel and can not seem to get the formula and formats ... A: The formula for row 1 would be: =C1-B1+E1-D1 but if your data is entered as 5:00 then Excel assumes ... | |
| Ordering a drop down List after elimnating duplicates | 1/29/2010 |
| Q: I implemented your solution for elimnating duplicates from a drop down list however I would like the ... A: I don't remember what I sent you, but here's a way to reorder a list and you can reference the ... | |
| Excel data copying from one sheet to another sheet. | 1/28/2010 |
| Q: I have excel sheet in which sheet1 data is pulled into sheet2.In sheet1 there is a formula in column ... A: A few ways: 1 - Format the sheet to not show zero values; a)in xl2003: ... | |
| Work Days in Excel | 1/28/2010 |
| Q: I'm using NETWORKDAYS to calculate working days. The problem is it assumes Saturday AND Sunday to be ... A: Well, if the start date is the same as the end date, my formula gives 1, yours would give 0. The ... | |
| If then statement formula | 1/27/2010 |
| Q: Is it possible to format an if-then statement to “exclude” the hours of class time as true; and ... A: Please send a sample wb to me at bobumlas@yahoo.com (I can't really read the image you sent) - use ... | |
| Better method to work with multiple sheets | 1/27/2010 |
| Q: I have a workbook that is used by equipment operators to track daily downtime. At the beginning of ... A: Put in this statement near the top of the macro: On Error Resume Next Then you can test to see if ... | |
| I would like to know. | 1/26/2010 |
| Q: your thoughts on: Security ideas for my xcl-03. What can be done to conceal, protect the codes in ... A: It's not a VBA name, it's the name of your formula. For example, suppose you have a formula like ... | |
| Unlock-Unprotect | 1/26/2010 |
| Q: your thoughts on: Security ideas for my xcl-03. What can be done to conceal, protect the codes in ... A: When you protect, you're prompted for a password. Anyone with the password can unprotect. There are ... | |
| Your thoughts on the matter-matter. | 1/26/2010 |
| Q: your thoughts on: Security ideas for my xcl-03. What can be done to conceal, protect the codes in ... A: I don't know what you mean by xcl-03 nor c# coders. Do you mean Excel 2003? You can always protect ... | |
| Colourcoding dropdown lists | 1/26/2010 |
| Q: I have created a dropdown list using M, K, 1, 2, & 3. I need to assign a colour to each letter. i ... A: Excel 2007 can do what you want (64 conditions). But you don't have 2007, so you're going to need a ... | |
| Worksheet function Excel2007 COLOR icons, both unresponsive | 1/25/2010 |
| Q: I do hope you will do what a dozen others have not been able to, and that includes EXCEL forum, MSN ... A: I'm confused by your reference to Worksheet Function - what does that have to do with Color? And ... | |
| Excel formula and formatting | 1/25/2010 |
| Q: I am using Excel 2007 Enterprise. I am trying to use the LOOKUP formula to expedite imputing ... A: The process is to ensure the cell is formatted as you want (in this case General), THEN enter the ... | |
| Old Excel macros | 1/23/2010 |
| Q: I am a very seasoned excel user. I have made several applications in old macros for excel. I like ... A: I'm not sure I follow - you can't RECORD in the old macro style (XLM-code, like =RETURN(), for ... | |
| Hidden data | 1/22/2010 |
| Q: I am trying to create a spreadsheet where I could enter a reference number that relates to an item ... A: Somewhere you'd already have to have a table of numbers and names. Say that's on Sheet2, in A1:B300. ... | |
| Sum with Vlookup | 1/20/2010 |
| Q: I'm trying to sum a range of values received with Vlookup formula. For example: Row# A B 1 ... A: i is the row # inside the loop -- if you enter =SUMXYx(4), then i will be 4, then 3, then 2, then 1, ... | |
| Sum with Vlookup | 1/20/2010 |
| Q: I'm trying to sum a range of values received with Vlookup formula. For example: Row# A B 1 ... A: Not sure I can be much more specific. Press Alt/F11. Then use Insert/Module. Then paste in this: ... | |
| Sum with Vlookup | 1/20/2010 |
| Q: I'm trying to sum a range of values received with Vlookup formula. For example: Row# A B 1 ... A: You can use a UDF (User-defined function). Press Alt/F11, use Insert/Module, put this in: Function ... | |
| excel formula | 1/20/2010 |
| Q: please tell me about in brief how indirect function is used . i want to ask you from some examples ... A: INDIRECT takes text and tries to treat it as a range. If you have "Hello" in cell A1, and in cell A2 ... | |
| excel | 1/19/2010 |
| Q: THIS IS WITH REFERENCE TO MY PROBLEM WITH HOUSE NUMBERING AND ITS TYPE AT MY CONSTRCTION ... A: OK assume H1:H40 (adjust it) is for Type1, I1:I40 for type 2, J1:J40 for Type3: ... | |
| Macro for copying and pasting data from multiple workbooks within a folder | 1/19/2010 |
| Q: Within excel 2003 I have 250 data templates (identical layout but filled in on 250 separate ... A: Doesn't matter if A1 is blank -- using ROW(A1) returns 1, no matter what's in A1. I want 1 for the ... | |
| Filling in Cells | 1/19/2010 |
| Q: I am using Excel 2003. I have several different worksheets with a main summary worksheet. I want ... A: Follow these steps exactly: 1 - in the Main sheet, cell J7 enter this (without the "=" sign): ... | |
| help with INDEX | 1/19/2010 |
| Q: Could you please help me out.If I'm getting error "#VALUE" by using the formula ' ... A: A few ways. Let's assume these formulas are in E1:E20. You can use ... | |
| Time Sheet | 1/18/2010 |
| Q: Hey Bob, It's me again. Last week you worked on 3 formulas for a time sheet for me. The toughie. It ... A: add this to the beginning of every formula, using roe 22 as a sample: IF(COUNT(A22,C22:D22)<>3,0,... ... | |
| MACRO Help | 1/18/2010 |
| Q: COULD YOU PLEASE TELL ME what does the follwing code in Macro mean ' ActiveCell.FormulaR1C1 = ... A: It means to enter into the active cell this formula (but it depends on where the active cell is). If ... | |
| Using macros | 1/18/2010 |
| Q: Well i have 25 columns and 500 rows (B8:Z500), each row has 15 random numbers from 1-25, so each row ... A: I'd use a user-defined function, like this: Function MaxSequence(Rg, Value) For i = 1 To ... | |
| excel problem.. | 1/17/2010 |
| Q: i fill my first column ranging from 1 to 100 and the values in the second column depend upon those ... A: I need to know what version of excel you're using - 2003 limits you to 3 colors using conditional ... | |
| User Input to Execute Print Macro | 1/16/2010 |
| Q: I have a daily log where activities are recorded in excel sheet by date (CLM A) and duty code (CLM ... A: Sorry, I don't do VBA to do email But here's a macro to print them: Sub answer() clma = ... | |
| Macro for copying and pasting data from multiple workbooks within a folder | 1/15/2010 |
| Q: Within excel 2003 I have 250 data templates (identical layout but filled in on 250 separate ... A: You can get #N/A if you didn't define the name Files correctly in step 1. If you save this workbook ... | |
| Macro for copying and pasting data from multiple workbooks within a folder | 1/15/2010 |
| Q: Within excel 2003 I have 250 data templates (identical layout but filled in on 250 separate ... A: 1 - If xl2003: Insert/Name/Define. Use any name, say "Files" (no quotes). In the Refers-To box, ... | |
| I need to compare two lists of names | 1/15/2010 |
| Q: I really hope you could help me with this. I have two lists of names that I need to compare. ... A: to get a list of names in A not in B, ctrl/shift/enter this formula somewhere and fill down: ... | |
| Excel 2007. Copying Data from Master Worksheet in their respective supplementary list | 1/14/2010 |
| Q: I'm no programming expert and get most of my excel assistance of shared answers through this ... A: I think I'd need to see the wb - what you're looking to do is still a but vague, partly because ... | |
| bubble graphs | 1/14/2010 |
| Q: How do I make a bubble graph with size of bubble related to number of participants or score and each ... A: Bubble charts require 3 coordinates -- x value, y value, and bubble size. To label the bubbles would ... | |
| Macro for copying and pasting data from multiple workbooks within a folder | 1/13/2010 |
| Q: Within excel 2003 I have 250 data templates (identical layout but filled in on 250 separate ... A: This can be done without macros (!) - but you need to follow directions exactly. I'm assuming you ... | |
| Time Sheet | 1/12/2010 |
| Q: Hey Bob, We ran out of updates on the last question. We were discussing a time sheet to calculate ... A: E22: =IF(WEEKDAY(A22,2)<6,24*(MIN(D22,0.75)-MAX(C22,0.25)),0)*O22 E23: ... | |
| Excel | 1/12/2010 |
| Q: I have a table with 3 columns on sheet 1 of a workbook. Column B B1:B233 contains names, columnC ... A: It can be done with fairly involved VBA to keep the lists sorted, etc, but why not take advantage of ... | |
| Time Sheet Calculations | 1/12/2010 |
| Q: I am looking for a formula to calculate standard, overtime, and double time. My standard time runs ... A: Sorry, but this is getting more confusing -- you say you have "a" date cell, yet in your latest ... | |
| Excel Option Buttons | 1/9/2010 |
| Q: I am trying to use form option buttons for several different questions on the same form - 2 option ... A: If they're really on forms, what's probably happening is that if you click on one of the buttons in ... | |
| Creating a Pop-up message when starting Excel that has a link | 1/7/2010 |
| Q: Office 2003 I am wanting to create a Pop up message when I open a specific Workbook that says ... A: Doesn't need to be so complicated with using WScript.Shell. Something like: Private Sub ... | |
| Disable Save based on Condition | 1/7/2010 |
| Q: the macro below works excellent the only thing is that it is not letting me save even if there is no ... A: Sorry. Try this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ... | |
| Changing tab references in formulas when dragged down | 1/6/2010 |
| Q: Bob, Happy New Year! I have a question which has been bugging me for a long time. I work ... A: Needs a bit of VBA. Alt/F11, Insert Module, enter this: Function SheetNo(Ndx) As String SheetNo ... | |
| problem in excel | 1/5/2010 |
| Q: Sir, I m writing a program in visual basic editor. the program is in such a way that it should ... A: Sorry, still don't get what you want to do. Firstly, if you're talking about VBScript, I'm not your ... | |
| Retrieving data from other workbooks | 1/4/2010 |
| Q: hope you are well. I have a workbook which has a macro button on the front page. When a user clicks ... A: Instead of just Application.Dialogs(xlDialogOpen).Show I recommend MyFile = ... | |
| Catergory Management | 12/22/2009 |
| Q: I am in the process of creating a catergory management system. I have a work sheet with 40,000 cells ... A: You'd need VBA code to do this, but I don't have enough information - send a sample wb to me at ... | |
| Macro to - paste special - values | 12/21/2009 |
| Q: I have been using a macro to transfer data from an invoice in excel to a database in the next tab. ... A: Sub orderdb() Sheets("Invoice").Range("B5").Copy Sheets("Invoice DB").Range("A" & ... | |
| Changing Cell format from "NumberFormat" to "Text" | 12/21/2009 |
| Q: I am copying a "date" in a coulmn of one workbook from another. After copying I changed its ... A: Sub ChangeDateToText() For Each thing In Range("C1:C" & Range("C65536").End(xlUp).Row) ... | |
| Transpose multiple rows into columns | 12/18/2009 |
| Q: Miguel answered a question regarding transposing multiple rows into columns. He used the OFFSET ... A: In C1 (assuming that's empty), enter xA1 (no "=", just xA1). Using the fill handle, drag C1 right to ... | |
| Automatic sorting | 12/17/2009 |
| Q: I am tabulating votes which each participant votes for 5 others. SHEET1 has all particpant listed ... A: Instead of just closing/opening, exit excel & restart. If the problem still exists, send me a sample ... | |
| Disable Save based on Condition | 12/17/2009 |
| Q: Hey Bob,I wanted to thank you first for the macro you helped me with. Also just had a follow up ... A: 1 - I suggested you place the code before the Exit Sub and after the End With which you didn't do, ... | |
| Excel Vlookup Function | 12/16/2009 |
| Q: I have two list. One has employee hire date (List A),while the other does not have (List B). I used ... A: It means the value doesn't exist. What would you LIKE to see instead of #NA? You COULD use a formula ... | |
| copy paste | 12/16/2009 |
| Q: I have some data in column D5,D6,D7,D8, D9, D10, D11, D12 upto D749 and another data in Col. E6, E8, ... A: Sub DoIt() For Each x in Range("E6,E8,E11,E22:E25,E733") 'for example ... | |
| Still having problem | 12/16/2009 |
| Q: you had advised me the following code to copy the cells from multiple workbooks to one workbook. ... A: Change For Each thing In Sheets ThisWorkbook.Sheets(1).Cells(L, 1).Value = ... | |
| follow-up question re: "IF" formula | 12/15/2009 |
| Q: Bob, I'm using Excel 2003 with Windows XP3. I'd like to adjust a formula using an "IF" statement. ... A: enter =ROW(1:5) in a cell. You'll see 1. But click in the formula bar and press F9. You'll see ... | |
| not working in existing workbook | 12/15/2009 |
| Q: you have provided me the below code which is working perfect. but i need one more enhancement. i ... A: Sorry for the typo - should be ThisWorkbook, not ThisWorokbook I don't understand this paragraph: ... | |
| Difficulty combining three separate sections of working code/ Handling objects | 12/14/2009 |
| Q: Please let me ask for your guidance. Pasted below is code intended to perform three separate ... A: sorry it took so long to get back to you -- you didn't say where you're getting the error, and it'd ... | |
| EXCEL 2003 | 12/12/2009 |
| Q: I have tryed to get a formula in excel 2003 for the following:- QTY. QTY. OPENING IN OUT BALANCE 300 ... A: I don't think I understand. the cell containing the "100" should contain a formula such as =A3-B3. ... | |
| To copy and paste from other workbooks | 12/11/2009 |
| Q: Some months ago you had helped me out in drafting the below code. It is working perfect eversince. ... A: Part of your #1 question got chopped off. It says "...I rather want to sp....code) which cell to be ... | |
| Search value in one sheet and paste row to another sheet | 12/11/2009 |
| Q: I’m new at this of creating Macros and I really need an expert help. I need to create a macro that ... A: This was untested but should work Sub Answer() Dim N as integer On Error Resume Next N=0 ... | |
| cell color | 12/10/2009 |
| Q: I am making a vacation scheduling spread sheet. I have 8 different work centers with a list of ... A: Can't be done automatically -- Assigning a color to a cell doesn't trigger any event that can be ... | |
| excel macro disconnects after printing | 12/9/2009 |
| Q: I'm using a macro to print Rx pads and consults from an XL worksheet using data gathered from a vb ... A: Not sure, but instead of clearing the form after printing, why not unload the form after printing? ... | |
| Can't get VLOOKUP to work correctly | 12/9/2009 |
| Q: Here is what I'm trying to get to work. I need to lookup the value stored in AG1 but add MVS to the ... A: Since Current IOCDS has a space, all references to it must be surrounded by single quotes, so this ... | |
| Excel VBA Automatic Creation of Range Names including Duplicates | 12/7/2009 |
| Q: Excel VBA Automatic Creation of Range Names including Duplicates ... A: Updated: Jan1 is NOT a valid range name in Excel 2007 -- that's a cell reference!! So, these names ... | |
| Histograms | 12/6/2009 |
| Q: I have heard that I can create a histogram in an excel cell that changes. For example: If I have a ... A: Here's a way you can do it. Using column B as an example. Make the font for B Arial Narrow, and Red. ... | |
| Sort by category then by date | 12/6/2009 |
| Q: How i can sort category then by date of appointment to find seniority of each category. please see ... A: Follow these steps exactly: Select B2:E25, use Edit/Goto/Special, select Blanks, click OK. Then type ... | |
| to copy specific cell from diffrent worksheets to one workbook | 12/5/2009 |
| Q: i have several workbook in a folder. each of the work book contains one or more sheets. now i want ... A: Assuming you want the path in F: Sub DoR5() Dim Fil As String, L As Integer MsgBox "Point to ... | |
| Looping in Macro | 12/4/2009 |
| Q: can the macro look in a set of folders for a specific name based on a specific cell value and copy ... A: Dim FName As String, cdir As String Sub TryAgain() Chdrive "D" Application.DisplayAlerts = False ... | |
| Validation Box | 12/4/2009 |
| Q: I am trying to manipulate some data - am sure the solution to this is easy but cant figure it out ... A: If your initial dropdown, containing the unique names from column A is in cell F1, for example, then ... | |
| Date arithmatic and formatting while copying from another workbook | 12/4/2009 |
| Q: I am using following code to copy a column from Book2.xls to Book1.xls: Const WBA As String = ... A: for #1 - simply format col C as you want: Workbooks(WBA).Sheets(1).Columns(3).Numberformat = ... | |
| Looping in Macro | 12/4/2009 |
| Q: can the macro look in a set of folders for a specific name based on a specific cell value and copy ... A: Try this: Sub SaveMe() Dim FName As String, cdir As String cdir = CurDir ... | |
| to copy specific cell from diffrent worksheets to one workbook | 12/4/2009 |
| Q: i have several workbook in a folder. each of the work book contains one or more sheets. now i want ... A: Try this (updated): Sub DoR5() Dim Fil As String, L As Integer MsgBox "Point to the ... | |
| Formula for Excel Task | 12/3/2009 |
| Q: Could I please know a Dynamic formula that will search for an entry in all cells of the two columns ... A: Sorry -- the easy answer is this formula instead: =IF(ISNA(MATCH("Not ... | |
| Formula for Excel Task | 12/3/2009 |
| Q: Could I please know a Dynamic formula that will search for an entry in all cells of the two columns ... A: =IF(ISNA(MATCH("Not Available",F1:F1000,0)),IF(ISNA(MATCH("Not ... | |
| Looping in Macro | 12/3/2009 |
| Q: can the macro look in a set of folders for a specific name based on a specific cell value and copy ... A: I'm confused -- there is no "sheet" in a folder. Sheets are in workbooks. Are you really looking for ... | |
| Copying a column based on the header row | 12/2/2009 |
| Q: I need help with a formula or macro that can copy a whole column to an existing workbook. I receive ... A: Assuming the header row in Workbook1 is row 1, then you can get the column # by: ... | |
| Disable Save based on Condition | 12/2/2009 |
| Q: Hey Bob,I wanted to thank you first for the macro you helped me with. Also just had a follow up ... A: You already have a msgbox -- maybe you want to select the offending cell: Change MsgBox "You can't ... | |
| Disable Save based on Condition | 12/1/2009 |
| Q: Hey Bob, I have excel 2003 file that my colleges use. Since it is used by so many different users it ... A: Sorry - typo. Try this (and yes, yuo can keep adding if-statements, but your reference to "column ... | |
| Large files | 12/1/2009 |
| Q: Bob, we've created a rather large workbook and are wondering if we should bring down the size. ... A: I very frequently work with files well over 15-16 meg. (130 sheets, 50,000 lines of VBA code,...) ... | |
| Want some Value from Sheet 1 to Sheet 2 | 11/30/2009 |
| Q: Bob My question is i want to find some value from one sheet and paste to other sheet by using Macro. ... A: I'm sorry, but I can't follow what you're saying. What does this mean: Sheet 1 = X ?? Then you say ... | |
| Disable Save based on Condition | 11/30/2009 |
| Q: Hey Bob, I have excel 2003 file that my colleges use. Since it is used by so many different users it ... A: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) With ... | |
| Disable Save based on Condition | 11/25/2009 |
| Q: Hey Bob, I have excel 2003 file that my colleges use. Since it is used by so many different users it ... A: You didn't say what's not working! In any case, your description isn't clear. When you say "Such ... | |
| Bar Chart with two indepent variables | 11/25/2009 |
| Q: I am trying to recreate the attached chart. My data is a set of records that contain two key ... A: First, it's a column chart, not a bar chart. Next, I'm not clear on what you're charting -- a COUNT ... | |
| To Concatenate | 11/25/2009 |
| Q: I have a sheet which has data. In column a there are book names and in column B there shelf numbers. ... A: You need VBA - User defined function. Press Alt/F11, use Insert Module, then paste this in: Function ... | |
| Copying columns from another workbook | 11/24/2009 |
| Q: My requirement is a little larger version of following problem, and I will follow your guidance for ... A: Adjust the workbooks names accordingly: Const WBA as string = "BookA.xls" Const WBB as string = ... | |
| Copying columns from another workbook | 11/24/2009 |
| Q: My requirement is a little larger version of following problem, and I will follow your guidance for ... A: Select wb B's Sheet 1's range B4:B1000 (as far down as the last used cell is), copy, activate wb A, ... | |
| Excel formula question. | 11/22/2009 |
| Q: I am a real estate agent who created a spread sheet for our deals. I have a close date on the ... A: First issue is getting the series you want. In cell Q1 enter 12/1/2009. Select Q1:Q2 (Q2 is empty), ... | |
| delete row where cell has specified text | 11/21/2009 |
| 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 ... | |
| WorkbookBeforeSave | 11/20/2009 |
| 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 ... | |
| application.match function | 11/20/2009 |
| 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 |
| 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 = ... | |
| To Remove duplicate rows | 11/20/2009 |
| 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, ... | |
| split one sheet in 2 others | 11/19/2009 |
| Q: I have the following problem: I have an Excel file with 3 sheets: B+N, B and N. The user has to add ... A: I need to know what would trigger this thing happening -- you want a button on the sheet to run a ... | |
| To Remove duplicate rows | 11/19/2009 |
| Q: I have an excel sheet which has data. Column B contains Name, Column C contains cell phone numbers ... A: According to what I read, your 3 scenarios are really one: If name & phone are the same, it's a ... | |
| split one sheet in 2 others | 11/19/2009 |
| Q: I have the following problem: I have an Excel file with 3 sheets: B+N, B and N. The user has to add ... A: Still a bit fuzzy -- you want that when a user types info into B+N that that info is copied into B ... | |
| excel solver | 11/18/2009 |
| Q: I need help figuring out how i am going to do this - please help me - not sure what to put in solver ... A: Yes. put this formula in E3 & fill down: =C3*D3 put this in E7: =SUM(E3:E6) Set solver so that the ... | |
| Reseting numberous cells back to zero | 11/18/2009 |
| Q: I have a Worksheet (calculator) on my website that projects my clients ROI, but how do you reset the ... A: select all the cells you want to reset to 0 (ctrl/click), then insert/name/define and give a simple ... | |
| merging data excel | 11/18/2009 |
| Q: I have two spreadsheets with lists of part numbers and some data. I would like to check if a part ... A: Before you can get this to work you MUST make the part numbers identical in content and text/number, ... | |
| Visual Basic question | 11/17/2009 |
| Q: I am trying to have a worksheet appear or hide based on the answer to two questions using a drop ... A: The way you have it set up is pretty confusing. I can't tell which is the first or second question, ... | |
| Working with large excel file | 11/17/2009 |
| Q: I am using a large excel file having 16 sheets. There are many links also. It take about 30 minutes ... A: 16 sheets isn't large (I typically work with over 100 sheets and 50,000 lines of VBA code!) So it's ... | |
| Return a count on 3 filtered criteria | 11/16/2009 |
| Q: I am using Excel 2003. I need to duplicate the results of using three filters in my first ... A: SUMPRODUCT required numeric values; E6:E200="P" returns TRUE/FALSE; N-function changes true to 1, ... | |
| Absolute cell reference | 11/16/2009 |
| Q: Bob Umlas Why doesn’t the absolute cell reference work in Excel 2007? When I drag a cell which ... A: First, the formula makes no sense -- it's like using =SUM(75) -- it will always be 75, so the SUM is ... | |
| How to four columns where data is in Credit | 11/14/2009 |
| Q: I cannot see a reply from you regarding my answer to your query, are you able to help with how to ... A: If the letters "CR" are actually part of the cell, then the values are all text values, not numeric ... | |
| Excel 2007 formula | 11/14/2009 |
| Q: I have converted a file from 2003 to 2007. It seems that a formula that I was using in 2003 does ... A: A 2003 file opened in 2007 is opened in "compatibility mode" in which there are still only 256 ... | |
| checkbox in on sheet in correspondace to another sheet | 11/12/2009 |
| Q: I really hope that you can help me with this. I have 12 sheets in one book. and another book to ... A: In C2 of book2 Sheet1, put this formula: =IF([Book1]Sheet1!$C2=1,"X","") in D2: ... | |
| Special Drop Down List | 11/11/2009 |
| Q: I'm a beginner in Microsoft Excel. What I need right now is a macro that would have a special drop ... A: Somewhere you'd already have to have a table of item, price code, item price, like: Item1 F7162 ... | |
| Excel - Split text into different cells | 11/11/2009 |
| Q: I have text that is pulled into excel. The text is in the format of: 113916;(n°19783) Banks ... A: Just make iRow be a loop index: Sub Macro1() Dim Rec As String Dim Alist As Variant ... | |
| How to return the top column based on variable in row | 11/11/2009 |
| Q: I use a data base to report when i was last at a location I have 12 columns (jan-dec) and 300+ rows ... A: If the month names are in K1:V1, then you can get the month by: ... | |
| Vlookup for Multiple Columns | 11/10/2009 |
| Q: Bob, Would you kindly assist me with formula that will look at the date in column 12, if there is ... A: If you treat each VLOOKUP as a complete formula (which it is), your current syntax is effectively: ... | |
| Copy values from excel and paste the same to another | 11/10/2009 |
| Q: I need to compare one row in one excel (Wx) with another excel (Wy) and if they are same, i need to ... A: If you mean 2nd workbook when you say 2nd excel, then you can use a formula - no copy/paste ... | |
| follow up que.. | 11/3/2009 |
| Q: ...you got my last que...i think so.. i have another problem in the same macro.. Once if from ... A: Sub vrunda() Dim N As Integer, M As Integer On Error Resume Next For each SH in ... | |
| How to four columns where data is in Credit | 11/3/2009 |
| Q: Is there a way of sorting four columns of data to display values that are in credit for all of the ... A: I don't understand - can you give what this would look like AFTER said sort? Are you saying you want ... | |
| follow up que.. | 11/3/2009 |
| Q: Hey Mr.Bob Ur solution of to get multiple rows from another sheet based on cell value is running ... A: 1 - I didn't (don't) keep previous solutions, so I don't have any macro to modify 2 - I get TONS of ... | |
| Macro to sort position based on a list | 11/3/2009 |
| Q: how are you doing? i am stuck with a nesty problem again and please help me out like over the months ... A: Sorry, but I have no idea what you're saying - first, what does it mean to "remove or set a cross ... | |
| Macro | 11/1/2009 |
| Q: I have been trying to record and execute a macro involving two separate workbooks but am not being ... A: Sub InsertVac() Dim n as Long Set WB2=Workbooks("WB2.xls").Sheets(1) Set ... | |
| Creating a Formula | 10/30/2009 |
| Q: I am trying to create a formula to calculate a checkbook balance. What I have is M3+4-K4. That's ... A: A few solutions, but your formula has a simple 4 in it which I don't think you want. Assuming that's ... | |
| Excel 03 Crashes | 10/29/2009 |
| Q: I have a users that is experiencing an excel crash for no apparent reason. I have checked all of the ... A: It would be great if it could be prevented, but until and unless the cause is found and fixed, I'm ... | |
| To get multiple rows from another sheet based n some value | 10/28/2009 |
| Q: "hello sir,, i have two sheet....1) soda 2) sale Now in sheet1(soda).. there is a column named ... A: Assuming the word "Date" is on the Soda Sheet in cell A1, and the other data in Sale starts in A1 as ... | |
| To get multiple rows from another sheet based n some value | 10/28/2009 |
| Q: "hello sir,, i have two sheet....1) soda 2) sale Now in sheet1(soda).. there is a column named ... A: I'm not sure if you're looking to add the results of sale onto soda or if you want to copy all the ... | |
| copy paste worksheet | 10/28/2009 |
| Q: I have seen a code in the link below to copy paste worksheets. It is seen that the code works for a ... A: Unless I'm not understanding, the formula should be comparing 200912>200901 which is true, so the ... | |
| $ to Euro values on large excel mut. tablw spreads | 10/27/2009 |
| Q: QuestionWe have many Excel spreadsheets in our company that we use on projects - budget sheets, ... A: But the conversion rate keeps changing -- where/how do you get the new rates? It can be done with ... | |
| Auto Update of Form Numbers | 10/27/2009 |
| Q: Follow-upNow that I know Excel can update a form number each time the form is opened, can you please ... A: Yes. But is it a "form" or really a workbook? If you open the workbook and the form changes from 1 ... | |
| multiple conditional formating using vlookup | 10/27/2009 |
| Q: How are you. I am stuck in a problem again. I have a table A with two column (pls see the ... A: Assuming Excel 2007 (since Excel 2003 can only handle 3 in conditional formatting), you can select ... | |
| Please help Me with Macro Or V Look up | 10/26/2009 |
| Q: Good After Noon Bob, I have 2 sheets Named , Sample and results, I want to check the data in the ... A: You selected J2 and copied it, then pasted it into the entire column J, which takes forever - over a ... | |
| Data linking and pasting | 10/26/2009 |
| Q: I would like my command button to do the following: Ther are 2 workbooks i use. the first one ... A: Have you tried recording the steps? I can do this for you but am quite busy at the moment and not ... | |
| Copy/Paste New Sheet Macro | 10/23/2009 |
| Q: I originally wrote this macro because I have 50 unique values in which I need to add a new worksheet ... A: I don't understand, sorry. But you can shorten the macro a LOT by changing this: If Cells(sRow, "C") ... | |
| conditional formatting | 10/23/2009 |
| Q: i have excel for mac 2008. I want a column of cells which contain dates. I want to have the font ... A: First, I don't know Mac -- I'm assuming the menus are similar to Excel 2003's. Select all the cells ... | |
| Setting up a date sensitive table | 10/23/2009 |
| Q: Bob, Thank you for taking the time to look at my question. I am using Excel 2007 and am trying to ... A: Sorry, I don't follow your math - given the sample data, why is 10% under <30 days? Why is 30% under ... | |
| Cell Color Changing Due To Date | 10/23/2009 |
| Q: I need to work out how to change one column so that the color of each cell in that column change ... A: Assuming Excel 2003, select all the cells in the column, use Format/Conditional Formatting, change ... | |
| VBA Macro Conditional Formating excel 2003 | 10/22/2009 |
| Q: I have a worksheet where in column c the categories like A,B,C,D,E and F. If you can kindly help me ... A: Sorry - forgot you need to put THIS code in the sheet module: Private Sub Worksheet_Calculate() ... | |
| Concatenate based on input box | 10/22/2009 |
| Q: Can you please help with the following. Concatenate based on input box. I would like a concatenate ... A: Sub Combiner() Dim Stuff As String, items Stuff = InputBox("Enter characters to be appended ... | |
| multiple rows -> multiple columns with common field | 10/21/2009 |
| Q: I have a large amount of data that was transferred over from crystal reports (10 i think) and I want ... A: Using a PT I can get: bowls gloves hats plates H111 1 3 2 H222 ... | |
| Creating a macro involving more than one work book | 10/21/2009 |
| Q: In excel 2003 consider 12 workbooks(A-L) with 50 sheets each.The sheets are identical in all the ... A: You can select the cell where you would insert a row, then run this macro instead: Sub ... | |
| Concatenate based on input box | 10/21/2009 |
| Q: Can you please help with the following. Concatenate based on input box. I would like a concatenate ... A: Formulas do not give input boxes. That's from VBA only. You say "If B = input box..." and that ... | |
| VBA sort key | 10/21/2009 |
| Q: Bob, I would like a VBA script which inserts a column to the left of A and then in that column it ... A: I don't understand the whole first part -- you want to insert numbers 1,2,3,... and then sort? But ... | |
| IF Statement with Validation Lists | 10/20/2009 |
| Q: I would like to create an if statement where if true it returns a drop down validation list in the ... A: In D4: =IF(D3=II,True,False) Define a range name, like MyList, which represents all the items in the ... | |
| IF Statement with Validation Lists | 10/20/2009 |
| Q: I would like to create an if statement where if true it returns a drop down validation list in the ... A: It's a contradiction. Suppose it could be done. You have something like =IF(xxx,yyy,zzz) where ... | |
| Userform Calculations | 10/19/2009 |
| Q: First of all I am completely new to VB within Excel. I have somehow (lots of trial and error and ... A: When you say "appear on screen" - do you mean you want the result of the calculation to be in a ... | |
| Trendlines | 10/14/2009 |
| Q: Could you explain the trendlines options on a chart? We're interested in forecasting one period. Is ... A: Depends on the nature of the data -- if you want to forecast 1 period, on the options tab of the ... | |
| Protect each individual worksheet from viewing | 10/14/2009 |
| Q: I am using MS Office 2007 I have a Excel file that has 13 worksheets, the first worksheet is ... A: First, XL2007, to save with macros, must have the .xlsm extension. Next, where are you putting that ... | |
| Insert Picture from Userform into Excel Worksheet | 10/14/2009 |
| Q: I want to insert an image file from Userform into Excel Worksheet at Cell G14. I have a userform ... A: You can have another command button which takes the same image and pastes it in: Private Sub ... | |
| Defining a dynamic range in vba excel | 10/13/2009 |
| Q: I'm trying to write a macro, that would work on a specified range in a workbook. The range always ... A: Is this what you're looking for? Sub za() Set zasieg = Range(Range("G23"), ... | |
| The code had side effects :-( | 10/13/2009 |
| Q: I hate to be a pest, but the the code had side effects. It is about the PO file question I initially ... A: Sorry -- it left excel in a state of Events not being fired because of the Application.EnableEvents ... | |
| Excel cells mandatory based on value in another cell | 10/9/2009 |
| Q: I saw your answer regarding how to not let the user save an excel file if they don't enter data in a ... A: In the VBE, double-click the "ThisWorkbook" and enter this code: Private Sub ... | |
| Extracting names | 10/9/2009 |
| Q: I have question please. I have a worksheet with 2 spreadsheet tabs. In column A on the first sheet ... A: Assuming there are 100 names on the 1st worksheet and 50 on the 2nd. In A51 of the 2nd worksheet ... | |
| Excel functions within macros | 10/8/2009 |
| Q: I am trying to simplify a routine in a spreadsheet with the use of a one-touch keyboard entry, ... A: Application.WorksheetFunction.Concatenate doesn't exist in VBA. Use the ampersand instead. Also, you ... | |
| Adding... | 10/8/2009 |
| Q: I have 2 questions for you. I hope you will be able to help me. In Column B i have names starting ... A: in F1: =MID(B1,FIND(",",B1)+2,255)&LEFT(B1,FIND(",",B1)-1) and fill down Macro: Function ... | |
| Removing a code after Save As | 10/8/2009 |
| Q: I have a Purchase Order Template (file name PO Template) that has the following code in it Private ... A: Change: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ... | |
| VBA IF STATEMENT | 10/8/2009 |
| Q: VBA king Bob. I am using excel 2000 version. Can you please help with the following macro. The macro ... A: I recommend a book: ... | |
| Removing a code after Save As | 10/7/2009 |
| Q: I have a Purchase Order Template (file name PO Template) that has the following code in it Private ... A: In the save routine you can use something like this (change range Z1 as necessary -- need some cell ... | |
| Cycling through sets of columns on a worksheet and copying contents into arrays in vba | 10/7/2009 |
| Q: I have some knowledge in both VB and general excel but would still consider myself very much a ... A: Still need more info. "...create an array of every row with data from that column set..." You want ... | |
| Cycling through sets of columns on a worksheet and copying contents into arrays in vba | 10/6/2009 |
| Q: I have some knowledge in both VB and general excel but would still consider myself very much a ... A: Can you rephrase your question being careful about your use of rows & columns? As it is, it's quite ... | |
| Excel 2007 Problems | 10/6/2009 |
| Q: Bob, I upgraded from Excel 2003 to 2007 recently. I thought that I knew a few things about VBA, ... A: 1 - the VBA is primarily the same, with the obvious enhancements to handle the new features. 2 - ... | |
| vba | 10/6/2009 |
| Q: Help please, my first post to one of these facilities, normally I struggle till I find a solution ... A: Change this: If Err.Number = 0 And Not rng Is Nothing Then 'found rws = ... | |
| Printing a common line above every row | 10/6/2009 |
| Q: I am a teacher. I have a spreadsheet with each quiz/exam and the points possible on row 1 and 2. ... A: Slight modifications... Follow these steps EXACTLY: 1 - in E1 and E2 enter 0 2 - in E3 enter 1 3 - ... | |
| Automatic sorting | 10/5/2009 |
| Q: I am tabulating votes which each participant votes for 5 others. SHEET1 has all particpant listed ... A: right-click the sheet tab of sheet3, select View code, put this in: Private Sub Worksheet_Activate() ... | |
| Printing a common line above every row | 10/4/2009 |
| Q: I am a teacher. I have a spreadsheet with each quiz/exam and the points possible on row 1 and 2. ... A: Not clear to me. You currently have this:?? Name Q1 Q2 Ex1 Pts poss 25 15 ... | |
| vba | 10/4/2009 |
| Q: Help please, my first post to one of these facilities, normally I struggle till I find a solution ... A: I'd have to see your modified code to color the cells. You can probably speed this up by putting ... | |
| Excel formulas | 10/2/2009 |
| Q: I have a spreadsheet that calculates a value based on a set of data. I have the data in another ... A: Sorry, I don't follow. First of all, if they're in the same workbook, even if on different sheets, ... | |
| need help writing a macro | 10/2/2009 |
| Q: I need a macro that will shift cells to the right if the cell equals the cell above it. I need this ... A: Sub MoveToRight() For i=Range("A1").end(xldown).row to 2 step -1 If ... | |
| vba | 10/2/2009 |
| Q: Help please, my first post to one of these facilities, normally I struggle till I find a solution ... A: Sub Finder() Dim Rg As Range, rws As Integer On Error Resume Next Application.DisplayAlerts = ... | |
| Formula to calc call time cost | 10/2/2009 |
| Q: Have call times expressed in minutes and seconds. i.e. 2m30sec or simply 2:30 or 2.30 Need to be ... A: Given it needs to take into account the 4 possible formats: 2m30sec 2m30s 2:30 2.3 this formula does ... | |
| CONCATENATE | 10/1/2009 |
| Q: VBA Expert Bob, Can you please help me with the following formula, I am using Excel 2000 version. ... A: Sub Concatenate() For i=2 to range("A65536").end(xlup).row Select Case Cells(I,1).Value ... | |
| Find text | 10/1/2009 |
| Q: I have been trying to find a way to do this for some time but cannot resolve it or know if it is ... A: Still not sure I follow fully, but to find the contents of cell B4 in one workbook in another ... | |
| Find text "workbook name" | 10/1/2009 |
| Q: I have been trying to find a way to do this for some time but cannot resolve it or know if it is ... A: I'm not sure I fully understand. You can select the cell with the reference to the closed workbook ... | |
| Importing data to Excel2007 from Access | 10/1/2009 |
| Q: Bob, I hope this is in your area..apologies if its not. I want to use Excel2007's "larger capacity" ... A: Not an Access person, but have a look at your setting in Office Button/Excel Options/Save -- top ... | |
| RE: Retrieving data from multiple worksheets | 9/30/2009 |
| Q: I have a set of multiple worksheets (20+). In each worksheet there is "methane" in column B. There ... A: after this line: ActiveCell.Offset(iSheet - 1, 0) = "'" & Worksheets(iSheet).Name put this code: Dim ... | |
| Excel data sorting | 9/29/2009 |
| Q: I have multiple columns, so for a particular client name, I have the corresponding country, solution ... A: Is this dropdown from a filter? If so, clearing the filter is equivalent to having "ALL" show up -- ... | |
| Extract data from one worksheet to many worksheets | 9/28/2009 |
| Q: I have a single spreadsheet with multiple rows and columns of data. I would like to take all the ... A: Not sure I understand -- in the above example, you'd want 4 sheets created, each looking like this: ... | |
| Importing CSV files into excel | 9/28/2009 |
| Q: I have a macro to import *.csv files into an excel sheet. It can import several files one below the ... A: Change: 'Copia los colores ActiveSheet.UsedRange.Copy ... | |
| Excel bill spreadsheet I created | 9/28/2009 |
| Q: PLEASE HELP! I need help with Excel to get the sum of column A2 thru A8 but only after I have filled ... A: Select cell B2 and then define a name, like yellow, to be =GET.CELL(38,Sheet3!A2)=6 In cell B2 ... | |
| Personal workbook | 9/26/2009 |
| Q: In my personal workbook I have the following in "This workbook": Private Sub ... A: It shouldn't. It refers, of course, just to personal.xls -- are you certain it's not referring to ... | |
| checking conditional formatting | 9/25/2009 |
| Q: In Excel '07, I've created many conditional formats and want to verify consistency and check for ... A: I'm sorry, but I know of know utility which will list all the conditional formatting settings in a ... | |
| before save | 9/24/2009 |
| Q: I need a code that it's executed when saving a workbook. It has to do a check for mandatory fields. ... A: Press Alt/F11, double-click "ThisWorkbook" in the project window, put in this code: Private Sub ... | |
| VBA | 9/24/2009 |
| Q: I am trying to write a vba program to copy data from one excel file to another. The excel file with ... A: It's distributing them to new sheets only if they don't exist already, based on the name. Clearly, ... | |
| VBA | 9/23/2009 |
| Q: I am trying to write a vba program to copy data from one excel file to another. The excel file with ... A: Sub Distribute() Dim Start As String, ws As Worksheet, BeginRow As Integer, curr As Worksheet On ... | |
| VBA | 9/23/2009 |
| Q: I am trying to write a vba program to copy data from one excel file to another. The excel file with ... A: Why not use the macro recorder and record the steps you want to do. It would record the opening of ... | |
| selectively updating | 9/22/2009 |
| Q: I have three different excel workbooks(different crops) with data on size, location and numbers of ... A: You can change formulas to values by selecting the cells, using edit/Copy, then edit/Paste special ... | |
| total time | 9/22/2009 |
| Q: I have an excel tracker in which an agent makes several attempts to contact the customer for every ... A: right-click the sheet tab, select View Code, enter this: Dim tm As Double Private Sub ... | |
| Importing data point (cell) from one excel file to another | 9/21/2009 |
| Q: I am trying to collect multiple data points which are located in various files. I am attempting to ... A: If they're open, type "=", then click on the cell you want to get the value from. If they're not ... | |
| Formula for date | 9/21/2009 |
| Q: I have an excel spreadsheet that tracks our reports at work and when we publish them. From the day ... A: =MIN(30,DATEDIF(G6,TODAY(),"d")) will stop at 30. Select the cell, use format/coditional ... | |
| combining a group of rows into one cell | 9/20/2009 |
| Q: Basically, I have two columns; in the column A I have a list of things and in the column B I have ... A: Here's a macro to do it: Sub Combiner() n = 1 BVal = Range("B1").Value ... | |
| Styles and formatting | 9/19/2009 |
| Q: Bob, I am working on a major project that is getting more and more complex all the time. My file is ... A: There is no builtin way to get a list of the number formats, unfortunately. You'd be better to try ... | |
| Copying & Pasting (top down format to left to right format) | 9/18/2009 |
| Q: I have a 60 worksheet document and am adding a summary worksheet at the front. Each page is for ... A: You can use a formula like this -- first select A1:Z1, for example, in the summary sheet, enter ... | |
| Excel 07 Conditional Formatting | 9/18/2009 |
| Q: I am making a training spreadsheet in Excel 07 and I need to use conditional formatting to see when ... A: First, color the range red (default) Next, with the range selected, and assuming G1 is the ACTIVE ... | |
| Highlight Duplicates | 9/18/2009 |
| Q: I'm trying to ascertain how to find duplicates within a sheet. This sheet contains 24 different data ... A: There's no way to do it for the non-contiguous ranges, but yuo may be able to get away with this. ... | |
| Excel nested if's | 9/17/2009 |
| Q: I have a gradebook assignment, I have to make a formula to enter a letter grade from a rounded ... A: That formula, as written, makes no sense. Just the first part, ... | |
| Document not saved | 9/16/2009 |
| Q: I write some data into excel using VB script. After writing all the data when i save the excel ... A: There are so many possible reasons for this. The most common: You're saving to a directory which is ... | |
| Data validation for multiple criteria | 9/16/2009 |
| Q: This is sami again, asking this time regarding a complex data validation list problem. i have a list ... A: Please send me a sample wb so I can show AND explain rather than just explain which is more ... | |
| (if(and vlookup??? | 9/16/2009 |
| Q: I am trying to do the following. I did get it to work, but i ran out of "nesting" at level 64! Here ... A: It looks like the criteria is always dependent on D6 being 8, so you can put all the other pairs of ... | |
| VB | 9/15/2009 |
| Q: Here are the exact details, In the same workbook i want to re-name tabs in (2) groups I want to ... A: You didn't answer myWhat should happen if BOTH L34 and K13 change as a result of the calculation? ... | |
| VB | 9/15/2009 |
| Q: Here are the exact details, In the same workbook i want to re-name tabs in (2) groups I want to ... A: Still unclear. If you change the value in the master so L34 changes on sheets 1->20, they all can't ... | |
| VB | 9/15/2009 |
| Q: Here are the exact details, In the same workbook i want to re-name tabs in (2) groups I want to ... A: This is a different scenario. Since they're protected, they only change (I assume) as the result of ... | |
| VB | 9/15/2009 |
| Q: Here are the exact details, In the same workbook i want to re-name tabs in (2) groups I want to ... A: Press Alt/F11, doubleclick "ThisWorkbook", enter this: Private Sub Workbook_SheetChange(ByVal Sh As ... | |
| Excel formula fire only once | 9/15/2009 |
| Q: I have a complex formula that looks to the last entry in a column on a previous worksheet and fires ... A: You didn't say which cell becomes true for this to "fire" If you can identify that cell, you can use ... | |
| adding columns in a pivot table | 9/15/2009 |
| Q: I'll be as brief as possible. I have a large sales information database with a pivot table as ... A: Not sure i can help if Excel says too many calcs, but if you can send me the (large) wb that ... | |
| Automatic data extraction? | 9/15/2009 |
| Q: I don't know anything about VBA programming, and I have been trying to solve the following problem ... A: ---(Sheet1!B:B) -> Why B:B and not $B:$B or B2:B100? Could have been B:B or $B:$B, referring to ... | |
| Excel Formula - Grabbing data from multiple worksheets by copying formula | 9/14/2009 |
| Q: I have a workbook with ~30 worksheets. I have a summary page that will reference the same ... A: If your sheets are named Sheet1, Sheet2, etc, then you can do it with dragging the fill handle by ... | |
| Excel formula fire only once | 9/13/2009 |
| Q: I have a complex formula that looks to the last entry in a column on a previous worksheet and fires ... A: Try making the workbook be manual calculation. Only when you ENTER the formula will the result ... | |
| excel 2007 | 9/13/2009 |
| Q: here is my problem: I'm trying to create an interative match schedule for the FIFA under 20 WORLD ... A: Sorry, I don't seem to be able to download the file at the website you provided. Do you have the ... | |
| Automatic data extraction? | 9/11/2009 |
| Q: I don't know anything about VBA programming, and I have been trying to solve the following problem ... A: If the starting worksheet is "Sheet1": if Spanish is in column B and Art in column C In a sheet tab ... | |
| Excel Macro Data Consolidation | 9/11/2009 |
| Q: Umlas I need to consolidate data from multiple worksheets (in separate workbooks) into one sheet ... A: There are too many things wrong with this code which would make it not work; I'll name a few. •You ... | |
| Autofilling dynamic data in a column | 9/11/2009 |
| Q: I am totally new to Excel VBA. So pardon me for any stupid questions. 1. Ok, so I have 3 columns ... A: 2 separate macros: Sub EnterFormulas() For Each x In Sheets x.Activate ... | |
| Cell Colour Query | 9/11/2009 |
| Q: Good Morning Is there a formula that can be created to solve a query based on the color of the ... A: Have cell B1 be the active cell. That is, select cell B1. Then, Excel 2003: Insert/Name/Define Use ... | |
| Autofilling dynamic data in a column | 9/10/2009 |
| Q: I am totally new to Excel VBA. So pardon me for any stupid questions. 1. Ok, so I have 3 columns ... A: I don't quite understand your questions. #1 doesn't need a macro -- enter your formula in the first ... | |
| Cell Colour Query | 9/10/2009 |
| Q: Good Morning Is there a formula that can be created to solve a query based on the color of the ... A: You can define a name, like TheColor, and if the active cell is B1 (important part) the refersto is ... | |
| Macro Help | 9/9/2009 |
| Q: Bob - Need support for writing a macro that will identify certain text in a cells for Column B in ... A: Why not just use a formula, like this in C1: =IF(B1="John","Doe","") and fill down? But if you ... | |
| Sorting | 9/8/2009 |
| Q: I am having a very hard time sorting in excel. I have a list of students last names and first ... A: You should use filtering - in Excel 2003 use data/filter/autofilter, then select the name you want ... | |
| Excel User Identification Prompt | 9/8/2009 |
| Q: I have the requirement to understand who uses an excel spreadsheet. I would like to be able to ... A: You can create a sheet to hold the names and hide it. Say you name it "XYZ". Use Alt/F11, ... | |
| Array might be needed? | 9/8/2009 |
| Q: I don’t know if this is possible to do or not. Please let me know if it is. I need code that counts ... A: Press Alt/F11, use Insert/Module, copy this in: option compare text Function Countx(Rg As Range, ... | |
| Excel 2003 - Mandatory Fields | 9/3/2009 |
| Q: The sheet records data for users who leave / join the company. Basics are 25 columns with data under ... A: Press Alt/F11, double-click the "ThisWorkbook" you'll see in the project window, put in this code: ... | |
| IF THEN Macro Help | 9/2/2009 |
| Q: Hey Bob, First off, I have excel experience from school but very very little experience with macros ... A: Your ENTIRE macro can be: Sub Vendor() Dim DestSheet As Worksheet Dim sRow As Long 'row index ... | |
| Rectangles | 9/1/2009 |
| Q: I am using Excel 2003 and my partner is using Excel 2007. An unusual problem has arisen that has ... A: Unfortunately, you've found a (nasty) bug, and I'm afraid I don't know of a workaround. Simply ... | |
| Copy similar data (specific columns) from multiple excel files into one | 9/1/2009 |
| Q: I have multiple excel files with two worksheets (TicketDetails and RawData). I am using RawData ... A: I don't understand. What do you mean by "...a different excel." A new workbook? Is there any ... | |
| Excel Date Question | 8/20/2009 |
| Q: These dates relate to certain expiry dates. I would like a formula in the cell that automatically ... A: a few questions: 1 - when you say "the date has more than 6 months to run" you mean the date is more ... | |
| ledger | 8/20/2009 |
| Q: I have a worksheet of accounts receivable and other worksheet of collections, I need to place ... A: You can use a helper column to see if the date is older than 30 days, something like this (assuming ... | |
| VBA match and copy cells. | 8/19/2009 |
| Q: At the outset, let me say if this is too involved, I understand. Thanks for any consideration. ... A: Sub Untested() dim N as integer, WB1 as Workbook, WB2 as workbook Set wb2 = Workbooks("2ndWB.xls") ... | |
| VBA ranges & Pivot Tables | 8/19/2009 |
| Q: Bob! I hope you could help me out with a couple of VBA problems: 1) Copying a selected range from ... A: Well, it wouldn't be a button because that would also reside on the workbook which contains the ... | |
| dependent validation lists with dynamic ranges | 8/19/2009 |
| Q: I am creating a spreadsheet in excel 2003, with three columns. In column a the cells are validated ... A: 3rd time's the charm :-) one minor adjustment, sorry! Fill corrected answer here: Suppose the ... | |
| VBA ranges & Pivot Tables | 8/19/2009 |
| Q: Bob! I hope you could help me out with a couple of VBA problems: 1) Copying a selected range from ... A: You can get all that info from this: Sub GetTheInfo() Dim TheRange As Range, TheWorkbookName As ... | |
| Insert | 8/18/2009 |
| Q: Hey Bob, im using Excel 2003 and im trying to create a macro that inserts columns with a vlookup ... A: This will work except that I can't finish the VLOOKUP formula because I don't know where you're ... | |
| VBA match and copy cells. | 8/18/2009 |
| Q: At the outset, let me say if this is too involved, I understand. Thanks for any consideration. ... A: oops. Sub Untested() dim N as integer, WB1 as Workbook, WB2 as workbook Set wb2 = ... | |
| VBA match and copy cells. | 8/18/2009 |
| Q: At the outset, let me say if this is too involved, I understand. Thanks for any consideration. ... A: Sub Untested() dim N as integer, WB1 as Workbook, WB2 as workbook Set wb2 = ... | |
| conditional formating for dates | 8/18/2009 |
| Q: I am trying to use conditional formatting to make the fill color of the cells reflect status based ... A: select all the cells with dates that are to take on this formatting. Conditional formatting has up ... | |
| Hide Formula errors in excel 2003 | 8/18/2009 |
| Q: How to hide formula errors such as: #REF!,#NAME?, #NUM!, #N/A and 0 (Though '0' is not error, still ... A: No need to use macro unless you insist, and then you can record these steps: Select ALL cells ... | |
| VBA ranges & Pivot Tables | 8/17/2009 |
| Q: Bob! I hope you could help me out with a couple of VBA problems: 1) Copying a selected range from ... A: 1 - Sub CopyRange() With ... | |
| How to enable delete formula cell | 8/17/2009 |
| Q: I have following code to hide and protect cells having formula. But the problem is that once i ... A: If you want to be able to delete formulas, why bother with this routine at all? You CAN unlock the ... | |
| Converting Letters | 8/17/2009 |
| Q: Hey Bob, My follow-up question option has been maxed out, but just to help you refresh the question ... A: Function MyCode(rg As Range) As String Const Codes As String = "ABCDEFGHIJ" '<========change ... | |
| VBA Script | 8/15/2009 |
| Q: i have a large amount of data in sheet 2 columnA as shown below.i need the unique values of the data ... A: Still confusing. How do you go from PARIS.1, LineWest-A Slot 302 STM-1 1 1-2-6-1 <-> Trib3 Slot ... | |
| Converting Letters | 8/15/2009 |
| Q: Hey Bob, My follow-up question option has been maxed out, but just to help you refresh the question ... A: No idea. It works just fine for me! I'm using: Function MyCode(rg As Range) As String Const Codes ... | |
| Sum of Lookups | 8/14/2009 |
| Q: I'm using Excel 2003 SP3. I have a matrix in cells B1:D4 as follows: p1 p2 p3 0.33% 0.10% 0.12% ... A: Ctrl/shift/enter: =SUM(N(OFFSET(A2,0,MATCH(lookupnames,$B$1:$D$1,0))) * $B$27:$C$27) INDEX only ... | |
| VBA code to capture data from Windows Explorer to Excel | 8/14/2009 |
| Q: Just wondering if you could help me with a VBA code that would capture data directly from Windows ... A: Actually, it doesn't clarify it for me. What's your "original template"? What's its name and how is ... | |
| VBA Script | 8/14/2009 |
| Q: i have a large amount of data in sheet 2 columnA as shown below.i need the unique values of the data ... A: I can't figure out the relationship between your input & output. Why is the 3rd line of input not in ... | |
| Sum of Lookups | 8/14/2009 |
| Q: I'm using Excel 2003 SP3. I have a matrix in cells B1:D4 as follows: p1 p2 p3 0.33% 0.10% 0.12% ... A: I don't understand quite a few things here -- like: p2 p1 0.4 0.2 what is that? or what's in ... | |
| Converting Letters | 8/13/2009 |
| Q: Hey Bob, My follow-up question option has been maxed out, but just to help you refresh the question ... A: You can't use BIWAH with this code because you never gave me the translation for "W". You really ... | |
| copy range between 2 workbooks | 8/12/2009 |
| Q: First I want to say Thank you for taken your time to read my question. I have 2 workbooks and each ... A: Make the changes as indicated, if necessary: Sub CopyCol6() wb1="Workbook1.xls" '<====change ... | |
| Master sheet | 8/12/2009 |
| Q: i want to create a master excel sheet. If i work on another sheet and my colleague also doing the ... A: There's no such thing as "automatically updated in the master sheet" -- I'd need to know a lot more ... | |
| How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Row) | 8/12/2009 |
| Q: i want the following data from INPUT Sheet to be shifted to OUTPUT sheet in following way using VBA, ... A: Sub Rearrange() Dim n As Integer, TheRow As Integer, TheCol As Integer, i As Integer, Cur As ... | |
| hide formula in excel 2003. | 8/12/2009 |
| Q: How to hide formula but allow to copy it(formula)in excel 2003 workbook. A: 1 - View/Formula Bar (uncheck it) - but anyone can redisplay it; 2 - Format cells/protection/selcet ... | |
| Data Entry | 8/11/2009 |
| Q: I have 4 sheets in one workbook. I have a "main" sheet which I would like to input my data and then ... A: If it's in the same cell as the main sheet, you can right-click a sheet tab, select "Select All ... | |
| Input box for entry deletion macro | 8/11/2009 |
| Q: I am writing a macro that formats an Excel 2007 sheet with many entries. The entries have different ... A: Sub DeleteCodes() Dim ToDelete As String, i As Integer, NumDeleted as Integer ToDelete = ... | |
| Input box for entry deletion macro | 8/10/2009 |
| Q: I am writing a macro that formats an Excel 2007 sheet with many entries. The entries have different ... A: This code deletes cells from column A - adjust as necessary... currently deletes cell, not entire ... | |
| Large function, relative value | 8/10/2009 |
| Q: I know how to use Large function to get largest& smallest number in a range. MyHow do i get the ... A: Need a few helper columns, say G & H. In G1: =COUNTIF($D$1:D1,D1)-1 Fill down to G5. in H1: ... | |
| Large function, relative value | 8/10/2009 |
| Q: I know how to use Large function to get largest& smallest number in a range. MyHow do i get the ... A: I still need to know HOW you want to see this. You want to see, in ONE cell, "Andy and Peter"?? Do ... | |
| Pivot Table | 8/9/2009 |
| Q: In the attachment Balance Column is not in Pivot Table. 1- I would like to create Borders against ... A: You can't do this automatically - you either need a macro to do both the pivot table and this extra ... | |
| Converting Letters | 8/9/2009 |
| Q: Currently, I have a list of codes in my excel spreadsheet that I need to decode. Our code uses ... A: It's a lot easier to use a user-defined function. Press Alt/F11, use Insert/Module, copy this in: ... | |
| comparing two excel spreadsheets | 8/8/2009 |
| Q: Is there an automatic way to compare data on two different excel spreadsheets? Both spread sheets ... A: I don't understand the part about "identify a donation..." There's no really good way to compare ... | |
| Trying to copy a cell contents once validated | 8/6/2009 |
| Q: I have a set of data, as below: room serial item status 104 00060916 CHRD010 ... A: A formula won't really do it, you need VBA code -- Assuming the sheet with all the data is on Sheet1 ... | |
| Adding rows in a protected worksheet | 8/5/2009 |
| Q: I'm working with Excel 2004 for Mac. I've created a job costing template with mixed locked/unlocked ... A: Not familiar with Max excel, but in PC excel, when you protect a sheet you have the option of still ... | |
| cell input & alphabetization | 8/3/2009 |
| Q: Is there a way to input a value (ex. last name) into a cell and then have that value added into an ... A: XL 2003: Right-click any toolbar button, select Forms, click on the button icon and draw a button ... | |
| cell input & alphabetization | 8/3/2009 |
| Q: Is there a way to input a value (ex. last name) into a cell and then have that value added into an ... A: Then you'd need a button to run the code to move the 2 together. The button's code could be ... | |
| cell input & alphabetization | 8/3/2009 |
| Q: Is there a way to input a value (ex. last name) into a cell and then have that value added into an ... A: If the list is in column D and the trigger cell is A1: right-click the sheet tab, select View Code, ... | |
| Adding or updating figures in Excel 2003 | 8/1/2009 |
| Q: Bob, let say A1=2 and B1=5 then i add B1 with A1 in B1 which is = 7 i.e. B=7 (i entered this ... A: This works on anything entered in column A will be accumulated into column B, same row: Private Sub ... | |
| Adding or updating figures in Excel 2003 | 7/31/2009 |
| Q: Bob, let say A1=2 and B1=5 then i add B1 with A1 in B1 which is = 7 i.e. B=7 (i entered this ... A: This can be done with this macro - right-click the sheet tab, select View Code, enter this: Private ... | |
| min if | 7/30/2009 |
| Q: I have two columns: A B 45 5 42 3 55 7 42 7 Initially I created a formula to find the lowest ... A: Lowest A: ctrl/shift/enter: ... | |
| Creat a Shortcut | 7/30/2009 |
| Q: your last answer is very fantastic i really like it. my next question is instead of creating index ... A: Assuming you already have the macro: Sub ShowIndex() Sheets("Index").Activate End Sub then you ... | |
| Chart data range, indirect | 7/29/2009 |
| Q: "Hi Bob, using Excel 2007. Using a Chart in the "data range" field i would normally use, ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Looking up data from a table, in which the data being pulled has multiple criteria or variables | 7/29/2009 |
| Q: At my job for a wholesaler, I am creating a form used to choose filters our customers buy. The ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Create a Button in Excel | 7/29/2009 |
| Q: I have excel file which contain 30 sheet on which i want to work daily. My question is thereany way ... A: If you insist on using buttons you will have to create the macro: Sub ShowIndex() ... | |
| 2 criteria lookup or match | 7/29/2009 |
| Q: Could you please help in making formula for double criteria match. i am using excel-2007 i have ... A: Easiest way is to make the #N/A values not show. Select the column, use Home/Conditional Formatting, ... | |
| Excel Calculator | 7/28/2009 |
| Q: .:-) I got with me a price list with the variables like; [1] Size of Book [2] Binding Type ... A: I have no idea what your desired output is, where you're getting numbers like 11.35 or 10.17, 12.53, ... | |
| To transpose data in excel | 7/28/2009 |
| Q: My name is suchitra . I need help to transpose set of column data to row data in excel. for Ex: 123 ... A: Sub ABC456() Dim Stp As Boolean 'output in column C n = 0 For i = 1 To ... | |
| Changing cell colour depending on the date | 7/28/2009 |
| Q: In excel I am wanting a cell to change colour depending on the date entered. I would like the cell ... A: Assuming xl 2003: Select all the dates involved, and assuming the active cell is A1, use ... | |
| How to Make duplicated column data into a Row using Excel VBA? | 7/28/2009 |
| Q: How to Make duplicated column data into a Row using Excel VBA? Input Data as follows... (first rows ... A: Sub Rearrange() Dim First As Boolean First = True Dim hold, n As Integer, m As Integer, q As ... | |
| Run a macro to anther sheet or workbook | 7/27/2009 |
| Q: I would like to seek for your assistance, I am not a programmer guy and I want to run the macro I ... A: You want the same macro which is currently run from a button on sheet1 to run on sheet 2 or on ... | |
| To Paste Jpg Images form Folder | 7/27/2009 |
| Q: You had provided me the below code which is working wonderful. It searches for the file name which ... A: I don't know of a way for Excel to open a .jpg file -- the process would normally be done outside ... | |
| Excel 2003 problem | 7/27/2009 |
| Q: I have multiple excel 2003 sheets linked up to a summary sheet, what I would like to be able to do ... A: It's a very difficult thing to do unless you can assure me that the only kinds of formulas are ones ... | |
| Importing data from multiple sheet in one sheet | 7/23/2009 |
| Q: I need this badly!!!!!!! I want to copy data form Sheet1, sheet2 & Sheet3 to Sheet4. I want the ... A: Sub AnswerUntester() Sheet1.UsedRange.Copy Sheet4.Range("A1").PasteSpecial ... | |
| Autofiltering Protected Worksheet | 7/23/2009 |
| Q: Bob - I have a macro that is called by the Workbook_Open event to Protect specified worksheets in ... A: If Val(Application.Version)<10 then 'xl2000 or less msgbox "You can't use filtering on a ... | |
| lookup in excel, max minus min values | 7/23/2009 |
| Q: I have a problem that I think requires a formula that goes beyond my excel knowledge. In column b I ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| To remove / hide Screentips of Hyperlinks | 7/23/2009 |
| Q: I have the following code (provided by you,, thanks for that) which is working great. This code ... A: Untested: Sub Hyperlink_Fast() 'It is compatable wit Excel 2007 Dim MyFolder As String MyFolder = ... | |
| Cell Display Based on Current Date | 7/23/2009 |
| Q: I am wondering if there is a formula that will look at the current date and place my chosen info ... A: Put all the 13 dates in A1:A13. Enter this formula where you want: ="PERIOD "&MATCH(NOW(),A1:A13,1) ... | |
| cell interior color | 7/23/2009 |
| Q: I am using a macro to from the internet that gives me the cell interior color but I have noticed ... A: Colorindex will only return values from 1 to 56, but Excel can handle 16,000,000+ colors, so there's ... | |
| Disable cut and copy only | 7/22/2009 |
| Q: I am working with a spreadsheet where I want to disable cut and copy (only) to prevent users from ... A: The problem with this is that the user can copy by ctrl/c, edit/copy, click a toolbar button, ... | |
| vlookup in pivot table | 7/22/2009 |
| Q: I have this data: Period Time Code Price Qty 11:00 11:04:57 ACGC 5.9 6597 ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| hit a wall in excel :( | 7/21/2009 |
| Q: I am working on an Excel spreadsheet and am running into some roadblocks with some formulas to ... A: You can see if something's not found via something like this: ... | |
| Excel checkbox hide code | 7/20/2009 |
| Q: Good afternoon, I am trying to make checkboxes hide when rows are hidden. Another expert at this ... A: Press Alt/F11, use Insert/Module, then put the code you gave me in there. Alt/Q will return you to ... | |
| Cell Display Based on Current Date | 7/20/2009 |
| Q: I am wondering if there is a formula that will look at the current date and place my chosen info ... A: This formula: = IF(AND(NOW()>12/28/8,NOW()<1/24/9),1) is comparing NOW() to 12 divided by 28 divided ... | |
| Excel 2007 "MAX" function | 7/16/2009 |
| Q: I have sucessfully gotten "{=MAX(IF('7413'!$B:$B=1,IF('7413'!$D:$D=2008,'7413'!$I:$I)))}" to give me ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Sumproduct (multi) | 7/16/2009 |
| Q: I am hoping you can help me with a sum product, possibly count if formula. Something I am doing is ... A: The formula is right, but I'd need to see the data -- perhaps RVP is really "RVP " (with ending ... | |
| Combining/Matching Data From two sheets | 7/15/2009 |
| Q: I have two spreadsheets both have serial numbers for items but only one has base values for the ... A: If the serial#s for sheet1 is in column A and the base values are in B, and if the serial#s for ... | |
| Excel copy and paste Macro | 7/14/2009 |
| Q: I'm trying to write a macro that will copy only CERTAIN worksheets within a file, to a summary ... A: Sub CopyToSummary() Dim ws As Worksheet Dim wsDest As Worksheet Dim wsAppPiv As ... | |
| Auto-populate data to a master worksheet from other sheets | 7/14/2009 |
| Q: I wish auto populate data from one workbook to several master worksheets using text information from ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| To hide Rows If column has certain text | 7/14/2009 |
| Q: I have a sheet which has data. Top row has headings. My users also view that sheet. I need to keep ... A: In a regular module, put this macro, which you need to associate with the click of the checkbox ... | |
| ranking highest occurance of largest values | 7/13/2009 |
| Q: I have a chart that shows daily sales amounts within a month, rounded to the nearest thousand. When ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| vlookup formula with multiple array - IF statement based | 7/13/2009 |
| Q: want to use the vlookup formula to retrived value to pick value from sheet 1, 2 and 3 in sheet 4. ... A: I don't quite follow -- Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", ... | |
| TextBox due date | 7/13/2009 |
| Q: I would like to make a userform with a text box that will popup whenever I open the file and show ... A: Private Sub Workbook_Open() Load UserForm1 On Error Resume Next For i = 1 To ... | |
| To hide Rows If column has certain text | 7/13/2009 |
| Q: I have a sheet which has data. Top row has headings. My users also view that sheet. I need to keep ... A: right-click the sheet tab, select View Code, put this in: Private Sub Worksheet_Activate() On ... | |
| TextBox due date | 7/12/2009 |
| Q: I would like to make a userform with a text box that will popup whenever I open the file and show ... A: Assuming column A has the "due dates": In the Workbook_Open event: Private Sub Workbook_Open() ... | |
| Alternate record shading based on column value changes | 7/10/2009 |
| Q: I can't seem to find this question/answer and this is my first visit so will ask it: I want to ... A: You need another helper column. If I is available, then in I2 enter this formula using ... | |
| Alternate record shading based on column value changes | 7/10/2009 |
| Q: I can't seem to find this question/answer and this is my first visit so will ask it: I want to ... A: You need a helper column. Say you have col H available. Enter this in H2: ... | |
| Alternate record shading based on column value changes | 7/10/2009 |
| Q: I can't seem to find this question/answer and this is my first visit so will ask it: I want to ... A: If it weren't a filtered list, it'd be pretty straightforward, but since it's filtered, and you may ... | |
| Worksheet name in formula (Indirect function) | 7/9/2009 |
| Q: I am trying to create a workbook using the indirect function. For example, =INDIRECT(A1&"!E12") Cell ... A: If the formula you're entering is in column A: =INDIRECT($A$1&"!r12c"&COLUMN()+4,FALSE) will do the ... | |
| Excel Chart | 7/9/2009 |
| Q: I work with chart on excel and I can't figure out how to convert a horizontal chart (with X = depth, ... A: Are you talking about changing a bar chart to a column chart? You may alco have to change which are ... | |
| File name from a formula | 7/9/2009 |
| Q: How can do this? ='[1BW108-1.xls]Review'!$F$55 where 1BW108-1 is on C8. I have a list of excel ... A: Assuming the other values are in C9, C10..., you can do this (follow the steps precisely): If the ... | |
| cell protection | 7/8/2009 |
| Q: Greetingss. Thanks for being there I have protected a worksheet in excel, and would like to ... A: You protect a worksheet, not cells. Before you protect the worksheet you FORMAT certain cells to not ... | |
| VB Coding for AutoCapitalize | 7/8/2009 |
| Q: I'm currently using the following VB code to autocapitalize: Private Sub Worksheet_Change(ByVal ... A: Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Column Case 3,4,6,12,13 'etc ... | |
| Time difference | 7/7/2009 |
| Q: The scenario is like when I enter a text in column A then column B should take the time of the text ... A: Right-click the sheet tab, select View code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| Golf | 7/7/2009 |
| Q: We have a golf group that plays once a week. Each person has a handicap and we may have 12 to 16 ... A: Sorry, I KNOW what you want, but your ilustration doesn't show HOW you got there -- trial and error ... | |
| To update Master Record with Yearly Record | 7/7/2009 |
| Q: In response to my followup question you answered the below. After incorporating the below code, it ... A: put the entire path in this line of code: If Err.Number<>0 then Workbooks.Open "C:\yada yada\yada ... | |
| Golf | 7/6/2009 |
| Q: We have a golf group that plays once a week. Each person has a handicap and we may have 12 to 16 ... A: I understood the problem, but again, this is a MATH problem, not an excel problem. You told me what ... | |
| Excel Help | 7/6/2009 |
| Q: Please help me I need help concerning an excel work sheet (see below) in column D, when the date ... A: Select all of column A&B, use conditional formatting (Excel 2003 assumed -- get back to me if it's ... | |
| To update Master Record with Yearly Record | 7/6/2009 |
| Q: I have a workbook "Master Record" which contains data of employees with column headings. This data ... A: The usedrange of sheet2 is ONLY the new records, and does not include all the cells. If you've ... | |
| To update Master Record with Yearly Record | 7/4/2009 |
| Q: I have a workbook "Master Record" which contains data of employees with column headings. This data ... A: Untested, but this should work: Put this in the Thisworkbook code for the Record 2009 workbook: ... | |
| time | 7/3/2009 |
| Q: I am trying to creat a timesheet for my daughter's work. What I want to do is at the top heading is ... A: Is 7:30-3:30 all in one cell? No AM or PM? If not, can I assume the first is ALWAYS AM & the 2nd ... | |
| Query regarding Creating a data base in Excel. | 7/3/2009 |
| Q: I have a column in worksheet 1 which has 70 values , i.e from 1-70,all these 70 values have a amount ... A: Sorry, I don't understand. What do you mean by "an amount calculated against them"? How is this ... | |
| To update Master Record with Yearly Record | 7/3/2009 |
| Q: I have a workbook "Master Record" which contains data of employees with column headings. This data ... A: It's tough to do in the close or save event because say you've entered 25 records. When you save, ... | |
| finding values in a column group then copying and pasting them | 7/3/2009 |
| Q: I am trying to figure out a way to search a column group for a matching row from a different column ... A: You can do that with formulas. In E1: ... | |
| Excel VP Code | 7/2/2009 |
| Q: I had previously asked a question about a formula and the expert replied back with VP Code. The code ... A: 2 things come to mind: the cell is protected; you're in a event loop. In the latter case, surround ... | |
| VLOOKUP or Match Index | 7/2/2009 |
| Q: or My question is that I have some info that I need to verify and match against another ... A: I can't tell you if there's an easier way because you gave me nothing to compare it against. How are ... | |
| Sorting | 7/2/2009 |
| Q: How do I sort columns while keeping the entries in the entire row intact? Eg, If I sort according to ... A: That's how the sort works inherently. Are you selecting one cell or all the cells you want to ... | |
| IF ELSE | 7/1/2009 |
| Q: Below are the two difference If conditions in my macro code ====================================== ... A: Not sure what's not working except this logic, as I indicated previously: If Left(Cells(i, 2), 2) <> ... | |
| IF ELSE | 7/1/2009 |
| Q: Below are the two difference If conditions in my macro code ====================================== ... A: First off, Left(cells(i,2),2) will NEVER = "RED". It might equal "RE", but you're only comparing 2 ... | |
| IF function, multiple variables | 6/30/2009 |
| Q: First I have a table with itemid A1 and then 2 different variables, B1 and C1. In B1 the number can ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| excel formula | 6/30/2009 |
| Q: I am in the process of creating a spreadsheet for a walking program that we are doing. I need to ... A: I don't quite follow. Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", ... | |
| To Copy Multiple Cell Values in One Cell | 6/30/2009 |
| Q: I have a sheet column A has ID numbers like "20769" column B hase Name Column C has Department Names ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Time sheet calculations | 6/30/2009 |
| Q: Well, here we go......I am looking to create a time card spreadsheet I would like it to calculate, ... A: Print screen you sent is not really readable/usable. Send a sample wb to me at bobumlas@yahoo.com, ... | |
| excel problem | 6/30/2009 |
| Q: I am doing a Excel (2007) spreadsheet and have columns for debits and credits. To verify entry ... A: Most likely the issue is precision. Put your formulas inside a ROUND function, like ... | |
| Excel 2003 box displays an X | 6/29/2009 |
| Q: I have a spreadsheet set up so that an auto shape box of the exact size I need displays an X when a ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Excel Aging | 6/29/2009 |
| Q: Bob, How do I set up an aging in Excel that will only calculate based on date. I would like to be ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Excel 2003 - lock a cell automatically when data has been inserted into it? | 6/29/2009 |
| Q: it is me again, asking another question? I have a spreadsheet where many people will be using it. ... A: ANY cell? What do you mean by "repeated in the corresponding cells beneath it?" Send a sample wb to ... | |
| Excel 2003 - automatically transferring data from 1 sheet to another | 6/29/2009 |
| Q: I have rows of information with column headings. When I type in a date into a cell I want that ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Reference another worksheet in VBA | 6/25/2009 |
| Q: I have the following function to find the last row of the active worksheet. How would I change that ... A: change ActiveSheet to Sheets("A") or whatever the name of the sheet is. To make it more generic, use ... | |
| Excel Formula | 6/25/2009 |
| Q: I am trying to generate a random list of 5185 numbers between the range 556983-1554848 using the ... A: A random # between your 2 values would be =556982+INT(RAND()*997865) so fill A1:A5185 with that ... | |
| Macro For Formating | 6/24/2009 |
| Q: I have a sheet which has entries in rows. Column E contains start dates for programs. I need a code ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Auto Numbering Purchase Orders | 6/23/2009 |
| Q: Hey there, this one should be easy for you. I'm currently using : Private Sub Workbook_Open() ... A: Youo can't auto-# the template because it doesn't get saved and has the same # every time you open ... | |
| Macro For Formating | 6/23/2009 |
| Q: I have a sheet which has entries in rows. Column E contains start dates for programs. I need a code ... A: CF formula: =AND($E1<TODAY(),$E1>0) code change: Private Sub Workbook_Open() For i = 1 To ... | |
| Expiry of Spreadsheet after # of uses | 6/23/2009 |
| Q: I found one of your answers regarding how to create a Macro so that a spreadsheet has a limited ... A: You can make it still unusable by hiding all the sheets except one which says something like "You ... | |
| Excel date formula | 6/22/2009 |
| Q: .having so much trouble trying to get a formula right for dates (trying to put one into conditional ... A: I do, but it's pretty much impossible to use one month after today's date without detailed ... | |
| Macro For Formating | 6/22/2009 |
| Q: I have a sheet which has entries in rows. Column E contains start dates for programs. I need a code ... A: #1 doesn't need code. Select all cells, use format/conditional formatting, change "cell value is" to ... | |
| .csv data source for Pivot Table | 6/21/2009 |
| Q: I have been searching and can't find an answer to this one... I am using excel 2003 I have a csv ... A: If there's a "$12.00 format" in the csv file, then it can't be a number, because csv files can't ... | |
| Using Excel conditional formatting | 6/20/2009 |
| Q: I'm working a large spreadsheet (roughly 20 columns x 8000 rows) and believe that conditional ... A: Excel 2003: Select All of column A, (cell A1 will be the active cell) use Format/Conditional ... | |
| Dsum question and Copy from closed workbooks macro | 6/20/2009 |
| Q: Hey Bob, I was wondering if you could help me with a few things: Dsum is a perfect formula for me ... A: I don't know what column the CI values might be in, but if they're in column B, for example, and the ... | |
| Dsum question and Copy from closed workbooks macro | 6/19/2009 |
| Q: Hey Bob, I was wondering if you could help me with a few things: Dsum is a perfect formula for me ... A: DSUM REQUIRES that the criteria range be contiguous. Can't be done with A1,A3. However, if you let ... | |
| Sort by Font | 6/19/2009 |
| Q: I would like to sort a list by the font style. is there a way to do it with a VBA code? Thanks ... A: Sub SortFont() Dim Col As Range, toSort As Range Set toSort = Application.InputBox("Click on an ... | |
| Excel Multiple file opening | 6/19/2009 |
| Q: I need to open multiple files for a macro, which uses the code below. filenames = ... A: Where you have the ???? you only have ONE file open. Work on it (it's already active) and when ... | |
| Masking Data | 6/19/2009 |
| Q: I have a data sheet that contains sensitive data and I want to mask some of it - however certain ... A: You can have a style which has a font of white and you can protect these cells and include "Locked" ... | |
| saving a file in excel from a cell and creating a folder based on that cell name | 6/18/2009 |
| Q: Below is part of the code I use. I was wondering if there is a way to have it save under the same ... A: I don't know about UNC paths -- never used them. But in general you can see if a folder exists by ... | |
| Excel 2007 | 6/18/2009 |
| Q: I'm trying to find a macro that changes the cell red after it passes a date entered into the cell. ... A: No macro needed. Select all the cells for which this is to happen, use Home/Styles/Conditional ... | |
| two way lookup withen ranges | 6/18/2009 |
| Q: could you please help me in two way lookup within ranges. i will attach image file for you to ... A: Instead of "250 to 400" etc, just have the 250 as a regular number. Instead of 200, have 0. And by ... | |
| Data transfer between two worksheets | 6/18/2009 |
| Q: I only have a very basic knowledge of excel. But it seems that I need to build a macro to perform ... A: Very confusing description: 2 sets of data input into one column. I don't understand. Save that in 2 ... | |
| two way lookup withen ranges | 6/18/2009 |
| Q: could you please help me in two way lookup within ranges. i will attach image file for you to ... A: The way your table is laid out you can't really look up values -- the top row & left column bust be ... | |
| To Restrict Scroll Area For Users | 6/18/2009 |
| Q: Der Bob You had advised me the following code to restrict scrolling to data area only for all ... A: I don't understand how YOU specify the last row -- where/when do you do this? Without knowing the ... | |
| Questions for Excel... | 6/16/2009 |
| Q: I am sorry if this has been ask more than once but I am having problem in my report. What I want is ... A: A table with all the values needs to already exist somewhere so you could look up the values. If ... | |
| vba 1004 runtime error | 6/16/2009 |
| Q: I just don't know why it doesn't work. This is how it goes. I am given a crapload of data to ... A: You have this line: Activecell.Offset(501,0).Select inside a loop which gots from 1 to 4952. So ... | |
| Macro on imported external data. | 6/16/2009 |
| Q: I'm new to Excel macros and would like to ask for some help. I'm trying to apply the macro on the ... A: You might try this -- run your macro automatically when the worksheet changes -- but then it'd run ... | |
| excel 2002 Auto recover | 6/16/2009 |
| Q: Shortly after rebooting WinXP SP3, I started Excel which presented Document Recovery pane, when it ... A: I NEVER trust document recovery -- always has a problem for me. Your best bet is to set autosave for ... | |
| Cell Safeguarding | 6/15/2009 |
| Q: Bob, I have the necessity of a cell where the value can be changed, but not deleted. For example. ... A: Press F11 to get to the VBE, double-click the "ThisWorkbook" kin the VBAProject, put this in: ... | |
| Cell Safeguarding | 6/15/2009 |
| Q: Bob, I have the necessity of a cell where the value can be changed, but not deleted. For example. ... A: Even if you disabled the Delete key in the cell, a user could type a space then a backspace to also ... | |
| Bonus Sheet | 6/15/2009 |
| Q: I have produced a spreadsheet that will calculate bonus payments using this formula: ... A: Still not sure I fully understand, but maybe this does the trick: ... | |
| Dynamic Charting - Odd Chart type, causes problems | 6/15/2009 |
| Q: I am having an issue with a chart which is called "Line - Column with 2 Axes" under the custom tab. ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| To Adapt To User's Screen Resolution | 6/15/2009 |
| Q: I want that all the sheets (Zoom %)of my workbook template be adapted to the screen resolution of ... A: The statement Sheets("Sheet2").Range(...).Select only works when Sheet2 is ALREADY active. ... | |
| Bonus Sheet | 6/14/2009 |
| Q: I have produced a spreadsheet that will calculate bonus payments using this formula: ... A: First, when I copy/paste that formula into Excel I get an error, so please correct it. 2nd, can you ... | |
| help in writing macro in excel. | 6/13/2009 |
| Q: I am a graduate student and need to write a macro for data analysis. I am badly stuck as the excel ... A: I'm confused. In one place tou say to match A,B,C,d, and later you mention A,B,F,G -- so which ... | |
| calendar | 6/12/2009 |
| Q: I am hoping you can help me with a project i am working on. I work in DVD productions and we do a ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Excel - Date based on cell change | 6/11/2009 |
| Q: Hope you can help. I am trying to update a date field in column D based on change in formula value ... A: The worksheet_Change is triggered, but your code kicks it out because of Target.Column = 3... You ... | |
| Saving form to a specific drive and folder | 6/11/2009 |
| Q: Welcome back: You kindly provided me with the code below and it works well but I was wondering if ... A: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True ... | |
| reformat a spreadsheet | 6/11/2009 |
| Q: I download csv files from ebay that show the details of sales I have made. I have to send this ... A: You can more easily do it by sorting the columns into the sequence you want and then deleting the ... | |
| Adding time frames in Excel (ex: weekly work schedule) | 6/10/2009 |
| Q: Is there a formula that can add the hours scheduled on a weekly work schedule? For example, if I ... A: Given this specific layout, which is in cells A1:D4, then put this in E2 (but it MUST be ... | |
| Multiple value’s in one cell. | 6/10/2009 |
| Q: I have Excel 2007 and a table as shown in the image and my question is how do I get multiple ... A: Offhand, I'd say this does require a function macro -- what's your hesitation? But I'll take another ... | |
| Macro To Sort Data | 6/10/2009 |
| Q: I have data on the sheet form range A1:L613. this range is dynamic i.e data is frequently being ... A: In the code behind "ThisWorkbook": Private Sub Workbook_BeforeClose(Cancel As Boolean) SortIt End ... | |
| Adding time frames in Excel (ex: weekly work schedule) | 6/9/2009 |
| Q: Is there a formula that can add the hours scheduled on a weekly work schedule? For example, if I ... A: Does ONE cell contain Monday- 9:00am-4:00pm or is this in 3 cells: A B C ... | |
| Hide rows | 6/9/2009 |
| Q: Okay, so I have heard that there is not a formula to hide an entire row in Excel, but instead one ... A: I don't fully understand all of your question. It's true that formulas cannot change row heights (or ... | |
| Macro to Generate User Form | 6/9/2009 |
| Q: I would like to know if it is possible to build a macro that automatically generates a user form. ... A: It's possible, but I never do it, so have no expertise there. -- perhaps you can create a userform ... | |
| Complex VLOOKUP formula | 6/9/2009 |
| Q: Please breakdown this VLOOKUP formula: =VLOOKUP(F8,$B$4:$D$8,3)+(F8--1- ... A: the screenshot doesn't show cell references, so it's hard to know where F8 and B4:D8 is. However, ... | |
| Excel Spreadsheet | 6/9/2009 |
| Q: I am trying to create an excel spreadsheet where a drop down field has value or Yes, No. My aim is ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Predictive Sum | 6/9/2009 |
| Q: I'm hoping you might be able to help. I'm trying to create a "what if" scenario with excel to ... A: Sorry - this is more a math/simulation question than an Excel question. If you know the formulas or ... | |
| Excel VBA Form to Sheet | 6/8/2009 |
| Q: I am a novice at VB and I am trying to write a form to fill a sheet... I have most but I can not get ... A: This section: Cells(lRowNum, 4).Value = OptionButton1 Cells(lRowNum, 4).Value = OptionButton2 ... | |
| Dynamically adding rows to a table | 6/8/2009 |
| Q: I am putting together a report that shows daily status on tickets in a queue. I have a few pivot ... A: You don't use it as a formula, you use it as a defined name, then you can branch to it, etc. If you ... | |
| Data Sorting | 6/7/2009 |
| Q: Basically I need a data sheet that I can update daily and then sort it as well. This sheet will have ... A: I'm a bit confused. "sort the whole row"?? Don't you mean column? If the data being sorted has ... | |
| Dynamically adding rows to a table | 6/6/2009 |
| Q: I am putting together a report that shows daily status on tickets in a queue. I have a few pivot ... A: It's not clear to me what you're trying to accomplish, but in general you can have a dynamic range ... | |
| Macro | 6/5/2009 |
| Q: I am trying to record a macro where a formula fills in for all rows in a given column on the sheet, ... A: Are you placing this sub in its own Module? That is, are you using Insert/Module and putting it ... | |
| Macro | 6/5/2009 |
| Q: I am trying to record a macro where a formula fills in for all rows in a given column on the sheet, ... A: This macro does the same as yours but is flexible -- based on # of rows in Column B. Sub Macro4() ... | |
| Excel 2007 Change background colors | 6/4/2009 |
| Q: For work, I am trying to create a excel worksheet that contains employees and dates. Such as when ... A: Select all the cells whose colors change according to your criteria, then use Home/Conditional ... | |
| Simple chart in Excel - not so simple for me | 6/3/2009 |
| Q: I'm trying to produce a very simple bar chart with age of folks in left column (starting at 21; ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| To Copy Down Formula Until Last Row | 6/3/2009 |
| Q: I have formulas in row 2 in the cells: H2, V2, & w2. I need a code to copy down the formulas till ... A: Sub CopyFormula() Dim LastRow As Long With Worksheets("Data") LastRow = ... | |
| eliminate date format from excel forever | 6/2/2009 |
| Q: Is there a way to format the Excel 2003 on my computer so that it will NEVER EVER convert anything I ... A: Best way would be to select ALL cells, format as Text. But that would be only for the current ... | |
| Character limit on formula output | 6/2/2009 |
| Q: I am familiar with using Data Validation to limit the number of characters when inputting ... A: Not by Data Validation. You'd either need VBA code to check the length of the result of the formula ... | |
| auto run macro on multiple summary pages | 6/2/2009 |
| Q: I have a workbook that contains monthly logs and two summary sheets. I need my macro(which is an ... A: You didn't give me the name of the other summary sheet. Assuming it's "Summary": Private Sub ... | |
| Excel User Forms | 6/1/2009 |
| Q: Bob, Here is my dilemma, I am trying to create a Task Scheduler in excel to manage active and ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Assigned hours to pay codes | 5/30/2009 |
| Q: I have a simple spreadsheet, it lists 4-digit paycodes in Column B and the corresponding hours ... A: There are lots of solutions. In the example you supplied, you could use this formula in F11: ... | |
| Macro To Enable Macros | 5/29/2009 |
| Q: My workbook template has some macros when user opens the workbook it prompts to enable macros (in ... A: Can't be done. If macros are already enabled, there's no need to ask to enable macros, and if macros ... | |
| If statement | 5/29/2009 |
| Q: I am presently using =IF('Crew List'!G5="loaded",'Crew List'!B5,'Crew List'!AJ1). What I am trying ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Excel - Pasting Special | 5/28/2009 |
| Q: I have a spreadsheet that we update daily and each cell is then linked to a overall sheet. The ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Error in Excel "Unable to Read file" | 5/28/2009 |
| Q: I have a file which is created in excel 2000 having formulas & Pivot table and it is password ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Related to: Merge cells for same same data | 5/28/2009 |
| Q: The question in that questinon was: "I need to know how to Merge cells for same same data by ... A: Run this procedure: Sub Redistribute() Dim NextCol As Integer, NextRow As Integer, i As Integer ... | |
| Excel Data Validation | 5/28/2009 |
| Q: I have an excel file with Macros. In one of the sheets I have two dropdown lists by Data Validation. ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Related to: Merge cells for same same data | 5/28/2009 |
| Q: The question in that questinon was: "I need to know how to Merge cells for same same data by ... A: You're giving me conflicting information. You say you need to have Program FileDD ----> ... | |
| Data validation list | 5/27/2009 |
| Q: I have a table of "coded" values (A, B, C, etc.) and a translation of these coded values (Ape, Bear, ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| creating worksheet | 5/27/2009 |
| Q: Action on the Excel sheets: On sheet1 enter date, enter information, transfer from sheet1 to sheet2, ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| creating macros | 5/27/2009 |
| Q: I own 5 rental cottages and am trying to get away from keeping a calendar on a piece of white paper! ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| simple sort macro | 5/27/2009 |
| Q: I was wondering if you might be able to help me with a small macro problem Im having?? I know ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Data validation list | 5/27/2009 |
| Q: I have a table of "coded" values (A, B, C, etc.) and a translation of these coded values (Ape, Bear, ... A: Let's say your table is in K1:L4 -- Ape A Bear B Cougar C Dog D and your data validation is ... | |
| Excel Date Q | 5/26/2009 |
| Q: Mr. Umlas, This should be a simple question for you to answer. I unfortunately could not find this ... A: What happens to 5-9 days old? 21-30 days old? And what do you mean to pull the other information -- ... | |
| simple sort macro | 5/26/2009 |
| Q: I was wondering if you might be able to help me with a small macro problem Im having?? I know ... A: Excel can't see blanks as zeros. and "" is not a blank, it's a zero-length string. You need to ... | |
| Find Macro Function | 5/26/2009 |
| Q: I'm in need of some help from you sir regarding Creating a Macro. I use office 2007. In Sheet2, I ... A: Sub CompatCheck() dim Fnd as Range On Error Resume Next Err.Clear Set Fnd = ... | |
| To Create Hyperlink | 5/26/2009 |
| Q: I have an excel sheet which contains the scheules of training programs. Column B contains the ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| How to change color of "found" cell when using find/replace | 5/20/2009 |
| Q: Is it possible to change the border colour (from the standard black) or the cell colour of the cell ... A: Not exactly -- you can use the Find All (without replacing yet) and in the resulting bottom section ... | |
| Date Calculation Code does not work | 5/20/2009 |
| Q: I have an excel which I will be doing massive date imports. I would like the excel to calculate how ... A: I don't see an easy way to add it to the Worksheet_Change code because it would be recursive -- that ... | |
| Excel Macro | 5/20/2009 |
| Q: I have a macro that a guy helped me with, I need help adapting it to hide pivot charts containing ... A: Change xlSheetHidden to xlSheetVisible Mark your calendars for a 2-day advanced Excel conference ... | |
| Date Calculation Code does not work | 5/20/2009 |
| Q: I have an excel which I will be doing massive date imports. I would like the excel to calculate how ... A: You have code to only work on one cell at a time. Easiest would be that after you import your ... | |
| Pop up message if a cell is untouched | 5/19/2009 |
| Q: I have a questionnaire for sales reps to fill out and they are required to fill out every cell. If ... A: The issue is when should they see this message. Certainly not while they're filling it out, because ... | |
| vba | 5/19/2009 |
| Q: Hai bob, how r u, i vimal again with strange question. 1) i want to vlookup some data to my ... A: It'd help for me to see your current code. Basically, you need to set an object variable to the 2nd ... | |
| Copy & Paste Data in Transpose Mode (follow Up) | 5/19/2009 |
| Q: You had provided me a code to copy data from several workbooks and paste in a summary sheet in ... A: Try this (untested): Sub Combine3() Dim Area As Range, Files As Variant Files = ... | |
| SaveAs macro | 5/18/2009 |
| Q: I am brand new to macros, so I need some specifics. I have found some "almost" solutions to this ... A: Assuming you're using Excel 2003, right-click the Excel LOGO (Near the file menu), select View Code, ... | |
| INSERTING ROWS THAT COPY THE PREVIOUS FORMULA | 5/18/2009 |
| Q: Bob, I am not very good with coding and have received lots of help on the below code. The code is ... A: Offhand, I don't see how it wouldn't copy the formulas down. Please send your latest version of the ... | |
| Trigger Event When A Calculation Generates A Different Value | 5/18/2009 |
| Q: The following code works perfect but the "change" event is only triggered when working directly on ... A: You had originally said that if the cell CHANGES you want the row highlighted. That's VERY different ... | |
| Time stamp / Excel formula | 5/18/2009 |
| Q: I need a formula that will have the following date function: 1) Cell O7 was last edited on ... A: This works when column O CHANGES, so you need to change every cell in column O! You can do this, ... | |
| Copy data to new sheet if specific criteria met | 5/18/2009 |
| Q: I am looking for a bit of advice. I have been landed with a project for tracking workloads. There ... A: Do you want this copied the moment he enters his name? What if he spells his name a little ... | |
| Trigger Event When A Calculation Generates A Different Value | 5/17/2009 |
| Q: The following code works perfect but the "change" event is only triggered when working directly on ... A: This normally would be done using the Worksheet_Calculate event, but to do this when a value CHANGES ... | |
| Custom Formatting | 5/16/2009 |
| Q: Sir, I have a date that is formatted as follows: Dddd Mmmm d yyyy I am wanting to get the Dddd to be ... A: If it's really a date and FORMATTED as you said, then no, it can't be done, since what's REALLY in ... | |
| Time stamp / Excel formula | 5/16/2009 |
| Q: I need a formula that will have the following date function: 1) Cell O7 was last edited on ... A: Can't be done with a formula, because that'll keep changing. You need a VBA Event: Right-click the ... | |
| Trigger Event When A Calculation Generates A Different Value | 5/15/2009 |
| Q: The following code works perfect but the "change" event is only triggered when working directly on ... A: First, the calculation event fires whenever there's any change to the worksheet. If you're ... | |
| Conditional Formatting More Than One Cell | 5/15/2009 |
| Q: Is there a way to use the conditional formatting tool to check more than one cell at a time? I have ... A: Sure. Select ALL the cells in the NEW column, apply the conditional formats you want using the ... | |
| Gridlines question | 5/15/2009 |
| Q: Excel 2007 in question Cells with dotted borders are the problem, they are virtually invisible ... A: Couldn't see the video, but under Office button,Excel Options, Advanced, about 2/3 of the way down ... | |
| My excel Speed is very very slow and cannot be run. | 5/14/2009 |
| Q: I am working with the large excel workbook. Unfortunately, my file needs to use the "Vlookup" ... A: I'd have to see the file, but you can certainly strip it down to way less than 50000 lines & 10 ... | |
| copy and paste multiple worksheets into one | 5/14/2009 |
| Q: I have an excel workbook containing 4579 worksheets each with identical number of rows and columns ... A: Sub CopyPaste() ' ' CopyPaste Macro ' this macro copies tables from different worksheets into a ... | |
| report generating in excel | 5/14/2009 |
| Q: I have 2 list boxes in 1st sheet 1 is list of the names of executives and the other is LOB names and ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Excel | 5/14/2009 |
| Q: I am using Excel 2003. I have a mailing list that has names and addresses of recipients. Each ... A: I could help a lot better if I saw the actual file. Send a sample wb to me at bobumlas@yahoo.com, ... | |
| tallying ranked votes | 5/14/2009 |
| Q: I am using Excel 2004 for Mac OSX 10.4.11. My question is how to modify a spreadsheet that tallies ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Delete rows based on comboBox and offset values | 5/14/2009 |
| Q: I am new to this forum and I hope you can help me with the following excel userForm macro: I am ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Looking for value in a table and color coding it | 5/13/2009 |
| Q: I have two separate tables in Excel. I am trying to do sort of a vlookup here. But what I want is ... A: you want every item in table 2 to be color coded if it appears in table 1? If so, conditional ... | |
| Excel | 5/13/2009 |
| Q: Kindly help me to get a row No. i.e. cell ref. of a cell in lieu of cell value extracted by large()- ... A: Your question is a little unclear, but if you want to find the row of the largest value: ... | |
| VB code Different colored text counts | 5/13/2009 |
| Q: "Sir, I have dates horizontally, & in veticials I have lab set up, against each lab set up I have ... A: 1 - I don't understand what you are trying to do; 2 - use VBA code to do WHAT? Send a sample wb to ... | |
| Filtering The Data Using a Combo Box | 5/13/2009 |
| Q: I have an excel sheet which contains data with labels in top rows. I want a combo box in the top of ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Excel macro to copy sheet from workbook and paste into new workbook and then protect and close | 5/12/2009 |
| Q: I need a macro that will take all sheets in a workbook that have data in cells(1,2) -A2 and save ... A: Without testing it, it seems this will help. First, the line: For Each ws In ... | |
| INSERTING ROWS THAT COPY THE PREVIOUS FORMULA | 5/12/2009 |
| Q: Bob, I am not very good with coding and have received lots of help on the below code. The code is ... A: Sorry this took so long to respond to. It'd help me to see the issue. Send a sample wb to me at ... | |
| need a correct isblank statement | 5/12/2009 |
| Q: I am working on a spreadsheet to make it simpler for our manager to create our monthly schedules. ... A: The formula doesn't make sense. The syntax is =IF(condition,value if true, value if false) Your ... | |
| Hiding and displaying tabs | 5/12/2009 |
| Q: I have 6 tabs(excel sheets) in an excel file. My requirement is that initially all tabs except the ... A: The Single button should be assigned to run this macro: Sub Sing() Sheets(2).Visible = Not ... | |
| Entering time returns error | 5/12/2009 |
| Q: I have built a spreadsheet by modifying an existing one using Excel 2000 and Windows XP. There is an ... A: Since 15:00 is a very valid time, something else is going on. Send a sample wb to me at ... | |
| Random number generation | 5/12/2009 |
| Q: i wish to have a spreadsheet that will allow me to issue a random number to a date eg 11/5/2009 ... A: Sorry it took so long to get back to you. Sorry, I don't understand what yuo're asking. What do you ... | |
| use checkbox in excell sheet | 5/11/2009 |
| Q: Actually my question is simple. I have FOUR Columns. COLUMN A COLUMN B COLUMN C COLUMN D ... A: Each checkbox needs to be "linked" to a particular cell. When you create the checkbox, you ... | |
| list index question | 5/11/2009 |
| Q: On the spreadsheet I'm working on, I have certain cells that are locked for protection. I want only ... A: Here's an example that will unlock cells A3, F4, and G5 if cell D1 has the word "Test" in it, but ... | |
| Excel Table | 5/11/2009 |
| Q: I was wondering if it's possible to have a table i've created in excel be automatically inserted to ... A: I need more detail. So far, it sounds like all you ened to is insert a row above the table, so I'm ... | |
| Hyperlinks in Pivot Table | 5/11/2009 |
| Q: I have an Excel sheet in which i update data frequently. One of the columns have hyperlinks to some ... A: Sorry - pretty sure this can't be done directly, but you can use vba: right-click the sheet tab of ... | |
| how to use excel formula | 5/10/2009 |
| Q: Sir, Firstly, I thank you for your prompt response to my query on excel formulas, secondly the ... A: I don't follow how you get from A20 to B18:E18 or from A16 to B8:E8. I also don't understand what ... | |
| How to find all occurances of a string within a range & list adjacent cell values in another cell | 5/9/2009 |
| Q: I would very much appreciate your help on this matter. I am working on a software testing project & ... A: You need a function macro to do it. Press Alt/F11 to get to the VBE, Use Insert/Module, paste this ... | |
| AUTOPOPULATE DATA | 5/9/2009 |
| Q: I am creating an address book using excel. I want to be able to select names from a pick list (this ... A: Assuming all the data is already in the workbook somewhere, say Sheet1!A1:G300, and your pick list ... | |
| Excel Query | 5/9/2009 |
| Q: Whenever i key details on row1 and save, the row1 should save in color red & in the same way when i ... A: Not clear -- you want to change say row 1,3, and 7, for example, then when you save the workbook you ... | |
| Excel | 5/8/2009 |
| Q: I am trying make an "IF" statement that evaluates a field that calculate if a time is under a ... A: Either: =IF(C3<TIMEVALUE("0:30"),"U30",0) or =IF(C3*24<30/60,"U30",0) First is comparing time ... | |
| Excel "IF" statement issue | 5/8/2009 |
| Q: I am trying make an "IF" statement that evaluates a field that calculate if a time is under a ... A: Time in excel is measured as parts of a day. 1 hour is 1/24 of a day, so if you have 9:00 in one ... | |
| Worksheet_Change(ByVal Target As Range) | 5/7/2009 |
| Q: I am trying to solve in my head, and maybe I am too close to the problem, so I thought I would reach ... A: Instead of selecting B21 (or wherever), you can get the value by using: TryAgain: TheValue = ... | |
| Microsoft Excel 2007 | 5/7/2009 |
| Q: How do I display a data series on a graph in ascending or descending order without 'sorting' the ... A: Have a 2nd range refer to the original range in sequence. For example, if the original data is in ... | |
| Selecting range of data from excel using drop box | 5/7/2009 |
| Q: I am very new to excel programming. Now I got some problem with excel VBA macros. I had a data in ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Drop Down Boxes Disappearance | 5/6/2009 |
| Q: My drop down boxes in all of my spreadsheet have disappeared for some unknown reason. I tried to ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| RANK formula - Excel | 5/6/2009 |
| Q: I have an Excel 2003 spreadsheet in which I have entered raters' scores on applicants names. The ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| Sum a Row using a Reference in another Worksheet | 5/5/2009 |
| Q: Bob, I have created a very complicated workbook for my businesses End of Period P&L's. It gives us ... A: Send a sample wb to me at bobumlas@yahoo.com, use subject of "AllExpertsQ", repeat the description ... | |
| is there a delete command? | 5/5/2009 |
| Q: I am using the =LEFT(A2,FIND(" ",A2,1)) command to separate the last name from the first and middle ... A: Yes, but it's unclear what A2 looks like: Barry X Smith or Smith Barry X or other? Once you have ... | |
| Seeking to Automat a Process in Excel | 5/4/2009 |
| Q: Umlas, I have a template and I would like to be able to populate this template with information from ... A: I would need the actual workbook. Send a sample wb to me at bobumlas@yahoo.com, use subject of ... | |
| Conditional formatting question | 5/4/2009 |
| Q: I have an excel sheet that I use for logging mail into Royal Mail's (United Kingdom) business ... A: You only need to check if cells contain WEU, not if the VLOOKUP(...) returns WEU. You mention D1 -- ... | |
| Excel HELP | 5/4/2009 |
| Q: i am doing a timesheet on excel. in column "A" i have a list of custumers and in column "B" i have ... A: You need a master list somewhere with all the customer names. Say this list is in K1:K50. Next to ... | |
| checkbox control | 5/3/2009 |
| Q: i want to enable and disable a tick box in my excel sheet from VBA Microsoft excel objects sheet 1 ... A: If you don't want the code to run when you click one of the checkboxes, what event do you want to ... | |
| checkbox control | 5/1/2009 |
| Q: i want to enable and disable a tick box in my excel sheet from VBA Microsoft excel objects sheet 1 ... A: Not sure what you mean by " need to be able to enable disable from VBA Microsoft excel objects sheet ... | |
| Reference Trendline Equation in Cell | 4/30/2009 |
| Q: Bob, I am trying to reference a trendline equation in a cell, in which I want to dynamically update. ... A: If your data is in A1:A15, select 1 row x 7 columns and ctrl+shift+enter this: ... | |
| runtime error '1004' | 4/30/2009 |
| Q: I have some VBA code (created with the recorder) which does the following: 1. copies 5 ... A: I'm not going to look at the details of this longish macro, but if you've reached a limit for excel, ... | |
| I am having trouble out of #N/A. | 4/29/2009 |
| Q: Well, I thought I wouldn't have any trouble out of the #N/A problem. As you advised,I tried ... A: You select all the cells which MAY possibly contain the error. You use the conditional formatting ... | |
| summarizing multiple ranges | 4/29/2009 |
| Q: I was wondering if there is a command, or a versatile macro that will (for lack of the proper term) ... A: it's called a User-Defined function. You actually should add one line to it at the beginning to ... | |
| To bypass UpdateLink Dialogue | 4/29/2009 |
| Q: You sugessted the below code To Copy Cell Ranges From Multiple Worksheets and PasteSpecial in ... A: Untested, but should work: Sub Combine() Dim Area As Range, Files As Variant Files = ... | |
| summarizing multiple ranges | 4/28/2009 |
| Q: I was wondering if there is a command, or a versatile macro that will (for lack of the proper term) ... A: You can write this function macro: Option Base 1 Function NoBlanks(ParamArray rgs()) As Variant Dim ... | |
| summarizing multiple ranges | 4/28/2009 |
| Q: I was wondering if there is a command, or a versatile macro that will (for lack of the proper term) ... A: if the ranges are A3, Sheet2!F12, Sheet3!B4, for example, this will pick up the text: ... | |
| Excel spreadsheet formula | 4/28/2009 |
| Q: I apologise for the errors in my first try, it should make more sense now: "I am trying to evaluate ... A: Sorry, but this doesn't make sense to me. "N1 needs an input" -- you mean someone enters a value ... | |
| To Copy Cell Ranges From Multiple Worksheets and PasteSpecial in One Sheet | 4/28/2009 |
| Q: I have several identical workbooks in one folder in my computer, all the workbooks have only one ... A: Sub Combine() Dim Area As Range, Files As Variant Files = ... | |
| Dynamic range lookup select and copy/paste | 4/28/2009 |
| Q: In summary i want to be able to paste in ranges from RatesMexport sheet row 55-57 & 58, into sheet 1 ... A: I'm sorry, but without the trail of your question & my answer(s), this is pretty impossible to ... | |
| Deleting extra check boxes from a cell. | 4/27/2009 |
| Q: i'm trying to figure out the VBA coding that would be able to count the number of checkboxes in a ... A: Checkboxes (and all objects) have a topleftcell property which I guess you can use, since a checkbox ... | |
| To Copy Cell Ranges From Multiple Worksheets and PasteSpecial in One Sheet | 4/27/2009 |
| Q: I have several identical workbooks in one folder in my computer, all the workbooks have only one ... A: Sub Combine() Dim Area As Range, Files As Variant Files = ... | |
| Recursive formula | 4/26/2009 |
| Q: Trying to write a recursive formula with the following information. Total cost = x Interest Rate = ... A: I don't know about a formula to do this, but you can use goal seek. If you put some x value in D2, ... | |
| Changing Sheet Reference | 4/26/2009 |
| Q: I have created a workbook that is going to hopefully be used for a long period of time, with one ... A: Then I don't see why currentYearSheet would need to change. Is it based on the year? That is, if ... | |
| To Copy Cell Ranges From Multiple Worksheets and PasteSpecial in One Sheet | 4/24/2009 |
| Q: I have several identical workbooks in one folder in my computer, all the workbooks have only one ... A: Sub Combine() folder = Application.GetOpenFilename Application.DisplayAlerts = False ... | |
| counting specific dates | 4/23/2009 |
| Q: Sorry, Bob - I have more info: I provided an incorrect formula in my previous e-mail. The formula I ... A: =IF(COUNTIF(Sheet2!H6:H332,DATEVALUE("1/3/09"))>0,"x","o") It's because "=1/3/2009" is not found. ... | |
| Excel | 4/22/2009 |
| Q: If I want to input a vlaue into a cell with a value already in it, how do I get Excel to add the ... A: Right-click the sheet tab, select View code, put this in: Dim Prev Private Sub ... | |
| Regression Macro | 4/22/2009 |
| Q: Just want to thank you advance for your help. I have written a macro and it works well. When I run ... A: If I understand you right you want to still select ENTER CURVE DATA sheet to do the work on that ... | |
| Print Macro | 4/21/2009 |
| Q: I have a worksheet with about 5 ranges that I need to print on separate pages for each. The first ... A: This is not a valid printarea: .PrintArea = ... | |
| Time stamps in excel | 4/21/2009 |
| Q: I need to do individual time stamps using stop times and start times for an OEE spread sheet. This ... A: Lots of ways are possible, depending on what you want. Here's one way (may not be the easiest, but ... | |
| look-up all values in a row | 4/20/2009 |
| Q: I have a vacation schedule. Column A is employee id#s. Columns after that are daily dates for the ... A: Sorry - my original answer had an error anyway. Let's say the employee # you need the dates from is ... | |
| Extracting info from a string using excel | 4/20/2009 |
| Q: I have several excel spreadsheets into which i have dumped data. I need to count data in column H ... A: By "count" do you really mean count or sum? In the example you gave, for the desired extensions, the ... | |
| Day by day update in chart | 4/20/2009 |
| Q: Mr Umblas I'm from Sweden and I have a question for you. I want to extract numbers from cells ... A: It's possible, but you'll need to use defined names. In the workbook with the dates & values -- say ... | |
| excel cell 'rule' | 4/20/2009 |
| Q: I wondered if you can help. I would like to insert a rule onto a number a cell on excel where, if ... A: Assuming you mean column A when you refer to column 1, then do the following: Right-click the sheet ... | |
| IF statement | 4/19/2009 |
| Q: Can you do more than 8 options with the IF statements, I am trying to do the months of the year, IF ... A: No need. If the 1 or 2... is in cell A1, use this formula: =DATEVALUE(A1&"/1/2000") OR ... | |
| xl 2007 - conditional formatting - Icon set | 4/19/2009 |
| Q: I have a question relating to excel 2007--->Conditional formatting--->Icon sets. I have data in two ... A: Icon sets work only for the cell(s) they're in, so you need a helper column. Say your values are in ... | |
| look-up all values in a row | 4/19/2009 |
| Q: I have a vacation schedule. Column A is employee id#s. Columns after that are daily dates for the ... A: Let's say the employee # you need the dates from is in cell C11 (arbitrary). You need 2 columns to ... | |
| Excel: | 4/17/2009 |
| Q: I have a spreadsheet that several of us work off at once-what I would like to do is when I select a ... A: If the dropdown result is in cell E3, for example: Select the whole row you want to change color, ... | |
| Repeting code | 4/16/2009 |
| Q: Hey im an amature user of excel i have the following code that works but i would like to compress it ... A: Sheets("Temp").Select Sheets("Sheet1").Range("A3:AU3").Copy Range("A1") ... | |
| Excel Formula | 4/16/2009 |
| Q: Bob, I need to setup a defined name using a 2 column lookup formula to then utilise as a list in ... A: Entire column is way too big for this. It'd be better to create a dynamic name, like ColA whose ... | |
| To Enter Date Through Calendar Control | 4/16/2009 |
| Q: I have a data entry sheet in Excel. In some cells date needs to be entered and I want that users ... A: Attach the calendar control to C25. Right-click the tab, select View Code, insert this: Private Sub ... | |
| Scatter graph | 4/15/2009 |
| Q: . I'm a Help Desk employee at Northwest Technical College. In a computer lab (computers protected ... A: for a nxn array, excel will plot the series in rows. It will plot it in columns if there are more ... | |
| Workbook protection using VBA Code | 4/15/2009 |
| Q: I just want to protect the whole file(workbook) automatically when i close. Also, I should be able ... A: The code I gave would go into the Thisworkbook, which is what's shown if you get to it via ... | |
| excel PROBLEM | 4/14/2009 |
| Q: EXAMPLE 25/4/2009 = WEEK DAY 26/4/2009 = OFF DAY 27/4/2009 = WEEK DAY TO GET WEEKLY OFF ON ... A: 1 - All caps looks like you're shouting, please avoid. 2 - I have no idea how "1/2 day duty" is ... | |
| Merge cells for same same data | 4/14/2009 |
| Q: I need to know how to Merge cells for same same data by running a VB macro (or some other automatic ... A: Assumes the first "PGM01" is in A2: Sub Merger() Dim n As Integer Application.DisplayAlerts = ... | |
| Text boxes | 4/14/2009 |
| Q: I'm pretty convinced there is no way to do this, but I thought it wouldn't hurt to post the question ... A: Can be done with VBA: Write this function (alt/F11, Insert/Module): Function TextBox() ... | |
| Combining statements in VBA | 4/13/2009 |
| Q: I'm trying to combine 2 statements in VBA that would then input into a specified cell. For some ... A: There's quite a bit of code missing for me to be able to help. For example, where is fullpath ... | |
| Convert Text to Columns Wizard | 4/13/2009 |
| Q: Is there any way to change the default delimiter in the Convert Text to Columns Wizard? My office ... A: You need to do it manually first time only for each time you start Excel. There's no way (I know of) ... | |
| Time taken from problem logged to resolution excluding non-office hours | 4/13/2009 |
| Q: I'm trying to calculate the time taken from problem logged to problem solved excluding non-office ... A: Given the complexity of the original solution and it wasn't set up to be flexible, I'm afraid I ... | |
| Program Guide | 4/2/2009 |
| Q: I have a series of columns, the first is A3 to A176 it is a range containing slots of an hour apart ... A: Something I'm not understanding -- it seems that all you need in C is a reference to E, like in C3 ... | |
| excel help | 4/2/2009 |
| Q: Respected sir, i hav an excel sheet which has data stored in it as follows: colA ... A: If you're going to need i & j to be known, then =Closetime(A5,B5:B7) is the same as knowing i and j ... | |
| Vlookup multiple values | 4/1/2009 |
| Q: I have a list of names in column A and a list of roman numerals in column B. The names in column A ... A: Are you looking to find the 2nd, 3rd, etc roman numeral for the same name or are you wanting to look ... | |
| XY scatter chart: adding more than one line to a graph | 4/1/2009 |
| Q: I am using excel 2007. I need to show the statistical difference between two sets of data points ... A: Once you have your xy scatter from A1:B3, click on the chart, use the Design tab from the Chart ... | |
| Max Value in a changing range | 4/1/2009 |
| Q: i have a range which states whether a certain condition has been met or not, a column that has Y or ... A: Not sure how your data is organized so that you can get the NEXT queue of y's so where would this ... | |
| Excel, Flashing Colors on a conditional statement | 3/31/2009 |
| Q: How would I make a specific cell in a worksheet continuously flash a set of specific colors when ... A: Let's suppose the cell in question is D4. Right-click the sheet tab, select View code, put this in: ... | |
| Formula or Macro in Excel 2007? | 3/31/2009 |
| Q: I'm ultimately trying to delete rows in Excel when the Count of a column is > 2. Is there a formula ... A: in C2: =IF(COUNTIF($A$2:A2,A2)>3,1,0) and fill down. in C6 in this example (the first delete) you'll ... | |
| Search an array | 3/31/2009 |
| Q: Bob, I have in one worksheet a list of values. In the same workbook, on a separate worksheet, I ... A: Let's say the value you're looking for is "x" and that you're looking for it somewhere in A1:Z500. ... | |
| Entering data in excel that will result in specified data appearing in another cell | 3/30/2009 |
| Q: I am working with salesmen and their regions. I want to be able to type the salesman's name into A1 ... A: Sure. If the service center is in column C: =VLOOKUP(A1,Sheet2!A1:C50,3,FALSE) basically: ... | |
| Application Filesearch | 3/30/2009 |
| Q: I have inherited an excel sheet from work that uses the filesearch macro. I am a really novice user ... A: It's hard to make it work given the current code doesn't seem to make sense -- look at this: If ... | |
| Dynamic Sheet Access | 3/29/2009 |
| Q: Bob, I have a workbook with a sheet named for every day of the month (i.e 1,2,3,4). I want to ... A: You can put the sheet names in separate cells, like E1 & E2 or get the day of the month in E1&E2 by ... | |
| Excel Visual Basic | 3/27/2009 |
| Q: I understand that you can get message to pop up using Visual basic but I do not no much about it. ... A: Sorry -- try this: Private Sub Workbook_BeforePrint(Cancel As Boolean) If Range("H48").Value <> ... | |
| Excel Visual Basic | 3/27/2009 |
| Q: I understand that you can get message to pop up using Visual basic but I do not no much about it. ... A: Assuming excel 2003: Right-click the Excel LOGO (near the file menu), select View Code, enter this: ... | |
| To print all the sheets contained in the workbook | 3/27/2009 |
| Q: I have a workbook which contains around 35 to 40 worksheets. I need a code to print (print area ... A: You can make it an addin simply by selecting the ThisWorkbook in the project window and change the ... | |
| ActiveX control causes "...reset project..." message on file open. | 3/27/2009 |
| Q: I've added two combobox controls directly to an XL2007 macro-enabled worksheet because I needed to ... A: Without seeing/testing the actual workbook, I would guess this might work: Put in a Workbook_Open ... | |
| Comparing | 3/26/2009 |
| Q: I have a workbook with three spreadsheets. I want to compare the entries in three columns with the ... A: You'd have a much easier time simply using Conditional Formatting. Please send a sampel wb to me at ... | |
| Trend line Equation | 3/26/2009 |
| Q: Bob, Why does the trend line equation on a linear graph change when you add dates to the chart? ... A: You can get the true equation by using formulas - I've noticed that the dates seem to throw off the ... | |
| VLOOKUP Problem | 3/26/2009 |
| Q: I have an excel 2003 spreadsheet that contains a Volunteer Worksheet for the purpose of driving ... A: You can't use VLOOKUP for multiple lookup cells -- if what you want to look up is a combination of ... | |
| To make copies of sheet | 3/25/2009 |
| Q: I have an excel workbook.In sheet 1 has list of departments in column A which has header. I want to ... A: Sub MakeSheets() Application.ScreenUpdating = False Set main = Sheets("Main") Set newwb ... | |
| picture or clip art to be an answer in a formula | 3/25/2009 |
| Q: I need to make a spreadsheet where if I put a value in cell A1 that a picture of a sheet metal ... A: Send me a sample WB and I'll show you how to do it -- you're going to need to have ALL your pictures ... | |
| Finding a 10 digit number within a text field | 3/24/2009 |
| Q: I have a column with 10 digit ranges of numbers (ie 9855123456-9855123456) within a text field. ... A: I'm sorry but I'm STIL confused. If there are 2 ranges of numbers like in your example, do you want ... | |
| Finding a 10 digit number within a text field | 3/24/2009 |
| Q: I have a column with 10 digit ranges of numbers (ie 9855123456-9855123456) within a text field. ... A: Sorry - I'm still a bit confused. In your latest list, yuo seem to imply you're ending at 9855123451 ... | |
| Counting green digits. | 3/23/2009 |
| Q: I have a cell that contains a list of numbers separated by commas in the following format: 0612, ... A: This works on one cell at a time: =CountByColor(A1) Function countByColor(rg As Range) ... | |
| counting cells and conditional formatting | 3/21/2009 |
| Q: I have a relatively simple need which could come in quite handy for many Excel needs. However, I'm ... A: Rather than a function which would do it (there is nothing builtin -- you'd need VBA code), you can ... | |
| To make copies of sheet | 3/21/2009 |
| Q: I have an excel workbook.In sheet 1 has list of departments in column A which has header. I want to ... A: Sub MakeSheets() Set main = Sheets("Main") On Error Resume Next For i = ... | |
| Visual Basic Loop "Counters" | 3/19/2009 |
| Q: "Counters" usually start at zero, as an example. How do I assign a formula result to the value of ... A: Counter = Application.Countif(Range("yourrange","Network") or if the result already exists in the ... | |
| Macro question | 3/19/2009 |
| Q: I am having trouble in writing a macro for excel.I have an excel with many sheets. I need to copy ... A: Sub Combine() On Error Resume Next Set NewSheet = Worksheets.Add n = ... | |
| Time taken from problem logged to resolution excluding non-office hours | 3/19/2009 |
| Q: I'm trying to calculate the time taken from problem logged to problem solved excluding non-office ... A: As you'll see from my answer, this is actually quite a tough problem. I believe this formula works ... | |
| excel vlookup | 3/18/2009 |
| Q: If I need it to look up for example 7 -- Instead it's pulling in $75,362 -- how do I get it to ... A: I'm not sure I follow (I don't know what DealAnnualVolume is -- if it's 7, it's doing the right ... | |
| Macro question | 3/18/2009 |
| Q: I am having trouble in writing a macro for excel.I have an excel with many sheets. I need to copy ... A: Sub Combine() On Error Resume Next Set NewSheet = Worksheets.Add n = ... | |
| Macro question | 3/18/2009 |
| Q: I am having trouble in writing a macro for excel.I have an excel with many sheets. I need to copy ... A: I'm assuming the sheets are named A,B, C - otherwise change this macro accordingly: Sub Combine() ... | |
| More Conditional Formating | 3/18/2009 |
| Q: I am a user of excel 2003 which has options of only three conditional formatings, is there any macro ... A: There are actually 4 - the format when none of the conditional formats are met + the 3 from the CF ... | |
| Converting File path to Hyperlink | 3/18/2009 |
| Q: I have an excel sheet. In column A there are some file addresses like C:\Documents and ... A: Make sure the MakeHyperlinks sub is available to the workbook because this routine calls it: Sub ... | |
| Excel | 3/17/2009 |
| Q: I am in the military and I have several companies I must track. I have made a spreadsheet which will ... A: SOUNDS perfect for a pivot table (but I don't know the layout of the data but if it's sortable, it's ... | |
| compare time values | 3/17/2009 |
| Q: Respected sir, I want to compare time values in excel sheet, using vba macros. The criteria for me ... A: I get 0 for =Closetime(A1,B1:B2) because there is no time BEFORE the value in A1 in B1:B2. I don't ... | |
| Macro to indent wrapped text | 3/16/2009 |
| Q: I need to create a macro that will indent the second line of wrapped text in a cell by 2 spaces. For ... A: That macro wouldn't do what you want. That macro runs automatically whenever anything on the sheet ... | |
| Excel - VB/ Macros Help for Template | 3/16/2009 |
| Q: Windows XP Pro, Office 2003 Pro Excel, I need and invoice system that will be used as a template, ... A: OK, now I'm thoroughly confused. I think it's time to send me the workbook/template. I don't know ... | |
| Help with Excel | 3/16/2009 |
| Q: I have a golf tracking sheet. I want to add/total cells in a range C3 thru C12, but don’t know how ... A: I can't tell from the image what's in C3:C12 -- give an example of what's in those cells and how ... | |
| Excel - VB/ Macros Help for Template | 3/11/2009 |
| Q: Windows XP Pro, Office 2003 Pro Excel, I need and invoice system that will be used as a template, ... A: Since the template is being SAVED AS..., the value in R8 isn't really being updated, so you need to ... | |
| Checking info against other worksheets in a workbook Macros | 3/10/2009 |
| Q: My workbook has 3 main worksheets (Loans, Paid, & Working). I paste updated information to my ... A: You can add this above your End Sub: ProofLoans then have a separate procedure so you CAN run it ... | |
| Checking info against other worksheets in a workbook Macros | 3/10/2009 |
| Q: My workbook has 3 main worksheets (Loans, Paid, & Working). I paste updated information to my ... A: What column is the account # on in the Loans & Working tabs? If not found you want just the account# ... | |
| remove macros | 3/10/2009 |
| Q: I'm trying to delete an old macro out of a workbook. I did the Tools/macros, then Delete. I saved, ... A: The existence of VBA code ANYWHERE (modules, sheets, Thisworkbook) will trigger the dialog. If you ... | |
| time compare vba | 3/10/2009 |
| Q: respected sir, i want to comare time values in different columns,for close match and as u ... A: I don't understand. 10:04:10 is 22 minutes from 10:26:29 and 10:20:44 is 2 minutes. I don't get how ... | |
| Formula | 3/10/2009 |
| Q: If I create a spreadsheet and have an expiry column is it possible to have the dates automatically ... A: Select all the dates, use Format/Conditional Formatting, change "Cell Value Is" to "Formula Is", ... | |
| Macros results different when using step through | 3/9/2009 |
| Q: I have the following macro that copies cells from one worksheet to another based on criteria. There ... A: Send me the wb - bobumlas@yahoo.com use subject of "AllExpertsQ". Hard to tell what might be causing ... | |
| getting excel sheet names | 3/9/2009 |
| Q: I have two work book and each of it contains 10+ sheets. Now tell me how to get the sheet names, so ... A: With a formula in each sheet: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) With VBA ... | |
| Time additions & Time Formatting | 3/9/2009 |
| Q: My staff are allowed 60 minutes of break time, in as many break segments as the workload permits, ... A: Send me a sample wb to bobumlas@yahoo.com, use subject of "AllExpertsQ" A basic issue you're seeing ... | |
| split text string into separate columns | 3/8/2009 |
| Q: Umlas, Would you be kind enough to construct formula(s)to separate address text strings into ... A: You want the initial number in one column and the rest in another column? Or more involved than that ... | |
| columns on scatter chart? | 3/7/2009 |
| Q: I have to make some charts as part of an assignment which have to have vertical columns, but have a ... A: You CAN do it -- make sure the top of your x-values is blank and the top of your Y-values are ... | |
| vba code help | 3/7/2009 |
| Q: i hav some different time values in excel sheet cols. e.g. Col A 1.[01-27-2009 03:08:22] ... A: in VBA code this: Function Closetime(Begin As Range, possibles As Range) As Double diff = ... | |
| Deleting rows considering two different columns | 3/6/2009 |
| Q: My problem is trying to find a vba solution for deleting duplicates based on 2 criteria. Column "C" ... A: Sorry, we're still not on the same page. If this is the case: C K 12345 ... | |
| Saving a file according to the Purchase Order No. | 3/6/2009 |
| Q: Sorry I was not clear on one of the questions I asked. Again we have purchase order form located on ... A: In the code behind "ThisWorkbook": * (assumes F3 contains the PO# to save with): Private Sub ... | |
| Macro Question on unique number | 3/6/2009 |
| Q: I used the macro below for a PO form I created for our department to use on a shared drive. ... A: You should keep a separate file in G named LastNumber.xls which simply contains the last used # in ... | |
| User Form Collating Data | 3/6/2009 |
| Q: I'm trying to create a userform that can be used to generate automatic notes to be collated at the ... A: You can definitely compile all this into a textbox on the user form. You may need to sue ... | |
| Macro Question on unique number | 3/6/2009 |
| Q: I used the macro below for a PO form I created for our department to use on a shared drive. ... A: You can delete a macro & start from scratch, but this macro is saving the last used number in the ... | |
| Currency symbols | 3/5/2009 |
| Q: We have many Excel spreadsheets in our company that we use on projects - budget sheets, product cost ... A: The problem is, there's no real "$" in the cells, it's only in the formatting. I'm afraid you can't ... | |
| Creating a quote from an excel spreadsheet | 3/5/2009 |
| Q: Problem: I currently have a stock list of +/- 700 line items. I wonder if my clients can go through ... A: If I understand the question right, you can use data/Filter/Autofilter, and on the dropdown for the ... | |
| Cell Fill Color | 3/5/2009 |
| Q: I have worksheet with columns of suppose 1st Jan to 31 july and the first 4 columns with activity, ... A: You can use conditional formatting to do what you want but I can't supply details because I don't ... | |
| vba code help | 3/4/2009 |
| Q: i hav some different time values in excel sheet cols. e.g. Col A 1.[01-27-2009 03:08:22] ... A: If A1 contains [01-27-2009 03:08:22] then if the time to compare is in K1, for example, this formula ... | |
| Colored cells | 3/3/2009 |
| Q: I am trying to set cells to show up with specific colored fonts and backgrounds. For example, I have ... A: Select all the cells which you want to possibly change color. Use Format/Conditional Formatting. ... | |
| Lookup data based on two variables | 3/3/2009 |
| Q: I need help coming up with an Excel formula please. I need to look up data based on two variables. ... A: If your sample table is in A2:C6, and you have the values to look up in E1 and E2, for example, (E1 ... | |
| sliding scale | 3/3/2009 |
| Q: I have a spreadsheet that calculates the cost of digital printing...The larger the quantity, the ... A: Sorry, I should have had both references be the same cell. If A1 contains .9, then 150-50*A1 gives ... | |
| How to disable editing while multiple sheets are selected? | 3/3/2009 |
| Q: Is there any way to do this? I'm tired of accidentally deleting, writing, changing row heights in ... A: For each workbook you want to ensure this doesn't happen, right-click the excel logo (near the file ... | |
| missing dates in excel | 3/3/2009 |
| Q: "I will get data from a machine at every one hour. I have some historical data @ hourly basis i.e., ... A: If the date is in one column & the hour in another (say col A & B), you can select all the data in ... | |
| Reconciling variable data amounts to a variable given total | 3/2/2009 |
| Q: I have a similar query to one posted on this forum last year with the subject title above. I have a ... A: Suppose your #s are in A1:A27. In C1 put =A1*B1 (B1 is empty). fill down to C27. In C28 put ... | |
| Vlookup | 3/1/2009 |
| Q: Bob, I am having trouble understanding the vlookup function in excel. I need to be able to compare ... A: VLOOKUP looks up ONE value. Lokos like you're trying to look up 2 values. You need something like ... | |
| comparing Excel spreadsheets | 2/27/2009 |
| Q: If I want to compare an Excel spreadsheet with someone else's spreadsheet to see if both sheets are ... A: Suppose other spreadsheet is named "other.xls" in yours, select A1, define a name, "Other", to be: ... | |
| Calendar Control | 2/26/2009 |
| Q: In reading your response to the "Calendar Control to navigate cells" I am trying to do the same ... A: Sorry - I have no recollection of my having responded to a "Calendar control to navigate cells" and ... | |
| Offset & Indirect | 2/26/2009 |
| Q: Greetings, I have data on 'Sheet 1' beginning in cell A1 and going down the column (A2, A3, ...). ... A: No need for Offset or Indirect -- follow these steps EXACTLY: in Sheet2 cell B13 enter this: ... | |
| blank cells plotting as zeros | 2/25/2009 |
| Q: I'm having a problem with plotting a dataset that was derived from a isna() function that may have a ... A: You want the #N/As. instead of the =IF(ISNA(B4),"",B4) simply use B4. The N/As will plot as you ... | |
| VBA excel help | 2/25/2009 |
| Q: i m stuck up with a problem in my code..the code matches two stings using the matches function..in ... A: 1 - I don't know what "matches" is -- it's not native VBA code. 2 - I don't see matching in your ... | |
| Excel Drop Down List for macros | 2/24/2009 |
| Q: I have multiple macros in my spreadsheet that I would like to list in a drop down list (in a data ... A: If the data validation is in cell D12, for example, and your macros are named January, February, ... | |
| Sum formula | 2/24/2009 |
| Q: How are you? I am looking a formula for sum the hrs if the four condition will match Let suppose. ... A: If your data starts in D1 ("Date"), then if your "test" data is in L1:N1 (date, svc, s/no), this ... | |
| Insert a sequential number | 2/23/2009 |
| Q: About the insertion of the sequential number: If it is possible to generate a number like FIN001 it ... A: Sorry, I still don't quite get it -- what EXACTLY does the user do to "submit the form to an email ... | |
| Find and highlight across spreadsheets | 2/22/2009 |
| Q: I run a Nascar pool that I maintain in Excel 2007. When I enter a participants driver pick and ... A: Sorry, not quite clear enough - I think you're using spreadsheet when you mean workbook. Excel 1 is ... | |
| Find and highlight across sheets | 2/22/2009 |
| Q: I run a Nascar pool that I maintain in Excel 2007. When I enter a participants driver pick and ... A: Since I don't know "when" this should happen, since you may want it to happen when entering the name ... | |
| named range in trig function | 2/21/2009 |
| Q: Sir, I have a formula, =IF(C6>$I$4, " ", SIN(D6*PI()/180)*($I$2/2)), I would like to make the sin a ... A: Why not just give a name to this function: =IF(A1=1,1,-1) and use that? If the above were named x: ... | |
| Buttons from the controls toolbar not visible | 2/21/2009 |
| Q: Since upgrading to 2003 buttons from the controls toolbar are no longer visible. I opened the sheet ... A: Sorry I never heard of this situation before and actually don't have any ideas since you tried all ... | |
| Calibration register | 2/20/2009 |
| Q: We currently use a equipment calibration register to display calibration status of our equipment, it ... A: Only because NOW()>EDATE(N6,-1) is false. Maybe you really want that part of the formula to be ... | |
| Calibration register | 2/20/2009 |
| Q: We currently use a equipment calibration register to display calibration status of our equipment, it ... A: It would help to know what's in N6 -- your formula can ONLY say "Fail" if N6 equals NOW(). Assuming ... | |
| Insert a sequential number | 2/20/2009 |
| Q: About the insertion of the sequential number: If it is possible to generate a number like FIN001 it ... A: I'm sorry, this remains unclear to me. If you're limiting the user to ONLY choose FIN, then the cell ... | |
| toggle button? | 2/20/2009 |
| Q: I'm creating a valuation model for a company that has many announced projects. But no one really ... A: I don't know what you mean re the projects or on or out -- what does that actually mean? Protect the ... | |
| Insert a sequential number | 2/19/2009 |
| Q: I appreciate your help a lot! The last question that I asked and where you give me an answer worked ... A: So I still have a question (or 2) If the user selects FIN from this listbox, and 003 is put in B5 ... | |
| Merging Cells considering duplicates | 2/19/2009 |
| Q: I currently have an excel file with 3 columns and on basis of them I want to create a fourth column. ... A: There's no function builtin to do this. It CAN be done by writing a macro, but it would not be a ... | |
| Time Calculation | 2/18/2009 |
| Q: i am trying to set up a very simple timesheet type calculation, but i am only having problem with ... A: Hours are stored as parts of a day. 1 hour = 1/24 of a day, formatted as a time. If you enter =1/24 ... | |
| Populating other cells based on a choice from a drop-down in Excel | 2/18/2009 |
| Q: I'm creating a drop downs in excel and I have hit a road block. I would like to have numerous ... A: You need VBA to do this, and since you do, it's much easier to do it all by using a UserForm -- if ... | |
| Cell Colour | 2/17/2009 |
| Q: Following on from the previous question is it possible now to have it do the same thing but for each ... A: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.column = 15 Then Select ... | |
| Excel Cell Colours | 2/17/2009 |
| Q: Is their away of making the cell change colour everytime you click on it, what I am basically trying ... A: Try this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$E$3" ... | |
| Insert a sequential number | 2/17/2009 |
| Q: I appreciate your help a lot! The last question that I asked and where you give me an answer worked ... A: At what point should this number be generated? Can't be done when it's opened because it's not yet ... | |
| Too Many Cell Formats ? | 2/17/2009 |
| Q: I have some large Excel spread sheets and for some reason I now get a message "Too many different ... A: Without upgrading to Excel 2007 (you said Word 2000???), you're going to have to look at ... | |
| Excel Cell Colours | 2/17/2009 |
| Q: Is their away of making the cell change colour everytime you click on it, what I am basically trying ... A: If the cell is already selected, clicking on it again won't do anything -- you need to click away ... | |
| Mandatory Question | 2/16/2009 |
| Q: I am a student and trying to get an linear algebra eqution to work. I want the user to be prompted ... A: Right-click the excel LOGO (Near the file menu), select View Code, enter this: Private Sub ... | |
| Need a macro that will check text in one column | 2/16/2009 |
| Q: I need a macro that will check the text in one column and if it finds it(e.g Case, Package or Each) ... A: You know how to do the | |