| Subject | Date Asked |
|
| Inserting a button that will sort data | 1/7/2009 |
Q: I created a simple spreadsheet to track work flow. i goes as follows: ...
A: Won't it be easier for you to record a macro that sorts the data. you can add a shortcut to the ...
|
| Excel macro | 1/4/2009 |
Q: I have several files which have a filename such as WBS--20090222, whereby the number portion of the ...
A: David, Create a module and enter this code ---- Option Explicit Public Sub openFile() Dim xls As ...
|
| Cell shading | 1/4/2009 |
Q: I want to shade a cell based on wether the formula, condition, that I have used in that cell is true ...
A: Khalid, You can use "Conditional Formatting" to get this done. 1. Select the cell which is ...
|
| Excel 2000 & Access 2000 | 1/4/2009 |
Q: How do I take data from a Microsoft Access 2000 database's table and insert it into an Excel 2000 ...
A: Zacharie, The easiest way is to create a database query within Excel. You should be able to create ...
|
| Print 365 pages with one cell changing the date | 1/2/2009 |
Q: I have a tracking sheet that I use and it is on one page in excel. I would like to print it with ...
A: Brad, Create a new module and plop in this code -- Option Explicit Public Sub print365() Dim xls ...
|
| Insert a Row and then insert formulas into that row. | 6/30/2008 |
Q: I'm trying to write a marco which will insert a row in any part of a spreadsheet and then insert ...
A: this should suffice Dim Rng, n As Long, k As Long Dim xlr As Excel.Range ...
|
| Excel Formula | 6/26/2008 |
Q: I created a worksheet to compare two different types of data, it checks it and everything should ...
A: Jermaine, For the exact function, you need 2 arrays - I see that you are using ...
|
| Excel Formula | 6/25/2008 |
Q: I created a worksheet to compare two different types of data, it checks it and everything should ...
A: Jermaine, This should work. Enter this as an array formula ...
|
| excel formula | 6/19/2008 |
Q: I need to count the number of entries in column a with a value equal to the value in cell d2 when ...
A: you can enter it as an array formula =SUM(IF(A1:A10=D2,IF(B1:B10=1,1,IF(B1:B10=-1,-1,0)),0)) in ...
|
| EXCEL/VB | 6/19/2008 |
Q: How can I detect if a cell contains a hyperlink using VB to look into the cell?
A: create a module and enter the following code Option Explicit Public Function ishyperlink(r As ...
|
| Crossreference | 6/18/2008 |
Q: Ok I am an intern at a life insurance firm and am doing some file clean up. I have two excel ...
A: you should be able to use the vlookup function. unfortunately, without any details on the layout of ...
|
| copy a cell to another sheet | 5/1/2008 |
Q: into A1 of Sheet1 there is a value which is update every sec from a timestamp cell. If the value of ...
A: mark, here is what needs to be done: 1. Press Alt + F11 to get to the code window 2. in the sheet1 ...
|
| formula in cell reference | 3/5/2008 |
Q: I have two sheets. In sheet 1 a number is entered in cell say b4 and I need this to go to sheet 2 ...
A: you can use the address function to construct the location that you need to pick data from. eg. ...
|
| Data Table | 2/28/2008 |
Q: Can you please tell me how do i use the table (what-if analysis) under data menu when the row input ...
A: not very sure about it but typically when excel shows a limitation o using cells only from the ...
|
| Merging data from all worksheets in workbook | 2/26/2008 |
Q: I have found your answers posted here very helpful. I work as a statistician in industry, and I ...
A: Partha, Copy this code in a module. Have a blank excel tab called Final. run the macro. The tab ...
|
| question | 2/25/2008 |
Q: how do i exract information from a column from a different sheet after inputing say quantity on a ...
A: basically you are looking for a left lookup instead of using vlookup (in which you can do lookups ...
|
| Vlookup on non-exact match data | 2/19/2008 |
Q: I am trying to do a vlookup across two large worksheets. The 1st file contains simple part numbers, ...
A: Janie, Fascinating problem. Might have a solution for you. Assume that sheet 1 has the 2 columns ...
|
| Cumulative Sum | 2/13/2008 |
Q: If I have a series of numbers, let say:1,5,15,9,10,3 I want to find the cumulative sum for the ...
A: Ric normally, i dont like to resort to vba and brute force but this is one case where ther is little ...
|
| Averages | 2/13/2008 |
Q: I need to record a daily transaction average utilizing the following data: Transaction Dollars - ...
A: the way is to include or exclude the number of zeros from the denominator =SUM(B61:B72) / ...
|
| IF Statements | 2/13/2008 |
Q: I was wondering how i would incorporate colors into an "if statement". So for example: If B5 is not ...
A: You should be able to conditional formatting to get the result.(Format -> Conditional Formatting) ...
|
| V-Lookup | 2/12/2008 |
Q: I have an issue with v-lookup. On spreadsheet A, I have multiple delivery numbers. On spreadsheet ...
A: Assume in SheetA (the output sheet), you have Delivery # in Column A, Quantity in Column B and you ...
|
| excel VBA formula | 2/11/2008 |
Q: suppose i have a column in a excel sheet like this- feature mandatory feature package future ...
A: if you have a header for the column, i.e data starts at row 2, then you can use the "Advanced ...
|
| populate cell condionally | 2/10/2008 |
Q: can i get advice please. I have two columns in excel - one with hours and one with days. i want ...
A: you can write a custom vba function and call it. however, you should be able to accomplish the same ...
|
| how to caculate data in particular month | 2/10/2008 |
Q: Respected Sir, I like calculate of data in MSexcel on particular date range how to use sumif ...
A: Cannot use Sumif directly as sumif uses only one condition use an array formula. instead of ...
|
| A relatively simple task, except I couldn't figure it out. | 2/10/2008 |
Q: I've just started using Excel 2007, so I'm afraid this question might come across as quite simple ...
A: You are on the righ track. you can nest IF statements. It becomes more complex as there are 3 ...
|
| match, Index, sum | 2/9/2008 |
Q: I am doing a home budget. On one sheet(TX Sheet) I have date, transaction, transaction category( for ...
A: Jennifer, you should be able to use the SUMIF function. However, if you have transactions that ...
|
| doing arithmetic with numbers in time format | 2/8/2008 |
Q: You've been of much help to me in the past, and I hope you can do so again. A B ...
A: Chris, I am not sure I agree with your formula on how to calculate the 9:00 but for the immediate ...
|
| Auto insert static date | 2/7/2008 |
Q: I am trying to create a formula which inserts a date when i enter either "yes" or "no" into another ...
A: Rich, My apologies for the late response. I knew I had a solution for this but was unable to get it ...
|
| sumproductvisible | 2/6/2008 |
Q: I HOPE YOU ARE WELL,I HAVE A PROBLEM TO USE “SOMEPRODUCT” JUST FOR ONLY VISIBLE CELLS. I WANT TO USE ...
A: instead of cell.value, construct a string with the same syntax as the criteria, would suggest ...
|
| league awards formula | 2/3/2008 |
Q: In our pool league we give money awards at the end of season, 4% of total to last place and then ...
A: Enrique, since you need same percentage increments, the answer is a bit complex A1 -> # of teams B1 ...
|
| All possible combinations | 2/3/2008 |
Q: I've looked at some of Damon's answers on combinations but can't quite adapt them to my problem. I ...
A: Diane Apologies for the late response Assume -> A1 = 2 A2 = 8 B1 = 2 B2 = 8 C1 = 0 C2 = 1 ...
|
| excel formula | 2/2/2008 |
Q: How do I make a cell format another cell? ex. If cell a1 is blank the text in cell a2 is bold or if ...
A: Jeff, Apologies for the late response. The best way is to use conditional formatting. There are 2 ...
|
| Excel counting formula | 1/31/2008 |
Q: Is there a way to count a column of numbers that may have duplicationed numbers and only count every ...
A: you can if you have an additional column that you can use assume that you have. let a1:a10 have ...
|
| count multiple conditions | 1/25/2008 |
Q: in one worksheet i have test in column A and dates in column B. e.g. A B apple ...
A: try using array formulae. have answered this a few times in the past. if you could search my old ...
|
| Need help with an IF formula | 1/21/2008 |
Q: . wondering if you can help with a formula I am trying to use for a profit share spreadsheet. In ...
A: Tim, The easy answer to the formula is that to use IF(SUM(B28:E28)<E4,E28,E28/2) But if you ...
|
| Built-in Copy function? | 1/20/2008 |
Q: I'm a relatively new user to Excel macros and VBA. Here's myI'm evaluating an Excel built-in "IF" ...
A: the IF function allows you to copy the values. =IF(<condition>, <copy contents of this cell if ...
|
| formula | 1/19/2008 |
Q: Here is what I'm trying to accomplish. In a single cell I have numbers increasing from 0-250,000+. ...
A: create a table in H1-J4 as follows H1 -> 0 H2 -> 20000 H3 -> 100000 H4 -> 250000 I1 -> 0 I2 -> 15% ...
|
| Importing Large text data | 1/18/2008 |
Q: I have a number of text tiles which when imported to into Excel exceeds number of rows, is there ...
A: Kiran, i thought that it automatically puts it into the next sheet if there is an overflow. maybe ...
|
| Selecting and Looping Range | 1/17/2008 |
Q: I've been trying to think of the best way to do this and coming up blank. I want to format a range ...
A: Very tricky but doable I am assuming you want to format it something like a box around or something ...
|
| excel comparing column A & B and putting different results in column C | 1/16/2008 |
Q: I was wondering if it’s possible to compare column B to column A and put the different results on ...
A: Assume - Column A has the base column that is being searched Column B has the input data For ...
|
| Take Criteria from multiple columns and count total of string appeances in another column | 1/9/2008 |
Q: Take Criteria from Column A , Column B, and Column C AND count the number of times a string appears ...
A: Kristol, since your criteria already has Column C = 1, the criteria in the count section is ...
|
| copying if | 1/9/2008 |
Q: I am working on a sheet with performance statistics. This is based on 2 sheets. what I try to do is ...
A: Adrian, I think you should be able to use the MATCH function and the OFFSET function to do this. ...
|
| conditional formats for 8 value ranges | 1/7/2008 |
Q: I have 8 ranges that I need to be able to color code in excel. < 0.2 > 0.2 < 0.4 > 0.4 < 0.6 > 0.6 ...
A: unfortunately, excel only allows 3 conditional formatting. if your aim is to tag them, you could ...
|
| excel vlookup and sort help | 1/5/2008 |
Q: I have an excel sheet that has two pages on one I have a list of user data and on the other I have ...
A: Damion, You don't need to sort the sheet everytime you add new data. In the vlookups, the last ...
|
| shorten day reference | 1/5/2008 |
Q: i am creating a gantt chart and have two rows of info, day and the day number A 1 mon 2 7 i ...
A: Pat, the problem is that the inbuilt formatting for Excel is limited even if you do custom ...
|
| Excel VBA - Copying one sheet in a workbook to another | 1/4/2008 |
Q: I have a problem related with VBA. I have a task to copy one sheet with VBA function to another ...
A: my guess is that you don't need this complex code Dim xlw_source As Excel.Workbook Dim xlw_dest As ...
|
| Log in and Password page | 1/2/2008 |
Q: Is it possible to add a log in page(blank page with icon and log/password), a page that would appear ...
A: Pete, The way I approach this is as follows 1. Hide all the sheets in the excel file with a password ...
|
| Counting multiple sets of data that match | 1/2/2008 |
Q: I need to count the number of cells where two sets of data match - for example: Column A lists ...
A: Tom, Love these questions as it give me an opportunity to use array functions in excel. Array ...
|
| Holiday Tracker | 1/2/2008 |
Q: your now the third expert I've asked the same question to, I hope you can help Bhaskar. I have a ...
A: Peter, I should be able to help you. Can you send me the following. Layout of the Agent sheet and ...
|
| Copy contents of Sheet1 to Sheet2 automatically | 1/2/2008 |
Q: i need a formula which copies the contents of sheet1 to sheet2 automatically.
A: You could record a macro and copy sheet1 to sheet2. and run it as needed. other than that, you ...
|
| macro | 12/31/2007 |
Q: hoping you can help. i recorded the following macro on one of my sheets, it is connected to a click ...
A: Pat, Try this. I just tweaked your code Private Sub CheckBox1_Click() Range("B4:CM4").Select If ...
|
| VBA | 12/3/2007 |
Q: Good Day Sir I have two work books in excel one is a purchase order that needs two account numbers, ...
A: Peter, Not sure what you need - but you simply need to have a column that identifies the month in ...
|
| formula creation for repeated numbers. | 11/14/2007 |
Q: i have series of numbers in cell A1 TILL A50 , i want to analyse in cell C17 that in range A1:A16 ...
A: a little bit complex. use this formula is C17 =CONCATENATE("5 occurs ", ...
|
| Vlookups by Rank | 11/9/2007 |
Q: On wone worksheet I have a list of data, this contains in columb B, A name, then in column C a cash ...
A: assume that your data is in sheet2, and B1 in the sheet3 has the value Top/Bottom (Can easily be ...
|
| Excel VBA Macro help needed | 10/29/2007 |
Q: some have many pages with data). All of these worksheets are in the same 'form' format. I looked ...
A: Angie, I have attached a somewhat more elegant solution that might be of help and you should be ...
|
| VBA Macro Help. Moving specific data from Sheet1 to Sheet2 | 10/29/2007 |
Q: Its a strange one. I know it can be done but I am struggling. I need a macro to read data from ...
A: Ajay, I normally like to avoid Macros where possible. What you need can be done using formulae ...
|
| usage of countif() | 10/29/2007 |
Q: I would like to get the count if the values are >10 and <25 using countif(). Basically, get total ...
A: Sanjesh, To use multiple conditions, I would suggest using Array formulae. If your data is in A1 ...
|
| VBA macro to format data | 10/25/2007 |
Q: I am trying to format an 8 column table of data to a new 3 column format as follows: 8 column data ...
A: Jason, I normally like to set the macro as below as it makes it easy to follow and debug Sub ...
|
| MS Excel VBA Programming Emergency | 10/23/2007 |
Q: I have a spreadsheet that is used to track documents and I want to create a call sheet. For ...
A: Jeanna, Its morning now so not sure if this is later than you needed but it seems that your ...
|
| payback period | 10/22/2007 |
Q: Im wondering how to calculate the payback period using a formula for a question where i have varying ...
A: John, Lets assume you have the values 0 1 2 3 4 in Row 1 (from A1 onwards) and -1000, 425, 300, ...
|
| Conditional Formatting based on sumif? | 10/22/2007 |
Q: I have a range of 50 cells with different dollar values ranging from $200.45 to $4797.24. I ...
A: James, I solved a similar issue on Sep 16th. URL is enclosed ...
|
| Using a macro to update a formula | 10/21/2007 |
Q: How would I create an Excel or VBA macro to automatically update a VLOOKUP formula to change the ...
A: Mike, You can solve this problem elegantly using array formulae. Lets assume that you are doing a ...
|
| weighted | 10/19/2007 |
Q: I need your help once again, I have a table that is 8 rows by 5 columns. each row can only have 1 ...
A: Philip, You mention - First row is worth 5points, 2nd is 3points, 3rd is 1 point, 4th is 0 points ...
|
| Retrieving Data From A Matrix | 10/18/2007 |
Q: I am having trouble getting data from a matrix. I have tried to use and IF,AND statement however it ...
A: Daniel. Vlookups are great to do lookups on tables. they won't work on lookup on a matrix. luckily, ...
|
| locking out cells in rows | 10/18/2007 |
Q: I'm making a spreadsheet to for people to tick off so I can collect data of people passing a test, I ...
A: in the macro - you see the formula If Trim(xls.Cells(row_num, 2)) <> "" Then ct = ct + 1 If ...
|
| locking out cells in rows | 10/17/2007 |
Q: I'm making a spreadsheet to for people to tick off so I can collect data of people passing a test, I ...
A: Philip, Assumption: 1. The 3 columns B - C - D are the columns where you enter the pass fail n/a ...
|
| Formula based on how another cell is formatted. | 10/16/2007 |
Q: I am doing employee schedules and want to track if they have the potential for overtime with how I ...
A: Jaret, There is no way to make a formula based on color. If you really need to do it, I would ...
|
| Formulae | 10/15/2007 |
Q: I am trying write a formulae that will work out any bonus that i could earn and failing miserably, ...
A: Harsh, In sheet 1, lets put the following values in these cells, A2 ->0% B2 -> 5% A3 ->25% B3 ...
|
| Vlookup in VBA | 10/14/2007 |
Q: I have this formula in cell W2 "=Q2-VLOOKUP(M2;C:H;5;FALSE)" in cell W3 ...
A: You could put that statement .Range("W2").Value = Range("Q2") - ...
|
| Pivot Tables | 10/14/2007 |
Q: When using Pivot tables for Company, Account Amounts is there a way to populated the company is all ...
A: Randy, I am assuming that you are using the data after creating the pivot table and ...
|
| conditional formula | 10/11/2007 |
Q: I'm not sure how to go about this -- hope you can help. I'd like to write a function (or create a ...
A: Gloria, I think in 2), you meant P310 -P392, the most robust formula is an array formula. You type ...
|
| Excel Formula for Determining Values | 10/10/2007 |
Q: I use Excel to help me track my weight loss, calorie consumption and calorie expenditure. At present ...
A: Pat, Hope you are doing well. Lets assume that you have the data (weight, BPM) in Columns A and B - ...
|
| Excel extract records | 10/8/2007 |
Q: I have a spreadsheet that includes a payee, description, estimated payments, and acutal payment ...
A: Maureen, There are several ways to achieve it. I am attaching some methods from the most trivial ...
|
| macros in all workbooks | 10/4/2007 |
Q: Me again. The last macro you helped me with works fine in the workbook in which it is installed. How ...
A: Brian, You should be able to access the macro from any sheet. lets say the macro is present in ...
|
| Excel formulas | 10/2/2007 |
Q: I am trying to design a timesheet. In most parts it is simple except when I come to the standard ...
A: Jane, In the example, shouldn't the overtime be 5 hours (8:30-9:00 and 18:00 - 22:30) not 5.5 hours ...
|
| VBA with FORECAST func. | 10/1/2007 |
Q: My problem is that I need do interpolate 368 values. So I wanted to write a macro, which will do ...
A: Try this Option Explicit Public Sub forecast_values() Dim Bcell As Range For Each Bcell In ...
|
| removing empty cells | 10/1/2007 |
Q: If I have a column or row of numbers with some blank cells and I wish to tighten it up, i.e. ...
A: Brian, When you get to Visual Basic Editor, click Ctrl+R. This should show the "Project Window". ...
|
| removing empty cells | 10/1/2007 |
Q: If I have a column or row of numbers with some blank cells and I wish to tighten it up, i.e. ...
A: Brian, 1. Press Alt+F11 2. Click on Microsoft Excel Objects 3. right click and insert module 4. ...
|
| Excel Headers of many sheets | 10/1/2007 |
Q: Kishore, Good Day! Is it possible to change the headers (or the footers for that matter) of many ...
A: Amna, You can click on all the tabs at once - this will group them. Then, you can change the page ...
|
| removing empty cells | 9/29/2007 |
Q: If I have a column or row of numbers with some blank cells and I wish to tighten it up, i.e. ...
A: Brian, Try this code. This assumes that you have the start cell selected when you run the macro. It ...
|
| Excel macros | 9/26/2007 |
Q: I am having some problem working with the macros, let me brief you about my problem, I have an excel ...
A: You probably need something like this: Function reduceFactor() Dim xls1 As Excel.Worksheet Dim ...
|
| Help with Excel Function | 9/24/2007 |
Q: Hi. I dont know if excel can even do this. If i have a column containing many values, is there a way ...
A: Michael, I solved a similar issue for someone else. The URL is attached. ...
|
| Sum columns using HLOOKUP | 9/24/2007 |
Q: I have a datalist with weekly Dates as headers and various cash flow categories along left column. ...
A: Ron, Use this array formula =SUM(IF(MONTH(1:1)=1,IF(YEAR(1:1)=2002,6:6,0),0)) This sums up the ...
|
| Comparisons | 9/20/2007 |
Q: I often need to find similarities between to work sheets. An example is somebody will send me a ...
A: John, vlookup is doable if the comparison is specific to a column - i.e. you are searching for an ...
|
| comparing values | 9/20/2007 |
Q: I have a sheet of data (A1,A3000) and (A:M).Means a matrix of data having columns as A to M and rows ...
A: Arun, couple of questions. 1. is the values being looked up in a particular column? 2. If yes, do ...
|
| Nested functions | 9/19/2007 |
Q: My problem is I need to output Mileage Range base on multiple conditions. Take a look at my ...
A: Theresa, Hope you are doing well. One possibility is: 1. Use cells H2:J19 to enter the data as in ...
|
| formulas | 9/18/2007 |
Q: me again. I just inherited a job from some kind of excel genious. She has our inventory set up with ...
A: Kristen, this should be interesting . the person is using Array formula - a rather advanced topic. ...
|
| finding combination in the selected cells | 9/16/2007 |
Q: I have a problem working in excel. i want to find a particular number(combination) from a Selected ...
A: Mervin, Interesting question. There is an elegant solution that might be of help to you. ...
|
| Excel | 9/14/2007 |
Q: An easy one for you hopefully. EG. Sheet 1, call A1 has a vlookup to sheet 2 and returns a date. How ...
A: Alex, Hope you are doing well. Lets assume that A1 is the cell that you want to change the color if ...
|
| sheet based on another | 9/12/2007 |
Q: I made a sheet that contain job titles with the date they must be done.I want to have them in ...
A: Armin, Very interesting question. Possible Assumptions: Sheet1 has the data 2 Columns A, B -> A ...
|
| arranging data | 9/12/2007 |
Q: I have a 365*48 matrix in sheet 1 and I want to write this as 1 column in sheet 2. So in sheet 2 the ...
A: Suketu, You don't need VBA for this Use this formula from row1 onwards ...
|
| Parsing name fields | 9/11/2007 |
Q: I have a column that contains first name, middle initial (sometimes) and last name. The names are ...
A: Aha, I have just the thing for you. Had a similar issue a while back and found this code on the net ...
|
| Checks comma in rows and notify user when he tries to save the worksheet | 9/10/2007 |
Q: I have this code below which looks for duplicates in row B77 to B132. The other stuff it does is it ...
A: what you could do is before the ActiveSheet.Protect Password:="sjghc", _ ...
|
| More than 7 if statements | 9/5/2007 |
Q: please see example: =IF(E4<=500,E4+1),IF(AND(E4>=501,E4<=899),E4+2) ,IF(AND(E4>=900,E4<=1399),E4+3) ...
A: Hope you are doing well. Interesting question - took me some time to figure it out but this should ...
|
| MS Excel Macro | 9/3/2007 |
Q: I need some help on creating a function or macro which compares two columns and displays the rows ...
A: Hope you are doing well. This should help Option Explicit Function identify_similarities() Dim ...
|
| IF/Then | 8/30/2007 |
Q: What I would like to do if possible is an if/then statement that changes font color. For example if ...
A: Parker, You can definitely achieve it by using Conditional Formatting. Steps 1. Select B1 2. Go to ...
|
| Macro Help | 8/30/2007 |
Q: I currently have a summary worksheet (identical to subsequent sheets that is pulls from but to be ...
A: Josh, A tad bit complicated but this should suffice. Change the sum_sheet_name to the summary ...
|
| Copying Zip Code to each line item in that Zip Code | 8/28/2007 |
Q: Philips but I can't answer his followup question), I've got a large spreadsheet which I was able to ...
A: I think I understand the issue. Assumptions 1) Column A has the number 4342, 4359 (some ID) 2) ...
|
| WorkSheets and coding | 8/27/2007 |
Q: Can you use the COUNTIF function for multiple criteria in different fields? for example below, ...
A: You can simply enter this array formula in E5 =SUM(IF(Worksheets("ETO")!F:F="NEW CUSTOM", ...
|
| combination and calculation | 8/27/2007 |
Q: I have a question dealing with combinations, I have three products and have about 100 accessories ...
A: Shorab, It should be possible. Assumptions -> 1. You have names of the 100 accessories in D1 to ...
|
| WorkSheets | 8/27/2007 |
Q: I just wondering do you know why I keep getting this error: Run-time error '9' Subscript out of ...
A: Ken, The reason you are getting the error is the Metrics is not enclosed. Even then the above ...
|
| Find unique records | 8/24/2007 |
Q: which formula should I use to find unique records in a document? Data->Filter->AdvancedFilter->copy ...
A: Its close to the weekend - so I'll give you a brute-force-method to solve the issue. You can ...
|
| WorkSheets | 8/24/2007 |
Q: I'm currently working on a project using Excel Macro and vba. I'm wondering if you could help me. I ...
A: Ken, Hope you are doing well. To recap 1. Data4Anal has 3 columns. Col 1 has the dates. Col 3 has ...
|
| Excel Queries | 8/23/2007 |
Q: I am trying to run an excel query to extract a trial balance from the database….i have one problem ...
A: This might be of help to you. 1. Create the base query. 2. Edit the query -> Click Next till the ...
|
| Find unique records | 8/23/2007 |
Q: which formula should I use to find unique records in a document? Data->Filter->AdvancedFilter->copy ...
A: When you say that you want it to keep the records live, I suspect you mean that as the data changes, ...
|
| List issue | 8/23/2007 |
Q: My problem is that I want to use this same list on another sheet without having to redefine the ...
A: Create a named range for the cells -> Insert -> Name -> Define you can then use the named range ...
|
| How do I add color to a particular field | 8/23/2007 |
Q: I want to automatically have excel color certain field text and/or highlight if a particular ...
A: If you want to change the formatting of a cell based on the value, try this: Say - Change ...
|
| Conditional Formatting | 8/22/2007 |
Q: I want to format a cell based on whether or not another cell contains any data. I'm using Excel ...
A: Jamie, You can do it using Conditional Formatting. 1. Click on E2. 2. Go to Formatting -> ...
|
| Marco | 8/21/2007 |
Q: i am trying to record a marco whereby it will select an object and make it larger. i can get it to ...
A: Paul, Hope you are doing well. This might help you. Option Explicit Function make_bigger(obj As ...
|
| formatting issue | 8/20/2007 |
Q: I am trying to format cells that have the number 20070819 and make excel recognize that this is ...
A: Tyler, Hope you are doing well. There are two issues a) Why does formatting the cell as yyyymmdd not ...
|
| Excel Feedback Form | 8/19/2007 |
Q: As i do not have any programming experience, i am trying to make an excel sheet which will have the ...
A: If you have limited programming experience, it might be difficult to do what you want to achieve. ...
|
| Further questions on delete rows on protected sheets | 8/19/2007 |
Q: I found that this VBA code is very useful to me: ...
A: Hope you are doing well. Attached is the code that does what you need when you press Shift+DEL. Only ...
|
| COMPLICATED FORMULA FOR EXCEL | 8/19/2007 |
Q: EXCEL: I’ve searched everywhere to figure this out and would greatly appreciate it if this could be ...
A: Charles, Hope you are doing well. I think this solution will work for you. 1. In the place that ...
|
| Excel Command | 8/17/2007 |
Q: good day, I am using random formula in my spreadsheet but since it is random, whenever I have input ...
A: it a little bit complex but doable. you need to use a circular formula assumptions 1. Cell C5 has ...
|
| Help needed | 8/17/2007 |
Q: Bhaskar hi, I am working on an project spreadsheet and I need a series of months based on the start ...
A: Hope you are doing well. Assumptions 1. Sheet1 -> A1 -> start date. A2 -> end date 2. sheet 2 -> ...
|
| cross reference | 8/16/2007 |
Q: This is my first time using this website. I really need your help about cross referencing in excel. ...
A: Jan, There are several ways to tackle your issue but I was thinking about a solution that is ...
|
| average without blanks or zeros | 8/16/2007 |
Q: I want to average a list of numbers omitting blanks or zeros. Please help at your earliest ...
A: If your data is in cells C2:C11, the following should work =IF(COUNTA(C2:C11) + COUNTBLANK(C2:C11) ...
|
| VBA Select Directory | 8/16/2007 |
Q: I'm sure there is a simple, one line answer to this but I have not yet found it! How do I display ...
A: I found this code at one point during my search for a similar problem. Hope this helps Bhaskar ...
|
| Macro issue want to make sheet protected while running macro | 8/16/2007 |
Q: I have a query! Can you help me. I have this code below which does unprotect the sheet and runs the ...
A: Jawad, That is because when you have the "If rng Is Nothing Then Exit Sub" that gets exexcuted when ...
|
| Excel formula | 8/15/2007 |
Q: I have a list of 10 numbers and I want to add the lowest 5 numbers. But I want to exclude zeros . ...
A: You had omitted to mention in your previous emails that the numbers could be blank. Also, if you are ...
|
| Calendar Control | 8/15/2007 |
Q: I am using a calendar control within a worksheet. This is how I want the control to work ... Click ...
A: It depends on how you are showing the calendar. I think you are using a form with the calendar ...
|
| link userforms together | 8/15/2007 |
Q: I'm working on a project using MS Excel vba and i'm just wondering if you could help me out here. ...
A: Ken, Hope you are doing well. It is a bit difficult to visualize what you are trying to do. Where ...
|
| how to make a comand button | 8/15/2007 |
Q: what will be the code for the command button which will enable me to save predetermined worksheets ...
A: Nitin, Hope you are doing well. The solution is a little bit more complicated but possible 1. ...
|
| Excel formula | 8/14/2007 |
Q: I have a list of 10 numbers and I want to add the lowest 5 numbers. But I want to exclude zeros . ...
A: Eric, Hope you are doing well. It can be solved using an array formula. The formula is ...
|
| Having issues with different worksheets | 8/14/2007 |
Q: I am having few troubles. I have this code below. '''''''''''''''''''''''The correct ...
A: Ensure that the code is copied and pasted onto the "this workbook" module in each xls file. then it ...
|
| Excel computing | 8/14/2007 |
Q: I would like to know if there is such solution for this: geting an average value is easy, but what ...
A: Hector, The question seems simple but the answer is much more complicated than I expected at first ...
|
| Having issues for duplicates | 8/14/2007 |
Q: I hope you are doing good. I have one more question to ask as i am facing the problem. when there ...
A: Jawad, I don;t understand the issue. I checked the code and if there are more than 1 occurnaces in ...
|
| About finding duplicates in row B77-B132 | 8/14/2007 |
Q: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ...
A: Jawad, The reason is that the first part of the code that you have added leads to subroutine being ...
|
| Excel VBA/Macro to Find from | 8/14/2007 |
Q: Basically, there are 2 sheets, 1 is the complete database and the other is a generated and processed ...
A: Jad, You don't need VBA/Macro coding to achieve what you need to do. You can simply use Conditional ...
|
| excel for Mac | 8/13/2007 |
Q: i cannot get a macro that i created in excel for pc to work on my mac that has excel loaded on it. ...
A: It should be similar unless you are making calls to the OS (like getting folder information etc). ...
|
| Excel computing | 8/13/2007 |
Q: I would like to know if there is such solution for this: geting an average value is easy, but what ...
A: Hector, There are infinite number of combinations with a given average. The key is to create a ...
|
| Formula for Adding numbers sequence | 8/13/2007 |
Q: I currently need to put jpg files into a database. I have them named 1_1, 2_1, 3_1 etc. Is there a ...
A: Philip, Hope you are doing well. You haven't mentioned how you are using it - via Excel formula or ...
|
| Move the cursor to active cell when detecting duplicates | 8/13/2007 |
Q: I have a small query on the code. Can you also help me that when it picks up the duplicate cells can ...
A: Hope you are doing well. Where you have dup_count = dup_count + 1, add the next line as ...
|
| Prevent Delete | 8/12/2007 |
Q: Good Day Bhaskar, can you please tell me; I have an excel spreadsheet, saved on a local LAN. Is it ...
A: Ryan, This is probably not an Excel question. It depends on the Operating system and the permissions ...
|
| Excel VBA to search Inventory record. | 8/12/2007 |
Q: i want to create a spreadsheet with 2 sheet, first sheet is the inventory record with first column ...
A: Hope you are doing well. You don't need VBA. Just formulae would suffice. Assumptions: 1. Sheet1 ...
|
| excel formula | 8/10/2007 |
Q: I have two tables of data for customers that I need to combine in one table with a single row for ...
A: I am assuming the following: 1. Sheet 1 has the first table. Row 1 has the header and data is from ...
|
| check duplicate data in rows B77-B132 | 8/10/2007 |
Q: I want to ask a question. I dont know how to create a macro to find where the duplicates are in rows ...
A: Attached is the macro that you need to enter the the module "ThisWorkbook". Click (alt + F11) and ...
|
| Checking if row id are filled then saving the worksheet | 8/10/2007 |
Q: I have a small question here as well. Well i have like row number given to my excel sheet which are ...
A: Similar to the one sent earlier, you need to update the code that i sent earlier to the following: ...
|
| Limit of 255 characters in a cell (when copying) | 8/9/2007 |
Q: My application is now up and running in the factory here at Ioline but as is the usual case we found ...
A: Interesting question!!! this function splits the input string based on the input number of ...
|
| SeriesCollection | 8/9/2007 |
Q: Howdy, I hope I can explain this well enough. I am using Excel 2003 to plot a series of graphs on ...
A: My understanding of your problem is this: a. you have a cross-tab of data, say columns are "years", ...
|
| VBA Macro | 8/9/2007 |
Q: Bhasker, I have been working with this macro I created (shown below) which takes information from ...
A: There are a couple of issues with the code a. The input and output files are not clearly defined. ...
|
| normalized distribution function | 8/9/2007 |
Q: I would like to ask you about how to normalize a curve by using excel i want to get a normalized ...
A: I am not sure if I understand your question correctly. Based on your query, attached is some ...
|
| macros | 8/4/2007 |
Q: I have a list of ID numbers across row 1. I get a daily list of some of these ID numbers along with ...
A: You don't need macros for this. assumption: a. Tab DATA has all the IDs from cells A1 to J1 b. Tab ...
|
