You are here:
| Subject | Date Asked |
| 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 Vlookup in VBA? Do you want to be prompted for what to look for (Case or ... | |
| Excel Cell Colours | 2/16/2009 |
| Q: Is their away of making the cell change colour everytime you click on it, what I am basically trying ... A: I'm using E3 here as an example -- change it to the cell you want. Right-click the sheet tab, select ... | |
| excel formula | 2/16/2009 |
| Q: I need formula to find the total full legth recquired for given numbers of cut length, for example ... A: Sorry - I do NOT understand the question. What's "mts"? What's a "full bar"? The phrase "...as the ... | |
| import data, match and change exisitng data | 2/14/2009 |
| Q: I am importing excel data with 4 fields (item #, vendor, description, price). I have a data base ... A: I need more details. You need VBA code to do it. You want the code to import the data or is this ... | |
| Excel Query | 2/14/2009 |
| Q: Public LastCol As Integer Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel ... A: From what I can see, it's because of these statements: Set TWS = ThisWorkbook.Worksheets(1) ... | |
| Looking for multiple keywords in a string | 2/13/2009 |
| Q: I have spreadsheet with following : A1 B1 jackal word jump ... A: You need a User-defined function. Press Alt/F11, use Insert Module, then enter this: Function ... | |
| vba macro help - creating new worksheets | 2/13/2009 |
| Q: **I noticed it doesn't say VBA in your "No" list. Please disregard if you do not know VBA. Hello, ... A: Sub CreateSheets() On Error Resume Next Application.DisplayAlerts = False ... | |
| Complicated Excel Formula | 2/13/2009 |
| Q: I asked a question a few days ago which was not asked properly, this time i have atttached a jpeg it ... A: 1 - the image is too small to really be helpful 2 - I don't understand how C24 is supposed to ... | |
| Auto Consecutive Numbering | 2/12/2009 |
| Q: My question is almost the same as the one you answered previously in October of 2007. I am also ... A: not tested: Private Sub Workbook_BeforePrint(Cancel As Boolean) application.enableevents=False ... | |
| Reset constant fields to zero | 2/12/2009 |
| Q: I must thank you for your help on previous questions. I have another one: When a user is finished ... A: Not sure why it didn't work, but this code should do it for ONE worksheet -- attach to a button on ... | |
| Macro in Excel | 2/12/2009 |
| Q: i am new to macro but would like to write an macro which expected to do follows... 1. I am having ... A: Sub Rearrange() N = 0 With Sheets("Sheet2") For i = 1 To ... | |
| Macro | 2/11/2009 |
| Q: I am working on a project where we have individual excel files for each department, and each file ... A: If you have individual files or each department, are you asking to update ONE master list and have ... | |
| Show only Rows that contain any of certain values listed.... | 2/10/2009 |
| Q: I have a big workbook with several sheets. Basically, column A contains a list of numerical ids. I ... A: Assuming your data starts in A2 (A1 being a header), enter this formula in G2 (assuming col G is ... | |
| Control Chart | 2/10/2009 |
| Q: Jon Peltier demonstrated in his PTS Blog how to create control charts dynamically. He defined ... A: 'Control Chart'!$1:$2 refers to rows 1 and 2 of that sheet, which is the array {1;2]. By subtracting ... | |
| Excel Timesheet with Incremental hour | 2/10/2009 |
| Q: How do you set up an excel sheet to automatically formulate the cells to understand incremental ... A: You can enter 8:00 AM in A1 and 8:01 AM in A2, select both and drag the fill handle and you'll get ... | |
| Excel Timesheet with Incremental hour | 2/10/2009 |
| Q: How do you set up an excel sheet to automatically formulate the cells to understand incremental ... A: 1 - I don't know what you mean by "the correct incremental time for the week". 2 - You can enter ... | |
| Calculating days out from date, excluding weekends | 2/9/2009 |
| Q: I have a spreadsheet showing ship dates and delivery dates. Does Excel have the ability to ... A: Can you clarify something: 2/27 + 22 days -- is that 22 WORKING days excluding weekends? If so, then ... | |
| Microsoft Excel | 2/7/2009 |
| Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: As I said, there is no way whatsoever to increase the # of rows without upgrading to xl 2007 (no ... | |
| Excel 2007 compare worksheets | 2/6/2009 |
| Q: I have multiple worksheets with employee numbers listed in columna A. I receive new worksheets ... A: Since you have XL2007, you can copy them all in m then use Data tab, Remove Duplicates! Or, you can ... | |
| Cut & Paste Values | 2/6/2009 |
| Q: I am working on a scheduling spreadsheet that has job numbers in column A, and columns to the right ... A: I'm missing something. That sounds like Copy/Paste, not Cut/Paste. Are you saying you want to ... | |
| date & time stamping | 2/6/2009 |
| Q: in excel in cell b2 i want the date and time stamping if the value has been entered in cell ... A: I don't know what you mean by "giving the code of that person in column "B". But here's how yuo can ... | |
| Commission Calculations | 2/6/2009 |
| Q: Ulmas, Thanks for taking the time for answering my question. I'm sure you will be able to answer my ... A: =IF(SUM(L:L)<=10000,.15,IF(SUM(L:L)<=20000,.2,IF(SUM(L:L)<=25000,.3,IF(SUM(L:L)<29000,.25,.35)))) ... | |
| Excel 2007 | 2/5/2009 |
| Q: I'm not sure how to ask this question. I am having a hard time combining multiple columns with text ... A: If you have A B C D E F it sounds like you want the result to be A B CAB D E FAB But then ... | |
| Help with Userform to sheet entry. | 2/5/2009 |
| Q: I have been working an a large workbook, and am currently trying to make a sheet to house ... A: Instead of searching cell by cell in the ranges, you can do a select special for blanks & take the ... | |
| Conditional Formatting | 2/5/2009 |
| Q: I would like to condition a cell to turn a certain color if it's value is (1) past 1/1/2008 and (2) ... A: Select the cell which MAY change color. Use format/conditional formatting, change "cell value is" to ... | |
| Macro problem. | 2/4/2009 |
| Q: Hallo Bob. This is a MACRO question. I have been given a spreadsheet which lists data down the ... A: Sub TryThis() Set sel = Selection With Selection .Copy .Resize(1, 1).Offset(0, ... | |
| search data on multiple worksheets (looping) | 2/4/2009 |
| Q: At the moment my sheet looks like this (theres 31 sheets): Code Object Department In Out ... A: Sub SearchForStrin() Dim LSearchRow As Integer Dim LCopyToRow As Integer For each sh in ... | |
| Ranking a number vs. a pivot table | 2/3/2009 |
| Q: I am currently trying to find the rank of a value in cell g4 within all the numbers displayed in the ... A: INDEX('Sheet 1'!$15:$200,0,MATCH(1E+307,'Sheet 1'!$16:$16,1))) should give an invalid reference. the ... | |
| Deleting Rows | 2/3/2009 |
| Q: I need to delete rows in excel between specific times (22:15:00 to 05:45:00 inclusive) on ... A: Perhaps this formula will work: Enter in D1: ... | |
| excel problem | 2/3/2009 |
| Q: when I open a new workbook, and then when I open another workbook an error message appears ... A: You are probably opening a 2nd instance of Excel, rather than a new workbook within the same ... | |
| Microsoft Excel | 2/2/2009 |
| Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: Existing charts will look fine, but if you need to tweak the charts, you'll find a whole slew of new ... | |
| color formatting question | 2/2/2009 |
| Q: I know how to color a cell using conditional formatting, and I know how to color a cell by clicking ... A: Only other choice is by number formatting, but that's somewhat limited. Something like this as a ... | |
| Microsoft Excel | 2/2/2009 |
| Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: There is no way to increase the # of rows in Excel 2003 and earlier. Excel 2007 has 1,048,576 rows, ... | |
| Collating two worksheets | 1/31/2009 |
| Q: I have two worksheets of similar data. Worksheet X has 3 columns with over 10000 rows. Worksheet Y ... A: In Worksheet X, enter this in D1 (unused column) =IF(ISNA(MATCH(A1,WorksheetY!A$1:A$3000,0)),1,2) ... | |
| Using a formula to auto change color of a cell... | 1/31/2009 |
| Q: Im trying to put together an Acct register for me and my wife's bank accts. I have 4 main columns. ... A: Format all of column J's fonts to pink to begin with. Select all of column J, use Format/Conditional ... | |
| VBA Loop | 1/30/2009 |
| Q: I am having difficulty with what should (In theory) be a simple if and loop in VBA. For the range of ... A: Sub Macro11() Dim Cell As Range For Each Cell In Selection If Cell.Value = "" Then ... | |
| Macros | 1/30/2009 |
| Q: Bob, I have another question for you I have created a macro as follows: Range("A4").Select ... A: First, you want to have this as your first line in the Sub: Application.Screenupdating = False ... | |
| macro | 1/30/2009 |
| Q: 'nice to meet you. I use Excel 2003. I create a macro in, say, file A. I want to be able to use ... A: put the macro into your personal.xls. If you don't yet have one, then simply record any macro and ... | |
| Formulas | 1/28/2009 |
| Q: I am working on finding the sumproduct of multiple numbers between a given hour. I am currently able ... A: You could use this: ... | |
| Pasting information into column bleeds into other colums | 1/28/2009 |
| Q: I have text in TIFF format that I want to copy and paste into excel columns. (Excel Microsoft Office ... A: Pick any cell with data, use data/text-to-columns, deselect any checkboxes you have, click finish. ... | |
| Mandatory column for a sheet | 1/28/2009 |
| Q: I am working on an excel sheet to be fillled by different departments in the hotel when they fill i ... A: This is the procedure, but where you put it is also important. Press Alt/F11 to get to the VBE, ... | |
| Mandatory column for a sheet | 1/28/2009 |
| Q: I am working on an excel sheet to be fillled by different departments in the hotel when they fill i ... A: Assuming you don't mean COLUMN named ID -- to fill that they'd have to enter 65536 cells in xl2003! ... | |
| Workday calculations | 1/28/2009 |
| Q: Bob! How are you? Is it possible to use the INDIRECT-function with named ranges? I'm working on a ... A: I don't understand your named ranges - can you supply a few examples? Do you have Mon as a named ... | |
| Extracting Word from string from a List | 1/27/2009 |
| Q: I asked this question once already and I got an answer and it did not work. the guy I asked is not ... A: I would use a User-defined function. Press Alt/F1 to get to the VBE, use Insert/Module, put this in: ... | |
| if statements | 1/27/2009 |
| Q: The instructions for my assignment is to place a conditional statement that will place an "A" if the ... A: If the grade is in A1 and there's an X in A2 (You didn't say how it's indicated that they turned in ... | |
| Categorical scatter plot by Excel 2007 | 1/26/2009 |
| Q: IF it is possible for you please help me with followingI have multiple data points (10-20) for ... A: I would use for the x values of each separate category, a series of 1's, then 2's, etc, like: 1 4 ... | |
| count consecutive blank cell | 1/25/2009 |
| Q: PLEASE FIRST TRY TO UNDERSTAND WHAT I AM LOOKING FOR. YOU HELP WILL BE GREATLY APPRECIATED. I need ... A: In the worksheet, use something like =Blank5(A1:A100) Here's the UDF: Function Blank5(rg As Range) ... | |
| searching for text inside a text box | 1/24/2009 |
| Q: First of all thanks for offering your time to answer our Excel questions! I have large ammounts of ... A: One to find and highlight the text, one to reset all the text: Sub Findtext() Dim FindWhat As ... | |
| Excel Question | 1/24/2009 |
| Q: I am the process of doing short observations of teachers for five areas of classroom instruction. I ... A: Teachername Area Name Observation Value Date Bob Area1 2.5 ... | |
| searching for text inside a text box | 1/24/2009 |
| Q: First of all thanks for offering your time to answer our Excel questions! I have large ammounts of ... A: Sub Findtext() Dim FindWhat As String On Error Resume Next FindWhat = InputBox("Find what?") ... | |
| Excel Hyperlinks | 1/23/2009 |
| Q: In Sheet1 I have a cell A20 that has a text and I have inserted a hyperlink. In Sheet2 I have ... A: selection.directprecedents.Hyperlinks(1).Address you can do this on the summary sheet - again, ... | |
| Excel Hyperlinks | 1/23/2009 |
| Q: In Sheet1 I have a cell A20 that has a text and I have inserted a hyperlink. In Sheet2 I have ... A: Change $E$3 to the correct cell in Sheet2 (keep the "$") - this is just for the example. Right-click ... | |
| hh:mm:ss formatting | 1/22/2009 |
| Q: I want to be able to enter six numbers into a cell and have it formatted to read hh:mm:ss. (example ... A: If it's for looks ONLY (not actually be a time value really the number 203,400), you can format it ... | |
| NTSC Drop Frame Formula | 1/22/2009 |
| Q: Below, there is a formula I came up with after trying to convert from video frames to NTSC Drop ... A: I'm not sure I understand -- is the cell containing 29.97 DF the one to get this special formatting ... | |
| Cells | 1/21/2009 |
| Q: Hey! I wanted to ask you how do I make a cell appear of a coler, red for example if the value ... A: Format/Conditional Formatting/Change "between" to "less than", enter .7, click Format, give it a red ... | |
| Group info from cells | 1/20/2009 |
| Q: I must thank you for your help on previous questions. I have another one: When a user is finished ... A: You need helper columns. In E2 (NOT E1 -- leave blank) enter ... | |
| macro - find function | 1/20/2009 |
| Q: I'm doing a small project on which I need your help. I would be obliged if you can help me on this. ... A: Straight out of help: This example finds all cells in the range A1:A500 on worksheet one that ... | |
| order form | 1/19/2009 |
| Q: I am writing you from Turkey. I found a form from internet which makes preparing the order forms ... A: Excel can't "know" which ones are 8 and which are 18 unless you tell it. If you put the VAT rate in ... | |
| NTSC Drop Frame Formula | 1/19/2009 |
| Q: Below, there is a formula I came up with after trying to convert from video frames to NTSC Drop ... A: You can define many names, like a to be =MOD(F7,17982) b to be =MOD(F7-1800,17982) c to be ... | |
| Create Ledger Automatically | 1/19/2009 |
| Q: i have table in excel like this Date Particulars Debit Credit 1/1/09 Cash 2000 ... A: right-click the sheet tab, select view code, put this in: Private Sub Worksheet_Change(ByVal Target ... | |
| protect worksheet after closing it | 1/17/2009 |
| Q: I must thank you for your help on previous questions. I have another one: When a user is finished ... A: right-click the excel LOGO (near the file menu), select View Code, enter this: Private Sub ... | |
| Auto Insert Picture | 1/17/2009 |
| Q: Good morning, Bob; Thanks for your previous help and answers. Do you know if there is a way to ... A: All the possible pictures would need to already be in the workbook somewhere, then it can be picked ... | |
| Queary on excel | 1/17/2009 |
| Q: I want a master file, where in whenever i key & save data on file 1 sheet 1 & file 2 sheet 1 and so ... A: Right-click the excel LOGO (near the file menu) of each of the Test1 & Test 2 files and select View ... | |
| auto graph question | 1/17/2009 |
| Q: Is there a way which I can type a number into a cell eg 12 and have the 12 cells above it ... A: Yes. Assuming the row you're entering the value into (important to know) is 20, select A1:Z19 and ... | |
| drop down = | 1/17/2009 |
| Q: I have a scoring system for a cleaning evaluation ie: dirty tables means you loose one point, seats ... A: You can set up a 2 column table somewhere in the worksheet something like this: dirty tables ... | |
| Vlookup with formula | 1/16/2009 |
| Q: I work mainly with video and post production. From time to time, we receive EDL's or Subtitle files ... A: Now I'm not sure I fully follow, but you can make formula MUCH shorter in general by defining a ... | |
| Related to unique values in the drop down list question | 1/16/2009 |
| Q: I could get the unique list in the drop down menu, but it still lists #NUM! error in the list as ... A: I DO need the whole question including my answer -- basically you need to use the match to find the ... | |
| Mean, Min, Max, Standard Deviation | 1/16/2009 |
| Q: If I have only the Mean, min, max, and standard deviation of data, is it possible to create graphs ... A: Just treat the range as a regular set of numbers and use them as the source for the chart. For ... | |
| SUMPRODUCT formula | 1/15/2009 |
| Q: I am a little bit stuck with a SUMPRODUCT formula. Please see the formula below; ... A: the comma-notation doesn't work to do this, but you can use the asterisk (and then don't need the ... | |
| Vlookup with formula | 1/15/2009 |
| Q: I work mainly with video and post production. From time to time, we receive EDL's or Subtitle files ... A: If the dropdown is in E2, for example, you can use something like this: ... | |
| Unique data in drop down list | 1/14/2009 |
| Q: I have a workbook with 2 worksheets. 1st - Main Sheet; 2nd - Report. The main sheet has a data of ... A: The =OFFSET... was the definition of the range name "Rg", as I said. this is needed because of the ... | |
| Unique data in drop down list | 1/14/2009 |
| Q: I have a workbook with 2 worksheets. 1st - Main Sheet; 2nd - Report. The main sheet has a data of ... A: It's a little tricky and involved, but here's what you should do. You could use another sheet, say ... | |
| size (mb) each worksheet takes | 1/14/2009 |
| Q: Good morning Bill, I have a file that contains 12 worksheets. My overall Excel file is awfully big - ... A: activesheet.usedrange has the effect of resetting the position of the last cell which is often wrong ... | |
| size (mb) each worksheet takes | 1/14/2009 |
| Q: Good morning Bill, I have a file that contains 12 worksheets. My overall Excel file is awfully big - ... A: Unless there's obviously tons of data / formulas, 12GB is large for 12 worksheets. On each ... | |
| Reset data, save and back up | 1/14/2009 |
| Q: I need help with the following: Whenever I open a workbook (with data) in excel, I want to have a ... A: Right-click the excel LOGO (near the file menu), select View Code, enter this: Private Sub ... | |
| MACRO Search function | 1/13/2009 |
| Q: I am trying to create a search function for easier access to information. I have a workbook with 5 ... A: In the normal Find dialog, you have an option so search within all the worksheets, not just the ... | |
| Excel "Personal.xls" | 1/13/2009 |
| Q: I apologize if you've answered this before, I couldn't find anything on it. Since loading 2007, ... A: Personal.xls SHOULD open whenever you open Excel, but it should be a hidden window. It "sticks ... | |
| excel formula | 1/12/2009 |
| Q: Bob, I am using an Excel 2003 Workbook to calculate 12 month rolling totals for my production ... A: You could freeze the "old" values & leave only current month with ths formula...? It's hard to give ... | |
| Hyperlink Event | 1/10/2009 |
| Q: Good Afternoon. The sheet1.cell(B2) have hyperlink formula ... A: Which one? If you dblclick in sheet 2 cell H22 what should happen? If you dblclick in sheet 2 cell ... | |
| auto average in macro | 1/9/2009 |
| Q: I am wrting a macro and am wondering if it is possible to write an auto-average into the script ... A: Straight formula (in column C, for example): =AVERAGE(C1:INDEX(C:C,ROW()-1)) in VBA: Sub Avg() ... | |
| Excel Formula | 1/9/2009 |
| Q: =IF(H19=I19=J19=K19=L19=M19=N19=O19,"OK","DIFF")--- I have the following formula to check for ... A: The expression H19=I19=J19=K19=L19=M19=N19=O19 makes no sense in excel. Suppose they're all 2's. ... | |
| Comparing dates and formatting cells | 1/9/2009 |
| Q: Wonder if you can help me out. I am trying to get a spreadsheet together, in Column B I have dates ... A: Formula: =IF(B1-TODAY()<21,"DUE WITHIN 21 DAYS","") Either format the font as red, or use ... | |
| VBA - Dynamic Row Count | 1/9/2009 |
| Q: As part of a piece of analysis work, I have a report that needs to be run over 130 clients. The ... A: No need for many of your actions -- recorder makes for inefficient code: Sub Decrease_Totals2() ' ' ... | |
| restrictions in excel sheet for different user | 1/8/2009 |
| Q: Would it be possible to put a restriction to each sheet of 1 Excel file? I will be making a ... A: Right-click the excel LOGO (Near the file menu), select View Code, put this in: Private Sub ... | |
| Macro question | 1/8/2009 |
| Q: I have several spreadsheets (approximately 80 columns by 800 rows). I need to search the cells for ... A: You probably could have done it by using the macro recorder, but this should work: Sub Delete88() ... | |
| Printing VBA | 1/8/2009 |
| Q: I am some what new to VBA. I have written a few basic programs. I have 2007 Excel. I have a macro ... A: You have one too many commas between the 1 and the 2 -- remove one and it works fine. The syntax is: ... | |
| Hyperlink Event | 1/8/2009 |
| Q: Good Afternoon. The sheet1.cell(B2) have hyperlink formula ... A: If there's no hyperlink in sheet2!b2, then right-click sheet2's tab, select View Code, put this in: ... | |
| countif | 1/7/2009 |
| Q: I have a countif formula that counts a range, outputting how many 'a', 's' or 'c' etc there are. (a, ... A: This formula works as long as a cell containing aaaa would count the # of a's as 1 -- that is, the ... | |
| Copying data from multiple sheets in vba | 1/7/2009 |
| Q: i am fairly new to VBA and would like to know how to copy multiple sheets and concatenate them all ... A: Sub Joe() 'This is untested but should work for each sh in Sheets if ... | |
| countif | 1/7/2009 |
| Q: I have a countif formula that counts a range, outputting how many 'a', 's' or 'c' etc there are. (a, ... A: does a,s & c get a result of 3? or do you want a formula to check for # of a's and a separate ... | |
| Copying data from multiple sheets in vba | 1/7/2009 |
| Q: i am fairly new to VBA and would like to know how to copy multiple sheets and concatenate them all ... A: Sub Joe() 'This is untested but should work for each sh in Sheets if ... | |
| Copying data from multiple sheets in vba | 1/7/2009 |
| Q: i am fairly new to VBA and would like to know how to copy multiple sheets and concatenate them all ... A: Sub Joe() 'Assumes "Upload" sheet already exists 'This is untested but should work for each ... | |
| Common Column Data change across worksheets | 1/6/2009 |
| Q: I have 4 worksheets that all share a common 'attendees' list if you will in column 1. When updates ... A: You can put the sheets into "group mode", do the change, then get out of group mode and all will be ... | |
| Macro to copy several columns to a new | 1/6/2009 |
| Q: On the same worksheet I have two matrices. I know the number of columns for each matrix, but I ... A: I don't get it -- where do these numbers come from? For example, the Amy line before was Amy 30 4 ... | |
| Macro to copy several columns to a new | 1/6/2009 |
| Q: On the same worksheet I have two matrices. I know the number of columns for each matrix, but I ... A: Your change copies the bottom row of D:F to the next row down, not to the A:C matrix. So now I'm ... | |
| excel | 1/6/2009 |
| Q: A B 1 -3000 My salary 20000 2 23000 -6000 3 19000 -5000 please ... A: In B2: =if(a2="","",if(a2>20000,B1+3000,if(a2<20000,B1-1000,B1))) and fill down. But you say ... | |
| Consolidate data from multiple workbooks | 1/5/2009 |
| Q: I am trying to consolidate data from about 300 workbooks into one master workbook, with seven ... A: I can, but I'd need more info -- first, I don't understand the significance of the seven worksheets ... | |
| Excel file comparision | 1/5/2009 |
| Q: i am working on excel and i had a huge problem in comparing excel files. i.e, i had an Excel file ... A: I don't follow. You want to compare Sheet1!B3 against B3:B700 and then Sheet1!C3, etc thru ... | |
| Formula in Data Validation | 1/4/2009 |
| Q: How are you?I put the formula in data validation it was working fine but when I add more list in the ... A: You can do it this way. Set up a 2 column table where the first column is 1,2,3,4... and the 2nd ... | |
| Formula in Data Validation | 1/4/2009 |
| Q: How are you?I put the formula in data validation it was working fine but when I add more list in the ... A: The number of items in the CHOOSE list is limited to 29. Your match can't return a number over 29 ... | |
| Queary on excel | 1/3/2009 |
| Q: I want a master file, where in whenever i key & save data on file 1 sheet 1 & file 2 sheet 1 and so ... A: This can be done using VBA with the master file open - but are you saying that whenever you enter ... | |
| Weekly Report in Excel | 1/1/2009 |
| Q: I would like to prepare a weekly report which lists on the tasks that a particular person is working ... A: I think the easiest way to do this is using Filters. Try recording the steps to filter by the wanted ... | |
| How to change color of cells with specific dates in them | 12/31/2008 |
| Q: Bob, This was the information that you told me to do earlier: You need a user-defined function -- ... A: Select all the dates. Assuming the holidays are in I1:I10, do this: Format/Conditional Formatting ... | |
| Splittting spreadsheet with password protection | 12/31/2008 |
| Q: Could you please tell me if you can split a MS Excel 2003 spreadsheet with the right side being ... A: Well, sort of. You need VBA code, something like this -- Right-click the sheet tab, select View ... | |
| Drop down list | 12/31/2008 |
| Q: How are you? I need your assistance that I made a dropdown list in B1 tha data is from A1 to A100 ... A: Not by a data validation list nor a forms combobox, but by a control toolbox combobox, yes. ... | |
| Setting up schedule for weekends and holiday | 12/31/2008 |
| Q: I have a Inspection sheet that lasts for 6 months and is only for weekend and holidays. The first ... A: Holidays is the name you need to give to cells containing the actual holidays, like if you list them ... | |
| What is the best way to fill in dates on mulitple worksheets within a workbook | 12/30/2008 |
| Q: I have a schedule for each month of the year. All the basic information is the same. I currently ... A: Put this function in the VBE (Alt/F11, Insert/Module, paste this in): (in each workbook) Function ... | |
| Setting up schedule for weekends and holiday | 12/30/2008 |
| Q: I have a Inspection sheet that lasts for 6 months and is only for weekend and holidays. The first ... A: You need a user-defined function -- set up a range with all the holidays, named "Holidays", then in ... | |
| Dynamic hiding and unhiding accounting rows. | 12/30/2008 |
| Q: I am hoping you can help me on this!! I am creating a template that for reporting our accounting ... A: You probably need a helper column in each sheet which has a formula similar to ... | |
| i have some problem in excel sheet | 12/29/2008 |
| Q: This side kkdas from india.I m working in gloves manufacturing company as a IT Incharge. I would ... A: There's a lot of what you're saying that I just don't understand. For example, "time automatically ... | |
| create a range i seeing as cell | 12/29/2008 |
| Q: i have a column and i need check using subtotal to compare with other subtotal with add in ... A: Sorry - still don't understand -- How can you have A7:A14 (which is 8 cells) contain 15 items? Then ... | |
| Populating spreadsheets | 12/28/2008 |
| Q: I have a spreadsheet that contains a list of clients and their contact info. The first column has ... A: There's no direct simple way to do that -- fields change only by direct links or thru VBA code. ... | |
| Excel messed up by macros | 12/27/2008 |
| Q: I think I've run a macro that has disabled all my toolbars and menus. At this moment, my Excel shows ... A: First, change your security settings so macros can run: Tools/macro/Security and set to either ... | |
| VLOOKUP with Nested IFs | 12/27/2008 |
| Q: Good Morning, Bob! I have a workbook with multiple sheets. I am using various VLOOKUP formulas ... A: First, you can simplify this: PARTSLIST!$D$2:PARTSLIST!$K$9899 to PARTSLIST!$D$2:$K$9899 But my ... | |
| INDEX formula | 12/27/2008 |
| Q: How are you? I am using the formula which you told me ... A: I have no idea what that formula is trying to do. The syntax for the match is MATCH(a value,an ... | |
| Excel | 12/27/2008 |
| Q: I have a spreadsheet sheet 1 "Individual" sheet 2 "Group" If I add a name and score to the ... A: Start with either a blank sheet or perhaps the Group sheet, then use data/Consolidate, then click in ... | |
| Excel | 12/27/2008 |
| Q: I have a spreadsheet sheet 1 "Individual" sheet 2 "Group" If I add a name and score to the ... A: Sounds like you could more easily use Data/Consolidation or Pivot table on demand when you need all ... | |
| Excel | 12/26/2008 |
| Q: I have a spreadsheet sheet 1 "Individual" sheet 2 "Group" If I add a name and score to the ... A: I don't really follow what you want to do. It seems like if you're adding to any individual sheet, ... | |
| Choose formula | 12/25/2008 |
| Q: How are you? I am using the formula which you told me ... A: If you add Suzuki N/A 0 to C46:E46, then you can put this formula in E10 via ctrl+shift+enter ... | |
| inserting intermediate rows | 12/24/2008 |
| Q: In an excel sheet where hundreds of rows are filled with data,is there any shortway (other than ... A: If your data goes up to column H, for example, then in I1 enter 1, in I2 enter 2, and select I1:I2 ... | |
| Custom button | 12/23/2008 |
| Q: I want to create custom button in MS Excel 2003, but NOT in toolbar but just somewhere in cells. ... A: If you dno't want a forms toolbar button, and you don't want to assign a macro to an autoshape, I'm ... | |
| Extracting data to make report | 12/23/2008 |
| Q: I have data in the main sheet, titled "raw data". there are currently 4 columns namely agent, score, ... A: You need a helper column, so you can use E, keep everything hidden by the color of col E. in ... | |
| importing from other spreadsheet macro | 12/23/2008 |
| Q: I apologize in advance if this is a complicated request. I have two files named "NETS 22 Dec ... A: this solution assumes you only need to look at 5000 rows -- adjust the formula if necessary. Also ... | |
| Choose formula | 12/23/2008 |
| Q: How are you? I am using the formula which you told me ... A: Your LandCrusier (which is misspelled and should probably be LandCruiser) isn't the same format as ... | |
| Excel query | 12/23/2008 |
| Q: am having one worksheet, having related data of different persons & now i want to prepare another ... A: If I understand you correctly, in the individual sheet you need to create formulas which link ... | |
| find and display in excel | 12/22/2008 |
| Q: Please help :) Im i have a spead sheet with Hundreds of names and addresses on it, every one has a ... A: If your ids are in column A in sheet2, for example, and the names & Addresses are on sheet 1 in cols ... | |
| Searching for Text within a Cell in Another Sheet in Excel | 12/22/2008 |
| Q: I need to check a column of short hostnames in one sheet to see if the string exists within a column ... A: Sheet1!B1: =IF(ISNA(MATCH(A1&"*",Sheet2!A:A,0)),"NOT FOUND","FOUND") Sheet1!C1: ... | |
| Excel converted my general numbers to date after file open | 12/22/2008 |
| Q: G'day. I have a 622KB large xlsx file that I use every day. Today when I open it I find that all my ... A: If this were a bug in excel, someone else would have also found it quite some time ago, so I'l still ... | |
| Excel hyperlink filename | 12/21/2008 |
| Q: Good morning I am trying to create a spreadsheet with hyperlinks in to reference other work sheets ... A: Put the filename in another cell, say E1, and you can use this: ... | |
| Linking y axis min/max scall to cell in each excel sheet | 12/20/2008 |
| Q: I hope I can make this simple and clear as to not confuse you. I am currently building an excel ... A: This macro assumes D2 & D3 contain the Min/Max values: Sub MinMax() With ... | |
| Excel Move cursor over cell to show contents | 12/19/2008 |
| Q: Bob, do you know a way that I can leave a cell a normal size w/o resizing but when I place the mouse ... A: Again, without clicking, you can't do it. If you set comments to not show the flag you also won't ... | |
| Time formula | 12/19/2008 |
| Q: Bob How are you? Below are the details of the formula which I searched on allexperts website and ... A: You didn't specify what to enter in C2. When I enter 3, I do see 3:03, but that's because of the ... | |
| An IF formula to copy an entire row from a separate worksheet | 12/18/2008 |
| Q: In an excel spreadsheet I have on one worksheet about 100 rows with information on certain ... A: Probably the easiest (without writing a macro) is use Data/Filter. Select the dropdown arrow on ... | |
| Calculating days between dates | 12/18/2008 |
| Q: I hope you can answer this question for me as it's driving me up the wall! I'm calculating the ... A: I don't think you can get what you want due to the nature of the beast. Oct 30 to Nov 30 one would ... | |
| excel 2007 formula ? | 12/18/2008 |
| Q: I would like to create 2 cells or a drop down, you choose either one or the other value, that ... A: I don't understand... Can you give more specific example? What do you mean by "create 2 cells or a ... | |
| check for data in 2 cells and make a persisting time/date stamp based on the cell with data | 12/18/2008 |
| Q: I am sending you a previous thread to let you know where I am. The fellow who answered my last ... A: I'm unfamiliar with the Mac, and in Windows I'd say to right-click the sheet tab and select View ... | |
| Hyper Link and drop down menu in excel | 12/17/2008 |
| Q: First, can you tell me how to create a drop down list in excel. Then can you explain how to create ... A: My solution was for named ranges on the same sheet as the validation, not for other sheets, although ... | |
| Hyper Link and drop down menu in excel | 12/17/2008 |
| Q: First, can you tell me how to create a drop down list in excel. Then can you explain how to create ... A: Data validation can't put hyperlinks into its list, but you can simulate them this way: Use ... | |
| grouping option buttons | 12/10/2008 |
| Q: I am trying to group option buttons into four groups so each of the groups works independently. I ... A: If you're referring to putting these on a worksheet, the only solution I've found is actually to ... | |
| Dynamic arrays | 12/10/2008 |
| Q: Hey Bob, Question of setting up a dynamic array for data consolidation in that the number of ... A: This should work, but untested. Build the array string in the loop and use the Consolidate once ... | |
| Excel file list | 12/10/2008 |
| Q: I'm using the following code: Public Sub ListFiles2() Dim Directory As String Dim ... A: Try this: Public Sub ListFiles2() Dim Directory As String Dim FileName As String Dim IndexSheet As ... | |
| Userform link to Excel | 12/9/2008 |
| Q: I have linked data entered onto text boxes on a user form onto a spreadsheet. What I would like to ... A: yuo can put a scrollbar on the userform whose vba code is connected to a value which could be a row# ... | |
| Using Macro to Add Rows and Delete Previous ones | 12/8/2008 |
| Q: I am looking for a macro that that add all rows with the same ID number, creat a new sum row while ... A: Excel 2007: Select one cell from your range A1:C80000 Insert/Tables/PivotTable Click Existing ... | |
| Userform link to Excel | 12/8/2008 |
| Q: I have linked data entered onto text boxes on a user form onto a spreadsheet. What I would like to ... A: I'm not sure what you mean by "retrieve that data entered back onto the userform"...? If you mean ... | |
| Using Macro to Add Rows and Delete Previous ones | 12/8/2008 |
| Q: I am looking for a macro that that add all rows with the same ID number, creat a new sum row while ... A: Sorry, I didn't realize it was just a small sample. With such a large sample, you have 2 good ... | |
| Excel VBA Project Explorer | 12/7/2008 |
| Q: On one of my PCs (Windows XP) when I close a workbook, either manually or by VB routine, it remains ... A: Sounds like you have an object variable in one of the workbooks which was left in memory and excel ... | |
| Using Macro to Add Rows and Delete Previous ones | 12/7/2008 |
| Q: I am looking for a macro that that add all rows with the same ID number, creat a new sum row while ... A: You don't need a macro - there's a formula which will do. If your original list starts in A1, then ... | |
| Shortcut to a macro | 12/5/2008 |
| Q: I want a macro to run when I press alt+v .. How can I do that? .. I hv written following code for ... A: It seems that the Alt/letter keys which match the menu commands won't work like you'd think they ... | |
| Advanced Transposing | 12/4/2008 |
| Q: Greetings, I am in need of a way to transpose a row of data from Sheet1 into a column of data in ... A: Something needs to trigger the transfer. Assuming you can run a macro to transfer the data for the ... | |
| If then macro | 12/4/2008 |
| Q: I am trying to write an If...Then macro to apply conditional formating to specific ranges. For all ... A: What's in the rest of column B? If the whole column is either x or blank, then you can select them ... | |
| Character deleting macro | 12/4/2008 |
| Q: After your sound advice last time compared to others, I would like to ask you another question I ... A: What's REALLY in the cell is a value called a Serial Number, not a text string consisting of dates. ... | |
| functions | 12/3/2008 |
| Q: I have an excel spreadsheet template with 3 columns. Column A is a list of accounts, column B is ... A: Can you explain about that 2%-- are columns B & C percentages? Current Cash and Projected cash sound ... | |
| Small change in large macro | 12/2/2008 |
| Q: I have a small change to a macro that I cannot figure out. The code below works great. I just need ... A: To do this: cell D3 in worksheet "(4) Watch List" to be copied to The next available row in column ... | |
| Vlookup across multiple sheets | 12/2/2008 |
| Q: I've read your answer regarding the Vlookup across multiple sheets by my problem I more complicated. ... A: On the assumption that the product code is in the same ROW on each sheet, you can do this: Create 2 ... | |
| IF formula or any other. | 12/1/2008 |
| Q: Bob we have 5 types of car toyata yaris honda civic and BMV and all have 5 service level I defined ... A: THis is the first time you're mentioning column X, I believe, but I need to see that layout -- why ... | |
| IF formula | 11/30/2008 |
| Q: How are you? I have a question that I am trying to use the IF formula but items are more than 20 and ... A: Sorry - still unclear. First, I don't know what that 48 & 44 are at the bottom of your example. I ... | |
| IF formula | 11/29/2008 |
| Q: How are you? I have a question that I am trying to use the IF formula but items are more than 20 and ... A: There are often other ways to get what you want, but you didn't say what you're trying to do. An ... | |
| excel trendline | 11/28/2008 |
| Q: Is there a way, with code or otherwise, to capture in a cell the equation generated by a trendline ... A: select 1 row x 3 columns enter this formula with ctrl+shift+enter (adjusting the ranges): ... | |
| data matching and sorting | 11/26/2008 |
| Q: I have 2 tables in excel, each with contain 2 columns, but hundreds of rows deep. In the first ... A: in I1: =MATCH(A1,$E$1:$E$1000,0) in J1: =A1 in K1: =B1 in L1: =INDEX(F:F,I1) Fill down to row 1000 ... | |
| Formula Question - IF statements | 11/26/2008 |
| Q: I have attached a file that we are trying to get to work. We can word it on paper just fine but can ... A: The first part is true: E6 is less than J3 and E6*J3 is greater than J4, so the answer is true which ... | |
| Distinct value within date range defined by cells and across multiple sheets. | 11/26/2008 |
| Q: Stuart answered my original question which I thought would be simple to combine the formulas to ... A: Formulas which work across sheets is a very limited set. To make this work, you'd need to reference ... | |
| Excell data directly selected in cell | 11/26/2008 |
| Q: I was trying to utilize this information. ... A: View/Toolbars/Control toolbox. Click on more Controls, then find the Calendar control 11.0. ... | |
| End-page totals in excel tables | 11/25/2008 |
| Q: I have a question about making tables. My tables consist of many pages and I fill the tables with ... A: If you're talking about having the totals of the items on each page, and the # of items keeps ... | |
| data matching and sorting | 11/25/2008 |
| Q: I have 2 tables in excel, each with contain 2 columns, but hundreds of rows deep. In the first ... A: if the first table is in F1:G100 and the 2nd table in K1:L100, for example, then in your example, if ... | |
| Allowing Manual Entry in Drop Down Lists | 11/24/2008 |
| Q: Bob, I have created two drop down lists that are linked. The first list contains the names of ... A: If the dropdown lists are from data validation, then there's no way to both have a list AND allow a ... | |
| Referencing a certain column in a particuarly row with 12 values for each month | 11/24/2008 |
| Q: I have a table of months with one percentage for each month that has happened so far. These ... A: Not sure what you mean by "Current Month box". I think it'd help if you send me a sample file to ... | |
| nested if statement | 11/23/2008 |
| Q: I am trying to create a procedure that will check-in and check out rented movies. Right now I am ... A: You didn't say what's not working in the macro you sent; Your subject says nested IF statement, and ... | |
| printing graphs | 11/21/2008 |
| Q: is there a way to custom position multiple graphs on one page in Excel? thx nick A: custom position? What do you mean? To ensure one page, you can copy the graph object (ctrl/click the ... | |
| Excel YTD | 11/20/2008 |
| Q: What formula can I use in order to calculate the YTD for each employee? I need to sum all hours and ... A: If what you have to add up is in the SAME cell in all the sheets, then you can use something like ... | |
| Macro not working | 11/19/2008 |
| Q: I'm hoping someone can help me. I'm VERY new to macros and have been asked to prepare a ... A: It looks fine -- I'd suggest putting some debug statements to help find out what's the problem. For ... | |
| Distinguish between Letters and Numbers | 11/19/2008 |
| Q: I have many rows of data starting in Cell A1. Each row has an identifying value in Col A. Each ... A: In general, worksheet functions, like MATCH, used in VBA, need to be any of these 3: ... | |
| excel formula | 11/18/2008 |
| Q: I need a formula that will only total the amounts in a row or column that are not highlighted. ... A: Changing the fill color will not trigger a recalc, so if you change the fill color you'll have to ... | |
| Borders around indefinate amount of cells | 11/18/2008 |
| Q: I need to add some code to my macro that will encompass the data in a heavy border provide thin ... A: Sub BorderIt() With Range("B10").CurrentRegion With .Borders(xlEdgeLeft) ... | |
| excel formula | 11/17/2008 |
| Q: I need a formula that will only total the amounts in a row or column that are not highlighted. A: You need a user-defined function to do it. Two, actually. Function RestOfRow(Rg As Range) ... | |
| Automatically updating sheets into one mastersheet | 11/17/2008 |
| Q: I have a situation where multiple users have to add data in their personal sheets, though the data ... A: Right-click the excel LOGO (Near the File menu), select View Code, enter this (change "Sheet1" to be ... | |
| Progressive sum | 11/16/2008 |
| Q: I have asked this question to Tom Ogilvy. He told me to check this question to you. I have data ... A: Tom is right -- there's no way you can do this without either a helper column or a User-defined ... | |
| copy the name of the worksheets to another worksheet if two criteria are met | 11/15/2008 |
| Q: I hope to find you well. I would like to ask you something really important and urgent for my work ... A: Worked fine for me. Slight change so it doesn't include the result sheet, but you may run into the ... | |
| Linking to Cell contents | 11/14/2008 |
| Q: I have a workbook that has several worksheets. On the Master worksheet I have a Title in column A ... A: On Worksheet2 (and others) you need to pre-fill cells with formulas to automatically get the data ... | |
| copy the name of the worksheets to another worksheet if two criteria are met | 11/14/2008 |
| Q: I hope to find you well. I would like to ask you something really important and urgent for my work ... A: Sub GetNames() 'Untested... Set Result=Worksheets.Add N=1 for each x in Sheets If ... | |
| Excel data validation using user defined function. | 11/14/2008 |
| Q: I am trying to do data validation using a calculated list. For example, given two column excel data ... A: I'm assuming the real data does NOT simply contain A, B, C,... because this technique wouldn't work ... | |
| How to refer to a variable file name in a formula | 11/13/2008 |
| Q: I'm using VBA for the first time to try to automate an "add patient" process for a patient tracking ... A: Instead of ActiveCell.Formula = "='[ActiveWorkbook]Sheet1'!A15" you can set a variable to that ... | |
| Converting a date | 11/13/2008 |
| Q: I am currently converting dates by hand, but wondered if there was a particular formula or way to ... A: I need to have the specific way they're currently appearing in a cell. You say they're in the 1800's ... | |
| SUMPRODUCT with AutoFilter | 11/13/2008 |
| Q: I have a table of employees and projects, showing how employees are allocated (by %) to individual ... A: For TOTAL FTE, change from SUM to SUBTOTAL. Something like =SUBTOTAL(9,c2:c100) and fill right. I ... | |
| Excel Hiding a Forms Checkbox | 11/13/2008 |
| Q: I am creating a spreadsheet that changes depending on a selection from a dropdown box in A2. I ... A: It should work in xl2000 (which I don't have). But you do need to ensure the names of the check ... | |
| Excel Hiding a Forms Checkbox | 11/12/2008 |
| Q: I am creating a spreadsheet that changes depending on a selection from a dropdown box in A2. I ... A: Right-click the sheet tab, select view code, enter this: Private Sub Worksheet_Change(ByVal Target ... | |
| Excel help | 11/11/2008 |
| Q: I have star, Flower and house images. I have built a database that allows the user to input ... A: I couldn't find a font which has those 3 objects as images -- that would have been the easiest ... | |
| Generating a worksheet(s) from a cell value | 11/11/2008 |
| Q: an integer entered in a cell a copy or copies of an existing spreadsheet is/are created. I've ... A: Assuming the hidden sheet's name is "Master", then right-click the sheet tab, select View Code, ... | |
| Excel | 11/10/2008 |
| Q: Is there a macro I can used to remove a bottom border if another cell is blank else leave the bottom ... A: You can do this with conditional formatting, no need for a macro. Give B1 a bottom border. Click the ... | |
| dynamically refencing info from another file. | 11/10/2008 |
| Q: I am developing a worksheet to organize info dynamically from other sheets. All my reference sheets ... A: If I understand you correctly, you'd need a macro to do that, but I can't help without knowing a lot ... | |
| Lookup multiple matches | 11/10/2008 |
| Q: Please help. I have a table witch I am using vlookup to pull information from. My look up is only ... A: Let's say your table is I1:J100. Let's say what you're looking for in the lookup is the value in ... | |
| find in worksheet | 11/10/2008 |
| Q: kindly assist me i am trying to copy an active cell from a diffrent worksheet I can conly copy from ... A: Sorry - this is also confusing. • You have a dropdown menu (menu? you used VBA to create this menu?) ... | |
| find in worksheet | 11/9/2008 |
| Q: kindly assist me i am trying to copy an active cell from a diffrent worksheet I can conly copy from ... A: I don't understand -- Can you describe in plain words what you want to accomplish instead of demoing ... | |
| FORMULEA | 11/7/2008 |
| Q: I've built a spreadsheet that accurately displays my company's price list. However, from time to ... A: Suppose there's a 10% increase. put 1.1 (110%) in an unused cell, copy it, select ALL the cells ... | |
| Securing my Userform and code | 11/6/2008 |
| Q: I made a userform in a ecxel 2007 workbook and wrote its code. my question is how can i secure and ... A: In the VBE: Tools/VBAProject Properties access the Protection tab, click Lock Project for viewing ... | |
| Add a loop to a working macro | 11/6/2008 |
| Q: Bob, How can a loop be added to the following macro to execute down column A until A is blank? ... A: It's not easy to follow your logic here since it starts with Selection.Copy, which is not a good ... | |
| Excel Pivot Tables | 11/5/2008 |
| Q: I have many worksheets which were created in Excel 2003 with pivot tables. When I open these ... A: I set up an example for myself with 20000 rows in xl2003, opened in 2007 and had no issue like you ... | |
| auto fill | 11/5/2008 |
| Q: When filling a formula down my spreadsheet is there a way to make it fill using values across? For ... A: In A3: =SUM(OFFSET($A$1,0,0,1,ROW(A1))) This formula is the same as =SUM(OFFSET(A1,0,0,1,1)) which ... | |
| Pivot tables | 11/4/2008 |
| Q: In a pivot table there is an option to highlight multiple rows at the same time for easy formatting ... A: It's for an inner field, not the left-most field, Play with moving the cursor slowly to the left of ... | |
| Hidding a row based on a cells value | 11/4/2008 |
| Q: I am trying to hide rows so that I can chart only the visible data. I have the code for the charts ... A: I didn't realize that ALL the items in DI were formulas. Change the code to Range("DI:DI").Formula ... | |
| update of pivot without files open | 11/4/2008 |
| Q: Hope your having a good day. There is something regarding pivot tables I am unable to work out at ... A: What happens when you try to refresh the PT without opening the files? Are you getting a prompt ... | |
| copy specific cell if another cell in a range matches | 11/3/2008 |
| Q: I have 2 workbooks I'll call A and B. A has dozens of worksheets that are unique customer invoices. ... A: right-click a sheet tab in workbook A, use Select All Sheets, click in F5, enter this formula: ... | |
| Excel | 11/2/2008 |
| Q: I have two questions. 1. I cannot delete the following error message that appears when starting up ... A: You can remove the textbridge by going to view/toolbars/customize, then clicking on TextBridge and ... | |
| Excel | 11/2/2008 |
| Q: I have two questions. 1. I cannot delete the following error message that appears when starting up ... A: I don't know what the TextBridge toolbar item is. I'd suggest you do a search for *.xlb on your ... | |
| Excel Question | 11/2/2008 |
| Q: I am looking for a way to have a formula look at a list of numbers, and then return a LIST of ONLY ... A: You can do this with data filter. Select one cell in the range, use Data/Filter/Autofilter. Click ... | |
| Excel | 10/30/2008 |
| Q: Is there a way that I can have the (:) entered when I enter the time regardless of the number of ... A: VBA code would be required to "automatically" insert the ":", and since the places where you want to ... | |
| Automatically filling fields | 10/30/2008 |
| Q: This might be a basic question but I don't use Excel as often as I'd like and I'm trying to keep it ... A: You need to have a table somewhere of the products and prices, like Mug 3.95 T-Shirt 11.99 ... | |
| excel | 10/28/2008 |
| Q: How do i switch around entire columns ie put B before A etc.,if i drag n drop B onto A, A then ... A: a few ways: • Click the "B" to select all of column B; Edit/Cut; click the "A" to select all of ... | |
| Excel | 10/28/2008 |
| Q: Is there a way that I can view and/or edit my code in my tabs all at once instead of clicking on ... A: By code, one usually means VBA, but code in your tabs makes me think you mean the data in the ... | |
| Excel 2002 vs. 2003 calculations | 10/27/2008 |
| Q: in our work we use a lot of SSDev and similar calculations, mostly on the mainframe. We recently ... A: All I can suggest is you look in Help in each version which should explain how it's calculated. I ... | |
| Sheet name linked to cell. | 10/27/2008 |
| Q: I have a workbook with the first sheet named 'Project'. I have several other sheets that give a ... A: This isn't something which can be done automatically -- you have to run a macro to do it. If the ... | |
| IF Function with text as the test | 10/27/2008 |
| Q: Greetings, I'm trying to use IF to test out alphanumeric strings (such as FF214) but I want to test ... A: Not quite sure what you're asking, but if you're looking to test sheet FF based on the first 2 ... | |
| Sorting by most frequent entries | 10/26/2008 |
| Q: I'm an excel newb and I have a question about sorting entries by most frequent occurrences. My ... A: If there were no duplicates, you could use Data/Filter/Autofilter, and in the resulting dropdown ... | |
| Date formatting error | 10/23/2008 |
| Q: I am having an issue with formatting the date in ecel. Background: I am using a web-based customer ... A: Most likely the reason is that some of these are text, and formatting text as a number has no ... | |
| The Object Invoked has Disconnected from Its Clients | 10/23/2008 |
| Q: The error occurs at this line of the newRate procedure: Add Type:=xlValidateList, ... A: Likely there's not much you can do about it aside from pure trial & error -- however, I can take a ... | |
| Moving cells | 10/23/2008 |
| Q: I am using Excel to keep a basic list of customers that require a call. What I am looking to do is ... A: Assuming the "specific column" is column H (arbitrary), and that you want to move the entire row to ... | |
| Merge Excel based on Column Headers | 10/23/2008 |
| Q: I have a Excel Sheet(Say OG.xls) which has some data already in it with some 5000 rows with headings ... A: You said "All these 5 files has different no of columns but identical to that of OG File" so where ... | |
| Excel Formula | 10/22/2008 |
| Q: Mike belongs to group 1 Joe belongs to group 2 Ana belongs to group 3 Peter belongs to group 4 ... A: I don't know the criteria for knowing how a group has a correct answer. Unless you mean that in your ... | |
| VBA | 10/22/2008 |
| Q: Hey Mr. Umlas, I am a beginner in VBA and I stress the word beginner. I would like to know if you ... A: It could depend on whether the 2nd workbook is open; I'm going to assume it is, and is named ... | |
| macro to collect data | 10/22/2008 |
| Q: i'm trying to write a marco, that collects data from diffrent sheets to a single , overview sheet. ... A: 1 - you haven't asked a question, so I don't know what you're looking for 2 - Once you can tell me ... | |
| HLookup sum | 10/22/2008 |
| Q: In sheet 2 have a date table under month column headings. ie, Jan through to Dec. In sheet 1 I have ... A: If the total row in Sheet1 for Jan is A50, for example, and if the date entered were Feb so you'd ... | |
| Creating links between cells and sheets | 10/22/2008 |
| Q: My Excel file contains a number of sheets (e.g. 10). Sheet 1 is the master sheet and contains a ... A: Can I assume that in sheets 2 to 10 there are no duplicate works order number? If that's the case, ... | |
| counting cells | 10/21/2008 |
| Q: I have a spreadsheet in Excel, with actual / forecasted spends for every month in columns (from ... A: First, the image is too small forme to decipher, so please send a real workbook to me at ... | |
| Data Lookup | 10/21/2008 |
| Q: Is there a way to pull a value from within a table of information and add it to another based on the ... A: According to your description the first table exists all in one row? If that's the case, and the ... | |
| Counting dates | 10/21/2008 |
| Q: I have 2 sheets in my workbook. One sheet has information for each problem in three columns - Log ... A: I know how to do this, but I can't really picture the layout of your data. What's a "Plate problem"? ... | |
| Merge Excel based on Column Headers | 10/21/2008 |
| Q: I have a Excel Sheet(Say OG.xls) which has some data already in it with some 5000 rows with ... A: Untested, but this should work -- change A,B,C,D,E to the real names -- the files should all be in ... | |
| counting cells | 10/20/2008 |
| Q: I have a spreadsheet in Excel, with actual / forecasted spends for every month in columns (from ... A: It can easily be done, but I need to know the exact contents of CZ4:DK4 -- is it the actual date, ... | |
| advanced goal seek | 10/19/2008 |
| Q: respected sir 1.we have any advanced options like getting goal by changing n multiple cells by equal ... A: Here's your macro: Sub AddToGoal() Set rg = Application.InputBox("Select the cells to ... | |
| Calculation | 10/17/2008 |
| Q: Please see the image.This format for small net cafe. # Have to enter data manually in "Name" "Member ... A: 2 things I don't follow: • Is the ID# totally a random number? Any particular length? Should it ... | |
| Copying Excel Formulas | 10/16/2008 |
| Q: I'm trying to copy a formula but need to change the cell reference constantly. For example, on cell ... A: Change formula in B3 to: =SUM(OFFSET(UK!$B$4,7*(ROW()-3),0,7,1)) and fill down. in B3, ROW()-3 is ... | |
| If/then using a macro | 10/16/2008 |
| Q: I know this is easy but for some reason I am not getting it. I am trying to get my macro to run ... A: You need to place the macro in the worksheet's change event - right-click the sheet tab, select View ... | |
| conditional formats | 10/16/2008 |
| Q: I have a formula in a cell as follows =IF(L31>0,REPT("g",(L31/10)),"") This simply counts and ... A: Well, the cell doesn't contain a value like >100 or between 50 & 75 -- it contains either blank or ... | |
| Add row Macro | 10/16/2008 |
| Q: See below the data - this is provided once a month and not all the pages will be there each time so ... A: 1 - I don't understand "not all the pages will be there each time..." 2 - I don't understand "I need ... | |
| Excel 2007-apply function to a range of data | 10/15/2008 |
| Q: I need to apply a function(=round or =ceiling) to a range of numeric data. How is this done? I ... A: If the range of values are in A1:A100 you can run this VBA code (change to suit your needs): Sub ... | |
| Timekeeping in Excel | 10/15/2008 |
| Q: I am having trouble coming up with the correct formula to keep my own time in / time out record. My ... A: See if the cell is formatted as Text. What you can do is ctrl/shift/1, then re-enter the formula. ... | |
| Using Cell References in GetPivotData Formulas | 10/15/2008 |
| Q: I was hoping you could help me with the getpivotdata function. I've been trying to learn as much as ... A: When you reference a date in a cell, like ="Today is "&A1 and A1 contains 10/1/2008, you'd see Today ... | |
| Excel Formula Question | 10/15/2008 |
| Q: I'm working on an expense spreadsheet. Column A has the expense date in the format mm/dd. Column B ... A: One way would be to filter the main sheet, and only display a particular month from the filter ... | |
| excel comparision | 10/14/2008 |
| Q: I am having two excel files.One of them has the values which are allowed and the other one is the ... A: If the ranges are named rg1 and rg2 (rg1 being the allowed values), then ctrl+shift+enter this ... | |
| Show Data between dates | 10/14/2008 |
| Q: I made a UserForm to insert data in the different work sheets in my work book using textBoxes and ... A: Private Sub CommandButton1_Click() On Error Resume Next Range("A1").AutoFilter ... | |
| advanced goal seek | 10/14/2008 |
| Q: respected sir 1.we have any advanced options like getting goal by changing n multiple cells by equal ... A: Goal seek can change one cell only. If your 1;2;3 was =1+C1 =2+c1 and =3+C1, and you had a total (in ... | |
| Conditional format in Excel 2007 | 10/14/2008 |
| Q: I want to use the new features of conditional format in Excel 2007 (bars or colour grades) to format ... A: Select all the names (say that's A1:A50) Home/Conditional Formatting/Manage Rules/New Rule Use a ... | |
| inventory control | 10/14/2008 |
| Q: i work in the construction field and i am having problems keeping track of inventory that comes to ... A: Not being an "inventory specialist", I'm not familiar with any specifics, but it seems to me all you ... | |
| Combinations/Permutations | 10/13/2008 |
| Q: I'm running windows nt, excel 2003 My level of proficiency with excel is about a 2. I want to get ... A: Put all those values in G1:G16, run this (however, there are no combinations which happen to add up ... | |
| VBA | 10/13/2008 |
| Q: I have spreadsheet with multiple parts for multiple Jobs on it. I would like to have this ... A: you'd need to set up linking formulas, either by cell address or range names. But you've supplied ... | |
| Excel | 10/13/2008 |
| Q: i have a spreadsheet which contains information about all our our products, costs, selling values ... A: If the sheet with the info is Sheet1 and goes from A1:E100, for example, then if Sheet2 column A ... | |
| excel comparision | 10/13/2008 |
| Q: I have a column of numbers from which i want to find the maximum and minimum value. I am ble to do ... A: Sub Ans() Set vals = Selection n = Selection.Cells(Selection.Cells.Count) If n = 9 Or n ... | |
| Combining data in multiple rows in excel | 10/9/2008 |
| Q: I need to combine the data in multiple rows into a single row as comma separated values. My data ... A: Are there always 2 rows of this data? Is your example all contained in one cell (514137 4 Good ... | |
| deleting rows | 10/9/2008 |
| Q: I would like to delete rows based on values. I have data comprised of zeros and ones in each row of ... A: Sub DeleteStuff() Ct=0 For i=Activesheet.usedrange.rows.count to 1 step -1 For j=5 to 23 'E ... | |
| Excel | 10/8/2008 |
| Q: Bob, is there a way I can check to see if a number is repeated consecutively 15 times? I used an if ... A: If your data is in Column A, then enter this formula in B1 by ctrl+shift+enter: ... | |
| Duplicating formulas for a number of sheets | 10/6/2008 |
| Q: I have 52 sheets (each week) in identical format (but obviously different values in each). I have a ... A: You need an easy access to all the sheet names. Go to the VBE (Alt/F11), use Inser/Module, enter ... | |
| text file to excel file | 10/6/2008 |
| Q: 1) i have table down loaded from sap.in to excel. but i get '- ' and '|' like this ... A: Why not just replace - with nothing, | with nothing, and to replace * you need ~* and replace THAT ... | |
| deleting rows | 10/6/2008 |
| Q: I would like to delete rows based on values. I have data comprised of zeros and ones in each row of ... A: Sub DeleteStuff() For i=Activesheet.usedrange.rows.count to 1 step -1 For j=5 to 23 'E to W ... | |
| Copying formats using VBA | 10/6/2008 |
| Q: I have created a sheet that uses a VLOOKUP function to produce the desired value in multiple cells; ... A: Right-click the sheet tab, select View Code, use this: Private Sub Worksheet_Change(ByVal Target As ... | |
| EXcel Question (formulas" | 10/5/2008 |
| Q: I trying to create a formula kinda like the EMS training guy that was answered by you before. I am ... A: I don't quite get what you're asking, especially about the "color change or highlight that will ... | |
| search columns and copy them in other sheet | 10/4/2008 |
| Q: I want to search for columns based on their names in sheet and copy them, according to a certain ... A: Not sure I'm understanding you -- sounds like all you want to do is rearrange columns. You can do ... | |
| Excel | 10/3/2008 |
| Q: How do you have a value that is entered to show the time (hh:mm)regardless if the value entered is 3 ... A: 'This first line is how you tell excel you want to run this code whenever the worksheet changes. ... | |
| Excel | 10/3/2008 |
| Q: How do you have a value that is entered to show the time (hh:mm)regardless if the value entered is 3 ... A: If you want to do it to columns a,c,e,g,...: Private Sub Worksheet_Change(ByVal Target As Range) ... | |
| Cell dependency | 10/3/2008 |
| Q: Bob. Excel 2003. I have a form set up with a series of cells each with data validation lists ... A: Have the list of choices somewhere on the worksheet, like Q1:Q4. In C11's data validation, use List, ... | |
| EXCEL MACRO RUN TIME ERROR PROBLEM | 10/2/2008 |
| Q: From the sheet 1, I need to copy the 2nd observation from column B (Nasdaq Ticker for Mutual funds) ... A: One problem is the line Range("=").Select, since that's not a range. I assume that's where Excel ... | |
| SUMPRODUCT | 10/2/2008 |
| Q: I read your tip on how to sum by quarter using the your formula below: ... A: It'd help if you gave me the entire formula which didn't work. However, it sounds like you replaced ... | |
| extract certain text into columns (state abbr. only, etc) | 10/2/2008 |
| Q: I have a column of location names and I need separate into 3 columns: city, state & location. Text ... A: Let's assume your first piece of data is in A2. Enter these formulas: B2: =LEFT(A2,FIND(" ... | |
| SubtotalIF | 10/2/2008 |
| Q: I'm new to this whole Excel Wolrd and i'm learning as needed, but I've got to a point where i'm ... A: Sorry. First, that code should go into here: Alt/F11, then Insert/Module. Next, from any cell, ... | |
| Excel 2003: New Worksheet Auto Update Formula / Add Round Up Equation into Existing Formula | 10/1/2008 |
| Q: Aloha - I have two questions, (rather simple I'm assuming), that I am HOPING you can help me with. ... A: First Q: There is no automatic way. You can simply edit/replace '1' with '2' and you're done -- a ... | |
| Excel | 10/1/2008 |
| Q: How do you have a value that is entered to show the time (hh:mm)regardless if the value entered is 3 ... A: Right-click the sheet tab, select View Code, put this in... This code will do what yuo want for ... | |
| Automated Multi-chart Generation w/ VBA | 10/1/2008 |
| Q: Over the past week I have been learning VBA in order to write a macro which generates one chart for ... A: Are you looking for this:? Sub test() Charts.Add ActiveChart.ChartType = xlColumnClustered ... | |
| "SubtotalIF" | 9/30/2008 |
| Q: I'm new to this whole Excel Wolrd and i'm learning as needed, but I've got to a point where i'm ... A: Instead of COUNTIF, use COUNTIFFiltered, which is a user-defined function: Function ... | |
| Advanced Transpose - Columns to Rows | 9/29/2008 |
| Q: Hello, I am in need of a way to transpose a portion of a set of data from columns to rows keeping ... A: Assuming the word "PORT1", PORT2, etc, is in column B, this macro should work: Sub PORTer() Dim ... | |
| follow up question | 9/29/2008 |
| Q: Bob, This is Kevin Ho the one have the question on sorting table. thanks for the reply here is the ... A: It's the nature of the beast. If you really need for these special ones to sort to the bottom you ... | |
| Pivot Table | 9/27/2008 |
| Q: Bob Umlas How are you. Hope you are fine. if you check attachment file, when I make a pivot table ... A: If your range to pivot is in A1:F30, for example, define a name, Database, to be ... | |
| Finding the duplicates | 9/26/2008 |
| Q: I have a problem in actually find the duplicates in the below example. Is there any formula that I ... A: This LOOKS like it's all in one column. If that's the case, and we're looking at A1:A10, you can ... | |
| Errors! | 9/26/2008 |
| Q: Last week you helped me with a macro that was working, yet now I'm getting errors! In particular, ... A: I added this line in the code: target.offset(0,-1).resize(1,2).ClearContents but that clears the ... | |
| excel VB question | 9/26/2008 |
| Q: I have several pivot tables with several fields that I want to set the default CurrentPage value to ... A: Untested, but you'll get the idea: For Each pt1 In Worksheets("FESumtest").PivotTables For ... | |
| follow up question | 9/26/2008 |
| Q: Bob, This is Kevin Ho the one have the question on sorting table. thanks for the reply here is the ... A: Sub Sorter() For Each chunk In Cells.SpecialCells(xlcelltypeConstants).Areas ... | |
| Errors! | 9/26/2008 |
| Q: Last week you helped me with a macro that was working, yet now I'm getting errors! In particular, ... A: Your code: -------------------------- If Target.Offset(0, -2).Value = 1 Then Target.EntireRow.Delete ... | |
| Transferring Data | 9/25/2008 |
| Q: I asked the following question and received the answer below, however having done as Jan said, when ... A: It's because the value of ocell or 2 columns to the right of ocell should contain text representing ... | |
| Marquee in Cell | 9/25/2008 |
| Q: Sir, I have a text data in Cell C2, I need that data to display in cell A1 and it need to ... A: In order to do this a macro must CONSTANTLY be running, so you wouldn't be able to do ANYTHING else ... | |
| sort multi table in one page | 9/25/2008 |
| Q: I have 5-6 different tables in one worksheet. and I want to sort out the all the tables , I know ... A: Change to: Sub Sorter() For Each chunk In Array("A1:B10", "D1:E20", "F1:G15", "A12:B30") ... | |
| sort multi table in one page | 9/25/2008 |
| Q: I have 5-6 different tables in one worksheet. and I want to sort out the all the tables , I know ... A: Since some of the tables touch (in your example -- D1:E20 touches F1:G15), you can't simply select ... | |
| Using OR statement with a list | 9/24/2008 |
| Q: I need to check a cell's contents (text) against a list of names, and if the cell cotnains a list ... A: Sounds like you neglected to hold ctrl+shift when pressing enter. If you did it correctly, then when ... | |
| Macro | 9/24/2008 |
| Q: I trying to customize a large spreadsheet, which I want the user to be able to hide rows they are ... A: The statement rng.Areas(2).Count is invalid and bombs when it gets there. Try this: Sub Macro1() ... | |
| Microsoft MVP | 9/23/2008 |
| Q: Bob, I wish to become a Microsoft MVP. More specifically I want to learn all I can about Excel and ... A: One becomes an MVP by being nominated by other MVP's. One gets nominated when you're seen on various ... | |
| excel | 9/22/2008 |
| Q: Bob I am a novice with excel but I have to report on construction team performance. I record errors ... A: You can't make a chart from only text and you described trades and codes, so what's graphable? If ... | |
| Macro help | 9/22/2008 |
| Q: I was wondering whether you could help me edit a macro; this was written for me by someone else as I ... A: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range On Error GoTo ErrHandler ... | |
| Variables | 9/22/2008 |
| Q: Good day sir i need your help about the macro i made. I sent it to you in your email pls help me. My ... A: I don't know what you're trying to accomplish -- Your routine "try" goes through each row in Master ... | |
| Macro help | 9/22/2008 |
| Q: I was wondering whether you could help me edit a macro; this was written for me by someone else as I ... A: This should work (untested) if I understand you - changes have '<=== in it: Private Sub ... | |
| functions based on vlookup result | 9/18/2008 |
| Q: I have a large set of records and based on one field - the company code - I am doing a vlookup in ... A: How do YOU distinguish whether it's a calendar or work day? Are the fields in different columns? ... | |
| Cell Space in Excell | 9/18/2008 |
| Q: i want to eliminate the space (24 722.75) between 24 and 722.75, which i think is due to tab command ... A: If it's actually a space, then select the column, use edit/replace, and replace a space with ... | |
| Formula Question | 9/17/2008 |
| Q: When you place a department in B1, it needs to compare to the labels in B2:D2 and if it matches, ... A: This statement has a conflict: "This would return 7N as NA. I need it to show that it is NA, not ... | |
| Capturing Date and Time in excel | 9/15/2008 |
| Q: i want to create another tab in excel wherein i can monitor how often the user opened the file. How ... A: Right-click the Excel LOGO (near the File menu), and select View Code. Paste this in: Private Sub ... | |
| Matching a value in another column | 9/15/2008 |
| Q: How can I compare the values in one column with the values of a 2nd column and return “Yes” or “No” ... A: Sorry, now your rules for Yes/No are not clear to me. Can you explain it in a more simple way? ... | |
| excel-sum | 9/15/2008 |
| Q: I am trying to "sum" a column and all that shows up in the cell is #####. I can't override it, I ... A: It USUALLY indicates the cell is too narrow to display the number, so widen the column ... | |
| Days elapsed | 9/15/2008 |
| Q: What I need to do is have a function which calculates the days elapsed between current date & time ... A: T1:=B1-(R1-INT(R1)) U1:=INT(C1-S1) ... | |
| Find Data Between 2 Dates | 9/14/2008 |
| Q: I have 3 columns in an Excel worksheet A issue Date B Ticket No C Ticket Value I want to get the ... A: Not sure what you mean by "get the Tickets" -- you want them selected? Here's how you can do that: ... | |
| Department Wise Filter | 9/13/2008 |
| Q: Since I was not able to ask you the follow up. I am sending you this new question. The four ... A: Put the 4 departments in 4 cells outside this list, like M1:M4, for example. Put this formula in H2 ... | |
| using vlookup across multiple sheets | 9/13/2008 |
| Q: I have a workbook namely "Test". I want to lookup for the value in cell "G5" of this workbook in an ... A: It works just fine for me in 2003. I entered random numbers from A1:A40000 by using =RAND()*400000 ... | |
| Question on excell | 9/12/2008 |
| Q: I have a series of classifications. Every classification has a series of categories Every category ... A: Suppose your classifications are Class1, Class2, and Class3. Suppose the categories for Class1 are ... | |
| Autofiltering the data for values | 9/12/2008 |
| Q: I have the below code which works to pick up the values of Column N(contains the cost) only for ... A: Not quite clear. If you filtered on Phoenix learning Center, why aren't you also copying the ... | |
| Autofiltering the data for values | 9/12/2008 |
| Q: I have the below code which works to pick up the values of Column N(contains the cost) only for ... A: I'm having a tough time understanding your question. "...Dept code needs to be filtered ... | |
| Date sheet to new sheet | 9/12/2008 |
| Q: I have a main sheet with all of my exercise data ie;Date, Time, Distance etc. where type can be run, ... A: Assuming the data entry sheet is called "Sheet1", then right-click on the Run sheet, select View ... | |
| Search question using Excel | 9/12/2008 |
| Q: My question is I developed a listing of documents we use at work. I have about 200 lines worth of ... A: Assuming the search field is in F1, select all the data, use Format/Conditional Formatting, change ... | |
| Excel | 9/11/2008 |
| Q: First of all, love this service, and thank you for your participation. I have a highly proprietary ... A: Try this: Right-click the sheet tab, select view code, press F4 (to get to the properties window), ... | |
| Excel "Security" | 9/11/2008 |
| Q: First of all, love this service, and thank you for your participation. I have a highly proprietary ... A: You can ask him what he has under tools/options/general/username. Suppose that's CRichardson. You ... | |
| Counting cells with dates | 9/11/2008 |
| Q: I am trying to count the number of times a certain month appears in a column full of dates (e.g. ... A: Change this formula to account for the right # of rows: =SUMPRODUCT(N(MONTH($A$1:$A$100)=ROW(A1))) ... | |
| Autofiltering the data for values | 9/11/2008 |
| Q: I have the below code which works to pick up the values of Column N(contains the cost) only for ... A: First, you listed only 3 facilities, but you can easily append the 4th to my code below: Sub ... | |
| Custom merge-unmerge Button | 9/10/2008 |
| Q: I am hoping that you can help me solve my problem. I am using excel 2000 at this moment and was ... A: Since I no longer have access to XL2000, I don't remember if there IS a merge command (and I ... | |
| Excel Graphs | 9/10/2008 |
| Q: I have two data sets that both have positive values. Is there a way to graph one set above the ... A: not that I know of... you can have a 2nd set of values which reference the first set and the 2nd set ... | |
| comparing rows with unique identifiers | 9/9/2008 |
| Q: I would like a formula that compares a master row (range) of data (a2:ap2) to another child row ... A: Instead of a formula solution, how about this: Select A3:AP100 (last row of data), use ... | |
| Gather info from multiple worksheets into one column | 9/9/2008 |
| Q: I saw this question on your website and think my question is the same, but I don't see the final ... A: Sorry, I don't remember stuff from 10/2004!! The problem is that if you have, say, 200 rows of data ... | |
| Running a macro for multiple workbooks | 9/9/2008 |
| Q: I have 4 workbooks - FC total backlogs.xls, FC1 backlogs 090108.xls,FC2 backlogs 090108.xls and FC3 ... A: You don't need to activate a sheet to refresh a formula. Simply a Calculate will do if you have ... | |
| Excel Macro | 9/8/2008 |
| Q: I have macro1 (below), that hides successive rows within a range of a worksheet. This seems to work ... A: OFFSET(#rows,#columns) so, OFFSET(-2,0) is 2 rows up and so is OFFSET(-2) OFFSET(4) is 4 rows down ... | |
| Add formulas to a row from the row above | 9/8/2008 |
| Q: I am beginner in Excel VBA programming I have a work sheet with the formulas below The first row: ... A: If the data is to go into column A, then this is the part of the code which will fill in the values ... | |
| Formatting through deleteing cells | 9/8/2008 |
| Q: I am creating a program in access however i need to read in a spreadsheet from excel. I have no ... A: Assuming "Person A" is in cell A1, and the dates & data is in A2:F2, then Person B in A3 & data in ... | |
| Manage Lists | 9/7/2008 |
| Q: I routinely need to process a table where 90% of the input data is garbage. A single column contains ... A: I think I need to see the workbook - hard to envision what you really mean -- "keep ABOUT 10% of the ... | |
| Copy and paste value | 9/7/2008 |
| Q: Sorry it is me again. Typing into B3 works perfectly but is there a way that I put a formula into B3 ... A: right-click the sheet tab, select view code, put this in: Private Sub Worksheet_Change(ByVal Target ... | |
| Copy the cell to another cell automatically | 9/5/2008 |
| Q: Bob, Sir, my problem is very simple to explain (I dont know if the solution will be simple). I want ... A: You need a helper column, first row must be blank -- and you can hide this column (best). Let's use ... | |
| Rotate a shape | 9/5/2008 |
| Q: Greetings, I have a shape called "Group 7" and a toggle button on my worksheet. When I click on the ... A: Play with this -- I don't have the time to find what's really needed for this one -- I don't usually ... | |
| Matching a value in another column | 9/5/2008 |
| Q: How can I compare the values in one column with the values of a 2nd column and return “Yes” or “No” ... A: C1:=IF(AND(NOT(ISNA(MATCH(A1,B:B,0))),NOT(ISNA(MATCH(B1,A:A,0)))),"Yes","No") C2 thru C5 (or C100): ... | |
| Storing a cell value | 9/5/2008 |
| Q: I want to record in a column of cells the value of a single cell whenever it changes value. I would ... A: If the cell you're keeping track of is B3, for example, this will store the results in M & N. ... | |
| multiple sheet form | 9/4/2008 |
| Q: I have a form that has Sheet1 with item numbers and description and price on the same row. Sheet1 ... A: That changes things! Try this (untested): A2 would contain the item# B2: ... | |
| watermark an excel spreadsheet | 9/4/2008 |
| Q: I have an excel spreadsheet that is a draft. I would like to be able to have the word draft appear ... A: It's not straightforward. Create the word "Draft" in a spreadsheet using Insert/Picture/WordArt. ... | |
| multiple sheet form | 9/4/2008 |
| Q: I have a form that has Sheet1 with item numbers and description and price on the same row. Sheet1 ... A: Assuming sheet2 has A1:C1 being Item, Description, Price: A2 would contain the item# B2: ... | |
| List of values when using conditional formatting | 9/4/2008 |
| Q: I have a list of values in X1:X10, example: BMW, AUDI, MERCEDES..... When entering in A1 lets say ... A: I'm afraid I don't understand. Send a sample file to me at bobumlas@yahoo.com, use subject of ... | |
| interest rate spreadsheet | 9/4/2008 |
| Q: Even though youre an Excel guy ive read several of your replies to interest questions - is there a ... A: Try this: F1: 12 (or 365, to see daily compounding) F2: 7 (# of years; change accordingly) F3: 4% ... | |
| Excel in Office 2007 | 9/3/2008 |
| Q: Sir, I am trying to highlight a cell (red or Green) based on the current date, a future (departure) ... A: I need more information. But in general, and assuming you're using Excel 2007, if you want to make a ... | |
| Lookup Values | 9/3/2008 |
| Q: I think I need to create an array, but I am stuck...I have 2 worksheets. 1 worksheet has a location ... A: 2 solutions come to mind: Formula based: ... | |
| Need help on doing IF statements for cell copying | 9/3/2008 |
| Q: I would like to have a calculation of the following. I want a cell in the Statistics tab to ... A: Function Defects(Shname As Range, FailBlock As String) 'FailBlock should be "Fail" or "Block" ... | |
| Random Numbers Apearing! | 9/2/2008 |
| Q: I'm hoping you can answer a question that is puzzling me and has done for a while. Its a general ... A: Hard for me to see the screenshot, but I think this is what's happening: 00/01/1900 is the date ... | |
| Running a macro for multiple workbooks | 9/1/2008 |
| Q: I have 4 workbooks - FC total backlogs.xls, FC1 backlogs 090108.xls,FC2 backlogs 090108.xls and FC3 ... A: 1 - it's not a good idea to open files inside a workbook_open event. Change it to Private Sub ... | |
| Need help on doing IF statements for cell copying | 9/1/2008 |
| Q: I would like to have a calculation of the following. I want a cell in the Statistics tab to ... A: First, note that you said column O, but 01_Interface has values in P, and 02_Function Check has ... | |
| Rolling average | 8/25/2008 |
| Q: Bob, I have spent the last three days searching for an answer that I understand, with no luck, ... A: It's not clear why you need to cut & paste, as you can simply put page breaks where needed. The ... | |
| Retrieving Date-Specific Information | 8/25/2008 |
| Q: I'll get right to it, my format in my spreadsheet is as follows: Sheet2 A1 B1 C1 D1 ... A: In your new sheet: A1: =IF(WEEKDAY(TODAY())=2,TODAY()-3,TODAY()-1) A2: =IF(WEEKDAY(A1)=2,A1-3,A1-1) ... | |
| x in cell | 8/25/2008 |
| Q: I am working on a form in excel. I want to format the cells so that when you click the mouse on ... A: Right-click the sheet tab, select View Code, put this in (this example does what you want for cells ... | |
| Excel 2000 - Formula Question | 8/22/2008 |
| Q: I am running an early version of Excel (2000). Evidently, I do not know how to write formulas for a ... A: Not sure why you don't simply put the results in the resulting cells, that is put =B1*E1 in cell F2, ... | |
| Random numbers...with a twist | 8/21/2008 |
| Q: I am using Excel 2003 with WindowsXP. I know how to generate random numbers with RAND and ... A: The formula MUST be in D10 - it needs to be self-referencing or it won't work. I find it hard to see ... | |
| Individual data labels on Excel Chart | 8/21/2008 |
| Q: I am attempting to find an automatic way to give each data point in a series an individual label. ... A: If the chart is in its own sheet (not embedded), and if you have labels in column A, data in B&C, ... | |
| Paradox (or quirk?) with trendline function in Excel | 8/20/2008 |
| Q: I am trying to create a chart (the standard "Line" chart) in Excel from a set of 2 values, namely ... A: I THINK the issue is really not enough data points for a 6th degree to make sense. When you say it's ... | |
| charts ignoring empty cells | 8/20/2008 |
| Q: I have data streaming into excel and i want include the data i have but to also update and include ... A: It'd be better to use defined names and use that name in the Chart's SERIES formula as well. The ... | |
| spreedsheet | 8/19/2008 |
| Q: I need you to separate each entry in to it component parts. For example... VEX60/XN/S 7CE3 DDL Is ... A: If yuor data is in column A and everything else is blank, put my first example in B1, second in C1, ... | |
| Rolling average | 8/19/2008 |
| Q: Bob, I have spent the last three days searching for an answer that I understand, with no luck, ... A: Assuming the net data is in column C, and the word "net" is in C1: ... | |
| Multi-sheet lookup and copy/paste | 8/18/2008 |
| Q: I have a 1,500 row "Control" spreadsheet that I need to match against ~300,000 rows over about 250 ... A: This is untested, but looks like it should work <g>! When done, if there are more than one match, ... | |
| packing list | 8/18/2008 |
| Q: I'm not sure if this is doable or not, but essentially I want to create individual box labels ... A: It's doable, but the answer really depends on the layout of your master list. Do you want to print ... | |
| Counting in Excel | 8/18/2008 |
| Q: In a growing column, I have occasional cell values marked by the number 1, else 0. I need to ... A: The COUNT($A:$A) counts the # of numbers in column A, so if you have 100 numbers, the COUNT returns ... | |
| Multi-sheet lookup and copy/paste | 8/18/2008 |
| Q: I have a 1,500 row "Control" spreadsheet that I need to match against ~300,000 rows over about 250 ... A: Using VBA it's possible, but is it 250 workBOOKS or 250 workSHEETS (in the same workbook)? And if ... | |
| Copy Row w/ Indicator | 8/18/2008 |
| Q: Mr. Umlas - I have a sheet with muliple columns and multiple rows compiling data for unique items ... A: Richt-click the sheet tab, select view code, put this in: Private Sub Worksheet_Change(ByVal Target ... | |
| total in different worksheet | 8/16/2008 |
| Q: I have 12 months of data that I want to compile into a different worksheet...how do I do this? A: There are many possibilities, depending on how your data is laid out. Are you talking about just ... | |
| Macro Help | 8/15/2008 |
| Q: Please help if you can. I have a sheet of data and in one of the columns (B) I have dates and ... A: Sub ClearSomeTimes() Dim i As Integer For i = Range("B65536").End(xlUp).Row To 1 Step -1 ... | |
| Macro for selecting certain data and pasting | 8/15/2008 |
| Q: Could you help me with the following. I have a spreadsheet with a certain amount of columns. When i ... A: You didn't specify which column is to contain the "no" (so I'll assume column F for this example), ... | |
| Check for existing data | 8/14/2008 |
| Q: In excel, I created a form which will allow the user to input data. Once the data has been typed to ... A: my guess is it's crashing on Set c = .Find(dataInput, LookIn:=xlValues) because it may not find what ... | |
| Excel | 8/14/2008 |
| Q: in sheet "Objects" there is a compleat list of all objects in the drawers of the office. I want to ... A: Sounds like a perfect use of the Data/Filter. Click anywhere in your list of objects, use ... | |
| Copying information from a master into multiple worksheets | 8/13/2008 |
| Q: I have several instances where I have a master list and I want this information copied into other ... A: All the other sheets would have to have formulas referencing the master sheet, and without seeing ... | |
| Excel- hidden empty rows | 8/13/2008 |
| Q: I am trying to present the Profit and Loss statement for share trading activites. Date Table A ... A: I forgot one thing -- the labels must be X, Y, and Z in B1:D1 or this macro would need to be ... | |
| Excel | 8/12/2008 |
| Q: Workbook1, sheet1 cell A1 is going to change every week by manual entry of a number. I want sheet2 ... A: Right-click Sheet1 tab, select View Code, paste this in: Private Sub Worksheet_Change(ByVal Target ... | |
| Consolidation of Comments | 8/12/2008 |
| Q: I have asked this question before, but there is one issue for which I am not finding a solution. I ... A: CHANGE THE WORKBOOK NAMES AS APPROPRIATE; Have the 8th be active Sub GatherComments() Dim wb, cmt As ... | |
| return records without auto-filter | 8/12/2008 |
| Q: I would like to create separate lists detailing each individuals sales from a database of all sales. ... A: The easiest way (non-formula) would be to use Data/Filter, and select David of John or Larry. You ... | |
| 8/11/2008 | |
| Q: Sub PrintIf() If Range("J21")>0 Then ActiveSheet.PrintOut End If End Sub I not very familiar with ... A: right-click the sheet tab, select View Code, paste this in: Private Sub Worksheet_Change(ByVal ... | |
| coverting an address list in excel. | 8/11/2008 |
| Q: I want to convert an address list that I copied from a word doc to a excel sheet so that each ... A: If you follow these steps EXACTLY, it'll do what you want. In B1 enter "A1" (no quotes, and no "=", ... | |
| Automatically generate new worksheet | 8/11/2008 |
| Q: I hope you can help. I have a simple question but feel the answer will not be. All I want to do is ... A: Private Sub Worksheet_Change(ByVal Target As Range) Sheets("EPF").Copy Before:=Sheets(Sheets.Count) ... | |
| Automatically generate new worksheet | 8/11/2008 |
| Q: I hope you can help. I have a simple question but feel the answer will not be. All I want to do is ... A: I don't know what you mean by an "event form". To duplicate a worksheet, use Sheets("Whatever").Copy ... | |
| Conditional Drop-Down Lists In Excel | 8/11/2008 |
| Q: I have set up a conditional drop-down list in Excel using the method of =INDIRECT(SUBSTITUTE($I$9," ... A: Similar to what you already did for the first level of INDIRECTion. You need a list of defined names ... | |
| decimal points | 8/10/2008 |
| Q: I'm looking for a formula in excel such that if a product is "5" it will format as "5.0" -- problem ... A: What I gave you was a number format -- Use Format/Cells, click the Number tab, then click the ... | |
| decimal points | 8/9/2008 |
| Q: I'm looking for a formula in excel such that if a product is "5" it will format as "5.0" -- problem ... A: Without resorting to a macro, I think this is the best you're going to do: [>=1]0.0;General This ... | |
| now() function | 8/9/2008 |
| Q: When I put the now() function in a cell, the seconds don't advance. Is there a way to make the ... A: Only by running a macro, which, while running, will severely hamper your doing anything else. The ... | |
| Automatically generate new worksheet | 8/8/2008 |
| Q: I hope you can help. I have a simple question but feel the answer will not be. All I want to do is ... A: Let's say the cell with the Y/N is B4 and the company name is in F4. Right-click the sheet tab, ... | |
| excel | 8/8/2008 |
| Q: I am trying to combine an and & an or in a nesting function and having difficulty. here is an ... A: Even in English, it's not easy to determine what yuo mean - there are 2 interpretations: if x=1 or ... | |
| Excel- hidden empty rows | 8/8/2008 |
| Q: I am trying to present the Profit and Loss statement for share trading activites. Date Table A ... A: If you follow these steps exactly, it'll do what you want. Assuming your data is on Sheet1 from ... | |
| Consolidation Follow Up | 8/7/2008 |
| Q: Hey Bob, I had to start a new question as I could no longer follow up on the old one. The last ... A: Sub unprotectWks() Dim FSO As Object, Folder As Object, file As Object Dim wkbSample As Workbook Dim ... | |
| Macro Question | 8/7/2008 |
| Q: I am trying to write a macro that at the load of the spreadsheet, it will automatically color every ... A: No macro necessary. Select A7:M3000, use Format/Conditional Formatting, change "cell value is" to ... | |
| Merging Comments in excel.. | 8/7/2008 |
| Q: I want to merge comments inserted in multiple cells into one cell. I have got the following macro ... A: Sub Comments() Dim Combined As String For i = 1 To ActiveSheet.UsedRange.Rows.Count ... | |
| Duplicates | 8/6/2008 |
| Q: We have a file of 62k rows of raw claims information. The files contain multiple rows of claims per ... A: Do you want to ignore the value in the 2nd (& beyond) occurrences or accumulate the values? If ... | |
| Consolidating worksheets from multiple workbooks | 8/6/2008 |
| Q: Stuart assisted me with an earlier question regarding the unprotecting of worksheets within multiple ... A: Try this (untested): Sub unprotectWks() Dim FSO As Object, Folder As Object, file As Object Dim ... | |
| Merging Comments in excel.. | 8/6/2008 |
| Q: I want to merge comments inserted in multiple cells into one cell. I have got the following macro ... A: This is untested, but should work. Sub Comments() On Error Resume Next For i=1 to ... | |
| Consolidating worksheets from multiple workbooks | 8/5/2008 |
| Q: Stuart assisted me with an earlier question regarding the unprotecting of worksheets within multiple ... A: Sub unprotectWks() Dim FSO As Object, Folder As Object, file As Object Dim wkbSample As Workbook ... | |
| multiple values in vlookup | 8/5/2008 |
| Q: Bob, First off thank you for the time you put into this sight, it helps a lot of people. Anyways, ... A: Still not 100% clear -- if you are looking for the 3rd column if you know the 1st 2 (Jim ... | |
| excel creating temp files | 8/4/2008 |
| Q: I am using excel 2003 on a network, and every time users try to save the document, a temp file is ... A: When you do save an Excel file, it does so by saving it with a strange temp name (like FAEE8000) ... | |
| EXCEL | 7/13/2008 |
| Q: 1- What is absolute value ? 2- How to link cells together? 3- What function Key LOOK UP? A: 1) the value of a number regardless of the sign. So absolute value of 4 is 4, absolute value of -4 ... | |
| Count of AutoFilter Results | 7/12/2008 |
| Q: I have a worksheet containing multiple columns of data. With the AutoFilter function active and ... A: If you placed the EXACT formula in A1 you might have run across a circular reference, since that ... | |
| Copying all values with Vlookup function | 7/11/2008 |
| Q: I am using Vlookup fun to check the data in other xl file,But it copies only on column where we have ... A: VLOOKUP returns 1 value. If you want more than 1, you need more than 1 VLOOKUP function. For ... | |
| help in excel | 7/10/2008 |
| Q: Please help me, I already sent 1 question, it was supposed to be set up wksht that will print on 1 ... A: 2 - I don't understand what you mean by "which chart" - you select the range or one cell in the ... | |
| TREND Vs Forecast | 7/10/2008 |
| Q: I would like to ask you some question about this two statistic formula in Excel. i want to predict ... A: They should produce the same result. Put some random numbers in A1:A7, then in B1 enter ... | |
| Using trendline equations in cells | 7/10/2008 |
| Q: Bob, I know you have answer such as this before; i wanting to use the trendline equations ... A: The coefficients from the LINEST function can create up to a 16th-degree best fit curve! For 3rd ... | |
| macro | 7/9/2008 |
| Q: The following macro works great to suppress empty columns... but my sheet includes the first row ... A: Function AllZero(R As Range) As Boolean ' This function returns TRUE if the range is entirely ... | |
| Allowing 'overwriting' in drop down (validated) lists | 7/9/2008 |
| Q: I've created a reasonably comprehensive workbook in Excel combining a raft of forms all pertinent to ... A: If you know the values of the 2nd list, you can have the data validation refer to either of 2 (or ... | |
| excel timestamp | 7/9/2008 |
| Q: I want to time stamp columns A & B, everytime columns C-G are updated in that row. I only want to ... A: Sorry - didn't notice the stamp in col B. Try this: If ... | |
| Hiding rows if condition in another cell is met | 7/9/2008 |
| Q: I have a worksheet in which I am trying to hide certain rows if a condition in another cell is met. ... A: 1) rows can't hide themselves because of a value in another cell, so you MUST use a macro. ... | |
| Auto inserting a picture into specific cell | 7/9/2008 |
| Q: s/sheet is layed out with various bits of data in rows 2 to 12 and an empty cell in row 1 (repeated ... A: Sounds like you need a macro -- have you tried recording your manual steps? It would need some ... | |
| Data validation Warning Messages | 7/8/2008 |
| Q: I was wondering if it were possible to have 2 different warning messages apply to the same single ... A: Not by data validation, but you can do it with VBA code. Assuming the cell is E1, for example, you ... | |
| Change colour of cell based on date | 7/8/2008 |
| Q: I do volunteer work and keep records of qualifications of other volunteers. I need a quick way of ... A: Does "inside 3 months" mean a date between now and 3 months from now or between now and 3 months ... | |
| Comparing/Merging Data in Columns | 7/7/2008 |
| Q: I have two columns of data. Column A has a list of names. Column B has a list of aliases. Each ... A: You need VBA code -- press Alt/F11, use insert/Module, then put this in: Sub Merger() mergedrow ... | |
| Excel formula | 7/7/2008 |
| Q: I am trying to create a formula for excel 2003 that will recognize a value 2:47 as 2 minutes and 47 ... A: If 2:47 is in A1, then this formula will make it seconds: =A1*1440 but you'll have to format that ... | |
| Locking cells automatically | 7/7/2008 |
| Q: I have prepared a time sheet in excel and put in share folder. I have instructed my colleague to ... A: Assuming the workbook is already protected (that's the only way to lock a cell), this should work: ... | |
| Excel | 7/7/2008 |
| Q: Is there any formula wherein I can detect duplicate entries in a column. For example in Column A I ... A: It's not clear to me the relationship between columns A & B, but in an unused column, you can enter ... | |
| Print Area | 7/6/2008 |
| Q: Bob Umlas I want know how I can print maximum area in Excel? Because I’m trying to print one form ... A: Are the settings in Page Setup all set as you want (including the bottom)? In any case, most ... | |
| Excel Macro - Extract text from Autoshape | 7/6/2008 |
| Q: I'm tring to create a macro that will extract the text from a shape and paste it the adjacent column ... A: I don't see how your macro could work -- there's no "Copy" statement, so what is it trying to paste? ... | |
| Formulas | 7/5/2008 |
| Q: I am after a formula to use to read a cell with a salesperson name which can differ and then copy an ... A: I need more information -- how is this laid out? Where is the running total for a particular date? I ... | |
| linking data | 7/4/2008 |
| Q: Good day sir! Thank you for reading this inquiry. I have a problem with my excel doc. I have 2 ... A: I need more details. Which sheet did you enter in the + sign? What cell? What cell in the other ... | |
| removing number from alphanumric | 7/3/2008 |
| Q: I have a sheet in A1 there are ALPHANUMRIC ABOUT 1500 for example HUPU154UYP1258HYE128, ETC. how can ... A: You need a user-defined function to do this. Here's the function: Function AlphaNum(Rg, Which) Dim ... | |
| Conditional Formatting | 7/3/2008 |
| Q: I work alot with excel in my line of work, but one particular problem has plagued me, and I have ... A: Change "Cell Value Is" to "Formula Is" in the conditional formatting dialog. Then, for example, if ... | |
| removing characters in a cell | 7/3/2008 |
| Q: I have this problem with excel regarding a particular cell value wherein it has an alphanumeric ... A: Let's use the IMAG6114356 again in cell A1 and take apart the formula: ... | |
| duplicate entry prevention | 7/3/2008 |
| Q: I am facing a slightly different case of preventing duplicate entry in excel sheets. I created a ... A: Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Dim n As Integer ... | |
| pulling specific things out of a spreadsheet | 7/2/2008 |
| Q: I have data on a particular stock index. I'm trying to pull specific rows of data into a second ... A: Put the dates in a range and name the range "Rg", then you can run this: Sub ThirdFriday() Dim n As ... | |
| Run Macros in a loop | 7/2/2008 |
| Q: Bob, I have below macro to split one cell content into 3. The Problem is i have to go to the ... A: Sub ABC() Dim s As String, s1 As String Dim s2 As String, s3 As String Dim iloc As Long, iloc1 As ... | |
| removing characters in a cell | 7/2/2008 |
| Q: I have this problem with excel regarding a particular cell value wherein it has an alphanumeric ... A: If that value is in A1, for example, enter this formula WITH CTRL+SHIFT+ENTER: ... | |
| duplicate entry prevention | 7/1/2008 |
| Q: I am facing a slightly different case of preventing duplicate entry in excel sheets. I created a ... A: Unfortunately, data validation gets hosed when you paste into it, as you discovered. You can try ... | |
| pulling specific things out of a spreadsheet | 6/30/2008 |
| Q: I have data on a particular stock index. I'm trying to pull specific rows of data into a second ... A: Sub ThirdFriday() For i = 2 To Range("A65536").End(xlUp).Row If ... | |
| Split one cell content into 3 cells in excel using Macros | 6/30/2008 |
| Q: Actually, i raised the same as a new question since AllExperts doesn't allow me to ask follow up ... A: Function Splitter(Rg As Range, Part As Integer) On Error GoTo NG Select Case Part ... | |
| Printing with Excel | 6/29/2008 |
| Q: At the current time I have a workbook that contains 6 sheets. The first sheet is the "form order" ... A: Did you actually follow the first step: Right-click the excel LOGO (near the file menu), select View ... | |
| Add formulas to a row from the row above | 6/29/2008 |
| Q: I am beginner in Excel VBA programming I have a work sheet with the formulas below The first row: ... A: If you want this formula: =VLOOKUP(A4,DRecord!A3:M10,2,FALSE) to become this formula as you fill ... | |
| .linking | 6/27/2008 |
| Q: I want to link column A in worksheet 1 to column A in Worksheet 2. I want to link column B in ... A: you don't link a column to another column, you link a cell to a cell. In A1 you can write =Sheet2!A1 ... | |
| Split one cell content into 3 cells in excel | 6/27/2008 |
| Q: Hope you are doing fine. I need your help in solving the below scenario. I have a column called ... A: If the first sample is in cell A1, then put these formula in B1:D1--- B1: ... | |
| Print macro | 6/26/2008 |
| Q: I am attempting (newbie)to write a print macro that will print selected sheets based on the values ... A: I think the issue is with all the statements you have in which yuo use both OR and AND. It works ... | |
| Delete "name' | 6/26/2008 |
| Q: I have inherited a file that contains more than 75 defined "names'. Is there a easy way to delete ... A: First, names are MUCH better than cell references, so deleting them is really counter-productive. ... | |
| error in active chart - why? | 6/26/2008 |
| Q: in sheet2 i have some cells which are update dynamically, and in sheet3 i have a chart which is ... A: Since you didn't show me the entire macro (or where you put it--normal module or event code module), ... | |
| Add formulas to a row from the row above | 6/26/2008 |
| Q: I am beginner in Excel VBA programming I have a work sheet with the formulas below The first row: ... A: It's not clear what you GOT vs what you WANT to get. Please try to explain more clearly, using exact ... | |
| Database help | 6/25/2008 |
| Q: I am setting up a database that will split up information from a 'Master' worksheet, by using ... A: My first guess would be that you'd have a much easier time if you used pivot tables, since you can ... | |
| Populating cells in multiple sheets based on text in a different tab. | 6/25/2008 |
| Q: I have a list of data in a tab entitled "MAIN". In "MAIN" Col B will contain either ... A: I'll use ACC as an example, but you need to follow these instructions very precisely. in Acc, cell ... | |
| Printing with Excel | 6/25/2008 |
| Q: At the current time I have a workbook that contains 6 sheets. The first sheet is the "form order" ... A: Right-click the excel LOGO (near the file menu), select View Code, put this in: Private Sub ... | |
| Getpivotdata grouped rows | 6/24/2008 |
| Q: I am trying to use the Getpivotdata on a table in which the rows are grouped by Year and further by ... A: This is from Help and should help(!): {Pictures don't come across, so if you look at Help, you'll be ... | |
| Graph | 6/24/2008 |
| Q: I am facing a problem with basic charts. I have two columns of data say Week and Data. Say I only ... A: You can't put a null value in a formula - best you can do is a 0-length string. Instead, try NA() ... | |
| Retrieve an exact value from a table using two seperate search criteria | 6/23/2008 |
| Q: I have two separate tables. Table 1 has dates in column A & FX symbols in column B. Table 2 has FX ... A: Assuming you want to use A2 and B2 from table 1 to find the entry in table 2, and assuming you want ... | |
| Changes to Excel 2007 | 6/22/2008 |
| Q: Bob, I realize this one may be a stickler, but I'm hoping to find at least a trail of crumbs to ... A: By Dialog boxes are you referring to Excel 4-type dialog boxes (where the vba would be ... | |
| Excel data manipulation | 6/21/2008 |
| Q: I have an excel spreadsheet that lists columns of information, but not in a useful format to sort ... A: feel free to send a sample workbook to me at bobumlas@yahoo.com - use subject of AllExpertsQ and ... | |
| duplicate rank formula | 6/19/2008 |
| Q: if i have to give the rank to the data, i get the following result. Marks Rank 55 ... A: If your example is in A1:B5 (A1="Marks") then do this: Move B1:B5 off to the side, say in F1:F5 ... | |
| Real Time Stock Quotes | 6/18/2008 |
| Q: I have streaming quotes in a cell(continuously updating). I wish to capture price every 15 minutes ... A: Sub Auto_Open() Application.OnTime Date + TimeValue("9:45:00 AM"), "every15" End Sub Sub ... | |
| Real Time Stock Quotes | 6/18/2008 |
| Q: I have streaming quotes in a cell(continuously updating). I wish to capture price every 15 minutes ... A: Sub every15() If LCase(Sheet1.Range("A1").Value) = "end" Then Exit Sub Application.OnTime ... | |
| Excel Vlookup help | 6/18/2008 |
| Q: I use this often but it seems on this particular report it never works. Instead of giving me an ... A: The formula is fine. The cell is formatted as TEXT. Select the cell, press ctrl/shift/~ (or ... | |
| VLOOKUP, IF functions, SWITCH function | 6/18/2008 |
| Q: Hey Bob, First off, thank you so much for this website and for taking the time to answer questions. ... A: If the dropdown box (I'm assuming from data validation, not from a real combobox) is in cell E2, for ... | |
| vlookup formula | 6/18/2008 |
| Q: I'm having a problem with a vlookup formula. my results are inconsistent and are not updating when I ... A: If today's date is in cell Mar30toJune28!E12, for example, then the MATCH will return 5. However, ... | |
| Question about filtering data in MS Excel | 6/17/2008 |
| Q: Expert, I received an excel document (I am guessing it was created from a portfolio management ... A: Sounds like you're describing outline symbols, but I've never seen them on the right side of the ... | |
| Data validation from other cell values | 6/17/2008 |
| Q: In Excel 2003, how do i create a drop down list based off of values in another cell 2003? I know ... A: I don't think I follow. A1 has exactly: email,letter,Jack all in A1? With the 2 commas? and the data ... | |
| Sum alternate column values. | 6/17/2008 |
| Q: Sir I have data for about 100 offices. Each Office one Column contains Receipts and another ... A: You don't need VBA code to do it. If it goes to column GR, then this formula will add up all the odd ... | |
| Date function in Excel does not recognize 31st of the month | 6/16/2008 |
| Q: I'm trying to use a date function in excel to calculate the number of out-of-town days using the ... A: The definition (from Help) says: Returns the number of days between two dates based on a 360-day ... | |
| calculating age | 6/15/2008 |
| Q: i have 20000 birthdates and i want to calculate the age of the persons. Now i am using excel 2007 ... A: looks like A1 or B1 is text, not a number. And A1-B1/365.25 should probably be (A1-B1)/365.25. But ... | |
| Formula not working | 6/14/2008 |
| Q: I am using the formula which you told me =IF(Z80=$A$1,$B$3,AA80), I am getting data ... A: Are you sure you have calculation set to automatic instead of manual? I don't remember the original ... | |
| Obtaining data from multiple sheets. | 6/12/2008 |
| Q: I have 2 excel sheets with about 50,000 rows,and 9 columns. The main column (1) contains the model # ... A: your formula, =VLOOKUP(A11060,Sheet1!M1,N1,FALSE) is looking up a value in ONE CELL (M1). That won't ... | |
| List range size | 6/12/2008 |
| Q: I have an app that is written for 2003 Pro and it imports and exports XML. I have xml list import ... A: unfortunately, I have no experience working with XML files, so I don't really have an idea as to ... | |
| Obtaining data from multiple sheets. | 6/11/2008 |
| Q: I have 2 excel sheets with about 50,000 rows,and 9 columns. The main column (1) contains the model # ... A: I'm not clear if you want a formula to pick up the information or a way to find the information. To ... | |
| Inserting Automatically Date Created & Date Modified into Excel Spreadsheet | 6/11/2008 |
| Q: I would like to automatically insert Date Created & Date Modified into Excel Spreadsheet (Date ... A: right-click the excel LOGO near the file menu, insert this code: Private Sub Workbook_Open() ... | |
| Charting in Excel | 6/11/2008 |
| Q: I was wondering whether it is possible to capture an Excel chart's properties and use these to ... A: Maybe a simple copy/paste formats would do... Something like this: (assumes 2 charts on the same ... | |
| Productivity time question | 6/9/2008 |
| Q: I am working on a productivty sheet to track time spend doing on task or the other. i would like my ... A: If the cell is question (where you're entering the 37 or the 1:45) is A1, for example, you can ... | |
| Transferring data series from individual worksheets to a master worksheet | 6/9/2008 |
| Q: I am currently attempting to develop a macro that will copy data series from individual worksheets ... A: this routine will copy all sheets from row 2 to end to MASTERSHEET beginning in row 5: Sub ... | |
| problem with IF statement? | 6/7/2008 |
| Q: I do not know much about macro writing. This challenge was the first for me, mostly based on the ... A: One thing you might want to look at is the beginning of the macro: Sub DataCopy() If ... | |
| Transferring data series from individual worksheets to a master worksheet | 6/6/2008 |
| Q: I am currently attempting to develop a macro that will copy data series from individual worksheets ... A: Sheets(1).Range("A1").CurrentRegion will reference all the cells with data assuming there are no ... | |
| Count in a cell, not an array | 6/6/2008 |
| Q: I have a cell that its value is changing all the time. i.e., each 10 seconds, mark TRUE. I want to ... A: Assuming, for this example, the cell you want to check is D3, and the cell to contain the count is ... | |
| If-then question I think | 6/5/2008 |
| Q: I want to produce a progressive series of numbers in a row or column. Starting at 0 and ... A: Need more info -- is it only A1 & A2 which determine your series? That is, A3 & beyond doesn't enter ... | |
| Count by date | 6/5/2008 |
| Q: I have a list of about 200 dates in mm/dd/yyyy format. I would like to set up the spreadsheet so ... A: If your dates are in A1:A200, for example: Jan 2007: ... | |
| Pivot Tables | 6/4/2008 |
| Q: I have a list downloaded weekly into a worksheet, from this list I have created 5 Pivot Tables (on ... A: I don't know of a good way to do this without first moving each pivot table's row field into the ... | |
| HR | 6/3/2008 |
| Q: I need help please I have 3 columns Position Name Hourly Rate Total Hours IF the ... A: Assuming C2 contains the total hours-- Not sure if you're asking to compute the total pay given your ... | |
| Lookup Table help | 6/3/2008 |
| Q: I am trying to match a number of a car that is in a column of numbers on the first sheet with a car ... A: First, the syntax in your formula, cars2!"Tom" is invalid. Either Cars2!B1 (for example) or "Tom". ... | |
| format cell (currency) based on selected drop down list | 6/2/2008 |
| Q: I need to know if there is a way to change the format of various cells based on the value of another ... A: If the name for all the cells is "Gail", for example, you can do this -- also assumes the dropdown ... | |
| hyperlinks | 5/30/2008 |
| Q: This is a MS Excel (Office Professional 2003) question. I'm very proficient with formatting and ... A: Don't think you can do this with hyperlinks as they're not dynamic. But you can do it with VBA. ... | |
| Pivot Tables | 5/29/2008 |
| Q: I have a list downloaded weekly into a worksheet, from this list I have created 5 Pivot Tables (on ... A: not by linking, but by some vba code to do the same thing. For example, if the page field is in cell ... | |
| Print preview with optional sheet printing | 5/29/2008 |
| Q: I have a workbook which contains lots of sheets - e.g. UK, USA, NZ, AUS, etc. I need the user to be ... A: Lots if possibilities exist. One is to have a userform with a multi-select list box from which they ... | |
| concatenate/if formula | 5/29/2008 |
| Q: I am trying to take current date then have a formula refer to another sheet and cell reference, then ... A: I don't really understand what you want to do -- the formula you supplied is invalid and can't work. ... | |
| moving to right cell depending on amount of results | 5/28/2008 |
| Q: i get two results from two searches, A & B. i must put them one below other on sheet, so that there ... A: It's help if you could give an example with exact cell references of where you're looking and exact ... | |
| Checkboxes | 5/27/2008 |
| Q: Currently i have a few checkboxes(they are forms) on a single worksheet. I would like to have a ... A: Sub HTH() WhocalledMe = Activesheet.Checkboxes(Application.Caller).Name if ... | |
| Excel formula... | 5/27/2008 |
| Q: I have a large list (potentially 60,000+ records). The recs are sorted according to US STATES and I ... A: You can do it with a formula in col F, but you need some defined names first. Assuming your first ... | |
| Checkboxes | 5/27/2008 |
| Q: Currently i have a few checkboxes(they are forms) on a single worksheet. I would like to have a ... A: Your macro needs to know which checkbox was the one clicked. Try something like this: Sub HTH() ... | |
| copy loop | 5/26/2008 |
| Q: Heya! I have a problem. I have textdata in A5-A8 and B5-B8 and I need to do a macro that first it ... A: See my comments inside the code -- mostly, you're pasting the last item on top of the other ones... ... | |
| excel vba | 5/25/2008 |
| Q: Sheet1 is an input doc with a date (A1) and say six rows of numbers. (C1:C6) Sheet2 stores that ... A: Sub Xfer() Dim n As Integer On Error Resume Next n = 0 n = ... | |
| TEXT formula needed for separating Full Name | 5/22/2008 |
| Q: I've got a spreadsheet column that has full names. Some names have middle initials and some don't. I ... A: You got the first pat right: =LEFT(A1,FIND(" ",A1)-1) 2nd part: =MID(A1,FIND("}",SUBSTITUTE(A1," ... | |
| Convert text into number | 5/21/2008 |
| Q: i want to convert number into text in excel, for examole A1 10 A2 show with help of function A2=Ten ... A: Allexperts.com is NOT for people asking the experts to DO their work for them, it's to help with ... | |
| Make a Software for Store Ledger Card | 5/21/2008 |
| Q: i want to convert number into text in excel, for examole A1 10 A2 show with help of function A2=Ten ... A: Maybe I'm getting dumb in my old age, but I have no idea what you're asking me to do. "Make software ... | |
| excel formulas | 5/20/2008 |
| Q: i am using office 2003. In a workbook, I have two worksheets. I use information from worksheet one ... A: Does the cell ACTUALLY CONTAIN "30minutes"? or "6:00am" and "3:30pm"? or does it contain 6:00 AM and ... | |
| How do I plot named variables in Excel 2003? | 5/19/2008 |
| Q: I have defined several variables that dynamically change size. Is it possible to make an XY-plot ... A: In the SERIES formula of the chart (click on the series), change something like: ... | |
| excel formulas | 5/19/2008 |
| Q: i am using office 2003. In a workbook, I have two worksheets. I use information from worksheet one ... A: In cell D1, in sheet 2, use this formula: =IF(AND(Sheet1!A1>=7,Sheet1!A1<=8),"SUMMER", ... | |
| Subject: Expire a Spreadsheet | 5/18/2008 |
| Q: Bob, I used: Private Sub Workbook_Open() If GetSetting("open", "open", "Count", 0) * 1 = 5 Then ... A: Make sure the sub is in the Workbook events, not a regular module. You can make it work in a regular ... | |
| Excel blinking cell date expired | 5/16/2008 |
| Q: I am using Microsoft Office Excel 2003 on Windows Professional XP. I would like to have my formula ... A: Flashing can only be done by an always-running macro, which severely limits what you can do. And ... | |
| Page numbering in Excel 2003 | 5/16/2008 |
| Q: I have created an action plan in excel with each tab as it's own stand-alone section (by doing this ... A: File/page Setup -- on the Page tab, there's a checkbox for first page #. Change that to 1 on each ... | |
| Removing a hyperlink | 5/15/2008 |
| Q: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim strHyp1 As String Dim ... A: "the number being indexed..." -- what's that? Also, the line xlSheet.Hyperlinks.Add ... | |
| MAX forumla help | 5/14/2008 |
| Q: Hey Bob, you've been extremely helpful for me before and I need some help w/ a MAX type situation in ... A: =INDEX(A:A,MATCH(MAX(B:B),B:B,0)) will return Jason. A tie would be more difficult, but possible, ... | |
| Moving data across tabs | 5/13/2008 |
| Q: I have a spreadsheet with three tabs. One tab shows inventory coming from a certain source, and ... A: This should be the code for Sheet1, not sheet2. My mistake re Col F -- I coded it for Col E. the ... | |
| Show/Hide Button | 5/12/2008 |
| Q: I have a spreadsheet with tons of columns and I would like to add several buttons that will expose ... A: You need VBA to have a callout show when you're in the column and disappear when you're not in that ... | |
| Page Total and Grand Total in MS-Excel Worksheet | 5/12/2008 |
| Q: "Sir, I have an MS-Excel worksheet containing 10,000 records with 6 columns. Sl. No., Name, ... A: Page totals are not a feature of Excel, since it's not at all an easy task to determine where the ... | |
| Moving data across tabs | 5/11/2008 |
| Q: I have a spreadsheet with three tabs. One tab shows inventory coming from a certain source, and ... A: Yuo need a macro to do this, but you want it to copy & sort only when you say so, not when you type ... | |
| Assign macro to hyperlink | 5/10/2008 |
| Q: Is there a way to create, say, 3 different hyperlinks, where each one runs a different macro on a ... A: Use the Selection_Change event: Private Sub Worksheet_SelectionChange(ByVal Target As Range) ... | |
| Custom Filter | 5/8/2008 |
| Q: I tried to use the data filter and do a custom search for text within a cell - only problem with ... A: You can do this with the Advanced Filter. Put those texts into a list, like E1:E6 (E1 has - ICT, E6 ... | |
| Y-Axis Values on Charts | 5/8/2008 |
| Q: I have some charts with an average of 4 series in which the y-axis is seconds/minutes, ranging from ... A: the y-axis can't do this - the spacing is either consistent or logarithmic. I'd suggest 2 charts - ... | |
| Reprogram Excel 2007 Menu System | 5/6/2008 |
| Q: The new Excel 2007 Menu is hidious and has none of the features I traditionaly use Icons for. I ... A: Sorry -- it's the cost of progress. Give it time -- you'll not only get used to it, you'll like it ... | |
| Excel VBA procedure | 5/5/2008 |
| Q: I want to write a procedure that uses an IF statement to look for specific text in a field (lets ... A: Sub Ted() Dim FirstTime As Boolean FirstTime = True Set Current = ActiveSheet SpecificText = ... | |
| Excel VBA procedure | 5/2/2008 |
| Q: I want to write a procedure that uses an IF statement to look for specific text in a field (lets ... A: Sub Ted() Dim FirstTime As Boolean FirstTime = True Set Current = ActiveSheet SpecificText = ... | |
| Excel VBA procedure | 5/1/2008 |
| Q: I want to write a procedure that uses an IF statement to look for specific text in a field (lets ... A: Sub Ted() 'untested, but should work Set Current = ActiveSheet SpecificText = Inputbox("Enter ... | |
| setting up receivers of mail | 5/1/2008 |
| Q: I have my spreadsheet being automatically sent to a designated person once a button is pushed on the ... A: Dim IntendedFileName As String Set myOLApp = CreateObject("Outlook.Application") ... | |
| GoTo range | 4/29/2008 |
| Q: This is simple question, but I've looked for the answer for 1/2 hour now and can't seem to say the ... A: Define the name with the sheet name appended to it. That is, instead of using the name "Billing" as ... | |
| Excel Question | 4/28/2008 |
| Q: I have two columns below for now but it can be more colums. The data will be different every time. ... A: I don't understand. if A1 "has a zero", how is that supposed to copy 2 form B1 or do anything, for ... | |
| Using Individual Sheets and importing to a master list. | 4/28/2008 |
| Q: I'm trying to set up a call-log with a master sheet of each individual call, and individual sheets ... A: This requires the master sheet to be named MASTER: Right-click the Excel LOGO (Near the file menu), ... | |
| Page breaks on multiple worksheets | 4/28/2008 |
| Q: .. I have a workbook with 26 worksheets that are each 10 pages long. I want to select all sheets and ... A: Right-click a sheet tab, select "Select All Sheets", then do your work. You'll be able to do ... | |
| Excel | 4/26/2008 |
| Q: I am new to excel and i had a requirement in excel, we have 2 sheets, in one sheet i am going to ... A: Nothing really happens automatically in Excel -- you must either write VBA code (not a good idea to ... | |
| looking up account names, then pulling related value | 4/25/2008 |
| Q: there! I have an Excel question that I'm hoping you can help me with. I have an Excel file that ... A: If the first info in Sheet1 is in row 1, enter this formula in cell B1, but you MUST enter it via ... | |
| creating a table of contents using VBA | 4/25/2008 |
| Q: Good Morning, I have a question regarding creating a table of contents in excel. I need to create ... A: As long as the sheet name EXACTLY matches the cell in the table of contents sheet this will work. ... | |
| Formula question | 4/24/2008 |
| Q: I have a spreadsheet that I gather various information in text format. I need a formula that will ... A: SUMPRODUCT requires the list of arrays to be numeric. B1:B100="Kalamazoo" would produce an array of ... | |
| Creating a line-column chart on two axes / mixed chart | 4/24/2008 |
| Q: This is a great opportunity to get some real answers. So many don't know where to turn. Anyhow, I ... A: I don't know how to do charts in Powerpoint (and certainly not in "powerment"!) So this is purely an ... | |
| Follow up question part 2 | 4/24/2008 |
| Q: Good day sir. Here's the scenario: ANSWER: Might the data look like this? Ritchie 11546 ... A: Sorry - I just don't get it -- the best would be for you to send me a workbook with 2 sheets: Sheet1 ... | |
| Excel 2002 - formatting print | 4/23/2008 |
| Q: I am constructing a single-worksheet telephone directory which I want to print out as separate page ... A: Conditional formatting won't put in page breaks. You need VBA. Run this: (it assumes your real data ... | |
| Buttons in Excel | 4/22/2008 |
| Q: Hey Bob. I wanted to ask if there is a way I can add an ACTIVE BUTTON (like the ones at Power ... A: Not sure what the difference is between a button and "an active button", but you can create a button ... | |
| Multipage User Form | 4/22/2008 |
| Q: Bob. Excel 2003. I have designed a multipage userform which works fine. However, I would like to ... A: Well, yes, by fudging. Select A1:O50 on a new worksheet, or thereabouts, and give it a fill pattern ... | |
| Renaming excel worksheets | 4/22/2008 |
| Q: Is there a way to change the name of an excel worksheet based on the data contained in a cell in ... A: It's possible, but I'd need more details. It would work off the WorkSheet_Change event of the main ... | |
| Copying data from one sheet to another depending on two conditions being met | 4/22/2008 |
| Q: If you can solve this it would be greatly appreciated as I've been struggling for ages on this! If ... A: In the sample file, in the March sheet, enter this in T11 (helper cell), but enter by pressing ... | |
| Excel ranges | 4/22/2008 |
| Q: Bob, I once asked this question to you a long time ago, but I didn't keep track of the answer. ... A: I didn't keep it either -- try this: Sub nm() On Error Resume Next For Each nme In ... | |
| moving items from a listbox | 4/15/2008 |
| Q: I'm using Excel 2003. I have two listboxes and would like to be able to move the items that I ... A: Unclear if this is 2 list boxes in a userform or on a worksheet. If on a worksheet, and the listbox ... | |
| displaying data in a set range of dates | 4/15/2008 |
| Q: I am struggeling to find an answer to my problem, i already have a spreadsheet set up, with 6 ... A: Sub ThisShouldWork() On Error Resume Next Application.DisplayAlerts = False Sheets("Old ... | |
| expanding checklist Excel document | 4/14/2008 |
| Q: I have experience with Excel, but not with macros or visual basic. I am trying to create a checklist ... A: It does sound like you will need VBA (same as macros, by the way), but there also may be a ... | |
| IF formula comparing dates in Excel | 4/14/2008 |
| Q: I have a spreadsheet where I keep track of the dates that cost authorization letters are executed. ... A: the error is probably the colon at the end of the formula instead of a comma. You CAN do what you ... | |
| Re:code required for lists in Excel 2003 using VBA | 4/14/2008 |
| Q: I have a small requirement in excel 2003. the requirement goes something like this: I need to create ... A: I'm sorry, but I don't understand what you want to do. If you can provide more specifics, I'm sure I ... | |
| Create TimeStamp in Excel | 4/14/2008 |
| Q: I've read your answer on creating timestamp in Excel using VBA. It was very useful. However, what ... A: Private Sub Worksheet_Change(ByVal Target As Range) if not intersect(target, range("A3:K3")) is ... | |
| Create TimeStamp in Excel | 4/13/2008 |
| Q: I've read your answer on creating timestamp in Excel using VBA. It was very useful. However, what ... A: Private Sub Worksheet_SelectionChange(ByVal Target As Range) if not intersect(target, ... | |
| Create TimeStamp in Excel | 4/12/2008 |
| Q: I've read your answer on creating timestamp in Excel using VBA. It was very useful. However, what ... A: You're not specifying where the time stamp should go. This routine will put the time stamp in the ... | |
| auto populate selected rows and columns based on drop down selection | 4/10/2008 |
| Q: How do I get excel to auto populate rows and columns once a drop down is selected? Sheet1 has the ... A: I looked at the wb, but I don't understand what you want to do. Can you be specific about what you ... | |
| Create TimeStamp in Excel | 4/10/2008 |
| Q: I've read your answer on creating timestamp in Excel using VBA. It was very useful. However, what ... A: I don't remember the original post. This routine will put the time in the cell to the right of the ... | |
| group sorting | 4/10/2008 |
| Q: I am looking to have an "auto sort" for several groups of rows by column x. Sample would be just ... A: Sorry to have taken so long -- I was away. Try this: Sub sorter() For Each chunk In ... | |
| macro or script | 4/9/2008 |
| Q: i need help pls. I need a script that will check column D. If column D2 says PROMONTORY - HIGH RISK ... A: Sorry this took awhile to get back to you. I'm not sure what you mean by "take entire row of D2 and ... | |
| group sorting | 4/9/2008 |
| Q: I am looking to have an "auto sort" for several groups of rows by column x. Sample would be just ... A: I was with you until the very end -- are you asking for an auto SORT ot auto FILTER? You can't have ... | |
| Count Formula | 4/8/2008 |
| Q: I would like to know how to use the COUNTIF formula to count something based on 2 criteria rather ... A: You can't use COUNTIF for 2 criteria -- you need SUMPRODUCT: ... | |
| Incrementing numbers | 4/6/2008 |
| Q: Bob, I create workbooks where each sheet is the next invoice. The invoice numbers are in the form ... A: 1) it's not using the sheet name, it's using the sheet INDEX (first sheet has index of 1, 7th sheet ... | |
| Fluctuating decimal places from none to 2 | 4/6/2008 |
| Q: I am running Excel 2003 on an XP machine. If my result is truly a whole number, I want it displayed ... A: You can't do this with formatting; you need VBA. Right-click the sheet tab, select View Code, paste ... | |
| Excel | 4/5/2008 |
| Q: I need to find how many times Account is greater than Average. What excek function I need to use ... A: I'm not sure I understand what you want to do... In your example, is the answer 1 since 2 is ... | |
| Automatic highlighting of row | 4/5/2008 |
| Q: How do I make a whole row in the spreadsheet to be highlighted when the cursor is in any cell of ... A: You need to do 2 things: 1) right-click the sheet tab, select View Code, paste this in: Private Sub ... | |
| Pivot Table | 4/4/2008 |
| Q: When I build a pivot table I generally leave the default settings alone. Recently I noticed that ... A: In order to see the grand total for rows, you need more than one item to show in the column fields ... | |
| Hide Worksheets & Password View | 4/3/2008 |
| Q: Using Excel 2003 workbook containing multiple worksheets. I need to password protect viewing of some ... A: OK, I'll take a hug any time! :-) Glad I was able to help. | |
| Excel cell forat | 4/2/2008 |
| Q: I exported time reports to Excel. The cell shows 1:15:00am. However, I want the cells to show 1.15 ... A: 1:15:00 as a decimal you want would be 1.25, not 1.15. You can format the cells as H:MM to display ... | |
| Formula | 4/2/2008 |
| Q: I am from the USA. I have two sheets in a workbook. I need to match the data in column C of sheet ... A: Change =IF(ISERROR(MATCH(C:C,'P&S'!A:A,0)),"",OFFSET('P&S'!$A$33,MATCH(C12,'P&S'!A:A,0) + 9,14,1,1)) ... | |
| Excel Bug or by Design? | 4/2/2008 |
| Q: Hey Bob, it is Nathan, another Excel expert. I had someone present me with a data table problem, ... A: it's sorta kinda like a circular reference but not really. it IS non-intuitive, and clearly ... | |
| selecting multiple values from a list to input one cell | 4/1/2008 |
| Q: What I'm interested in finding out how to perform is being able to select specific data (multiple ... A: it depends on the nature of the list. If it's literally one cell with the string as above, then you ... | |
| Excell sum formula | 4/1/2008 |
| Q: This is hard to explain but I'll try. We have a spreadsheet that was created by someone who is ... A: If "the sum formula doesn't include some of the cells that it should include", then how can the ... | |
| Combining Vlookup and Offset functions | 3/31/2008 |
| Q: I would like to know if you could please help me on how to first to a vlook up and then add on an ... A: I'm not sure I follow. VLOOKUP returns a VALUE, not a location, so using OFFSET(VLOOKUP... doesn't ... | |
| Excel Autocomplete | 3/31/2008 |
| Q: Is it possible to set up an autocomplete that also include colour formatting? In rating a list of ... A: Select the entire range which MAY contain the colors. Use Format/Conditional Formatting. Change ... | |
| Excel Autocomplete | 3/31/2008 |
| Q: Is it possible to set up an autocomplete that also include colour formatting? In rating a list of ... A: You can use conditional formatting if you have at most 4 different values. Let me know if that's ... | |
| Excel function that applicable to all open workbooks | 3/30/2008 |
| Q: I found a similar answer here about adding macros to the Personal.xls workbook so that they can be ... A: Sorry - I answered quickly because I was answering while attending a meeting! Create your function ... | |
| workbook links | 3/28/2008 |
| Q: can i link cells with specific dates so if i enter a name in one worksheet in appears in another ... A: Can be done, but it'd be a lot easier to show you than describe it when I don't really have the ... | |
| Excel | 3/28/2008 |
| Q: I need to find a way to make the text of one particular row the color "red" and "bold" only when a ... A: Use conditional formatting. Say we're talking about when K4 has X and you want cell D4 to be red & ... | |
| Userforms | 3/28/2008 |
| Q: Bob. Excel 2003. Some time ago, you explained how to get a drop down validation to be dependant on ... A: Suppose in A1:A2 you have Fruit Vegetable. The Userform Initialization code would use: Private Sub ... | |
| problems with dates | 3/28/2008 |
| Q: I am working on a yearly bonus tracker for our company. It consists of 4 worksheets that track each ... A: The CHOOSE function looks at its first parameter. If that's a 1, it will return the next (2nd) ... | |
| problems with dates | 3/27/2008 |
| Q: I am working on a yearly bonus tracker for our company. It consists of 4 worksheets that track each ... A: In the quarter 1 sheet: A1 is =DATEVALUE("1/1/"&YEAR(TODAY())) and formatted as a date B1 is ... | |
| Can you use a generic rather than a specific name for worksheet in a macro | 3/27/2008 |
| Q: I was wanting to know if you can use a generic name for worksheets in a macro which relates to its ... A: Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Address Case "$B$5" ... | |
| Need excel formula | 3/26/2008 |
| Q: Chief I am looking a formula that If C6=A1 then B1 data should be transfer in D3, (A1=today date) ... A: Did you do tools/options/calculation and click Iterations checkbox and use 1 as Maximum iterations? ... | |
| comparing and updating two files | 3/25/2008 |
| Q: I’m trying to create a code that will effectively compare two files, and update the differences from ... A: Sub MakeComparison() Dim WBName As String, WBSheetName As String, WB, LRow As Integer Set WB = ... | |
| comparing and updating two files | 3/25/2008 |
| Q: I’m trying to create a code that will effectively compare two files, and update the differences from ... A: You don't need a macro - a formula will do. If your file names are DAY OF.xls and DAY PRIOR.xls: in ... | |
| Excel, CPI vs Date linear trend line | 3/25/2008 |
| Q: Plot CPI (y values) vs Date (x values) from gov. data. Get trend line, get equation (y = ... A: if you're getting y=1.0949x-1061.8, then m is 1.0949, not .03597 (not sure where you're getting this ... | |
| Excel Ranking with Two Criteria | 3/25/2008 |
| Q: I am trying to rank a list containing information about people, but it is based on two criteria. ... A: Use another column to do the combining of the values first, like =B2+C2 and filled down, or ... | |
| Excel, CPI vs Date linear trend line | 3/24/2008 |
| Q: Plot CPI (y values) vs Date (x values) from gov. data. Get trend line, get equation (y = ... A: If your values are in A1:B25, for example, you can use this: ... | |
| macro? | 3/22/2008 |
| Q: Every cell in Columnn A gives a drop down list of about 100 products. Each of these 100 products ... A: 1 - there's no question here - not sure what you're asking me to help you with; 2 - it's hard to ... | |
| linking cells | 3/22/2008 |
| Q: I have linked cells in two worksheets. However some of the cells in col C in the destination ... A: You can't delete cells by a formula, so instead, you can use =IF(LEN(C1)=0,NA(),C1) and fill down. ... | |
| Macro chaining | 3/22/2008 |
| Q: My problem is that I am trying to cut down on the time it takes to run about 100 macro sorting ... A: This macro will run all the macro named in column A, ignoring the blank cells, in the order it comes ... | |
| IF formula | 3/21/2008 |
| Q: I am having difficulty writing an IF statment that states if the amount of money is greater than or ... A: First, don't put the commas in the formula! Excel "thinks" this is separating parameters, not ... | |
| Offset | 3/20/2008 |
| Q: What is the equivalent of OFFSET function with INDEX and match. I have a software that does not ... A: They are not interchangeable. The OFFSET function also has parameters to determine the #of rows & ... | |
| Dynamic range | 3/19/2008 |
| Q: I would like to change the range value within the statement below, as the range differ each day. Is ... A: If you're talking about ActiveSheet.PageSetup.PrintArea = "$A$6:$K$45" you could simply change the ... | |
| Excel (2003) Macro to Insert blank row | 3/18/2008 |
| Q: Bob, I have an Excel table that has the following format: Col A Col B Col C Row 1: Mach ... A: Actually, you don't need a macro: In Cell D3 enter =IF(A2<>A3,1,"X") and fill down for 20000+ rows. ... | |
| Expire a Spreadsheet | 3/18/2008 |
| Q: Hey Bob, you have helped me in the past and I purchased your book, which I love by the way!! ... A: right-click the Excel LOGO near the file menu, select View Code, paste this in: Private Sub ... | |
| Updating spreadsheets using a Macro | 3/17/2008 |
| Q: There are 2 workbooks (one New and one Master). Both workbooks have the same column headings (there ... A: Still -- are you only changing column B? If so: right-click the sheet tab, select View Code, paste ... | |
| How can I select random cell values in several tabs worksheets sum to another cell another worksheet | 3/16/2008 |
| Q: How can I select/capture random cell values in several tabs/worksheets worksheets, then sum to ... A: You indicated you wanted to know if I do courses like on Webex? I do (on Centra), and there's an ... | |
| adding remarks in excel | 3/16/2008 |
| Q: sir, i am a teacher and i do my pupils' marks using excel. at the end of the term i have to write a ... A: If you have the 5 remarks in cells T1:T5, for example, you can use the rating/mark as in index to ... | |
| IF Funtions with Multiple Conditions | 3/15/2008 |
| Q: My column A has value either: ACM or DCM Column B has either : EOD or ITD Column C is named as ... A: Easier to see with line breaks inside the formula (which is allowed): And you need nested if's ... | |
| How can I select random cell values in several tabs worksheets sum to another cell another worksheet | 3/15/2008 |
| Q: How can I select/capture random cell values in several tabs/worksheets worksheets, then sum to ... A: 3 random cells in each worksheet: Sub Joe() Dim ans As String ans = "=" For Each sh In ... | |
| Updating spreadsheets using a Macro | 3/15/2008 |
| Q: There are 2 workbooks (one New and one Master). Both workbooks have the same column headings (there ... A: There's one basic problem which needs to be addressed: If an entire row in New is being updated, ... | |
| Generating Charts | 3/14/2008 |
| Q: I am interested in making a button for Generating charts. It should read in, for instance, the ... A: Then once again -- why not simply record the steps to make the chart you want, and with a little ... | |
| Excel Formula | 3/14/2008 |
| Q: I'm having trouble wrapping my brain around a formula to do this ... if A1= N then 0, if A1 = Y ... A: I don't think you mean A1+B1, but that each is Y and that you also mean the character "Y" (or "N"). ... | |
| Copying data from one worksheet to another | 3/14/2008 |
| Q: I have a spreadsheet where I am trying to copy cells from one worksheet to another using relative ... A: I'm not following your references. You say sheet1!A8 should go to sheet2!A8 sheet1!C8 should go to ... | |
| Time stamps (?) in excel 2003 | 3/13/2008 |
| Q: I need help in highlighting a cell(s) when a change is made. Here is my situation. I have a Deal ... A: You need VBA. Right-click the sheet tab, select View Code, paste this in: Private Sub ... | |
| Vlookup and IF functions in Excel | 3/13/2008 |
| Q: I have a question in excel formulas: column one = county number column two = year column three = ... A: if the initial table is in A1:C7, then you must first list all the unique years down a column, say ... | |
| Sum if a column has 5 digits | 3/12/2008 |
| Q: Bob Thanks in advance for your help! I would like to summarize dollar totals in Column I if Column ... A: =SUMIF(A2:A100,"<100000",I2:I100) To do it by customer I think a pivot table would do well, or you ... | |
| Excel list question | 3/12/2008 |
| Q: I have a list that I've created, which is really just 3 columns of data (column 1 is UserName, 2 is ... A: Probably would be safest/easiest to use a UserForm with some VBA code to check your entry. Feel free ... | |
| date range check | 3/12/2008 |
| Q: I am putting together a quarterly performance spreadsheet. I have 14 columns/weeks. Underneath each ... A: Easier with conditional formatting. Assuming the dates are Sunday dates & the first is in B1, select ... | |
| Removing duplicate names in a column | 3/11/2008 |
| Q: I want to thank you for your answer on 2/13/08 on Excel matchup. It worked great. In column I i put ... A: look at data/filter/advanced filter -- it has an option for unique values only. Once you isolate the ... | |
| command buttons | 3/10/2008 |
| Q: I have created a web of command buttons, which works really well. My only problem is that when I ... A: Are they running VBA code which exists in the same workbook that the buttons are in? If so, it ... | |
| Macro error | 3/10/2008 |
| Q: I have a basic macro set up where an input box has you select a cell than organizes the data based ... A: Sub StreetSort() On Error Resume Next '<=======ADDED BY ME Dim MyRange As Range Dim LastRow As ... | |
| number determines how many cells will change color | 3/10/2008 |
| Q: When putting a certain number (i.e. 2 or 3) in a cell, I would like to have other cells in a row or ... A: If there's only up to 4 values possible, you can use conditional formatting. Select the cells which ... | |
| Returning names | 3/9/2008 |
| Q: I would like to set up a sheet in my workbook to put out data of who will be leaving the ... A: You need VBA to do this. Assuming the table begins in A1 ("NAME"), and that the 30 DAYS...60 Days... ... | |
| Formula usage | 3/9/2008 |
| Q: I was presented with this formula, The formula is: ... A: The SUMPRODUCT function requires arrays of numbers. the array ($D$1:$D$16="B") produces an array of ... | |
| excel | 3/7/2008 |
| Q: I have a worksheet that has 716 rows and I need to pick every 17th one for an audit. I am not very ... A: Assuming your data starts in row 1 (where row 1 is headers & the 'real" data starts in row 2), pick ... | |
| Conditional formatting | 3/7/2008 |
| Q: Is there any way to highlight a selection of cells by just marking another cell. That is: In my ... A: I don't think there's any conditional formatting which will do the job, hence the VBA routine. But ... | |
| Conditional formatting | 3/7/2008 |
| Q: Is there any way to highlight a selection of cells by just marking another cell. That is: In my ... A: Here's one way -- right-click the sheet tab, select View code, paste this in: Private Sub ... | |
| Using filter with data validation. | 3/6/2008 |
| Q: I have a problem with my excel sheet. 'cos my english isn't that good, I try to explain this as ... A: It's hard to picture how it can hide rows in the layer column without hiding the info in all the ... | |
| Excel Macros | 3/6/2008 |
| Q: I have a schedle that I want to use for my mgrs which has the staff names down column A the actual ... A: Manually hide the columns, then run this: Sub CreateAView() ActiveWorkbook.CustomViews.Add ... | |
| Time entry in 24-hr format without puncutation | 3/6/2008 |
| Q: I'm building a tool that requires time input in 24-hr format, and am trying to avoid forcing the ... A: If the cells for this to happen to are known, say C10:C20 only, then right-click the sheet tab, ... | |
| charting question | 3/5/2008 |
| Q: first time on this site..looks great I have a huge file with weather data, one column has the year ... A: Change the combination of year & month into a real excel date and format it as you want. So if A2 ... | |
| simple formulas (i think) | 3/4/2008 |
| Q: First off, your service was great in responding to my questions about checkboxes last week - so ... A: It'd be easier if you could email me a sample workbook at bobumlas@yahoo.com & I'll see what I can ... | |
| Search in multiple sheets | 3/4/2008 |
| Q: I am looking for a function to search in multiple sheets. The situation is like this: There are ... A: Assuming the final sheet is the last sheet, run this macro: Sub Largest10() For i = 1 To 12 ... | |
| Excel Formula Autofill | 3/4/2008 |
| Q: Problem: I need to enter a VLOOKUP formula into a cell, then 'Autofill' dragging it down 40000 Rows! ... A: Use absolute reference notation -- "$": =VLOOKUP(E1,$A$1:$D$5000,3,FALSE) for example. When this is ... | |
| IF Sum | 3/3/2008 |
| Q: I am writing a data table for golf handicaps ranging from 28 - 0 down the first column. IN the ... A: Not sure I follow, but if you want the "total" line to really be a "copy" of a particular line, you ... | |
| ranking data with 2 criteria | 2/29/2008 |
| Q: Bob, I have been trying to figure out if one can rank some data using two criteria. The spreadsheet ... A: use the regular rank function in the first 5 cells referencing the range B2$:B$6 (as it seems to be ... | |
| VBA question & sharing question | 2/28/2008 |
| Q: Please can you tell me what would cause records in a Spreadsheet to duplicate themselves when a ... A: I know nothing about sharing workbooks. You can open a spreadsheet at the right row (assuming the ... | |
| checkboxes (again) | 2/28/2008 |
| Q: I recently asked a question here and one of the folks here so terrific and responded quite fast with ... A: I gave you very similar code. Try this: Sub DoIt() Dim thing For Each thing In ... | |
| checkboxes | 2/27/2008 |
| Q: I currently have over 1000 different checkboxes within my spreadsheet and each one is in a different ... A: You'll have to make the font white, as you said, but you would also have to run this macro only once ... | |
| Checkbox | 2/27/2008 |
| Q: Can not get the checkbox to resize after putting it on a worksheet. the text will resize but not the ... A: It's not available for resizing. But you can simply draw a square and assign a macro to it to turn a ... | |
| nested IF cond THEN copy series of cells on same spreadsheet | 2/26/2008 |
| Q: I consider myself a basic Excel user. I hope I can put in writing what I am trying to do...here ... A: Sub MoveLoop() For i = 1 To Range("A65000").End(xlUp).Row For j = 1 To 11 If ... | |
| Field Comparison/Consolidation | 2/26/2008 |
| Q: I need an Excel macro which does the following: 1)Change all the values in the "IncentiveAmount" ... A: Sub Jim() Application.ScreenUpdating = False Dim Numrows As Single Numrows = ... | |
| Combining tabs from multiple workbooks | 2/25/2008 |
| Q: I have your book "This isn't Excel, it's Magic!" and I think it's wonderful! I have a challenge ... A: It's hard to visualize what multiple checkboxes would look like when copied onto the same worksheet ... | |
| hiding parts of number | 2/25/2008 |
| Q: I have a list of people that were given a random number, they are not in sequence ie. (not 1,2,3,4) ... A: Only with some trickery. If the column to contain the SS# is B, say, then format the whole column as ... | |
| Excel Formula | 2/25/2008 |
| Q: I want to say thanks again for answering my last question. I have another that might be pretty ... A: If a sample formula you now have to get info from Sheet2 is =Sheet2!D3, for example, do this: Put ... | |
| nested IF cond THEN copy series of cells on same spreadsheet | 2/24/2008 |
| Q: I consider myself a basic Excel user. I hope I can put in writing what I am trying to do...here ... A: 1 - your pseudo code said to do the exact same thing if A1=E1 OR A1=F1, so there's no need to ... | |
| Sumproduct | 2/23/2008 |
| Q: I get ur formula =SUMPRODUCT(--(U3:FO3>0),--(MOD(COLUMN(U3:FO3),6)=3)) for countif and it is very ... A: The formula DOES only look at U3,AA3,AG3,..., even tho it doesn't look like it. Here's how it works: ... | |
| Tax bracket; if, then, function | 2/22/2008 |
| Q: I want to be able to fill in the earnings before taxes and then have the cell with the tax equation ... A: I have no idea how/where to get the US tax bracket system, but if you already have that table in ... | |
| Copy same cells in same worksheet of multiple workbooks into one master spreadsheet | 2/21/2008 |
| Q: I have read your solution to the problem raised in: ... A: Untested, but this should work: Sub Guatam() ThisWorkbook.Sheets(1).Clear 'or change this to ... | |
| Consolidating information from multiple spreadsheets into another one | 2/21/2008 |
| Q: Can I pull data from several individual Excel files and summarize it in a completely separate Excel ... A: Simply use the Data/Consolidate & check the create links to source data and everything should be ... | |
| Excel SUM(IF(...... formula | 2/20/2008 |
| Q: I tried sum up the dollars amount for the months of Jan 07 & Feb 08 (or 1st quarter of the year + ... A: If your sample data is in A1:C900, for example. 1st quarter: ... | |
| Using Lookup Functions | 2/20/2008 |
| Q: How do I use the lookup function to have a product name and price automatically entered in a sales ... A: Assuming the range A1:C300 contains the ID, name & price, then if you enter the ID in a cell in the ... | |
| Excel 2003-Controling sequence of cell navigation | 2/20/2008 |
| Q: I have created a simple fill-in the blank excel form and would like to set a tab order or somehow be ... A: If you use format/cells to unlock the columns you want the user to be able to access (First select ... | |
| corrupted file | 2/20/2008 |
| Q: my file is corrupted plz tell is there any way to get the contents of that file A: start a new workbook, make sure when you use file/open you're in the same directory as the corrupted ... | |
| drop down menus and automatically populating field | 2/19/2008 |
| Q: i have 2 separate questions: 1)i have a worksheet which enables drop down lists. when i click on ... A: It'd be a lot easier to help if I could see the actual wb -- send me a copy at bobumlas@yahoo.com ... | |
| how do i do this with macro, orsolver or VBA | 2/19/2008 |
| Q: http://www.4shared.com/file/37865668/12136fe1/temp.html is a snapshot of an excel file. i need to ... A: Sorry - I thought you were referring to the actual column -- column I is always 9, not 0 or less ... | |
| trendlines in excel | 2/19/2008 |
| Q: I have a couple of questions and looking at your records (and following previous questions – I ... A: Ctrl/Shift/entering =LINEST(A1:A20,ROW(1:20)^COLUMN(A:C),,TRUE)) IS the way to get the coordinates ... | |
| Need more help | 2/19/2008 |
| Q: Bob, I recently designed a macro (with your help) to record history on a tracking spreadsheet I ... A: Replace whole thing with this (which does the same thing!): Private Sub Worksheet_Change(ByVal ... | |
| FUNCTION PROBLEM | 2/18/2008 |
| Q: BOB BACK AGAIN!!! THANKS AGAIN FOR PREVIOUS SOLUTIONS PLEASE CAN U HELP WITH THIS ONE? I NEED A ... A: List the unique Makes down a column, say E2:E8. In F2: ... | |
| error if value not found | 2/18/2008 |
| Q: I have another issue with the form that I emailed to you. The excel spreadsheet is setup in two ... A: Private Sub cmdCalculate_Click() Dim ACost As Currency Dim GST As Currency Dim ATotal As ... | |
| Page Numbering | 2/18/2008 |
| Q: Bob, I have a 28 page spreadsheet in Excel that has been set up to look like and print like it was ... A: merged cells notoriously have issues with getting the row heights correct. I don't think screen ... | |
| Work Allocation | 2/18/2008 |
| Q: "Hi Bob, I need to develop a database in excel via which i can track a transaction/unit which can be ... A: how is it accessed by multiple users -- thru a shared workbook? sent via email, updated & sent back? ... | |
| how do i do this with macro, orsolver or VBA | 2/16/2008 |
| Q: http://www.4shared.com/file/37865668/12136fe1/temp.html is a snapshot of an excel file. i need to ... A: I don't see any formulas that would make the goal seek work, but you can goalseek each cell you ... | |
| Troublesome multiple IF function. | 2/16/2008 |
| Q: I've done a few months roughly touching in and out of Excel. I know my way around the basics and can ... A: You're getting #NAME? because if A8 were Monday, for example, your formula would return the name ... | |
| CheckBox in Excel cell | 2/15/2008 |
| Q: I saw that you have answered this question already for cell B2, but what if i want to use the VB ... A: Try this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row <> 5 Then ... | |
| Calendar Control to navigate to cells | 2/15/2008 |
| Q: First of all I have no experience in vb, vba or any type of coding. So please try to explain it as ... A: Right-click any toolbar button or menu, select "Control Toolbox". The last control on this is "more ... | |
| Page Numbering | 2/15/2008 |
| Q: Bob, I have a 28 page spreadsheet in Excel that has been set up to look like and print like it was ... A: You'll need to do it in 2 print jobs. On the first one, simply print the first 2 pages. On the 2nd ... | |
| date issue in Excel | 2/14/2008 |
| Q: I have a quote form that i built for submitting quotes. I used the =TODAY() in a cell to auto enter ... A: Pressing Ctrl/; will put in todays date as a fixed value. If you need this to update on certain ... | |
| count formula array | 2/14/2008 |
| Q: I have a results worksheet that needs to calculate the quantity of claims for data stored in another ... A: You didn't say where the quantity column is, so I'm going to assume it's AJ (adjust as necessary). ... | |
| Circular Reference Gauntlet | 2/14/2008 |
| Q: I have just finished creating an eight-worksheet Excel workbook with multi-sheet =Sum(IF arrays and ... A: getting "Run time error '91 - Block vairable not set." means there's VBA code running. When you get ... | |
| Shuffling Data | 2/13/2008 |
| Q: I am creating a spreadsheet to do inventory on our parts in the warehouse. In just one warehouse we ... A: Here's a simplified method: If your part#s are in A1:A10000, put this formula in B1: ... | |
| Search Technique to find inexact matches | 2/13/2008 |
| Q: I would like to set up a cross-reference list of job. We store our job by customer name, shop order ... A: How about creating a formula which strips the dashes, slashes and spaces, so variations on these ... | |
| Excel matchup | 2/13/2008 |
| Q: I have six columns in excel. Column one has the names of people who bought product A. Column two ... A: I don't see a way of doing precisely what you want, but this gives you the answers presented in a ... | |
| Hiding Excel columns | 2/13/2008 |
| Q: I know how to use hide/unhide options in Excel...but how do I hide a column in excel and then send ... A: protecting it won't hide anything. First hide the column, then protect the worksheet. He just won't ... | |
| copy .txt to excel file | 2/13/2008 |
| Q: I need to copy the temporarily generated .txt file to the already existing excel file named sam.xls. ... A: I don't know what you mean by "the word INDEXING gets scattered". In order to know how many rows ... | |
| question | 2/11/2008 |
| Q: Hey My little brother had email you asking an excel question. You reply was for him to go buy your ... A: Sorry - I have no recollection of saying that -- I have a standard "trailer" to everyone mentioning ... | |
| copy .txt to excel file | 2/10/2008 |
| Q: I need to copy the temporarily generated .txt file to the already existing excel file named sam.xls. ... A: First open the txt file from within excel, perhaps using the wizard to get it into the appropriate ... | |
| Index Match | 2/9/2008 |
| Q: I am doing a home budget. On one sheet(TX Sheet) I have date, transaction, transaction category( for ... A: If the category on sheet1 is in column C: =SUMIF(Sheet1!C:C,"Category",Sheet1:D:D) (where D is the ... | |
| Help with one of your macros | 2/8/2008 |
| Q: Bob, I have been designing a history spreadsheet of changes made to cells on a diferent ... A: you can't have 2 Worksheet_Change event macros for the same sheet. Maybe this?: Private Sub ... | |
| Excel "IF" Formula Assistance Needed | 2/7/2008 |
| Q: I need to get a particular calculation into the cell based on the #in D5: If D5 is >= to 75 then ... A: First, you don't specify what you want if D5 >65 & <75, so I'm going to return "UNKNOWN" for those ... | |
| Excel Formula (Very Complex) | 2/6/2008 |
| Q: 1/15/2007 92 1/16/2007 6 1/18/2007 25 …. …. 1/2/2008 1 1/3/2008 5 1/4/2008 9 1/5/2008 10 ... A: I'm confused when you say "1/18/2007, because that is one year from 1/17/2008" -- that's not true. ... | |
| Copy based on criteria and paste in specified range | 2/6/2008 |
| Q: I have tried searching the previous answers for code that would work for me to no avail. I am ... A: a few questions: • copying B:N doesn't paste correctly to A:N (not same # of columns)-- do you mean ... | |
| how to match 2 worksheet and count their match | 2/4/2008 |
| Q: i have 2 worksheet, both with 6 columns (A-F) containing numbers on each cell... i need to compare ... A: In sheet3, A1: Row1 In sheet3 B1: Row2 etc. In Sheet3, A2, enter this formula by holding the ... | |
| macro initiation | 2/4/2008 |
| Q: Bob, I have mostly worked with tieing a macro to word art or to a manual operation is a spreadsheet. ... A: Sounds like you may have an error value on your worksheet...?? Try this: Private Sub ... | |
| how to match 2 worksheet and count their match | 2/4/2008 |
| Q: i have 2 worksheet, both with 6 columns (A-F) containing numbers on each cell... i need to compare ... A: Before I write such a thing I wasnt to ensure I understand... You want only a COUNT ofhow may times ... | |
| Counting appearances of text across many sheets | 2/3/2008 |
| Q: I have created a sign in sheet for a club and would like to create a formula that counts how many ... A: If that name appears anywhere in the sheets, like A4 in sheet1, B22 in sheet2, Q800 in sheet3, ... | |
| Excel Help on incremental increase | 2/2/2008 |
| Q: I am trying to create a formula in excel that could tell me what the incremental percentage increase ... A: The formula looks quite complex, but you can get the result by tools/Goal-seek: Put the initial ... | |
| Excel | 2/1/2008 |
| Q: I downloaded a template (10100133)from Microsoft website. It's a Purchase Order. I have made ... A: Right-click the excel LOGO (near the file menu), select View Code, paste this in: Private Sub ... | |
| Multipe Accumulator Cells | 2/1/2008 |
| Q: I wonder if you can help a mere excel novice. I managed to find some code that creates an ... A: Private Sub Worksheet_Change(ByVal Target As Range) Static e16Accum As Double, e10Accum As ... | |
| macro initiation | 2/1/2008 |
| Q: Bob, I have mostly worked with tieing a macro to word art or to a manual operation is a spreadsheet. ... A: This will put the time into the cell to the right of the word "yes", when "yes" is entered. ... | |
| matching | 2/1/2008 |
| Q: Good day. I hope you can help me. I wanted to cut and paste the data from one column to another. ... A: Run this macro: Sub MoveSame() Dim BVal, CVal, n As Integer, m As Integer On Error Resume Next ... | |
| matching | 1/31/2008 |
| Q: Good day. I hope you can help me. I wanted to cut and paste the data from one column to another. ... A: Might the data look like this? Ritchie 11546 100.00 Joey 11543 50.00 Richard ... | |
| Merging Excel files into one | 1/30/2008 |
| Q: ! Suppose i have marklist of all students of classes I to XII in different excel sheets. The number ... A: Try Data/Consolidate. First start on a blank sheet, use that command, and select each sheet's range ... | |
| Excel Data Sorting | 1/29/2008 |
| Q: I have an Excel sheet with 4 columns: Date, Customer #, Part #, and Quantity. Each column contains ... A: On the sheet which is to receive the correct records, enter the same column headers (date, customer ... | |
| How to break down data in specific date intervals | 1/26/2008 |
| Q: I would appreciate your help on the following.... I have three months data from Sep15 to Dec15. ... A: 2nd question first: 7:30 hours is stored by Excel as part of a day. To convert to decimal, you need ... | |
| Vlookup between 2 dates | 1/25/2008 |
| Q: I need to write a vlooup formula that does the same as a Vlookup function however it needs to lookup ... A: =(vlookup(datevalue("07/21/07"),A1:C10,3)+ vlookup(datevalue("07/22/07"),A1:C10,3)+ ... | |
| Query | 1/25/2008 |
| Q: 1) I'm trying to create a tool that will calculate all the interim deadlines for a project when the ... A: 1 - If I put Jan 2 in A1 then =Workday(A1,10) gives 39463 which, when formatted is Jan 16 (not 15th) ... | |
| Baffled by code... | 1/24/2008 |
| Q: I'm very good at creating VBA code. I've even written code to work between Excel and Monarch and ... A: Could have used If Intersect(Target, Range("A1:D12")) Is Nothing Then Exit Sub But if you try this ... | |
| Paste data to filtered column | 1/23/2008 |
| Q: I have two columns with many rows in sheet1. The header for column A is group and column B is name. ... A: It's the nature of the beast. Simply don't paste into a filtered column. Try this experiment: Enter ... | |
| VBA | 1/23/2008 |
| Q: I am a high school teacher and I am trying to create a macro which will highlight students that are ... A: Select C3:C30 first, (important step) then use format/conditional formatting, then change "cell ... | |
| Formula to auto fill data on a summary tab | 1/22/2008 |
| Q: I have a spreadsheet with approximately 1500 lines of data in it. There is a 2nd tab that is a ... A: Assuming the "other tab" is named Other: ... | |
| Dynamic range selection | 1/21/2008 |
| Q: I have a personnel list and have a coding system for classifying them according to their position, ... A: If the barcode is 8 digits, why use mid(...2,2) & mid(...,5,8) -- should't it be mid(...2,2) & ... | |
| click to answer | 1/20/2008 |
| Q: I am a fire fighter currently in Iraq. I have a program that I am creating that is a check list for ... A: Right-click the sheet tab, select View Code, put this in (modify the range as appropriate): Private ... | |
| Formula help - calculate criteria based running average | 1/17/2008 |
| Q: I need to develop a formula to calculate "burn rate." (Please Note: I cannot use Pivot Tables, must ... A: The formula you can use is: ... | |
| Excel | 1/16/2008 |
| Q: I was wondering if it was possible to delete or have a non response to a spreadsheet at a given date ... A: right-click the excel LOGO near the file menu, select View Code, enter this: Private Sub ... | |
| Tab Key in Excel | 1/16/2008 |
| Q: I have a spreadsheet made with Excel that has 6 columns. After I finish entering data for each row, ... A: several possibilities: 1 - After entering data in column F, press Home and down arrow 2 - use VBA ... | |
| using data in Excel | 1/15/2008 |
| Q: I have a spreadsheet with sets of info fields for 23 separate days on one tab. One set for Jan 2, ... A: Assuming row 2 on the main data tab will always contain data so the last item in that row could be ... | |
| Text Values | 1/15/2008 |
| Q: I'm very stuck here, so hopefully you can help. I've got a worksheet giving sales details for a ... A: For the first part, you want to use Data/Filter, then click on the dropdown & select one advisor and ... | |
| Selecting range from another workbook | 1/11/2008 |
| Q: I am trying to write a macro that allows a user to select a range from another workbook. Ive tried ... A: You can, but it takes 2 clicks. the first click simply activates the 2nd workbook. One more click ... | |
| I need 10 nested IF's in Excel 2003 | 1/11/2008 |
| Q: I have Excel 2003 and am only allowed i believe it is 7 nested formulae. can you please tell me how ... A: Don't use IF: ... | |
| Reversing Columns in Excel | 1/11/2008 |
| Q: I'm using the indirect function in excel which someone just told me about. It's great... but I have ... A: Why not leave it as is and just grab the data from the latest column? If row 1 is non-empty through ... | |
| excel | 1/10/2008 |
| Q: i have a row with the due dates turning red, is there a way to make the rows that contain past due ... A: Select all the cells you want to change color (maybe), and use format/conditional formatting. Change ... | |
| sheet question | 1/10/2008 |
| Q: i made a list for my real estate office. colum "A" they are numbered 1 - what ever... colum "B" I ... A: Instead of entering 1,2,3..., assuming the first data row is row 3, enter =ROW()-2 in A3 and fill ... | |
| Populating multiple forms from one set of data | 1/9/2008 |
| Q: I am wondering if you can help me out. I need to know how to populate multiple excel forms with one ... A: I need a lot more info. Are you talking about VBA-type Userforms or simply a range of cells? I think ... | |
| Locking format and auto-feeding data from another book | 1/8/2008 |
| Q: I have a report that I must do every month where I have to pull | |