Excel - Microsoft Software - Computing/Technology

	AllExperts > Excel
	Search

About Aidan Heritage
(Top Expert on this page)

Expertise
I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards

Experience
My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

You are here: Experts > Computing/Technology > Microsoft Software > Excel

Questions Answered By Expert Aidan Heritage
In Category Excel

Subject	Date Asked

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 ...
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 test for email, so should be amended If ...
using Excel for online forms	6/26/2009
Q: Our organization needs to gather lots of information through online forms. Some of these are ... A: This would be better posed to a web expert, in that the solution would probably be to design a ...
To Send Mail	6/26/2009
Q: I have a sheet, column H contains links of PDF files saved in my computer. The column G contains ... A: This line does the test for email, so should be amended If cell.Value Like "?@?.?*" And _ to: ...
Combining Data	6/26/2009
Q: Say I have two separate spreadsheets. Column A = names of people. Column B:IV represents a week in ... A: No problem, and you've almost used the right function name in your question - it would be a vlookup ...
Add Sheets	6/26/2009
Q: I have a question for you. I have a excel 2007 file which has many sheets in it. The number of the ... A: Sub adder() Dim sh Dim check As Boolean check = True For Each sh In ActiveWorkbook.Worksheets If ...
Calculate overtime	6/25/2009
Q: I have a simple spreadsheet for excel. I am trying to calculate overtime for saturday if an employee ... A: not sure what the rule you are applying for overtime is, other than the total hours being more than ...
sorting and sub sorting macros in excel	6/25/2009
Q: Aidan I just wanted to than you in advance for your help. What I want to do is for example: Lets ... A: Do you mean groups them so it say red dog and shows 5 as the result - if so, no need for a macro - ...
Column checking	6/25/2009
Q: I have two columns of data - in A it is a list of names, in B it is the same names but some extra. ... A: If you don't mind NOT having them aligned, then you could use conditonal formatting to highlight the ...
Excel	6/24/2009
Q: I have been asked to save all my word and excel documents with the file/path location in the footer. ... A: Word is easy, as any changes to the normal template will do this - so open the normal template and ...
Summary Report	6/24/2009
Q: I have a excel sheet which a lot of data in many columns and rows. In column A therez some data ... A: I would use a pivot table to summarise the column A data - that would seem to be the easiest way to ...
Microsoft Excel- Referencing the contents of a cell by row and column	6/23/2009
Q: I am trying to create a spreadsheet that gives the user the amount in a cell when the user enters ... A: I have problems seeing most of the pictures that get onto this site- probably my bad eyesight - but ...
Excel macro to copy and compare worksheets	6/23/2009
Q: I have a workbook that contains two worksheets. One is called Index. It creates index of all ... A: I'm not absolutely sure what we are comparing but something like dim CurSht as worksheet set ...
256 column limit	6/23/2009
Q: I have a database (csv) of over 1000 data items and want to be able to edit using Excel 2003. Is ... A: so the macro needs to read the 1000+ items, going down the sheet for each field, and back to the top ...
insert signature	6/23/2009
Q: i have a spreadsheet that has macros as far as i can say. I am not very faliliar with excel. I am ... A: Somehow I managed to miss the fact that this was Excel - I think you have a further problem in that ...
re: is this sumproduct:sorry! here is my corrected question!	6/22/2009
Q: I’m a complete formula newbie. A colleague gave me this formula for my task, but it isn’t working ... A: The formula itself looks fine, so I would IMAGINE that one of the cells in the range c2:c401 or ...
Find next coloured cell in a range	6/22/2009
Q: You very kindly helped me with a recent question, and I would be very grateful if you could help me ... A: ActiveCell.Interior.ColorIndex (or any other cell reference) will return the fill of a cell, white ...
VLOOKUP query	6/22/2009
Q: I have a problem Im hoping you can help me with in Excel 2007. I have a spreadsheet with multiple ... A: vlookup works like this =vlookup(what,where,which,logical) the what is fine, it's te B3 value The ...
Delete Duplicate Blanks rows	6/22/2009
Q: to Everyone There are many Software and Addin and Macro or Code to Delete or Eliminate the ... A: Sub aidan20() Set currentcell = Worksheets("Sheet1").Range("A2") For looper = 1 To ...
Excel	6/20/2009
Q: I have a workbook with 3 worksheets. I have it look up items on worksheet 2 when I type in the ... A: At face value it sounds as though there is already protection on the cells - this would be what I ...
Populate Data in Multiple Worksheets	6/20/2009
Q: My question is how to I get data to populate in nine different worksheets. I have established a ... A: Sounds like an array filter, although I'm not 100% sure why we need to have the data repeated ...
Updating charts	6/20/2009
Q: Good day Aiden. I hope you can help me because I am lost. I did read what I want to do somewhere, ... A: Sorry for the delay in replying - was spending time with the family over the weekend. I'm in the ...
Updating charts	6/19/2009
Q: Good day Aiden. I hope you can help me because I am lost. I did read what I want to do somewhere, ... A: Absolute, relative etc references are fine, but won't help here because the data source keeps ...
excel	6/19/2009
Q: I have encrypt my work book excel then I change windows when i get to open excel work book I get ... A: its not very clear from the question what the problem is - if it's a forgotten password then I may ...
VLookup	6/19/2009
Q: Aidan: Is there a way to embed field name in the VLookup function to grab its contents if the row is ... A: sounds like a double vlookup? Or an Indirect? I don't quite follow the question - if it's a ...
Importing file using wildcard function	6/18/2009
Q: I am sent a file daily that is in this format FXRATES_DDMMMYYHH_MM_SS. It is sent a copy daily so i ... A: I would probably do this using the following method Set fs = Application.FileSearch With fs ...
Absolute cell reference between worksheets	6/17/2009
Q: I am trying to create an absolute cell reference between worksheets. I want to be able to be able ... A: Yes, it can be done without macros, although macros would make it easier - at the moment, the ...
Making changes to chart data using a macro	6/17/2009
Q: I'm using a proprietary software that exports data in the form of Excel 2003 spread sheets. I've ... A: It's certainly possible, and indeed I regularly do the same thing. What I'm not clear on though is ...
Excel Help	6/17/2009
Q: I am trying to show two different years but the same month on the x axis. There are two bars one ... A: I would probably just have the data show the month, with a legend indicating which colour ...
NEED Help with a formula to delete multiple rows of data	6/17/2009
Q: need to scrub some data, I've got multiple rows with a single part number, need to delete rows for ... A: Not clear if there is other data, but data, filter, advanced filter, unique records might be a way ...
Excel Macro and Linking	6/17/2009
Q: I need to write a macro to numerically sort a list of Integer type number in acending order. Basic ... A: I think I follow this - I'll paraphrase to ensure we've got that right though - you have a list of ...
fix sized charts	6/16/2009
Q: I'm using Excel 2007. I would like to know how to fix the size of charts so that when any cell ... A: I do apologize - because it was already so un-intuitive I foolishly assumed Microsoft couldn't have ...
Absolute cell reference between worksheets	6/16/2009
Q: I am trying to create an absolute cell reference between worksheets. I want to be able to be able ... A: as you have gathered, it doesn't work this way - excel is doing what is GENERALLY needed, which is ...
Time Referencing!?	6/16/2009
Q: Edward, I am trying to create a call sheet for this production company i just got hired at and one ... A: It's no problem at all - time to excel is simply a decimal fraction - 1 equals one day, so 24 hours ...
writing to txt file using vba at specific location	6/16/2009
Q: i need to write some data in an excel file to a text file. I've a macro for this. But the ... A: There is a binary format file which has specific locations, so that's one option, but I'm not sure ...
SUPER FAST AUTOFILL IN EXCELL	6/16/2009
Q: IS THERE ANY OTHER WAY I COULD QUICKLY AUTOFILL AT LEST A MILLION CELLS.THE USUAL DROP AND DRAG ... A: sure - type your value in the first cell, then press F5 (the goto key) and type the full range you ...
Chart Dummy Series	6/16/2009
Q: I am using one of Excel 2003's custom charts. This specific chart is called the "line - column on 2 ... A: The quickest and best answer I can give is to refer you to an EXCELLENT site for charting ideas ...
formating with if clause	6/16/2009
Q: conditional formatting did not meet my needs. i want to form sth like that: if the value is xxx; ... A: There isn't anything directly in excel that can pick up if a value is in bold, but is the bold ...
FORMULA	6/15/2009
Q: Cell F24 IS BLANK Cell G24 IS BLANK Cell AB24 CONTAINS FORMULA =AVERAGE(F24:G24) PLEASE PROVIDE ... A: It is not clear what you want to do here - you say "formula for Cell H24 to be blank" but you don't ...
excel lists - excell 2003	6/15/2009
Q: I have a list on sheet 1 columns - Surname, First Name. Date of Birth, Occupation etc. This list is ... A: It would probably be possible but I don't understand the requirement to have the list in two places ...
Excel 3D Chart wall picture	6/15/2009
Q: Using excel 2003 SP3 - I can insert a picture for the walls of a 3D chart, but the portion of the ... A: I tend not to work with 3d charts as they can be quite difficult to get to look right, and using ...
Find nth value in range	6/15/2009
Q: Good day, can you please answer this: Say in a sheet I have a range A1:20 and certain values in each ... A: yes it is - my match function has the range specified as a1:a20, so change that to X, and change the ...
To Adjust Zoom According To Screen Resolution	6/15/2009
Q: I have found the following Code which works perfect. It identifys the best fit zoom itself and ... A: Private Sub Workbook_Open() On Error Resume Next Dim r As Worksheet Dim cur As Range ...
Drop down lists / data validation lists	6/14/2009
Q: Hey Aidan, I have a query I hope you can help me with. I have an excel sheet with two lists: ... A: not sure what your OR statement is doing - I'd try ...
Find nth value in range	6/14/2009
Q: Good day, can you please answer this: Say in a sheet I have a range A1:20 and certain values in each ... A: Dim holdvar As String Dim n As Long For n = 1 To 3 If Len(holdvar) <> 0 Then holdvar = holdvar & ...
#DIV/0 error	6/13/2009
Q: sreadsheet, my formula in the cell is =SUM(A1)*(B1/C4) when C4 is zero, and is quite often the case ... A: I see you like the sum function! As I mentioned before, it's not necessary - =sum(a1:A100) is ...
Extra Columns	6/13/2009
Q: This should be an easy Q & A, but I'm stumped. I have an Excel worksheet with extra columns that I ... A: ctrl end takes you to the end of the last EVER used range - so at some point, someone would have ...
Formula problem	6/13/2009
Q: I have two files,sample as per attachment. I whish to have data updated from file B when change the ... A: it sounds like it needs a multiple condition sumif formula, which can be done with sumproduct - it ...
Importing XML files using a macro	6/12/2009
Q: I'm trying to create a macro that will import (in a saved) XML file that asks the user for the ... A: Not sure how much you have in VBA already, so I'll assume you are OK apart from getting the file ...
Ho can I do merge a cell by keyboard in MS Excel	6/12/2009
Q: I want to know that how can i do merge a cell in ms excel by keyboard A: There is no keyboard shortcut already assigned that I'm aware of, but you could assign a keyboard ...
Drop down lists / data validation lists	6/12/2009
Q: Hey Aidan, I have a query I hope you can help me with. I have an excel sheet with two lists: ... A: I have an example of this at ...
convert text to time in excel	6/11/2009
Q: In regards to this post http://en.allexperts.com/q/Excel-1059/Excell-Time-Format.htm I got a data ... A: allexperts answers tend to be addressed to the specific questioner, so there is nearly always a need ...
One Cell: Two Conditions: Two Attributes	6/11/2009
Q: I have a cell value (X) that should be blue when it is greater than Y and underlined when greater ... A: It's three conditions therefore - the first is x>Y,x<=Z the second X>Z,X<=y and the last is ...
Excel	6/11/2009
Q: What is the best way to learn Excel..? A: everyone learns differently, but my own method is to find challenges and then work out ways of ...
Intentional Circular Referance without VBA	6/11/2009
Q: I desire to use circular referancing to keep a summation total of all value changes that are made in ... A: sorry for the delay in replying - for some reason I didn't see this post at the top of my list. My ...
How to calculate a total for the week from my daily totals?	6/10/2009
Q: I formatted all the cells on the sheet as TIME, Type: 1:30 PM. Then I wrote my hours as follows, ... A: The custom format I gave you was intended only for column C which was the calculation of time, ...
Excel - Macro Run On Cell Value	6/10/2009
Q: I'm currently working on a spreadsheet for work. I'll try and be as brief as possible for you. Cell ... A: It does make sense, but what is triggering the change - is it (I hope) a manual update? If so, you ...
not letting data move after creating sheet	6/10/2009
Q: I created an alphabetized name column - then a code number column - then a paragraph location ... A: question not exactly clear, and I personally wouldn't insert data into the list, just add it to the ...
Populating cells on a conditional formula	6/10/2009
Q: I have an existing spreadsheet listing all processed Invoices and their values. This is sorted by ... A: Sorry, I made a guess that you knew about the different types of references - you need to use what ...
Excel - If and search	6/9/2009
Q: I'm currently working on a spreadsheet for work. I'll try and be as brief as possible for you. Cell ... A: It does, it's vlookup =vlookup(d2,sheet2!$A$2:$B$300,2,false) which means look in the first column ...
Count cells with certain criteria	6/9/2009
Q: I have created a chart for work which contains a reference number, the person dealing with the ... A: This sounds like a job for a pivot table - which will do it easily without formulas - you didn't ...
How to calculate a total for the week from my daily totals?	6/9/2009
Q: I formatted all the cells on the sheet as TIME, Type: 1:30 PM. Then I wrote my hours as follows, ... A: You have made life difficult for yourself by using your formula in C- change it to =b1-a1 and ...
Returning value with text	6/9/2009
Q: How to return the one cell out of five that includes text and not zeroes as the others? A: Firstly congratulations on being the first person who's left a picture that came out large enough ...
Callouts	6/8/2009
Q: I'm a long-term Excel expert, and even I don't know the answer to this one. I'm creating a document ... A: sorry for the delay in replying - had to find a machine with the right version of office- I think I ...
Excel INDEX & MATCH	6/8/2009
Q: everything works except one formula in two cells, the same formula different references. I have not ... A: without the file I cannot be certain, but I'm confused with the match function - at least, as ...
excel compounding growth	6/7/2009
Q: I am attempting to use Excel to calculate a final unit value based on a percentage growth of 10%, ... A: There are a large number of financial functions, although this isn't an area I'm familiar with - see ...
automatic multiplying line item cells	6/4/2009
Q: I am constantly using excel for quotations and would like to know how to set up the cells so they ... A: I assume you mean that you want cell I to have the results of b x H - which would be =B2 * H2 as a ...
Excel 2007	6/4/2009
Q: is there an easy way to get rid of the green triangles in cells? My formulas are correct and I don't ... A: it's a warning rather than anything else, but if you are sure you don't want it, click on one of the ...
Callouts	6/4/2009
Q: I'm a long-term Excel expert, and even I don't know the answer to this one. I'm creating a document ... A: despite a long search, I've not managed to find an answer on this - although you didn't mention the ...
Analysis of Ranked files	6/3/2009
Q: Yesterday I have sent an e-mail at with an attached wb file. Did you receive it ? I have stated my ... A: I did get a file, I have replied as your explanation is not clear - PLEASE remember that this is a ...
excel	6/3/2009
Q: If want the information on sheet 1 to appear on multiple sheet on the same line while I am typing, ... A: selecting all sheets would be the way to go, the only other way would be to use VBA - but I'm not ...
Create Outlook 2003 appointment from EXCEL	6/1/2009
Q: I'd like to create an appointment into Outlook 2003 from data in an EXCEL sheet. I want the ... A: The macro copy looks fine - he hasn't referenced activecell.offset etc because that was my ...
Macro To Enable Macros in Workbook	6/1/2009
Q: My workbook template has some macros when user opens the workbook it prompts to enable macros (in ... A: Thinking about it, the button is pointless, as they cannot run it if they haven't already enabled ...
multiple ands within an if statement	5/31/2009
Q: Aidan: I have the following formula which is not working. I suspect the problem is with the second ... A: I think that the formula is something like ...
multiple ands within an if statement	5/30/2009
Q: Aidan: I have the following formula which is not working. I suspect the problem is with the second ... A: WHY are both ANDS necessary - an AND will only return true if all conditions are true, so nesting ...
date calculations in excel	5/30/2009
Q: I have excel 2003, i am a truck driver, and trying to write a small program in excel to determine ... A: This should be easy, but a key part (I think) of your question is that you want this to work with ...
Connecting calculations to a cell	5/29/2009
Q: I have a large pivot table (I believe that's what it is--auto filtered on each column). I sorted it ... A: I suspected that that may be the case - I THINK that a pivot table is probably going to be the ...
using Excel for online forms	5/29/2009
Q: Our volunteer organization needs online forms that will enable us to use the data in Excel and ... A: Not clear what the purpose of the forms is - or indeed, what you had in mind by online! However, if ...
Macro To Enable Macros in Workbook	5/29/2009
Q: My workbook template has some macros when user opens the workbook it prompts to enable macros (in ... A: In Excel 2007, the file is set as macro enabled, and the Trust centre handles the warnings - these ...
Transferring data from one excel worksheet to another	5/28/2009
Q: I am trying to find a way to populate excel spreadsheets from previous excel spreadsheets that I ... A: I think I would do this with Visual Basic - in that you are creating batch information where certain ...
Color a text cell from data of a value cell.	5/28/2009
Q: It's rather an odd question, but here it goes. I would like to color cell B5 with text 'Ron', when ... A: You say color and also text - and it's not totally clear what needs to happen - my GUESS is that you ...
Connecting calculations to a cell	5/28/2009
Q: I have a large pivot table (I believe that's what it is--auto filtered on each column). I sorted it ... A: Pivot tables certainly exist - but you then say auto filter which is unlikely though not impossible ...
Error in Excel "Unable to Read file"	5/28/2009
Q: I have a file which is created in excel 2000 having formulas & Pivot table and it is password ... A: There is no difference in file format, so there is something odd about the file or it's location - ...
Formula/Function	5/28/2009
Q: I am working on a workbook that has 5 worksheets. The first 4 have quarterly info with category ... A: Your email didn't make it through to me, but I HOPE mine did - if you can mail me a sample file I ...
Excel accessing data behind a secure login; for Automated Machine Failure Notification System	5/28/2009
Q: Aidan, I need your help to access web data through excel that resides behind a secure login page. ... A: I've not done web access via secure logins before, but I found ...
Create Outlook 2003 appointment from EXCEL	5/28/2009
Q: I'd like to create an appointment into Outlook 2003 from data in an EXCEL sheet. I want the ... A: see http://en.allexperts.com/q/Excel-1059/2008/6/VBA-Excel-Outlook-Appointment.htm for an example ...
linking workbooks	5/27/2009
Q: I have a set of workbooks that are all formatted the same way with the same set of information, but ... A: Unfortunately, I would imagine that this is almost the only solution - it would be possible to use ...
Merge and move data	5/27/2009
Q: My dear expert! I would appreciate very much if you could help me. I need to do the following ... A: I assume that there is an error in the example, as 2 should presumably show de. There is no ...
Excel Formula	5/27/2009
Q: I have box “P16” with a drop down of several values. Let’s say I choose value 3. I then have drop ... A: P16 is already set with a drop down choice - but you want to adjust this value based on other ...
Excel	5/27/2009
Q: I am working on a spreadsheet, I have product code numbers in column A and the same in column G. I ... A: it doesnt really make sense I'm afraid, though it's probably just me - are you checking that the ...
To Copy Sheet in a New Workbook	5/27/2009
Q: In responce to my below question you suggested the following code:I have a workbook with some ... A: Sorry for my failure to answer the full question - it's rather too easy sometimes to see what you ...
vba help	5/26/2009
Q: Hoping you can help me with some vb code. My spreadsheet that has a combobox pull-down menu. The ... A: I don't really have enough to go on here - POSSIBLY a select case might be the way to go? select ...
pivot table	5/26/2009
Q: is there a way i can resize the data instead of going the pivot table wizard. is there a way to ... A: I would do this by setting a dynamic range - i.e a named range that adapts to the size of the data - ...
looops	5/26/2009
Q: In column A I have around 650 symbols. Can I set up a macro to go to the next symbol until the macro ... A: As I mentioned, the actual requirements aren't clear - what defines a Symbol? It BASICALLY sounds ...
looos	5/26/2009
Q: In column A I have around 650 symbols. Can I set up a macro to go to the next symbol until the macro ... A: first thanks for sticking with me - However, I'm not sure what you mean by symbols - it is this that ...
Auto copy data from sheet 1 to sheet 2	5/26/2009
Q: I have a workbook using 2 sheets. It contains a list of potential clients on sheet 1. Column O is ... A: Sorry, not clear - I'm pretty sure you need VBA using the change event of the worksheet, but I ...
Option based macro	5/26/2009
Q: I currently have a spreadsheet that is used in the US and Europe. I currently have 4 buttons at the ... A: You didn't specify what the macro actually does, but as you seem to have a working macro I would ...
combining two macros in MS Excel 2007	5/26/2009
Q: I have the following macro code Sub Import_output() ' ' Import_output Macro ' ' ... A: not clear where you want to add it but (1) copy the code and paste it into the area you want - thus ...
Formula/Function	5/26/2009
Q: I am working on a workbook that has 5 worksheets. The first 4 have quarterly info with category ... A: Seeing the sheet might help, as I'm not sure if the layout of all 5 sheets is the same - IF it is, ...
Lookup a number and compare then print.....	5/26/2009
Q: I have two different sheets in a workbook that each contains a unique number that I can compare. ... A: basically a vlookup function - this will return #n/a if no match- if that's ok, just go with the ...
Repeated Subtractions in Excel	5/26/2009
Q: I have a question regarding MS Excel 2003. I have three rows in my spreadsheet, the first row is ... A: The $ sign determines the state of the reference - a reference of C1 is a relative reference, i.e. ...
looos	5/26/2009
Q: In column A I have around 650 symbols. Can I set up a macro to go to the next symbol until the macro ... A: you can put in time delays in macros - I'm not 100% sure what you need the macro to do - but for ...
Excel	5/26/2009
Q: A row is formatted to yellow fill and i need to add all the yellow rows only. Do you know of a ... A: It depends how the cell is formatted - there is no direct formula to count formatted cells, but if ...
Find Macro Function	5/26/2009
Q: I'm in need of some help from you sir regarding Creating a Macro. I use office 2007. In Sheet2, I ... A: what wasn't clear was if the error found should actually have any reference to WHERE the error was ...
To Copy Sheet in a New Workbook	5/26/2009
Q: I have a workbook with some worksheets. The sheet "TNAF" is hidden. I need a code which I will ... A: sorry - that should have been Sub Macro1() Dim MyBk As Workbook Set MyBk = ActiveWorkbook ...
Gold Purity Calculation Formula	5/25/2009
Q: I originally asked how to set up this formula for determing gold purity. The gold is weighed dry and ... A: I'm not sure what the underlying calculation is supposed to be - the formula you were given goes ...
excel 2003 - data range	5/25/2009
Q: PROBLEM: I need to refer to an adjustable data range in a macro. I know NO VBA. I need to refer ... A: It's not really a recordable thing - but you COULD define a name and refer to that - the name would ...
vba help	5/25/2009
Q: Hoping you can help me with some vb code. My spreadsheet that has a combobox pull-down menu. The ... A: I think the easiest solution would be to have the list of items somewhere on the worksheet (hidden) ...
Dates count	5/25/2009
Q: i would like to know how to count NO of times date changes in particular cell. i would like to put ... A: question not totally clear - is this to be one cell counting dates in other cells, or a count of the ...
To Copy Sheet in a New Workbook	5/25/2009
Q: I have a workbook with some worksheets. The sheet "TNAF" is hidden. I need a code which I will ... A: Sub Macro1() dim MyBk as workbook set mybk=activeworkbook Application.ScreenUpdating = False ...
Print to specified paper bin	5/24/2009
Q: I have a problem I can't solve by myself - so I hope you can! I have a Visual Basic 6 application ... A: somewhat ridiculously, this is not directly possible - as you've seen, it's easy in Word, but in ...
always return the difference in a table of numbers	5/24/2009
Q: I have set up a table for $SALES and I am having a difficult time trying to figure out how much ... A: I think I'd do this with a lookup table, where the current sale is looked up and the column to the ...
IF formula - returning False when True	5/23/2009
Q: Am fairly Excel savvy but this simple formula 'error' is driving me nuts! IF is comparing a forumla ... A: I think the clue is in your statement taht the formatting is the same - changing a format doesn't ...
selecting all non blank cells	5/23/2009
Q: I want to select the range of cells from the first cell (A1)to the last non blank cells even if ... A: Not sure if this is what you mean but I'll try put the cursor on A1, press Ctrl Shift End - this ...
working with Zeros in front of a number	5/22/2009
Q: I have numbers that may have any where from 1 to 4 zeros in front of them. The numbers without zeros ... A: your second item seems to make the first redundant - though I'm not sure if the columns being ...
To Copy Sheet in a New Workbook	5/22/2009
Q: I have a workbook with some worksheets. The sheet "TNAF" is hidden. I need a code which I will ... A: The EASIEST thing to do is to record the actions of unhiding the sheet, copying it to a new workbook ...
Pull data from txt file to Excel	5/22/2009
Q: I have just sent you my question and realized that the sample data did not get copied correctly. I ... A: it's hard to follow from the text description here - and I cannot see the ACTUAL format of the text ...
Pulling data onto Excel from txt file	5/22/2009
Q: each month-end I prepare a report for my manger which takes 2 to 3 hours. I like to automate it. ... A: it's hard to follow from the text description here - and I cannot see the ACTUAL format of the text ...
running totals	5/21/2009
Q: I am just beginning to learn my way around excel. I have a spreadsheet with 5 columns to it. They ... A: Sure - I'd use an IF test probably against quantity =if(c2=0,"",sum(whatever)) where C2 is the ...
Excel V-lookup question	5/21/2009
Q: I have a product excel sheet that has several columns including model numbers and attributes such as ... A: I THINK it's probably more an automated filter you need, which can be done with formulas - I have ...
EXCEL 2003	5/21/2009
Q: I have a excel shared spreedsheet on our network. The file has been working great until recently. ... A: Shared workbooks are a BAD idea - it's an idea Microsoft had that really doesn't work - sooner or ...
Excel VBA & Loops	5/21/2009
Q: I am just starting off with VBA programming in Excel (but am not new to programming in general). I ... A: A for next loop will work like this for variable=lowerbound to higherbound optional step value ...
Down one row to the next available cell	5/20/2009
Q: How I get data from the same cell of several different worksheets to show up in a column of a ... A: I'm not clear what is happening here - are you talking about consolidating growing data from ...
Excel question	5/20/2009
Q: I have a field pulled from a database that is formatted as follows: 5/17/2009 15:30 I want to have ... A: Your formula doesn't make sense to excel, it would need the AND keyword ...
Excel VBA & Loops	5/20/2009
Q: I am just starting off with VBA programming in Excel (but am not new to programming in general). I ... A: various options - you could reference them one at a time, you could do a for.next loop with a test ...
run time error #5	5/20/2009
Q: I have Excel 2002 I have a basic recorded macro that includes some conditional formatting code. It ... A: VBA is the language used for macros, wether recorded or written - this code seems to have redundant ...
code for copy & paste multiple Tables from diff word files to excel at different sheets	5/20/2009
Q: Sir, I have One word document contains three different tables. I have worked with excel VB codes ... A: the selection of entire table by table number means the number of rows doesn't matter, BUT it's not ...
cumulative addition	5/20/2009
Q: i'm doing a project where am first required to collect data at an interval of 5 seconds over a 24 ... A: You won't be able to do this in notepad, so there are two options - 1, copy directly to excel and ...
NEED EXCEL FORMULA -HELP!!	5/19/2009
Q: Hey Aidan,,my name is Dave...my fiance is working on a Excel personal Project for an online school. ... A: absolute reference - one that refers absolutely to the specified cell and doesn't change when moved ...
run time error #5	5/19/2009
Q: I have Excel 2002 I have a basic recorded macro that includes some conditional formatting code. It ... A: I would have thought a lower version would be more likely, but I don't know what the code is so ...
cumulative addition	5/19/2009
Q: i'm doing a project where am first required to collect data at an interval of 5 seconds over a 24 ... A: I'm not exactly sure what is required here - it SOUNDS as though you may simply want to have values ...
code for copy & paste multiple Tables from diff word files to excel at different sheets	5/19/2009
Q: Sir, I have One word document contains three different tables. I have worked with excel VB codes ... A: Sub WordDoc() Dim appwd As Object ' On Error GoTo notloaded Set appwd = ...
Excel Nested If statement with Dates	5/18/2009
Q: Statement that returns a "1" if date range is between certain criteria. It is not working though, ... A: Not sure why you don't want to use the year function, and not clear what the criteria is - 12/31 ...
excel	5/18/2009
Q: I'm interested in doing the work of an administrative asst, receptionist, hotel reservation/front ... A: As I haven't worked in these situations, I have no idea- however, worth a download is the file ...
Drop-Down Macro	5/17/2009
Q: after searching for a code to allow my dropdown list to select multiple items in the same cell (i.e. ... A: I did make the comment that you might need to extend this to add the right hand side as well! ...
Excel 2007 conditional formatting	5/15/2009
Q: I have two rules already, one to turn the cell green, one to turn the cell red. These are based on ... A: I don't quite follow, but I'm not sure what the rules you are applying are- it SOUNDS though as ...
conditional formatting	5/15/2009
Q: First I want to thank you for your help in this matter. I have an Excel (2007) sheet in which I ... A: It's not conditional formatting, it's simply an IF statement - or a lookup - I'd probably do it with ...
Excel Novice needs help with a formula	5/15/2009
Q: I am sure this is a very simple formula but I am a beginner learner and could use some help. In box ... A: The formula is vlookup - or at least, the easiest way to do it is that formula Create a data table ...
Dependent Filtering	5/15/2009
Q: Aidan, I have 15 columns that I want to filter. I need the filter to be dependent on one another. ... A: http://www.mcgimpsey.com/excel/lookuppics.html may give some ideas for the image - as to the filter, ...
hyperlink problem	5/15/2009
Q: I have an excel sheet of names and addresses. I have created a hyperlink (the person’s name) to open ... A: It doesn't quite make sense - but a hyperlink is a link to a document to be opened - it SOUNDS as ...
data validation with "mixed criteria"	5/14/2009
Q: Can I create a data validation condition with a drop-down list that "allows" EITHER a date to be ... A: I think it depends a little on the date - if this is a range of a small-ish number of dates, then ...
Excel Formula Question	5/14/2009
Q: I have a spreadsheet that is in need of a formula that I can't figure out how derive. I have a ... A: I'm GUESSING that we cannot rely on the order number being 4 characters, and that we aren't allowed ...
Excel symbols	5/14/2009
Q: Do you have any idea where i could obtain a list of excel symbols for example: > greater than < ... A: Not sure I know what you need to know (you've already covered a lot of the common ones!), but ...
VLookup Relative Reference	5/14/2009
Q: I have a VLookup formula that contains a reference to another cell in the worksheet. That cell ... A: You've answered your own question - well, nearly! - indirect is the function you need if using a ...
Set Excel to show upcoming expiration date using start date.	5/14/2009
Q: I have several dates that show when a class was taken and I need those dates to show red when they ... A: didn't specify version of Excel but it's basically conditional formatting using formulas - not sure ...
IF statement and VLOOKups	5/14/2009
Q: Hey Aidan, I have hit a brick wall with my formula in excel 2003, and hope you may be able to help. ... A: I think the formula can probably be simplified, but at the moment you end with an if statement that ...
excel autosum	5/14/2009
Q: On my spreadsheet I have columns that when you add a number it is automatically added to the total ... A: One of two things - either you are on manual calculation at which point it says calculate at the ...
simplify formula	5/14/2009
Q: I am working with a spread that was compiled several years ago & is quite labor intensive. There ... A: well, G10*($I$9+$J$9+$K$9+$L$9+$M$9+$N$9+$P$9+$Q$9+$R$9)/$C$9 to is the same as ...
How to delete rows with zero values with VBA	5/14/2009
Q: Aidan, I am looking for a bit of assistance on the following code. I need to find zeroe's in an ... A: This is the standard method I use in this situations - this version tests both for a zero value and ...
Calculating daily and weekly OT	5/14/2009
Q: I have Windows Vista and Office 2007, on my excel timesheets, I need to calculate overtime for ... A: shouldn't be too hard - I IMAGINE the 40 hours in a week is a red-herring, as to work more than 40 ...
XL formulars	5/13/2009
Q: I want to fill in an invoice form on sheet 1 and have the date and amount of the invoice transfer to ... A: Formulas won't do it as you want the then current data to move across and be retained - so it would ...
Copying rows	5/13/2009
Q: Sir, I hope you can help me out, I knwo this can be done, but having trouble. 1. 1 workbook is all ... A: copying rows Not sure how much of this you have already done - it would be easier to apply the code ...
Code for question's response	5/13/2009
Q: "I am working on a survey. Each question has many responses. I am going to have a separate column ... A: I'm not at all clear what you want to do here - it sounds like you might have a series of questions ...
Turning Cell Red if value is "0"	5/13/2009
Q: I am trying to turn cells red if the number 0 is entered into the cell. I have tried a couple simple ... A: a blank cell evaluates to zero - so your condition needs to use a formula - I'll assume we are ...
Structuring a column of data so it can be graphed.	5/13/2009
Q: I have two columns of data. column 1 is continuous with no breaks and is the X axis data. Column 2 ... A: I'm having problems relating to the problem - as the question reads, you want to copy the data "as ...
save as macro	5/13/2009
Q: I have been trying in vain to get a macro to save a copy of my excel file with a name from cell C2, ... A: specific printer will be difficult as the printer doesn't expose itself (as it were!) to VBA as far ...
Calculating if X, then copy Y formula.	5/13/2009
Q: I have quite a question to ask. I have a sheet that I am working on where I have asked Excel to ... A: Having stared at the question a number of times it LOOKS to me as though you are simply after a ...
Using IF, AND and OR in formulas	5/13/2009
Q: I've got a spreadsheet with numbers and i've trying to use a combination of IF, AND and OR in one ... A: In English, your formula says if A2 is greater than OR equal to B2 then display yes - as you say you ...
Personal Macro Workbook	5/12/2009
Q: I received an error in Excel which cause my Personal Macro Workbook not to open automatically when ... A: I don't think the location you've given is correct - but we'll ignore that for the moment. Copy the ...
How to get data from web in Excel	5/12/2009
Q: I would like to automate couple of my tasks using VBA/Excel. Let me explain the current process that ... A: I've not done this myself, but it should be possible - my only involvement with web data has been ...
Copying Conditional Formatting	5/12/2009
Q: I was able to create conditional format across a row, so that 6 data points will turn colors based ... A: You didn't specify the formatting conditions you had used, but basically you are going to need a ...
Random number generation	5/12/2009
Q: i wish to have a spreadsheet that will allow me to issue a random number to a date eg 11/5/2009 ... A: interesting question - it would have to use VBA, it would have to store the results somewhere - not ...
Auto filter	5/11/2009
Q: I have a spreadsheet that for some reason will not show all the parts when I click on the auto ... A: To be honest, I'm not 100% sure - there do seem to be bugs in auto-filter - we have a sheet in the ...
Replacing text in a cell	5/11/2009
Q: I have a spreadsheet in excel 2003. In cell a1 I have a name for example:Smith,Lisa In cell B2 I ... A: else I might have suggested wild cards - I'm happy that you have a working method and sorry that I ...
Compare then Copy	5/11/2009
Q: I want to compare column A with a date listed in A1. Upon finding the date in column A I want to ... A: it sounds like a vlookup, which is a function that looks up a value in one column and returns ...
Complex Mail-Merge	5/11/2009
Q: I am not sure if what I am trying to do is even possible but here is what I am trying to do: We use ... A: VBA could be used to pick up specific changes and then generate documents based on this - OR if ...
excel validation problem	5/11/2009
Q: i am using excel 2003. using the validation function everything works as it should, until i use the ... A: using the mouse to select a cell and then select another shouldn't stop the validation kicking in - ...
Nesting an IF function in a PMT function	5/10/2009
Q: How do I nest an IF function into a PMT function? I am trying to do my midterm and I just can't get ... A: PMT function isn't one I've used, but using variable values in a funtion is easy enough as you ...
IF	5/10/2009
Q: I'm trying to make my "value if true" add 2 cells together and my "value if false" add 4 cells ... A: It should be something like =if(YourTest,b14+b15,sum(b14:B17)) NOTE that when adding two values I ...
Reveal a cell after another has data	5/8/2009
Q: Is it possible...even using VBA if need be...to have the contents of cell B1 eg only become visible ... A: you could use conditional formatting to set the font to white (or the same colour as the background) ...
Auto filter	5/8/2009
Q: I have a spreadsheet that for some reason will not show all the parts when I click on the auto ... A: The number of items in a list can affect the filter - using data, filter, autofilter - then custom ...
Save with cell contents as file name	5/7/2009
Q: I was sondering if you could help me? I need a macro that saves the workbook I'm working in (and ... A: I'm puzzled, as it LOOKS as though you are reading in the value of the invoice number and setting ...
Save with cell contents as file name	5/7/2009
Q: I was sondering if you could help me? I need a macro that saves the workbook I'm working in (and ... A: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ...
Spreadsheet Ideas?	5/7/2009
Q: I'm currently developing a research paper on the Dead Sea Scrolls. I have to develop a worksheet ... A: Rather difficult to answer - spreadsheets will help you gather and analyise data, and do processing ...
Userform to search thru multiple sgeets	5/7/2009
Q: I've a userform where the user is able to select a date thru the activex calendar. Once he selects ... A: not quite sure what you wanted to do but something like for each sh in activeworkbook.sheets for ...
Snapshot fo Excel conditional formatting	5/7/2009
Q: I have a conditionally formatted cell (range) which changes colour depending on the contents. My ... A: The problem is that the cell has no colour, only format colour - so the ONLY way I can think of ...
moving around by command, in Excel	5/6/2009
Q: how are you? ypu've been very helpful in the past, and I hope you can help again. I use Excel 2003 ... A: Move to would need VBA - do you actually need to select that cell, or are you talking about ...
display list	5/6/2009
Q: I would like to have a specific list available for selecting data that is dependent on an earlier ... A: version shouldn't overly matter - see ...
Keyboard Shortcuts	5/6/2009
Q: I cannot remember the keyboard shortcut on Excel to alter the size (height and width) of cells A: if you type keyboard shortcuts into the help screen in excel you can get a full list - but I'm not ...
time sheet formula	5/6/2009
Q: i am trying to create a spread sheet in excel to add the hours worked by my staff, i would like to ... A: I've provided various answers on this in the past (eg ...
Excel formula	5/5/2009
Q: I have a sheet that calculates a vehicle rental based on different daily charges example X,Y,Z and ... A: At face value, a countif of sumif function would be the way to go, but I would GUESS that you might ...
vba script	5/4/2009
Q: i have a range of values in sheet 1 columnA as shown below. [1]PORT LOUIS1 .1, HB Trib-B1 Slot 403 ... A: Sub aeq() ' Dim holdvar Dim cls For Each cls In Range("a1:A3") cls.Offset(0, 1).Value = ...
Check if a cell is highlight in yellow	5/4/2009
Q: i have a spreadsheet which has some cells highlighted in red and some highlighted in yellow. I want ... A: Can you clarify what form of highlighting we are talking about - IF it's the cell format, then the ...
Count Dates	5/2/2009
Q: . Expert, I have a column in excel with dates, sometime a date repeats more then 2-3 times, for ... A: the result three equates to the number of unique dates presumably? There is no inbuilt function to ...
Investor relations management	5/1/2009
Q: Aidan, are you aware of any add-in or template to manage an investor base? We have just over 300 ... A: I'm not aware of anything - I'd use Access myself, and if not comfortable with it (which is fine) ...
copy cells to another worksheet based on a value	4/30/2009
Q: I have been using the macro code which you posted last year about copying cells to another worksheet ... A: you might have to point me to the original macro, but it's PROBABLY using an offset with that being ...
Assign keyboard shortcut?	4/29/2009
Q: Can I assign keyboard shortcuts in Excel 2007, and if so, how? I cannot find this information ... A: I assume this means to macro commands? If so, you need to see the developer ribbon, which you can ...
Comparing Totals in Excel	4/29/2009
Q: ~ I have a spreadsheet in excel that lists a beginning balance, amount used and remaining for each ... A: Not really clear what you want to do - are you talking about showing the difference between months? ...
excel if-then problem	4/29/2009
Q: excel 2003, (IF)statement problem formatted in currency. =(IF(AND($D98>=D99,$D98<=D100),D103,0)) ... A: A cell FORMATTED as something doesn't change it's underlying value, I would tend to use a ROUND ...
excel column is not doing the correct sum in excel 2003	4/29/2009
Q: I am doing the export to excel using c# code but the problem is that columns that contain some ... A: I'm not sure I follow the question - if the C# code is creating the file, it should be able to ...
Random Assignment	4/28/2009
Q: I am trying to random assign and not repeat a hotel room list for students. I have 32 students and 8 ... A: try http://en.allexperts.com/q/Excel-1059/Excel-Random-Assignment.htm which has a solution to a ...
Autofilter	4/27/2009
Q: I am using excel 2003 and for some reason the AUtofilter does not work when selecting specific ... A: If the fields concerned are dates, then these are serial numbers effectively - 2009 is equates to ...
Copying data from multiple worksheets to a master without blanks.	4/26/2009
Q: I am moderately savvy with Excel basics but I am stuck now. I have had fits trying to make this ... A: The basic principle I'm happy with, BUT your output is subtly different to the input - you have week ...
Automatically opening my personal.xlsm	4/24/2009
Q: I am trying to get my personal.xlsm file to open when I open Excel for macro execution. In Excel ... A: I'm confused - the CORRECT file name for the personal macro file is personal.xlsB not M? If the ...
Excel	4/24/2009
Q: I have time series data collected every second for 1 day. I need 10 second averages. The time ... A: The important thing is that you have a method that works - a macro method would simply speed up the ...
Adding a formula for quarters	4/24/2009
Q: In Column A I have end of month dates from December to December, in Column B I have Monthly Return ... A: My email is aidan.heritage@virgin.net - if you'd like to send me a sample of what you are doing and ...
Excel VBA Code Help- find and copy from mult sheets	4/23/2009
Q: I am working with an Excel file that has data in a lot of different sheets. All the data is ... A: Moving to a larger number of questions, I'd suggest insert a userform into your program - this could ...
IF count then	4/23/2009
Q: trying to work a simple formula in excel to count cells in a range od 3 cells, then return S if ... A: what did you want to count in the range of cells, and is it always the same three? COUNTA will ...
Excel VBA Code Help- find and copy from mult sheets	4/22/2009
Q: I am working with an Excel file that has data in a lot of different sheets. All the data is ... A: At a quick glance, the error seems to be For sRow = 1 To Range("D65536").End(xlUp).Row which ...
replace function question	4/22/2009
Q: I am trying to work on address labels and I am working on mail merge however for me it would be ... A: try the data menu, then text to colums - choose delimited, with commas - this will THEN give you a ...
Regression Macro	4/22/2009
Q: Just want to thank you advance for your help. I have written a macro and it works well. When I run ... A: You are referencing the sheet directly - eg Sheets("Sheet1").Select try for each sh in ...
Reverse Lookup?	4/22/2009
Q: I have a spreadsheet with Customer Names down the left, and Branch locations across the top. I used ... A: It's do-able but I'm not sure how easy it's going to be to describe - still, I'll do my best - it ...
Excel	4/21/2009
Q: I have time series data collected every second for 1 day. I need 10 second averages. The time ... A: Interesting question - do we have one measurement every second, or are there gaps or more than one ...
replace function question	4/21/2009
Q: I am trying to work on address labels and I am working on mail merge however for me it would be ... A: I'm puzzled - the data is comma seperated, so I assume you are dropping the fields you need into ...
Excel Minimize	4/21/2009
Q: Every time I open a document/spreadsheet it is a small minimized window, how do I fix this to open ... A: I would IMAGINE that you have Excel running when you shut down the computer, probably minimized to ...
VBA code: Inserting a row	4/21/2009
Q: I'm working with a datalogger and trying to write a macro to take daily summaries: max, min, ... A: Sub Inserter() Set currentcell = Worksheets("Sheet1").Range("A1") Do While Not ...
Ctrl+C in Excel Vista	4/21/2009
Q: i use Ctrl+C very often but know with the vista when i do it don´t copy, instead opens a dialogue ... A: office 2007 still has the same shortcuts - as I proved earlier today by answering a question for ...
how to create formula to ignore bkts.	4/21/2009
Q: is it possible to create a formula that ignores what you type into a bracket. EX. Cell A1= 10 Cell ... A: =A1-SUM(VALUE(LEFT(A2:H3,SEARCH(" ",A2:H3)-1))) ) as an array formula (ctrl shift enter) would I ...
combining multiple cells into single entity	4/21/2009
Q: I have a two-column chart with 15000 rows with the following information. Column A is part number, ... A: =IF(COUNTIF(A2:$A$7,A2)=1,combinetext(A2,$A$2:$A$7,1),"") would be the function I'd use - with ...
Workbook Links	4/21/2009
Q: I want to link my workbooks together so i only have to update one spreadsheet and it will update all ... A: to create a link, open both sheets then type the = sign to start the formula, use the window option ...
Adding a formula for quarters	4/20/2009
Q: In Column A I have end of month dates from December to December, in Column B I have Monthly Return ... A: If I follow this correctly, then jan, apr, jul and Oct payments would need to take account of this ...
Video Clip	4/20/2009
Q: If I shoot a small video clip(2-3 min.) with my personal camera, is it possible to insert it and ... A: You can insert objects into spreadsheets, so a video clip would be fine - it would have to be double ...
Compare two columns and find the dups	4/20/2009
Q: I have two columns, item# and quantity sold, e.g Item# QTy 5056 7 5056 5 I want ... A: the formula will count all entries in the range A1:A4000 that match B1 exactly- but any leading or ...
if two columns are identical, add the values	4/20/2009
Q: I have two columns, item# and quantity sold, e.g Item# QTy 5056 7 5056 5 I want ... A: Pivot tables are very useful, for just this sort of situation (amongst others) - use the insert ...
Runtime Error 13 type mismatch	4/20/2009
Q: I am getting a Runtime Error 13 type mismatch error when I run my code. I have attached the code ... A: If the error is in this chunk ERROR STARTS HERE---> If Sheets("Document").Range("A3").Offset(0, ...
if two columns are identical, add the values	4/20/2009
Q: I have two columns, item# and quantity sold, e.g Item# QTy 5056 7 5056 5 I want ... A: I'm guessing NOT just for the item 5056 - if it IS just 5056 then a sumif will do it ...
Check box idiosyncracies	4/19/2009
Q: I don't get it. I'm currently updating a pretty involved spreadsheet that I first created a few ... A: There were (as far as I remember) two ways of inserting check boxes - one was the CONTROL toolbox ...
Delete rows macro help	4/16/2009
Q: I have a data table with several thousand rows that I want to export to another new, blank workbook ... A: Lets say the active cell is k2 - when your macro deletes k2, the original k3 becomes the new k2, so ...
is it Possible to Expire Excel Sheet on Specific Time ?	4/16/2009
Q: i made 1 small programme and i want to share it with others but with time period. it is Possible to ... A: This is a question I get asked fairly regularly - it's not something I would normally do as any ...
vlook up query	4/16/2009
Q: i am totallly new to vlookups. I work in accountancy. I have list of figures debits and credits ... A: I'm not sure what you are actually trying to produce, but a lookup table doesn't sound like the ...
Automatically removal of a specific character in excel	4/16/2009
Q: I have data formated the following way and wish to remove the dots "." before each number resulting ... A: The sort will be an issue in that by including non numeric characters, the values are treated as ...
Delete rows macro help	4/15/2009
Q: I have a data table with several thousand rows that I want to export to another new, blank workbook ... A: Your method will run into problems as once you have deleted a cell, the active cell changes - try ...
Column width will not stay at 14.11	4/15/2009
Q: I've been working with Excel since 1995 & have never come across this. Currently using Excel 2007 on ... A: Very odd- I've just set up a blank sheet, set it to concourse, and modified the width - all OK. I ...
Excel Formula	4/15/2009
Q: I have a spreadsheet with server names in column A, and their per disk usage in column B. Column A ... A: You could do a sum if, but MUCH easier would be to use a pivot table - you didn't specify version, ...
Delete rows macro help	4/15/2009
Q: I have a data table with several thousand rows that I want to export to another new, blank workbook ... A: You can delete the data, but why do so - why not simply check the value of column K and ONLY export ...
Excel	4/15/2009
Q: I have used the SUMPRODUCT function but the problem is one row has dates and I can not find a way to ... A: You don't explain the function very clearly - it SOUNDS like a 2 level count if? ...
excel formula	4/15/2009
Q: I'm trying to build a spreadsheet that will automatically put names in teamsheets. On sheet 1 in ... A: Use the MATCH worksheet function to return the row from the data set =match(TheValue,DataSet,0) ...
Excel - Help with summing range of values in lookup table	4/15/2009
Q: I have a dynamically changing date range which the user may enter into 2 different cells. I need to ... A: If I follow this you want to add up all cells in a range where another column in the same range is ...
recipe costing worksheet	4/15/2009
Q: I wish to create a recipe costing worksheet. I would like to use a table so that I can add ... A: It sounds a great idea, but I've spent some time thinking about this and haven't managed to come up ...
Pivot tables not refreshing when buried in vba	4/14/2009
Q: This is my first time asking a question here; hopefully I'm giving enough info. I am building a ... A: The code looks fine, but there isn't anything in this code seting the ItemName string value - and ...
excel - export data	4/14/2009
Q: I have the code to export all the data from my spreadsheet which works but I only want to export ... A: as you gave me a full blown, reasonably complex procedure I assumed you were Ok with VBA - so my ...
xls to image	4/14/2009
Q: How can I save an xls as an image file or convert a worksheet to an image file? A: Not sure what you wanted to use the image for, as that could have a bearing on the way to do what ...
Question on excel working	4/14/2009
Q: 1)Is there any function that would convert numerical letters into alphabetical format Eg: 626 in ... A: (1) nothing built into Excel I'm afraid but see http://www.ozgrid.com/VBA/ValueToWords.htm for a way ...
Excel formula question	4/14/2009
Q: I am constructing a database of respondent for market research. One of the questions is do you have ... A: I have a slight disadvantage in that I'm in the UK so don't know the grade structure, but I think ...
concatenar formato	4/14/2009
Q: Estoy tratando de concatenar la informaciÃ³n (texto y nÃºmeros), que incluye nÃºmeros que son el ... A: I THINK having read your translation and checked with my Spanish speaking brother, that you are ...
Counter in VBA	4/14/2009
Q: I have an Excel spreadsheet witch I use for my wage calculations. Annually I have to submit a ... A: I'm not sure what would enable you to work out where to apply this but the use of the CELLS option ...
Macro Problem	4/14/2009
Q: I have a question. I am currently using a macro (listed below) to gather information from a ... A: I presume by double spaces you mean the line feed codes that you are actually sending - so dim ...
Data Vaidation and vlookup problem	4/14/2009
Q: I am again facing an aching problem, its that, i have a drop down list in cell A1 using the data ... A: Not clear how the data appears, but IF it's always over the same number of rows, but just a ...
Text boxes	4/14/2009
Q: I'm pretty convinced there is no way to do this, but I thought it wouldn't hurt to post the question ... A: You are right that no formulas can do this (at least, not as far as I know) but VBA could - I'd be ...
Excel Programming Issue	4/13/2009
Q: Within my workbook, I have a sheet named Security List. In that sheet, I have a list of values going ... A: I'm not sure I totally follow the requirements as you state you want to store "that value into cell ...
Remove Duplicates in Database on Excel	4/13/2009
Q: I have a very large database for contest entries (about 30,000 entries). Each row is one entry and ... A: Not familiar with Mac versions, so I'll give two possible answers - one data, filter, advanced ...
Vlookup with Codes and Rates	4/13/2009
Q: Im dealing with codes and rates. Currently, when I input a code on worksheet (1), it returns the ... A: see the other reply (!) - you could also use a countif ...
excel - export data	4/13/2009
Q: I have the code to export all the data from my spreadsheet which works but I only want to export ... A: To write selected data for rowNdx=startRowtoEndRow write #Fnum,cells(rowndx,1),cells(rowndx,2) etc ...
ISERROR	4/13/2009
Q: Can you please provide the correct formula for iserror (or any other function) to add to my current ... A: It's not clear where the error could occur, so the simplest method (if slightly long) is ...
conditional formatting number of decimal places based on a table value	4/13/2009
Q: I noticed your answer on a related question, and hoped this would be easy for you (not for me!). I ... A: Ah, I'd misread that - I'm not sure I can think of any other easy ways round this - the use of the ...
conditional formatting number of decimal places based on a table value	4/12/2009
Q: I noticed your answer on a related question, and hoped this would be easy for you (not for me!). I ... A: I'm afraid the answer is that you cannot do it with conditional formatting - but I would suggest ...
Excel Charts and Data to PPT Automatically	4/11/2009
Q: Experts Is it possible with macro to Copy Chart and Range from the Sheets to PPT Means, i have ... A: It would certainly be possible to control a PowerPoint presentation via VBA, but I have no ...
Vlookup with Codes and Rates	4/11/2009
Q: Im dealing with codes and rates. Currently, when I input a code on worksheet (1), it returns the ... A: vlookup as you have seen will return the first matching entry - so the solution is EITHER to amend ...
frequency in excel	4/10/2009
Q: ive been given some work by my university and im having a lot of problems using excel. one of the ... A: If this is simply a question of counting particular entries, then I'd use a pivot table to do this - ...
Sorting in excel office 2007	4/10/2009
Q: When I sort (1 2 3 1a 2a 2b 2c ) I get just that. I want the sort to look like (1 1a 2 2a 2b 2c 3) ... A: Numbers get sorted first, then text - so what you are seeing is by design - to get round it, create ...
VLookup	4/10/2009
Q: ) I need a specific value looked up, I'll explain. My report looks like: Project # Budget ... A: two options - one, select the column and do a search and replace - replace all spaces with NOTHING. ...
excel	4/10/2009
Q: I AM WORKINN IN AN ORGANISATION'S HR DEPARTMENT. I HAVE CONFIGURED AN ATTENDANCE SHEET WHICH IS ... A: How does the sheet record that you are present - this has a bearing on the answer - it is BASICALLY ...
Import Webpage content	4/10/2009
Q: With great hopes I'm contacting you. I hope, wish and pray that you should be able to help me with ... A: I'd be inclined to do this directly with web-queries, which you already have in place - simply have ...
open textfile in local drive and save it as a delimited file	4/10/2009
Q: Private Sub CommandButton2_Click() Dim strLoad As String Dim bk As Workbook Dim sPath As String ... A: The OPEN command will open ONE file, but you've tried to run a directory listing as far as I can ...
Auto populating inverted sheet 1 to sheet 2	4/9/2009
Q: I'm trying to create a chess board, in which I can switch from sheet 1 to 2, and essentially turn ... A: Nearly everything is possible - some things just take time! This one is fairly simple - I'm ...
Data from multiple spreadsheets	4/9/2009
Q: I need to compile data from about 2000 individual spreadsheets into one. I need to pick a range of ... A: Yes, you can do this with a macro - are all the files going to be in the same location? If so you ...
Rearranging text data in a cell	4/9/2009
Q: rearranging order of text string inside a cell ... A: I'm sure it should be possible to do this - unfortunately, AllExperts allows no more than 3 days to ...
Excel File Names	4/9/2009
Q: Is there a way to automatically add the date and time to an excel file name so that each time a ... A: You could use the before save event of the workbook to trap the save and run a Save As instead? BUT ...
Formula for data replication	4/9/2009
Q: I have to Create a format A and B and under Hours, i need to be able to enter hours worked and then ... A: Not exactly sure what you need here- are you talking about having as many rows as you need and have ...
logic commands	4/9/2009
Q: Can you see what is wrong in this formula? ... A: It has too many brackets - there is a limit to a nest of 7 levels of bracket - however, it's also ...
VLookup	4/9/2009
Q: ) I need a specific value looked up, I'll explain. My report looks like: Project # Budget ... A: I presume you are needing to lookup the COMBINED project number and budget category? possibly the ...
percentage help in excel	4/9/2009
Q: I have data in (d4:k4) I need to get the percentage of d4 compared to the total of D4:K4. the ... A: The formula you need is =D4/SUM($D$4:$K$4) The cell should be formatted as percentage to ...
exel comment box	4/8/2009
Q: when i insert a comment on any cell the name Phil: comes up every time. How can I permanently erase ... A: The name is the indicator of the person who made the comment, it's taken from tools, options, ...
Changeable Sums	4/8/2009
Q: Okay so I am summing up a table and I have full sum at the bottom. I don't want to lose that value ... A: slightly confusing explanation, but it SOUNDS like you want a SUMIF formula - which is ...
Excel Forumla	4/8/2009
Q: I have 2 sheets in a workbook. The rows on sheet 2 equal the values on sheet one. The formula I ... A: Why do you need to duplicate the information - would not a SUM function do what you need? It's also ...
count inventory using excel	4/8/2009
Q: I have to make an inventory list (Here is the start of it Attached) using excel. I just need to know ... A: There is no attachment as allexperts doesn't allow that - my email is aidan.heritage@virgin.net ...
Excel help	4/8/2009
Q: When I go to print out my spreadsheet some of the dates come out as ####. What does that mean? A: It means the column isn't wide enough to display the data - so rather than display 1 January for ...
Excel 2000	4/8/2009
Q: I am importing a text file daily into an excel spreadsheet but everytime the column widths adjust - ... A: How are you importing the text? And is the file always the same name? I'd be inclined to do it via ...
Question about Random Numbers	4/8/2009
Q: cell A1:A50 have random numbers generated by formula ( 0 to 20 ), now what ever we do these numbers ... A: I don't quite follow, but it sounds as though you want to run a series of random number generations ...
Percentage Formula	4/7/2009
Q: Example: If I have 4 cells (A1-A4) and I have to put completion dates in those cells; each one of ... A: My only SLIGHT problem is getting excel to know you want to deal with 4 cells - ideally you'd have ...
Setting a range of cells in Excel	4/7/2009
Q: I can't seem to figure out how to set a certain range of cells in excel; specifically, I need to ... A: You can either have the entire column OR a given range, but unfortunately cannot mix them - I tend ...
Date column for 24 hours	4/7/2009
Q: I have data that is in 24 hour increments and I need to add a date column to the spreadsheet. I am ... A: I assume the data is JUST in time format, rather than day and time (if so, it's easy, as you just ...
name of person with highest score	4/7/2009
Q: Using Excel2007 functions, what would be the most efficient formula for identifying the name of the ... A: =INDEX(A:A,MATCH(LARGE(B:B,1),B:B,0),1) will do it for you - slightly more efficient if you change ...
Drawing objects in Excel	4/7/2009
Q: Hope you can help me with this one. We use Excel 2003 and while using this application for ... A: I've spend a fair amount of time trying to research this for you, slightly hampered by the fact that ...
excel	4/7/2009
Q: Aidan, I wonder if you could help me with an excel formula. I have a list of items in column A and ... A: I think this would need VBA to achieve with a custom function, but I'm not 100% sure I'm clear on ...
Clear contents of blank cells	4/6/2009
Q: I have a spreadsheet that once data is placed into it there remain some blank cells with underlying ... A: I THINK you'll find that these "blank" cells aren't blank at all - they simply contain one (or more) ...
Macro for "SaveAs" using a cell reference	4/6/2009
Q: My company has a file structure that accords every project a unique number and this number is also ... A: ActiveWorkbook.SaveAs Filename:="N:\Projects\" & range("A1").value & "\Base info.xls" should do it ...
ABOU EXCEL FORMULA	4/6/2009
Q: SIR, IF 0N 01/01/2009 MY WORKING EXPERIENCE IS 10 YEARS, THEN WHICH FORMULAE SHOULD I PUT SO THAT ON ... A: I tend to use the YEARFRAC function for date handling - this is in the analysis toolpak (tools, ...
Closing Excel 2000 workbook	4/6/2009
Q: I want to automatically run a certain Macro and then save the workbook when I close the workbook. ... A: Yes it's possible - the CLOSE event of the workbook would be the best place for this to happen - alt ...
Cell Value Comparison	4/6/2009
Q: I need some help from you. I really wish and hope you can help me on this. This is solve a very big ... A: Shouldn't need a macro, as you can use conditional formatting - use the home ribbon, then the ...
Automated timestamp saving problem	4/5/2009
Q: This explanation from your site: http://en.allexperts.com/q/Excel-1059/Automated-Time-Stamp.htm I ... A: As I didn't write the original answer...! IF you save a file, then that file MUST have the data you ...
macros	4/5/2009
Q: i need more detailed code for printing combinations for huge set of rows...can i get it...for ... A: Do you want the output in the form show (with brackets) and are we limited to 4 columns or may it ...
Automatically Hiding Rows	4/4/2009
Q: I have a drop list (using validation) in a cell with Yes, No and N/A as options. If a person selects ... A: I would be inclined to use conditional formatting to make the questions "invisible" (i.e. same ...
Excel 2003	4/4/2009
Q: I created a file for work and another location is now using it. I locked the sheets but now when I ... A: You've answered your own question - it does make a difference, so unshare it and all will be well ...
macros and matching a cell to a column	4/4/2009
Q: B. if there is a match to write the cell # match in sheet 2 cell A1 and if there is no match to ... A: I think I'm missing something here- the macro says if ...
Comparing Excel files	4/4/2009
Q: I run SAS programs to output data to excel files. Within SAS I open an excel template with a DOS ... A: When the machine is locked the display isn't on, so that is PROBABLY the reason - I don't know what ...
Comparing Excel files	4/3/2009
Q: I run SAS programs to output data to excel files. Within SAS I open an excel template with a DOS ... A: Difficult to answer - I've had experience of this message myself, but it has only manifested itself ...
how to expire Excel file after certain date	3/31/2009
Q: I don't Know Whether my Question have a Sense or not. But it is Possible to Expire Excel Sheet on ... A: It will need macros - I'd be inclined to have the master sheet set to VERY HIDDEN - so only if the ...
Quary	3/31/2009
Q: I have a excel sheet which has 6000 rows full of dada. i want every row in a new sheet or excel file A: I must be missing something here - EITHER select all (ctrl A) and copy, then paste to new location, ...
hilight input if it was from the list stated	3/31/2009
Q: I have a lot of part no. to key in the excel file. Some of the part no. is under special control. So ... A: conditional formatting should work - use the formula option, and use the COUNTIF worksheet function ...
Deleting Cells and Parsing left over information	3/30/2009
Q: I do a weekly report that I base on results that I do with Internet Explorer. I can then cut and ... A: The pictures always come through very small on AllExperts, so it's almost impossible to see what you ...
Multiple Formulas.	3/30/2009
Q: I am trying to put a rather large formula into a cell. I'm trying to sum 4 different columns, from ... A: Er, yes, it is rather long. You don't need multiple sums (use commas to seperate the ranges eg ...
Excel Add in	3/30/2009
Q: 2 part question- I have an add An excel add Easy filter, but it will not record when I try to ... A: Not easily - but if you record the macro, you can put it within the section I outlined - eg for ...
Comparing cells and then assigning score	3/30/2009
Q: I have a large spreadsheet with student test data. The key is listed at the top, separated by ... A: I'm pretty sure this doesn't need a macro but can be done with a formula - IF I follow this ...
Excel Add in	3/30/2009
Q: 2 part question- I have an add An excel add Easy filter, but it will not record when I try to ... A: Recording macros will only record some excel actions (not even all excel actions) but I certainly ...
how to expire Excel file after certain date	3/30/2009
Q: I don't Know Whether my Question have a Sense or not. But it is Possible to Expire Excel Sheet on ... A: You seem to have posted this question in a number of places, and I'm not sure WHY you want to do ...
Excel Query	3/28/2009
Q: I have to create an excel format in which, When some one input some particular number in a ... A: This question is difficult to understand, and you say GREATER than 33, but I think you mean less ...
Stock Vesting Calculation	3/27/2009
Q: I need to know how to create a formula that calculates vested stock for a 4yr vest, 25% 1yr cliff, ... A: I'm not familiar with the math that would be needed (or most of the terms you used!) - but see ...
changeable data validation list range	3/27/2009
Q: I am trying to create a automatically changeable data validation list range. The list range will ... A: I would do this with a dynamic range - defined by insert, names - the name can be anything, but ...
sorting problem	3/26/2009
Q: I have id values in column A (every value is different) that I need to find in column B, which has ... A: Not too complicated for excel, but a bit complicated for me to follow (but it's been a long day at ...
excel tabs	3/26/2009
Q: I would like to know if there is any way I can create multiple layers of tabs. I have a workbook ... A: It COULD be done - it would need VBA to achieve it though, so would ONLY work if the users enabled ...
excel dates	3/26/2009
Q: In Excel 2007, I used format cells, dates to format my date columns to read mm/dd/yy. However, when ... A: 032609 is a decimal number, which to excel means you have typed a whole number - which it is happy ...
VBA	3/26/2009
Q: I am currently working on an inventory checklist for my work. I need help in writing a vba code to ... A: The great thing about Excel VBA is that you can record macros, so you can see the syntax for what ...
execel	3/26/2009
Q: how can i add a value in the cell am on it and another value in another cell in the same sheet? A: Not sure what you are asking - the formula =a1+b1 would add two values together. clicking on one ...
removing columns	3/26/2009
Q: Am using Excel 2007 in XP. When I hit end+home I go to cell WXP961. The last column I actually have ... A: You will at some point have entered data to the right of the area - USUALLY by formatting an entire ...
Filter Macro in Excel '07	3/25/2009
Q: I am currently building a model to help calculate the cost of hiring an external candidate for an ... A: Obviously I don't know the data structure, but to me it SOUNDS as though the query to Access should ...
Adding rows in a column automatically	3/25/2009
Q: I have one column that has rows with numbers; however, the rows are in groups and each group will ... A: Sub totalme() Dim rowcounter As Long Dim columncounter columncounter = ActiveCell.Column Dim ...
SUMIF Text	3/25/2009
Q: In column c1:C10 I have a list of names and D1:d10 a list of Text based serial numbers. In f1 i ... A: It SOUNDS as though you don't want to sum, merely to lookup the appropriate value? if so, it's ...
lists	3/24/2009
Q: Aidan i just about get by with the computer on a need to know bases but there is something i would ... A: 2 ways of doing this - ONE, and the easiest, is to use a vlookup function - you enter a value, the ...
Excel Autocomplete & =value()	3/24/2009
Q: Basically we run a martial arts class, on one sheet with have members and on the other we have a ... A: see http://www.ozgrid.com/Excel/autocomplete-validation.htm for an explanation of the first part - ...
excel formula	3/24/2009
Q: I need help configuring a formula if it's even possible.... I have 2 receivables columns; amt ... A: It's certainly possible - it sounds like it is actually just a case of adding the two values up - a ...
Installing Microsoft Office	3/24/2009
Q: From: You helped me out with a problem before and I hope you can help me again. I recently ... A: Difficult - the software needs to be installed as items get populated in the registry and system ...
Trying to do inventory using Excel, poss. use Vlookup	3/23/2009
Q: I'm not sure if this question has been answered before; I'm still searching through the archives. ... A: I think a SUMIF function would do what you want - this would SUM entries in one column where another ...
Excel Splitting Rows	3/23/2009
Q: I need help splitting a 15,000 row database into two databses by selecting every third record. I've ... A: A macro would be OK to do it, but you'd need to know how to write one - the SIMPLEST route would be ...
Excel formula...need help	3/22/2009
Q: I have two sheet. In one sheet i have the data base details which has 3 company names, code to each ... A: I'd be inclined to use a pivot table to do the data analysis, which would make the job automatic. ...
counting cells and conditional formatting	3/21/2009
Q: I have a relatively simple need which could come in quite handy for many Excel needs. However, I'm ... A: You are right that there isn't any easy solution to this, BUT as you are using formulas to set the ...
Excel Functions	3/21/2009
Q: how are you doing today? I have the 2000 version of Excel and am using Windows XP. I am new to ... A: You cannot easily have a formula automatically down all 65,536 cells - and you probably wouldn't ...
Excel Formula	3/20/2009
Q: I need to know how to fashion a function that will give me true/false answers for multiple ... A: I had assumed you had a list of values in another area - that would be the way to go - lets say 1 to ...
Excel Formula	3/20/2009
Q: I need to know how to fashion a function that will give me true/false answers for multiple ... A: It doesn't quite make sense, but are you BASICALLY checking one value against a list of values to ...
Excel	3/20/2009
Q: I have a problem in excel, i want to convert text entered into a cell and converts that text into ... A: I wouldn't (probably) use VBA for this - I'd simply use a lookup table with the vlookup worksheet ...
find min value where	3/20/2009
Q: I am looking for a formula that will find me the smallest number within a set of values based on ... A: I don't think I explained the formula before - the MIN version returns the value of the cell if it's ...
Highlighting a row selection	3/19/2009
Q: I need to be able to automatically highlight an entire row whenever I click anywhere within that ... A: The EASIEST way would be to simply select the entire row when you click into it - which can be done ...
Locking cells?	3/19/2009
Q: I know this has probably been answered before, and I don’t want you to be redundant, so if you can ... A: I don't recall answering this as such before - but I can see the problem - I'd be INCLINED to make ...
Master Tab	3/19/2009
Q: I'm not very familiar with Microsoft Excel but I'm making a workbook that has thiry one tabs ... A: Not sure what you want the master tab to do - however, to create a copy of an existing sheet, right ...
Transfering file from Mainframe to PC automatically in excel format	3/19/2009
Q: I need to transfer Mainframe file (PS) to particular directory automatically in excel format. This ... A: SOUNDS like it is more of a mainframe query - what program do you use to access the mainframe (in my ...
Excel	3/18/2009
Q: Is there a way of excel moving from one cell to another automatically. What I have got is one cell ... A: I'm not sure what you are doing at the moment to get the values to go across to the two cells, but ...
Getting a cell to reference a table	3/18/2009
Q: I have a table divided in 4 columns for each week. I would like to be able to click on the cell ... A: If you want something to update by clicking, it will need some VBA to achieve it - possibly easier ...
macros and matching a cell to a column	3/18/2009
Q: B. if there is a match to write the cell # match in sheet 2 cell A1 and if there is no match to ... A: I think it's something like sub Matcher if ...
Character Limit in Word Drop Down Menus	3/18/2009
Q: Utilizing Word 2007, I have found i need to include some lengthy sentences in a drop down i created, ... A: WHY do you need to include lengthy sentances in a drop down? As the drop down control is an activeX ...
Excel Worksheet w tables	3/18/2009
Q: I have 5 worksheets (Main,A,B,C,D). I have a dropdown in Main listing A,B,C,D. I have TABLES with ... A: from the description it sounds as though the indirect worksheet function would be what you need if ...
Excel Data Swap	3/18/2009
Q: I know its possible to to take data from one cell and swap it with data in another cell. Is it ... A: Not too clear how the swap is to take place - one option COULD be to select the block of cells you ...
EXCEL	3/17/2009
Q: Im trying to delete the parenthesis in an entire column of approx. 10,000 Phone numbers, ex. ... A: =SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","_") will do this for you where A1 is the cell you want to ...
Mandatory entry from data validation list	3/17/2009
Q: I have a data validation list of text and want to make it mandatory that an entry is made. ... A: Options are (1) save the file with a default answer (2) use conditional formatting to cause any ...
Excel Calculations	3/16/2009
Q: Well first thanks for even taking the time to ready my question. Let me take a moment to relay my ... A: You need the WORKDAY function to do what you need - this is an add-in but a standard one - tools, ...
Excel Formulas	3/16/2009
Q: I have cells that resemble the following characters "80.0% (4)" less the quotes. I am trying to ... A: try ...
Copying from multiple sequentially named worksheets to one mastersheet within same workbook.	3/16/2009
Q: Is there a way to copy a specific cell range (ex: BB2343:DD2343) from sequentially named worksheets ... A: Sorry for the delay in replying - took me time to find time to write the macro! and even so, this ...
Variable Function Length?	3/16/2009
Q: I was wondering if there is a way to make a formula fill down a column to a number of rows based on ... A: You can use a macro to fill a formula, or to enter it in the event of data entry, but personally I ...
Excel 2007 version	3/15/2009
Q: I recently visited Ancestry.com. A file I was looking at attached itself to my Excel 2007 as a new ... A: If you use the FIND option, then you can select a file from this list and delete it (click it once, ...
Offset() imbedded?	3/15/2009
Q: I can't figure out a way to write a formula for this type of problem. A10: cell consiting either ... A: I've assumed there is a typo in your question, as if A10 was 9, it PRESUMABLY woud be a nine cell ...
Excel 2007 version	3/14/2009
Q: I recently visited Ancestry.com. A file I was looking at attached itself to my Excel 2007 as a new ... A: You will (probably) have saved the file in the start up directory of excel - probably the easiest ...
Question	3/14/2009
Q: I need to produce a print button on an Excel worksheet. I can create the button and I know the ... A: With ActiveSheet.PageSetup .PrintQuality = -3 .Draft = True .BlackAndWhite = ...
VBA problem	3/13/2009
Q: I am trying to make a cell recognise a date put in it and then to cut the row and move it to another ... A: Private Sub Worksheet_Change(ByVal Target As Range) Dim offsetvar offsetvar = ...
Two columns into one in Excel	3/13/2009
Q: I have this problem but couldn't solve. I am sure you being expertise in the filed could definitely ... A: You could do it with VBA, or possibly easier with a simple formula - which would go in a third ...
Copying from multiple sequentially named worksheets to one mastersheet within same workbook.	3/13/2009
Q: Is there a way to copy a specific cell range (ex: BB2343:DD2343) from sequentially named worksheets ... A: easy enough to do with macros - for each sheet in the workbook, if it's not the master sheet, copy ...
Moving Excel Comments	3/13/2009
Q: When editing an excel comment I am able to move that comment around the spreadsheet, however when I ... A: Comments are designed to be comments on a particular cell, so will always link to that cell - if you ...
Excel Question	3/13/2009
Q: Here is my question. I am putting together an insurance inventory for farmers. I have drop down ... A: Not sure what the source for the drop down lists are - I'd have it as a named range, and to the side ...
icons	3/13/2009
Q: I'm attempting to learn from the Professor teaches Office 2003. I started with excel, and its like I ... A: Assuming you have excel installed, move the mouse pointer over an icon and wait - it will pop up a ...
Excel workbook with links crashing	3/13/2009
Q: We are having a recurring problem with a spreadsheet which has links to approx 15 other workbooks. ... A: I can see that this is an issue faced by others ...
Excel 2007 - VBA programming	3/12/2009
Q: I hope you can help with VBA questions. It wasn't specified if you did or not in your profile. So ... A: Yes, I'm happy to do VBA - but first I'd need a little more information - is it always the number 5 ...
find min value where	3/12/2009
Q: I am looking for a formula that will find me the smallest number within a set of values based on ... A: I've just tested it and I would expect it works fine for me - dont forget to press ctrl shift enter ...
Excel Macro	3/12/2009
Q: I have a spreadsheet a lot of data to be encoded. There is a macro which does this. I want to ... A: the word selection has no meaning on it's own, so it's interpreted as an undeclared variable, which ...
refEdit values to Word & incorrect date format	3/12/2009
Q: I have a UserForm in Excel (2003 on WinXP SP2) which contains 6 refEdit fields to allow selection of ... A: OK, so we can be sure the date is coming in as a date which is good! dateserial is something I ...
find min value where	3/11/2009
Q: I am looking for a formula that will find me the smallest number within a set of values based on ... A: I've probably done this before other ways, but I think I'd do it (this time!) with an array formula ...
Formula Keeps giving Circular Reference	3/11/2009
Q: =VLOOKUP('Quote Detail IP'!$I$14&'Quote Detail IP'!$I$11&'Quote Detail IP'!$I$5&'Quote Detail ... A: A circular reference means that a formula is referring to itself, so getting into a potential ...
refEdit values to Word & incorrect date format	3/11/2009
Q: I have a UserForm in Excel (2003 on WinXP SP2) which contains 6 refEdit fields to allow selection of ... A: we perhaps need to backtrack slightly - where does the frmCaseNote.rfeFirstDate.Value get it's ...
Counting with check boxes	3/10/2009
Q: I currently use an excel spreadsheet to track customer contacts at work. However, I would like a way ... A: I THINK I'd be inclined to have a small macro, attached to a button on the spreadsheet, which when ...
Count the first instance of a value only	3/10/2009
Q: once again in need of your awe inspiring knowledge! I am trying to count the number of ... A: Your count is almost right but would return the same number at each instance of the organisation - ...
Iterative series addition to a chart	3/10/2009
Q: I have an iterative macro that results in a number of pairs of data columns (anywhere from 4 to 40 ... A: the new series adds a new series, so it won't be 3 anymore - SO your macro adds a new series (which ...
refEdit values to Word & incorrect date format	3/10/2009
Q: I have a UserForm in Excel (2003 on WinXP SP2) which contains 6 refEdit fields to allow selection of ... A: Your method is NEARLY going to work, but you are trying to change the value of the cell in excel, ...
Deleting Duplicate rows using 2 columns	3/9/2009
Q: My problem is trying to find a vba solution for deleting duplicates based on 2 criteria. Column "C" ... A: Well spotted - I had in my head this was column A, and wrote the macro accordingly, then realised my ...
Copying Excel formula using drag down option	3/9/2009
Q: I'm trying to copy a formula but dragging it down to subsequent cells. The formula is =D2/B1 and as ... A: The formula you need is =D2/$B$1 to save typing the dollars, press the F4 key with the cursor on ...
auto copy data between sheets	3/9/2009
Q: I have an excel workbook that contains 25+ separate sheets all formatted the same way. I want to ... A: Sorry for the delay in replying - it took me some time to find the time to write the macro you need ...
Deleting Duplicate rows using 2 columns	3/8/2009
Q: My problem is trying to find a vba solution for deleting duplicates based on 2 criteria. Column "C" ... A: Sub Deleter() Set currentcell = Worksheets("Sheet1").Range("c2") Do While Not IsEmpty(currentcell) ...
removing spaces	3/7/2009
Q: I am a little more than a casual Excel user, but by no means an expert, and know very little VB. I ... A: Sorry - I misread the question and THOUGHT you'd said the cell was formatted as a number rather than ...
Deleting Duplicate Records based on 2 columns	3/6/2009
Q: An answer to yourMy problem is trying to find a vba solution for deleting duplicates based on 2 ... A: Your clarification didn't really clarify - I MEANT to ask but probably failed to do so, what ...
removing spaces	3/6/2009
Q: I am a little more than a casual Excel user, but by no means an expert, and know very little VB. I ... A: the value you are seeing is the cell in scientific notation - just increase the cell size and it ...
Deleting Duplicate rows using 2 columns	3/6/2009
Q: My problem is trying to find a vba solution for deleting duplicates based on 2 criteria. Column "C" ... A: So the process would be count the number of instances of the entry and IF there are comments OR ...
Ignoring blank values in a formula	3/6/2009
Q: I am trying to compare how scores in a questionnaire have changed over time by looking at the ... A: My formula tested for a blank value - so it the blank could be in another column it would need to be ...
Finding next value	3/6/2009
Q: I have a question that is giving me a bit of a headache. I have a table as below with 0's and 1's. ... A: Error trapped version first ...
Drop-down menus and additional sheets	3/5/2009
Q: I have developed a drop-down menu with various categories one has to choose from which are linked to ... A: It would need to use VBA running on the change event of the worksheet - though if using VBA I'd be ...
Extracting Rows	3/5/2009
Q: I have two files. File A has 100 entries. File B has 1000 entries. There are 100 entries in file B ... A: I'd do this with a formula in a helper column =countif(FileARange,FileBcell) where FileARange is ...
Finding next value	3/5/2009
Q: I have a question that is giving me a bit of a headache. I have a table as below with 0's and 1's. ... A: No, the question isn't clear, but I ASSUME you are saying if a or b or c is one, you want to find ...
Ignoring blank values in a formula	3/5/2009
Q: I am trying to compare how scores in a questionnaire have changed over time by looking at the ... A: Various possibilities spring to mind, but as your MIN and MAX are based around formulas I'd go with ...
Cell Fill Color	3/5/2009
Q: I have worksheet with columns of suppose 1st Jan to 31 july and the first 4 columns with activity, ... A: You would need to use conditional formatting to check the date in the cell against the dates - but ...
Excel Help	3/4/2009
Q: Got a quick question. I am working on a couple of spreadheets and hit a bump in the road. I want to ... A: as you've specified to copy as picture, that's probably the problem - try ...
Need help with a formula	3/4/2009
Q: I have a spreadsheet that I need to be able to count by two criteria. Example, the attached ... A: The multiple condition countif function which this equates to will work with as many conditions as ...
VBA excel code help	3/3/2009
Q: i m stuck up with a problem in my code..the code matches two stings using the matches function..in ... A: as an example =Invester("Fred", sheet1!A1:A1000, 3) would check for all instances of Fred in ...
change cell reference number by an n-amount	3/3/2009
Q: I have Sheet2 ("Month") reference Sheet1 ("Day") data by a total. The totals for each month on ... A: The INDIRECT worksheet function will convert something that looks like a reference into a reference ...
Counting instances of "" depending if crityeria from another cell is met	3/2/2009
Q: im no expert at this and have tried to figure it out and cant but know that you will be able to ... A: You COULD possibly use a pivot table to analyse the data (would give you all the results) but a ...
VBA excel code help	3/2/2009
Q: i m stuck up with a problem in my code..the code matches two stings using the matches function..in ... A: try something like this Public Function Invester(Pol As String, RVar As Range,OfVar as long) Dim ...
Shadow form use in excel	3/1/2009
Q: Looking at a previous answer you gave this is exactly what I need to do however I was hoping you ... A: You didn't refer me to the original answer, but the phrases used don't SOUND like mine - not least, ...
Excel Macro Questions	2/27/2009
Q: I'm attempting to create a macro which will hide a selection of rows (rows 22-28) based on a ... A: change it to be a CHANGE event macro on the worksheet Private Sub Worksheet_Change(ByVal Target As ...
Calculate specific criteria over multiple worksheets	2/27/2009
Q: I've created a spreadsheet with several worksheets in it. On each worksheet I've created a formula ... A: Annoyingly, the sumproduct function won't allow you to run across multiple sheets - so you can ...
Excel Macro Questions	2/27/2009
Q: I'm attempting to create a macro which will hide a selection of rows (rows 22-28) based on a ... A: It's easier than you think - but you are referencing the cell incorrectly - try range("C21") or ...
pivot table ?	2/26/2009
Q: aidan was wondering if you could help me. i am starting to write letters and submitting them to our ... A: Not really a pivot table, as that is a means of summarising data (so you could count up all the ...
"if" formula	2/26/2009
Q: I have a drop down box in column A, there are 5 options. I am trying to set a formula that says if ... A: I'm probably missing detail here- not least as I'd usually use whatever options are chosen directly ...
Excel - SAVE AS dialog appears when clicked on SAVE when opened any existing file issue	2/25/2009
Q: I am facing one issues since a month. I am getting this issue with when I open the existing EXCEL ... A: The only time I know that the save as dialog box would come up would be with a new file - so I'm ...
Date/Time Excel	2/25/2009
Q: I would like to be able to input a set of data, very basic set, and have excel calculate the ... A: If you format the cell as mm/dd/yyyy hh:mm you will see the date change - EVEN if the cell is NOT ...
ASSCI DATA TO TEXT IN EXCEL	2/25/2009
Q: I'm trying to figure a way to scan a barcode into an excel cell and then convert the numeric ASCII ... A: Are the codes scanned in of a fixed length? If so, taking your cell as being A1 =CHAR(LEFT(A1,2)) ...
Searching in Excel	2/24/2009
Q: I know how to find and search for numbers and even words in Excel, but I am wondering if it is ... A: Yes, and as always, lots of different ways - if the values are in different columns, then data, ...
Inserting blank cells	2/24/2009
Q: From previous experience I have to say I actually expect a brilliant and effective answer from you! ... A: I've double checked it in excel 2007 and it does run - what it DOES need is for the data to start at ...
Excel Time Calculation	2/24/2009
Q: I am stuck when trying to work out my times for work. This is a time sheet, that so far has been ok ... A: I'm GUESSING you are entering a time and date into the cell so IF so, the time is simply End Time ...
Inserting blank cells	2/23/2009
Q: From previous experience I have to say I actually expect a brilliant and effective answer from you! ... A: You are much too kind! This macro SHOULD do it as long as the data (a) has no gaps in column C, and ...
Linking ONE single excel sheet in powerpoint	2/22/2009
Q: you have helped me before on some things. Right now I have been laid off-so I'm trying to improve ... A: You stress the ONE in sheet, but then SEEM to be referencing a workbook, so I'm a little confused! ...
Validation - EXCEL	2/21/2009
Q: Would like to increase no of list in one scroll list - validation in excel. I would like to select ... A: Sorry, the question isn't clear - one cell can only contain one set of choices - are you wanting to ...
2 queries.	2/21/2009
Q: 1. In Excel field, i want to get current system time in one ENTER. 2. When clicking on email id in ... A: What would cause the macro to fire? The macro itself is easy enough, something like ...
save the work sheet as individual file	2/20/2009
Q: I have a excel file with 5 work sheets in it. How could I save these individual work sheet as a ... A: (1) save as a text format file - these only support single sheets, so only the current sheet would ...
How do I sort this data?	2/20/2009
Q: I am running a contest and am using Excel to help me sort my data. Basically what I am doing is I am ... A: I'm confused, in that excel should be quite happy to sort the results of formulas into ascending ...
View image within Word	2/20/2009
Q: I am wondering if you can show me the code to take - ... A: Something like this MIGHT do it for you - this however will clear ALL pictures, and requires a one ...
macro	2/20/2009
Q: I have 2 columns of data in excel: column A and column B. I would like to set up a macro that checks ... A: I've probably just do this with formulas in column C ...
grade book	2/19/2009
Q: i am trying to use a function to insert a letter in a column, based on a number in another column. ... A: Needs to be a vlookup function - you will need a table of the MINIMUM marks per grade, with the ...
Conditional formaating w/ Decimals Places	2/19/2009
Q: How can I conditionally format a cell to the number of decimal places? For instance, if a cell ... A: use the formula option in conditional formatting - if (say) we are formatting A1, then =int(a1)=A1 ...
Sumif	2/19/2009
Q: First of all thanks for your previous Answer of my Post. Sir, My Data is like this; ... A: The EASIEST way to do this would be via a pivot table, as this would automatically group things ...
Pivot Tables	2/19/2009
Q: Hey Aidan, I am working as a COOP for the first time and I am doing some work with pivot ... A: Difficult to answer based on the information I've got to go on - a pivot table will display a ...
Making an operation based on the difference between multiple cell values	2/19/2009
Q: I'm trying to find a way to calculate some values based on multiple conditions. What I do is take a ... A: although I'm still slightly at sea but not from your explanation - I've uploaded a file for you ...
copying & pasting dates	2/18/2009
Q: I have two questions: 1) Presently we use spreadsheets as timesheets, where employees enter ... A: Unless there is some conditional formatting applying that is hiding the results (but they would ...
excel help	2/18/2009
Q: I have been trying to copy and paste large number of rows into a column. For example there are 10 ... A: A formula will do it for you - see http://www.aidanheritage.byethost3.com/excel/forAE.xls where ...
excel filters	2/18/2009
Q: Here is my issue. I have a spread sheet that I can not change the format in as it is not mine and i ... A: PROBABLY possible! Do we only have ONE match for the data you are looking for? If so, the MATCH ...
Separating Glucose Readings	2/18/2009
Q: I need to take raw data that contains a date/time and a blood sugar value. I need to look at the ... A: I follow the question, but not sure on the detail - is the data in a file that could be linked to, ...
Counting Numbers	2/18/2009
Q: Expert I have data in Two Column Like This: Zone No. East 4 East 8 East 6 East 31 East 5 East 9 ... A: The FREQUENCY worksheet function would return the values in each range, but it wouldn't handle the ...
Excel workbook	2/18/2009
Q: example: workbook 1 named DECBANK now has info from workbook 2 named TEMPTAG and the detail/data ... A: I'm not sure I follow this - although a system restore only returns the OS to it's earlier state, it ...
copying & pasting dates	2/18/2009
Q: I have two questions: 1) Presently we use spreadsheets as timesheets, where employees enter ... A: I'm not surprised about the clipboard doing different things, but that hasn't solved the problem - ...
excel 2008 on Mac	2/18/2009
Q: I have a cell with a formula in it, resulting in a zero (to prove a correct result). When I fill ... A: At face value this seems unlikely, but clearly it is happening - obviously I don't know the formula ...
copying & pasting dates	2/17/2009
Q: I have two questions: 1) Presently we use spreadsheets as timesheets, where employees enter ... A: (1) someone has I think changed the date settings to the 1904 setting - in earlier versions it's on ...
Rows to Columns	2/17/2009
Q: I am in need of help converting a report with columns to rows so that I can import to access. Here ... A: POSSIBLY the way to go would be data menu, pivot table and chart report - this would allow you to ...
Format	2/17/2009
Q: how can I format a cell or column to accept seven numbers with no other charecters A: It's not format, it's data, validation - you can then set rules for the cell - in this case, that it ...
lowest number above 0 in a table	2/17/2009
Q: i have trying to find a formula to show which will state which number is the lowest number above 0 ... A: should still work fine - it tests each value in the range BUT it does need to be entered with ctrl ...
lowest number above 0 in a table	2/17/2009
Q: i have trying to find a formula to show which will state which number is the lowest number above 0 ... A: I have never liked the D functions as they never seem to work as I would want - for this one, I'd do ...
Copying from cells from sheet1 to sheet2	2/17/2009
Q: I am trying to copy cells from sheet1 to sheet2 in my worksheet to then apply a filter to sheet2. I ... A: Various ways - if this is a printing issue, use conditional formatting to set the cells font colour ...
Advanced search	2/17/2009
Q: I've been trying to figure this out for quite some time now and have tried several functions but I ... A: If I follow this correctly you can do it, but it MIGHT need VBA - I'm not totally clear of the ...
Testing on dates in Excel	2/16/2009
Q: Trying to test "If date in cell A1 > a certain date, display this value. If not, display a ... A: Never only one way to do it - I'd probably use EDATE if(a1>EDATE(TODAY(),-36),"Blue","Green") but ...
Format Merged Data	2/16/2009
Q: 1)What “code” can I use in my mergefield to have my percentages merge properly from Excel? For ... A: I'll do the second one first as it's easier and you are almost there - # means display if required, ...
Testing on dates in Excel	2/16/2009
Q: Trying to test "If date in cell A1 > a certain date, display this value. If not, display a ... A: 1/1/1980 is interpreted as 1 divided by 1 divided by 1980, which is why it's not working. You can ...
Macro that will check text in one column	2/16/2009
Q: I need a macro that will check the text in one column and if it finds it(e.g Case, Package or Each) ... A: Not quite sure why this needs to be a macro, but if you know the formula you are most of the way ...
excel format to .prn	2/16/2009
Q: I want to convert a excel-file to a text-file (prn). My problem is that the excel spread sheet seems ... A: The file is OK, it's just that whatever you are viewing it in is wrapping the text - open it in word ...
job list	2/16/2009
Q: hi there i have a sheet with all the jobs our company does and i want to make a sheet that if you ... A: Yes, it's possible - the EASIEST way would be to use data, filter, autofilter to select the customer ...
lookup multiple values with changing data	2/15/2009
Q: I am trying to use index match offset (I assume) to return values. The page with the values shows ... A: I'm not sure I follow but it SOUNDS (If I have read it correctly) that you want to have the ...
Making an operation based on the difference between multiple cell values	2/15/2009
Q: I'm trying to find a way to calculate some values based on multiple conditions. What I do is take a ... A: I do think I can help me, but I'm confused slightly in that you say if the difference between the ...
Excel ques	2/14/2009
Q: i have 150 names but they are in a lower order means in the small letter. actually i know the ... A: To change the case of cells, use the formula as you suggest, THEN copy the result and put the cursor ...
Cell Cannot be Left Blank	2/13/2009
Q: I have created a form that requires users to use drop-down lists. However, If they do not choose an ... A: There is nothing wrong that I can see, but the message wouldn't do anything to make them enter ...
real time macro	2/12/2009
Q: . Im developing a program that basically is used to clock in and out. And im have a little problem. ... A: I really would not recommend converting time to a FORMATed value, as that returns a string - so 9 is ...
looping through workbooks	2/12/2009
Q: Im making a spreadsheet for my pilot log book. Each worksheet represents a page of my logbook. Im ... A: AllExperts allows pictures which seems great, but they are almost impossible to read, so although I ...
Excel Assignment	2/12/2009
Q: I have been stuck on this assignment for the past 2 weeks and don't know how to proceed. Any ... A: If I'm going to help with an assignment, I'd need to know what you are expected to know - it ...
Have the tab name pull from a cell	2/12/2009
Q: I have a spreadsheet that has the performance for my employees, and it has all thier names on the ... A: Not sure I follow - it sounds as though you want excel to rename a random worksheet with whatever ...
Excel 2000 Baloon Payment Forumal	2/12/2009
Q: I am searching for an Excel 2000 formula to calculate a $675,000.00 principal balance, amortized ... A: so it was basically not going to get an answer - if you click on the google link I THINK that the ...
If statement with a count	2/12/2009
Q: I was wondering if there was a way to identify a variable and count it. For example: One column is ... A: The QUICKEST way to do a bulk analysis of this sort of data is to use a pivot table - data menu, ...
How to get data from web in Excel	2/12/2009
Q: I download a lot of data from a URL and I have a workbook named dumps.xls in which I copy all this ... A: My first reaction to this would be to use a webquery - data menu, import external data - IF the data ...
Update	2/11/2009
Q: I am working on a project where we have individual excel files for each department, and each file ... A: for each individual sheet, for any item on the master list enter it's value as a linked cell - i.e. ...
names	2/11/2009
Q: Is there a way to turn a name over two cells into one cell ie Wood Stuart (2 cells), into Wood,S (1 ... A: yes, but it will take a 3rd column (at least to start with =FirstCellRef & ","& ...
Counting Text	2/11/2009
Q: Hope you can help. In column B2:B265 I have a list of names, each name appears about 4 time each. ... A: I would SUGGEST using a pivot table to do this - you didn't say which version of excel, but if not ...
spreadsheet - 2 items	2/11/2009
Q: I would like to know how to clear columns of information without clearing formulas or ... A: To ensure the sort works correctly either select NOTHING before doing the sort, OR select the entire ...
formula question	2/10/2009
Q: Is there a summing formula that would allow me to add together numbers, similar to the sumif ... A: OK - the first one is relatively simple as it just uses countif =COUNTIF(YourRange,"E*") The ...
IF function help	2/10/2009
Q: I'm working on a golf score card. here is my scenario. F10 represents the differenctial between the ... A: OK, I follow now - you BASICALLY need to test if ALL the entries are the same or if some are ...
Count Records based on multiple variables in multiple columns	2/10/2009
Q: Column A has a list of case types (Diversion, Court, Probation, etc) Column B has a date the case ... A: Yes, if you refer to my SumProduct example it MAY become clear - its basically ...
Date Format (UK Format)	2/10/2009
Q: I have a text format of 02.11.09 in my workbook Column A1. How can I convert this to UK date format ... A: IF the date is entered as 02.11.09 then it's not recognised as a date - but select the column and do ...
Count Records based on multiple variables in multiple columns	2/10/2009
Q: Column A has a list of case types (Diversion, Court, Probation, etc) Column B has a date the case ... A: You can use a neat trick with the sumproduct function to achieve a multiple condition countif or ...
Excel data replace	2/10/2009
Q: hopefully you can help me. I have a data set in excel and there are two things I am trying to do and ... A: I'm not sure that the Julian date would be 233, but that's minor - I would use a helper column to ...
IF function help	2/10/2009
Q: I'm working on a golf score card. here is my scenario. F10 represents the differenctial between the ... A: I have some problems here - (1) I'm a non golfer (2) the reference to F10 appears 3 times in the ...
IF, then for multiple cells	2/10/2009
Q: is there a way to send a screen print or copy of the excel sheet to help explain my question? If I ... A: I don't understand - I replied, you quoted the reply - my reply said that I couldn't understand the ...
Consolidate two ordered lists	2/10/2009
Q: I have a tabular output from an iGrafx model. The table has two lists, input and output. I need to ... A: I THINK I've got it sussed - I'm ASSUMING that the ordered list example you have given me here is ...
Consolidate two ordered lists	2/10/2009
Q: I have a tabular output from an iGrafx model. The table has two lists, input and output. I need to ... A: This is obviously clear in your mind, but this clarification actually confuses things further - the ...
Reference Cell	2/10/2009
Q: I want to get "Sheet" value from sheet1 to combine the cell value in sheet2. (=Sheet1!A "Combine ... A: The picture doesn't make it very clear, but it's possibly INDIRECT that you are looking for ...
adding to an existing formula	2/10/2009
Q: here is my existing formula for cell E10: =IF($D10="","",IF($D10="Black",$C$3-$C$5, IF($D10="Blue", ... A: Can I assume that D10 would require a value? if so ...
spreadsheet - 2 items	2/9/2009
Q: I would like to know how to clear columns of information without clearing formulas or ... A: There isn't any simple way to clear ONLY the values on a spreadsheet - you CAN do it with VBA but as ...
Excel	2/9/2009
Q: I have a colum of cells with Name, Address, City, state, Phone: state is not capitalized. How can I ... A: Ah, I misunderstood - I took it that the data was a series of columns - you COULD use data, text to ...
VLOOKUP, IF	2/9/2009
Q: I have 5 columns of data in ascending order. The first four columns will be my variables and the ... A: Not sure how fixed the structure of the data is - the EASIEST way would be to build the lookup table ...
Excel	2/8/2009
Q: I have a colum of cells with Name, Address, City, state, Phone: state is not capitalized. How can I ... A: If by capitalize you mean upper case at the start and lower case throughout, then there isn't a ...
Change formula based on cell calling it	2/8/2009
Q: What I am looking to do is... I have this formula in G3: =IF(F3="w",$J$3,-E3) The formula in J3 ... A: How would J3 know which cell was calling it? You can EITHER copy a series of formulas down the ...
Exporting Excel Data into Template	2/7/2009
Q: Heritage, I'm a first grade teacher, and I'm looking for a solution to help make somethings at my ... A: If you can create the award in Word it will be even easier - the job is then called "Mail Merge" - ...
duplicating a sheet with formatting	2/6/2009
Q: I have a spreadsheet that is one dimensional. It has sections that are identical,,,every 20 rows ... A: Not quite sure what you are asking here, but I'd PROBABLY do this by right clicking the one ...
Page Break View	2/6/2009
Q: I am unable to drag the lines in page break view. I have uninstalled IRM software of Microsoft ... A: you didn't give version of excel, but http://support.microsoft.com/kb/214691 ...
Calculating federal income tax	2/5/2009
Q: I am working in Excel 2003. I am working on a spreadsheet that incorporates federal income tax ... A: I can help, but I have a SLIGHT problem in that I'm in the UK so don't know the methodology of the ...
excel weekly graphs	2/5/2009
Q: I have 13 workbooks with 4 worksheets each(weekly data). and 13 workbook with 4 worksheets ... A: If I was doing this, I'd have 13 workbooks with 8 sheets - in fact, I'd have one master template ...
Excel-Guidence required	2/5/2009
Q: I have two sheets in excel:sheet 1 & Sheet 2. My problem is: While running vba code (depend on Check ... A: I may have misunderstood what you are asking, but you can always reference any sheet/workbook/cell ...
excel	2/5/2009
Q: how can creat vlookup & hlookup in one workbook and another workbook A: The same as any other lookup - I'd suggest having both workbooks open as you can then simply click ...
Automatic sum function within a cell	2/4/2009
Q: I'm wondering if there's a way to type a number into a cell and, instead of having it erase what is ... A: It's a fairly common question - it's not one I'd be happy to do myself as the chance of making an ...
using a vlookup text result in an IF statement	2/4/2009
Q: GENERALLY: The result of a vlookup formula (a text word) used in an IF statement is not matching ... A: Difficult to follow - the formula for testing is more complicated than it needs to be =D1=F1 would ...
Tally in Excel	2/4/2009
Q: I need to tally up the number of times a county is selected from the dropdown menu and tally it at ... A: Is this a count of the entries in a series of cells, or do you want to keep a running total of the ...
"0" disappears - TEXT function	2/4/2009
Q: The system does not allow me to ask any more follow-up questions. So far you you were very helpful. ... A: The text file itself will be fine - open it in notepad and you should see this - so I'd recommend ...
"0" disappears - TEXT function	2/4/2009
Q: The system does not allow me to ask any more follow-up questions. So far you you were very helpful. ... A: the text worksheet function will return, as text, the formatted value of any number - so if you have ...
first zero disappears after	2/4/2009
Q: In excel XP/2007, I have a phone number column. Each phone number appears in the following format: ... A: You should NOT save the file as text - if you do, when you re-open it in excel, it will go through ...
Summing, skipping rows, sheets	2/3/2009
Q: I think what I need is not so terribly complex, but while a power-user in general, I'm a noob with ... A: Something along these lines would seem to do it for you ...
Excel-Reference same row in columns and then down in rows	2/3/2009
Q: I have records in columns and rows (A1:H5). On a separate sheet, i need to reference the first ... A: Not easy to answer based on the information given - you COULD use a vlookup function to get the ...
Excel Query	2/3/2009
Q: I have to create an excel format in which, When some one input some particular number in a ... A: This would need to use the CHANGE event of the worksheet, and use VBA to achieve what was needed ...
Last 3 trading days of every month	2/3/2009
Q: I have S&P and DJIA data in Excel format I downloaded from Yahoo. The daily trading data traced back ... A: As this sounds like a one-off job, I'd do it as follows firstly, in a blank column (I'll assume F ...
How to read excel data and insert into sql db	2/3/2009
Q: I need to extract data from excel sheet (with many worksheets, one for each table) and create a ... A: Ok, well again a real world case - this doesn't loop through the sheets but something like dim sh ...
Quick Question on deleting rows	2/3/2009
Q: I want to delete the row if three cells in the row are equal to zero. I think the EntireRow.Delete ... A: try Sub Deleter() Set currentcell = Worksheets("Sheet1").Range("d5") Do While Not ...
sorting names in excel spreadsheets	2/2/2009
Q: I have been photographing tombstones and have listed all the names on excel spreadsheets. I have ... A: If you use the method I've given you, you will get a list of matches below the find - clicking any ...
Microsoft Excel	2/2/2009
Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: I have found over the years that emailing microsoft as an ordinary member of the public does no good ...
Microsoft Excel	2/2/2009
Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: Asking Microsoft to update old products is a non starter, but generating one text file from multiple ...
Microsoft Excel	2/2/2009
Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: The size limit of 65536 is set in all versions of excel up to 2003, as you say Excel 2007 has more ...
Drop Down Lists Width	2/2/2009
Q: yesterday I asked you a question regarding macros functioning with merged cells on the worksheet. ... A: The macro runs on one of the events of the worksheet, so you will need a similar macro on EACH ...
email attached files from excel	2/2/2009
Q: hope you can help:). So this is what I currently do: Teach class for 160 students, I post ... A: Its certainly possible but it would need vba code - see http://www.rondebruin.nl/sendmail.htm for ...
Activate/Deactivate sheets	1/31/2009
Q: I have many sheets within my program that do not need to be visible at all times. I want to create ... A: You haven't said where the checkbox came from, but if from the FORMS toolbar, right click it, format ...
Activate/Deactivate sheets	1/30/2009
Q: I have many sheets within my program that do not need to be visible at all times. I want to create ... A: Your code looks fine, but it doesn't turn screen updating back on - application.screenupdating=True ...
Nr format not pasted into hyperlink	1/30/2009
Q: I created a workbook where I send the content of certain cells into a mail threw a hyper link. The ... A: instead of using the cell reference on its own (which will return the true value of the cell) use ...
Automatic copy of lines in a sheet	1/30/2009
Q: My question is the following: I have an excel spreadsheet, containing different tabs with the same ... A: dim sh,rar for each sh in activeworkbook.worksheets for each rar in sh.range("D5:D30") if ...
sorting names in excel spreadsheets	1/30/2009
Q: I have been photographing tombstones and have listed all the names on excel spreadsheets. I have ... A: From the information given I would say you already have a way of searching - select all sheets ...
Conditional Formatting In Excel & Enabling Proper Text	1/29/2009
Q: Aidan, Using the program text (below....),I was able to comtrol any column on sheet 1 of my ... A: See http://www.mvps.org/dmcritchie/excel/proper.htm for a working proper case routine. As to ...
SUMProduct Function or COUNTIF	1/29/2009
Q: I have a monster spreadsheet. It collects data from IT Problems in > infrastructure. > > I need to ... A: The sumproduct SHOULD work - I take it it's not as simple as ignoring the year (just add up all ...
Risk Register Template Modification	1/29/2009
Q: Joseph, I am working on a template of a Risk Register. How can I modify a Combo Box table content. I ... A: I think you will find this is under the DATA menu, then Conditional Formatting - that sounds as ...
Automatically hide row if cells = 0	1/28/2009
Q: Is there a way to automatically hide a row if for example: If cell B32 and B33 both equal 0, then ... A: I take it that you meant rows 32 and 33 should be hidden, rather than "row" B (which is a column ...
excel question	1/28/2009
Q: I have a spread sheet with over 20,000 cells that contain formulas. When I want to up date it I ... A: I would do the following - put yourself at the bottom right of the worksheet and then put the cursor ...
link from QnE (application, processing and database) with MS Excel	1/28/2009
Q: my company currently using QnE for saving, updating and produce certain analysis results and it's ... A: I don't know anything about QnE, but if it can be queried then you can use excel to do it - my own ...
Converting Columns to rows	1/27/2009
Q: I have a spreadsheet in excel with a macro. When the macro runs, it sends the results to another ... A: All your requirements can be handled - I have a slight problem in that I don't know what the macro ...
Excel Percentages	1/27/2009
Q: I am trying to set up a data base on excel that works out percentages for example i have completed 7 ... A: Percentages are fairly easy to calculate and display - set the format of the cells to percentage ...
Excel - Adding years and months together	1/27/2009
Q: I am striving to add together all the lengths of service for my employees to end up with a statement ... A: if you have output looking as you've typed it, you have a series of text entries as far as excel is ...
excel tranpose by formula	1/27/2009
Q: Hai Joseph, I want to copy paste data from sheet1 to sheet2 with transpose/link mode. is it ... A: Use this formula =OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1) where Sheet1!$A$1 equates to the first ...
Code	1/26/2009
Q: I have a bunch of cells I want to apply this code to in the same workbook, but the values for each & ... A: Not clear how many rows, and how many messages but something like If Target.Column = 2 And ...
Password Protect Specific Sheet	1/26/2009
Q: I have been asked this question and have been unable to come up with an answer. Is it possible to ... A: (1) never share workbooks - it's a recipe for disaster (2) I've not had to do it, but would PROBABLY ...
Excel Automatic formula not working	1/26/2009
Q: I am using excel 2007, we are preparing estimation using excel worksheet.when I am changing the ... A: You say that you have checked automatic calculation, but the implication is that EITHER a macro has ...
consecutive blank cell	1/25/2009
Q: EASE FIRST TRY TO UNDERSTAND WHAT I AM LOOKING FOR. YOU HELP WILL BE GREATLY APPRECIATED. I need to ... A: Rather than fight with the two methods you've tried, I've written you a custom function that should ...
Drop Down Lists Width	1/24/2009
Q: yesterday I asked you a question regarding macros functioning with merged cells on the worksheet. ... A: Sorry, but if you don't want to change the column width and you DO want to use data validation you ...
Excel bug? - uneditable cell comments	1/24/2009
Q: First of all thanks for offering your time to help us with Excel! Sometimes I have this random ... A: I haven't come across it, although I very rarely use comments so that MIGHT be why! I've been able ...
Macros and Protected Sheets	1/23/2009
Q: my question pertains to protecting selected cells in a excel spreadsheet and still have the macros ... A: Macros can only do what can be done - if a lot faster than you could do it - so IF you try to sort a ...
Question regarding line charts	1/23/2009
Q: I'm a reasonably proficient Excel user, working with some data in Excel 2007. I'm trying to create ... A: I'm getting the same results as you, and charting is one area that I haven't had huge experience in ...
searching an excel range and copy	1/23/2009
Q: I need to search a range of cells, Say from A to Z, and all the sheets on the workbook for the word ... A: OK - the code that you have looks in column D only for EEPC anywhere in that column, and IF it finds ...
Excel Filter	1/23/2009
Q: When I use auto filter, I get a message "Fixed Objects will move" and there is no way to cancel that ... A: see http://www.mrexcel.com/forum/showthread.php?t=84261 for a reasonable explanation and suggested ...
searching an excel range and copy	1/22/2009
Q: I need to search a range of cells, Say from A to Z, and all the sheets on the workbook for the word ... A: You didn't give me any code to modify! It's not clear if EEPC would only occur once in the row, or ...
currency conversation	1/22/2009
Q: how to convert Us dollar to in Indian rupee using MS Excel A: I'm assuming you will want minimal inputs (the low tech way would be to input the US Dollar amount ...
Excel VBA	1/22/2009
Q: Am new to VBA,please excuse me if i am not clear ,workbook has three sheets. Sheet 1 contains ... A: Do you want this to be an automatic process, or one that runs on demand? Either way, the basic ...
Macro – Import Web Data	1/22/2009
Q: Please help me, I'm stuck up with something. I'm trying to Import data from an URL which is in cell ... A: My method was correct, but it may not be clear from the cut down code - the start of the macro ...
MS Excel labels	1/21/2009
Q: I'm not sure how to simply put this question so please bare with me as I try. I currently have an ... A: Difficult to answer without seeing the document - presumably the size of the cells has changed? You ...
Cells	1/21/2009
Q: Hey! I wanted to ask you how do I make a cell appear of a coler, red for example if the value ... A: It's the format menu, then conditional formattting - you can set simple value tests (which would do ...
Problem getting worksheet_change to update	1/21/2009
Q: I have a excel documents that querys a sql database. In excel then i want to count differnt values ... A: Unfortunately a change in the query doesn't trigger the change event - you could possibly set this ...
how to program a search using a command button	1/21/2009
Q: How do I program a command button so I can search a worksheet for a specific entry in a cell or ... A: I'm not sure why this macro is needed as Ctrl F would bring up the find box and do the same thing, ...
Assigning a number value to a text entry	1/20/2009
Q: I have created a drop-down list (to be used several times within the same row) that allows for the ... A: One of the downsides of questions on a site like this is that you get the answer to the question you ...
Excel	1/20/2009
Q: First of all thank you very much see my question, how will combine the multiple cell data in single ... A: The question is slightly unclear as to what you want to do but if you want to have one cell ...
Shorter the length of a column	1/19/2009
Q: When I open each tables in Excel, there is a column that always shows pretty wider than other ... A: Not clear if you are referring to a blank worksheet or a previously saved one - IF it's a blank ...
password	1/19/2009
Q: I noticed that after having put password protections in several sheets, when I give them to other ... A: I've noticed this too with workbook protection - it ONLY seems to relate to later versions. ...
Assigning a number value to a text entry	1/18/2009
Q: I have created a drop-down list (to be used several times within the same row) that allows for the ... A: I've seen various answers on the web to this, but they all want VBA which I think is overkill. ...
Start a counter when Cell turns different color	1/17/2009
Q: I'm using conditional formating on two cells. What I'm attempting to do is when the user places a 1 ... A: It would have to use VBA, and would use the change event of the worksheet. It would have to check ...
Setting up a Counter when cell color changes	1/17/2009
Q: I'm using conditional formating on two cells. What I'm attempting to do is when the user places a 1 ... A: It would have to use VBA, and would use the change event of the worksheet. It would have to check ...
Formula for comparing dates	1/17/2009
Q: I have two columns. One with a "due date" and another with an "actual date". Would like both ... A: I find the pictures on this site hard to view (probably my eyesight) - feel free to email me the ...
comparing similar fields	1/16/2009
Q: I have a column of names that has many duplicates, so I use the Data>Filter>Advanced filter to copy ... A: the easiest way WOULD be to remove DOC - select the column, then do edit, replace, type DOC in the ...
Storing Variables	1/16/2009
Q: We worked together in Fujitsu some years ago, you got me on the path to Excel when you left to go ... A: I'm still at NU (although we've been outsourced so technically are swiss re) - variables will be in ...
Conditional Formating	1/16/2009
Q: I am trying to perform a conditional formating based on values in my cells i.e I7 3 - Medium, M7 = ... A: "3-Medium" is a string, and M7<62 is not the same as M7=62 - I'm not sure if you have a named range ...
Saving an Excell spreadsheet using a cell reference	1/15/2009
Q: I am a novice in Excel. I would like a macro (don't know how to create, save or execute a macro) ... A: yes it can be done. Use tools, macro menu to record a macro - this will give you a starting point. ...
Excel versions	1/14/2009
Q: Can I load both an older version of excel and a newer one on my computer without them conflicting. ... A: It is possible, you would need to install them in different areas - don't think I'd use a zip drive ...
xl2003 autofilter	1/14/2009
Q: I have been given a spreadsheet with 3 combined lists on it. I have placed conditional format ... A: the autofilter will not always show all entries, although it should USUALLY show top 100 (but even ...
counting with multiple criteria	1/13/2009
Q: have to determine how many instances of fights occur during a 20 day period. I am able to narrow ... A: This method SHOULD work, but without seeing the data it's going to be difficult to debug - if you ...
counting with multiple criteria	1/13/2009
Q: have to determine how many instances of fights occur during a 20 day period. I am able to narrow ... A: The best way to do a multiple condition COUNTIF is to use the SUMPRODUCT function - this works as ...
Excel numbers and range	1/12/2009
Q: I have a list of numbers in a column. These numbers are not necessarily in any incremental order. I ... A: I don't quite follow what you want to do - are you wanting to count the numbers of entries in each ...
Save Button	1/12/2009
Q: im using vlookups and need a save as button to save only the current page info taking out all the ... A: You can either use the ChDir command to change the directory, or probably better just extend the ...
Excel sheet info copying within 'if' statement	1/10/2009
Q: I wish to copy info from multiple cells in one sheet to another if a certain criteria is met in ... A: Not sure how you wanted to do this -is this a Visual Basic question, or just using a formula to do ...
excel date formatting	1/10/2009
Q: I have an excel workbook that contains several different sheets.These sheets are used mainly for ... A: =StartCell+1 where StartCell is the cell with the week starting date - this formula adds one day to ...
Sum formula without duplicate	1/8/2009
Q: How are you? I have a question little bit difficult that to get the sum without duplicate. Let I ... A: I follow the question, BUT I'm puzzled, in that I had ASSUMED you had a way of generating the ...
Excel 2000 Macro	1/7/2009
Q: I have a spreadsheet with about 2000 rows of records with many words in lower case and some in upper ... A: something like this Sub updateme() Dim Rng For Each Rng In Range("g1:G2000") If Len(Rng.Value) ...
Excel mailing list merge	1/7/2009
Q: I know how to merge an address database from excel to word to create labels or envelopes, but I am ... A: I would USUALLY seperate the fields with a paragraph mark so that you had Address1 Address2 ...
countif	1/7/2009
Q: I have a countif formula that counts a range, outputting how many 'a', 's' or 'c' etc there are. (a, ... A: =SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"a",""))) would do it for you (this version is looking ...
Copying multiple sheets in vba	1/7/2009
Q: I am fairly new to VBA. I have 40 sheets of data in cell range A5:E91 and would like to ... A: something like dim counter counter=0 for each sh in activeworkbook.sheets if sh.name<>"Upload" ...
duplicating data	1/6/2009
Q: I am using excel 2003 on xp. My problem is that I have data that is self replicating. It seems ... A: Its not something I've come across - it could be a macro running on the sheet or (I suppose) some ...
excel if statement	1/6/2009
Q: I have an if statement =IF(D24<=8,D24*62.5) but once over 8 it needs to change to 50.00 so I'm not ... A: Only one if statement needed as it's IF test is true, do something, otherwise do something else ...
Sum formula without duplicate	1/6/2009
Q: How are you? I have a question little bit difficult that to get the sum without duplicate. Let I ... A: It's going to be a little tricky using just this formula - I would first like to suggest two ...
Excel Percentage formula	1/6/2009
Q: I have a labor wage chart I need to multiply each figure by 2.5% and add it. When I try this for ... A: Not sure what you are doing here - if the job is to have another column iwth a formula, then the ...
PivotTable	1/6/2009
Q: I recently changed to Excel 2007. I'm unable to use the PivotTable function properly in this version ... A: Using the Pivot table field list, you should have in the bottom right the two values - click the X ...
How to add color to area under line on XY graph.	1/6/2009
Q: For a basic XY graph in Excel, what VBA code will allow me to add color to the area under an ... A: The links I'm looking at for shading are ...
PivotTable	1/6/2009
Q: I recently changed to Excel 2007. I'm unable to use the PivotTable function properly in this version ... A: If you look at the field list you should see the x and y values - you can drag the X values to the ...
Excel Merging	1/5/2009
Q: I have a huge testing execution file with test cases written. I want to distribute the same sheet to ... A: I've never done it myself but this comes directly from the help file Merge workbooks Make sure the ...
Date Calculation	1/5/2009
Q: I need to calculate the "Seniority date" for employees. I have the formula to calculate the # of ... A: I would tend to use the YearFrac function which is on the analysis toolpak - tools, add ins and then ...
Formula in data validation allow list	1/4/2009
Q: How are you? I put the formula in data..Validation..Allow.. List it was working fine but when I add ... A: In all probability your formula has become too complicated - it's not really possible to debug the ...
Microsoft Excel 2000 Macros	1/3/2009
Q: I am trying to write a macro that will automatically execute when the workbook is opened. Oddly ... A: I haven't got excel 2000 installed anymore, but I THINK that event driven macros are present here, ...
PivotTable	1/3/2009
Q: I recently changed to Excel 2007. I'm unable to use the PivotTable function properly in this version ... A: By default it displays the sum, but you can EITHER right click on the heading (where it says SUM OF ...
Excel duplicate finder	1/2/2009
Q: I want free software for checking duplicate words in Excel (Deduplication check Software). I have ... A: I'd be inclined to move the data across to an access database and set it up such that NO duplicates ...
Percentages in Excel	12/31/2008
Q: Beginner here! I need a formula for percentages. Calculator is easy ... grrrr excel is not. I have ... A: in c10 enter =D4*B10 pretty much the same as a calculator EXCEPT that you put the = at the ...
Pushing data from one worksheet to specific cells in other worksheets	12/31/2008
Q: I have an Excel workbook that has multiple worksheets. There is one worksheet for the master data. ... A: Yes, VBA will certainly be able to do this - it's quite difficult for me to see from the screen ...
ADDRESS() function	12/30/2008
Q: Given: wkbk1/sheet1, wkbk2/sheet2, wkbk3/sheet3 wkbkx/sheet0 wkbk1, 2 and 3 are in ... A: You have actually answered your own question - when you have the workbook open, the function works, ...
Lost the ability to insert Row/Column	12/30/2008
Q: I am very good with data manipulation within excel 2007 and for some reason recently I lost the ... A: I would also go towards workbook/worksheet protection - it certainly doesn't look as though the ...
Controlling the data field within Excel.	12/30/2008
Q: I've seen excel files where a user can only operate within a set range of rows and columns and can't ... A: select the extra columns (ALL of them!) and then format, column, Hide - same for rows, except ...
VB Script	12/30/2008
Q: I am an hospice Chaplain and I need to do regular mailings for family contacts. I have been ... A: I'm not 100% sure what the script is supposed to be doing, in that I'd be inclined to have a macro ...
excel refernce	12/30/2008
Q: Aidan: I am having trouble with the following ("'s are entered to indicate actuals test entered): ... A: If the cell displays the literal text that you have entered rather than the formula, it means the ...
Gathering information from a report	12/30/2008
Q: How do I gather unique entries from a report and classify them? For example, my basic table is set ... A: The layout of the data is not ideal as each "record" occupies multiple lines. I think though that ...
hi	12/30/2008
Q: I have a worksheet with column A "customer names" column B address and column C tel number. I have ... A: I'd use a userform with VBA to achieve it then - it would use a COUNTIF function on the worksheet ...
Excel Formula	12/28/2008
Q: please help me Question- : if i have a list like this-: A A1 ajai A2 A3 ajai A4 A5 ajai A6 ... A: use column C as a helper column in column C enter =countif($A$1:a1,a1) in the C1 and then drag this ...
Pulling specific data from another file	12/24/2008
Q: My coworker has asked me to help her pull information from a file and input it in different ... A: macro likely to be easier than you think as a lot of it is recordable, but it basically involves ...
Pulling specific data from another file	12/23/2008
Q: My coworker has asked me to help her pull information from a file and input it in different ... A: The indirect worksheet function only works with open external files,so that explains the reference ...
Lost pictures	12/23/2008
Q: OMG! Do I need your help! This is what I did: I went to "My Pictures" folder, because I wanted to ... A: On AllExperts, you normally find an expert by subject - and the subject you found me on was Excel - ...
Formula for comparing dates	12/23/2008
Q: I have two columns. One with a "due date" and another with an "actual date". Would like both ... A: Another helper column is called for I think - this time I'd convert all the dates to the 1st of the ...
Lost pictures	12/23/2008
Q: OMG! Do I need your help! This is what I did: I went to "My Pictures" folder, because I wanted to ... A: It sounds like an operating system query more than an excel one. Without knowing what you did it's ...
If conditional formating with other formulas	12/22/2008
Q: Is it possible to ad a sum inside an if conditional formating? I'm in the furniture industry and I ... A: Your question doesn't quite work, in that you refer to conditional foramtting but then have a ...
excel	12/22/2008
Q: How can I sum the sales between 9 pm and 3 am Time Sales From To 1 AM $1.00 9 PM 3 AM 2 ... A: Sorry, I had it in my mind that the cells would contain a date component as well. ...
excel	12/22/2008
Q: How can I sum the sales between 9 pm and 3 am Time Sales From To 1 AM $1.00 9 PM 3 AM 2 ... A: This can be done by using a feature of SUMPRODUCT which is that TRUE and FALSE values can be ...
Calculating months and days in Excel 2007	12/21/2008
Q: I hope you can answer this question for me as it's driving me up the wall! I'm calculating the ... A: I'm definitely unsure - in that I work in the insurance industry where we have to calculate terms in ...
COUNTIF, IF	12/21/2008
Q: This one should be easy, but it is giving me some trouble, perhaps you can help. How can i get a ... A: As long as the ranges are the same size, just keep adding ...
Protecting worksheets	12/21/2008
Q: hope your well, i have a question that has bought me to a stop today. i have created a workbook ... A: You refer to Sheet1.Unprotect Password:="yourpassword" pwd = Application.InputBox("Please ...
IF formula won't work	12/19/2008
Q: I used the following formula in a saved workbook yesterday. It worked perfectly. I am trying to use ... A: the lookup as it currently stands says to look up the ENTIRE column A value - which isn't correct ...
Complex Statistical Formula	12/19/2008
Q: I have 2 columns, one with dates (m/dd/yyyy) and one with names (Quinn, Chris). How do I count the ... A: the sumproduct function can be used to handle multiple criteria ...
Vlookup link	12/19/2008
Q: When I reopen the sheet it asks me do you want to update - it just hangs with the hour glass { I ... A: I don't see the email addres, but the notification of reply will go to work if that is the address ...
Fill down with cell values	12/19/2008
Q: Aiden, I have streams of data that give me columns with up to 10,000 rows of data. How can I write ... A: I've assumed your data is in column A and you are starting at row 1 ...
count if issue	12/18/2008
Q: Im trying to create formula to count how many times x shows up in certain row while in the same ... A: You have used the STRINGS "1" and "2" but I'm GUESSING that the cells contain the values 1 and 2 as ...
Excel - Calculate Two Dates - If cell is blank enter Zero	12/18/2008
Q: What formula can I use to calculate two dates in Excel and if the cell is blank enter a value of ... A: Not sure what you wanted to do as regards the calculation, but =if(isblank(YourCell),0,Formula) is ...
COUNTIF, IF	12/17/2008
Q: This one should be easy, but it is giving me some trouble, perhaps you can help. How can i get a ... A: There is a rather neat trick that can handle multiple condition count if or sum if statements which ...
lock rows	12/17/2008
Q: I asked a question previously and didn't get all the information I need. The person I asked before ... A: Jans answer was pretty good - IF you data has NO gaps in it (by which I mean blank rows or columns) ...
Calculating months and days in Excel 2007	12/17/2008
Q: I hope you can answer this question for me as it's driving me up the wall! I'm calculating the ... A: Not sure what you are going to use the results for, but I'd go to tools, add ins and then check the ...
COUNTIF	12/16/2008
Q: My data has 2 columns, column A consists of names and column B consists of numbers as follow: ... A: I think I'd use a trick of sumproduct to do this =sumproduct(--(a1:a2000="John"),--(b1:B2000>0)) ...
Excel - Hyperlink - Named ranges	12/16/2008
Q: I ask this question of another expert but did not get a resolution and when I tried to reply, it ... A: You said you had named cells, but you THEN said that the hyperlink is =details!$C$2 - which isn't a ...
Linking data on excel worksheets	12/16/2008
Q: On Sheet 1: I have created a price list, column "A" is product description, column "B" is the ... A: sounds like a job for vlookup - which is =vlookup(what,where,which,logical) the WHAT is the item ...
intersection between column and row	12/16/2008
Q: I have the below two tables: Intersection Jhon 123456 A Mary 234567 Q David 345678 ... A: I would use the INDEX function, and the MATCH function to get the co-ordinates - so you match the ...
Splitting 1 column of excel data into several	12/15/2008
Q: I have an excel spreadsheet that has all data in 1 column like so: Car Weight MPG Car Weight MPG Car ... A: You don't actually need a macro to do this - you can do it with formulas to start with, then copy ...
Formula to Macro	12/15/2008
Q: Can you tell me if it's possible to convert a formula used in every cell in a column into a macro? ... A: macros can do all manner of things, but I'm not sure what you want the macro to do! The formula you ...
formula	12/15/2008
Q: on cell b6 i have type mon and on cell n6 and want to show that if on b6 is iqaul to mon i want to ... A: I'm not sure what you had in mind by 9.42 - if the value is 9 point 42 then =if(b6="mon",9.42,0) ...
Word or Excel	12/15/2008
Q: just wondering what's a better option for creating a template for MoM: Word or Excel? What are the ... A: As I've no idea what you are referring to as MoM I'm not going to be able to help easily - but in ...
Save Button	12/14/2008
Q: im using vlookups and need a save as button to save only the current page info taking out all the ... A: You can almost record this, but the full macro will be something like ActiveSheet.Copy ...
Excel question	12/13/2008
Q: This a very simple workbook question which is going to expose my extreme lack of Excel know-how... ... A: You can only know so much! This one is easier than you think - data menu, filter, auto filter - ...
Embedding Pictures/ PDF files	12/12/2008
Q: I need to embed pictures and pdf files into my excel spreadsheets. I would like to have the picture ... A: I think that this page http://www.eggheadcafe.com/software/aspnet/30479082/lock-image-to-cell.aspx ...
Dates in excel	12/12/2008
Q: How can i compare two dates for example. I want to put on an expire date in a cell and in another ... A: Not sure about an entire column, but basically it's format, conditional formatting - also not sure ...
blank & zero	12/12/2008
Q: I've designed a VB program that works in conjunction w/ excel. After entering data on the vb forms, ... A: Cannot be absolutely sure, but this scenario fits with the facts - the cells in question are NOT ...
Using a printing macro to select a printer	12/12/2008
Q: Adrian, Thankyou for the solution regarding post dating a cell in an Excel spreadsheet to 28 days ... A: From a relatively simple question to a tricky one - at least, so I thought - in Excel, you print to ...
HELP with IF / VLOOKUP function - please!	12/12/2008
Q: I have one large table of internal codes associated with to cities & states. There are three ... A: I didn't look at the picture as they tend to be hard for me to read but if this answer doesn't help ...
excel formula	12/12/2008
Q: i want to know the formula for my report in excel i have to separate numbers in a cell.for example ... A: I don't think you can do this with formulas, rather you would need to use a macro to achieve it ...
Minimum or Rate per kg	12/11/2008
Q: Trust all is well with you. My question is as follows: I have a rate for my customer as follows: ... A: possibly it's slightly counter-intuitive, but you need to use the MAXIMUM worksheet function, as you ...
COUPDAYS	12/11/2008
Q: Does the COUPDAYS function take into account if the coupon date falls on a weekend? A: I've not used the function myself, but the help file would seem to indicate that it doesn't - so I'd ...
Display the count of words in msgbox in word	12/11/2008
Q: When we try to find a word in word document we use Find option and if we want to find all the words ... A: I'm not quite sure what you are wanting to do, so this google search ...
Cross-stitch in Excel?	12/11/2008
Q: I was wondering if there was a way to either set the transparency in a picture or make the gridlines ... A: I'm not SURE you are going to be able to do it - ...
excel linking worries 2	12/10/2008
Q: Follow-up to "excel linking worries" 12/08/08 Several questions on macros: 1: Seem to receive an ... A: You are doing very well for a first macro! The FORMAT instruction is an instruction to format a ...
sum if problem	12/10/2008
Q: I've come across a sumif problem I hope you can help overcome. In column N5:N10 i have a range of ... A: If I read this correctly, you want to sum the values if n is, when rounded, equal to m - if so, the ...
Excel difference in 2000 to 2003	12/10/2008
Q: I have the following Visual Basic Marco that I used in 2000, but I can't get it to run in 2003. ... A: I've loaded up office 2003 and used your function - it works fine - however, it does need to be in a ...
Pivot Table	12/9/2008
Q: When I create pivot tables using the wizard, and view the output - I notice when there is more than ... A: I'm not quite sure what you are trying to achieve - the pivot table would consolidate on the ...
excel linking worries 2	12/9/2008
Q: Follow-up to "excel linking worries" 12/08/08 Several questions on macros: 1: Seem to receive an ... A: (1) if it's a new workbook all the time then dim MySheet as workbook set MySheet =workbooks.add ...
Colour formatting of cells	12/9/2008
Q: I need to have the text colour of a cell change according to the value of the number entered. ie if ... A: You didn't specify the version of excel, but it's basically conditional formatting - other than ...
Excel linking worries	12/8/2008
Q: I am creating an excel file that has all our forecasting information for multiple geographies. I ... A: Your assumption is correct, but I would expect to end up with a macro that followed a logic - which ...
Excel linking worries	12/8/2008
Q: I am creating an excel file that has all our forecasting information for multiple geographies. I ... A: IF the managers are only VIEWING the report, then I'd be inclined to keep everything unlinked in the ...
excel subtracting of multiple cells	12/8/2008
Q: I am trying to build a spreadsheet to track the miles (from the odometer) i drive day to day, but ... A: It SOUNDS as though you want the total from a given date - so =Max(AllValues)-StartValue is the ...
Excel	12/8/2008
Q: I am trying to set up a scheduling assistant in excel to better schedule group meetings with ... A: I'm probably missing something, but my understanding of this is that you put an X in a cell, and ...
excell if statement	12/7/2008
Q: On an excell document if you have a score (for example 91) that you want to change to a letter grade ... A: I'd probably do this with a lookup as that gives more flexibility but (assuming in this example that ...
Excel Formula Help,	12/7/2008
Q: My problem consists here. I have a cell with the values of this in the cell O10A2X4C1. ( this cell ... A: Why is 010 equal to 01 but 011 not equal to 01 - if you can give a rule that can be applied, I'm ...
Create 2 Column Table in Microsoft Works 8.5	12/6/2008
Q: ..I want to be able to create a very simple 2-column table (for doing household appraisals). First ... A: never having used works, I'm a little lost - IF this was excel, then I'd put a formula at the top ...
count if issue	12/5/2008
Q: Im trying to create formula to count how many times x shows up in certain row while in the same ... A: a multiple condition countif can be done with the sumproduct function ...
macro coping data into blank cells	12/5/2008
Q: What i have done so far it`s: in column D1:D10 once there is any data it`s being copied into I1:I10 ... A: Sub Button1_Click() Set sCol = Range("D1:D10") For Each cell In sCol If cell.Value = "" Then ...
VBA	12/5/2008
Q: I need to write a subroutine that first prompts the user to enter an odd integer. The number is the ... A: I'd PROBABLY get the number stored on the spreadsheet first, but Dim n As Integer n = ...
Conditionally Formatting in Excel 2007	12/4/2008
Q: I have Excel 2007 and I need to conditionally format (fill with color) rows based on text rather ... A: It sounds like you are almost there - but conditional formatting applies to the cell - so do one ...
Copy selection to another sheet by clicking on a button	12/4/2008
Q: I created a button called "APPROVED". It is linked to the macro called "APPROVED". I have an endless ... A: It sounds like you've stopped the macro part way through - I THINK activecell.entirerow.copy ...
Date recognition function	12/4/2008
Q: Is there a formula where I can type in any date and excel can return the next end of quarter date, ... A: I'd work something out for you but it seems to have been done already - see ...
excel chart options	12/3/2008
Q: We are right now working with Excel 2008 on Mac and would like to present our data in a bar-chart. ... A: I'm guessing you posted this to lots of experts (or at least 2) as I'm not Craig. I'm afraid I know ...
formulas	12/3/2008
Q: I have an excel spreadsheet template with 3 columns. Column A is a list of accounts, column B is ... A: You could do it with formulas, but it would be an array formula and fairly complex - easier I think ...
Copy selection to another sheet by clicking on a button	12/3/2008
Q: I created a button called "APPROVED". It is linked to the macro called "APPROVED". I have an endless ... A: You didn't include the macro and I'm not sure if the destination will always remain the same - but ...
Overflow Error	12/3/2008
Q: I am getting a Runtime error'6': Overflow On this line in excel: ActiveSheet.Cells(Stus, 7) = ... A: Nothing that I can see is obviously wrong with the code - however, instead of setting the value, why ...
Spreadsheet display "disappearing".	12/1/2008
Q: I have started having the worksheets partially "disappear" as I am working on them. Sometimes I am ... A: This is one of the "features" of excel (possibly spelt with the letters B U G normally) - I notice ...
Forecasting Year total	12/1/2008
Q: I have a forecasted total for 2008 (AL34) which needs to be based on the total for the year so far ... A: It wasn't easy to see this from the picture, but I'm hoping this makes some form of sense - if I can ...
MS Excel: Deleting specific row on spreadsheet	11/29/2008
Q: I would like to create a macro that will move through a spreadsheet and delete a row based on set ... A: Your macro refers to the ActiveCell but NEVER changes the active cell - try Sub Deleter() Set ...
conditional formatting	11/29/2008
Q: In Excel 2007 (or in XP), is there a way to define 2 conditions for a rule to take place? For ... A: I'd nearly always use the formula option, but in this case there is no need IF the cell being ...
delete duplicate rows in excel file	11/29/2008
Q: How to delete alternate rows in the excel work sheet? A: In your title you've said duplicate and in the question alternate - so I'm not sure which you need - ...
Excel Date calculation	11/28/2008
Q: Adrian, I thought of a requirement that would prove useful when sending e-mails using my e-mailing ... A: This is much easier - excel stores dates as serial numbers, so although they display as whatever you ...
Excel duplicates	11/28/2008
Q: I need to select (not just highlight) all duplicates in a column of my excel spreadsheet. Could you ... A: I'd use a helper column (a currently blank column) and then use a countif function ...
convert text into numers	11/27/2008
Q: I try to change letters into numbers. How do I do that? ANSWER: I take it you have text that ... A: =(SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,"w","")))*3)+SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,"d",""))) ...
convert text into numers	11/27/2008
Q: I try to change letters into numbers. How do I do that? A: I take it you have text that represents numeric values - like three hundred and want to convert to ...
data sorting and matching	11/26/2008
Q: Please take a look at this excel sheet I made up: ... A: As you can probably guess, I'd do this normally by using visual basic - and I'd have written the ...
Excel "fill" handlebar	11/26/2008
Q: I'm working in a spreadsheet where I want to copy data exactly from one row to the three rows below ... A: I'm not aware of any reason for the function misbehaving, but I'd do it differently - select the ...
Excel formula for difference between dates	11/25/2008
Q: I am in need of a simple Excel expression that will produce the number difference between two dates, ... A: I'm not 100% sure what you mean by the data analysis tool, so if any of this is stuff you've already ...
Convert .XLS to .CSV	11/25/2008
Q: While doing a google search, I read your answer to the following post: ... A: I take it that you've tried saving as CSV and opening in word and NOT getting the required format - ...
copy and paste and replace in a new cell	11/25/2008
Q: I have a formula: MSO - other# 222,5 1/2" X 3/4" - Walnut,as per template which I would like to ... A: Yes, but you'd need to use an event on the worksheet - as Private Sub Worksheet_Change(ByVal ...
copy and paste and replace in a new cell	11/25/2008
Q: I have a formula: MSO - other# 222,5 1/2" X 3/4" - Walnut,as per template which I would like to ... A: Not sure if this is a specific cell or the active cell but Sub ReplacingMacro() Range("A1").Value = ...
Excel formules	11/25/2008
Q: I am trying to copy some formulaes - when I do, the formula shows as having copied ok but the value ... A: Unless I'm wrong, the very bottom left of excel shows the word CALCULATE - this indicates your sheet ...
"AND" and "OR" Operators	11/24/2008
Q: You were so helpful on my previous question. Now I have one of a more general nature, using OR and ... A: You said macro, so ... If range("A1").value="ABC" or range("A1").value="DEF" then 'do events end if ...
In VBA, estab range from A1 to LastCell	11/24/2008
Q: Using Excel 2003, I read in an unstructured worksheet with my macro. I use the following statement ... A: It doesn't matter how much you know, there is always something more to know! In this case, I ...
In VBA, estab range from A1 to LastCell	11/24/2008
Q: Using Excel 2003, I read in an unstructured worksheet with my macro. I use the following statement ... A: Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select will do it for you (not the only way, but ...
Navigating in Excel	11/23/2008
Q: I have an extensive spreadsheet consisting of several sheets and books. All in-actice sheets/books ... A: It sounds like you are largely there - By the sound of it you have a working userform BUT it insists ...
MS Excel help	11/23/2008
Q: I have a problem in MS Excel spreadsheet, I have a weekly data of NASDAQ like Oct 17, 2008, Oct 10, ... A: I THINK that this is simply a vlookup =vlookup(what,where,which,logical) The WHAT is the value you ...
Pivotitem Value Causing Visible Field Error	11/23/2008
Q: Could you please advise what could be the reason why the main PivotItem has a date value in one ... A: The error is a slightly less than helpful one as it's one of the "catch-all" errors - but the FORMAT ...
Facebook	11/22/2008
Q: Ok i know this might not be on your range, but I didn't know where to go. On facebook (no other ... A: I can't really help as such - it's ODD that it's only happening on one website, but I have sometimes ...
Data Validation	11/18/2008
Q: Aidan, Thanks for answering some of my questions in the past. I have been working on this ... A: Data validation will work for the weeks - you can set this up so it will allow any number between 1 ...
Displaying zeros in cells	11/18/2008
Q: Aidan, I have a spreadsheet which I will use for a mail merge. I cannot get the zero's in the zip ... A: You could format the cells as custom format of 00000 BUT that won't help when you come to merge - so ...
excel	11/18/2008
Q: I have a database with many columns, but focus on 3: name, examination-date and country. How do I ... A: This is basically a multiple condition COUNTIF - which can be done with sumproduct ...
Excel VBA copy file	11/18/2008
Q: Good morning! I have an excel file that I use as a sort of reports distribution center. This excel ... A: The NAME command will rename a file in it's current directory - which isn't what you want to do - ...
MS Excel help	11/18/2008
Q: How do I sort successive 30 numbers in a column and put them in seperate new coloumn. Like I have ... A: You can either use an external link =[yourSheetLinkNameandpath]Sheetname!CellRef OR what would be ...
MS Excel help	11/17/2008
Q: How do I sort successive 30 numbers in a column and put them in seperate new coloumn. Like I have ... A: ROW() will return the row number of the cell with the formula - so if entered on A1 it returns 1 ...
merging in excel	11/17/2008
Q: my question is, I realized that I canot merge more then 2 cells together on excel without loosing my ... A: Not sure what format your data is in at the moment - if it's in rows, I'd be inclined to do a mail ...
Excel If Function	11/17/2008
Q: I have a spreadsheet with thousands of rows of data, which is imported from another system. It is ... A: If you are using another column, I'd be inclined just to use the YEAR function, which would return ...
excell formula	11/17/2008
Q: I need a formula that will only total the amounts in a row or column that are not highlighted. A: There is no formula that will do this - but it may be possible with VBA - I'm not 100% sure what you ...
Deleting sheets	11/16/2008
Q: I have a question that Im sure is a really simple fix, yet I can't figure it out. I have created a ... A: excel will print the used range unless you tell it differently - if you press Ctrl End you will ...
Summarizing by date	11/15/2008
Q: I have a column of dates (column name "Purchase Dates") and want to count how many instances occur ... A: If you are entering the formula in column A then you would get a circular reference if you refer to ...
MS Excel help	11/14/2008
Q: How do I sort successive 30 numbers in a column and put them in seperate new coloumn. Like I have ... A: I don't quite follow the question - by successive numbers do you mean the values from row x to row ...
How many and at what time	11/14/2008
Q: Douglas, Any assistance on this would be wonderful and I appreciate the time that you take for ... A: I would suggest using a helper column to store the rounded date/time - the formula would be ...
Excel sumproducts	11/13/2008
Q: I'm trying to get sumproduct to work for me. I have cell in sheet that has this formula. The ... A: the problem is that the first part is returning true/false answers which don't evaluate numerically ...
Extracting data from spreadsheet	11/13/2008
Q: I am using office 2003 I have a spreadsheet showing information on different projects. The projects ... A: A filter WOULD work providing you select the range you want to filter and then use data, filter auto ...
Comparing and highlighting cells	11/13/2008
Q: I have 2 worksheets. Below are the fields: Worksheet1: Id, Owner, Date, Y, Z Worksheet2: Id, Owner, ... A: You'd need to use conditional formatting to handle it, which would BASICALLY Need to do a COUNTIF to ...
filtering	11/13/2008
Q: I HAVE A QUESTTION ABOUT FILTERING IN EXCEL. I HAVE 3 SHEETS THAT HAVE ONE COMMON COLUMN(A) WHICH ... A: Not sure if you are doing ALL sheets - if not, this would need some amending to check something ...
Updating powerpoint with excell	11/12/2008
Q: I have a file in excel and I copied that page to powerpoint as link paste. My excel data is ... A: I'm not at all familiar with powerpoint - there is probably an object model available for it that ...
backward compatibility	11/12/2008
Q: I have created a series of Pivot Table files in Excel 2003. However, my end users have Microsoft ... A: Odd, in that I THOUGHT vlookup had remained consistent throughout versions - my instructions were ...
excel	11/11/2008
Q: in excel, how do i increase the mid 6 digits without changing the starting 4 and the ending 4 digits ... A: OK - so try =value(left(CellRef,4) & 416601 + row()-1 & right(CellRef,4)) This assumes the formula ...
creating list of data from several worsheets into one worksheet	11/11/2008
Q: Aidan, I have created a excel file that contains several tabs with specific information on them. ... A: There are, I think, two approaches to this - the manual one is to use data, filter, auto filter on ...
excel	11/11/2008
Q: in excel, how do i increase the mid 6 digits without changing the starting 4 and the ending 4 digits ... A: not sure how you WANT to alter them, but I'd do it (probably) by using a blank column to do the work ...
How to create a formula iteration	11/10/2008
Q: I have one spreadsheet I'm updating daily with call center data for all skills (multiple groups). I ... A: I'd do this with the offset function =OFFSET(Sheet1!$A$1,(ROW()-1)*10,0) will give you cell a1 ...
Excel buttons	11/10/2008
Q: I have a worksheet with a macro that works great. I have a button on the sheet to run the macro. The ... A: You didn't say which button, but I would IMAGINE this is a button from the FORMS toolbar - if so, ...
excel question	11/10/2008
Q: In the attached picture what i need to do is be able to enter a value in cell F10 thru I10 on the ... A: I can think of two ways of achieving this - the non macro solution would be to store the data on ...
SPREADSHEETS	11/10/2008
Q: I HAD MADE A SPREADSHEET UP, WITH EIGHT DIFFERENT TABS, THE FIRST OF THE TABS HOLDS ALL THE OTHER ... A: This will need some VBA to achieve - if that is acceptable, I can give you the code but I'm not sure ...
preventing a cell from changing	11/10/2008
Q: I have one sheet in a workbook that is linked to another, the first sheet contains raw data and the ... A: I'd probably do this via a macro, copying the existing data to a workrange, then importing the new ...
Excel- reminder	11/10/2008
Q: Good day. Would like to ask if possible to create a reminder saying that I have termination date ... A: Not quite sure what you had in mind, but you could use conditional formatting to shade cells based ...
Excel Formula Question	11/9/2008
Q: I am a spreadsheet with an unique ID in column A and a second sheet with column 2 having the same ... A: That you are getting an #N/A error means that, to Excel, there are NO matching values in the range. ...
Excel Formula Question	11/8/2008
Q: I am a spreadsheet with an unique ID in column A and a second sheet with column 2 having the same ... A: I do try NOT to offer dumb responses - the problem I have here is that the response I need to give ...
merge from excel to excel	11/7/2008
Q: Can I merge from an excel document to another excel doc? I need instruction as to how this works as ... A: I'm not sure what you need to "merge" but I would expect that the easiest way to do it would be via ...
Hyperlinks	11/7/2008
Q: Sir I have 1 Master sheets and there are 400 Company name with there detail. I make the worksheets ... A: the [book1] part needs to be the name of the spreadsheet - and once it's saved, it stops being book1 ...
Hyperlinks	11/7/2008
Q: Sir I have 1 Master sheets and there are 400 Company name with there detail. I make the worksheets ... A: Sorry, yes, the hyperlink function allows for a "friendly name" so just add a comma and the name - ...
Formula Problem	11/7/2008
Q: Hope you can help with this, I'm having a problem with one of my formulas.I have a database that ... A: This is pretty much what you explained was happening - I GUESS we could amend the formula to sum ...
Hyperlinks	11/7/2008
Q: Sir I have 1 Master sheets and there are 400 Company name with there detail. I make the worksheets ... A: (1) if you right click the scroll bar to the left of the tab names you can select any sheet you want ...
Excel Telephone Number formatting	11/6/2008
Q: Adrian, I thought of a requirement that would prove useful when sending e-mails using my e-mailing ... A: I THINK you would need to do this with a macro such that on the change event of the worksheet, IF a ...
Formula Problem	11/6/2008
Q: Hope you can help with this, I'm having a problem with one of my formulas.I have a database that ... A: I cannot see any reason why this would be happening - I'm not aware of anything inbuilt to excel ...
Combining 2 sets of data on a common column	11/5/2008
Q: I am using Windows Xp and Office 2003 I have 2 sets of data one is sales for 2008 by p/n the 2nd is ... A: I'd use the 2007 data, and then use a VLOOKUP function to look up the equivalent value from the 2008 ...
Pivot Tables in Excel 2007	11/4/2008
Q: I have many worksheets which were created in Excel 2003 with pivot tables. When I open these ... A: You are PRESUMABLY working with the files in compatability mode - in that mode, source file ...
ERROR FORMULA	11/4/2008
Q: I am inputting a simple IF AND formula,to obtain ranges. the first formula works correctly, however ... A: Excel has a limit on the amount of nesting you can do - it's up to 7 levels and you have MORE ...
number combinations	11/4/2008
Q: I just started using excel 2002. i don't know how to do number combinations. i need a list of all ... A: There are a grand total of 20,358,520 possible combinations - this is easy to calculate using the ...
Create a link to toggle button	11/1/2008
Q: im not familiar with toogle button. But am doing up a questionaire. So like to have the toggle ... A: A toggle button isn't what you want, as this would enable you to select one of (usually) two options ...
Protect macro from VIEWING, not USE	10/31/2008
Q: I just followed your answer to a question where the person wanted to insure a macro be used only by ... A: You haven't said what I advised (!) but the method I gave (I hope) should be what you need - in the ...
Multiple rows make a single order and Pivot table	10/30/2008
Q: I have a spreadsheet with order ID title and several rows collectively constitute one order ... A: I'm not sure what you want to do in terms of consolidation, but it sounds as though a combination of ...
Permanent Date in Excel	10/30/2008
Q: I want to autodate a cell when another cell is eqaul to a value in a table, but I want that date to ... A: There is no function in excel that will return a non volatile date SO you would need to make use of ...
Converting Seconds to Minutes:Seconds	10/29/2008
Q: I've got a spreadsheet of data, pulled from the Avaya phone data system, all data comes into Excel ... A: You need to divide your number by 86400 (60 seconds multiplied by 60 minutes multiplied by 24 hours) ...
Excel 2003 vs 2003	10/29/2008
Q: I have reverted to v2000 since v2003 seems to have an aversion to CSV files - they are seen as excel ... A: heritage@virgin.net. This wasn't something I saw when opening CSV files on my copy of 2003, but I ...
Un-protect Textbox / Protect sheet	10/28/2008
Q: I got many textbox in my worksheet and i need to entry some text in it. To facilitates the process, ... A: I suspected you might be Canadian as the large majority of questions come from USA/Canada - I'm not ...
Excel vlookup	10/28/2008
Q: I have a multi-tab worksheet that i want to populate data from one tab to the column headings on ... A: You could do it with formulas, using offset BUT you would need to pre-populate the formulas to be ...
Un-protect Textbox / Protect sheet	10/28/2008
Q: I got many textbox in my worksheet and i need to entry some text in it. To facilitates the process, ... A: I'm slightly puzzled in that any textboxes I would enter on the sheet WOULD be open to data entry ...
Help with COUNTIF	10/27/2008
Q: I have a spreadsheet with ticket information, and one of the column (say, Column C) is the Ticket ... A: Its one of the annoying features of excel that it works this way. But it does, so we have to work ...
EXCEL 2007	10/27/2008
Q: I am ew at functions and trying to figure this problem out. I have to create a spreadsheet based on ... A: I have two problems here (1) this sounds like a homework question, which, although I don't mind ...
Excel : rows to repeat at top	10/25/2008
Q: Please help me on this : I have standard format of Excel sheet that I often have use in office ... A: You can repeat a block of rows, but NOT a discontiguous range I'm afraid - you COULD amend the ...
excel email	10/25/2008
Q: I have Vista with Office 2007. I'm trying to e-mail an excel sheet only, not the whole workbook. ... A: It's not there as an option on it's own, but I can think of a number of alternative approaches 1 - ...
Excel 2003 vs 2003	10/24/2008
Q: I have reverted to v2000 since v2003 seems to have an aversion to CSV files - they are seen as excel ... A: I have had no issues with CSV files in 2003 - but I cannot visualize what you are describing - it ...
Formula	10/24/2008
Q: I am utilizing a formula that requires the number to be typed utilizing a comma to separate the ... A: The first way is via control panel, where you can set international settings which would allow you ...
Conditional concatenation of vertical cells	10/23/2008
Q: I'm hoping you might have a suggestion for the following conundrum which completely exceeds my ... A: does the data actually start as Excel format, or is it perhaps a CSV or TEXT file? My feeling for ...
How to link a spread sheet to a pivot table for data	10/23/2008
Q: I have one sheet on a workbook that shows totals per unit, three of the columns of this sheet have ... A: I would use a VLOOKUP function to look up in a static range (that of the pivot table) the values you ...
vb in ms word	10/23/2008
Q: Can you help me with vb in word?--Tom Ogilvy thought you might be able to help me. I want a macro ... A: Sorry about that - I made an assumption that Word would be happy without EXPLICIT statements, but I ...
Getting a True or False result in a cell in comparison to a data range	10/23/2008
Q: I am trying to generate a sheet that will identify which pricing level to use base on 'CCM" space ... A: Its very difficult to follow this from the picture - possibly (probably) due to my eyesight and the ...
Format merged result in Word 2003	10/22/2008
Q: After merging an alphanumeric field in MS Access to Word, I want the result formatted. I want ... A: I'm not aware of any such format - and if the field is alphanumeric, a date formatting switch ...
Excel - Extracting certian data to new spreadsheet.	10/22/2008
Q: I hope you can easily answer my query. I have a main spreadsheet into which we enter all invoice ... A: I'm never THAT keen on having data moved to other sheets, but that's probably just me - the method I ...
vb in ms word	10/22/2008
Q: Can you help me with vb in word?--Tom Ogilvy thought you might be able to help me. I want a macro ... A: Your macro is pretty close, but as you say it's doing a for..next loop for each column in the row - ...
Macros	10/21/2008
Q: If you are able please help. I am trying to write a macro that will search through a list of ... A: I think I'd be inclined to do this with conditional formatting using a formula =a1<>a2 and auto ...
VLookup - Data issues and #N/A	10/21/2008
Q: I am trying to write a macro that will collect data from an audit sheet and cross-reference it with ... A: What you haven't given me is the declaration of ID - is this a string or a numeric variable - it's ...
Accessing builtin function	10/21/2008
Q: I am working in Excel 2007 and I am trying to use the search function. The below is a snippet. ... A: You don't make it totally clear what the macro is trying to do but I would PROBABLY in this instance ...
excel - move or copy sheet	10/21/2008
Q: I am trying to copy a sheet to another workbook but the option is not available. The sheet is not ... A: The only way I've managed to duplicate the problem is with protection - which MIGHT be the case for ...
Excel and possibly VBA?	10/20/2008
Q: I'm very sorry to bother you, but I have what I consider to be a very daunting task. I have ... A: (1) - is it actually allowed to enter a non permitted value?! The use of data, validation would ...
counting cells	10/20/2008
Q: I have a spreadsheet in Excel, with actual / forecasted spends for every month in columns (from ... A: I think a Y at the start of each column is probably OK, but as you say, using a formula to achieve ...
Reorganize and sum data based on date and time	10/20/2008
Q: I have a table of downloaded credit card data that I need to reorganize and sum based on date and ... A: I would GUESS that you aren't in the US - my fault for making this assumption due to the format of ...
vba	10/20/2008
Q: How r u? I ve got a tricky situation here. I have a workbook with sheets containing many ... A: I don't quite follow the update by X as you said this was only if blank (I'd test the LENGTH fo the ...
hyperlink macro	10/18/2008
Q: I'm trying to make a macro that copies the data from a selected range of cells on the active ... A: Its possible indeed - the original answer did say how - the recorded macro serves as the base for ...
Excel Macro For Sending E-Mails	10/18/2008
Q: Richard,I wonder if you could possibly help me. I am trying to install the following macro in my ... A: Best answer here I think is to point you to Ron DeBruins site http://www.rondebruin.nl/sendmail.htm ...
RE: spreadsheet updating macro	10/17/2008
Q: Sure hope you can advise me on this one. I've got a standard excel (V.2003)spreadsheet. I'm doing a ... A: My last part is PROBABLY a tad confusing - I'm BASICALLY advocating some means of looping through ...
EXCEL Autofill Code	10/17/2008
Q: I have a spreadsheet that has A:G. I created a button that lets me goto the last empty cell and ... A: I think I'd do something like for each cell in range("A1:A1000") 'in this example, I'm using a ...
Excel Formula	10/17/2008
Q: I have a # value. I want to look that # value up in a table and return tha word value in the same ... A: VLOOKUP will do this =vlookup(What,Where,Which,Logical) WHAT is the value you want to look up - so ...
Copy cells to another worksheet based on a value	10/17/2008
Q: I have no doubt that you can help me with this as it seems quite a common problem but after ... A: If you look at my macro, you will probably see that I'm using N to denote the ROW - AND this is set ...
Reorganize and sum data based on date and time	10/16/2008
Q: I have a table of downloaded credit card data that I need to reorganize and sum based on date and ... A: As you are going to need a formula anyway to alter the ACTUAL date and time to an effective date, I ...
Excel for Windows Vista	10/16/2008
Q: I have a file in which i have 2 worksheets and i want to populate it so when I type in worksheet 2, ... A: Not clear how you want this to work - are you talking about duplicating values between the two ...
hyperlink macro	10/16/2008
Q: I'm trying to make a macro that copies the data from a selected range of cells on the active ... A: This can be pretty much recorded - as an example Selection.Copy Sheets("Sheet2").Select ...
Count if	10/16/2008
Q: I am trying to count how many cells fall in to categories such as: cells that have ... A: It's an annoying feature of countif that it only takes one condition - you can use the D worksheet ...
Lost drop-down list	10/16/2008
Q: I have Excel 2003. I have a workbook with few worksheets. Every sheet has few drop-down lists. I ... A: At a guess the validation is still working but simply doesn't have the in cell drop down - use a ...
Excel Gradebook Question	10/16/2008
Q: I am trying to create a formula that gives me the 5 overall most missed questions from a test. I ... A: I cannot quite visualise the layout of the data, so my answer is going to be slightly general - but ...
Rearrange Randoms	10/16/2008
Q: Can you help? A1:A10 = 1 to 10 B1:B10 = Random Numbers C1:C10 = Ascending Sorted of B1:B10 (Using ... A: I'm somewhat confused by the question, not least as I cannot see what th