You are here:

Excel/Questions Answered by Expert Victor Lan

Subject	Date Asked
Absolute cell reference	11/16/2009
Q: Victor Lan Why doesn’t the absolute cell reference work in Excel 2007? When I drag a cell which ... A: The example you gave is perfectly correct in both Excel 2003 and 2007. Absolute cell reference does ...
variable data size in a chart or worksheet	11/15/2009
Q: I want to use a worksheet with data of variable length, and plot the data. I import a text data file ... A: SERIES is a special kind of function to define a chart series. It can't incorporate worksheet ...
First Blank	11/13/2009
Q: Hope you are well. I am using the below sumif formula in cell C3. It’s the first cell of about 100 ... A: I'm not sure which worksheet you are referring to with the statement "either B or C is blank, C ...
How to serch speedly ?	11/13/2009
Q: I have 15 MB excel files which is consist of so many formulas. Therefore when I filter & choose ... A: To speed up Autofilter, especially while working with large excel files with many formulas, I would ...
Locked up a column only	11/12/2009
Q: Victor Lam, I hv a question need to ask u about Excel. Can u pls teach me how to lock up a column ... A: Lo, Yes there is a difference between 2003 and 2007. At the first glance, the interface is really ...
ComboBox Value to open a workSheet	11/11/2009
Q: I have a UserForm to enter information of our employees I have a ComboBox with the employees names, ... A: 1) Use the following code to have the user directed to the the appropriate worksheet when the combo ...
Removing hidden names from Excel	11/10/2009
Q: I keep receiving an error in several work spreadsheets relating to duplicate names within a file ... A: After looking at your issue, I have adjusted the code from the sample macro written by Microsoft. ...
Use Excel Shrotcuts in VBA	11/9/2009
Q: I want to use the excel shortcuts such as Ctrl + C, Ctrl + V, Ctrl + A etc...anything in my VBA ... A: Do you mean what you want to copy, cut and paste data using Excel VBA? If so, you can certainly do ...
compare, match and copy between sheets	11/8/2009
Q: sorry to email you directly previously.. below is what im trying to do If cell H of Worksheet A = ... A: The method SpecialCells(xlCellTypeLastCell) simply returns the range of the last cell that is used ...
SUMIF 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: SUMIF is not a 3-D function, thus it cannot be used across multiple worksheets. See ...
Using formulas to sort rows of data automatically	11/7/2009
Q: I'm making a financial spreadsheet. I need to be able to sort 5 columns and up to 20 rows (can be ... A: I looked at the website and it shows us how to use Excel formulas (without VBA) to generate a ...
compare, match and copy between sheets	11/7/2009
Q: sorry to email you directly previously.. below is what im trying to do If cell H of Worksheet A = ... A: The VBA code given is working fine. However, your description of the problem is not too clear. I ...
Using formulas to sort rows of data automatically	11/7/2009
Q: I'm making a financial spreadsheet. I need to be able to sort 5 columns and up to 20 rows (can be ... A: In order to sort it automatically, you will need to use VBA macro to capture worksheet events. ...
Appending specific cells from one sheet to another.	11/6/2009
Q: I am trying to transfer data from one sheet to another using a sub that I can call with a command ... A: I guess the following code will do what you wanted. Attached the VBA sub to a command button in ...
2007 Excel Conditional Formatting	11/5/2009
Q: Victor, This is about a problem I’m having in setting up the right formulas for conditional formats ... A: looking at your conditional formatting it seems that the formula may be causing some issues. Kindly ...
Auto Update WB Data To MHTML Page	11/3/2009
Q: My query is this Lets say I have an excel workbook/sheet in which the data is dynamic. I have a ... A: I need to know how do users access the Excel workbook on the webpage? If it's through a link, simply ...
Auto Update WB Data To MHTML Page	11/2/2009
Q: My query is this Lets say I have an excel workbook/sheet in which the data is dynamic. I have a ... A: Would implementing a VBA event to capture the save event in the workbook, such that it would save an ...
excel sheet combo problems	10/31/2009
Q: Sir, i want to insert a combo box in Columns like as list A: First, select the range of cells where you want the combo box. In Excel 2007, go to Data tab, click ...
Reset drop down boxes	10/30/2009
Q: Is there a way to use a macro, or any type of formula, or code, to reset cells to 0 upon clicking a ... A: Yes, you can use the following VBA code to clear the drop down boxes. To create a button that ...
Auto copy from multiple workbooks	10/30/2009
Q: I would really appreciate your help on the following problem: My company stores daily production ... A: We certainly could implement an automated process (using Excel) in VBA to open each and every file ...
Load external spreadsheets	10/29/2009
Q: I was just wondering what you would say was the best approach for the task I have in hand: I am an ... A: Glad to be of help. After going through the Treeview tutorial from www.excelguru.ca, I found that ...
Load external spreadsheets	10/28/2009
Q: I was just wondering what you would say was the best approach for the task I have in hand: I am an ... A: From what you wrote, it seems that you are looking at creating a master workbook which contains a ...
Macro with limited number of runs	10/27/2009
Q: Please, I want my macro to be used as a trial or evaluation version and after a specified period of ... A: I'm not aware of the various methods that are currently in use to lock Excel VBA code after a ...
If Then Statements	10/26/2009
Q: I am trying to write an IF, Then statement for Excel 2007. I have a spreadsheet that has three ... A: In Excel, go into VBE using Alt-F11. In VBE, double-click on the Sheet name where you wanted to put ...
Drop down list and VBA	10/26/2009
Q: I have an excel workbook with several worksheets. On the first worksheet I have a question in one ... A: Try the code below. Let me know if it works. ...
Drop down list and VBA	10/26/2009
Q: I have an excel workbook with several worksheets. On the first worksheet I have a question in one ... A: the above code is to be inserted into the worksheet (in Excel VBE) where you want the event code to ...
Export picture of printrange	10/26/2009
Q: I have a datarange being (about) 300 rows and using columns A-S. Using appropiate settings for ... A: To achieve the solution, go into Excel Visual Basic Editor (VBE), select References from the Tools ...
macro to prefix & suffix formula	10/26/2009
Q: I have seen your code in reply to Metin Zilan's question Here: ... A: Please find the updated code below: Sub RoundCells() Dim cell As Range For Each cell In ...
SUMIFS in combination with a TODAY() function (Excel 2007)	10/23/2009
Q: In Excel 2007 I would like to use the =TODAY() function as a criteria in the =SUMIFS function. ... A: Sure, I'm happy to help you out. Instead of =SUMIFS(A2:C2;A1:C1;"<=TODAY()"), create a cell for ...
Drop down list and VBA	10/23/2009
Q: I have an excel workbook with several worksheets. On the first worksheet I have a question in one ... A: You can do this using VBA. Please insert the below code into your worksheet VBA project window, it ...
Excel Formula	10/22/2009
Q: How do I combine 2 "if statements" as one? A: Use the following if statements. Play around with it and you will find out how it works. You can ...
If Then Statements	10/22/2009
Q: I am trying to write an IF, Then statement for Excel 2007. I have a spreadsheet that has three ... A: yes it's certainly possible to do that in Excel. We can do almost anything with Excel using its ...
autonumber	10/20/2009
Q: goodday, i am stuck in excel vba. hope that you can help me with this:) ive made a userform that ... A: In cell A1, key in 1 as a value, then create a form button with the following code: (Code below ...
counts	10/20/2009
Q: I have huge excel document with 2 sheets. "Sheet1" contains lots of boxes with initials and date(in ... A: You are welcome, and thanks for the question. This can be done using Excel sumproduct function and ...
Issue with Macro	10/19/2009
Q: I have the following table (Sheet: WBS&Action&Planning) –see appendix- with the following macro to ... A: Sorry, I could not see any attachment in your reply. Also, you could try using the following code ...
Macro to get pop up	10/19/2009
Q: I use Excel 2003. I want a macro wherein i get a pop up to update two cells B2 & B3 (the sheet name ... A: Following the steps below: In VBA, create a Userform with two Label, two TextBox, and one Command ...
If statements and blanks	10/18/2009
Q: What I want to do is be able to input the date and items on one worksheet but appear again on ... A: What you have noticed regarding the output of your IF statement is exactly what Excel will do. In ...
Macro with round formula	10/17/2009
Q: Victor, i would like to thank you in advance for your cooperation. I want to record a macro for ... A: it really helps. I tested the following code and it should do work exactly like what you have in ...
Macro with round formula	10/17/2009
Q: Victor, i would like to thank you in advance for your cooperation. I want to record a macro for ... A: Simply use the following code for your needs: -------------------------------------------------- ...
Issue with Macro	10/16/2009
Q: I have the following table (Sheet: WBS&Action&Planning) –see appendix- with the following macro to ... A: it really helps. (I can see that there is a problem when there are empty cells in between data in ...
Issue with Macro	10/16/2009
Q: I have the following table (Sheet: WBS&Action&Planning) –see appendix- with the following macro to ... A: Sorry, I do not fully understand your question and could not replicate the problem. Could you ...
Autofilter criterion as a macro parameter?	10/15/2009
Q: I am using Excel 2003 and 2007; I hope you can help me with a conceptually simple question, but I ... A: it helps me to answer your question. We just need to change a little syntax for it to work. Please ...
counting with multiple criteria	10/14/2009
Q: I have data in 1466 rows, columns A through H, as referenced below. I need a formula that will ... A: I had tested and it works for me. I suspect your data might contain extra spaces or non-printable ...
counting with multiple criteria	10/14/2009
Q: I have data in 1466 rows, columns A through H, as referenced below. I need a formula that will ... A: It's simple, type the following formula into the cell you want to use for counting the number of ...
Excel: Match and Count with Conditions	10/13/2009
Q: I have a problem finding the correct formula for counting matches with conditions between 2 ... A: I'm most happy to be able to help you automate your working schedule template. Now, in order to ...
Question	10/12/2009
Q: I have 2 sheets, each have a column that codes/values sorted in the column in sheet1 are partly ... A: How are you? Sorry I'm not too sure what you wanted to do from your question. Looking at the ...
Excel Formula	10/11/2009
Q: I hope you are well, thank you for your assistance. I have a simple forula request relating to ... A: I am well, am always glad to lend a helping hand whenever I can. For conditionally formatting for ...
getting a cell to add one in excel	10/10/2009
Q: I am new to the formula and button functions of Excel. I am tallying answers from several surveys, ... A: Yes, the command button is the right way to do this. For this to work, we need to add a simple VBA ...
VMA IF loop statement	10/9/2009
Q: I have this code: Sub xxxxx() Worksheets("name").Activate If Range("D61").Select <> 0 Then ... A: after looking at your example, I believe you want to test for the value at cell D61, E61, F61, etc. ...

All Questions in This Category

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Victor Lan

Top Expert on this page

Expertise

I use Excel at work and can assist you in manipulating and filtering data, creating and using formulas, pivot tables, and writing VBA to solve problems and automate processes. I'm willing to research and find a solution for almost anything in Excel for you.

Experience

3 years of professional Excel experience. I currently work for a global investment bank and our team is responsible for certifying the daily profit and loss of the trading desk. Excel is one of the primary tools used at work.

Organizations
Member of Toastmasters International.

Education/Credentials
BSc Banking and Finance (First Class Honours), University of London. Diploma in Electronics, Computer and Communication Engineering, Singapore Polytechnic.

Encyclopedia

Browse Answers: