You are here:
| Subject | Date Asked |
| Major VBA/Macro Lag! | 2/10/2012 |
| Q: When using the index function if no result is found the cell shows #N/A. Is there any way to hide ... A: The first thing I would do in this case is to get the macro to set the calculation mode of the sheet ... | |
| Excel - Hide #N/A | 2/8/2012 |
| Q: When using the index function if no result is found the cell shows #N/A. Is there any way to hide ... A: I can think of a couple of ways - one is to use conditional formatting to simply hide cells in the ... | |
| Index formula using cell value for sheet reference | 2/7/2012 |
| Q: I am having major problems with naming macros and editing them. The macro name will disapear then ... A: Sorry, mistyped the brackets - I've done it in excel rather than trying to type it this time - try ... | |
| counting colored cells in a spreadsheet | 2/6/2012 |
| Q: how are you? I want to count the number of colored cells in a spreadsheet.By googling, I quickly ... A: my email is aidan.heritage@virgin.net - just in case that helps (a means of getting a file to me for ... | |
| % sign appears spontaneously | 2/3/2012 |
| Q: I have bumped something but don't know what. I am entering numbers in a basic spreadsheet for the ... A: I think you MAY have used the keyboard shortcut to set % formatting (see ... | |
| disabling paste | 2/1/2012 |
| Q: i want to ensure that users can’t paste over this and remove the formats/validations. I am happy for ... A: I'm not 100% happy with code solutions to this sort of problem, as if a user has macros disabled ... | |
| dynamic and dependent formulas | 1/31/2012 |
| Q: I'm trying to do the following, I have weeks of the year and sales and stock for them, so something ... A: see https://www.yousendit.com/download/T2dlWUhpZ2dLVlZESjhUQw for a file explaining some of the ... | |
| Excel Formula | 1/27/2012 |
| Q: I was hoping you could help me with a formula in Excel. I have a spreadsheet that has many number ... A: It depends how many options you have as to the best route - if it's as simple as the three, then an ... | |
| Excel 2010 - show formulas | 1/26/2012 |
| Q: Is there a way to show formulas in only selected cells rather than in all of them. Thanks. A: As far as I know, this has remained constant throughout all versions of excel - you can see them all ... | |
| Importing in Excel 2007 | 1/24/2012 |
| Q: I built a spreadsheet in Excel 2003 that imported information from another spreadsheet. It works ... A: It's not a method I've used, and it does look as though it may have been changed in later versions - ... | |
| Importing in Excel 2007 | 1/23/2012 |
| Q: I built a spreadsheet in Excel 2003 that imported information from another spreadsheet. It works ... A: can you clarify "IMPORT" - basically, there shouldn't be a major difference in the two versions, so ... | |
| Format Function for Excel | 1/21/2012 |
| Q: When I place the following in a macro to name the file including the date - ... A: This macro sub tester MsgBox Format(Now(), "ddmmyy") end sub will run fine - at least, it will on ... | |
| Save a workbook with search function | 1/16/2012 |
| Q: Kindly I have a folder called "Sales target", inside this folder there are many sub folders "Jamal, ... A: The basic structure for a save as macro is ActiveWorkbook.SaveAs Filename:="C:\temp\test.xls" In ... | |
| Need Validation that allows typing only the numbers in a Cell | 1/13/2012 |
| Q: I need to prevent alphabets and alpha-numeric characters in a cell. So I used =not(istext(c1)) ... A: I'd simply go with a number option rather than a formula - whole numbers only between certain bounds ... | |
| Using Text-to-Columns with Cells Containing Alt+Enter | 1/11/2012 |
| Q: I have a CSV file with many hundreds of rows that I split into columns using the Excel ... A: If your data examples are correct, the formatting is an issue as well, in that the .69 relates to ... | |
| deleting digits on right | 1/9/2012 |
| Q: I tried to use the =left(a1,10) to eliminate all the zeros and T in the example below. However, I ... A: I'm guessing you would actually want it to be a date as well - if so you would need to use the date ... | |
| excel spreadsheet | 1/7/2012 |
| Q: I am looking to learn how to create a formula or some other shortcut for the following. Column A ... A: A relatively easy one this time - use a blank column on the "perfect" spreadsheet and put a countif ... | |
| Excel Spreadsheet Autopopulation | 1/3/2012 |
| Q: I am looking to create a spreadsheet for my clients employee benefits enrollment that will ... A: I'm not sure I've got enough information here - but I would use an input form of some description (a ... | |
| Seeking advice | 12/29/2011 |
| Q: sorry, a list of doubts to clear. But should be relatively easy. 1)How do I make a cell to have ... A: 1)How do I make a cell to have show number like 001 instead of 1. 012 instead of 12. Use the ... | |
| sumproduct between two dates | 12/27/2011 |
| Q: I actually have view this question in this forum but unfortunatley was unable to get it to work for ... A: Your method =SUMPRODUCT(--(Date2>=I5),--(Date2<=G5),--(EBillExist2="y")) is correct, and should ... | |
| Manual Input into a Cell Containing Formula | 12/22/2011 |
| Q: Is it possible to allow manual input into a cell containing a formula without losing the formula? ... A: A cell can either contain a value or a formula - putting a value in it would destroy the formula - I ... | |
| IF Function with THEN | 12/20/2011 |
| Q: Im currently using Excel 2011. I would like to use the IF THEN formula where cell G2 (which already ... A: I assume that G2 returns a true false value so =if(g2,c2/sum($d$2:$d$12),0) would do it - however, ... | |
| MS Excel Formula | 12/10/2011 |
| Q: In an Excel Sheet, if I type 6 numbers in a row, I want the same numbers to appear in the box, ... A: I would use conditional formatting for this - have all the numbers in the cells already, but with ... | |
| can i use vlook up with 3 conditions | 12/5/2011 |
| Q: im jst new to excel and i want to know if i can use vlookup with 3conditions. in sheet1 ... A: vlookup will only lookup one value, but you CAN amend the data to handle this - if you create either ... | |
| bulk find/replace | 12/4/2011 |
| Q: I have a column of file names that I need deleted from another spreadsheet. They are image names, ... A: I think this would need to be a macro solution - I haven't quite enough information to go on to ... | |
| food cost | 11/30/2011 |
| Q: am having trouble to get my food price down .please can you help will the formula on how to do is A: The only way excel could help you here is if you had all the prices from each store for each food ... | |
| Retaining Data when page is reset | 11/29/2011 |
| Q: I am working for a charitable organization and am trying to keep track of sales. I thought I was ... A: Makes total sense, and it is perfectly possible BUT will require VBA to achieve it - basically, you ... | |
| get data by sql in excel by vba | 11/28/2011 |
| Q: I am using sql queries to fetch data in excel by vba. usually data fetched in column format. i.e ... A: You haven't said how the VBA handles it, but if it can get a recordset and loop through each record, ... | |
| lookup help? | 11/26/2011 |
| Q: I'm not an excel expert by any stretch so not sure if this is even possible... I have a sheet with ... A: Seems perfectly possible - and it would use Vlookup - see http://www.xlfdic.com/ for a file with ... | |
| macro | 11/25/2011 |
| Q: I need help writing a macro. The macro needs to enter values between 1 and 10 in cell A1 and values ... A: I'm not sure I have the full information here - based on the data given, it sounds like a random ... | |
| Matching Columns row by row | 11/22/2011 |
| Q: .. I have two columns of data that I need to match row by row vs. column to column since some of the ... A: I'm not 100% sure I follow what you need to do, BUT if it's a case of highlighting data where E and ... | |
| insert space | 11/18/2011 |
| Q: My name is John and i want your help. I have hundreds cells with 13 numbers each one. For example: ... A: There are two methods depending on what you need it for! To physically alter the cells, use ... | |
| Excel - Conditional Formating | 11/18/2011 |
| Q: Preparing member database to identify those who have attended events and those who have not. We ... A: sounds a little tricky - my only thought would be to use a vlookup via a "translation table", this ... | |
| Locking specific cells | 11/16/2011 |
| Q: I am making a worksheet for my company and will be uploading it to my Sharepoint site, where members ... A: All cells are locked by default, but the locking only takes effect when the sheet is protected - so ... | |
| merging excel files. | 11/14/2011 |
| Q: In excel I have made a calendar which I would like to give a copy of to each of my colleagues. I ... A: It's certainly possible, though it would help to see a copy of the calendar - do we have both ... | |
| deleting words from excel | 11/2/2011 |
| Q: the cells in column A have quite a bit of text in them, which i will give you an example below, i ... A: you've already asked me this question, but possibly the answer link didn't get through to you - the ... | |
| last 3 rows | 11/1/2011 |
| Q: Hope you doing fine, Ok in cell A1=2,A2=1,A3=4,A4=3,A5=5,A6=3 and in cell B1:B3 ... A: I cannot think of a way to do this with only one formula, but I have come up with a 2 formula ... | |
| Copy populated cell, leave blank if empty | 10/29/2011 |
| Q: I am copying a time format cell (='CLIENT #1'!$C$10) from another worksheet in same workbook. The ... A: You have a number of options - the value you are seeing is the time equivalent of zero, which is the ... | |
| excel date | 10/28/2011 |
| Q: I would like to know how to stop the auto advance when dragging a date say down 20 lines, instead of ... A: no problem - there are various ways - when auto-filling, excel will do it's best to work out the ... | |
| Auto copy between cells | 10/27/2011 |
| Q: I need to find out how the info from once cell can automatically send to selection cells on ... A: At it's simplest, a formula on sheet 2 referencing sheet 1 would do it - simply have these ... | |
| deleting words from excel | 10/26/2011 |
| Q: not sure if you can help. I have an excel sheet that in each cell there is quite a bit of text, ... A: http://www.dailydoseofexcel.com/archives/2007/02/21/find-position-of-first-capital-letter-in-a-strin ... | |
| excel spreadsheet | 10/24/2011 |
| Q: I am looking to learn how to create a formula or some other shortcut for the following. Column A ... A: it depends a BIT on how many searches you need to do - the SEARCH worksheet function would mostly do ... | |
| Macros | 10/21/2011 |
| Q: Hoping you can help. I want to set up a macro where if one of our kits are put in the field, then it ... A: Do you have a list on the sheet somewhere of the items that make up the kit - assuming you do, the ... | |
| Time card - entering time in excel | 10/20/2011 |
| Q: I am working on entering a time card in excel but don't seem to get the correct total time at the ... A: No problem- you will basically need to compare each END time with it's equivalent start time, and if ... | |
| Time card - entering time in excel | 10/19/2011 |
| Q: I am working on entering a time card in excel but don't seem to get the correct total time at the ... A: I'm always slightly wary of building applications that accept incorrect data - as I mentioned, it is ... | |
| Time card - entering time in excel | 10/19/2011 |
| Q: I am working on entering a time card in excel but don't seem to get the correct total time at the ... A: The formula is fine BUT the time is wrong - you have 2:00, which is 2am - so effectively you left 6 ... | |
| Help with auto-populating in execl spreadsheets | 10/19/2011 |
| Q: I would like to input data in a master excel sheet and that master sheet information automatically ... A: Does the data need to be retained in the master sheet, or is tht ONLY being used for input? If I ... | |
| Relative Cell Reference | 10/17/2011 |
| Q: i compile a day-to-day sales database, per customer on one workbook but separated into different ... A: I think that probably the INDIRECT worksheet function is going to be the one you need to use. ... | |
| Excel drop down lists | 10/15/2011 |
| Q: How can I link two drop down boxes together I.e. Box one contains name of product and box two ... A: From the description, it sounds as though the second box will return a single value based on ... | |
| EXCEL PROBLEMS. | 10/14/2011 |
| Q: I want to ask if in excel a no's contains space as well as + or _ or - sign how can it be deleted ... A: at it's simplest a search and replace (three times over) would do it - you could also use a formula ... | |
| excel control/activeX toolbox | 10/12/2011 |
| Q: I have been searching this on the web and all by myself in excel but i'm still yet to find the ... A: There are two versions of controls in excel 2003 - the forms toolbar is the older version, the ... | |
| Pivot Table report filter in browser shows Multiple Selections improperly | 10/12/2011 |
| Q: (Using Excel v2007.) I have a simple Pivot Table uploaded to the web via Windows Live Skydrive. ... A: I'm afraid I have no experience of this - it SOUNDS as though it's an issue with Windows Live ... | |
| Vlookup formula for oposite columns | 10/11/2011 |
| Q: can you please let me know how can I set vlookup formula to look into column B find value and return ... A: Vlookup won't work like that - one solution is to add column C with a formula of =a? where the ? is ... | |
| Pivot Table report field formatting | 10/10/2011 |
| Q: I have Excel v2007. Using a normal pivot table, I have a single field in the report filter area. I ... A: I've not done a lot with pivot tables in office 2007, and my tables TEND to be fairly unformatted. ... | |
| how to add certain values | 10/10/2011 |
| Q: i want to add just certain values from a sheet. A=business type...in the cells of column "A" some ... A: You could use a pivot table to get a complete summary, or you could use SUMIFS (in excel 2007 or ... | |
| Calculation on SLA | 10/10/2011 |
| Q: i need to know how we can calculate the Turn Time. For Example : If the process start time is ... A: I've answered various questions related to SLA, and spent about a week in a previous job devising a ... | |
| Using the MAX function | 10/7/2011 |
| Q: I am using Excel 2007. I have attached an image, please refer to it for my question. I want the ... A: There didn't seem to be a question attached, but I'd do this with an array formula ... | |
| MAX Function | 10/5/2011 |
| Q: I have a cell that says: "=MAX(IF( AD1:AD7 <> "Sat", IF(AD1:AD7 <> "Sun", AE1:AE7)))" (I'm printing ... A: As long as it's working that is fine - though the logic of the statement will check ae if the value ... | |
| Copy cells to another worksheet based on a value | 10/5/2011 |
| Q: Good Day to you Aidan, (vba question for Excel 2010) I was looking for a macro and found your ... A: Your macro was quite close, but you have used N in a confusing way, and hadn't specified the type of ... | |
| Macros | 10/5/2011 |
| Q: Your question was I entered date: 01-05-2009 in a1 cell and 01-08-2011 in b1 cell My Problem / ... A: does this actually have to put a formula in place - surely it would be simpler to do dim N as ... | |
| MAX Function | 10/5/2011 |
| Q: I have a cell that says: "=MAX(IF( AD1:AD7 <> "Sat", IF(AD1:AD7 <> "Sun", AE1:AE7)))" (I'm printing ... A: I've copied your formula and entered some random values into the other cells, it returns the maximum ... | |
| MAX Function | 10/5/2011 |
| Q: I have a cell that says: "=MAX(IF( AD1:AD7 <> "Sat", IF(AD1:AD7 <> "Sun", AE1:AE7)))" (I'm printing ... A: When I'm entering the formula I'm getting a valid result, though I haven't got SAT or SUN in any of ... | |
| Merging 2 excel worksheet | 10/3/2011 |
| Q: I was hoping you can help. I have two excel worksheet and I am trying to merge them together.One is ... A: I'm not clear what you need to merge, BUT the vlookup function would PROBABLY be the way to bring ... | |
| IF statement | 10/3/2011 |
| Q: I need a formula that says the following: If I55>37.5 then K55 will = the >than amount of 37.5 in ... A: It SOUNDS as though you want I55 to contain both a value AND a formula, which it cannot do - IF the ... | |
| Hide Sheet and append | 10/3/2011 |
| Q: I've created a macro to capture time in one sheet and copy the data to another sheet, I need to have ... A: It rather depends what the macro does - a macro can put data on any sheet whether hidden or not, but ... | |
| Copy cells to another worksheet based on a value | 10/2/2011 |
| Q: Good Day to you Aidan, (vba question for Excel 2010) I was looking for a macro and found your ... A: my original macro sets a value to the variable n n = ... | |
| Excel Formula.. | 10/1/2011 |
| Q: With the Below data formulate a report as show in the attachment 1. it sould select the Staff ... A: There are a number of useful tutorial sites (search for excel 2007 pivot table) but this one looks ... | |
| words with values | 9/30/2011 |
| Q: I have a very unique situation I am discovering. I have asked for help but so far people seem to not ... A: vlookup would form the basis as it's the means of translating the colours into numbers - I'm ... | |
| Excel Formula.. | 9/30/2011 |
| Q: With the Below data formulate a report as show in the attachment 1. it sould select the Staff ... A: It's very hard to see the detail in the attachment, but I would tend to do this with a pivot table - ... | |
| Excel Problem | 9/29/2011 |
| Q: Good morning Aidan, I hope it is ok but I emailed you direct with a problem that I have with excel. ... A: Either is fine - direct does allow the attachment of files (which you have done!), via here gives ... | |
| Limiting validated list items | 9/29/2011 |
| Q: I'm trying to create a scheduling spread sheet in Excel. I would like to have drop down lists using ... A: The data validation would have to use a list, which would not allow you to have a rule relating to ... | |
| Excel drill down to pivot table data issue | 9/28/2011 |
| Q: I have the following issue: I have raw data which has been converted into a pivot table. On a ... A: The vlookup, or rather excel, is functioning as designed here - you are seeing the dependant cells ... | |
| Filtering | 9/26/2011 |
| Q: How are you? I've been trying to organize my data rows, hope you can assist me. I have a list of ... A: it sounds as though it needs an array filter - did you want to display MULTIPLE dates? See ... | |
| Pick out data in column 'B' according to its description in column 'A.' | 9/21/2011 |
| Q: I am running Excel 2011 (14.1) for Macintosh. I am doing research on course requirements of various ... A: (question found in the question pool) 2 methods spring to mind - the first would be a pivot table, ... | |
| excel linking with source sheet | 9/18/2011 |
| Q: Goodmorning, I am working for a Nationalised Bank and posted in a zonal office where we have 70 ... A: If the target data is multiple sheets, then this is (as far as I know) going to need VBA to achieve ... | |
| Excel - Function for time format | 9/17/2011 |
| Q: Aiden, I am working in world time zones; in doing so I am entering the times on a sheet as 02:30 - ... A: To excel, time and date is simply a series of numbers - with 1 being 1 January 1900, 2 being 2 ... | |
| Words with number value | 9/16/2011 |
| Q: I am a teacher and I created an excel sheet to grade behavior. The kids are assigned a color based ... A: You mentioned 5 colours and 6 values, but apart from that.. I'd use VLOOKUP to translate the ... | |
| Vlookup Multi search | 9/15/2011 |
| Q: ok I need a vlookup that can search for eg, A1&B1 on sheet1 and search a colum on sheet2. If it cant ... A: It will be a bit messy, but ... | |
| Creating cells with either-or results and no circular references? | 9/14/2011 |
| Q: I have a real tricky one here I think. I have a spreadsheet with three columns that need to only ... A: I'm assuming that the drop down lists are datavalidation, which makes it a little tricky - because ... | |
| Excel 07 Macro To Find Non Duplicates | 9/13/2011 |
| Q: I want to compare two columns in excel to find no duplicates.. I have 2 columns with 2849 entries in ... A: I would do this with formulas personally - a COUNTIF formula will enable you to get the entries that ... | |
| min if statements | 9/12/2011 |
| Q: It worked!! You are a genius that's for sure. I have one follow up question. In your statement how ... A: =MIN(IF(F343:F345=0,99999,F343:F345)) says take the minimum value from the following list IF the ... | |
| data connection | 9/10/2011 |
| Q: I would like to set up a data connection to have all the data on the page link below directly ... A: email address is aidan.heritage@virgin.net - no problem with screen shots! data ribbon far left, ... | |
| IFAND | 9/8/2011 |
| Q: I need to create a formula that gives me the following result: if cell B19=YES and cell B20=YES, ... A: Your formula looks almost right, but you have the structure slightly wrong =IF(AND(B19="YES", ... | |
| Show value on tiered information. | 9/6/2011 |
| Q: I have a list of hundreds of product lines (portfolio of products on a company). We are allowed to ... A: Not clear if you have one spreadsheet per customer (at which point it's a simple sumif) or if you ... | |
| data connection | 9/5/2011 |
| Q: I would like to set up a data connection to have all the data on the page link below directly ... A: Sorry for the delay in replying - hadn't got access to a PC with office 2007 on it. Use the DATA ... | |
| Excel2007-filter-unlocked cells-no vba | 9/4/2011 |
| Q: Hope you can help me. Lets say i want to lock the first 10 rows in a sheet, and the rest of the ... A: As long as the auto-filter is set (to show the drop down choices) BEFORE protecting the sheet, you ... | |
| Clearing Cells after printing | 9/1/2011 |
| Q: I have a spreadsheet that I enter info in, print and then need cleared to enter new information in. ... A: There isn't an after print event, but the easiest fix would be to create your own macro that printed ... | |
| Formula for adding dates | 9/1/2011 |
| Q: I have a spreadsheet with a large amount of information. One of the columns is the date the repairs ... A: I'd have a cell which had the formula =repairdate-day(repairdate)+1 where repairdate is the cell ... | |
| Min if and max if statements | 8/31/2011 |
| Q: I'm using Excel 2010. I'm trying to get mins and maxs but I need to exclude any zero values as the ... A: max shouldn't be a problem as a max of a range containing zeros would ONLY return zero if that was ... | |
| Xcel | 8/29/2011 |
| Q: Please can you help me with Xcel: I have a dataset of samples taken every second. I need to take ... A: =OFFSET($A$1,(ROW()-1)*10,0) A1 is the starting cell with your data - so amend this as required ... | |
| If and then | 8/26/2011 |
| Q: I am using a dynamic pivot table to tell me who is working and when. I want a cell (let's say F8 in ... A: Seems possible, though not sure what you wanted to happen if you had MORE than one rota'd day (as is ... | |
| Excel Formulas | 8/25/2011 |
| Q: I hope you can help me. I use Excel 2007 and I have to create a spreadsheet that shows total hours, ... A: I'm not certain that I've got enough information to go on Question 1 - surely the totals are totals ... | |
| Word to Excel | 8/25/2011 |
| Q: I need macro code to extract some fields of data in word and to paste it in columns of excel file. ... A: The basic concept then would be (I think) to have the macro in word. This macro to open each file ... | |
| excel creating temp files | 8/24/2011 |
| Q: excel is creating numerous temp files each time something gets saved. Running avast antivirus??? ... A: It sounds like a failed save - the save process is write the file to a temporary file once done ... | |
| Word to Excel | 8/24/2011 |
| Q: I need macro code to extract some fields of data in word and to paste it in columns of excel file. ... A: tables are referenced by number, which is therefore prone to problems, so I'd be inclined to ... | |
| How to calculate total hours in different days for different employees? | 8/14/2011 |
| Q: Excel version: 2003.I have an excel sheet where there are many employees that worked different ... A: vlookup would return the first matching value, so probably NOT what you want - it's not clear what ... | |
| Too many arguments in Excel 2007 | 8/5/2011 |
| Q: I am using Excel 2007. I have this formula where I would like to replace: ... A: It was rejected by the original expert as one to put into the question pool - I am painfully aware ... | |
| Autofilter base on cell value. | 8/4/2011 |
| Q: I have recently been trying to advance my knowledge in excel. I'm using 2003, and trying to filter ... A: The code that Tom gave looked to me as though it would do the job you want but I would ensure the ... | |
| Excel array issue - sumproduct within sumproduct? | 8/3/2011 |
| Q: I am trying to automate reporting on a dataset of variable size, that i extract from a system on a ... A: it will work BUT only if you have both halves of the formula referring to the same size array - I'd ... | |
| Vlookup Variation | 8/2/2011 |
| Q: I have two sheets of data. I want to lookup a value from sheet 1 on sheet 2. If it matches then I ... A: still sounds like a normal vlookup to me! I would use the COLUMN worksheet function to determine ... | |
| copy function | 8/1/2011 |
| Q: I would like to define cells where "if cell H3 is "_" then, copy cells in sheet2" Basically I will ... A: I'm not clear what it is you want to do - is there a choice of answers, if so a vlookup would be ... | |
| Attendance Report | 8/1/2011 |
| Q: In that I need the following help. Row Cell D7 to AH 7 contains Date from 1 to 31. 1. Column cell B8 ... A: Something like this Sub GetAbsentees() Dim n As Long, m As Long, outvar As Long, absent As Long Dim ... | |
| Import | 7/30/2011 |
| Q: Happy to find you to place my quesion My question is how I can import a table in MSWord into Excel ... A: The simplest method would be something like Dim AppWd As Object Dim looper looper = 5 On Error ... | |
| chaging the colour of the columns based on a single cell value using excel 2007 macros | 7/27/2011 |
| Q: I am using Excel 2007.What you said is working fine sir.But the issue is If i have Total 31 rows , i ... A: Firstly, select all cells with formulas (or simply select all by clicking the square at the top left ... | |
| incrementing numbers | 7/24/2011 |
| Q: Range("K23").Select Range("K23").AddComment Range("K23").Comment.Visible = False ... A: I'm not clear what it is you want to increment here - are you making all cells in K have a comment ... | |
| chaging the colour of the columns based on a single cell value using excel 2007 macros | 7/23/2011 |
| Q: I am using Excel 2007.What you said is working fine sir.But the issue is If i have Total 31 rows , i ... A: format painter should work fine - you have referred to doing columns, so you cannot miss any as you ... | |
| Copying whole rows based on a test word | 7/22/2011 |
| Q: I have an enormous product list that needs to be segregated by sales person. The Part number looks ... A: I would do the following (1) create a new column which contains the first two characters of the P/n ... | |
| Variable Rate Spreadsheet | 7/21/2011 |
| Q: Night, Saturday and Sunday rate only have two input example start 22/07/2011 16:00 finish 23/07/2011 ... A: Always tricky things I find - presumably a shift can extend across any 24 hour period, so could be ... | |
| Excell check boxes | 7/20/2011 |
| Q: Hey there, I'm trying to make a form for work to make logging patients easier. I want to make a ... A: you haven't specified version of excel which does make a difference - in excel 2010 you would use ... | |
| Pressing Enter in Text Box that is in locked spreadsheet | 7/19/2011 |
| Q: I am creating a form in Microsoft Excel 2010 that needs to be locked down to protect the formulas ... A: I've re-read the question, and managed to find my machine with office 2010 on it, both of which help ... | |
| Adding Minutes | 7/18/2011 |
| Q: I am trying to add a row of minutes and seconds, I've reviewed answers on line, but none has worked, ... A: You shouldn't get a value error adding large numbers though you might find it hard to see the ... | |
| Difficult count in excel | 7/18/2011 |
| Q: I am a school teacher in Sheffield UK and have been designing a tracking document for the English ... A: Greetings from Stevenage, North herts, UK! This is indeed tricky, but I think achievable - I would ... | |
| Cell Color Formula | 7/13/2011 |
| Q: I am trying to have a cell background made differnt colors for 12 project manmagers on an excell ... A: Technically, conditional formatting allows 4 variables (the base colour of the cell being the 4th ... | |
| numbers into text | 7/12/2011 |
| Q: sir i want to know how to convert numbers into text like as at the end of invoices and quotation we ... A: It WILL need VBA - my own routine is set out below - to use this, open the VBA editor (Alt F11, use ... | |
| excel function prb | 7/12/2011 |
| Q: i m stucked in a problem here is the detail i have some names in cell A1 with data validations like ... A: You can use the VLOOKUP function to do this - assuming that the ID Card numbers exist in a table ... | |
| Excel formula | 7/8/2011 |
| Q: i am doing a budget spread and i do not know how to make a formula for this eg: B ... A: Question is not clear - it sounds like it's an IF statement but I don't know what the logic is if B ... | |
| Too many arguments in Excel 2007 | 7/8/2011 |
| Q: I am using Excel 2007. I have this formula where I would like to replace: ... A: It's very difficult to answer this with such a long formula, so my first process would be to try to ... | |
| Compare Two Colums | 7/1/2011 |
| Q: I have two colums that I need to compare. Column A has the hostname and domain (ex: ... A: Sorry for misunderstanding - more complicated, but still do-able ... | |
| Compare Two Colums | 6/30/2011 |
| Q: I have two colums that I need to compare. Column A has the hostname and domain (ex: ... A: I would do this in two steps - using column C FIRST to get the data =IF(LEFT(A2,LEN(B2))=B2,A2,B2) ... | |
| EXCEL SPREADSHEET | 6/27/2011 |
| Q: I HAVE A SPREADSHEET WITH THREE COLUMNS AND I AM TRYING TO FIND A NAME THE QUICKEST WAY POSSIBLE. ... A: It's certainly possible, but I'd need more information to provide a meaningful answer - the quickest ... | |
| Pre existing phone #'s | 6/23/2011 |
| Q: I have 1500 pre existing phone #'s with all kinds of variations. How can I change these Phone #'s to ... A: I'm not sure what you mean by the special format phone number, but it SOUNDS as though you have a ... | |
| Multicolor Date Format | 6/22/2011 |
| Q: I am using Excel 2007. I want to format dates in multicolor. I can do it in text format but cannot ... A: As far as I know you won't be able to do this, though it is possible someone else knows differently! ... | |
| Wrapping text in a check box control | 6/17/2011 |
| Q: I am creating a spreadsheet in Excel 2007. I have a limited amount of space and the instruction ... A: As far as I know with form controls this is automatic - once you have edited the text and clicked ... | |
| Excel and arrays | 6/17/2011 |
| Q: Hope you are doing well. After two years, I see myself coming to you again for some assistance. ... A: I'm not sure WHEN I started doing this, though it was definitely more than 10 years ago! Your two ... | |
| Macro | 6/15/2011 |
| Q: How would I re-write this macro to use on any size workbook? By size I mean number of worksheets in ... A: No, sorry for not being clearer Sub FORMAT1() ' ' FORMAT Macro ' Macro recorded 5/8/2008 by Edward ... | |
| Excel | 6/14/2011 |
| Q: I have an excel formula question, for IF statement and ISNUMBER function. I am trying to write a ... A: The logic is fine, it depends on what you want the result to be =and(test1,test2,test3) requires ... | |
| Excel-Formula | 6/14/2011 |
| Q: I am working in a excel database using excel 2007. I would like to know how a row can be ... A: I would select the first row and set the formulas =AA1="YES" (when entered in row 1) and ... | |
| combining conditional | 6/13/2011 |
| Q: "hola.tengo 18 números(01-18)y participo de un evento en el cual se extraen 6 (seis)de ellos,mi ... A: It sounds as though you basically want a lottery type of program - there are 18564 possible ... | |
| Excel error warning box | 6/13/2011 |
| Q: How do I remove error warning popups in excel? I unchecked it in excel options and still get the ... A: I SUSPECT that you may have deleted rows on the template - if for instance you delete rows 1 to 10, ... | |
| Excel error warning box | 6/12/2011 |
| Q: How do I remove error warning popups in excel? I unchecked it in excel options and still get the ... A: Some warnings cannot be removed - the exact wording of the message would help, but if it relates to ... | |
| arrays or formulas | 6/10/2011 |
| Q: How do I set my spread sheet up to count columns of words (say different workshops) but ignore blank ... A: I'd be inclined to NOT use a formula for this but use a pivot table as this would enable you to ... | |
| importing emails to Excel | 6/9/2011 |
| Q: My organization uses online forms to let people apply for our services. The system generates an ... A: Yes, it's possible - though it would PROBABLY need a macro - it's presumably a question of reading ... | |
| Merging data from excel sheet into excel forms | 6/7/2011 |
| Q: Although each line has unique data on it, I need to merge multiple lines of data with a common field ... A: I'm not aware of an easy way of doing this, though in theory there should be - ... | |
| Preparing a checklist in excel | 6/7/2011 |
| Q: Hope you are doing great. looking for small help from you.I am trying to make a check list in ... A: Sorry about the delay in replying - my daughter has been unwell (nothing serious, but it's made it ... | |
| combine grouped line shapes | 6/6/2011 |
| Q: I'm using Excel 2003. I'd like to combine grouped line shapes, for instance a tetromino "L" shape ... A: You can certainly group shapes, but this simply creates a group of shapes, not a new shape - so a ... | |
| special search and movie in excell | 6/4/2011 |
| Q: my name is nasim I'm sorry i am newer in English.how can i open movie in excell . Is there any way ... A: I'm afraid this seems to be somewhat out of my area of expertise, though it may be that I still ... | |
| Excel lists | 6/3/2011 |
| Q: How do you add another row to a list that has already been created? I've validated the new row but ... A: I'm GUESSING that this is a list used for data validation - however, the same is BASICALLY true for ... | |
| IF function | 6/2/2011 |
| Q: I've been using the IF function and came across a problem. What I want is something like this If ... A: Ok, in that case I'd use a MATCH function to get the number, and then an index function to translate ... | |
| Date function | 5/31/2011 |
| Q: Hallo Aidan I'm assisting my wife with a work book consisting of 12 work sheets, representing the ... A: The DATE function would do it for you =date(year,month,day) I wouldn't necessary have the data ... | |
| IF function | 5/30/2011 |
| Q: I've been using the IF function and came across a problem. What I want is something like this If ... A: The limitation of 7 nested brackets is a system constraint, so the way round it is to find an ... | |
| IF function | 5/30/2011 |
| Q: I've been using the IF function and came across a problem. What I want is something like this If ... A: Your question has a logic error in it, as you seem to want both a value and a dependant formula in ... | |
| Excel Macro | 5/26/2011 |
| Q: I need a macro that will take a highlighted data set like the one shown below and sort it a specific ... A: I think I follow this, and I THINK it's probably fairly simple (doing a 3 way sort would seem to be ... | |
| macro for excel to internet based portal | 5/26/2011 |
| Q: sir, I have one portal wherein I have to upload various daily reports having different file name. ... A: Excel can certainly do a SaveAs text file - so it could create a file with the data you need. What ... | |
| consolidating multiple data ranges by using PivotTables | 5/25/2011 |
| Q: Apart from using the PivotTable and PivotChart Wizard, is there any other method to consolidate ... A: The only way I know to create a pivot table is via the wizard - I do use pivot tables a lot, but ... | |
| formula in a column using VB | 5/23/2011 |
| Q: i am trying to put a formula using in VB such that AB = (C3+D3*2^32)-(C2+D2*2^32) The formula ... A: difficult to follow from the picture (my email if it helps is aidan.heritage@virgin.net), and I'm ... | |
| Postcode analysis | 5/20/2011 |
| Q: i am trying to calc postcodes by multple criteria, using excel 2003. problem is i need to report on ... A: Wouldn't a pivot table be easier? Use a formula to split the postcode into the bit before the space ... | |
| Scatterplot | 5/18/2011 |
| Q: I'm just wondering if there is any way to insert conditional formatting into my scatterplot. I have ... A: A scatterchart is a chart, and charts don't have conditional formatting -but that doesn't mean you ... | |
| VBA | 5/18/2011 |
| Q: Hope all is well. We have a template that must be completely filled out for the next person to do ... A: You could even stop them saving it! This simple routine counts blank cells in a range Sub tester() ... | |
| Invoice Automation | 5/15/2011 |
| Q: I have a invoice template in excel and would like to automate it by including a button to save a ... A: My coding already increases the number by one - it runs on the OPEN event of the workbook - the ... | |
| Use a function to determine the Range() value | 5/11/2011 |
| Q: I've come up with a function that finds the exact row and column of a cell: ... A: OK, it's clearer now - you had mentioned a function, and VBA so I assumed it was a VBA function. As ... | |
| Checklist & Date Recording Excel spreadsheet | 5/6/2011 |
| Q: I'm trying to find the most effective way to keep an excel database up to date for multiple users. I ... A: Sorry for the delay in replying - somehow this question didn't appear on my list to answer until ... | |
| Missing components error message | 5/6/2011 |
| Q: I have used a Microsoft Home Publishing 2000 program for several years to create greeting cards for ... A: AllExperts is a good place to come for answers, but at top level you can navigate through different ... | |
| Missing components error message | 5/5/2011 |
| Q: I have used a Microsoft Home Publishing 2000 program for several years to create greeting cards for ... A: A few problems here - firstly the picture is too small for me to read, but secondly and more ... | |
| Remove duplicates from combo box | 5/3/2011 |
| Q: Question. I created a spreadsheet/form that has several combo boxes linked to my main data form. ... A: I think you would need an array filter that was used to return only ONE entry rather than the ... | |
| Replace function | 5/3/2011 |
| Q: Using Excel 2003 and I would like to replace the cells containing B,C or D in Column A with an X ... A: =if(or(a1="B",a1="C",A1="D"),"X",a1) would be the function I'd use - this would go into a different ... | |
| jumping typeing letters | 5/2/2011 |
| Q: I jump got a new hard drive in my ... I have Windows XP Professional..when I type anything,the ... A: The keyboard is a function of the operating system, and as you have mentioned typing in (I assume) ... | |
| Conditional statement for combobox | 4/29/2011 |
| Q: I have one combobox in the sheet with dropdown text in the combobox, now i need to use if statement ... A: It SOUNDS like you simply need to use a vlookup function in the "next cell" to pick up the text ... | |
| Data Set | 4/27/2011 |
| Q: I was wondering if you could help me. I have a data set: column A is the date (Sept 27-Oct 5), ... A: It SOUNDS as though it's basically a multiple condition countif - you want to count all instances of ... | |
| Passing parameters to a Macro in Excel | 4/25/2011 |
| Q: I am trying to somewhat automate a process that currently prompts a user to enter 4 parameters prior ... A: Yes, this is easy enough dim VarA,VarB,VarC,VarD Open "c:\temp\octopusreports.txt" For Input As #1 ... | |
| Calculate Business Date | 4/21/2011 |
| Q: I want to calculate a date based on a constant day each month. If I have a task to be performed on ... A: The WORKDAY function is the one you are going to need - I've copied the data from help. I'd use ... | |
| Problem with file | 4/18/2011 |
| Q: I have an Excel file that is a collection inventory. When I click on it to open it, a window pops ... A: This question is a little confusing flash drives are only available when plugged in - but you ... | |
| optimizing named ranges | 4/18/2011 |
| Q: I have an excel application with lots of named ranges ( > 5000) A lot of these have to be cleared on ... A: You possibly don't need an algorithm - simply use this syntax Range("Range_A", ... | |
| Spin button decimal increments | 4/15/2011 |
| Q: I can set up a spin button with whole numbers but I need to do it to decimal points 0.1, 0.2 etc, ... A: The spinner control will only do whole numbers - I would suggest that you link this to a cell that ... | |
| Macro to show a number inside a box | 4/13/2011 |
| Q: I have an excel sheet in the form of a list. The rows are colored either green or yellow. I have two ... A: You have to make an actual reference to the object - personally, I would be inclined to have the ... | |
| Macro to show a number inside a box | 4/13/2011 |
| Q: I have an excel sheet in the form of a list. The rows are colored either green or yellow. I have two ... A: This VBA user Defined Functions will enable you to do what you want - you didn't specify what sort ... | |
| Excel | 4/13/2011 |
| Q: I have 4 columns in an Excel spreadsheet that contain data from a survey, the questions that ... A: =6-SurveyResult (where SurveyResult is the cell reference) in a seperate column would be the ... | |
| Widen Data Validation List Comprised of Merged Cells | 4/12/2011 |
| Q: I apologize for previously not being more clear in my question below. Your answer provided runs the ... A: It's a question of limiting what happens, so maybe something like if target.column>7 and ... | |
| lookup the value in column 3 | 4/12/2011 |
| Q: cell A1=4,cell A2=3,CELL B1=2,CELL B2=1,CELL B3=4,CELL B4=3,CELL C1=1,CELL C2=3,CELL C3=2,CELL ... A: I created the formula by using your sample data to ensure that I gave you the right formula - it ... | |
| array | 4/11/2011 |
| Q: I am trying to get my head around an array but can’t quite get there – I am using Excel 2003. I have ... A: sumproduct would work if you wanted to count or sum values, but otherwise you are right it needs an ... | |
| about entering figures into cell | 4/10/2011 |
| Q: 23457E+16) I do not want excel to display the figure as this and I want excel to display the figure ... A: having the cell formatted as a number is the correct way to go, but I'm confused about zeros being ... | |
| If then? | 4/10/2011 |
| Q: Howdy, I have figures in A and B columns on two spreadsheets. If A1 April is larger than A1 May, ... A: I'd calculate the difference by using the ABS function which removes the sign =abs(April - May) ... | |
| Widen Data Validation List Comprised of Merged Cells | 4/9/2011 |
| Q: Aiden, You answered a question previously in 2009 from someone else with the following: "Answer ... A: Yes, you can use various methods - in this case, it is only columns from H to AC that are being ... | |
| Excel VBA, to compare 2 columns | 4/9/2011 |
| Q: I am trying to find and Highlight Common value of each cell of Col A against each cell of Col I. ... A: I'm guessing you will want to filter them, so I'd use a formula - it's a simple countif ... | |
| Excel Formula | 4/8/2011 |
| Q: I am using Excel 2003. I have one sheet called "Logs" Column A contains the reply type selected ... A: =SUMPRODUCT(--(Logs!A1:A3000="Chief Executive"),--(MONTH(Logs!e1:e3000)=4)) would give you the ... | |
| Profit Margin Formula | 4/7/2011 |
| Q: I hope you can help as it appears I have been doing my job wrong - I have a spredsheet that I use to ... A: It can be done - I think it's more a lesson in maths (for me rather than you!) rather than excel. I ... | |
| need clarification on references | 4/7/2011 |
| Q: I have a formula as below.. ... A: creating a new name doesn't change the name of the old name, it simply gives you a duplicated named ... | |
| VBA 2003 | 4/6/2011 |
| Q: I appreciate it must be extremely difficult the way I ask this question, I'm getting the error: when ... A: I'm happy to look at the actual workbook (aidan.heritage@virgin.net) BUT I suspect the problem is ... | |
| return value of the index of column | 4/6/2011 |
| Q: Cell A2:A21 =RANDBETWEEN(10,27), CELL B2:P2 = A,B,C....O respectively, cells B2:O21 ... A: I think I've followed this - I assumed that the column names are ACTUALLY in row 1 as you have ... | |
| Find command | 4/5/2011 |
| Q: My "Find" command in Excel 2010 cannot find numbers, amounts or texts within a work sheet. Please ... A: A little difficult to advise as it should work - could I see a sample worksheet with an indication ... | |
| making windows exel 2010 work for me | 4/4/2011 |
| Q: im having trouble propelly using the windows exel 2010 on my website that im trying to get up in ... A: I'm not sure what you are asking here - Excel 2010 can publish to the web, but it only publishes ... | |
| Conditional Cell Value - look up tabe | 4/2/2011 |
| Q: I would like to update a cell based on 3 cell values in the same row and do this for multiple rows. ... A: your first method failed as you have referred to NECA as though it is a named range - you can fix ... | |
| Monthly Tabulation | 4/2/2011 |
| Q: I work for a private school that distributes daily report cards to our students. We currently use ... A: I find the pictures on here quite hard to read (probably my advancing years (!!) ) but am happy to ... | |
| COUNT IF (DIV/O!) | 4/2/2011 |
| Q: I have the following CountIf statement ... A: zero divided by anything other than zero is fine as it returns zero - at least, it's fine as far as ... | |
| Combining multiple variables in a cell from one single "parent" | 4/1/2011 |
| Q: I'm trying to figure out how to combine variables into a single cell based on some parent data. For ... A: I cannot think of a native excel function that would do this, but you could use a user defined ... | |
| Cut & Paste filtered lists | 3/30/2011 |
| Q: Aidan This is something I think has got to be easy for excel but I just can't work out how it is ... A: If I'm following this, the actual data is on the same rows in each sheet, but you need to pick out ... | |
| Overtime calculation | 3/30/2011 |
| Q: How to calculate Overtime? Suppose: A1 have starting hours B1 have ending hours Considering 8 hours ... A: based on your suppositions alone =B1-A1-(8/24) would give the result - BUT your examples don't ... | |
| conditional formatting of color cells in a row | 3/29/2011 |
| Q: How do I create one single row of cells colored to represent each of three separate sections that ... A: the formula option sounds like the way to go, but it's not too clear from the question how this data ... | |
| lookup value final | 3/29/2011 |
| Q: I've changed the macro and its working but i can't seem to loop this macro from Row B until row ... A: For Each cell In Range("D:D") this line specifies to run for every cell in column D - the word ... | |
| How are "Too Many Formats" Determined | 3/28/2011 |
| Q: The version of Excel we're using here at work (Excel 2003, Service Pack 3, running on Windows XP ... A: I don't get to see your email address - though if it helps, mine is aidan.heritage@virgin.net The ... | |
| Looping and copying | 3/28/2011 |
| Q: I am trying to perform a operation that i have done before in other software packages (STATA). I ... A: I don't think it needs to be as complicated as that - if the purpose of getting the quarter and year ... | |
| Deleting rows in the end of worksheet | 3/28/2011 |
| Q: Hei, It may be a simle thing but I am not able to delete the ending rows in a worksheed. I have a ... A: There are two methods - the first is quick and dirty, which is to create a new worksheet, copy the ... | |
| To Print data of all sheets of a workbook | 3/26/2011 |
| Q: I have a workbook which has a few sheets. I want to print the data of all sheets in continuity as if ... A: pplication.ScreenUpdating = False Dim NewSht As Worksheet Dim LogicTest As Boolean Dim lastcell As ... | |
| Look up value | 3/25/2011 |
| Q: How do you look up a value from a cell in its own column? What Macro should I use? I’m trying to ... A: it can be done using a macro, it's basically Sub output() Dim outvar As Long outvar = 2 For Each ... | |
| Simple Excel shop spreadsheet | 3/22/2011 |
| Q: I am making a simple excel spreadsheet for a shop's sales. It has various columns with a forumla to ... A: You can use vba, but easier would be to use a formula that did it for them - if this is a "sum all ... | |
| Limit number of Words in a form | 3/22/2011 |
| Q: ) I have an application form set up (I have one done in Exel and another in Word)and in this form, ... A: It's always difficult on AllExperts to work out what level of knowledge the questioner has, and ... | |
| To open a book with some rows and sheets hidden | 3/22/2011 |
| Q: I have a work book which contains a macro to open another book, it is working ok, below is the code: ... A: Private Sub Set_Calendar_Click() Dim mywrkbk As Workbook application.screenupdating=false Set ... | |
| Conditional Counting of Colored Cells | 3/21/2011 |
| Q: I have a spreadsheet that is a rating system (scoring from 1 to 4 by coloring the cells green). I'm ... A: You are overcomplicating it - at least, I assume you are as you state that the cells are ... | |
| Vlookup an IF statement | 3/21/2011 |
| Q: I have the following issue where i have to analyze actual bookings, and ensure they follow the ... A: I’m not clear where the data comes from but it sounds as though it’s something like ... | |
| Limit number of Words in a form | 3/21/2011 |
| Q: ) I have an application form set up (I have one done in Exel and another in Word)and in this form, ... A: as we can assume that 100 words has 99 spaces (one between each word) you can use that to get your ... | |
| Quiz Macro | 3/18/2011 |
| Q: I am trying to develop an Excel macro to ask a user multiple choice questions from a question bank ... A: The rnd function returns a random number - from help Returns a Single containing a random number. ... | |
| Noob Excel question | 3/17/2011 |
| Q: Can you please help me with this problem please How can I add formulas to show increases for each ... A: If I follow this it's simply a bit of normal maths =B8*(1+$B$3) gives you the share price increase ... | |
| excel drop down list | 3/16/2011 |
| Q: o) I have two needs. I figured out how to make a drop down list, but I would like to just be able ... A: drop down list would be data, validation, list - but you already have that. For auto-complete, see ... | |
| Hyperlink in Data Validation | 3/15/2011 |
| Q: is there a way to have a hyperlink that the user can select in a validated list? For example, when ... A: I'm not that good on hyperlinks, but I think that IF the dropdown list itself is to be a hyperlink ... | |
| finding change in a price list | 3/15/2011 |
| Q: I have a query - that shows selling price by period - looking at six months - I want to see lowest ... A: the minimum price is easy enough - if you want to see all of them, I would use a pivot table, with ... | |
| Convert multiple XML files to XLS | 3/11/2011 |
| Q: Trying to use your macro at: ... A: that macro SHOULD work correctly in office versions prior to 2007, but in 2007 and later the methods ... | |
| Manipulating Time Formats | 3/10/2011 |
| Q: I have a series of cells where the user enters a time estimate. The problem is, not everyone enters ... A: I would have the cell formatted as time, and have data validation on the cell that only accepted a ... | |
| How to link access table to excel | 3/10/2011 |
| Q: Is there a way to populate excel file from an access table. I know how to export it but I want it in ... A: I would do this by creating a data query – - you didn’t specify version of Office, so I’ll give ... | |
| Excel Number Display | 3/10/2011 |
| Q: "I would like to get excel to display negative number with bracket. When I use format cell option ... A: The options come (I think) from regional settings BUT this is not important - simply use the CUSTOM ... | |
| Excel Mail Merge | 3/9/2011 |
| Q: Can I have a date formatted as 01/01/11 in the excel data source, but show as January 1, 2011 in the ... A: The format of the date in excel actually has no bearing on the mail merge – being in the UK this can ... | |
| Decending order | 3/9/2011 |
| Q: I want to set the results of Column D, in Decending Order in Column E. B C ... A: It doesn't come over very clearly in the text, but it does look as though you have errors in the ... | |
| Widen Data Validation List Comprised of Merged Cells | 3/8/2011 |
| Q: Aiden, You answered a question previously in 2009 from someone else with the following: "Answer ... A: Glad you got that sorted – I must have made a copying error as my test macro did only set columns ... | |
| Widen Data Validation List Comprised of Merged Cells | 3/6/2011 |
| Q: Aiden, You answered a question previously in 2009 from someone else with the following: "Answer ... A: merged cells are automatically a target of multiple cells, so you will need to remove the .count>1 ... | |
| Excel question | 3/4/2011 |
| Q: I have about 75 data sheets that contain 12 columns and between 10 and 40 rows. I am trying to get ... A: the picture doesn't come through in a very legible format - probably my eyesight - MIGHT work if you ... | |
| payroll hours calcuations | 3/4/2011 |
| Q: I am trying to set up a spreadsheet that allows employees to enter the time they start work in ... A: OK, two parts to this (it would only be one if they entered the time AS time eg 16:00 and 2:00) ... | |
| offset formula | 3/3/2011 |
| Q: I am using excel 2003 I have got a sequence of numbers across a row from column A to AA, In every ... A: It might be easier to do this via email (that way I could see a sample file) - my email is ... | |
| Matching the first Row Names with Other excel sheet and extract data | 3/3/2011 |
| Q: Aidan Heritage Greetings, I have an excel sheet with first row contains the Heading such as Emp Sl. ... A: The MATCH worksheet function will match a given entry so could be used with an INDEX function to get ... | |
| date formatting | 3/3/2011 |
| Q: sir, I have one xls file maintain by team members wherein few columns contains dates entered as ... A: Setting the cell format to mm/dd/yyyy will give you what you want, but I come back to my original ... | |
| date formatting | 3/2/2011 |
| Q: sir, I have one xls file maintain by team members wherein few columns contains dates entered as ... A: In most date systems, the full stop isn't a date seperator - this is ACTUALLY an international ... | |
| Insert Data into differet cells | 3/1/2011 |
| Q: I want to insert data of 3 columns (I.E SN,Username,Password)into some cells of a sheet. As you can ... A: The layout looks rather difficult as you appear to have a series of blocks of data going both across ... | |
| To copy text from combo boxes to List Box and specified cells | 3/1/2011 |
| Q: I have a user form it contains a text box which has an employee code like (0100). There are some ... A: Sub ADD Listbox.additem combobox1.value & "-" & textbox1.value & "-" End sub Is the basic method ... | |
| Headache..:) | 2/25/2011 |
| Q: Hai. I'm from Malaysia so pardon my English I'm working using excel and having headache of how to ... A: I'd use a lookup table for this which would give you the =currency * ConversionFactor in one easy ... | |
| excel 2003 date reference | 2/24/2011 |
| Q: I have a worksheet where the first column (column A) is dated as individual dates. ie 01/01/11, ... A: Basically a multiple condition countif - which you can do with sumproduct ... | |
| importing data from txt file to excel | 2/22/2011 |
| Q: I want to import 3 lines of data after word DATE from notepad file to excel sheet2. Notepad file is ... A: Could you send me an actual file? aidan.heritage@virgin.net - it's just that it could have a number ... | |
| importing data from txt file to excel | 2/21/2011 |
| Q: I want to import 3 lines of data after word DATE from notepad file to excel sheet2. Notepad file is ... A: This code will import some lines of data - you didn't specify where you wanted it imported, or if it ... | |
| To generate code number | 2/21/2011 |
| Q: I have an excel sheet which contains employee data. I have created a user form to fill the details: ... A: cells(TheRow,13).value=combobox1.text would do it - you can use a similar method to the string ... | |
| To generate code number | 2/21/2011 |
| Q: I have an excel sheet which contains employee data. I have created a user form to fill the details: ... A: TextBox1.text=worksheetfunction.max(range("B:B")) +1 will return the maximum value plus one, and the ... | |
| Excel | 2/19/2011 |
| Q: I have a excel data sheet in which weight of pregnant women we are taking in every month. So i want ... A: Can you clarify what it is you wish to get out of the data? If it helps, my direct email is ... | |
| nestled validation | 2/18/2011 |
| Q: I have been developing a spreadsheet for costing recipes from and inventory list. I am not even sure ... A: I would do this by using named ranges,with the nameof the produce range being PRODUCE and the range ... | |
| row column intersecting point value | 2/18/2011 |
| Q: I am trying to create a formula that will look up a month in the row, a city in the column, and ... A: Use the MATCH and INDEX worksheet functions I’ll give you the individual components of this – first ... | |
| Vlookup??? | 2/18/2011 |
| Q: I'm creating an expense workbook. I named "sheet1" = "Item" "sheet2" = "Week1" ... A: I would suggest a redesign of the sheet would be in order – when you enter hard coded values, those ... | |
| Excel Drop down list | 2/16/2011 |
| Q: I have received a spreadsheet I am doing some editing on for a client. There is a drop down list in ... A: You didn’t specify that you had selected the entire workbook before doing clear all as clear all ... | |
| Delete 2nd sheet with Header | 2/16/2011 |
| Q: When I typed the header on the first page it automatically included the same header on the 2nd page. ... A: Excel isn't Word, so the assumption is that every page you print would have the same information - ... | |
| Excel 2010 Print, Increment Formula, Repeat | 2/15/2011 |
| Q: I was wondering if it is possible to do the following: I have (sheet 1) which is has mutiple cells ... A: mailMerge is done in word - it basically takes a standard document and populates it with variable ... | |
| to create AddInn | 2/15/2011 |
| Q: I have 4 different codes to delete empty rows depending upon certain conditions which I have copied ... A: http://www.ozgrid.com/VBA/excel-add-in-create.htm gives you the means to create an add-in, and on ... | |
| Filters & Drop down selections | 2/15/2011 |
| Q: I am building a form on Sheet 1. On this form I would like for it to be idiot proof so I want drop ... A: I’m SLIGHTLY concerned that you mention a number of users, as this MAY imply data entry, for which ... | |
| visual basic6.0 | 2/15/2011 |
| Q: what code can I use in vb6.0 to excel because when i use this code Private Sub cmdadmin_Click() Dim ... A: I wouldn't usually use SHELL for anything, but if you MUST use Shell you will need the full path - I ... | |
| EXCEL FILES DO NOT SAVE | 2/14/2011 |
| Q: Recently, I had reinstallation of Windows XP (SP3)English version to replace same of Greek version . ... A: I'm sure the fact that you have two copies is part of the problem, which would be in line with the ... | |
| conditional copy-paste-delete | 2/14/2011 |
| Q: help on excel 2007 If sheet1!a1=value v1,copy sheet2!range a1:d3 into sheet3!range a1:d3.else, if ... A: I think it would have to be a macro, but the process seems somewhat complex - it's also hard to ... | |
| EXCEL FILES DO NOT SAVE | 2/14/2011 |
| Q: Recently, I had reinstallation of Windows XP (SP3)English version to replace same of Greek version . ... A: This isn't specifically a word problem, more an operating system one - my GUESS would be that the ... | |
| to learn the formulas | 2/12/2011 |
| Q: when you use the formula of PROPER which capitalize the first letter of a word but it only allow to ... A: My suggestion is to create a blank series of cells as wide as the original data, and to apply the ... | |
| Find and replace a pattern of cells in a particular column | 2/12/2011 |
| Q: Basically, all I need is a program that can look through a single column of data, find a pattern of ... A: Perhaps I've missed something, but surely a simple search and replace would do the job - select the ... | |
| export to .dbf file | 2/11/2011 |
| Q: Sir, I am using Excel 2003 and Visual FoxPro 6.0. In an excel file there are a few fields from ... A: See other answer, and also http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation which could help with ... | |
| export to .dbf file | 2/11/2011 |
| Q: Sir, I am using Excel 2003 and Visual FoxPro 6.0. In an excel file there are a few fields from ... A: This macro will save as a TEXT file rather than dbf, but it will be formatted in the style you ... | |
| conditional formating | 2/11/2011 |
| Q: Cell A1:A65536 = =randbetween(0,27) Cell B1:B65534 = =AND(A1=$F$1,A2=$F$2,A3=$F$3) Cell C1 = ... A: IF you want something to happen after a random event occurs and be stored even after further random ... | |
| Excel 2010 Print, Increment Formula, Repeat | 2/10/2011 |
| Q: I was wondering if it is possible to do the following: I have (sheet 1) which is has mutiple cells ... A: I’m assuming there is some reason for not doing mail merge (which would produce a 1000 page document ... | |
| Need your favour plz | 2/10/2011 |
| Q: Please guide me in this regard. I think it is possible in excell 2007, coze we can use 64 ifs in ... A: My apologies - my eyes tricked me into seeing continuous blocks of numbers - slightly more ... | |
| Unique List from 2 Locations | 2/9/2011 |
| Q: I am using a relatively simple formula to create a unique list of data from one column, in one ... A: Does this have to be done with formulas?! I cannot think of an obvious way of getting two columns ... | |
| Union problem | 2/9/2011 |
| Q: Aidan, Here is a cleaned up excerpt of a vba code from Excel 2007. I am trying to copy and paste ... A: I did test the code, but only by copying to a new excel sheet, so it MAY WELL be that copying to ... | |
| Union problem | 2/9/2011 |
| Q: Aidan, Here is a cleaned up excerpt of a vba code from Excel 2007. I am trying to copy and paste ... A: I would suggest NOT using union, nor indeed the variables as they exist only to carry out this ... | |
| Excel 2010 Print, Increment Formula, Repeat | 2/8/2011 |
| Q: I was wondering if it is possible to do the following: I have (sheet 1) which is has mutiple cells ... A: You could use a macro to do this - it would have to run a for...next loop 1000 times replacing the ... | |
| Need your favour plz | 2/8/2011 |
| Q: Please guide me in this regard. I think it is possible in excell 2007, coze we can use 64 ifs in ... A: I’d be inclined to have a look up table for the deductions otherwise it gets messy with the if’s ... | |
| export to .dbf file | 2/6/2011 |
| Q: Sir, I am using Excel 2003 and Visual FoxPro 6.0. In an excel file there are a few fields from ... A: save as using file type DBF should work, although as you say if the data isn't in a valid format to ... | |
| Need to query MAX for Pivot table data | 2/5/2011 |
| Q: I have a simple pivot table showing Sales with 3 dimensions: Product, Color, Salesman. Product and ... A: As you suggested sumproduct I'd be inclined to go with that - although the headings are dynamic in ... | |
| convert data from excel and change into barcode | 2/4/2011 |
| Q: I need to export data from excel and convert into a barcode and print the barcode using publisher. ... A: I'm not familiar with Publisher (at least, not in this context) - I would have THOUGHT that a mail ... | |
| Sorting an array on excel | 2/4/2011 |
| Q: I am trying to make a macro (using VB for excel 2010) but I have run into a problem. This is my test ... A: It's quite hard to follow from the written text here - does the sort order appear on the ... | |
| Activate lost Excel feature | 2/3/2011 |
| Q: I am using Excel 2003 and a feature I commonly use doesn't seem to be working anymore. If I begin a ... A: You have incorrect settings in tools, options and then the EDIT tab - there is a box for move ... | |
| more =IF() in same column? | 2/3/2011 |
| Q: Miguel; I have a function IF(K2=554,"AP ",I2) in column P which essentially copies values from ... A: =IF(ISERROR(VLOOKUP(K2,Q2:r3,2,FALSE)),I2,VLOOKUP(K2,q2:R3,2,FALSE)) is what you actually need - ... | |
| more =IF() in same column? | 2/3/2011 |
| Q: Miguel; I have a function IF(K2=554,"AP ",I2) in column P which essentially copies values from ... A: take a look at http://aidanheritage.byethost3.com/excel/xlfdic01.xls which gives examples of many ... | |
| show complete sentence | 2/2/2011 |
| Q: I entered following formula in cell D6. Whenever i entered "Bill Not Prepared" in C6, then part of ... A: I don't follow what you are asking - although C6 may display in another cell if text wrapping is on, ... | |
| Function | 2/2/2011 |
| Q: I have a workbook with multiple sheets which is used to create parts lists, order sheets for ... A: If you are doing it with a FUNCTION rather than VBA then it would need to be an array filter - see ... | |
| Macros | 2/1/2011 |
| Q: Greetings- Similar to a previously posted question, i have a question regarding Move row from one ... A: Cannot find the previous answer though I guess that may have had the basis of a suitable macro in it ... | |
| Excel Hyperlink | 1/31/2011 |
| Q: I have a large spreadsheet of accounting data. I use Microsoft office Excel 2007. I have numerous ... A: This problem relates to the operating system and hyperlinks - I would GUESS there is some issue with ... | |
| Date to reflect the date sheet was saved | 1/31/2011 |
| Q: Ken, I have a form I am created in Excel 2007. My problem is I have a cell (A2) which I would like ... A: There isn't (as far as I know) an inbuilt function that will do this but you could build a ... | |
| to learn the formulas | 1/31/2011 |
| Q: when you use the formula of PROPER which capitalize the first letter of a word but it only allow to ... A: You could use a macro to change each cell, OR you could use a series of blank columns (possibly on a ... | |
| EXCEL | 1/30/2011 |
| Q: i have a worksheet in which i want 3 rows to be freezed and a group of cells H8-K11 to be shown ... A: you can freeze columns to the left and rows above, but you cannot have a floating range of cells ... | |
| Conditional Formatting with Text | 1/28/2011 |
| Q: I would like to format text in excel. For example: F14 has Joe Smith and in G14 there is a date. If ... A: Not clear what you mean here - if the cell colour is orange, then you can only use this IF it was ... | |
| VBA Coding | 1/28/2011 |
| Q: I have some .txt file,(around 4-6), which I download from server and put in a folder. every time it ... A: You didn’t specify version of excel, which MIGHT matter as far as getting the full list is concerned ... | |
| Excel VBA - combine list of columns into on list | 1/28/2011 |
| Q: I have a spreadsheet with a range of columns of zip codes. There are 4 additional columns with data ... A: Yes, very do-able – it’s a nested for next loop basically Sub writer() Dim N As Long, O As Long, ... | |
| array filter spreadsheet | 1/27/2011 |
| Q: Aidan: Can you show me how to modify your array filter to use multiple criteria? I would like to be ... A: As the formula gets more complicated, it is sensible to keep the array part and the returned value ... | |
| Ignors hidden values with help of function or formula | 1/27/2011 |
| Q: I ask my question with help of example I want to insert function or formula in F2 to f7, when i ... A: It doesn't come across very clearly in the typed format I can see here, but I'm not sure you are ... | |
| Array returning function | 1/27/2011 |
| Q: I am trying to write an array function which takes input from a 30 rows x 2 columns array, check if ... A: =IF(SMALL(IF($B$1:$B$30>0,ROW($A$1:$A$30),99999),ROW())=99999,"",INDEX($A$1:$A$30,SMALL(IF($B$1:$B$3 ... | |
| Excel Code Stopped working. | 1/26/2011 |
| Q: Hey, I came here in early October for help w/ a project I was doing. Tom helped me with it, and it ... A: I'm GUESSING but possibly some formatting has taken place (or other action) that has set the end of ... | |
| Excel "Read only" | 1/26/2011 |
| Q: I have a sheet that multiple users have access to throughout the working day as a result it is not ... A: To be honest, not easily - vba could stop the saving, BUT if users have macro security set high then ... | |
| To insert e-nmail address from outlook contacts | 1/26/2011 |
| Q: I have a excel sheet. I often need to insert e-mail addresses of different persons in the sheet ... A: Your code has the SAME destination for the phone number and email address, and as phone number is ... | |
| VBA | 1/25/2011 |
| Q: Hope you are well. I have a macro based workbook. There are two sheets "Home" and "Main". When I run ... A: OK, basically no problem - though I'm not 100% sure why data is being put onto other sheets and then ... | |
| Excel 2007 return data from combo box to chosen cell | 1/25/2011 |
| Q: I have Sheet 1 which is a data entry form (of sorts). Some of the cells on sheet one are drop down ... A: It SOUNDS as though the code on the combo box needs to use the change event to update the ACTIVE ... | |
| About sum | 1/24/2011 |
| Q: I have a book with 3 sheets. In cell a5 from sheet1 the value is 10 In cell a5 from sheet2 the value ... A: start typing sum as normal, but then click on the sheets you want - click the first one, then hold ... | |
| To make a list of sheet names | 1/24/2011 |
| Q: The below code makes a list of all the worksheet names in the current sheet. It makes list in A1 ... A: I gave you more than one version of code which isn't helping I suspect, but basically you need an ... | |
| Using Cell color as condition in Excel | 1/23/2011 |
| Q: I would like to know is it possible to do a SUMIF function in Excel using cell background color as ... A: There isn't a direct way of using the background colour - if that was set using conditonal ... | |
| Chart Columns Width | 1/23/2011 |
| Q: I cant seem to create wider columns in my chart. There is too much space beween the axis labels. In ... A: | |
| Find number in Data Entry Sheet | 1/22/2011 |
| Q: I have a excel worksheet, which has thousand of data entries. I have an other excel sheet, which ... A: EASIEST way would be to use a blank column and use the COUNTIF worksheet function ... | |
| calculating $ of time per call in excel 2007 | 1/21/2011 |
| Q: I'm trying to figure out how to calculate the percentage of time per call in excel. In a1 I have ... A: BUT it is the same question, and the same answer - IF you want to know the percentage of calls in ... | |
| calculating $ of time per call in excel 2007 | 1/21/2011 |
| Q: I'm trying to figure out how to calculate the percentage of time per call in excel. In a1 I have ... A: It is possible, but you have the thought process wrong I think - if you want 50% then the time isn't ... | |
| Replace chart or add chart | 1/21/2011 |
| Q: I have tried to type the below macro which If sheets("Account Chart") exists, delete it and call ... A: I had tested it and it worked fine when I ran it - on error resume next means In the event of an ... | |
| add and minus function | 1/21/2011 |
| Q: Everytime I run the below code, I got error massage of "Run-time error'13': Type mismatch" and res = ... A: Without seeing the file I can only be general, but EITHER one of the vlookups will have returned an ... | |
| Replace chart or add chart | 1/21/2011 |
| Q: I have tried to type the below macro which If sheets("Account Chart") exists, delete it and call ... A: Your test is not doing what it is meant to If Len(Sheets("Account Chart").Name) > 0 Then ' This ... | |
| To make a list of sheet names | 1/21/2011 |
| Q: The below code makes a list of all the worksheet names in the current sheet. It makes list in A1 ... A: I gave you more than one version of code which isn't helping I suspect, but basically you need an ... | |
| Retrieve a list of names based on one variable | 1/20/2011 |
| Q: I'm trying to retrieve a list of names based on one variable, like # of years with company. I've ... A: This needs an array filter - see http://aidanheritage.byethost3.com/excel/Array_Filter.xls for an ... | |
| To make a list of sheet names | 1/20/2011 |
| Q: The below code makes a list of all the worksheet names in the current sheet. It makes list in A1 ... A: The first bit is easy enough if sheets(x).name<>"RDBMailOutlook" then Cells(x+y, 2) = ... | |
| Chart Columns Width | 1/19/2011 |
| Q: I cant seem to create wider columns in my chart. There is too much space beween the axis labels. In ... A: I'm not an expert on charting as I don't use charts much, but the info over at ... | |
| VBA Help | 1/19/2011 |
| Q: I have macro that i'm currently working on. What this macro does is it loops through excel files ... A: The line to change is the one with the 2702 reference in it - my method would be ... | |
| more =IF() in same column? | 1/19/2011 |
| Q: Miguel; I have a function IF(K2=554,"AP ",I2) in column P which essentially copies values from ... A: You can nest up to seven levels of brackets, but surely easier would be to have a list of the codes ... | |
| absolute | 1/19/2011 |
| Q: in Excel 2002 eg. in A1 i type 2000 B1 i give reference as =A1 i get 2000 if i change A1 ... A: A formula will display the result of the formula - so if you change A1, anything that references A1 ... | |
| To make a list of sheet names | 1/19/2011 |
| Q: The below code makes a list of all the worksheet names in the current sheet. It makes list in A1 ... A: Not clear if you want it always to start at B7 or to start at the first blank in B7 – if B7, then ... | |
| cell contents auto-change.... | 1/19/2011 |
| Q: I attached an image, having difficulty wording what i have and want... I have reports that use data ... A: The pictures on here are always hard for me to read - probably my failing eyesight at 50 years of ... | |
| Macros | 1/19/2011 |
| Q: Can you make such macros Description: Workbook ‘ base.xls ‘will be located in website in hidden ... A: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True MsgBox ... | |
| extract the data without opening excel file and paste it in new file | 1/18/2011 |
| Q: I have excel (2007 version) file of 100 MB (Approx around 40K rows), in which I want to extract the ... A: The only macros that I can think of would begin by opening the file so that they could read the data ... | |
| Excel function | 1/17/2011 |
| Q: I use an Excel function MAX.K to add consecutively highest K values in a row or column. It works ... A: I've never heard of this function - I'm GUESSING it's probably a user defined function written in ... | |
| saving/opening excel files | 1/17/2011 |
| Q: I am running Windows7,Office 2000 and Adobe Reader 9.4 When trying to save or open an xls file, it ... A: Technically this is an operating system question, but no problem - for some reason, XLS files have ... | |
| Excel VBA | 1/14/2011 |
| Q: I am trying to make a VB program for importing data from web-pages to excel. For this I have ... A: I'd be inclined to have a list of the connections and locations stored on the spreadsheet Sub ... | |
| Excel IF formula | 1/14/2011 |
| Q: I need to write this into a formula on excel 2007. I have tried a couple of things but can’t get it ... A: =IF(C7<1,0,IF(C7<4,C7*13.5,IF(C7<10,C7*13,IF(C7<20,C7*125,IF(C7<40,C7*12,C7*11.5))))) will do it ... | |
| Excel 2007 JOINS | 1/13/2011 |
| Q: Understanding the row limit of Excel 2007, is there a way to accomplish a classic JOIN of 2 tables? ... A: Yes it's possible, though I'm not sure it can be done with formulas (at least, not easily using an ... | |
| COUNTIFS | 1/12/2011 |
| Q: I am trying to Figure out a formula that will count each incident that occurs with in a given month. ... A: The word MONTH is a function, so should be left in place. Additionally, SumProduct won't take a ... | |
| Excel Lookup question | 1/12/2011 |
| Q: I am trying to build an Equipment Replacement Schedule for computer hardware. I have a column that ... A: I would do this using vlookup as the easiest method =VLOOKUP(F11-1,C1:D6,2,TRUE) C1 contains the ... | |
| Keeping leading 0's in excel | 1/12/2011 |
| Q: Im into Bank service that has variable accounts length with variable leading 0's. I use excel XML ... A: I've not worked with XML data, but possibly the easiest fix is to have the cells formatted as a ... | |
| Excel: Change cell value based on another cell | 1/12/2011 |
| Q: In this workbook there are 2 Columns (A1-A500) Status and (B1-B500) Cleared. What I need to do is, ... A: IF it is always Yes when it is accepted, then an IF statement would do it - eg =if(a1="Accepted", ... | |
| Excel 2007 worksheet reference | 1/11/2011 |
| Q: I have a workbook that has several worksheets in it. I want to make a 'summary' page. The first ... A: For some reason this question is still in my queue even though I thought I had answered it - so ... | |
| Sorting | 1/11/2011 |
| Q: I need help in forcing rows to stay together while sorting a column by contract #. Also the rows ... A: Sorting will, as you have seen, sort ALL rows in the order specified - which in this instance isn't ... | |
| COUNTIFS | 1/10/2011 |
| Q: I am trying to Figure out a formula that will count each incident that occurs with in a given month. ... A: It can probably be done, but I've not really got enough to go on - is each entry an incident for ... | |
| Pass/Fail gradebook for students | 1/9/2011 |
| Q: and thanks in advance for any help! I am using excel 2010 and trying to automate some the functions ... A: For the leaving blank, try IF(AND(ISBLANK(H12),ISBLANK(I12),ISBLANK(J12),ISBLANK(K12)),"", ... | |
| Using the text value from a cell in a formula | 1/8/2011 |
| Q: I would like to use a text value from a cell in a new formula. The formula I have refers to a ... A: Use the INDIRECT worksheet function =indirect(Yourcell & "BlanksRange!a1") to convert the literal ... | |
| Add & Subt. of time | 1/7/2011 |
| Q: Sir How can i subtract time in excel i.e. Time subtract 30 minus 7:45 7:15 A: Excel treats time as a fraction - so 12 hours is 0.5, 6 hours 0.25 etc - therefore to take time away ... | |
| Multiple SUMPRODUCT with wildcard | 1/6/2011 |
| Q: I have come to you a few times in the past and you have always solved my problems and Im hoping you ... A: I tested my little bit of the formula and it did work - search is NOT case sensitive, whereas FIND ... | |
| Countif function not ignoring hidden values | 1/6/2011 |
| Q: I ask my question with help of example A B C D E F ... A: Not sure if this is possible without changing the formula or using VBA - to change the formula, this ... | |
| Conditional Formatting | 1/4/2011 |
| Q: I've tried to use the formulas listed on your site but I dont understand what I'm doing wrong. I'm ... A: Not sure which of my replies you have read - but I would use a formula option to handle this - you ... | |
| Count if Conditions | 1/4/2011 |
| Q: Good evening and I'm hoping you might be able to help with regards to my query. I have been using ... A: Possibly the easiest way of doing this is to not use formulas at all but to use a pivot table - this ... | |
| summing non-adjacent columns based on condition | 12/30/2010 |
| Q: I have a business that send bills each month and receives payments each month. Sometimes we receive ... A: It's not quite the way I use this version of sumproduct, which is a multiple condition sumif (or ... | |
| generate permutations | 12/29/2010 |
| Q: I'm trying to create a Bell curve..I need all combinations that add up to a value.Use the lowest ... A: The macro is relatively straight forward, though I'm not clear what you mean by "add up to a value" ... | |
| Excel Formula Query | 12/21/2010 |
| Q: Good Day, I am using Excel 2003 version (on an oil rig offshore Egypt, so don't laugh please) I ... A: use conditional formatting, set that to a formula and (for simplicity) 182 days as 6 months - I'm ... | |
| Copying data from one workbook to another | 12/16/2010 |
| Q: On a daily basis I receive a workbook that has a date specific name, so for example today it would ... A: I'd use a file open dialog box to get the file you want application.Filename = .GetOpenFilename ... | |
| find email addresses | 12/16/2010 |
| Q: I have a file with about 300 rows and 14 columns. One of the columns has a teacher name. For each ... A: Is putting them in consecutive cells actually helping in the ultimate goal (which of course is ... | |
| excel | 12/15/2010 |
| Q: i would like to know how to formulate a sheet for knowing things have passed the excepted date i.e. ... A: Use format, conditional formatting to achieve this - use the formula option =YourDate<today() ... | |
| Looking up the date | 12/14/2010 |
| Q: What I am trying to do is look up the date (from column "B")for each instance a patient has called ... A: If you want to get all dates for all patients, then a pivot table may be the way to go - you didnt ... | |
| employee scheduling in excel | 12/14/2010 |
| Q: I manage a coffee shop and do my employee schedule in Excel. I would like to figure out a way for ... A: It will be possible, but not with the data structure you are using at the moment - I would be ... | |
| comparing and matching | 12/9/2010 |
| Q: I have two separate spreadsheets in Excel 2007 and I am working to reconcile them. One has more data ... A: Various ways spring to mind - I'm also not sure if you just need to match the NAME or the full set ... | |
| using excel macros/vba to print to word | 12/7/2010 |
| Q: I am trying to print out a confirmation template from excel into word, I have had some success in ... A: I'm not clear if this is running from Word or excel - from the creation of an excel object, it seems ... | |
| excel | 12/4/2010 |
| Q: My name is John and i want to know how can i find the cells that have the same values in a worksheet ... A: I would use conditional formatting to identify these – format, conditional formatting then use a ... | |
| find email addresses | 12/4/2010 |
| Q: I have a file with about 300 rows and 14 columns. One of the columns has a teacher name. For each ... A: I’m having slight problems visualizing this, but I don’t think you need to transpose. I’m not clear ... | |
| Change "Then Exit Sub" | 12/4/2010 |
| Q: I am new to VBA and Macros. I have this small macro that I use on a data base of on “Sheet1”. The ... A: I don't like copy and paste in macros unless it's essential. In this case your macro SEEMS much ... | |
| Excel Conditional Formatting | 12/1/2010 |
| Q: My PC has Windows XP. I created a pivot table in Microsoft Office Excel 2003 with conditional ... A: I think you will find if you refresh it on your machine it will do the same thing - a pivot table ... | |
| 2003 excel before print command | 12/1/2010 |
| Q: "I have an excel file with only one page as you can download it here : ... A: Although there IS a beforeprint event, it won’t help here as you want to print N copies, but ... | |
| Excel 2010 | 11/30/2010 |
| Q: I was wondering whether there is a spreadsheet or macro that allows me to change data on 1 sheet and ... A: It would be possible, though I've not really got enough to go on - it is POSSIBLY an array filter ... | |
| Automatic Rounding in Excel 2004 for Mac | 11/29/2010 |
| Q: I'm using Excel 2004 for Mac. When I enter a decimal number into a cell, it automatically rounds to ... A: From the SOUND of the information given, this would be a global settting - at tools, options, edit, ... | |
| Formula | 11/26/2010 |
| Q: I need to find the formula for anniversary years. Say the league started in 1980, I need my next ... A: Not clear what the question is - are you looking for the number of years between two dates? If so, ... | |
| Pivot table question | 11/24/2010 |
| Q: You recently had a question on changing the page fields in a pivot table and having those changes ... A: so my THOUGHT was to have ONE pivot table, with formulas coming off it to bring in the rest of the ... | |
| changing colours of cells | 11/23/2010 |
| Q: I have put together a spreadsheet and I want to change a cell colour based upon a number. So in a ... A: use the format menu, and choose conditional formatting - you can manage up to 4 levels of colour in ... | |
| change the filters for multiple pivot tables at once | 11/20/2010 |
| Q: I found this question, copied below, and I think it might help resolve my own personal query. I have ... A: I'm not sure the original answer was one of mine (it doesn't look like my style!), and as you are ... | |
| vlookup query help! | 11/19/2010 |
| Q: I work with a datasheet that reports on a rolling 3 months data. My current process is to copy the ... A: Use the INDIRECT worksheet function - eg =vlookup(g25,indirect(a1&"Pivot!H:I"),2,false) which ... | |
| Compile excel sheets | 11/18/2010 |
| Q: Aidan, Hope everything is well. We have a project where we get hundreds of excel sheets (in one ... A: Sounds fun – I’d PROBABLY use excel to handle this with code – it would control both outlook and ... | |
| Excel | 11/17/2010 |
| Q: I am trying to make a spreadsheet which is going to be used as an order form for delivering ... A: PRESUMABLY you only want them to see the current order, but have the total orders being used to ... | |
| Excel progressive drop down lists | 11/17/2010 |
| Q: I would like to create a project calculator to assist in my work. I know how to create a drop down ... A: I've done this in various ways in the past,but I THINK the easiest way is to have a series of named ... | |
| transform number to letter | 11/15/2010 |
| Q: My name is John and I'm writing you from Greece. My question might look a bit bizarre to you. I have ... A: There isn't anything direct in excel to do this, but you can use a macro - press Alt F11 to get into ... | |
| printing with excel | 11/14/2010 |
| Q: What I want to do is print on the same page a couple of items from the same worksheet but from ... A: If the two areas are not Contiguous, it doesn't matter what method you use to print them, they will ... | |
| Adjusting an inventory count between sheets | 11/12/2010 |
| Q: On a current sheet that an order was entered on, my code assigns each item and quantity to a ... A: Not sure I follow - however I will do my best - if this is done in code, use the ... | |
| UPDATE CODE TO PREVENT ERRORS | 11/11/2010 |
| Q: I am not good at coding and I have the below code that was written for me by someone else for use in ... A: Easiest fix would be an "on error" line so at the start change it to Private Sub ... | |
| Fitting text into cell | 11/11/2010 |
| Q: I have used the automatic cell height function but my text is still looking odd A: difficult to be exact without seeing a file (aidan.heritage@virgin.net will get me if that helps) ... | |
| Stock Sheet | 11/10/2010 |
| Q: I have constructed a stock report spreadsheet for a fish processing factory, (this is where I work). ... A: if you want to update the values contained in the cells, you would need to use VBA to achieve this- ... | |
| Sheet name as variable | 11/10/2010 |
| Q: Is it possible to look for cells depending to name of sheet? i.e. I have sheets "201001", ... A: The INDIRECT worksheet function is probably what you need - this converts text that could be ... | |
| excel sheet formatting | 11/8/2010 |
| Q: My initial sheet is a series of survey results that looks like the following : DATE ... A: I'd make sure the macro also filters out the response of 1, then use a pivot table to produce the ... | |
| automatic email | 11/6/2010 |
| Q: My name is Ashton i am 15 years old and i have my excel programme automatically updating lets say ... A: a macro would work, you wouldn't need more than one, though the logic would need to be clear - the ... | |
| Interest rate formula | 11/5/2010 |
| Q: Aidan, there are a host of financial formulas in Excel, but I can't seem to find the exact one I ... A: Although I work in financial services, interest rates have never been my thing - but see ... | |
| Importing Data | 11/5/2010 |
| Q: I am trying to import a list that I have cut and pasted from the Internet. How would I import ... A: I would use word to edit it - difficult to be certain, but I would GUESS that you would have ... | |
| Converting text into numbers | 11/5/2010 |
| Q: I recntly had extracted data from google finance and I have to do work on it with the numbers using ... A: Isn't the easiest way to do a search and replace - select the entire column, replace B with the ... | |
| excel question | 11/4/2010 |
| Q: How can I sum all negetive numbers from a list based on a criteria in adjacent column. eg. gbp -300 ... A: It's basically a multiple condition sumif so this method will do it for you ... | |
| Change text case in Excel | 11/4/2010 |
| Q: I have a list of names all in UPPER case. I would like to change the case to proper and/or lower ... A: One of two things is happening here - EITHER and most likely, you have the format of the cell set to ... | |
| Excel - One click Copy and Paste | 11/4/2010 |
| Q: I want to set up a documet that has a list of 20 or so explinations for returning work sent to us by ... A: You could you vba to do this, but wouldn't using AutoCorrect be an easier route - they type No ... | |
| excel macro | 11/3/2010 |
| Q: In my column K I have a bunch of reference numbers. Some of them are listed as follows: 1RP4-5RP4 ... A: This function Public Function ReturnArrayVar(var As String) As String Dim startnumber As Double Dim ... | |
| excel macro | 11/3/2010 |
| Q: I have a table in excel with a bunch of part numbers in column A, and "find numbers" in column C. ... A: Sub fixer() Dim checkvar As String Dim Loopervar As Double Dim SetVar As Double SetVar = 1000 ... | |
| Hiding Worksheets | 11/2/2010 |
| Q: I have a speadsheet that i will be uploading to a website where i would like only me to be able to ... A: Not clear where the website is, but unless it's something like a sharepoint intranet site, the files ... | |
| Excel Autopopulation from master list | 11/1/2010 |
| Q: Im new to excel 2010 and im trying to set up a table to keep track of work progress. We have a ... A: vlookup would be the function to do this - see ... | |
| VLOOKUP Help | 10/27/2010 |
| Q: I am trying to create a lookup type worksheet where I use a drop down list to select a customer, ... A: I take it the customer name is the unique ID, so a simple vlookup will work based on this - the IF ... | |
| counting specific days | 10/27/2010 |
| Q: Every month I produce a working schedule for my colleagues at work. Depending on their shift, I am ... A: The function would return the number of sunday dates, but it wasn't clear from the original exactly ... | |
| cell format | 10/22/2010 |
| Q: I have a workbook which contains 2 sheets sheet 1 - contains invoice number invoice amount date due ... A: I would suggest experimenting first to ensure that the cell("format") function gives you what you ... | |
| cell format | 10/21/2010 |
| Q: I have a workbook which contains 2 sheets sheet 1 - contains invoice number invoice amount date due ... A: The CELL worksheet function MIGHT give you a starting point =CELL("format",A2) I'm not 100% ... | |
| Formula to go across all spreadsheets in a workbook | 10/20/2010 |
| Q: I need to create a formula that adds specific rows across all spreadsheets to a summary sheet where ... A: If I understand it, you want the equivalent of a sum(a:a) sort of formula - going across sheets - if ... | |
| ascending ordering | 10/16/2010 |
| Q: i want to order a spreadsheet and the numbers i want to order are in the first column of spreadsheet ... A: Perfect sense, and excel will do this IF you either select only one cell in the range OR you select ... | |
| EXCEL:Converting a Number in to Text | 10/14/2010 |
| Q: Whether I can convert a number in words(Eg. 500 in to "Five Hundred". If possible I can use it to ... A: Your original question asked how to display numbers as text, and that is the macro method I've given ... | |
| Macro to move data | 10/13/2010 |
| Q: I have a workbook with 4 spreadsheets. All the data, Account Number, Name, Address and Date, is ... A: It's certainly possible, it would need a macro, though I'm not certain that moving the data around ... | |
| nesting OR function within AND function | 10/13/2010 |
| Q: I need to test 3 different variables with one variable having to valid values. this is how I wrote ... A: I've checked your formula and it's fine, so logically the OR part of the statement is returning a ... | |
| MS excel 2007 macros | 10/12/2010 |
| Q: I need to be able to pull data from any red cell or cell that contains the letter "d" and the cell ... A: Sub MoveData() Dim n As Long Dim y As Long y = ... | |
| OFFICE SMALL BUSINESS 2007 | 10/11/2010 |
| Q: EXCEL SHEET WILL NOT ALLOW MY HYPER-LINKS TO WORK FOR NO REASON I CAN SEE. WHEN IT GIVES ME ( CAN ... A: files saved as DOC format are in the old format, so in compatability mode - which is fine, it means ... | |
| excel nested if, and, or | 10/10/2010 |
| Q: I am having trouble getting my if statement to work. Here is what I have IF(AND[JOB ... A: You haven't said what the logic is, but the formula appears to have brackets in the wrong place - ... | |
| EXCEL:Converting a Number in to Text | 10/8/2010 |
| Q: Whether I can convert a number in words(Eg. 500 in to "Five Hundred". If possible I can use it to ... A: You will need to put a module onto your excel file (alt F11 to get to the VB editor, then insert ... | |
| OFFICE SMALL BUSINESS 2007 | 10/5/2010 |
| Q: EXCEL SHEET WILL NOT ALLOW MY HYPER-LINKS TO WORK FOR NO REASON I CAN SEE. WHEN IT GIVES ME ( CAN ... A: hyperlinks to files need to include the full path, otherwise they assume that the file is in the ... | |
| table look up help | 10/5/2010 |
| Q: I have a problem I hope you may be able to advise me on? I have a master table with eight coloumns ... A: It does sound as though vlookup should work fine - it's basically ... | |
| Importing user stats into master stats spreadsheet | 10/4/2010 |
| Q: We have a master 'user stats' spreadsheet & columns are usermname, then 4 associated data colums for ... A: It does sound as though it would be VBA that would be needed - could I see some sample data (just ... | |
| how to show present time every 1 minute | 10/4/2010 |
| Q: i had a problem to show a present time each a minute, for example, in cell a1=10.30, b1=now(), ... A: The time will update anytime the sheet is recalculated, but it won't update as a clock would UNLESS ... | |
| excel | 10/2/2010 |
| Q: how r u// i m just working in a small office, where i have to work on a screen opened on the ... A: It is theoretically possible, but with the data I currently have from your question it would use ... | |
| concatenating spreadsheets | 9/29/2010 |
| Q: I have a number of spreadsheets, each has the same format. Each has 9 tabs. Instead of twenty ... A: Not sure about the reference to VB6 (though it can certainly be used) but I would suggest VBA as ... | |
| Opening files | 9/28/2010 |
| Q: I am using MS Excel 2003. What I am trying to do it to create a function that will allow me to open ... A: This would need to be done in Visual Basic, though I guess you worked that out by the reference to ... | |
| Macro | 9/28/2010 |
| Q: hope you might be willing to help me, im in the middle of trying to write a new excell sheet, that ... A: Your explanation was all I needed - open up the visual basic editor (I use Alt F11 to do this, but ... | |
| if statement | 9/25/2010 |
| Q: Smith, I have data in a master table and I'm trying to summarize the data in a separate sheet by ... A: I don't think it's an if statement - IF you need to get what is effectively a filtered list across, ... | |
| Excel Formula | 9/21/2010 |
| Q: I am trying to figure out a formula in Excel. I have a spreadsheet which lists various professors ... A: It actually sounds like a pivot table would be the way to go as this would be automatic, but ... | |
| Define name in excel | 9/20/2010 |
| Q: I am new to excel. I would like to know about Name funtion in excel. Could you please explain ... A: Not sure what you mean specifically, but you can insert a name using the insert menu - this name ... | |
| Simple =sum question | 9/18/2010 |
| Q: I have an array of quiz score data for a class I am teaching and I need to pull the highest 10 ... A: http://www.eggheadcafe.com/software/aspnet/32115156/average-highest-16-numbers-on-a-column-of-32-num ... | |
| excel 2010 | 9/17/2010 |
| Q: I have a column of numbers 10050 - 10099. I want to click on any number in the column and have it ... A: The only way I can think of to do this would be via vba - for which, see ... | |
| Data Validation | 9/16/2010 |
| Q: On sheet 1 A1:A5 I have a small list of letters and numbers formatted as General. On sheet 2 A1:A5 ... A: I would set up sheet1 a1:a5 to be a named range, and then use data validation on sheet 2 to be a ... | |
| hyperlinks | 9/16/2010 |
| Q: On one excel tab/worksheet I have column A which contains 200 hyperlinks in the first 200 cells. I ... A: This is very strange, as I've just had a very similar question from someone else! The formula you ... | |
| excel | 9/15/2010 |
| Q: I'm trying to set up an invoice in excel xp, I would like the invoice numbers (i.e. 1000, 1001, ... A: this code ran on the change event of the workbook, IF you wanted it to run before save, then move my ... | |
| excel | 9/15/2010 |
| Q: I'm trying to set up an invoice in excel xp, I would like the invoice numbers (i.e. 1000, 1001, ... A: yes, it's possible - it would need VBA to achieve it - the version I did at ... | |
| Extracting data | 9/14/2010 |
| Q: I have some problem in extracting the data,the following code works fine to extract specific data ... A: the code uses the reference range("A" & rowscount) - so make this cells(rowscount,1) to do the same ... | |
| Excel Formula | 9/13/2010 |
| Q: I belive you may clear my problem. In D1 the cell having "Monday 8 AM daily".Here I used some ... A: is the formula being used one that actually returns a time (such as a custom function) - either way, ... | |
| Change Event | 9/13/2010 |
| Q: Aidan, would you show me how to call a macro when I change a number in “Sheet1” cell“V1”. Alan A: you use the CHANGE event of the worksheet - as below - my examples show a1:a20 in various forms and ... | |
| Need to write excel forumla that will satisfy 2 conditions and select the correct answer | 9/13/2010 |
| Q: I am having problems with writing a formula in excel that will satisfy 2 constraints and select the ... A: Difficult to follow this via the constraints of AllExperts – I cannot see hwo the numbers 300 and ... | |
| linking | 9/10/2010 |
| Q: I have Excel 2003. I have a spreadsheet whereas I key in a number at B9. (It is a PO#-purchase order ... A: Your formula in B9 needs to be =HYPERLINK(VLOOKUP(B9,$F$20:$G$23,2,FALSE)) and you need to alter ... | |
| Excel Add-in | 9/9/2010 |
| Q: I have an addin which is saved on the shared drive and used by others, but the problem im facing is ... A: I would have the file saved with attributes of Read Only (i.e use explorer, right click the file, ... | |
| Excel has a mind of it own ! | 9/8/2010 |
| Q: my name is Lee. I just got back from work and have had the day from hell. I have been having ... A: According to Microsoft (and LIMITED research on my part!) the Normal style is "predefined" - I would ... | |
| excel question | 9/7/2010 |
| Q: I am using 2003. I am building a sheet that I want to only import mulitiple cell data on one row ... A: Not sure I've got enough information to provide an answer, but possibly see ... | |
| dividing sum result in multiple cells | 9/4/2010 |
| Q: I'm new to all this and i hope you can help me, i have some knowledge in excel but I'm stuck with ... A: I assume that it's always first 500, next 2000 etc =if(SumOfparts>500,500*cost,SumOfParts*cost) ... | |
| Excel 07 (max date <= today) | 9/3/2010 |
| Q: My brain hurts... I have created a sheet to schedule (basically) shift work. Once a person ... A: Not clear why you need the actual date - wouldn't something like =IF(MAXA( IF( R_Values = "U", ... | |
| Avoiding Weekends in the formula | 9/2/2010 |
| Q: I have a formula 'F2+(AG2/60)/24' which gives me the total implemented time in a format 7/1/10 2:14 ... A: This is always a nightmare of a scenario - I had to calculate Service Level Agreements at one job ... | |
| drop down box | 9/2/2010 |
| Q: I use a template with two drop down boxes with a lot of choices in each box (medical CPT codes and ... A: IF I read the question correctly, this page http://www.ozgrid.com/Excel/autocomplete-validation.htm ... | |
| Histogram at zero | 9/2/2010 |
| Q: How do I make a histogram that includes x=0? I have this data Frequency - Counts (y) ... A: I'm guessing, but assuming you are charting this - if so, right click the axis and set the minimum ... | |
| Table Info from WORD to EXCEL | 9/1/2010 |
| Q: I'm not sure if this should be in the WORD or EXCEL section. I created a long table in WORD and I ... A: It's a word question, as the data is in word - and it SOUNDS as though it's the Word table that is ... | |
| serch for a string | 9/1/2010 |
| Q: I have a list of strings in a file named active.csv. I want to input them one at a time.. check ... A: basically needs a macro - this would open the file for input, read each line, do a check against the ... | |
| conditional formating | 9/1/2010 |
| Q: Is there a conditional format that will shade alternate months in an excel calendar? ANSWER: I ... A: Sorry for not being clearer - you had asked for conditional formatting, so I gave you a formula to ... | |
| How to remove space between numbers | 8/31/2010 |
| Q: I have thousand of entries on sheet like this 1978 3351 1234 5678 I want to remove space with help ... A: use the substitute worksheet function =SUBSTITUTE(A4," ","") where A4 is your cell - do this in a ... | |
| excel formula | 8/31/2010 |
| Q: i am sakthi. i want a formula regarding a excel sheet. I want to get t count of t items on my ... A: The COUNTIF function would do it =countif(Sheet1!range,"OPEN") where range is the range of cells ... | |
| MAX + 2x IF Function writing | 8/31/2010 |
| Q: I have a question about the Max function and using If functions with that. I want to find the ... A: Haven't got excel 2007 to hand, but I think it would have to be the same method as earlier versions ... | |
| vba populate list box from text box entries | 8/30/2010 |
| Q: I have 4 textboxes on a userform (named vendor1,2,3,&4 respectively). When the next userform is ... A: There is nothing on here that looks wrong as such EXCEPT that it is only referencing itself, and you ... | |
| Excel adding text to cells | 8/30/2010 |
| Q: First, let me thank you for taking the time to answer my question. I have numeric values (set as ... A: Not clear what the ultimate purpose is, but that aside – the easiest way to do it would be to use a ... | |
| concatenation | 8/30/2010 |
| Q: I place a number in cell A5. In cell C2 I want to show text and a cell address together, such as: ... A: if you are cutting and pasting the cell, then C2 will update the formula automatically. However, it ... | |
| Need formula for excel | 8/30/2010 |
| Q: i need your help to put excel formula for, I am trying to prepare training room booking excel where ... A: The principle seems easy enough, though I suspect there will be problems in that if you want a room ... | |
| Count or index | 8/30/2010 |
| Q: I'm needing help to count 2 conditions being met in 2 column. What would the formula be to count ... A: The pictures on here are always hard for me to read (if you want to mail me directly I'm on ... | |
| Excel Formula | 8/29/2010 |
| Q: I am looking for a formula that will count the number of x's in a range example A2:F2 and will ... A: you need to use conditional formatting for this (format menu) =countif(a2:f2,"x")>4 entered as the ... | |
| Creating multiple tables in a spreadsheet | 8/29/2010 |
| Q: Sir,I shall indeed be grateful to you,if you would let me know how to create multiple tables having ... A: It is not clear what you wish to achieve, but as excel has 256 columns and 65536 rows on a sheet ... | |
| Present Value of a monthly annuity due | 8/28/2010 |
| Q: I have a mental block at the moment about what should be a simple annuity due calculation:- I need ... A: The maths and process is alien to me (saw this question in the question pool) BUT this google search ... | |
| Excel formula | 8/27/2010 |
| Q: I'm trying to show the day of the week and time in each cell and express the difference between two ... A: I'm not sure I follow - especially as you mention work week - you would need to have a starting ... | |
| excel query | 8/27/2010 |
| Q: i have a small query in excel working, i have xl file(1) contains data like this : customer no., ... A: Assuming this is to be done ONCE, I'd put in a "helper" column which has =customer no & Name ... | |
| Grid Lines Not Printing | 8/27/2010 |
| Q: I just upgraded to Office 10. I have checked the box that requests that grid lines print, but they ... A: if the cells have borders set to white then these will print, which means white text on white ... | |
| Excel, make X number of copie of sets of sheets based on numeric value in cell | 8/27/2010 |
| Q: I have two questions that are linked: 1)I have a workbook with 16 sheets each has a unique name. The ... A: The first bit seems to be reasonably simple, as you have stated that manually it works – so ... | |
| count if - only using time values | 8/27/2010 |
| Q: I am using Excel for Windows 2003. I use it to make my schedules. When an employee is scheduled to ... A: It's not clear from the question how standardised the wording for shifts and not scheduled is - if ... | |
| XL help with populating a cell based on a matrix and answers in other cells | 8/26/2010 |
| Q: I am trying to auto populate a cell with a value in a matrix based on the values in another 2 cells. ... A: difficult to follow from the typed list, but it sounds like an index function ... | |
| Cop cell content | 8/26/2010 |
| Q: I have a need to copy the text content of Cell L8 on Worksheet "Quotation" to the next empty cell ... A: Dim n As Long n = 5 While Sheets("Forecast").Cells("D" & n).Value <> "" n = n + 1 Wend If n > ... | |
| multiple code functions | 8/25/2010 |
| Q: I have a worksheet with the following code that works perfectly (was able to edit something found, ... A: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If ... | |
| Excell Import Macro | 8/25/2010 |
| Q: I am trying to create macro for excel to automatically import data from multiple .txt files located ... A: basic process is simple enough - the last line might cause an issue only in as much as you haven't ... | |
| Sending Excel via email | 8/25/2010 |
| Q: I have a few documents that I would like to send via e-mail. However, each time I ... A: The size of the file may be to blame, but other than that it's hard to be specific - how are you ... | |
| Excel Time Calculation | 8/24/2010 |
| Q: .I need your help to solve time difference. The question is I want to check time difference between ... A: I would need to know how consistent the data in column D3 is - does it always show weekly 8 AM ... | |
| survey creation | 8/24/2010 |
| Q: I would like to conduct survey on my Service usage with some questionaries .... Finally i need to ... A: I'd be inclined to use data validation rather than radio buttons, giving you a drop down set of ... | |
| Row Heading Numbers Not Visible | 8/23/2010 |
| Q: On all of my spreadsheets in one notebook, the row numbers which head the rows on the left side are ... A: Rows can be any height from zero upwards, but you won't be able to read the row number at much less ... | |
| Excel Look Up Formula | 8/23/2010 |
| Q: I am hoping you will be able to help me out. I have a spreadsheet which has an area code in Column ... A: sounds reasonably simple, if potentially a long formula - is it possible to see the sheet (or a mock ... | |
| lookup? function help | 8/22/2010 |
| Q: Please help me with this. Not sure which function does this. Below is an example list. If I type ... A: I learnt VBA by recording macros and analysing them - so I've no knowledge of which books are good ... | |
| Need Help | 8/21/2010 |
| Q: i have data in excel 2003 which include start date,start time & end date,end time in different ... A: IF I follow this correctly, it's simply a case of looking up the date and time IN and and date and ... | |
| Need to formula to sum the numbers from a table | 8/20/2010 |
| Q: Sorry to trouble you with this email, and I've tried all solution thats possible, but nothing works ... A: a SUMIF function would do it for you – =SUMIF(B2:B12,"Navy",C2:C12) Would sum all values in column ... | |
| Word 2007 Mail Merge from Excel 2007 | 8/19/2010 |
| Q: I am using Word 2007 to mail merge names, titles and dates from an Excel 2007 spreadsheet to ... A: Perhaps I'm missing something, but you have told excel to use the 1904 date system, which would make ... | |
| Excel Dates | 8/19/2010 |
| Q: im filling out a spreadsheet where i have to put a lot of different dates in, is there any way that ... A: see ... | |
| lookup? function help | 8/19/2010 |
| Q: Please help me with this. Not sure which function does this. Below is an example list. If I type ... A: If you want it to happen in the same cell it's not a formula - there are two options - the first is ... | |
| SUM cells with text in cells | 8/19/2010 |
| Q: I am trying to SUM the following cells that may have text in the cell. I want excel to ignore the ... A: My original comment specified "IF it was just numbers and then text, without dollar signs, then" - ... | |
| Macros | 8/18/2010 |
| Q: Sir, I receive daily report dump generated by the system (faults figures in corresponding cell ... A: Yes I can, though I haven't really got enough to go on - I PRESUME all files would be in one folder, ... | |
| Change Font Color Based on Dropdown Value | 8/18/2010 |
| Q: I created a semi-monthly timesheet in Excel. Cells C17-F17 are merged and are used to present a ... A: Yes, it can be done with conditional formatting, using a formula option – I'm not sure what the ... | |
| SUM cells with text in cells | 8/17/2010 |
| Q: I am trying to SUM the following cells that may have text in the cell. I want excel to ignore the ... A: IF it was just numbers and then text, without dollar signs, then =SUM(VALUE(LEFT(A1:A3,SEARCH(" ... | |
| Excel 2004 | 8/16/2010 |
| Q: I have a spreadsheet in which I enter the incomes of all members of a household. It calculates the ... A: sounds like either vlookup or index would do it - see ... | |
| Percentage discounts | 8/16/2010 |
| Q: Need a formula to figure multiple discounts. If an item is $2.99 and I need to input different ... A: Not totally clear what you had in mind but if it's simply how much is 2.99 when discounted by an ... | |
| Excel help needed | 8/11/2010 |
| Q: I am trying to create a database in excel that i can later use for a mail merge. I am copying ... A: providing it's always a comma, then yes - and excel will do it for you - select the column (or cell) ... | |
| Mail Merge to fit into Window Envelope | 8/10/2010 |
| Q: I have been doing basic mail merges for month, I now have a challenge, I need to include the ... A: This is really a word question, but in Word simply use the merge to document option - this then ... | |
| AutoComplete Table | 8/5/2010 |
| Q: Greetings! Please i have an excel databse of 3 columns, my primary key is email addresses, the other ... A: sounds like a vlookup job as this can look up a given value and return values from columns to the ... | |
| VLOOKUP on network | 8/5/2010 |
| Q: Aidan, I am using vlookup from an excel file to an excel master file on a network but when both ... A: Your original explanation was very clear, it was my answer that wasn't - basically it relates to the ... | |
| word | 8/5/2010 |
| Q: If I scanned a signature and I paste it in Word in a letter. I just want to protect the signature ... A: it should work Ok with the section break - you would then need to protect the document - for this, ... | |
| Compare and Replace w/Conditions | 8/4/2010 |
| Q: I have been struggling with this for quite some time now. Your enlightened insight would be GREATLY ... A: The screenshots never come across very clearly, but if I read this right it's a simple vlookup ... | |
| word | 8/4/2010 |
| Q: If I scanned a signature and I paste it in Word in a letter. I just want to protect the signature ... A: You could put the signature in the footer area, which would make it locked in place until you edited ... | |
| VLOOKUP on network | 8/3/2010 |
| Q: Aidan, I am using vlookup from an excel file to an excel master file on a network but when both ... A: I'm afraid the only way I know of doing this is to open the files - mind you, it IS possible to do ... | |
| VBA Code | 8/3/2010 |
| Q: Expert Mr. Tom Ogilvy refered me to you. I have following code to copy range and paste in excel and ... A: Tom has also been in touch with me to give me more detail on what your plan is - I would SUGGEST ... | |
| vlookup and IF | 8/2/2010 |
| Q: I hope you can assist yet again. I want to write a formula that says look at each individual person ... A: I follow the logic, but I'm not clear where you are using it - as I say, the lookup table will ... | |
| auto sum | 8/2/2010 |
| Q: My auto sum returns a zero? I have checked the tools/options/calculation is set to automatic - I ... A: From the description, with everything else being equal, the value MUST be zero - or at least, a zero ... | |
| vlookup and IF | 8/2/2010 |
| Q: I hope you can assist yet again. I want to write a formula that says look at each individual person ... A: I'm certain it can be done, I'm just not quite clear WHAT has to be done! A basic vlookup would ... | |
| VBA Code | 8/2/2010 |
| Q: Expert Mr. Tom Ogilvy refered me to you. I have following code to copy range and paste in excel and ... A: I'm not clear why you need to copy anything - surely just emailing the word document as an ... | |
| I want to count the total number of rainy days on Sunday | 7/30/2010 |
| Q: I want to count the total number of rainy days on Sunday A B Sunday Rain Sunday Sunny ... A: Both your links were the same, but we'll let that go - the easiest way is to use sumproduct (unless ... | |
| reversing names in Excel 97 | 7/29/2010 |
| Q: I have names imported on the fly from another application into an excel worksheet column that are ... A: Sorry, I didn't make it clear - the formula shows you how to split different parts of the name out - ... | |
| Date formatting in Excel | 7/29/2010 |
| Q: When I enter a date in any cell(e.g. 07/29/2010) it replaces the "/" with "M" as soon as it is ... A: sounds like a custom format, though it MAY be down to a setting in windows control panel - easy ... | |
| Excel sheet referenced in several OTHER excel sheets? | 7/28/2010 |
| Q: I am having difficulty coming up with a solution. I have several different Excel "tracking" sheets ... A: The easiest way would be to have one master task sheet, and create as many formulas as you need to ... | |
| Copy/paste to another worksheet | 7/28/2010 |
| Q: You answered another persons question that im struggling with right now. The macro you gave was ... ... A: Depends a bit what you are checking - something like Dim holdvar For Each cell In ... | |
| reversing names in Excel 97 | 7/28/2010 |
| Q: I have names imported on the fly from another application into an excel worksheet column that are ... A: see http://excelhints.com/2009/01/24/separate-full-name-into-first-last-name/ for a discussion on ... | |
| need a formula | 7/26/2010 |
| Q: I have two different people scoring the same exam out of 100 and if their scores differ by more than ... A: good question - took me a while to find an answer - I'm assuming you are OK with the basic principle ... | |
| MS Excel Macro | 7/26/2010 |
| Q: Hey, I'm trying to find out how i can set a macro to sort an individual column out into 2 parts. ... A: I presume you actually want to sort columns 1 and 2 so that the data remains together, but that you ... | |
| Sumproduct Formulae | 7/23/2010 |
| Q: I have this sumproduct formulae I have been trying to get to work without success. It goes like this ... A: It is possible to have a dynamic range in a pivot table – I'd use a named range set to be dynamic ... | |
| Sumproduct Formulae | 7/22/2010 |
| Q: I have this sumproduct formulae I have been trying to get to work without success. It goes like this ... A: If I read this right, we are talking a multiple condition sumif? If so, it's easier to do it as ... | |
| Text formatting and cell merges | 7/21/2010 |
| Q: Aidan, I am working with Excel '03 on a spreadsheet with a lot of data pertaining to contact ... A: yes, I did mention it in passing at the end of the original answer, just not very clearly - the line ... | |
| Excel series/formula question | 7/21/2010 |
| Q: I have a table on sheet 1 of an excel document with entries in columns A and B. On sheet two, I ... A: In A3 enter =OFFSET(Sheet1!$A$1,INT((ROW()-1)/2),0) and in A4 ... | |
| Left and Right | 7/21/2010 |
| Q: Can you please take a look at this for me? Below is the code that I am trying to use. ... A: I'm actually having problems seeing how either of the functions could work as you are trying to ... | |
| Auto Populate Cells Dependant on Information in Previous Cell | 7/20/2010 |
| Q: I do not have much experience with Microsoft Excel, and I am not certain that this is a possibility. ... A: Various ways of doing it - you could have a vlookup (or similar) formula combined with an IF ... | |
| vlookup, countif or something else? | 7/20/2010 |
| Q: .. I have searched all over the place to try and figure this out. I have a column (A) with ... A: Well, I'd be inclined to do it with a pivot table, and I'm slightly puzzled as you say the lis in A ... | |
| VBA one part of my macro doesn't make any sense | 7/20/2010 |
| Q: I set count to equal the number of used rows (because I was checking for blanks for actually this ... A: The only possible thought I have is that IncompleteSchematicGeometric was not set to zero before the ... | |
| Link cells using activecell.offset | 7/20/2010 |
| Q: What I am trying to do is write VBA code that will select a cell and enter a formula in it. Now this ... A: That's odd, the ONLY error I could see would have been for N/A1 NOT to be found on the sheet. ... | |
| Link cells using activecell.offset | 7/19/2010 |
| Q: What I am trying to do is write VBA code that will select a cell and enter a formula in it. Now this ... A: No need to select the sheet - use the INSTR function to search for the text - this Sub test2() ... | |
| Text formatting and cell merges | 7/19/2010 |
| Q: Aidan, I am working with Excel '03 on a spreadsheet with a lot of data pertaining to contact ... A: I'd use a series of formulas to fix this - to the right of the existing data for the text ... | |
| paste special vba | 7/19/2010 |
| Q: i have created a macro with which, a range of cells are copied and pasted into a new generated ... A: It's odd as I would EXPECT that a paste would be a full paste generally, certainly by doing it ... | |
| Excel question | 7/19/2010 |
| Q: I have Excel and need to know how to set up a spread sheet using a formula that can help me add ... A: there are a number of sheets already on the net for fantasy football, but it SOUNDS as though it's a ... | |
| search and edit text cell | 7/17/2010 |
| Q: i have a text cell. it looks like: "word1 number1 word2 number2". i need to isolate number1 and ... A: You could use data, text to columns to seperate the values into individual cells OR you could use a ... | |
| Merging Excel Tables | 7/16/2010 |
| Q: Two questions regarding Excel Tables: 1. I have 3 worksheets w/an Excel Table on them, I want to ... A: I'm not clear what you meant by merging tables, but if it is simply copying and pasting a set of ... | |
| SUMPRODUCT Formula Assistance | 7/16/2010 |
| Q: I hope you are well and can spare some time to help me with a formula, you have helped me before and ... A: difficult to see from the picture (probably my eyesight!) - if it helps, you can always mail me a ... | |
| concatenate first names in a sibling list | 7/15/2010 |
| Q: i have a list of siblings, in excel. Instead of mailing 2-5 letters to a household, I would like to ... A: I would use a pivot table to consolidate the records, and then a series of vlookup formulas to bring ... | |
| Excel COUNTIF with multiple criteria | 7/15/2010 |
| Q: I have a list of customers, and each customers transaction is listed by date with contact ... A: found your question in the question pool - IF it's excel 2007, then SumIfs has come in, otherwise ... | |
| alphabetized list | 7/14/2010 |
| Q: I am in excel 2003 I have an alphabetized list. Through a button a user will enter a name. I want ... A: I would SUGGEST storing the extra data by means of a button as well – I would have the button fire ... | |
| Date Update | 7/13/2010 |
| Q: I have a timetable at work which I created for myself on MS Excel. On this timetable I formatted the ... A: =today() this gives you the current day and =now() gives you day and time Not sure what the ... | |
| Excel Sheet Help | 7/13/2010 |
| Q: I need help on an excel line chart. I've got data that represents daily numbers. These numbers ... A: Various thoughts, but the easiest one that springs to mind would be to make the axis a fixed scale, ... | |
| Excel Input Horribly Slow | 7/11/2010 |
| Q: In the last few months I have noticed inputting (typing) into Excel is horribly slow. For instance, ... A: I'm not aware of anything specifically that would do this, though I am prepared to research it for ... | |
| Excel macro | 7/8/2010 |
| Q: I need help with a macro please. I need to be able to delete all rows up to where the phrase ... A: Sub forAE_Delete() Set currentCell = Range("b1") Do While InStr(LCase(currentCell.Value), ... | |
| merging existing directory with spread sheet | 7/6/2010 |
| Q: I have learned how to mail merge labels and am trying to update a directory in the same way, but am ... A: My concern is that it sound as though you want to UPDATE entries that already exist in the directory ... | |
| problem with a button to copy inputs into a database | 7/6/2010 |
| Q: I am trying to program a button on an excel worksheet (in excel 2003) that is designed to work as a ... A: The code itself looks fine, but you haven't told me the error message - a streamlined version of the ... | |
| Excel 2007 is not picking up my system date (specifically the year) | 7/4/2010 |
| Q: I dearly hope you can help me. I just installed Excel 2007 on my new Dell Inspiron laptop with ... A: It's very odd, and I wouldn't expect a new laptop to be confused about the date - BUT I cannot find ... | |
| Help with formula | 7/2/2010 |
| Q: I'm trying to write a forumla for a file i'm creating in excel but i have hit a brick wall with ... A: You won't be able to do it with a formula, as what you want is when a certain condition becomes ... | |
| Counting Coloured cells | 7/1/2010 |
| Q: In continuation of your answer posted in 2008 ... A: Function colorcount(myvar As Range, ColVar As Long) application.volatile For Each cell In myvar If ... | |
| Need help with Excel Macro/function | 7/1/2010 |
| Q: I need to create a macro or some kind of formula to compare two different excel books. My knowledge ... A: It sounds as though a simple COUNTIF function should do the job for you ... | |
| Extract data from received mail to excel sheet | 7/1/2010 |
| Q: I want the macro for the received email to outlook in the excel sheet. The requirement is like this: ... A: Sub ImportFromNewsgroups() Dim counter counter=2 MsgBox "Before proceeding, make sure that the ... | |
| Counting Coloured cells | 6/29/2010 |
| Q: In continuation of your answer posted in 2008 ... A: You can add application.volatile at the start of the function - this will make the function ... | |
| Extract data from received mail to excel sheet | 6/29/2010 |
| Q: I want the macro for the received email to outlook in the excel sheet. The requirement is like this: ... A: Are you talking about getting data from ALL mails in a folder, or just one mail? This is ... | |
| merging existing directory with spread sheet | 6/28/2010 |
| Q: I have learned how to mail merge labels and am trying to update a directory in the same way, but am ... A: I'm not clear what it is you are trying to achieve here - I presume this is a word directory of data ... | |
| Linking | 6/25/2010 |
| Q: my question is: I have 5 source spreadsheets and one consolidated sheet. I need to have the ... A: I take it that you want to link one cell to one cell - my only concern here is that you say you have ... | |
| VBA | 6/24/2010 |
| Q: I have the following code Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 ... A: Sorry, that was silly of me while lcase(holdvar)<>"yes" and lcase(holdvar)<>"no" ... | |
| VBA | 6/23/2010 |
| Q: I have the following code Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 ... A: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 And Target.Row > 7 Then If ... | |
| Automation Of Production Report | 6/23/2010 |
| Q: I have my datas in a workbook. i want to create a report on monthly basis in another workbook. it ... A: I would need more information to be able to help specifically, but if the information in the report ... | |
| formula to copy cells in excel | 6/22/2010 |
| Q: In excel i have a sequence for winning and losing stats in the following sequence W,L,W,L,W,L,W,L ... A: Try =OFFSET(B2,INT((ROW()-10)/2),0) where B2 is the starting value, and 10 is the number of the ... | |
| MS Excel 2007- Filtering Issue | 6/17/2010 |
| Q: Good Day Aidan, I have a MS Excel 2007 shared spread sheet, where 8 users are updating sheet ... A: I'm afraid my advice on shared workbooks is the same as most experts you will find - don't use them, ... | |
| Embed Excel Spreadsheet and Linking Throughout Word Document | 6/16/2010 |
| Q: I am writing a report template (though it is not saved as a template) in word that will be used for ... A: I don't tend to work with embedded sheets as I've always had problems with them - however, the ... | |
| DDE links to other excel on ntework drive through VBA | 6/15/2010 |
| Q: I'm looking for a solution for a potential bug with "edit links" programmatically in excel 2007. I ... A: I don't think it's the code that it at fault, as networked files don't always update properly in my ... | |
| Exporting Data | 6/15/2010 |
| Q: hope you can help me. I need to export rows of specific data contained in one file to another and ... A: The non automatic method would be data, filter, auto filter - select the drop down arrow on B and ... | |
| Select Data | 6/15/2010 |
| Q: Excel 2007 Suppose, I have an excel file with 5 columns: Student Name, Nationality, Country, City, ... A: (1) could be done with an array filter (see ... | |
| Excel Matrix Look Up | 6/11/2010 |
| Q: I am trying to return a value to a cell based on a matrix lookup (with the matrix being on a ... A: It took me a couple of reads, but yes it was clear - see ... | |
| Excel | 6/10/2010 |
| Q: I have two fields with different data. Column A has a 10 digit non repeating number that will always ... A: Your formula won't work as you have >= the text string 45 - which is nearly ok, but would allow 46* ... | |
| Show Automatic additional info | 6/10/2010 |
| Q: "Hi, I am making a database in MS excel of people that are members of my club that are contributing ... A: I would TEND to do databases in Access as that is what it is for - doing this in access would make ... | |
| Macro in Excel workbook | 6/10/2010 |
| Q: I am stuck with a problem. We perform real time temperature monitoring of few processes. I was ... A: Don't quite follow what the trigger is - do all columns need to have a value, or do we just look for ... | |
| Excel Formulas | 6/9/2010 |
| Q: I am trying to figure out a formula. I am trying to count all the cells in a given range that are ... A: There isn't anything built into excel directly to do this, but you can do it with a user defined ... | |
| Excel Question | 6/9/2010 |
| Q: I have created a form in excel. My question is, is there a way to set up a form so that it has a ... A: Not as such but see http://www.aidanheritage.byethost3.com/excel/invoice.xls which uses VBA - in ... | |
| Organising text automatically | 6/8/2010 |
| Q: Hoping you can help... I have a self-assessment questionnaire in which people choose responses to ... A: ="You are " &INDEX($A$1:$A$4,MATCH(LARGE($B$1:$B$4,1),$B$1:$B$4),1) &" and " ... | |
| Horizontal Unique values | 6/7/2010 |
| Q: I hope you are well. My question is similar to one I have previously asked but hopefully it’s more ... A: The description says the data is vertical and needs to be returned horizontally, but the formula ... | |
| Staff on holiday | 6/6/2010 |
| Q: I need to make sure not more than 2 members of staff are taking a holiday in each dept. I have a ... A: insert ribbon then pivot table follow the wizard, and then (as I hate the helpful new way of doing ... | |
| Staff on holiday | 6/6/2010 |
| Q: I need to make sure not more than 2 members of staff are taking a holiday in each dept. I have a ... A: I'd be INCLINED to do this with a pivot table (possibly with conditional formatting) - the pivot ... | |
| Excel 2003 | 6/4/2010 |
| Q: I have text(products) on sheet 1 in column D(in random order) that I have listed with each customer ... A: Not sure what has gone on with that file - if you email I can send you a real copy of it - as to the ... | |
| Floating Text Box | 6/3/2010 |
| Q: How can I create a floating text box that moves up and down when scrolling on a spreadsheet? ... A: I've never had cause to do this - there isn't (that I know of) anything built into Excel that would ... | |
| Locking a folder (password protection) | 6/2/2010 |
| Q: There must be a way to put a file folder in a central location but have a password so that only ... A: Ah, that's what you want - no problem, use the SAVE AS option from the office button, and use tools, ... | |
| Locking a folder (password protection) | 6/2/2010 |
| Q: There must be a way to put a file folder in a central location but have a password so that only ... A: Folders are a function of the operating system - it is certainly possible in Windows using user ... | |
| Excel 2007 formula for multiple spreadsheets | 6/1/2010 |
| Q: I am building a spreadsheet in Excel 2007 for a friend in order to help him keep track of a small, ... A: I'd be INCLINED to do this in Access rather than excel as that allows for the system to be ... | |
| Masking Passwords | 5/28/2010 |
| Q: I currently have a macro that prompt am input box for a user to put in a "Password". Is there a way ... A: There isn't any way that I know of to do this with inputbox, but if you change to a user form you ... | |
| excel sumif formulae | 5/28/2010 |
| Q: Can I abbreviate this formula to something more manageable...? ... A: There isn't an inbuilt sumif across multiple worksheets, so without building a User Defined Formula ... | |
| Excel: Multiple tables combined, now partial duplicate rows | 5/27/2010 |
| Q: I was given several Excel files containing contact information. Each had different column headers, ... A: I think the easiest way (if not the only realistic way) to do this would be via a macro - is there ... | |
| How to export data into excel in multiple tabs | 5/26/2010 |
| Q: I want to export data into multiple tabs in excel based on a criteria. How can i do it. For example ... A: I would do this with a macro - without that, then it takes a series of filter, autofilter, copy and ... | |
| Sum after last row and delay | 5/25/2010 |
| Q: I am trying to find out the following: 1. How do I find the last row of a set of data and add ... A: I would TEND to use the FIRST row to do this sort of sum as it's a lot easier, but a macro could do ... | |
| List all dates between start date and end date | 5/24/2010 |
| Q: I have an Excel 2007 spread sheet that is set up to audit an inventory. The question I got stuck on ... A: Sorry for the delay in replying - work week was long and hard - I would do this with an array ... | |
| date format in message box | 5/20/2010 |
| Q: How do you format a message box so it reads for example "Thurs 20 May 2010". Cell 13,8 is formatted ... A: I'm not aware of any way to do this in a message box, though you COULD use a user form to achieve a ... | |
| Excel has a mind of it own ! | 5/19/2010 |
| Q: my name is Lee. I just got back from work and have had the day from hell. I have been having ... A: the ### is usually due to display size - if the cell isn't wide enough, excel displays that instead ... | |
| macro and sheet name | 5/18/2010 |
| Q: I am writing a macro that will pull info from numerous excel files into 1 spreadsheet. Each of the ... A: I don't tend to like copy and paste within macros - in this instance it seems to be that you are ... | |
| Finding the Mode Column in Excel 2003 | 5/18/2010 |
| Q: I am using excel 2003, and I am trying to determine the mode column. For example: I have a multiple ... A: mode works fine with numbers, but not with letters - SO you can do this by converting your letters ... | |
| Count If Statement | 5/14/2010 |
| Q: I need the syntax for the following: Count if A1=1 AND B1 is >0 but <8 Can you help me? Thanks ... A: You didn't give me version of excel, which could make a difference - the global method for doing ... | |
| fields from word to excel 2003 | 5/14/2010 |
| Q: I'm using '03. I need to transfer certain info from word files to a single excel. I can open the ... A: I take it you want to do this with VBA - I can't think of an easier way of doing it, so the basic ... | |
| Highlighting cell reference | 5/13/2010 |
| Q: I have a worksheet that contains pricing data for the same items from many different vendors. These ... A: (1) can be achieved with conditional formatting and (2) with a match function - though I'm not 100% ... | |
| How to rename cell names in many tabs using VBA and Macro? | 5/12/2010 |
| Q: I am trying to rename many cells in 5 different tabs. For example A1 is currently = TEST and I want ... A: A macro can refer to all sheets, or specific sheets - so for instance for each sh in ... | |
| Excel 2003 link problem | 5/12/2010 |
| Q: I have two Excel files. One is the input & the other one has links from the input files. They are ... A: If your customer can duplicate the exact structure of the file location then it should work, but ... | |
| Excel 2002 printing entries on separate pages | 5/10/2010 |
| Q: I have a worksheet that contains records for over 1000 staff. Within the workbook is another sheet ... A: I’d probably do this via mail merge using Word, but that said, this macro should do what you want – ... | |
| dynamic range with offset | 5/9/2010 |
| Q: 7 months of learning excel, and im still feeling like i can never make it dow hat I want, oh how i ... A: 586 downwards actually - and no, that isn't what keeps it dynamic - it will extend downwards as you ... | |
| dynamic range with offset | 5/9/2010 |
| Q: 7 months of learning excel, and im still feeling like i can never make it dow hat I want, oh how i ... A: Actually, your formula is fine BUT if you press F5 (goto) and type DATE then OK you'll see the ... | |
| Ranking | 5/8/2010 |
| Q: I took a dollar amount and divided it by a monthly payment and got the following (Numbers) and ... A: I would imagine you have a formula in the NUMBER column - replace this with a formula similar to ... | |
| Excel Formula | 5/8/2010 |
| Q: I wants to retrieve a data from the list of database, if three critierias(one text and two date) are ... A: The formula I gave you gives you the row number of the data - using INDEX allows you to get the data ... | |
| Manual Copy/Paste Limit in v2007 | 5/5/2010 |
| Q: I have a worksheet with 40 columns and 1 row. When I highlight this row (all 40 cols) and copy it ... A: I'm afraid I have pretty much the same solution - ... | |
| Excel Formula | 5/5/2010 |
| Q: I wants to retrieve a data from the list of database, if three critierias(one text and two date) are ... A: You can either do this with an array filter (see ... | |
| how to search data in excel sheet and display the result of search in another sheet | 5/4/2010 |
| Q: I have a excel worksheet containing employee records such as name, their skill and level of ... A: Difficult to do without seeing the sheet, but it's a relatively simple process - I imagine you would ... | |
| creating link from cell to another worksheet in same workbook. | 5/1/2010 |
| Q: I will start by saying that i am not an excel expert!. (so need fairly clear instructions) I wish to ... A: Yes, no problem at all - insert hyperlink as you were going to, but then click on the places in this ... | |
| creating link from cell to another worksheet in same workbook. | 5/1/2010 |
| Q: I will start by saying that i am not an excel expert!. (so need fairly clear instructions) I wish to ... A: I was alright with this until you said you wouldn't use the insert hyperlink option - a hyperlink ... | |
| Printing visible rows not hidden rows | 4/30/2010 |
| Q: XL 2003 Can you please remind me how to print only the visible columns and rows displayed when I ... A: You said printing - if data is hidden, printing that sheet would only print the unhidden area - but ... | |
| Daily Interest with Payments | 4/30/2010 |
| Q: I work at a law firm and this is what I'm trying to do. We have a Judgment or in our case ... A: Sorry for the delay in replying - it's been rather a hectic week at work and home. Effectively, you ... | |
| Find command in a VBA macro | 4/29/2010 |
| Q: I want to be able to use the find command in a macro to look for a text "either numbers or a word" ... A: What I don't know is what the macro is trying to do (other than find) but I wouldn't tend to use a ... | |
| Microsoft Excel | 4/28/2010 |
| Q: I have multiple values in a row (E2 to E467) and I want to decrease all of these values by 20%? Is ... A: Yes it is - though with a proviso that you don't need the values rounding! put .8 in a blank ... | |
| conditional formatting | 4/27/2010 |
| Q: I have a spreadsheet which is seperated into columns and there are numbers (or sometimes text) in ... A: The * character is always a pain in a string, as it's a wild card - but try =code(yourCell) this ... | |
| Minmum date and maximum date | 4/27/2010 |
| Q: in my excel sheet 11/15/39, 08/15/39, 05/15/39 my question is when i enter this types of date ... A: I'm not clear what you had in mind as you mentioned "on the basis of month and year" - to get a ... | |
| Windows 7, gmail | 4/24/2010 |
| Q: I recently purchased a new computer with OS Windows 7. I have an email address through GMAIL. I ... A: This isn't a word question, it's really a gmail question - but see ... | |
| VBA - button | 4/23/2010 |
| Q: Good day Aidan, I have created a simple macro to reformat several pivot table that are one below ... A: Not sure what you meant by "copy paste" but I'd have each macro as something like sub ... | |
| Finding the last value in a column | 4/22/2010 |
| Q: I asked this question yesterday and received an answer to a different question. Please see: ... A: I'm assuming you want to return the last value, and that this is the last used row, and that there ... | |
| Creating a Progressive Macro | 4/21/2010 |
| Q: My office uses excel to send out funding authorizations to different programs in our organization. ... A: Ideally I'd do this with a button or custom menu item to ensure that it took place when you wanted ... | |
| Formula Help? | 4/21/2010 |
| Q: . I have a formula to recognize the anniversary dates of my employees of a specified period (1yr, ... A: firstly, you don't need the IF ... | |
| How to modify the Find function | 4/20/2010 |
| Q: I am pulling data in from a website and have to use the 'Find' (Ctrl + F) function to locate the ... A: I wouldn't use the find function in code - I'd PROBABLY do a for next or while wend loop until I ... | |
| Comment boxes are moving - on their own! | 4/20/2010 |
| Q: On January 30, 2008, someone asked you a question about Excel comment boxes moving and changing ... A: I may well have meant data validation - not sure what (or how many) comments we are talking about, ... | |
| Comment boxes are moving - on their own! | 4/18/2010 |
| Q: On January 30, 2008, someone asked you a question about Excel comment boxes moving and changing ... A: I haven't yet tried office 2010 and can find nothing to answer the question - but see ... | |
| Copy cell color | 4/17/2010 |
| Q: I am trying to copy the cell color from one sheet to another. What I need is when I change the ... A: IF the cell colour is based on conditional formatting, then copying the formatting rule (as long as ... | |
| mastersheet relationship with secondary sheets | 4/15/2010 |
| Q: We have a workbook with 6 secondary spreadsheets behind the mastersheet. For some reason, the ... A: If this is controlled as seems likely by formulas, then it will ONLY pick up data where formulas ... | |
| Restricting paste in cells containing validation | 4/13/2010 |
| Q: "Hi, I'm using MSExcel 2003. Question in a simplified manner - I have two headings - A1 is City, and ... A: The problem with macros is that users can disable them if they choose - so this might be a problem - ... | |
| Formula to calculate 2 days before a due date | 4/12/2010 |
| Q: i.e. date of receipt and due dates. I have the receipt date in B1 and the due date in C1. I would ... A: use the formula option (as you guessed) and use =c1<now() to set the PAST due date and (as the ... | |
| extended conditional formatting | 4/12/2010 |
| Q: I asked Chad a question about extended conditional formatting and he pointed me to an article he ... A: I'm not sure I've got enough to go on here - at it's simplest, you could use the ACTIVATE event of ... | |
| Excel 2007 | 4/11/2010 |
| Q: Aiden- I have a column of numbers A:1 to A:1000, associated with a column of names B:1 to B:1000. I ... A: and elsewhere a list of delinquency dates? If so, a set of formulas could be used to produce the ... | |
| Auto-populating data from one spreadsheet to another | 4/8/2010 |
| Q: I have an Excel 2003 workbook with 2 spreadsheets. On one spreadsheet I have multiple cells with a ... A: I've read this question a few times and I'm having problems understanding the question - probably ... | |
| Functions | 4/8/2010 |
| Q: What I am trying to do sounds complicated but its very simple. I have a list of hundreds of ... A: I THINK that you can probably do this with a pivot table - use this to get the BASIC data for the ... | |
| Overtime Calculation | 4/7/2010 |
| Q: We use a spreadsheet that calculates hours worked for the pay period. It separates regular hours and ... A: I think I probably get asked about time sheets/overtime more than anything else on allexperts (with ... | |
| MACROS FOR EXCEL | 4/7/2010 |
| Q: i hav ms office 2003 i have two excel sheets & i need to copy some data from 2nd sheet to 1st sheet ... A: I wouldn't do this with a macro, though if it's essential one could be used - instead, I would use ... | |
| Intellegent Counting in Excel? | 4/6/2010 |
| Q: I have a question with a spreadsheet that I am manually pulling from another database. My goal raw ... A: it is confusing - which is possibly why it ended up in the question pool - I'm having problems ... | |
| Excel 2007 | 4/6/2010 |
| Q: Aiden- I have a column of numbers A:1 to A:1000, associated with a column of names B:1 to B:1000. I ... A: Sorry for the delay in replying -this somehow got lost in the system (my system anyway!) - I would ... | |
| Automatically insert different filename in a formula | 4/6/2010 |
| Q: how can i automatically insert different filename in this formula: ... A: I'm not quite sure what you had in mind here - an INDIRECT worksheet function or a search and ... | |
| Need Help in summing up number within a cell | 4/5/2010 |
| Q: Here's the scenario I have this set of numbers 3864-8 in A1 ,3496-333 in A2 and this goes to let ... A: This array formula will do it =SUM(VALUE(RIGHT(A1:A2000,LEN(A1:A2000)-SEARCH("-",A1:A2000)))) ... | |
| excel format... kinda | 4/1/2010 |
| Q: I am trying to create an inventory that is several pages long and that must be printed several times ... A: I THINK you would be best to do this in one of the following ways (1) use INDIRECT function to get ... | |
| Exporting Data at Half Second Intervals to Different Cells | 3/31/2010 |
| Q: I have been wrestling with this for a while and am hoping you can help. I have a piece of trading ... A: it would PROBABLY need to use the CHANGE event of the workbook to pickup that data had changed in A1 ... | |
| changing multiple cells' colors | 3/30/2010 |
| Q: I have a workbook with multiple worksheets. Within each worksheet are tables with columns of ... A: OK that would work - can you mail me (I cannot see your email address on this system) and I can then ... | |
| changing multiple cells' colors | 3/30/2010 |
| Q: I have a workbook with multiple worksheets. Within each worksheet are tables with columns of ... A: a cell has an interior shading which would be a given number, so it would be easy to set it back to ... | |
| Excel 2000 YTD Formula | 3/30/2010 |
| Q: I have a file that contains worksheets for each monthly employee scorecard. There are six criteria ... A: I think you need to use the COUNT worksheet function to count how many entries contain numbers - so ... | |
| sum to the right | 3/30/2010 |
| Q: I have a years worth of data in columns labelled P1 to P12. My sum column is at start of sheet ... A: certainly - you can use the match function to handle this ... | |
| excel column capability | 3/29/2010 |
| Q: we currently use excel 2003. we are running into a problem in that we are finding that we are ... A: The version after 2003 was 2007 - and that's the first version that's gone up to the higher binary ... | |
| Excel Macros | 3/26/2010 |
| Q: I am almost a brand new user to Excel and have been trying to teach myself as I go along. I've been ... A: difficult to answer without seeing the file - my email is aidan.heritage@virgin.net - there is ... | |
| IF Formula | 3/26/2010 |
| Q: I'm trying to disect this if formula - =IF(F11="","",IF(H11=0,0,IF(K11=0,0,H11/(H11+K11)))). I ... A: =IF(F11="","",IF(H11=0,0,IF(K11=0,0,H11/(H11+K11)))) - translation if f11 contains nothing then ... | |
| Changing Font Size of Dropdown Lists | 3/26/2010 |
| Q: I have created a data entry form in Excel 2007 to be used by users. I have created some dropdown ... A: The only solution I know of is to increase the zoom size of the spreadsheet - the data in the boxes ... | |
| automatically changing colours in excel | 3/25/2010 |
| Q: I have an excel spread sheet with dates in it. I want to know if it possible to have excel ... A: that will be no problem at all - my example formula would be the one to use for a date of 3 months ... | |
| automatically changing colours in excel | 3/25/2010 |
| Q: I have an excel spread sheet with dates in it. I want to know if it possible to have excel ... A: You didn't specify version, but unless you are on a REALLY old one then you need to simply look at ... | |
| Choose formula based on value | 3/20/2010 |
| Q: I have 4 different formulas in cells D3:D6. I want to set the formula for cell D7 to be one of the 4 ... A: Various ways of doing this – IF the values are actually 1,2,3 and 4 then =offset(d2,a8,0) would ... | |
| Oracle Query | 3/18/2010 |
| Q: I have Oracle 9i and Excel 2002. How can I query the Oracle database using VBA code within a macro. ... A: I know nothing about Oracle, and I think this question would need knowledge of that to get some ... | |
| COPY ARRAY OF WORKSHEETS. | 3/18/2010 |
| Q: I am new to Excel programming. I have one workbook, which is having a sheet containing names of all ... A: Dim myxl as workbook Set myxl=activeworkbook Dim sheetNameVar(14) as string Dim looper as long For ... | |
| macros | 3/18/2010 |
| Q: I would like to ensure that the macros that i have written appears on every workbook that i open in ... A: You have replaced the formula with another formula which contains the result of the first one - try ... | |
| Excel Macro Help | 3/17/2010 |
| Q: Heritage, I am creating a spreadsheet which shows spreads between various numbers between months. ... A: I’m slightly puzzled about why the data needs to be typed – if this data exists somewhere already ... | |
| excel | 3/17/2010 |
| Q: i want to write a entire row value in another sheet where the one cell value is same to the given ... A: You can do this with macros or with an array formula – I’ll assume array formula will be easier for ... | |
| Making a list based on multiple inputs | 3/16/2010 |
| Q: I am designing my own DVD collection sheet, and as I advance and enter more and more details, I need ... A: Easiest fix is to use the autofilter option then custom and select contains – anything that contains ... | |
| Pivot Field Name Screen Tips | 3/16/2010 |
| Q: There, Id like to know if there is a way to create screen-tip type labels for fields in a pivot ... A: Nice idea, I’m not aware of a way of doing it I’m afraid - mainly due to there not being a mouseover ... | |
| macros | 3/16/2010 |
| Q: I would like to ensure that the macros that i have written appears on every workbook that i open in ... A: Store them on the personal.xls workbook – this is an option when recording (record at least one ... | |
| Complicated Excel question for medical research | 3/15/2010 |
| Q: I desperately need your help! OK, so I have a data set of 50,000 patients that I need to do some ... A: I THINK that data, pivot table and chart report would PROBABLY solve this for you - drop the fields ... | |
| Excel | 3/15/2010 |
| Q: I keep a log of vehicle information for all vehicles that enter through the gates of a private ... A: Because you have multiple sheets, it is going to be tricky, though not impossible – ideally the ... | |
| excell | 3/15/2010 |
| Q: What formula in excel would you use to find the number of invoices dated between 7/1/2005 to ... A: As you haven't specified a version of Excel I'll assume that you are on excel 2007 - at which point, ... | |
| AutoFilter in macro | 3/15/2010 |
| Q: its showing as ctiveSheet.Range("$A$1:$V$202").AutoFilter Field:=5, Criteria1:="100056" ... A: I would run a macro that went through each row and IF the data matched, that row would be copied to ... | |
| Advanced "IF" funciton | 3/12/2010 |
| Q: Is there anyway you can automatically transfer data from one worksheet to another using the "IF" ... A: You can do this in two ways - one would physically transfer the data, and would be done by using a ... | |
| Macro: parse elements of a sum() | 3/11/2010 |
| Q: I have cells that have sum functions in them: =SUM(60,120,0,130) The sum will always have 4 numbers ... A: Sub Parser() Dim n As String n = Range("a1").FormulaR1C1 Dim holdvar(3) Dim counter As Long n = ... | |
| copy a format while linking from another spreadsheet | 3/10/2010 |
| Q: I am trying to copy a format by link from a seperate spreadsheet but cant seem to figure it out. ... A: Not sure what you need to achieve here - if the format is going to change, then you aren't going to ... | |
| excel help | 3/9/2010 |
| Q: I am trying to get data from a row in several columns into one part. the excel format looks like ... A: Well, this sounds like a mail merge - that would be the usual way to do this - IF it's only ONE ... | |
| Excel 2000, keystrokes to delete row or rows | 3/8/2010 |
| Q: Using Excel 2000, would like to know the the shortcut keystrokes to delete a row or selected rows. ... A: that (as far as I know) is the keyboard shortcut - but please feel free to press F1 and double check ... | |
| macro | 3/8/2010 |
| Q: I wonder if you could help me with this macro. The following is a macro you helped me with some time ... A: range("AE107").value="LC" & range("AE107").value this should fix the problem - the macro will run ... | |
| Pull text field with combination of Text and Date field | 3/8/2010 |
| Q: My Master table shows effective Duty Rates of materials. Hence Transaction Table searches Master ... A: Not sure why the sumprodut is using a vlookup – IF I follow the question correctly you want to ... | |
| Deleting certain values | 3/7/2010 |
| Q: Aidan, I have a sheet in a single Excel workbook with formulas that reference other sheets in ... A: Various ways of achieving this but probably the easiest is a simple search and replace – search for ... | |
| Remove words in a cell | 3/6/2010 |
| Q: Contents of cell A1 = Po Box 678876 Springfield Contents of A2 = 156 Main Road Neverland Cell ... A: The only way I can think of to do this is with a custom function – Press Alt F11 to get into the ... | |
| VBA Code for multple criteria | 3/6/2010 |
| Q: I'm trying to come up with a VBA code that will combine 2 "search parameters" The first being a date ... A: I tend not to use the search function within VBA, rather preferring to loop through the cells – this ... | |
| Convert to Proper Text in Excel 2003 | 3/5/2010 |
| Q: I have multiple columns of data in Excel 2003 which I would like to convert from all caps to proper ... A: OK, there are two possible ways to go (1) use a helper column - in this case, you enter the ... | |
| macro | 3/5/2010 |
| Q: I wonder if you could help me with this macro. The following is a macro you helped me with some time ... A: Looper is a variable defined as a number - so LC1 won't work as that isn't a number - what did you ... | |
| Excel Cells - Forbid usage | 3/5/2010 |
| Q: I have a program that allows me to copy many pieces of information to my clipboard and then paste ... A: although you can lock cells from being used, this would simply cause the paste to fail - if you have ... | |
| VB Code for selected area in sheet | 3/5/2010 |
| Q: Please check attached image for my question. In my sheet there is 5 students named A,B,C,D,E. They ... A: Surely the easiest process would be for the macro to hide the rows that have no data, then print the ... | |
| Convert to Proper Text in Excel 2003 | 3/4/2010 |
| Q: I have multiple columns of data in Excel 2003 which I would like to convert from all caps to proper ... A: You could do this with a macro, but the easiest way would be to do it the way you have started - use ... | |
| Excel | 3/3/2010 |
| Q: I am doing a vlookup and I would like for the result to pull the contents of the cell as well as the ... A: short answer is no, but you COULD use VBA to possibly do it - though it wouldn't be with a function ... | |
| Find more than one item | 3/3/2010 |
| Q: I am wondering if it is possible to use the search function for more than one item - meaning - I ... A: You cannot use the search function, but you can use COUNTIF to do this - if you do a COUNTIF of your ... | |
| Excel: no of pages | 3/3/2010 |
| Q: I have recently synced a laptop to my pc. However, when I go into excel it defaults to producing ... A: The USUAL answer to this would be down to formatting - if you press Ctrl End on your keyboard you ... | |
| finding and counting text boxes | 3/2/2010 |
| Q: Is there a way to find all the text boxes in a range and to populate their names into a list? (the ... A: You can do this with VBA – for example Sub counter() Dim sh For Each sh In ActiveSheet.Shapes ... | |
| Excel - Duplicate Values | 3/2/2010 |
| Q: I have a spreadsheet with about 5,000 rows and 25 columns. Basically it contains data on customers ... A: I would still probably use a countif function - put this in a helper column, then filter on all ... | |
| weighted average | 3/2/2010 |
| Q: I have 2 columns in spread sheet, one is with cost and one is with discount(%) given to the actual ... A: I’m not quite sure I follow what it is you want to do – at the moment it sounds as though ... | |
| Macro Protection in Excel 2007 | 3/1/2010 |
| Q: I have a workbook where I have password protected each sheet and the workbook as a whole, so that no ... A: I've used windows 7 on my machine to do this and had no problem - I've saved the file as a macro ... | |
| Excel Macro: Main to Sub | 3/1/2010 |
| Q: I have a tab named "Main" it has this data: Tab Name Update Date Entry ... A: Sub ae() Dim OutCounter As Long Dim MainCounter As Long For MainCounter = 2 To ... | |
| Copying rows based on multiple dates. | 2/26/2010 |
| Q: Aidian, I have a spreadsheet named "Database" that contains three dates in column J2, K2 %26 L2.. ... A: I would tend to use something along the lines of if test1 or test2 or test3 then sheets("Due in ... | |
| Seed spacing excel sheeet | 2/24/2010 |
| Q: I am attempting to create a excel sheet to help me with maximising seed to be spaced in a specific ... A: I love the question, but I admit to not being sure of how one would go about doing this - I'm ... | |
| Label Scatter Plot Points from a Filtered Worksheet | 2/24/2010 |
| Q: I am using MS Excel 2007. I have a Scatter Plot chart that I want to have a corresponding label for ... A: I would imagine working with a filtered list would present problems and I've not found a way round ... | |
| Excel formulas | 2/23/2010 |
| Q: A little bit of history about the reasons for my question. Many years ago I did a fantasy football ... A: firstly, this google search ... | |
| horizontal line—cannot change the color | 2/23/2010 |
| Q: When I insert a horizontal line in Word 2007 and try to format it, the Color option is unavailable. ... A: The first question needs to be - how did you insert the line - IF I was doing it, I would go to the ... | |
| excel | 2/23/2010 |
| Q: This is venkat,i am working as RA.I had one simple doubt,that in one worksheet i had 2lakhs data and ... A: This SOUNDS like it's probably vlookup - this file ... | |
| Combination Query. | 2/22/2010 |
| Q: Respected Sir, Suppose, I wish to make a matrix of 25 numbers which I have to choose from the range ... A: The answer I gave at http://en.allexperts.com/q/Excel-1059/combinations-2.htm will basically give ... | |
| 3-D excel sheet | 2/21/2010 |
| Q: I want to have a 3-Dimensional table which has 1: Process ( 10 processes in total) 2: Roles ( 20 ... A: Your described solution is 2D - what I don't follow is how it is 3D - in that, do all processes need ... | |
| Creating a list of matching column cells | 2/20/2010 |
| Q: I have two columns each with unique values. I want to create a third column with a list of the two ... A: Argh- sorry - I spotted the error - when I first read the question I thought I saw data from row1 - ... | |
| Creating a list of matching column cells | 2/20/2010 |
| Q: I have two columns each with unique values. I want to create a third column with a list of the two ... A: I would do this with an array formula ... | |
| Excel Deleting Group Duplicate Data | 2/18/2010 |
| Q: I have 2 columns of data. One is USERID and the other one is DATE JOINED. However, in the USERID ... A: I'm not quite sure I follow what you need to do , but if it is simply a case of deleting cases where ... | |
| Inserting docs into cells | 2/17/2010 |
| Q: How can I successfully insert a document from Word into a single cell in Excel? Every time I try to ... A: Not sure what you have done to do this - is it a hyperlink that is too long to display in one cell, ... | |
| Excel Problem | 2/17/2010 |
| Q: I want to count the amount of children that are eight or over eight AND are Boys. The ages are in ... A: well, the formula should return the right result but do all the girls entries actually have G - or ... | |
| Add Input Box to Rename Sheet | 2/16/2010 |
| Q: Microsoft Excel 2007. I am a fairly new user of VBA. How would I add a sheet a report and then add ... A: You didn't give me any clues about what sort of sheet needed to be added - so I've assumed a blank ... | |
| splitting data from 1 cell into 4 cells | 2/16/2010 |
| Q: In cell A1 on my excel sheet I have 4 pieces of data, example - Canada Can 1991 ... A: Hopefully your 100's of cells are all in the same column! If so, you will need to have three blank ... | |
| merged cells problem | 2/13/2010 |
| Q: Aidan: This code misfires because columns G and H are merged, what's the work around? Sub ... A: Ah, no it won't - it should - I admit to NOT working with merged cells very often as they aren't ... | |
| merging and consolidating | 2/13/2010 |
| Q: I have 5 excel sheets.I need to merge unique employee ID's from onlycolumn A from all workbooks to a ... A: Difficult to debug from the code alone – my method for debugging this would be to single step ... | |
| states equals a territory label formula or lookup? | 2/12/2010 |
| Q: I have a single worksheet with leads from a trade show and I would like to assign a territory in a ... A: I'm sure this should be straightforward - I'm just finding it hard to visualize! Is it possible to ... | |
| excel formula | 2/12/2010 |
| Q: i have to calculate the amount in Rupees an employee will get when he works overtime in the ... A: See http://www.aidanheritage.byethost3.com/excel/Time%20Card.xls for a method I created for another ... | |
| Formula Help | 2/11/2010 |
| Q: I have two cells with time in (Cell A), time out (Cell B) and in (Cell C) I want a formula that ... A: It depends what you want to do with column C - at it's simplest, =b-c and format the cell as [mm] ... | |
| Hyperlinks | 2/10/2010 |
| Q: Is there such a thing as a function that will give you the hyperlink address of a cell? For ... A: difficult to fix in that case - it WILL update when a calculation occurs, but changing the URL of a ... | |
| Hyperlinks | 2/10/2010 |
| Q: Is there such a thing as a function that will give you the hyperlink address of a cell? For ... A: I tend to write my udf's as non volatile, which means they only recalculate when forced - this is ... | |
| Consolidation of data | 2/10/2010 |
| Q: I have 5 excel sheets.I need to merge unique employee ID's from only column A from all workbooks to ... A: Your macro will open files and move the sheets - I tend to declare a variable to relate to my ... | |
| Excel spreadsheet | 2/10/2010 |
| Q: I have an inventory of spare parts on a spreadsheet and would like to automatically colorcode any ... A: No problem- go to format, conditional formatting, then change to FORMULA and enter ... | |
| Excel spreadsheet | 2/10/2010 |
| Q: I have an inventory of spare parts on a spreadsheet and would like to automatically colorcode any ... A: what defines a new entry- simply one on a line you haven't yet used? If so, conditional formatting ... | |
| Help Needed Creating Formula | 2/9/2010 |
| Q: I have a problem where there is an amount of say $200,000.00 in cell A1, I want to have the ... A: As entering a value into a cell would destroy a formula, you cannot do it in a simple way - but that ... | |
| Count numbers within date range | 2/8/2010 |
| Q: I need help with a formula. Here is what the spreadsheet looks like (column B contains only the ... A: Before working out a formula, can I check - will the data be ONLY current year, or do we have to ... | |
| Excel- Incident Calculation | 2/8/2010 |
| Q: I want to calculate how many hours the incident took to resolve from Date/Time logged and Date/Time ... A: This is a fairly common request - but not necessarily as simple (!) as laid out - can calls be ... | |
| fraction summation in excel | 2/7/2010 |
| Q: Hay! My previous questions is as "Sir, As when we add the time in excel for example A1 = ... A: how is the data entered here - can we assume a standard length for the whole number parts? If so, ... | |
| delete macro | 2/7/2010 |
| Q: I have an application that I wish to delete after 5 uses. It is opened by a macro in another file. ... A: When I checked this for you it did work - except that I got a path not found error, but I expected ... | |
| summation of base 9 and 20 | 2/7/2010 |
| Q: Hay! I m zulfiqar and need help in excel Sir, As when we add the time in excel for example A1 = ... A: =left(Yourcell,2) would give you the 2 characters from the left =right(Yourcell,2) would give you ... | |
| summation of base 9 and 20 | 2/6/2010 |
| Q: Hay! I m zulfiqar and need help in excel Sir, As when we add the time in excel for example A1 = ... A: Using time as an example doesn't help, as excel stores time as a decimal fraction - it just gets ... | |
| delete macro | 2/6/2010 |
| Q: I have an application that I wish to delete after 5 uses. It is opened by a macro in another file. ... A: B1 is never changing - the change event is firing on you updating A1 - if you modify that then it ... | |
| cell reference | 2/5/2010 |
| Q: Range("A1").Select Selection.Copy Cells.Find(What:="1370", After:=ActiveCell, ... A: you macro seems to just be a find macro, which I'm not sure is what you want - if it is, I'd just ... | |
| Hyperlinks | 2/5/2010 |
| Q: Is there such a thing as a function that will give you the hyperlink address of a cell? For ... A: I'm not aware of a function already in existence - but you can create your own - press Alt F11, then ... | |
| Moving Information | 2/4/2010 |
| Q: I am using Excel 2003.I have two sheets, Sheet 1 is Orders and Sheet 2 is Stock. The orders have to ... A: At its simplest, this would be done with a sumif function which would sum the volume of orders and ... | |
| Excel for Mac | 2/4/2010 |
| Q: I am using Excel on my Mac. I want to highlight a few rows with the intention of hiding them. ... A: I'm not a mac expert, and I'm not sure exactly what you are doing but (in windows) to select more ... | |
| new spreadsheet takes a long time to open and often gets stuck | 2/4/2010 |
| Q: My computer is Windows XP. For the last few years I have kept our holiday bookings records on excel, ... A: A shortcut is a shortcut, so deleting this shouldn't delete the file. If you have a LOT of ... | |
| 2/3/2010 | |
| Q: I have an application that is used by about 50 people, I do not want these people to share the ... A: Well, you can use VB code to do this BUT it will only work if they enable the code, so it's easy to ... | |
| Excel 2007 | 2/3/2010 |
| Q: How do I figure the number of hours worked from 8:30 AM - 3:00 PM, Minice 30 mins for lunch. I ... A: I like your formula, but it's over complicated =c5-b5-(30/60/24) and have the cell formatted as ... | |
| Excel reminder vba code | 2/3/2010 |
| Q: I hope you can help me with using VBA code to remind a field in excel. For example: ColumnA: ... A: your x code needs to be =cl.offset(1,3).value=”X” which I would put either just after or just ... | |
| Find and delete preceding row | 2/3/2010 |
| Q: I would like to find a value and if the cell directly below it has the same value I would like to ... A: Sub Deleter() Set currentCell = Worksheets("Sheet1").Range("A1") Do While Not IsEmpty(currentCell) ... | |
| Lookup Multiple Criteria | 2/3/2010 |
| Q: Is it possible to use a formula to look up two criteria to return a text value? In Sheet 1 Cell R1 ... A: Two ways of doing this spring to mind – the first and easiest would be to amend the structure of the ... | |
| Photo Links in Excel 2003 | 2/2/2010 |
| Q: I am able to manually create a hyperlink one at a time, however, with over 1,000 students, I would ... A: You could use the HYPERLINK worksheet function if you simply need to create a series of hyperlinks – ... | |
| Tally to Excel | 2/2/2010 |
| Q: I request your help in getting Tally data to excel. My current tally version is 7.2 but I am unaware ... A: I know nothing about Tally - that would seem to be the key here - I don't know what format Tally can ... | |
| Screen flash with macros | 1/27/2010 |
| Q: I have a workbook of around 80 sheets and everything works perfect until I protect all sheets. Once ... A: There is a lot of SCROLLING in this macro, none of which is necessary. Additionally, it is ... | |
| Screen flash with macros | 1/27/2010 |
| Q: I have a workbook of around 80 sheets and everything works perfect until I protect all sheets. Once ... A: Application.screenupdating should handle it, but possibly it is being set too late? Also, what are ... | |
| Colourcoding dropdown lists and conditional formatting | 1/26/2010 |
| Q: I have created a dropdown list using M, K, 1, 2, & 3. I need to assign a colour to each letter. i ... A: you actually have FOUR colours - the final colour being the base colour of the cell if all tests ... | |
| Formula and Macro possibly | 1/26/2010 |
| Q: Multi-part question. In my spreadsheet for current inventory, i have it set as =if(x-y<0,x+y,x-y) ... A: My apologies – you will need to use the events on the worksheet- I think I would probably do ... | |
| Please help with nesting functions with more than 7 IFs. | 1/26/2010 |
| Q: I am trying to calculate a value on Sheet1 which pulls data from cells on Sheet1 and compares them ... A: It isn’t possible to nest more than seven levels of bracket, though that’s not the same as more than ... | |
| Summary Sheet | 1/25/2010 |
| Q: I have two worksheets,as follows Sheet 1: Date Profit -------------------- 01/01/2010 100.00 ... A: >> I’d use a pivot table on the first page to group the data together (you didn’t specify version of ... | |
| Matching Data | 1/25/2010 |
| Q: I would be very grateful if you could shine a light on the following for me_ I have two worksheets ... A: You can physically bring the data in by using macros, but I would suggest that the easiest solution ... | |
| Percentage formular in excel | 1/24/2010 |
| Q: I want to reduce a total sum by a percentage. eg if the total was 4.50 in cell B2 and i wanted to ... A: an extra * seemed to be typed sorry - it should be =round(B2 * 0.971,2) ALSO if you can see the ... | |
| Percentage formular in excel | 1/23/2010 |
| Q: I want to reduce a total sum by a percentage. eg if the total was 4.50 in cell B2 and i wanted to ... A: Ok, basic maths kicks in here - a reduction of 2.9 percent is the same as multiplying by 97.1% - so ... | |
| Excel Problem | 1/22/2010 |
| Q: Respected Sir, I was working with an Excel Spreadsheet in my office. Suddenly my computer shut down ... A: I'd first try to use explorer to find the file and check that it looks ok from there - then try to ... | |
| SUMIF | 1/22/2010 |
| Q: This is a simple one but i can't for he life of me work out how to do it. I have a drop down box ... A: I can think of a few ways - one would be to name c11, c12, and c13 as Director, Manager and Admin ... | |
| Excel | 1/21/2010 |
| Q: We are using " " this symbol in excel formulaes. What is the use of this symbol and meaning. And the ... A: countif should work fine with a full column - can you send me a sample file to the email I gave in ... | |
| Hyperlink to multiple rows | 1/21/2010 |
| Q: I have a workbook with 2 sheets. 1st sheet has about 50 rows with names and second sheet has the ... A: I assume that each cell should link to the equivalent cell on the other sheet - you could create a ... | |
| I sent you mail | 1/20/2010 |
| Q: Scuse me i sent you a mail. Did you get him? Best regards Cristi A: I did - I haven't had a chance to reply yet, BUT I think it is going to be tricky to do what you ... | |
| Excel | 1/20/2010 |
| Q: We are using " " this symbol in excel formulaes. What is the use of this symbol and meaning. And the ... A: Is the data actually entered as text, if so you would be looking for a countif function ... | |
| Question about counters | 1/20/2010 |
| Q: I would like to set up a counter in Excel 2007, which will count up the number of unique values ... A: I’m not 100% sure if it is possible – what are you defining as unique, and does a history of this ... | |
| Row Update | 1/20/2010 |
| Q: I need to keep track of the latest data on a shared spreadsheet, so I'm looking for a formula or vba ... A: I never recommend shared workbooks as they will get corrupted, and I'm not sure that you will be ... | |
| Open and display directory spreadsheets for user to select and edit | 1/19/2010 |
| Q: I want to be able to display all excel spreadsheets in a particular directory for the user so they ... A: Perhaps I'm missing something here, but it sounds as though you simply need to set the Current ... | |
| copy paste cell value under certain conditions | 1/18/2010 |
| Q: The subject may be a bit misleading as there may be several ways of approaching this problem though ... A: Sorry for the delay - it seems to have been a bit manic round here sounds SOMETHNG like if ... | |
| excel time calculation with 2nd rate pay | 1/18/2010 |
| Q: I have a time card set up with a clock in clock out and two rates of pay. From 8pm to 6am is premium ... A: It’s a simple enough concept, though quite tricky to solve – can I ask for some more information, ... | |
| Excel | 1/15/2010 |
| Q: We are using " " this symbol in excel formulaes. What is the use of this symbol and meaning. And the ... A: Without seeing the specific sheet it's hard to be sure, but the REF error would indicate that one ... | |
| I need to compare two lists of names | 1/15/2010 |
| Q: I really hope you could help me with this. I have two lists of names that I need to compare. ... A: I presume that you are looking for an exact match here =COUNTIF($B$2:$B$2100,A2) will count a2 ... | |
| Excel Timing Format | 1/15/2010 |
| Q: I'd like to be able to calculate time differences going out four decimal places (example 37.7863 ... A: It’s perfectly possible, it just won’t get displayed – excel stores time as a decimal fraction, so ... | |
| copy paste cell value under certain conditions | 1/14/2010 |
| Q: The subject may be a bit misleading as there may be several ways of approaching this problem though ... A: You can certainly do this, but there is no need to use the conditional format - I'm not 100% sure ... | |
| Min/Max date list | 1/14/2010 |
| Q: Hope you can help me with my problem. First of all, I've got a list data that contains dates say in ... A: The first part could be done simply by sorting the data - data, sort. The second bit I would do ... | |
| Opening Files with Macro Security | 1/13/2010 |
| Q: When I open a particular file, it brings up the Macro Security box, requesting to disable or enable ... A: Slightly annoying one - IF the sheet has EVER had macros, this warning comes up - the only fix I ... | |
| Concatenate three numbers resulting from formula to 3 decimal places | 1/13/2010 |
| Q: I am trying to concatenate 3 values a,b & c to import as script into another application. I need the ... A: Use the TEXT worksheet function as well to fix this - eg =text(a,"0.000") & text(b,"0.000") & ... | |
| How to use LOOKUP function with several ranges | 1/12/2010 |
| Q: On this case I need a formula to lookup a given value in an array according to several given ... A: I think it would probably need a match function unless there are named ranges - can you send me a ... | |
| Excel " " | 1/12/2010 |
| Q: We are using " " this symbol in excel formulaes. What is the use of this symbol and meaning. And the ... A: anything within a set of double quotes is a string, so "" indicates an empty string, i.e the cell ... | |
| Excel formula linking and naming convention alterating | 1/11/2010 |
| Q: My name is Meghan and the company I work for relies all of our data entry and financial reports in ... A: I'm not sure how many sheets are being referenced, but an EASY fix (or easy ish at any rate) would ... | |
| Isblank function | 1/10/2010 |
| Q: Aidan, I am trying to create a worksheet to show if a project has been received or not. If the ... A: You asked about the ISBLANK function and gave me an example of the formula you were trying to use - ... | |
| Auto Input into a Budget | 1/7/2010 |
| Q: Greetings, I run a budget in excel but would like to find a way for Excel to automatically input my ... A: I agree that this does sound like it needs a macro – I’m happy to help, though it might help to see ... | |
| Check Box Filter | 1/7/2010 |
| Q: Happy New Year! I am trying to make a sort of dashboard using check boxes as a means to filter. I ... A: I’m having slight problems getting this clear in my head but it sounds as though it’s a KIND of ... | |
| How to get range of dynamic column? | 1/4/2010 |
| Q: This is a follow up to a question I asked here last week. I need to get the lowest value of odd ... A: I assume that a zero value wouldn’t be allowed anyway so in that situation ... | |
| Range from cell value | 1/3/2010 |
| Q: Range("K3").Select Selection.AutoFill Destination:=Range("K3:S3"), Type:=xlFillDefault ... A: Sorry for the delay this time - it was my daughters party at the weekend (age 5) and I had my mother ... | |
| Range from cell value | 1/2/2010 |
| Q: Range("K3").Select Selection.AutoFill Destination:=Range("K3:S3"), Type:=xlFillDefault ... A: to give you the answer to the question you asked range(range("g3").value) will give you what you ... | |
| embedding JPGs | 1/2/2010 |
| Q: i build a catalog for coins collection. each row represents a coin (record). in two cells (fields) i ... A: OK, I've created a sample file for you which does the sort of thing you want ... | |
| hi again !plz help!!:( | 1/1/2010 |
| Q: I used combobox active x control cause I can be capable to write a data and search it in that ... A: I would still go with a listfill range – if the user is adding data, presumably you would want this ... | |
| How to convert back read only files | 1/1/2010 |
| Q: Added a sata drive from 2000 pc to an xp. 2 HDs. Was able to open and work on excel spreadsheet for ... A: Sounds more like an OS problem than an excel one – basically however, if you have any read-only ... | |
| from spreadsheet to web | 12/31/2009 |
| Q: I have a question related to Excel and was wondering if you could help. In my work, I every morning ... A: It is PROBABLY possible, though it's outside of my knowledge area ... | |
| subtotal even after clearing filter | 12/31/2009 |
| Q: I want to sum the values of a filtered data. I did it by subtotal function in another sheet. But ... A: You could select your data as a block after filtering and look at the bottom right of excel where it ... | |
| How to get second lowest value of odd rows | 12/31/2009 |
| Q: I'm trying to get the second lowest value of odd rows only. I'm trying this: ... A: Your formula looks almost right, except that it should read ... | |
| sort combo | 12/30/2009 |
| Q: the only problem is when I enter or add a data in combobox It is not sorted alphabetically and it ... A: The combobox has no reason to know that you would want the data sorted alphabetically - for example, ... | |
| "What's the most fun and exciting way YOU HAVE EXPERIENCED learning Microsoft Excel?" | 12/30/2009 |
| Q: I get bored with books. I need a DVD or an online course, but some of them aren't professional or ... A: Well, I learn by doing – so my most interesting way is answering questions on this site – that ... | |
| Count names if other data is within range | 12/29/2009 |
| Q: I've researched to no avail and need your help. I need to count the number of unduplicated names of ... A: It’s basically (as far as I can tell) a multiple condition countif – IF you can create a helper ... | |
| Mean line | 12/29/2009 |
| Q: My problem is that I have two profiles Sound_Velocity X Depth and want to take the mean line of ... A: I'm not 100% sure I follow what you need to do - you can use a pivot table to summarise the data and ... | |
| Need more columns | 12/28/2009 |
| Q: I need more columns to work with. I have read that Excel 2007 gives you over a 1000 columns but I ... A: No nothing special, though you would have to make sure any file you are working on is saved as an ... | |
| Isblank function | 12/28/2009 |
| Q: Aidan, I am trying to create a worksheet to show if a project has been received or not. If the ... A: The formula is working fine - it's the logic that is wrong - to translate If B2 is blank, then ... | |
| embedding JPGs | 12/27/2009 |
| Q: i build a catalog for coins collection. each row represents a coin (record). in two cells (fields) i ... A: The best answer to this that I've found (it's a question I've been asked before) is ... | |
| Pivot Table | 12/26/2009 |
| Q: In pivot Table when i give the height to all Row 25.20, when i refresh the sheet its automatically ... A: I haven't actually experienced this - although some settings will be reset, row height shouldn't be ... | |
| Controlling certain keys | 12/25/2009 |
| Q: I have a friend who once got an answer to a similar question on your site, so I thought I'd write ... A: I've not had cause to do this and although it SHOULD be easy it took me a lot of searching - ... | |
| Excel Function | 12/24/2009 |
| Q: This is what I want to do. In the most basic sense, I would have two columns of data. For example, ... A: The EASIEST way to do this is with a pivot table - you didn't specify version of excel, so getting ... | |
| Excel Problems | 12/23/2009 |
| Q: I have finally figured out the formula I was looking for, but now I am trying to reduce it. having ... A: does the cell actually have to be blank or just LOOK blank - the second is easier as you can use ... | |
| MS Excell formula for comparing columns | 12/23/2009 |
| Q: I want to know how to compare two columns of numbers, Column A and Column B. I want to know which ... A: The EASIEST way to do it would be with a countif function, then filtering out the list and copying ... | |
| finding cross sectional area from a plotted graph | 12/22/2009 |
| Q: I'm trying to use excel to calculate the cross sectional area of a river transect for me.....i've ... A: I'm sure there would be a way, but I'm not up on the math necessary to do this! My google style ... | |
| xls to csv | 12/22/2009 |
| Q: sir, i am doing research on data mining.the tool needs csv format and my data is in excel with ... A: In theory each column becomes a field - but POSSIBLY you have merged columns, or commas already in ... | |
| Employee Wage Tracking | 12/21/2009 |
| Q: I have a workbook that has been used for some years to track daily hours of our employees. I am ... A: It might help to see an example of the sheet if that is possible – my email is ... | |
| COUNTIF Formulas | 12/20/2009 |
| Q: I'm experiencing a problem using a countif formular. I'm trying to count data from a spreadsheet on ... A: links to external workbooks SHOULD refresh, but you may well be prompted to update the data - and ... | |
| USER INFO | 12/18/2009 |
| Q: I want to print the user info in hard copy/excel filed. I am using office 2003 with net work. I ... A: You can certainly use VBA to pick up the current user from a network logon - is that what you were ... | |
| MAX and IF or something else? | 12/18/2009 |
| Q: could you please help me with my task: In column A I have five different machines In column B I have ... A: The EASIEST way of doing what you want is via a pivot table - you didn't specify version of excel, ... | |
| Extract Rows that start with specific data. | 12/17/2009 |
| Q: I have an Excel Spreadsheet with about 55,000 rows. I only need the rows that start with a :21, :25, ... A: I'd use a blank column to put a formula in and then filter on that =or(left(a1,3)=":21", ... | |
| Pick List and Macros | 12/17/2009 |
| Q: I was just wondering if a selection from a pick list can activate a macro? I am do not understand ... A: It’s not 100% clear what you are trying to do here, as potentially an IF statement could handle the ... | |
| External program and command from excel Macro | 12/16/2009 |
| Q: Firstly, thanks a lot in advance for reading my question. Appreciate it big time. Here is theIn ... A: the problem with shell is that it runs a program, but then has no control over what goes on - you ... | |
| Combining/Consolidating Fields | 12/16/2009 |
| Q: I have created a large file of contact information for people. It has each occupant of the house ... A: would all the people in the household have the same surname, and if they didn't would you want to ... | |
| formula for filtering data | 12/16/2009 |
| Q: is there any formula to make a filtered data out of a table of data ? I have entered large volume of ... A: My example was on one page just for ease of reference - the part that returns the row number is ... | |
| excel formula help | 12/16/2009 |
| Q: i am reasonably new at formulas and just need help. so basically i need a funcion that will count ... A: You didn't specify version, which in this instance does actually matter- excel 2007 introduces ... | |
| Combine if, or and vlookup functions in Excel | 12/15/2009 |
| Q: If cell B5 is blank or contains an error value i want the result to show blank, else should fetch me ... A: There is nothing logically wrong, but excel isn't happy mixing error values and other values - so ... | |
| copy paste | 12/14/2009 |
| Q: I have some data in column D5,D6,D7,D8, D9, D10, D11, D12 upto D749 and another data in Col. E6, E8, ... A: with difficulty - as there seems to logical progression it's hard to give a simple method - IF the ... | |
| zero in cell is detected as empty | 12/14/2009 |
| Q: I am trying to detect when a cell is empty and then place a No or Yes on another cell, but if I ... A: <> is not equal to As to question 2, it depends a bit on how many entries you have - possibly a ... | |
| Length Optimisation | 12/13/2009 |
| Q: I've done the advanced excel course and trying to self teach vba which I'm struggling with at times. ... A: Sorry for the delay in replying - it has been rather busy locally. I'm not 100% sure how one would ... | |
| Copy/Paste Cells in Excel based on Color | 12/12/2009 |
| Q: I am working on a project for school and have been trying to write an Excel function to help me ... A: well done for finding one of my answers - that's something I sometimes have problems doing (although ... | |
| Creating a Cumulative Sum of DDB Calculations | 12/12/2009 |
| Q: I am creating a twelve month cash flow projection for a subscription based service. I have set up ... A: I think I would do this by having TWO formulas - January stays as you have written it, but February ... | |
| ecel vlookup and if function | 12/12/2009 |
| Q: Please help me learn how to do this. What do I open - I do not even see vlookup or if function on ... A: very few formulas are on the toolbar - though they are in the function reference area - I don't know ... | |
| Macro to duplicate worksheets and modify function refrences | 12/11/2009 |
| Q: I am creating a workbook to track vehicle maintenance and associated records. The basic workbook is ... A: I have a slight concern that this MIGHT need to be multi-user, at which point it will probably ... | |
| VLOOKUP on Text | 12/11/2009 |
| Q: Worksheet 1 Line # Col A Col B 1 FISHER green 2 HARRIS yellow 3 JONES blue 4 BLOGGS red ... A: =MIN(IF(ISERROR(SEARCH(D1,A1:A4)),999999,ROW(A1:A4))) would do it - a return of 999999 would ... | |
| deleting empty cells or with zero values | 12/11/2009 |
| Q: Sir, i have read here in all experts mostly about deleting rows that contains cells with zero ... A: if so the normal delete code would work Sub Deleter() Set currentCell = ... | |
| Delimiting an alt-enter character | 12/10/2009 |
| Q: I have some cells that have a lot of data but the data in the cell is seperated with alt-enter ... A: Easiest fix I can think of is to use a macro Sub helper() For Each cell In Range("a1:a10") ... | |
| excel 2003 | 12/10/2009 |
| Q: .amazing site. I'm not good at formaulas, and have very minimal knowledge on excel, I have only ... A: I did effectively answer this before by directing you to a previous answer where I gave a word macro ... | |
| excel 2003 | 12/10/2009 |
| Q: .amazing site. I'm not good at formaulas, and have very minimal knowledge on excel, I have only ... A: Did you want all the numbers, or just a way of working out the number of possible combinations? For ... | |
| deleting empty cells or with zero values | 12/10/2009 |
| Q: Sir, i have read here in all experts mostly about deleting rows that contains cells with zero ... A: The code would be easy enough – you say to delete the CELLS so that implies that data would have to ... | |
| Formulas | 12/9/2009 |
| Q: I have a few columns on worksheet 1 and names in 1 of 2 different columns (A and B). what i would ... A: It is possible – I think I need to clarify – you are checking for something being in column a or b ... | |
| Vlookup Excel | 12/9/2009 |
| Q: I need vlookup to return multiple values into one cell and possibly place a coma between the ... A: It's possible, but not with vlookup or any native functions that I know of - however, as this is ... | |
| VLOOKUP on Text | 12/9/2009 |
| Q: Worksheet 1 Line # Col A Col B 1 FISHER green 2 HARRIS yellow 3 JONES blue 4 BLOGGS red ... A: lookup on text will work fine, but in this instance you want to look up something that isn’t ... | |
| Identify the last row of a range | 12/9/2009 |
| Q: I have developed code that merges two workbooks. After the workbooks are merged, the result is a ... A: I'm not sure what process you are doing to merge the data, but I guess that doesn't matter - as you ... | |
| Converting percentages to words | 12/8/2009 |
| Q: I have found a VB script that converts a number (20) to words (Twenty) but I need to convert a ... A: This is my vbscript to convert text to words Public Function ft(n) n = Int(n) Dim convert$ convert$ ... | |
| Converting a percentage to words | 12/8/2009 |
| Q: I have found a VB script that converts a number (20) to words (Twenty) but I need to convert a ... A: This is my vbscript to convert text to words Public Function ft(n) n = Int(n) Dim convert$ convert$ ... | |
| Copy selected cells to a new tab based on cell criteria | 12/8/2009 |
| Q: I hope you can help as I couldnt find a previously answered question with similar requirements. I ... A: Not clear if this needs to do all rows on the sheet, but basically it would be something like for n ... | |
| Column Look Up, Paste value in same row. | 12/8/2009 |
| Q: Looking for a bit of assistance in Excel with VB macro. I have 2 spreadsheets. One has an order ... A: Sorry for the delay in replying – it’s been a busy few days here! Try something like Dim wkbk as ... | |
| Finding partial match data in a list | 12/7/2009 |
| Q: I hope you can help. I think I have done this before, but can't remember how to do it for the life ... A: You say ANY cell in column B, so this sounds slightly tricky - ISERROR(SEARCH(B1,A1)) will do it for ... | |
| Transposing | 12/7/2009 |
| Q: I am trying to transpose multiple rows into three columns as in the example below. I know how to ... A: Sub transposer() Dim counter Dim RCounter Dim OCounter counter = 1 OCounter = 1 While ... | |
| Column Look Up, Paste value in same row. | 12/7/2009 |
| Q: Looking for a bit of assistance in Excel with VB macro. I have 2 spreadsheets. One has an order ... A: To put data in e all the time then simply use 5 for the column instead of counter (or set counter to ... | |
| Problem using IF(ISNA ) | 12/6/2009 |
| Q: Column F Column G 1.666666667 0 1.333333333 2 0.666666667 0 0.666666667 0 0.333333333 0 2 0 ... A: the example data doesn't come across very clearly in typed form - but I woder if #NA is a typed ... | |
| Counting changes in randomly spaced data | 12/5/2009 |
| Q: I have a column of data that alternates from -1 to 0 to 1 and back in a random pattern. (The data ... A: The only obvious way I can think to do this is with VBA - is that an acceptable solution - if so ... | |
| Inventory levels | 12/4/2009 |
| Q: Sir, I am trying to keep an inventory level of stock merchandise. Currently I have a workbook with ... A: IT SOUNDS as though you basically need a SUMIF or multiple condition SUMIF function – which you can ... | |
| Doubt in Excel | 12/4/2009 |
| Q: Question :How can I get the sum of Sales for a particular Territory for a product in a quarter ie ... A: without knowing the structure of the data it is hard to be definite, but I would HOPE that you would ... | |
| Creating a Dynamic Chart linked to 69 sheeets in a single sheet | 12/4/2009 |
| Q: Kindly help me out.I'm trying to create a Dynamic Chart linked to 69 sheets on a single summary ... A: IF the 69 sheets are in the same format, then using a formula to pull the data into the summary is ... | |
| Cell Color Changes based on condition for more than 3 colors | 12/3/2009 |
| Q: I have been trying to find a way to change the background color of a cell based on a condition, the ... A: You can actually have 4 levels of condition for conditional formatting because the base format of ... | |
| importing field from excel into word | 12/3/2009 |
| Q: I am trying to import spreadsheet data from Excel 2007 into word 2007 using mail merge. It works ok ... A: Although this seems odd I’m not entirely surprised – we have similar issues with dates which will ... | |
| excel Finding a value within a range then selecting the cell? | 12/2/2009 |
| Q: Finding a value within a range and selecting the cell? Via a macro, I want to copy a named range in ... A: various ways – you could use a counter and read across the rows until you came to a blank row, but ... | |
| VLOOKUP FROM SHEET TO SHEET | 12/2/2009 |
| Q: I have XP 2002. I exported data to Excel and have 5 worksheets in 1 file....original, working copy, ... A: The only thing I can spot about the formula is that it's a relative reference for the look up table ... | |
| HIDE COLUMN IN EXCEL | 12/2/2009 |
| Q: I MAINTAIN IN MY OFFICE ONE EXECEL FILE WHICH CONTAINS VERY CRUCIAL INFORMATION. THE SAME FILE IS ... A: Using a macro is not a very safe way of securing data as all a user has to do to bypass this is to ... | |
| Column Look Up, Paste value in same row. | 12/1/2009 |
| Q: Looking for a bit of assistance in Excel with VB macro. I have 2 spreadsheets. One has an order ... A: something like this I think will do what you want Dim LookForMe Dim cell Dim counter As Long ... | |
| how to enter a formula | 12/1/2009 |
| Q: How or what formula can I enter in order to get a zero amount automatically and the difference ... A: The explanation isn't clear but it sounds as though you want to type an actual value in I4 and then ... | |
| VBA code, accepting variable ‘replace’ value | 12/1/2009 |
| Q: Assistance VBA code, accepting variable ‘replace’ value. I am quite new to VBA programming, and am ... A: I wouldn't use search and replace in a macro myself for each cell in range("Cognos_link_newCW") if ... | |
| excel - pivot table | 12/1/2009 |
| Q: i need help figuring out how to do excel pivot tables - i cant seem to get the right information in ... A: It sounds a little like homework,but it should be easy enough - drop customer on the left and profit ... | |
| find dublicate rows | 12/1/2009 |
| Q: i'd like a better and faster solution in my question only if that is possible. =a4=a$3 is not what ... A: Doing this with a macro is fairly easy as it simply needs to loop through all the cells and compare ... | |
| Macro Error Handling | 12/1/2009 |
| Q: I have a Submit macro (code listed below) that I inherited in which I would like to add Error ... A: Not sure how many cells are in the defined name – IF it’s a single cell that contains the word ... | |
| Returning Start and End Dates | 12/1/2009 |
| Q: I am hoping to build a dynamic solution to this issue. I have a series of events (of differing ... A: Took me a while to come up with a solution, so sorry for the delay – see the file I’ve uploaded at ... | |
| Send Word UserForm data to Excel | 12/1/2009 |
| Q: I am very new at using Visual Basic. I created a simple Microsoft Word Visual Basic (6) UserForm, ... A: Your command button 1 click event should either call the GetExcel sub (simply type GetExcel as a ... | |
| repeat find word with red colour | 12/1/2009 |
| Q: I always appreciate your kind help. Hope, You can offer me some kind of solution for the problem I ... A: You haven’t specified WHAT is going to be true – try something like do While Selection.Find.Found = ... | |
| Can't Format Cells to Dates | 11/30/2009 |
| Q: I'm having a rather frustrating problem. I exported a client list from Drake 2008 Tax software to a ... A: As I mentioned, excel treats dates as numbers, so formatting a date as a number would display the ... | |
| Can't Format Cells to Dates | 11/30/2009 |
| Q: I'm having a rather frustrating problem. I exported a client list from Drake 2008 Tax software to a ... A: if the cell contains mmddyyyy then it won't directly format as a date - because excel treats days as ... | |
| Excel 2007 Conditional Formatting | 11/30/2009 |
| Q: I have a problem I hope you can answer. I have a worksheet which shows a calendar for a particular ... A: It doesn't come over very well in text - is it possible to see a sample file ... | |
| Using month for calculation | 11/28/2009 |
| Q: I am trying to construct a spreadsheet which will look at a month and make a calculation ... A: Not quite sure where the word ANSWER came from but =a2*(13-MONTH(DATEVALUE("01 " & A1))) as a ... | |
| Lotus Notes mails & Excel Reports using macros | 11/28/2009 |
| Q: Sir, My HOD gets 50 mails in a day on various projects. I have to compile the information from each ... A: I would use lotus notes to export the data to a text file and then import that - I'm afraid I'm not ... | |
| My difficulties with the last occurrance | 11/28/2009 |
| Q: I am trying to find the last occurance of the number 01 between B1 and F2000. I have an Excel ... A: The formula is fine with one exception, which is that 01 is actually 1,but that wouldn't stop the ... | |
| Date time in one cell minus time?? | 11/26/2009 |
| Q: I have one query regarding data and time working in excel. I m trying it from many days. If date and ... A: I must be missing something here because the answer SHOULD be =a1-a2 with the cell formatted as ... | |
| excel 2007 | 11/26/2009 |
| Q: I have a "total codes complete" cell in one sheet which I need to be able to enter a number from ... A: The explanation isn’t clear, but it SOUNDS as though you are looking for a value which will update ... | |
| Macro | 11/25/2009 |
| Q: I have a feeling that this might be a 'question too far' but let's see! I wonder if it is possible ... A: Sub sorter() Set currentcell = Range("A1") Do While Not IsEmpty(currentcell) Set nextCell = ... | |
| SUMIF(s) under inconsistent conditions | 11/25/2009 |
| Q: How can I work with SUMIF(s) under complicated, inconsistent conditions? Or should I use a ... A: Not clear if the Not OK is actually an entered value – if so, then I would use that with a match ... | |
| excel hours worked in a day | 11/25/2009 |
| Q: I want to create the following: column 1 hour began, column 2 hour left, column 3 diff between 1 and ... A: Allexperts is a volunteer service, so it’s free – ideally you will enter the times AS times (eg 9:00 ... | |
| Excel | 11/24/2009 |
| Q: I'm trying to create a copy down macro but everything I try does not work. I know it is simple, but ... A: Something simple like this Sub fillme() Dim counter As Long For counter = 1 To ... | |
| finding duplicates in Excel | 11/24/2009 |
| Q: I am usually great at Excel but this one has me stuck. I have names listed in column A. In column B, ... A: To highlight the cells, use conditional formatting (you didn't say which version of excel so I'll be ... | |
| Excel 2003, complex formulas | 11/24/2009 |
| Q: I need to creat a complex formula, with conditions....I have a total of 12 columns (possible values ... A: Sounds fairly straightforward ... | |
| Help required with Excel Macro | 11/24/2009 |
| Q: I require assistance from you in a college project where I need an excel macro which would save an ... A: easiest way would be with the Filecopy command Dim SourceFile, DestinationFile SourceFile = ... | |
| How to Concatenate a Text String from multiple cells containing a search value | 11/23/2009 |
| Q: On 11/20/09 you answered a question that partially helps solve what I'm attempting - I am aware of ... A: I would do this using a User Defined Function in VBA -=- ... | |
| Multiple Count Formulas (Excel 2003) | 11/23/2009 |
| Q: I am having problems trying to count multiple variables using Excel 2003. I have attached a picture ... A: You can do multiple condition countif or sumif calculations by a clever use of the sumproduct ... | |
| Excel Spreadsheet | 11/22/2009 |
| Q: I am creating a mileage chart in Excel with same cities horizontal and vertical - is there a way to ... A: Hopefully that's a different question - the original question seemed to relate to how to put the ... | |
| Excel Spreadsheet | 11/22/2009 |
| Q: I am creating a mileage chart in Excel with same cities horizontal and vertical - is there a way to ... A: if you are doing this as a one off, then you can copy the results and use edit, paste special to ... | |
| Check boxes in forms | 11/22/2009 |
| Q: I created a form in EXCEL 2003 on an XP machine using check boxes which, when checked will do ... A: Not sure what method you have used to create checkboxes as they exist in the control and forms ... | |
| macros that automaticly calculating values through a workbook system based on values entered into a cell | 11/20/2009 |
| Q: I have developed a workbook in excel that calculates production costs for circuit boards based on ... A: The macro sounds fairly easy- it's basically going to be a for...next loop which puts in the values ... | |
| Creating column macro (criteria based) | 11/20/2009 |
| Q: appreciate your time. MyIs there a formula/macro that can create column D below? Basically, ... A: Not a problem - you can use a COUNTIF function to determine the first row, and a sumif to give you ... | |
| Vlookup based on several drop downs | 11/20/2009 |
| Q: I am trying to show an answer based on several selections in drop downs. Using this: ... A: Not sure I have enough to go on in that at the MOMENT it doesn't sound like a lookup, simply an IF ... | |
| Pasting chart into Word | 11/20/2009 |
| Q: OK so I am at a complete loss ! I have used Excel and Word for years, I basically write scientific ... A: It sounds as though it's pasting but not coming into view- try the view ribbon and change to print ... | |
| Excel 2007 - auto-updating number | 11/20/2009 |
| Q: I have a spreadsheet that keeps track of employee hours. What I'd like to add is a cell that takes ... A: Excellent- your formula has a SLIGHT problem in that presumably the holiday continues in January, ... | |
| To Lookup and return values | 11/20/2009 |
| Q: I have a sheet which has data. In column a there are book names and in column B there shelf numbers. ... A: multiple lines is always going to be a problem as no function can program the lines, but you could ... | |
| To Concatenate and display in multiple lines | 11/20/2009 |
| Q: In my sheet cell A1 has candidate's Name and cell B1 has Father's Name. I have concatenated them in ... A: It's going to be difficult as although you can do it manually, there isn't a direct way of doing it ... | |
| Creating List of Document Hyperlinks | 11/20/2009 |
| Q: I am trying to create a list of file names in Excel corresponding to the documents in a folder - we ... A: The basic bit of the process boiled down is:- Set fs = Application.FileSearch Dim newbook, ... | |
| Multi email sender | 11/19/2009 |
| Q: How can I send personalized email to many (ex 40) customer with same subject and a bit different ... A: This is a standard mail merge - you didn't specify the version of word, but basically set your ... | |
| Excel LOOKUP? | 11/19/2009 |
| Q: I need a formula that would search the value of A2 in a array C:E and return the group "Joe" is in. ... A: I think you would need to do this with an array formula, which is entered by pressing ctrl shift ... | |
| ranking and pivot tables | 11/19/2009 |
| Q: Is there a function that allows you to work out the ranking of a particular column (ie. fees ... A: A good question- I THINK from research that this might be included in Excel 2010, but for now try ... | |
| toggle button | 11/19/2009 |
| Q: I am using Microsoft excel 2003 and have just created a questionnaire using if statements. ... A: Not clear what the purpose of this is, but the easiest way would be with data validation to give you ... | |
| excel solver | 11/18/2009 |
| Q: Subject: excel solverI need help figuring out how i am going to do this - please help me - not sure ... A: It was to the first time you asked the question - I'm in the UK and work during the day with no ... | |
| Conditional Save | 11/18/2009 |
| Q: Hey Aidan, Im trying to config. this macro to meet my condition but cant seem to get it to work. The ... A: but it is appreciated when people do. At no point in your macro have you set a default condition – ... | |
| userforms-find and edit info | 11/18/2009 |
| Q: My office works with specific job folders, each one with a unique job number. I need to keep a ... A: I’m slightly concerned that this is being done in excel as it SOUNDS as though it may need to be ... | |
| Spreadsheet layout design | 11/18/2009 |
| Q: I am seeking resources which will assist me to understand the elements of efficiently designed ... A: http://www.google.co.uk/search?hl=en&q=spreadsheet+design+best+practices&meta=&aq=2&oq=spreadsheet+d ... | |
| Icon Sets | 11/18/2009 |
| Q: I have added Icon sets (flags) to given formula results that equate in percentages. I.e. H9 = g9/f9 ... A: I assume you have used data validation to achieve the colours - I would do this by having TWO levels ... | |
| Excel | 11/18/2009 |
| Q: I am attempting to create an excel worksheet that will allow inputs on one page, such as the name, ... A: I’m not sure I follow the requirement for the sets etc – but it SOUNDS as though you will need VBA ... | |
| Word VBA | 11/17/2009 |
| Q: I have an excel macro that opens the WOrd application and copies an excel file into word. I would ... A: Not sure what the document is actually doing with regard to the copying across, but something like ... | |
| merging spreadsheets | 11/17/2009 |
| Q: I have 2 spreadsheets and each one in column A has an ID that I want to match up. Worksheet #2 has ... A: Two step process I think – the first is to get the data across to worksheet 1 which you can do with ... | |
| Excel 2007 - auto-updating number | 11/16/2009 |
| Q: I have a spreadsheet that keeps track of employee hours. What I'd like to add is a cell that takes ... A: I have an absence recorder system at ... | |
| Mail Merge from Excel Spreadsheet using Word | 11/16/2009 |
| Q: I have an excel spreadsheet with data as in the example below. I need to do a mail merge usingMS ... A: I'm sure there are other ways, but I tend to do this with a custom function using VBA - see ... | |
| Auto populate date error | 11/16/2009 |
| Q: I have a problem! I have a formula running all the way down column A that autopopulates the date if ... A: I'm not sure you want what you are getting - the formula NOW() will return the current date - and ... | |
| Lat/Lon conversion format | 11/15/2009 |
| Q: I am preparing an excel sheet to convert the coordinates between following formats: HH MM.MMM ... A: the picture doesn't come across very well, but if you would like to email me a sample file at ... | |
| printing | 11/14/2009 |
| Q: i would like to count how many times an excel file has been printed. this is because each time a ... A: This would need a macro to handle it, and I would suggest that it was a custom macro as it would ... | |
| To Print files from excel | 11/13/2009 |
| Q: I have an excel sheet which contains data. Column A contains file names along with filepaths (all ... A: The basic macro is Sub getfiles() Dim n Dim looper Dim MasterBk as worksheet Set ... | |
| Is it possible to run custom macro when excel toolbar button is pushed? | 11/13/2009 |
| Q: What I'm hoping to do is this....When the "PRINT" button the stardard toolbar is pressed it will run ... A: You can use the before Print event of the workbook to run any macros when ANY print command is ... | |
| Combine rows if data in column A matches | 11/12/2009 |
| Q: It seems like this should be a common issue, but I can't figure out how to word it that gives good ... A: Reasonably common - I've answered something similar on here before, so take a look at ... | |
| Vlookup and Match Combination | 11/12/2009 |
| Q: Hey Aidan, I have 2 excel 2003 files which need the values to be compared. In File 1(Sheet1), there ... A: Not sure if you have exact matches for the lookup value or if you need to cater for an error value - ... | |
| Excel Birthday Format | 11/11/2009 |
| Q: I need some serious help and fast! I have entered birthdays into an excel document as general ... A: Changing the date format box won't help - excel DOES store dates as serial numbers, but these are ... | |
| How do I return the sum of the 10 largest numbers of the first 11 numbers in a row which has 12 or more numbers? | 11/11/2009 |
| Q: I have a table with rows of numbers, most are zeros with a dozen or more positive whole numbers ... A: I'm sure it SHOULD be possible to do this in one formula, but I haven't managed it! However, I have ... | |
| Help! | 11/11/2009 |
| Q: Do you know of a way to extract the time only from a Date/Time column in excel. I am trying to ... A: The problem which I hadn't spotted was that column A doesn't ACTUALLY contain a date and time value ... | |
| Help! | 11/10/2009 |
| Q: Do you know of a way to extract the time only from a Date/Time column in excel. I am trying to ... A: The picture is impossible to view I'm afraid, but if a cell contains date and time then ... | |
| Adding not-exact vlookup values | 11/10/2009 |
| Q: I have a list of Project#'s in Column A (ascending order). On a seperate sheet in same workbook, ... A: I'm not exactly sure simply because from the data given there doesn't seem to be anything that would ... | |
| Match up numbers in twoi columns | 11/10/2009 |
| Q: the first column is my telephone directory, the other is a list of the number i have dialed from my ... A: As you only want to flag them, I would use COUNTIF =countif(LongList,ContactEntry) LongList is ... | |
| Wait for the Command Button Click | 11/10/2009 |
| Q: I am trying to code an Excel Macro. In this the first set of Input data is taken form sheet1 and i ... A: I tend to use two buttons where I have a two part process - it makes it easier - the first macro ... | |
| Copy values from one sheet to the other | 11/9/2009 |
| Q: I am having two excel sheets, W1 and W2. In W1 i have certain charecters and in W2 i have the 3 ... A: I’m not following what needs to be done here – can you clarify – if it helps, you can email me at ... | |
| Excel question | 11/8/2009 |
| Q: I hope you may be able to help me with a problem I am having with Excel. I am a commodities ... A: I’m tempted to suggest getting office 2007 which has a higher row count, but I suspect you would ... | |
| SUMIF formula across multiple worksheets | 11/8/2009 |
| Q: I have 20 worksheets that contain some of the same data (names). I want the formula to look at ... A: Cannot be done directly with functions – it’s one of the annoying things about excel – you CAN do it ... | |
| VBA Formula | 11/7/2009 |
| Q: I am trying to write a formula in VBA to update the value of a specific cell based on its value in ... A: use the CHANGE event of the worksheet to handle the macro (vba editor, click the worksheet, change ... | |
| Insertion point | 11/7/2009 |
| Q: assume that the insertion point is positioned at the beginning of the txtName text box. which one of ... A: technically none of them as it needs round not square brackets, also not a method I use myself, but ... | |
| finding letters of the alphabete | 11/7/2009 |
| Q: I like to do crosswords/codebreakers I automated the process but know want to cross the letters used ... A: I think the easiest way to do this would be with conditional formatting where a formula is used – ... | |
| Error with looping macros | 11/7/2009 |
| Q: In Microsoft Excel 2003 I have a macro set up that pulls information from a database where the ... A: There isn't a limit for looping as such, but there IS a limit for variables - you haven't included ... | |
| VBA Formula | 11/6/2009 |
| Q: I am trying to write a formula in VBA to update the value of a specific cell based on its value in ... A: As a function, it should have something stored in a variable called FF, and to VB you have a lot of ... | |
| Cross Referencing with Conditions | 11/6/2009 |
| Q: I work at an academic institution and we are working with academic records. I have two worksheets - ... A: I think it's PROBABLY easiest to do it with a combination of a COUNTIF worksheet function - this ... | |
| To extract date from file name and paste in a cell | 11/6/2009 |
| Q: I have a sheet, Column A contains file paths as hyperlinks. Each of the file name contains date ... A: This function Function GetDAte(MySTr As String) Dim counter As Long For counter = 1 To Len(MySTr) ... | |
| Using IF statements | 11/6/2009 |
| Q: I am trying to use an IF statement to automatically determine a value of a cell (A1) based on the ... A: OK, well - there is a dictionary of excel terms I found some time back which you can get at ... | |
| To extract date from file name and paste in a cell | 11/6/2009 |
| Q: I have a sheet, Column A contains file paths as hyperlinks. Each of the file name contains date ... A: it might be possible, though it sounds tricky - are the formats of the dates the same (so 1 October ... | |
| EXCEL if then statements | 11/6/2009 |
| Q: compute the return you would achieve, if you followed the technical analysis strategy. Look down the ... A: I'm not sure either, but only because I don't know what you are studying and therefore are supposed ... | |
| Userforms | 11/5/2009 |
| Q: Aidan, I am using excel 2000 version. Just wondering if it is possible to wrap text in the command ... A: I haven't currently got Excel 2000 installed, but I'm fairly sure that WordWrap was one of the ... | |
| Excel Summing of Tabs | 11/5/2009 |
| Q: Aiden I have an excel file that has a tab info for every week. For the end of the year i would like ... A: No problem - excel will sum ranges quite happily - to sum across sheets simply enter ... | |
| Create macro to access another application. | 11/5/2009 |
| Q: I want to create a macro through which I can access (1)internet explorer (2)login to the site and ... A: I haven't written anything myself that would interface directly to IE, but I'm wondering what the ... | |
| Excel calculation/formulas | 11/5/2009 |
| Q: I have a spreadsheet that was received in '03 and I work with '07. I have saved the file over and ... A: Assuming you have formulas in place, this would indicate that the sheet is in manual calculation ... | |
| Excel 2007_complex transpose | 11/4/2009 |
| Q: I have attached an image to help explain the problem that I have. I have two columns of data ... A: One possible and relatively quick solution would be pivot table, though this would give ALL ... | |
| Lookup | 11/4/2009 |
| Q: I have a validation list and a table which depending on what users pick from the list displays the ... A: This is an array filter - so it needs an array formula - see ... | |
| excel spreadsheet & VB | 11/4/2009 |
| Q: "HI Aidan, I am working on a spreadsheet that will track incoming trouble calls and their ... A: I’m concerned that you are considering doing this in Excel especially as you mention numerous ... | |
| Compare Column, then extract data from another column | 11/4/2009 |
| Q: I have a set of data and need to do the following:- (example) 1. Compare cell A1 in column A with ... A: It sounds like an array filter would do it as these would give you the row number – see ... | |
| Formula selection | 11/4/2009 |
| Q: I am trying to make a tax projection worksheet for individuals. I would like to be able to change ... A: Drop down box would be data, validation. The calculation method could be as simple as a set of IF ... | |
| Using IF statements | 11/4/2009 |
| Q: I am trying to use an IF statement to automatically determine a value of a cell (A1) based on the ... A: Not clear from the question how many possible values you need to check, so you MAY run into a ... | |
| Match Value then look up range | 11/3/2009 |
| Q: I have one worksheet that has a set of partnumbers with a certain qty needed. I have a second ... A: The data doesn't come across very well via text, but my first question is how fixed is the database ... | |
| complicated formula | 11/3/2009 |
| Q: =IF(C3=antipsychotic, ... A: You have your quotes in the wrong places =IF(C3="antipsychotic", ... | |
| Format change when saving from 2007 to 2003 | 11/3/2009 |
| Q: When some text is entered into a Text Box on Excel 2007, using tabs for spacing, is saved as a ... A: I’m afraid this is probably down to the compatability between the two programs – IF a file has to be ... | |
| VBA SCRIPT | 11/2/2009 |
| Q: Aidan, can you please help with the following script. VBA script for Excel 2000. I would like a ... A: Sub usedrangeselecter() Dim x As Long x = Cells.SpecialCells(xlCellTypeLastCell).Row Do While ... | |
| Search for two+ empty rows and delete all but one | 11/2/2009 |
| Q: Trying to get a macro to search for gaps between the info imported. My problem is I need it to ... A: I think this will do it for you Sub deleter() Set currentcell = Worksheets("Sheet1").Range("A1") ... | |
| Multiple IF statement | 11/2/2009 |
| Q: sorry to trouble you again so soon but I tried sending this query to another expert (Craig) but not ... A: As your text is not very variable, this can be done by extending the IF statement – without really ... | |
| Email from a database | 11/2/2009 |
| Q: I have a database of names, addresses, telephone numbers and e-mail addresses all on Excel (Title in ... A: The second bit negates the first bit, as least as far as a bulk mailing is concerned as there isn’t ... | |
| Counting emails listed in excel sheet | 11/2/2009 |
| Q: I'm using excel 2003. We use an excel sheet to record all of our sale enquiries and we collect ... A: The COUNTA worksheet function would count entries, so would ignore blanks – so a count of records ... | |
| Log file for ms excel | 11/1/2009 |
| Q: Sub WriteLogFile() On Error Resume Next Dim iFileNumber As Long Dim strData As String ... A: Change this from a sub routine to a macro running on the before close and before save events (or ... | |
| employee records | 10/30/2009 |
| Q: I really need your help , I want to creat a table , and in this table I have 10 departments Each ... A: Not sure I’ve got enough to go on here – IF I was doing this myself I would PROBABLY have a field ... | |
| ROUNDING TIME | 10/30/2009 |
| Q: I am working on a time sheet and need the answer to round the minutes to the following: In at 9:00 ... A: Not sure how you are recording the minutes, but IF they are stored AS minutes then ... | |
| Time | 10/29/2009 |
| Q: Cell format is [h]:mm IF A1 or B1 is greater than 24:00 then C1="Check your Data" I'm trying to ... A: Cell format doesn't matter for this one, you just need to remember that to excel TIME is a decimal ... | |
| auto fill a vlookup | 10/29/2009 |
| Q: Hell, i wrote a macro that has a vlookup in it. what i would like to do is autofill that vlookup ... A: It's alright, my keyboard misbehaves too, so I knew what you meant. For some reason when I copied ... | |
| Excel sorting by date | 10/29/2009 |
| Q: Do you have any ideas how I can solve the problem that excel has with sorting date fields. If I sort ... A: That is VERY wierd as it should then come across as a date - although I'm ASSUMING it' actually a ... | |
| default selection in drop-down list | 10/29/2009 |
| Q: In Excel 2000, Based on user input, Cell D12 displays an answer. Cell c12 has a drop-down list of ... A: The formula should be fine, but if you are copying and pasting I notice that it came over as curly ... | |
| Excel sorting by date | 10/29/2009 |
| Q: Do you have any ideas how I can solve the problem that excel has with sorting date fields. If I sort ... A: If excel is sorting the way you specify, this mean the cells AREN'T dates,but text that to you looks ... | |
| default selection in drop-down list | 10/29/2009 |
| Q: In Excel 2000, Based on user input, Cell D12 displays an answer. Cell c12 has a drop-down list of ... A: Two options – one would use VBA to set the default, the other and possibly easier would be a formula ... | |
| Formula | 10/29/2009 |
| Q: I need a formula for calculating based within a range. example I have a 88 documents, I need to ... A: Not sure what is determining the count entry, but =countif(YourRange,"<11") would give you the ... | |
| Excel "What If" Statements | 10/29/2009 |
| Q: here is what i am working on...its a financial table. for the first 14 cars a salesperson sells we ... A: Not clear what is being calculated here - I would IMAGINE that it requires a count of the salesmans ... | |
| To Use Iserror Formula | 10/29/2009 |
| Q: I have the following formula in cell A1 =If(c2=1," ","OK) When there is 1 in C2 the cell A1 becomes ... A: The ISERROR function returns TRUE if the cell it refers to contains an error value (#N/A, #Value ... | |
| auto fill a vlookup | 10/28/2009 |
| Q: Hell, i wrote a macro that has a vlookup in it. what i would like to do is autofill that vlookup ... A: Dim counter For counter=1 to cells.specialcells(cells.SpecialCells(xlCellTypeLastCell).row ... | |
| Copy and Paste to Sheet2 | 10/28/2009 |
| Q: I am in need of a macro which first finds the word "Error" in the column I and then copies or cuts ... A: Try this Dim counter Dim outvar outvar = 2 For counter = 2 To ... | |
| drop-down list, etc. | 10/28/2009 |
| Q: Based on user input, Cell D12 contains an answer out of 3 possible answers. Cell C12 contains a ... A: Two options – one would use VBA to set the default, the other and possibly easier would be a formula ... | |
| Time/Date Formula | 10/28/2009 |
| Q: I have a list of problem tickets numbers (colA) the date they were opened (B) the time (C) the date ... A: There is - but I'm not 100% sure if you want the simple answer or if it needs to be more complicated ... | |
| MS Excel | 10/28/2009 |
| Q: I have name,address,phone number,fax number,e mail and website, all listed below each other in ... A: I’m assuming you would prefer to do this with a formula – in this example I’m putting the data in ... | |
| excel | 10/27/2009 |
| Q: Labor Materials Unit # Hours Learning Rate Cost Learning Rate 1 5,000.0 0.7 250,000.00 0.8 2 ... A: There doesn’t seem to be enough information to go on in this question – can you clarify please – ... | |
| Locate a cell in spreadsheet | 10/27/2009 |
| Q: How to use a function to return the cell address in a range of cells for a specific value or the ... A: The MATCH worksheet function will do this for you Returns the relative position of an item in an ... | |
| Excel Data Validation | 10/27/2009 |
| Q: I hope you can help me with this. I have 3 columns: Cycle, Event, Value where Cycle 1, 2, 3 to 9 ... A: It is complicated, probably to the point of being almost impossible, certainly without VBA – with ... | |
| IF, then, then, then | 10/27/2009 |
| Q: I get large spreadsheets filled with data everyday. I want to pull specific data from that spread ... A: The picture doesn't come across very well - but that is probably my eyesight! I would do this with ... | |
| Table Creation from 3 Individual Colums | 10/27/2009 |
| Q: My company has recently implemented a new way of creating Bill of Materials which involves the ... A: Very possible - it's a pivot table - you didn't specify version so I'll assume NOT 2007 - at which ... | |
| Excel Share Issue | 10/27/2009 |
| Q: We work on an Excel Spreadsheet on a share basis (network) and two weeks ago it was necessary to ... A: The general feeling amongst experts is that shared files are a bad idea – they will crash and will ... | |
| Date formatting | 10/27/2009 |
| Q: I usually generate a report where I receive the date in various possible format...i want to convert ... A: General should be fine - just select the column, format cells and apply a date format - it should ... | |
| Excel Data Validation | 10/27/2009 |
| Q: I hope you can help me with this. I have 3 columns: Cycle, Event, Value where Cycle 1, 2, 3 to 9 ... A: It can probably be done but at face value it seems unnecessary - the data being chosen is the same ... | |
| Copy from n number of excel sheets into one sheet. | 10/27/2009 |
| Q: how r u..?? i have a problem with excel sheet. i'm having one workbook which contains bunch of ... A: As you haven't been able to specify rules that can be followed, it would not be possible from the ... | |
| Excel 2008 fill down and autoformatting | 10/26/2009 |
| Q: I am learning Excel 2008 for Mac. I have used fill down to create one column of the 12 months ... A: I'm not familiar with the mac, but on a PC using the ctrl key would let you select non contiguous ... | |
| Please Help Me With V lookUp or Macros | 10/26/2009 |
| Q: Good Morning Aidan , I have 2 sheets Named , Sample and results, I want to check the data in the ... A: Try this version Sub Macro3() ' Macro3 Macro dim counter as long for counter= 2 to ... | |
| Autosend Lotus Notes Email upon entering username in Excel Cell | 10/26/2009 |
| Q: I am looking to have an email sent out to my colleagues upon entering their name into an Excel ... A: We are on an older version of Lotus Notes in our office but I THINK this function should still work ... | |
| Autopopulating dates | 10/26/2009 |
| Q: I have asked this question to another expert and he responded that I would need to produce some ... A: It can PROBABLY be done with formulas, though they would get a little messy - a macro solution is ... | |
| Modifying formula to only get business days | 10/26/2009 |
| Q: I have written this formula to check the text of a cell, and depending on what's in there to get the ... A: You certainly don't need VBA - I've had to do formulas to work out service time based on fixed hours ... | |
| Excel 2007 | 10/26/2009 |
| Q: I am searching for an answer, not sure if its not a trick question. What is the last cell available? ... A: Not a trick question at all = these stats come from the help file Open workbooks Limited by ... | |
| Macro for different passwords for different columns | 10/24/2009 |
| Q: Hey Aidan... I am new to Excel macros. But I require to do an assignment where I want to apply ... A: It would be quite difficult I think - the macro would have to unprotect the sheet and set all ... | |
| Dealer List Comparison | 10/23/2009 |
| Q: I have a list of current dealers for our products and a list of dealers for a competitor. Can I ... A: I'm ASSUMING you have a degree of consistency in the naming of these such that dealer A is spelt ... | |
| To Retain Date Format While Concatenating | 10/23/2009 |
| Q: I have date in cell A1 i.e 21-12-2009. And I have some text in cell A2 i.e "Date of Interview is". ... A: Concatenating simply adds the text values together – and the format of a non text cell is not ... | |
| pls need a help | 10/23/2009 |
| Q: pls help me how to Create a formula in excel which will add .com to the end of the word and www. At ... A: =”www” & YourCellRef & “.com” gives you a concatenated phrase with www.etc etc.com The second bit ... | |
| Excel Formula Dilemma | 10/22/2009 |
| Q: I have a compliancy report that lists how many times YES (column A), how many times NO (column B) ... A: You didn’t say how you worked it out, but I would imagine with a countif divided by a counta or ... | |
| Apply multiple print ranges to entire workbook | 10/22/2009 |
| Q: I have a very large workbook containing financial data by month, in columns, for 1994 - 2009. The ... A: You would (I think) have to do this via a macro, though that would be easy enough to do – record the ... | |
| auto populate an entire row | 10/22/2009 |
| Q: I need some help I am using Microsoft Excel 2007 and I am tying to auto populate a row from another ... A: I’m assuming “entire row” is a fixed size set of columns – if so, the vlookup function will do it ... | |
| Excel - relating data in two worksheets | 10/22/2009 |
| Q: I am trying to figure out if what I want to do is possible, and hope you can tell me. If is ... A: It is possible, though it might be tricky with the two workbook scenario- I would probably use ... | |
| Help with INDEX and MATCH | 10/21/2009 |
| Q: Sample Table : -- A B C D E 01 N1 12345 54678 91546 ... A: Difficult to see from the typed example, but feel free to email me a sample file – ... | |
| Age Calculation, problem with formating the cell | 10/21/2009 |
| Q: I am creating a form, in which I have calculated the persons age from their date of birth using ... A: I personally would use the YearFrac function as this gives an exact result, but that's minor - the ... | |
| grouping by values in one column then summing the values of another column | 10/21/2009 |
| Q: I have a spreadsheet that looks something like this (only with 1000's of rows and several more ... A: Yes, simply use a pivot table – data menu, pivot table and chart report, then drop the company name ... | |
| Convert Large Blocks To Lists | 10/20/2009 |
| Q: I've searched for the answer to my problem & didn't find it. Hopefully this isn't a forehead ... A: Sub sortme() Dim counter As Long Dim cls counter = 1 For Each cls In ActiveSheet.UsedRange If ... | |
| Calculate a "due date" plus/minus 3 weeks (date range) | 10/20/2009 |
| Q: In Excel 2007: I will have an unknown start date that I will enter in a cell (Visit A). I need to ... A: I’m not QUITE sure what you want to return here, but if your Visit A date was in (lets say ) cell A1 ... | |
| To Break down EmployeeService Period | 10/20/2009 |
| Q: I have an excel sheet which calculates the service period of employees in years. For example I have ... A: Not totally clear, but I assume it’s based on actual service – so if only 8 years, it would be ... | |
| Conditional Formatting | 10/19/2009 |
| Q: I have a document in excel listing quite a few clients. We provide different services to each ... A: It is possible, though conditional formatting will only allow up to 4 levels of format – which I ... | |
| Asking Question | 10/19/2009 |
| Q: 1. I want to add continous number in a cell like in cell no a2 I entered 1 in cell a1 will show 1, ... A: It’s not clear what you want from the first part of the question although it SOUNDS as though you ... | |
| excel quiz answering formula | 10/18/2009 |
| Q: =IF(OR(B6={"tom","dick","harry"}),1,0) If i have a table used for a quiz and in B6,B7 & B8 you can ... A: You were clear, but I wouldn’t try to do this with a formula – it’s probably possible but it’s going ... | |
| price formula | 10/18/2009 |
| Q: I am entered the following formula in excel 2003 and I am ready to jump off a cliff. ... A: There is a limit on the number of nested brackets (7) AND the IF formula is hard(er) to edit if the ... | |
| HyperLink | 10/16/2009 |
| Q: I'm trying to create a Hyperlink in my Worksheet, when I click on it will open Search tool to search ... A: I wouldn't want to put it in a cell necessarily as that would only work if you could see the cell, ... | |
| Excel colums, numbers imported as dates | 10/16/2009 |
| Q: I'm importing data into excel. One column relates to horse racing odds: 9/1, 7/4, 15/2 etc. Trouble ... A: IF the data is text format, then you should be able to work through the import wizard and specify ... | |
| Compound interest | 10/14/2009 |
| Q: It was so good, I've come back with another question. I have figured out how to calculate compound ... A: Unfortunately, this sort of maths is outside my knowledge, but ... | |
| date and time | 10/14/2009 |
| Q: Subject date and time and data Question hi!i'm a beginner and i think that i will go crazy ... A: I mentioned in the original answser that I wasn't totally sure what you wanted to achieve - it would ... | |
| Auto-populate cells with related values | 10/14/2009 |
| Q: Aiden - So glad to find you here! I have a very similar situation as the person who posted under ... A: I presume you are OK with the data validation list to get the data - at which point, you need to use ... | |
| How to Prevent Macros from Overwriting Forumulas | 10/13/2009 |
| Q: I've been trying to find a solution to this problem and after hours of googling and trial and error, ... A: Amending C to the result of the calculation will indeed alter the formulas - as an aside, an easier ... | |
| Track the opening of an Excel File | 10/13/2009 |
| Q: We have recently been required to keep our timesheets on the server at work. I've never cheated on ... A: I can try, but to be honest all anyone needs to do to get round this is disable macros, OR not save ... | |
| Hyperlinks | 10/13/2009 |
| Q: I am "hyperlinking" to different types of files, i.e., Word docs., PDFs, that are located under a ... A: A hyperlink SHOULD be able to link to the full file, so I would expect that issues here are around ... | |
| Excel 2007 custom filter | 10/13/2009 |
| Q: My question refers to customizing the data displayed in the Excel 2007 filter drop-down box. The ... A: Nice idea, but the auto filter shows the items that are in that list, not other items - but why not ... | |
| hours calculation | 10/13/2009 |
| Q: A1=01-10-2009 7:30 AM, B1=01-10-2009 9:30 PM, C1=B1-A1=14hrs0min, D1=8hrs30min, E1=C1=D1=5hrs30min. ... A: You have speicified some values as time and some as date and time. BUT looking further at the ... | |
| Excel VBA Insert Object As Icon | 10/12/2009 |
| Q: I am trying to accomplish the following: Have user click on a button that runs a macro. The macro ... A: Yes it should be - my delay was primarily in trying to find a non office 2007 machine to do this on ... | |
| Excel | 10/12/2009 |
| Q: This question is two parts I have an extensive amount of time data points over 3,000 taken every ... A: I'm not clear what is being averaged here (the average of a series of points in the x second time ... | |
| date and time and data | 10/11/2009 |
| Q: i'm a beginner and i think that i will go crazy looking for a formula :)to do what i want, so and ... A: I THINK you are basically after a multiple condition countif, but I'm not totally sure about this ... | |
| Excel VLOOKUP Function | 10/10/2009 |
| Q: I hope you can help me. I am using excel 2000 I would like to test a cell and if it does not ... A: You can use conditional formatting, but this would allow up to 4 levels of format (one being the ... | |
| Extracting Numbers | 10/10/2009 |
| Q: Hope you are well I am using excel 2008 and am trying to extract numbers from a alphanumeric string ... A: see http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/0c9a27f591cb0f96 ... | |
| VBA for unique account | 10/9/2009 |
| Q: Hope you can help me with my problem. I have 2 sheets, one that contains outgoing accounts and the ... A: It doesn't come across very well via allexperts, so I'm not 100% sure what dictates a record, but ... | |
| VBA: only run command on new workbook files | 10/9/2009 |
| Q: I'm hoping that you may be able to help me with an Excel VBA code. I have the following routine ... A: Right(wkbsrc.ActiveSheet.Name, Len(wkbsrc.ActiveSheet.Name) - InStr(wkbsrc.ActiveSheet.Name, "_")) ... | |
| Product table | 10/9/2009 |
| Q: i`m working on quite simple task, but as more products i have to enter in my list, as more ... A: It would be possible but I would have THOUGHT that maintaining the seperate tables and then ... | |
| Excel VBA Insert Object As Icon | 10/9/2009 |
| Q: I am trying to accomplish the following: Have user click on a button that runs a macro. The macro ... A: I would do this by displaying the application.GetOpenFIlename to get the basic settings dim ... | |
| help with formulas | 10/9/2009 |
| Q: i am creating a spread sheet where i require information to be transferred from sheet 2 to sheet 1 ... A: This sounds like it can be done with Vlookup, which is vlookup(what,where,which,logical) WHAT is ... | |
| Calling a Macro with Arguments from another Workbook | 10/8/2009 |
| Q: How can I use VBA to call the following macro from another workbook: Upgrade_RunMacro(arg1 as ... A: should work as per the help file Runs a macro or calls a function. This can be used to run a macro ... | |
| HyperLink | 10/8/2009 |
| Q: I'm trying to create a Hyperlink in my Worksheet, when I click on it will open Search tool to search ... A: You can't do it with a hyperlink, which would simply jump to a location, but you CAN do it with a ... | |
| Creating something | 10/8/2009 |
| Q: I have a basic understanding of Excel and have been learning through trial and error and also ... A: I start worrying when I hear the word shared - a shared drive is no problem, but a shared excel file ... | |
| Microsoft Excel | 10/7/2009 |
| Q: I am trying to link data from one spreadsheet to another, I usually just use the = sign and it ... A: I'm probably missing something, but your explanation of what you are doing SOUNDS right - you SHOULD ... | |
| collect text from several cells to one cell | 10/7/2009 |
| Q: I would like to ask a little help. I automatically copy a range (cells containing text) from an ... A: Sub combineme() Dim lcount, dcount As Long For dcount = 16 To ... | |
| Formula to change text colour in Excel | 10/7/2009 |
| Q: I need a formula to change the colour of text (in the whole row) in my spreedsheet depending on what ... A: You've given examples which probably don't equate to the whole story - IF the number of options is 4 ... | |
| Microsoft Excel 97 - linking drop down boxes | 10/6/2009 |
| Q: I have an excel spreadsheet with two drop down boxes which contain the same list of options. I want ... A: I'm not quite sure WHY you want this to happen, but the easiest way would be to have the drop down ... | |
| Merges | 10/6/2009 |
| Q: I am trying to merge from an excel spreadsheet (all the info I need in my letter is in this one ... A: I would use a countif function to count the number of times the donor appears from that row down, ... | |
| excel chart help (legend and color to b/w) | 10/4/2009 |
| Q: I have an old 2003 version of Excel. I'm creating stacked column charts from my excel data. I need ... A: Annoyingly there isn't a direct way that I can see to set a standard chart to black and white, ... | |
| Excel Complex Advanced Filter | 10/3/2009 |
| Q: I have a long list of data records, sample enclosed, what I am trying to ahcieve is to filter out ... A: I'm not quite sure I follow "consecutive dates" but lets try it with a sumproduct ... | |
| Find Dependent Links | 10/2/2009 |
| Q: I am trying to figure out how to tell if a spreadsheet is dependent on a spreadsheet I'm working in. ... A: Interesting prblem - the only way I can think would be to use a program like Global Find - ... | |
| 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: There is an issue in that you don't have a consistent method of recording time - 2.30 is in time ... | |
| Adding cells in different Workbooks | 10/1/2009 |
| Q: Suppose I've got values in different .xlsx files that need to be added together (I didn't do it this ... A: Sorry, should have been clearer - I was referring to the one on the task bar, but I forgot that I'm ... | |
| Hide rows without a particular word | 9/30/2009 |
| Q: I have worksheet where the cells contain names, such as price, supply, rank, etc., and they are ... A: If you could state that a full word was followed or preceeded by a space, they you could search for ... | |
| Excel macro to (un)protect select sheets | 9/30/2009 |
| Q: I am trying to run a macro (Book) that sets up the print options for a sheet and hides certain ... A: OK the Book macro is fine, BUT I had overlooked the protect/unprotect comment -if a worksheet is ... | |
| HOW TO GET SUMS ON AN EXCEL COLUMN | 9/30/2009 |
| Q: I HAVE 25 COLUMNS AND 30-50 NUMBERS IN EACH COLUMN. WHEN I GO TO COLUMN "A" AND SUM THE TOTALS I ... A: POSSIBLY set to manual calculation - although this wouldn't explain if the FORMULA isn't going ... | |
| Excel Macro - Change Color of referenced cell | 9/30/2009 |
| Q: I have a column of cell references, IE: the value of cell A1 is "D3", value of A2 is "F5", value of ... A: You did explain it, and the macro I gave you SHOULD do that - at least, it did when I ran it ... | |
| Conditional IF statement over data range | 9/30/2009 |
| Q: I am creating a spreadsheet that is designed to track enrollment in a trial. Information from each ... A: Your formula would do it IF you entered it over 16 rows(ie select 16 rows FIRST then start entering ... | |
| formula | 9/30/2009 |
| Q: I was wondering if Excel can solve for "x". I need to know what a certain number needs to be in ... A: Make X another cell reference in the spreadsheet and then use Goal Seek to solve it - in this ... | |
| Adding cells in different Workbooks | 9/30/2009 |
| Q: Suppose I've got values in different .xlsx files that need to be added together (I didn't do it this ... A: I assume you mean you CAN access the xlsx files - so open them all first, then start typing a normal ... | |
| Excel spreadsheet addresses into printed letters | 9/30/2009 |
| Q: I have an excel spreadsheet with around 1,000 contacts and addresses in, with the fields labelled ... A: What you didn't say was how you are getting the letters - IF this was a mail merge via word, then ... | |
| trace dependence | 9/30/2009 |
| Q: I have 3 separate workbooks where the trace precedents works, but trace dependents doesn't. All the ... A: The only answers I've found are answers you may already have seen ... | |
| Hide rows without a particular word | 9/29/2009 |
| Q: I have worksheet where the cells contain names, such as price, supply, rank, etc., and they are ... A: Sub Hider() Dim Hideme As Boolean Dim rowvar As Long, colvar As Long, looper As Long, Colloop As ... | |
| Excel 2003 Formula to calculate annual due dates | 9/29/2009 |
| Q: I am working on a spreadsheet, my first, to track due dates for employee requirements based off date ... A: I'm not sure I've got enough to go on here - you mention annuals, and also 4 years indicating that ... | |
| HOW TO GET SUMS ON AN EXCEL COLUMN | 9/29/2009 |
| Q: I HAVE 25 COLUMNS AND 30-50 NUMBERS IN EACH COLUMN. WHEN I GO TO COLUMN "A" AND SUM THE TOTALS I ... A: having pressed ctrl c you need to select the cell to paste the formula to - but a quicker way would ... | |
| Excel Macro - Change Color of referenced cell | 9/29/2009 |
| Q: I have a column of cell references, IE: the value of cell A1 is "D3", value of A2 is "F5", value of ... A: dim looper on error resume next 'in case any cell in the range doesn't really have a reference in ... | |
| Regarding vba programming | 9/29/2009 |
| Q: I am doing my complete project using excel vba programming including formatting of cells. I need to ... A: Cells.SpecialCells(xlCellTypeLastCell).Row would pick up the last used row - so With ... | |
| RE: Deleting unwanted rows from multiple worksheets | 9/29/2009 |
| Q: I have 5 worksheets. I am trying to delete all rows containing "UNKNOWN" from these sheets. The ... A: Sub DeleteRows() dim currentcell,nextcell,looper dim DeleteVar as boolean for each sh in worksheets ... | |
| Counting in multiple columns | 9/29/2009 |
| Q: I am a new Excel user. Can someone help me in getting solution to below problem: I have following ... A: =sumproduct(--($A$2:$A$10="A"),--($B$2:$B$10=80)) for the first part and ... | |
| question on maintaining inventory excel sheet | 9/19/2009 |
| Q: sir i am working with on eof the insurance company in india i have always one complication in excel ... A: I appreciate that english is probably not your first language, but it is hard to follow what it is ... | |
| Transform Excel table into Access Table format | 9/18/2009 |
| Q: I would like to re-format an Excel table into a format suitable for uploading into an Access table. ... A: As this would seem to be a one off, I'd do it manually - insert a column and type Product A and auto ... | |
| complicated lookup | 9/18/2009 |
| Q: i've got a large amount of data for a shipping invoice. there are three variables here that i'm ... A: You can do this with a trick using SumProduct which allows a multiple condition sumif(which if there ... | |
| excel column identifier | 9/18/2009 |
| Q: I asked the question of Bob and got the answer below, but tools at the top doesn't have an "options" ... A: Quite a short answer - and as you didnt' mention the version to me, it might be difficult for me too ... | |
| XL SUM formula | 9/18/2009 |
| Q: I have a spreadsheet which monitors our current ‘opportunities’ for the services we deliver. I’m ... A: Sorry about that - I wrote the formula in excel but somehow in copying it managed to replace an = ... | |
| merging the columns of a matrix into a single long vector | 9/17/2009 |
| Q: I have an excel spreadsheet containing an important number of columns. Each column has data ... A: Ah, OK, sorry for the misunderstanding - obviously you can do it manually by copy and paste, but ... | |
| Excel - Conditional Formatting | 9/17/2009 |
| Q: I want column D to generate the highest of those 3 numbers (from A, B and C). Is this under ... A: As you guessed it's something else - conditional formatting would set the FORMAT of the cell based ... | |
| excel vba help urgent | 9/17/2009 |
| Q: "I have to teach a lesson for an assignment and I am having some major problems with the code in VBA ... A: difficult to see from the picture - my charting source tends to be www.peltiertech.com and this ... | |
| activate/deactivate macros | 9/17/2009 |
| Q: I am wondering how to add a button on my custom toolbar to activate a macro and then I also want to ... A: both are possible, and both would need ANOTHER macro to achieve (although it would be the same ... | |
| XL SUM formula | 9/17/2009 |
| Q: I have a spreadsheet which monitors our current ‘opportunities’ for the services we deliver. I’m ... A: To use more than one condition for a sumif you can use a trick of sumproduct which is that a true or ... | |
| linking references | 9/16/2009 |
| Q: I have to use a linking reference to link two worksheets and show a decrease in pay of 5%. Can you ... A: Cannot be totally specific as I don't know the cell references involved, and indeed I'm not sure ... | |
| Grabbing values from multiple workbooks | 9/16/2009 |
| Q: I'm trying to figure out the formula that will allow me to grab multiple cells from multiple ... A: easiest way is simply =[filename1.xls]sheet1!B10 +[filename2.xls]sheet1!B10 etc simply open all ... | |
| Mandatory fields, column ranges | 9/16/2009 |
| Q: I really need some VBA help!! I have been asked to create a patient tracker on an excel spreadsheet ... A: I can probably help, though I'm not sure I've got enough to go on to be specific at the moment - ... | |
| Copying cells after a filter | 9/16/2009 |
| Q: Say I have 20,000 rows in column A. All data entered in column A is either Alpha, Bravo, or Charlie. ... A: easiest way to do this is to use a blank column - in which you enter =if(a2="Bravo",a2,b2) (this ... | |
| merging the columns of a matrix into a single long vector | 9/16/2009 |
| Q: I have an excel spreadsheet containing an important number of columns. Each column has data ... A: Not sure how many columns, and not sure how you want the data seperated, but the basic principle ... | |
| hourly/daily averages | 9/16/2009 |
| Q: I have a large data set (26444 lines) that consists of 2 columns: date time (i.e., 8/22/2008 12:00) ... A: Easiest way would be to create a new column that shows the hour and a new column for the day ... | |
| Excel Help | 9/16/2009 |
| Q: I have two worksheets in an excel file. Log and Template. The log has five columns and i will be ... A: I'm not 100% clear what needs to happen here - you could use =log!a7 & log!a8 & log!a9 to pull in ... | |
| Summing outputs from self-referencing if-statements | 9/16/2009 |
| Q: I am using Excel 2007. I am running multiple scenarios and need to sum the outputs. I have a ... A: All i see in the picture is the data and one formula,so it's a littl tricky to resolve - is it ... | |
| Separate numbers from text | 9/16/2009 |
| Q: I have cells with this format: Peter Vault10 Caroline Reed8 Mary Johns9.625 And I will like to ... A: I don't think there is an inbuilt function for this (although another expert may know a trick that I ... | |
| Improving on Do Loop macro efficiency | 9/16/2009 |
| Q: I would really appreciate your help and/or advice regarding the efficiency of my macro which, ... A: I would probably do this without a macro - you create the subtotals, then use data, filter, ... | |
| Save As Macro | 9/16/2009 |
| Q: I have a macro that I am writing that uses save as. The file that I open is a template (.xlt) when I ... A: not sure what code you have to handle the save as, but presumably the file name ends up as ... | |
| Copying columns but admitting certain values. | 9/16/2009 |
| Q: I had a pool of serial numbers which I linked into a new workbook in column A (around 3000). I then ... A: a couple of options - firstly, data, filter, autofilter - then choose custom and select does not ... | |
| Advance Filter | 9/16/2009 |
| Q: Aidan, I have a data set where I use the Advanced Filter to sort through a column of data. I have ... A: If it's a macro, I'd probably get it to set the row height to standard for matched cases and zero ... | |
| Excel, MAXA Value | 9/16/2009 |
| Q: I want to assign the result [or value] of the MAXA function by it's row-correlating name. My MAXA ... A: I think I'd do this with a custom function as you want to combine text values which match the entry ... | |
| Excel Help | 9/16/2009 |
| Q: I have folder which contain 17 excel files on daily basis. I have to copy all excel file name in ... A: This code will do what you want - it stores the data in A2 downwards on the active worksheet. ... | |
| Lookup the closest match | 9/16/2009 |
| Q: Hey Aiden, Need a big favor to ask. Its regarding the vlookup formula. Here is the scenario. I have ... A: The problem is that IF you are using vlookup it will look in the FIRST column for a match and then ... | |
| Array Formula | 9/15/2009 |
| Q: I am attempting to write an array formula. I am trying to determine the total number of units for ... A: Difficult to give you a precise answer here as I don't know the ranges, AND for three countries I ... | |
| Excel Multiplying Criteria | 9/15/2009 |
| Q: I have two sheets on the same worksheet. On one I have a column with certain dates (column D). On ... A: Pictures don't come across very well, but this sounds like EITHER a sumproduct function or a vlookup ... | |
| If Statement | 9/15/2009 |
| Q: I'm building a nested "IF" statement. I understand that it has a limit of 7 statements that can be ... A: Anytime you need more than 7 nests is a good time to think of alternatives - not always possible, ... | |
| Print options | 9/15/2009 |
| Q: I am using Excel 2003 and want to know how to print two virtual pages on one physical page. My ... A: Not clear what you mean by virtual pages, but IF excel views two items as being two pages, it will ... | |
| change cell color when | 9/14/2009 |
| Q: I want to write a formula to add and subtract a series of cells resulting in a total in my last ... A: You are on the right lines - under conditional formatting, choose the FORMULA option - this then ... | |
| Excel - color cell based on increase/decrease in data | 9/14/2009 |
| Q: I'm manually entering data (numbers only) into cells. Based on the change in value of a cell, I ... A: It is certainly possible - the only issue is that this would ALWAYS kick in Public oldval, myval ... | |
| Excel VBA macro help | 9/14/2009 |
| Q: I am new to excel macros and I want to copy and insert rows using macro. Something like Copy rows ... A: A good way to learn how to write macros is to record them, and in this case that would give you ... | |
| copying sheets/formulas | 9/13/2009 |
| Q: I have separate sheet that summarizes certain information from other sheets via formulas in the ... A: probably the easiest fix is to do what you are doing, then edit, links and change the source - to ... | |
| Excel auto numbering | 9/11/2009 |
| Q: I want to be able to create an invoice number that will automatically increase by one when I open ... A: Not sure this works well for a novice as it would need a macro - but see ... | |
| If then, sort of | 9/11/2009 |
| Q: I have three cells, N2, O2, and P2. They are going to be sums of three distinct columns. I would ... A: Much easier than you made it, and indeed than I tried to make it when I started working on it - so ... | |
| IF and Filter Macro | 9/11/2009 |
| Q: I am writing with a Macro question in Excel 2007. The first thing I am trying to do is copy ... A: Lots of parts to the question -I would recommend http://www.rondebruin.nl/tips.htm for lots of ... | |
| Share an excel file using VBA | 9/11/2009 |
| Q: Hope your are doing great. I am having trouble sharing a file (program in VBA)in excel. If i dont ... A: I don't think you are going to be able to share/unshare through code, but I would caution strongly ... | |
| If and Match Function Combination | 9/10/2009 |
| Q: I have 2 excel 2003 that I'm using. File A which has all of the store numbers in it and the ... A: The formula looks fine, so SHOULD be returning the right store, though it would always return the ... | |
| Filtering | 9/10/2009 |
| Q: If i have two lists and want to find data in one list that is not in the other is there an easy way ... A: I'd use a COUNTIF function to count the entry against the other list =countif(MainList,YourData) ... | |
| Negative time and conditional formatting | 9/9/2009 |
| Q: I'm subtracting two time values to determine the difference. If the diffence is between -1:59 and ... A: I'm not clear what you want to do here as there are (it seems) two contradictory statements - you ... | |
| Progressive Invoice Numbers / protection | 9/9/2009 |
| Q: Would like to create an Excel invoice template/form for employees to utilize but would need a way to ... A: you probably know that a worksheet can be hidden or unhidden by using the menu choices - within VBA ... | |
| Excel - color cell based on increase/decrease in data | 9/9/2009 |
| Q: I'm manually entering data (numbers only) into cells. Based on the change in value of a cell, I ... A: If this relates to altering one cell, then it is possible but it would need VBA to handle it using ... | |
| Progressive Invoice Numbers / protection | 9/9/2009 |
| Q: Would like to create an Excel invoice template/form for employees to utilize but would need a way to ... A: I have an excel invoice template at http://www.aidanheritage.byethost3.com/excel/ - which probably ... | |
| macro | 9/8/2009 |
| Q: I've developed a Tool that is being used by 30+ users. Every time the Tool is opened by a user, the ... A: Not sure I follow the question - but if each file equates to a sheet in the workbook then the macro ... | |
| Multiple Criteria | 9/8/2009 |
| Q: Need to track product conversions, qty and $$ amount. 1.Column I25:I101 needs to equal "OS Conv." ... A: not sure your summary helped, but I assume you are summing if multiple conditions apply - at which ... | |
| Excel and Text Files | 9/8/2009 |
| Q: I am a newbie using excel and I struggling to use excel with text files. I have a text file ... A: Prior to excel 2007, there was a limit to file size of 65536 and you cannot exceed this. SO options ... | |
| 3 dimensional constant values | 9/7/2009 |
| Q: I know how to anchor references e.g. =A2+$J$23 But if I want to choose a cell on another ... A: I don't follow what it is you are trying to do - unless you simply mean you want to select a cell on ... | |
| Default file location in Excel | 9/5/2009 |
| Q: I am using version 2000. There's some glitch where everytime the PC gets rebooted, the default file ... A: The MAPI folder is supposed to be their for mail processes (messaging application programming ... | |
| Generate automatic Invoice no and P.O | 9/5/2009 |
| Q: i am vikas, i have an invoice formate in excel. in this i always write invoice, when i make new ... A: I'm not clear what you want to happen here - there is no automatic process to update a sheet name, ... | |
| Counting Blank Cells... | 9/4/2009 |
| Q: Aloha Aidan! I am not excel knowledgable but I'll try to explain to the best as I can. I have a ... A: If I follow this correctly, it's basically a count IF requirement - you are counting IF it's the ... | |
| Linking excel files to be used in a Macro | 9/4/2009 |
| Q: I am looking for some help on linking or reference a different excel spreadsheet file in a macro. ... A: I'm not sure I'm clear what you want the code to do - it sounds as though it would need to open and ... | |
| SUMIF function | 9/4/2009 |
| Q: I currently have a spreadsheet that I wouk out items purchased between specific dates and then adds ... A: easiest way is via sumproduct, which allows a multiple condition sumif function ... | |
| MODE for text values | 9/3/2009 |
| Q: MODE tells you the most common value in a set. But what about text values? How can I find the most ... A: I've found some VBA solutions online, but you can do it with an array formula - I assume your list ... | |
| Excel chart range with vba | 9/3/2009 |
| Q: Aidan, I have a spreadsheet template that I import data into and create a chart from that data. The ... A: You can use the RANGE command or the cells command - I'm not sure what you are doing with the range, ... | |
| Calculate horizontally to end | 9/2/2009 |
| Q: How would you formula the calculation of a row to infinity? My current formula is: =SUM(K5:AE5)+C5 ... A: Excel luckily doesn't go to infinity so the easiest fix would be to enter the last possible column ... | |
| Default file location in Excel | 9/2/2009 |
| Q: I am using version 2000. There's some glitch where everytime the PC gets rebooted, the default file ... A: I haven't been able to find an answer to this, but wonder if the issue MAY be related to the folder ... | |
| Mouse over pop-up | 9/2/2009 |
| Q: Because of your good support last time I want to use your knowledge again. I want a mouse-over ... A: There isn't a mouseover event for the cell, but you can get one to work by inserting a control ... | |
| Adding rows and automatic merging | 9/2/2009 |
| Q: I need to add a series of notes referring to the same row on a spreadsheet. I am doing this by ... A: I'm not sure I follow this, especially as you say you want to sort and filter - having a note in a ... | |
| EXCELL TIME FORMAT | 9/2/2009 |
| Q: i need help converting this times (11:24:00a) (12:37:00p) into regular excell format & military ... A: not sure if the brackets are part of this but I'll assume they are - probably the easiest method is ... | |
| Find and replace by part of value | 9/2/2009 |
| Q: Heritage, I´m currently trying to manipulate some of the codes I work with at the moment, but can´t ... A: Not clear what the rules are here, but do you have a table of old and new? If so, use a blank ... | |
| comparing two worksheets and merging when matches are found | 9/2/2009 |
| Q: I'll try to be as coherent as possible in asking thisI have two worksheets each containing different ... A: The COUNTIF worksheet function would enable you to determine if one value exists in another ... | |
| Macro | 9/1/2009 |
| Q: I have the following macro in an excel file: Sub MoveRows() Dim rngOrigin As Range, rngDest As ... A: It's easier to put data at the bottom of a worksheet, but it's not impossible to do it the other way ... | |
| frequency table | 9/1/2009 |
| Q: i am given data and i want to create a frequency table & histogram using excel? how do i create a ... A: This answer comes from the help file Show All FREQUENCY See Also Calculates how often values ... | |
| Conditionally Delete First Characters | 9/1/2009 |
| Q: I have an Excel 2007 sheet with many entries. The first 2 columns are "Order Number" and "Part ... A: I would PROBABLY do this with vba - a sample of that is here ... | |
| difference between dates Excel 07 | 9/1/2009 |
| Q: how do I return many whole calendar months and remaining days between dates in excel 2007? eg: from ... A: I'd tend to use YEARFRAC to handle this - this returns a decimal value indicating the exact fraction ... | |
| Changing Chart Based on Value Selected | 8/30/2009 |
| Q: Based on that selection, I want a chart to change. On Sheet1, I have the list with the dates to ... A: You can do it with indirect, but you would EITHER need to use VBA to reset the chart range OR have a ... | |
| Workday if function | 8/27/2009 |
| Q: I'm trying to create a table showing due dates as the last day of month, however, if end of month is ... A: I would suggest the easiest way is to test your end of month date against the holiday list ... | |
| Use VBA to copy sheets between workbooks - "Subscript out of range" | 8/27/2009 |
| Q: I am attempting to do a similar thing to another post on here (dated 01/04/08). I have attempted ... A: Set xlw_source = Excel.Workbooks(ResultSheet) is basically fine, SO the contents of ResultSheet ... | |
| Trouble with IF statement | 8/27/2009 |
| Q: I need some assistance with my formulas. I'm working on a spreadsheet that has 2 columns of data. ... A: I don't know what the IF statement is doing, but if a macro is generating results, it should be easy ... | |
| Charts | 8/26/2009 |
| Q: I have a sales sheet with data for a single store..I would like to copy this data on multiple pages ... A: Sorry, I took the reference to pages and sheets to refer to worksheets for pages and workbooks for ... | |
| Excel - coding & matching cells | 8/26/2009 |
| Q: I've prepared a budget spreadsheet which has 3 columns: date, description, amount. I would like to ... A: It makes sense, and I can think of a few ways to do it - IF you are after just an overall total, ... | |
| Accessing a closed workbook using Concatenate and EVAL | 8/26/2009 |
| Q: I'm working with Excel 2007 and have been muddling around with this problem for a while searching ... A: I don't think it's VBA so much as excel - it sounds as though you may be using an indirect worksheet ... | |
| Search Across Worksheets | 8/26/2009 |
| Q: I have a workbook with 60+ worksheets. I want to allow users to type in a keyword to search for ... A: It's certainly possible, though I'd need slightly more information to give you a working macro - are ... | |
| Deleting drawing object using VBA | 8/26/2009 |
| Q: Using VBA, how can I delete a drawing object (box with macro assigned to it) from a worksheet? A: I'm not sure I'd want to do this with VBA, but ActiveSheet.Shapes("Rectangle 1").Delete where ... | |
| Append Worksheet to Summary Sheet | 8/26/2009 |
| Q: I have 3 worksheets: "Data A" "Data B" and "Combined Data" I need to make a macro that puts the ... A: If this is a one off, I'd do it manually, if it's an ongoing process then presumably data may ... | |
| StartBlink | 8/26/2009 |
| Q: I have captured StartBlink Macro one of your Answers,I have implemented in my Excel2003 application ... A: If you looked at the answer I think you are referring to ... | |
| Copy Cell Formatting Using Vlookup | 8/26/2009 |
| Q: I suspect that this question may be a little complicated. I have a master timetable for all staff ... A: I presume the colour information is more complicated than would be allowed by conditional ... | |
| Help on Macro for copying data from one sheet to another | 8/26/2009 |
| Q: I want help on how to copy data from one sheet to another assuming Sheet 1 is having fixed range to ... A: Range("A1:E13").Copy 'amend to be the range you want Sheets("Sheet2").Select 'amend the sheet name ... | |
| Excel Macro? | 8/25/2009 |
| Q: I'm looking for a macro that will allow a section of a spreadsheet to be hidden. This will be ... A: use the control toolbar, insert a checkbox, right click it to view code and enter SOMETHING like ... | |
| Combinations | 8/25/2009 |
| Q: I am pretty new to excel. I am working on credit card audits for a real estate development company ... A: It is possible, and it's a fairly common question - so rather than re-inventing the wheel, I'll ... | |
| vba conditionally delete rows | 8/25/2009 |
| Q: ColB, beginning in row 2, contains absolute cell refs (e.g. $A$50). I need VB to look in colB down ... A: Set currentCell = Range("B2") Do While Not IsEmpty(currentCell) Set nextCell = ... | |
| Vlookup Formul | 8/25/2009 |
| Q: I have a workbook that I created that will import information that will have to be snswered by ... A: I'm not 100% sure I have enough information here - how is the data to be imported? Is it being ... | |
| Date & Time Difference | 8/25/2009 |
| Q: I have data in column A contains Date and Time of incidents which took place in our company IT ... A: I'm not sure about your maths - 6pm to 1pm is 19 hours - but apart from that the basic method should ... | |
| Excel . Help | 8/25/2009 |
| Q: Let’s say you have a typical Microsoft excel sheet and on the bottom you have standard table. You ... A: The INDEX worksheet function would probably be the way to go with the definition you have given. ... | |
| conditional formatting | 8/25/2009 |
| Q: I would like to ask a question related to Excel function with conditional formatting. In column A, I ... A: The problem as I see it is that your duplicated rows could presumably occupy both an odd number or ... | |
| stuck on excel formula :( | 8/24/2009 |
| Q: I have an excel spreadsheet with 2 different worksheets. On one worksheet I am trying to pull data ... A: If I read this right, it's not Vlookup, but HLookup that you want - that would look ACROSS the data ... | |
| Date & Time Difference | 8/24/2009 |
| Q: I have data in column A contains Date and Time of incidents which took place in our company IT ... A: You didn't specify which version of excel, but basically the NetWorkDays function will give you what ... | |
| TRIM function problem | 8/12/2009 |
| Q: I am then using EXACT to compare two different colums for text difference. I am getting a false ... A: If I read this correctly, it's simply a question of paste special, values - but possibly doing a ... | |
| Copying Data from sheet to sheet based on condition | 8/12/2009 |
| Q: CODE: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngPasteTo As Range If ... A: Your code is wanting a single cell to be selected, but when copying and pasting multiple cells are ... | |
| Formula for add row | 8/12/2009 |
| Q: Is it possible to add a row with an "if" formula? If a1&a2 are equal then insert a row below? ... A: Not directly - you can design your sheet in such a way that data appears on a blank line only if ... | |
| Excel to Access, Access to Excel | 8/12/2009 |
| Q: I am new with Access, but i have enought experience with Excel. I am responsible for the following ... A: It's certainly possible, and fairly easy, to write data to an access database - and indeed to pull ... | |
| excel 2000 | 8/12/2009 |
| Q: I have several workbooks with several worksheets, is there a way to create a "list" of each ... A: Not sure why you need to do this (right clicking the selection bar would show you all the sheets for ... | |
| Mail/Hyperlinks | 8/12/2009 |
| Q: I have a bunch of cells in a spreadsheet that are each hyperlinked to one or more email addresses. ... A: two options - one is via a macro, the other uses word to do a mail merge - see ... | |
| Excel Formula Question | 8/12/2009 |
| Q: I am attempting to set up a multi-site timesheet. What i need to do is set a formula that would ... A: I'm not sure I've got enough information to go on here but I'll do my best hours worked is a simple ... | |
| excel | 8/12/2009 |
| Q: I am asking this question after reading same question in your blog I know you can give me proper ... A: I'm not clear exactly what you want to do here - are you copying a file from a given location and ... | |
| 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: The basic macro is simple enough, BUT I cannot follow the logic - mainly I suspect due to the layout ... | |
| complex formula | 8/11/2009 |
| Q: How do I create an complex formula displaying an "DECREASE" ? A: Would you like to be a little more specific please - I have nothing here to base an answer on other ... | |
| Averaging data | 8/11/2009 |
| Q: I have data for every 15 minutes give or take a missed reading. I am trying to make these into 1 ... A: You didn't specify why this had to be done in VB - I'd do it with a pivot table as that would do the ... | |
| Offset function to multiple rows | 8/11/2009 |
| Q: I've been trying to get my head around the OFFSET function so that I can put one formula in C3 and ... A: You didn't tell me what the formula does, so I'm not sure if offset is actually the way to go, but ... | |
| Shading Cells in Exccel | 8/11/2009 |
| Q: We have a transportation log that was made in Microsoft Excel. It is supposed to be setup so that if ... A: You didn't specify version but as I'm sure it's at least excel 97 that shouldn't matter too much - ... | |
| Excel countif formula | 8/10/2009 |
| Q: I've been trying to figure out a formula that counts x, y, and z that are a, b, and c separately. ... A: possibly a pivot table could be the easiest solution as this will allow you to put x,y,z as (say) ... | |
| Help w Excel combining duplicate rows and add | 8/10/2009 |
| Q: I am a total Newbie with excel. i hope the question I asked can be answered in a way my meager mind ... A: The EASIEST way is going to be a pivot table - what you didn't tell me was which version of Excel ... | |
| formula/function for payroll | 8/10/2009 |
| Q: i have created a formula that calculates our pension contributions based on 5% of our employees ... A: no problem- the MAX function will do it for you =max(1000,yourFormula) NOTE also that I'd use the ... | |
| On-screen File Loading Animations | 8/9/2009 |
| Q: I have a large Excel file that I share with others. I would like to program in VB a simple loading ... A: An out of memory message would indicate to me that either some recursion is happening, or objects ... | |
| Look up formulas | 8/5/2009 |
| Q: I have a list of clients numbers on one worksheet that I would like to send a letter to and on the ... A: you need to use absolute references here – A2 is a relative reference, in that as the formula moves ... | |
| Excel 2007 Drop Down List with images | 8/5/2009 |
| Q: In Excel 2007, I would like to have a drop down list for a user to select one of three images/icons ... A: I like the idea, but I'm not sure how easy it's going to be - data validation will allow the use of ... | |
| Custom menus in 2007 | 8/5/2009 |
| Q: In Excel 2003, I have used the XL4 macro language to create a custom menu with four commands. One is ... A: I’m not sure what you mean by flyout menu, and I’m slightly worried by the use of XL4 language, ... | |
| How to Make duplicated column data into a Row using Excel VBA? | 8/5/2009 |
| Q: The Following data need to <COUNTED> upon Condition using VBA like first Column has Duplication of ... A: Your explanation is a little hard to follow, and I’m not 100% sure why this needs to be done via ... | |
| How to Count the unique cells from a set of Duplicates , using VBA ? | 8/5/2009 |
| Q: The Following data need to <COUNTED> upon Condition using VBA like first Column has Duplication of ... A: Your explanation is a little hard to follow, and I’m not 100% sure why this needs to be done via ... | |
| Find Nett available quantity from a list of transactions | 8/5/2009 |
| Q: I have a problem which I am not able to solve using Excel (being new to excel programming). My ... A: Presumably it's a known list of products, so I'd build this list first, and then use a SUMIF ... | |
| is 8000 a limit for the Solver? | 8/5/2009 |
| Q: In our company we try to solve a task of optimization that requires matrix of about 20 colomns and ... A: Your question relates to commercial software that is not part of excel so I'm unable to answer the ... | |
| Code for Upper Case in excel | 8/4/2009 |
| Q: Can you provide me with the code to enter in view code to change the entire column to upper case, ... A: dim cell for each cell in range("A:A") if len(cell.value)>0 then cell.value=ucase(cell.value) next ... | |
| More than 7 Nested IF Statements. | 8/4/2009 |
| Q: =IF(D2<=100,"1-100",IF(D2<=200,"101-200",IF(D2<=300,"201-300",IF(D2<=400,"301-400",IF(D2<=500,"401-5 ... A: You cannot nest more than 7 levels of if statements, but there are often other ways round this - in ... | |
| Excel to Word | 8/4/2009 |
| Q: We have addresses stored in an excel file and would like to group each horizontal entry into ... A: Go to word and choose the mail merge option - generate labels based on your data and drop the data ... | |
| cutting and pasting | 8/4/2009 |
| Q: Hey, My question involves cutting and pasting a list into a different column.. Say I have a list in ... A: Any form of hot key would probably take longer to do than using the mouse - right click on the ... | |
| How to calculate ratio in excel? | 8/4/2009 |
| Q: How can I calculate ratio for two values using a formula? Thanks A: not sure what you mean by GDP feature, but I assume by ratio you mean one value divided by the other ... | |
| nested sumif? | 8/4/2009 |
| Q: I need to sum C if A=B, and then in a seprate formula I need to sum C if B is a certain percentage ... A: Not clear what you mean by seperate formula, but for your sumif try ... | |
| Page number refencing | 8/4/2009 |
| Q: Is there any function or cell referencing method to refer to page number? I don't want help on ... A: Page numbers in excel don't actually exist - they do on printed output, but on the sheet itself it ... | |
| Importing data from a specific date onwards to a new file | 8/3/2009 |
| Q: I have an excel file named "experiment" with multiple columns out of which one column is named ... A: Would the date column always be in a given location, or would the macro have to work out which ... | |
| Copying data speci | 8/3/2009 |
| Q: I have an excel file named "experiment" with multiple columns out of which one column is named ... A: Would the date column always be in a given location, or would the macro have to work out which ... | |
| Date Range match | 8/3/2009 |
| Q: In a previous spreadsheet I have used the below formula to look for certain statement and list the ... A: a date in July 2009 is ACTUALLY a 5 digit number in the 40,000 range from memory- as your dates are ... | |
| To Copy File And Folder Names | 8/3/2009 |
| Q: I have a sheet which shall contain File names in column B and Folder names in column C. The files ... A: I cannot get the code as copied to run, which makes it difficult, but as far as I can tell the ... | |
| To Paste Jpg Images form Folder | 8/3/2009 |
| Q: I have the following code which is working wonderful. It searches for the file name which is written ... A: The Application.FileSearch has gone in office 2007 - though heaven knows why - this code With ... | |
| using excel to download files from sharepoint/ websites | 8/1/2009 |
| Q: Can we use excel to download files from the internet? Like, in my spreadsheet i will put in the ... A: downloading files is the same as opening them, so the VB command to open a file would be the way to ... | |
| To copy file and folder names | 8/1/2009 |
| Q: I have a sheet which shall contain File names in column B and Folder names in column C. The files ... A: Probably not the code I'd use, but Sub GetFileNames() Dim xRow As Long Dim xDirect$, ... | |
| Column headings | 7/31/2009 |
| Q: How do I always show the column headings as I am scrolling down a sheet quite far? Thank you A: window, split, then window, freeze panes - before doing the split, but the cursor one row below and ... | |
| Copying multiple worksheets to one master | 7/30/2009 |
| Q: Please help me to be able to copy text and numbers from multiple worksheets and stack them into a ... A: Manually, just open the sheets, copy the data, find the next blank row and paste. Via a macro it's ... | |
| Move columns from one sheet to another | 7/30/2009 |
| Q: I am trying to move say 5 columns (not contiguous) out of 15 from Sheet1 based on column headings ... A: manually, this can be done by selecting the columns as a block - just hold down ctrl, and then ... | |
| EXCEL | 7/30/2009 |
| Q: In MS EXCEL I would like to split the screen into an upper and lower section. I've done this, now ... A: I don't follow - it seems like you want to alter the one document, but have it reflect changes on ... | |
| Excel Formula | 7/29/2009 |
| Q: I am having difficulty organizing a formula or formula’s to assist in the determination of specific ... A: It's basically and OR statemnet that is required (I think) but I'm not clear what the x point ... | |
| workday problem | 7/29/2009 |
| Q: I am trying to figure out how to make a formula work. I would like to be able to enter one date into ... A: You need to add-in the analysis toolpak (tools, add-ins, then check it - as long as it's not office ... | |
| How Does Excel Recognize the Last Cell? | 7/29/2009 |
| Q: I daily download at least 10,000 lines of data and perform some calculations. This task definitely ... A: My cell.value uses the word cell as a variable, but basically would work fine - try something like ... | |
| How Does Excel Recognize the Last Cell? | 7/29/2009 |
| Q: I daily download at least 10,000 lines of data and perform some calculations. This task definitely ... A: What I don't know is what you macro is instructed to do, but there are lots of ways of handling it ... | |
| Cloest to average | 7/29/2009 |
| Q: I am conducting a promotion where the winner is closet to the average. There will be 26 numbers ... A: I'd use the RANK worksheet function - this ranks a given set of values in ascending or descending ... | |
| Excel sigma operation | 7/29/2009 |
| Q: How can I use sigma with boundaries in excel 2007? For example I want to calculate this value: ... A: I'm afraid my maths isn't up to it, I don't understand the concept or the alegebra used here - the ... | |
| Macro to create sheet with formatting. | 7/29/2009 |
| Q: In one Excel 2007 workbook, I have a sheet that I would like to re-create in a separate file each ... A: There isn't really such a command - although you can copy the shee to a new book, that doesn't do ... | |
| updating expenditure figures every month | 7/29/2009 |
| Q: I used to prepare monthly progress report where i have to delete the figures under monthly column ... A: You will basically need to use a macro to do what you want - it's going to need to set the ... | |
| Count shaded cells in a column | 7/29/2009 |
| Q: I'm looking to create a macro on Excel, where i click on a button and am prompted to enter the ... A: this macro should do it for you Sub countofshade() Dim cell, counter, check check = InputBox("Which ... | |
| Excel Auto Sort Filter | 7/28/2009 |
| Q: I have a master spreadsheet with different job information on it and I'm trying to write a macro or ... A: I'm not sure I would necessarily do this as data is added, but basically IF doing it then, it would ... | |
| On-screen File Loading Animations | 7/28/2009 |
| Q: I have a large Excel file that I share with others. I would like to program in VB a simple loading ... A: Personally, I wouldn't do this - I would put up a text box that tells them to wait - takes no ... | |
| Auto Fill Cells | 7/28/2009 |
| Q: I am trying to generate a form. One function I would like the form to do is when the user enters in ... A: This would need a macro to accomplish it - I have not got enough detail here to give you the ... | |
| Selecting data | 7/28/2009 |
| Q: This is a pretty simple question but... I'm stuck! I need to know how to select the last five cells ... A: As it's going to be for a graph, I think I'd use an array to pick up the data in a seperate graphing ... | |
| Formula to identify Mondays and add up values for week | 7/28/2009 |
| Q: I am working on our production spread sheets and am required to find out the total goods produced ... A: I would do this by creating a helper column which has the formula =a1-weekday(a1)+2 this should ... | |
| Excel Formula | 7/28/2009 |
| Q: I am looking for some assistance with regards excel formulas, I want to be able to have a real time ... A: I presume there is a list of targeted sales per day, or a formula that is used to work them out? If ... | |
| Adding Percentage Data Labels in Excel Bar Graphs | 7/28/2009 |
| Q: I am using Excel 97. I have plotted a graph and in step 3 of 5 of the Graph Wizard, I was given the ... A: The Percentage and Bubble size are settings for specific types of chart, they aren't for the data ... | |
| Excel multiple nesting IF statements | 7/27/2009 |
| Q: Good Day, I am struggling with the correct syntax to use for the following formula: IF B12 equals ... A: The formula based on what you've told me is something like ... | |
| Multiple Auto Insert Date | 7/27/2009 |
| Q: I have been following the directions by Richard Roberts in an article on this site titled "Auto ... A: As I mentioned, the macro should set a VALUE not the formula - again, I don't know what hte original ... | |
| Multiple Auto Insert Date | 7/27/2009 |
| Q: I have been following the directions by Richard Roberts in an article on this site titled "Auto ... A: ctrl ; is the keyboard shortcut to insert the current date into a cell, so this may be the way to go ... | |
| VBA Transposing a specific format | 7/27/2009 |
| Q: I am having a problem automating a format from one form to another. Basically what I want to is ... A: Pictures via this system are quite hard to for me (I think it's my eyesight rather than the system) ... | |
| Excel Button Control | 7/27/2009 |
| Q: I'm a gaming geek. We use a system to determine who gets items after an event in the game. Every ... A: What I don't know is what method you are using for the attending checkboxes - are they linked to ... | |
| excel database mgnt | 7/27/2009 |
| Q: I have been trying to make a small data management worksheet in excel. But can't seem to get ... A: I'd probably do this in Access, but excel sould be fine - I think the formula you need for stock is ... | |
| MS Excel 2007 Linkage Question | 7/27/2009 |
| Q: The scenario is that File A links to File B; and File B links to File C. Is there a way where a ... A: Without the dependant files being open, the data wouldn't update, so the only sure way to have the ... | |
| Macro: Deleting Rows with Certain Text | 7/27/2009 |
| Q: I have an Excel 2007 spreadsheet with many cells. Column B has many different entries, and I am ... A: much easier to do if left(YourCell,2)="RW" then 'delete it by whatever means you are using end if ... | |
| Email from spreadsheet using Lotus Notes | 7/27/2009 |
| Q: I was hoping you would be able to help me? I have a database which tracks the progress on working ... A: I've done this myself at work and do have the code for it there, but of course I'm at home at the ... | |
| Can I have a VLookup Calculated Field in a Pivot Table ? | 7/27/2009 |
| Q: I have a pivot table which runs of data in an Access database (which is made up of a lot of ... A: I'd do this by EITHER determining the maximum width and inserting my formula AFTER this width, or ... | |
| 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: I'm not aware of an existing function - it would probably be possible to write a macro to break down ... | |
| Max IF | 7/27/2009 |
| Q: I want to show the highest average in a column of averages, for a minimum amount of the total in a ... A: It should still work, but you need to amend my IF statement - currently I'm using an impossibly ... | |
| links? | 7/26/2009 |
| Q: I have two quick questions about hyperlinks in Excel. First, if I want to copy a cell from another ... A: mailing multiple people would best be done via mailmerge, but you could use macros - see ron ... | |
| Automatically insert data to the right place in another worksheet | 7/26/2009 |
| Q: I have this problem that I can´t get working. I need to get data from let´s say cell B2(sheet 1) ... A: The code you need is I think along these lines Sub mecheck() Dim RowVar, ColumnVar As Long Select ... | |
| Reg: Help with Macros - VBA | 7/25/2009 |
| Q: I need help with writing a Macro for the below scenario. If the value in the Cell A1 is "Band 4" ... A: if range("a1").value="Band 4" and range("B1").value="PSCS" then range("C1").value="E,E,A,C,DB,P&C" ... | |
| IF commands on excel | 7/24/2009 |
| Q: I’m trying to use the if function on excel to do the following. I bought an item in us dollars, but ... A: An IF statement works like this =if(ConditionBeingTested=True,ThingToDoIfTrue,ThingToDoIfFalse) ... | |
| excel | 7/24/2009 |
| Q: I would be very grateful if you can give me any help. EX: I have a sheet where are written 6 ... A: if the numbers are in one column, then a simple countif would do it for you - this would return ... | |
| excel drop down menus | 7/24/2009 |
| Q: Just wondering. I am creating a spreadsheet with drop downs but I wanted to see if there is a way ... A: A drop down list is usually a data validation list, so wouldn't allow anything else to be entered, ... | |
| Copy/Paste Macro | 7/24/2009 |
| Q: I am having trouble sorting and pasting this specific data. If you look at the screenshot, I'm ... A: I rarely if ever use copy and paste - I'm not 100% sure what the destination for these is - do they ... | |
| VBA - creating new rows | 7/24/2009 |
| Q: I was wondering if you can help me on some VBA code. I have a data that gets uploaded every so often ... A: I'd use a COUNTIF function to determine if the entry exists, and ... | |
| Input boxes and using input in a FOR statement | 7/24/2009 |
| Q: I am trying write some VBA script to take a list of people and sort into multiple coach lists. I ... A: You are asking the user to enter a range by using type 8, so an input such as A10 would be fine, but ... | |
| Max IF | 7/24/2009 |
| Q: I want to show the highest average in a column of averages, for a minimum amount of the total in a ... A: I'm not sure I follow the question! However, if it is simply a maximum based on a restricted range, ... | |
| to highlighted related cells | 7/24/2009 |
| Q: I have a worksheet for managing all documentation at our place. I was trying to come up with a macro ... A: I think this could be done with VBA though it might be a little slow - would the range of cells be a ... | |
| Conditional Formatting | 7/23/2009 |
| Q: really need some help with the following: I have created a spreadsheet to enable me to know when i ... A: If I follow this correctly, you have working conditional formatting, but you need to control the ... | |
| Change Event for Data Validation | 7/23/2009 |
| Q: I asked you a question previously and you were very helpful. Perhaps I can get your input again? I ... A: Glad you got it working - I tend to assume with my validation that people won't change their minds, ... | |
| Excel and Word question | 7/23/2009 |
| Q: Damon, had answered the following - Import MS word to Excel 2003 question. It works great to put ... A: You didn't give me the details of what Damon had provided, but it should be very easy to get Word to ... | |
| Excel Help | 7/23/2009 |
| Q: Greetings! I was wondering if you could help me with an excel computation problem I am having. ... A: Two thoughts - the quickest to get ALL values would be to use a pivot table which would let you get ... | |
| Summing a set of numbers generated from a function utilizing a "TODAY" to get the answer | 7/23/2009 |
| Q: I have a table where column a lists employees, column b lists their start date and column c lists ... A: As you already have the service period, I'd do a multiple condition countif - which you can do with ... | |
| formula/macro | 7/23/2009 |
| Q: I have a Excel file that has many different sheets. The main sheet contains a macro that copies info ... A: As I don't know what your code does, I'm going to GUESS that it does exactly what you've said - ... | |
| create a sheet that shows the balance owed dropping per payment | 7/23/2009 |
| Q: i would like to create a spreadsheet that I enter the beginning balance in cell A2 and then in A3 ... A: I think I'm missing something in this question - if A4 needs to show the beginning balance minus one ... | |
| merging 2 excel spreadsheets | 7/23/2009 |
| Q: I am trying to merge 2 excel workbooks where both workbooks have a 5 letter hotel code in the first ... A: you need to combine a countif and a vlookup ... | |
| Macro Issue | 7/23/2009 |
| Q: I have a macro that takes information from sheet 1 and copies it onto sheet 2. This works well with ... A: No files yet Nik? I'm still not clear what the week numbers are going to be - are they week numbers ... | |
| #VALUE | 7/22/2009 |
| Q: I used a formula: =IF(OR(A2="",D2"")," ",IF(ISERROR(VLOOKUP(A2,catalogue data!a2:c12,3,0))," ... A: with the exception of missing commas in the iserror part of the formula, which I take it is a typing ... | |
| Merging Excel Worksheets | 7/22/2009 |
| Q: I've recently been given a business issue in which our archiving team have created 600 Excel 2002 ... A: I think perhaps your tech support people were having a bad day? The process that they outline is ... | |
| Dynamic Vlookup | 7/22/2009 |
| Q: I understand this may be a really tough question to ask so if you cant answer it I understand... ... A: Ouch - I think the process is simple enough, just rather long in terms of sheets - are these all ... | |
| Excel, text color when between 2 values | 7/22/2009 |
| Q: I am working with an Excel spreadsheet for a maintenance aircraft oil change every 25 hours. My ... A: You didn't specify the version of excel, but it is basically going to be done via the format, ... | |
| excel cross work book look up | 7/22/2009 |
| Q: I have 2 spread sheets in different work books. one is a list of names in column A with a list of ... A: The basic formula is vlookup - the only issues I can see are (1) you have potentially a growing list ... | |
| Copy Macro in Excel | 7/22/2009 |
| Q: I hope you can help me with my Problem... Here is the scenario... I have data in 2 columns, lets ... A: What you don't tell me is what the other application is - does it expose itself to the programming ... | |
| XML to Excel Automate process | 7/22/2009 |
| Q: I have XML files stored in a directory "C:Documents and Settings chaharDesktopHRDM_R2HRDM_XML" I ... A: You can record the process of dealing with one file - you then need to set it to handle all files in ... | |
| Autofiltering Protected Worksheet | 7/22/2009 |
| Q: Aiden - I have a macro that is called by the Workbook_Open event to Protect specified worksheets in ... A: Application.CalculationVersion would return the version of excel (Returns a number whose rightmost ... | |
| Dependent drop down boxes | 7/22/2009 |
| Q: I have my spreadsheet set up with a few different drop down lists (validation). My problem is my ... A: I would personally make it impossible to do it this way round - although it could still be broken if ... | |
| Unhide Worksheet | 7/22/2009 |
| Q: I have a series of worksheets and some are hidden. However now I cannot Unhide them. I've tried ... A: YOu haven't specified what you have tried - seeing the worksheet might help, but I'd PROBABLY do ... | |
| Help on VLOOKUP & IF with Dates | 7/21/2009 |
| Q: Hey everyone! I have 2 Tabs. 1) Furnaces --> INPUT 2) Report --> OUTPUT 1) I have this speadsheet ... A: Pictures are hard for me to follow, but I'm happy to look at an example sheet - my email being ... | |
| Macro Issue | 7/21/2009 |
| Q: I have a macro that takes information from sheet 1 and copies it onto sheet 2. This works well with ... A: I'm not sure I've got enough to go on - you are moving to week numbers - but are these to be in ... | |
| Excel | 7/21/2009 |
| Q: I have a two table, each with 2 column and 5 rows. In each table there are different text in 5 rows ... A: This would need, I think, VBA to achieve it - however, I'm aware that often the use of macros is ... | |
| 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: Actually, this should be fairly straightforward - a COUNTIF function would return the number of ... | |
| Multiple question sets from word to excel | 7/21/2009 |
| Q: Sir, I have three different word files on different topics with 30 questions in each file. I tried ... A: Sorry, I'm not sure I can see any logic in the process (in terms of how to write a program, which ... | |
| Keep array as a value in a cell | 7/21/2009 |
| Q: Hope you would be so kind as to help out with this question. I'm using the following array formula, ... A: Thats one heck of a formula - but did you check the WORKDAY function that's already built into excel ... | |
| Excel Opening as eMail | 7/21/2009 |
| Q: I have a spreadsheet that I have been using for many years that has suddenly started opening as an ... A: seems odd if it is only one file, but try right clicking it and then choosing the open with option - ... | |
| Timesheet | 7/20/2009 |
| Q: I have allow 45mins at start and finish of day,require formula with this please leave home at 7:00am ... A: I'm not sure I follow what you need to record - time is simply (to excel) a decimal fraction, with ... | |
| macro for identifying the valid mail address | 7/20/2009 |
| Q: i have the excel file. on those one of column contains mail id i want to check whether the enter ... A: I'm not sure how you would want to validate - you can certainly check for the existence of an @ ... | |
| individual cell data from a master worksheet to many other worksheets | 7/19/2009 |
| Q: i want to transfer data from master worksheet to different sheets .if i type 7 in cell d5 of master ... A: I think you will need vba to achieve it, it would need to run on a change event of the worksheet - ... | |
| individual cell data from a master worksheet to many other worksheets | 7/18/2009 |
| Q: i want to transfer data from master worksheet to different sheets .if i type 7 in cell d5 of master ... A: IF this is all you need do then the INDIRECT worksheet function should work for you ... | |
| MS-Excel Array Formulas - Count excluding nulls | 7/17/2009 |
| Q: I am writing an array formula that sums figures based on two criteria. However, I want it to ... A: You didn't specify what your formula was, but I'd use something like ... | |
| Excel for survey reporting....?? | 7/17/2009 |
| Q: I have designed a survey for my employer, and now i am trying to track the results using excel.... i ... A: From the description, I think the easiest thing would be a pivot table - data menu, pivot table and ... | |
| Filtering data excel 2007 | 7/17/2009 |
| Q: I have a spread sheet that is 20 columns across and 20 rows down. Each cell contains a persons name ... A: Tricky for it to be totally automatic - possibly the quickest and easiest way is to have a countif ... | |
| VBA lines delete | 7/17/2009 |
| Q: i would like to write a macro that checks a cell in a row and if it = #NA delete the entire row and ... A: Sub aidan20() Set currentCell = Worksheets("Sheet1").Range("A1") 'this will handle column A - I'm ... | |
| Excel Countif Formula | 7/17/2009 |
| Q: I am using a formula to count participants in a program problem I have is my formula covers 2 ... A: sounds like a multiple condition countif is required, BUT the normal method won't work as you have ... | |
| Transfering a table from excel to word | 7/17/2009 |
| Q: I have done some work for someone as an excel sheet, with page totals at the top and bottom of each ... A: OUCH! Excel is good at sums, but not good (especially) at fixed layouts - but I gues it's PROBABLY ... | |
| Dynamic Sort in Excel 2003 | 7/17/2009 |
| Q: Sheet 1 contains values in columns A, B, C, D. The rows can range from 1 to 344. I would like to ... A: http://rapidshare.com/files/258062627/RankAndSort.xls.html contains a file I've created for you ... | |
| Drag While Skipping Rows | 7/16/2009 |
| Q: I have lots of information that is in typical excel formatting that I want put in custom forms. In ... A: I could easily write a macro, but it seems unnecessary - filter on the headings, drag down, then ... | |
| Drag While Skipping Rows | 7/16/2009 |
| Q: I have lots of information that is in typical excel formatting that I want put in custom forms. In ... A: If you have some information already in the sheet the you could filter on this - thus giving you NO ... | |
| Excel 2007 VBA Userform | 7/15/2009 |
| Q: I have looked at other answers you have provided however I am unable to use them on my exact ... A: Not sure why this is done via userform, and not simply entered into the spreadsheet, with the ... | |
| reg VBA code | 7/14/2009 |
| Q: I need to create a button in an excel sheet saying "Please click here to schedule". When someone ... A: Not sure how much help you need - Alt F11 will get you into the VBA editor, here you can design the ... | |
| Determine data range | 7/14/2009 |
| Q: I have got list of values like £17,000.00 £7500.00 £3500.00 £7100.00 £0.00 When I try the formula ... A: The formula you have given should work fine =COUNTIF(A1:A5,"<=33000")-COUNTIF(A1:A5,"<4300") is ... | |
| Excel Chart | 7/13/2009 |
| Q: I work with chart on excel and I can't figure out how to convert a horizontal chart (with X = depth, ... A: switching it would change the series around, so it's not the fix I thought it was - did you take a ... | |
| Using 2 criterias! | 7/13/2009 |
| Q: Good Morning Mr. Smith! I have a project I am working on and I need some help with a formula. I was ... A: Two ways of doing this - one would be via a pivot table which would enable you to automatically get ... | |
| Vlookup Error | 7/13/2009 |
| Q: I have a table for eg See the Link http://www.zshare.net/download/625960325302de48/ In Sheet " ... A: You need to find a way of getting the first, second, third etc entry- so I'm HOPING that a custom ... | |
| Auto populate maltiple cells in excel based on drop down selection | 7/10/2009 |
| Q: I have a spreadsheet I would like cells to auto populate three cells with prices based on the ... A: use a vlookup formula - I'm assuming that you have a table of engineer data - this would need to ... | |
| Conditional Formatting & Text Value Assignments | 7/9/2009 |
| Q: 1. Regarding the conditional formatting I was using below, using the same rules, is it possible to ... A: 1 - as you have excel 2007, you can do this - you'd need to use a formula to set it, something like ... | |
| 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: with difficulty- the indirect worksheet function could do it, but it needs the source file open - so ... | |
| qu | 7/9/2009 |
| Q: someone showed me how to call up a dialogue box which showed you what columns were hidden in a ... A: I'm not aware of the dialogue box you are referring to, but it may just be the definition that is ... | |
| is it possible to grab data depending on a colums value? | 7/9/2009 |
| Q: I have a table which has: - Name - Gender - Achieved December - Achieved April - Achieved July ... A: A pivot table would be the easiest way to go so that you can analyze the data just by dropping it ... | |
| Splitting text in 1 cell into 2 cells | 7/8/2009 |
| Q: I am using Excel 2004 on a Mac os 10.4 computer. I have a list of 1300 names and address, each ... A: What I don't know is how the data is formatted - what seperates the name from the address? If for ... | |
| comment boxes disapearing in excel | 7/8/2009 |
| Q: I am working on a very large spreadsheets with a large number of comment boxes in Excel 97. The ... A: comments are generally a nuisance - they probably won't have disappeared, but they will have drifted ... | |
| To Update Master Record with Yearly Record | 7/8/2009 |
| Q: I have a workbook "Master Record" which contains data of employees with column headings. This data ... A: The line as it stands is fine, but I've spotted that I mistyped the line above For looper = ... | |
| Sampling | 7/7/2009 |
| Q: first of all i would like to thank you again for your advice last time (few months ago). I would ... A: I'm afraid that statistics isn't really an area I'm strong in - so I'm going to give you a google ... | |
| To Update Master Record with Yearly Record | 7/7/2009 |
| Q: I have a workbook "Master Record" which contains data of employees with column headings. This data ... A: What I didnt make clear I think was that Ive assumed there is a sheet called SUMMARY which on cell ... | |
| line chart does not start at 0 | 7/6/2009 |
| Q: I have some values for a line chart and let's say the value for the first day is 12. Is there some ... A: Are you talking here about the scale - if so, thats easy, as you just right click it and set the ... | |
| 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'm not a golfer, but I do understand the question! ... | |
| VLOOKUP formulas | 7/6/2009 |
| Q: I have a spreadsheet with data by date, then by employee name (see attached image) I want to be able ... A: Images dont come out very clearly - so Im happy to see an original file - my email being ... | |
| Auto List by color | 7/6/2009 |
| Q: I have a worksheet (A1:FN75)where I've developed a basic process map, made up of several cells with ... A: These two custom functions will help Public Function WhatCol(cell As Range) As Long WhatCol = ... | |
| Range problem for graph | 7/6/2009 |
| Q: I need to hide columns using a macro where the columns to be hidden are in a range. Say A:1 = 10 and ... A: You need to use the INDIRECT worksheet function ... | |
| If this then that | 7/5/2009 |
| Q: My question is as follows: I have a language school and would like to organize the data of ... A: Various ways of doing it, but as you mentioned IF we'll go that way ... | |
| Confusing Excel Formula | 7/3/2009 |
| Q: I need an Excel formula which will take the month/day from one column plus the year from another ... A: The formula you gave me works fine when I enter it into my copy of excel - so it's either version ... | |
| 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: Sorry for the delay in replying. I'm not 100% clear on what dictates a new record, bearing in mind ... | |
| If, then statements | 7/2/2009 |
| Q: I am trying to write if, then statements to convert raw scores on tests to percentiles to help ... A: Im not sure Ive got enough to go on here - there is a percentile worksheet function, but with only ... | |
| Excel programming - copy, paste + time to another sheet | 7/2/2009 |
| Q: ) I was wondering if you could help me with some excel programming. This is the case. I have some ... A: Not sure if you mean copy different data on sheet1, or to a different area on the target sheet? The ... | |
| excel range to word row 12 | 7/2/2009 |
| Q: I have a little experience with Excel macro but did not touch word macro yet. I would like to copy a ... A: I tend not to work with copy and paste, preferring to set the selection text by the use of format ... | |
| problem running excel 2002 macro in excel 2007 | 7/2/2009 |
| Q: The macro was created in xl 2002 in windows xp and when i try to open it in xl 2007 windows vista ... A: OK, this is an operating system issue really, as Vista has issues regarding permissions - the ... | |
| Vlookup using Address function | 7/1/2009 |
| Q: How do I use the VLOOKUP function by specifying the table using the ADDRESS function? I keep getting ... A: The ref error indicates a problem with the reference - =VLOOKUP(2,INDIRECT(ADDRESS((A5,3) ... | |
| IF ELSE | 7/1/2009 |
| Q: Below are the two difference If conditions in my macro code ====================================== ... A: easy enough to do, but your condition will ALWAYS be true as it stands at the moment as you aret ... | |
| To Copy multiple cell values in one cell | 7/1/2009 |
| Q: I have a sheet column A has ID numbers like "20769" column B hase Name Column C has Department Names ... A: This line If Len(Sheets("sheet2").Cells(OutVar, 4).Value) > 0 Then Sheets("sheet2").Cells(OutVar, ... | |
| Excel programming - copy, paste + time to another sheet | 6/30/2009 |
| Q: ) I was wondering if you could help me with some excel programming. This is the case. I have some ... A: something like Sub copier() Dim rCount As Long For rCount = 2 To 4 Sheets("Sheet2").Range("a" & ... | |
| Excell time format | 6/30/2009 |
| Q: I have a text file that i imported into excell with time formats such as 11:58:00a for am times and ... A: as long as this column ONLY has time in it, select the column, then format cells as time hh:mm. ... | |
| Vlookup using Address function | 6/30/2009 |
| Q: How do I use the VLOOKUP function by specifying the table using the ADDRESS function? I keep getting ... A: I'm not sure WHY you are wanting to use the address function, but it only returns the text value of ... | |
| Lookup from Separate Sheet | 6/30/2009 |
| Q: I have an Excel file (I'm using 2007 if that matters) with multiple sheets. Sheet A is my "master" ... A: Best one is vlookup - this would need the part number to be in a column before the supplier data - ... | |
| 256 column limit | 6/30/2009 |
| Q: I have a database (csv) of over 1000 data items and want to be able to edit using Excel 2003. Is ... A: It's going to be more difficult as the output statement would need 1000 lines - I would suggest ... | |
| 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: I take it the ID is a unique value. If so, Dim counter As Long Dim OutVar As Long Dim ... | |
| Inq | 6/30/2009 |
| Q: need ur help to check if 2 field meet then count it. right now im using this fomular =COUNTIF('Svg ... A: You didn't say where the staff name was located, but I'll assume column Q =sumproduct(--('Svg - ... | |
| Convert a text formula into a computable formula | 6/30/2009 |
| Q: I want to enter a formula into cell A1 in the format (F#=C#*100/E#*D#) such that at each row in ... A: I think my brain was fried when I replied before - my daughter has been slightly unwell over the ... | |
| Excel Chart | 6/29/2009 |
| Q: Aiden - my favorite Expert. Here's my task. We get a certain number of orders each month. I track ... A: You are very kind - in this instance, I think I'll refer you to ... | |
| To Send Mail | 6/29/2009 |
| Q: I have a sheet, column H contains links of PDF files saved in my computer. The column G contains ... A: Sub Send_Files() 'Working in 2000-2007 Dim OutApp As Object Dim OutMail As Object Dim ... | |
| Custom function | 6/29/2009 |
| Q: Aidan, I work with cards and tried this evening to create a custom function to prepare for a trick. ... A: >easiest fix surely is to have a third argument, being the OFFSET value (though you say offset was ... | |
| To Send Mail | 6/29/2009 |
| Q: I have a sheet, column H contains links of PDF files saved in my computer. The column G contains ... A: Sorry for the delay in replying - This line does the | |