You are here:

Excel/Questions Answered by Expert Victor Lan

Subject	Date Asked
excel help	4/7/2010
Q: good day sir. I am Kaye from the Philippines and would like to seek your help. I am currently ... A: To find duplicate companies in your Excel file, assuming that you have one column of companies name ...
Finding Max and Min	4/6/2010
Q: I have a problem I need help with. I have a row of numbers on the top row. i.e. 15 20 25 40 50 80 ... A: Sorry I don't quite understand your question. "I have a row of numbers on the top row. i.e. 15 20 ...
For/Next Loop	4/5/2010
Q: I am trying to set up a For/Next Loop to display some data for a problem that is troubling me. So ... A: The following code will store the values in range A10:F15 into an Array Variant. It then pass it to ...
If else multiple cases macro	4/5/2010
Q: I have a column in which there are many values say V1,V2,V3,V4,V5 now i need to enter some code say ... A: The following code will determine the last row in column D and save it to variable LastDataRow. ...
unusual file size	4/4/2010
Q: i have a exel file that has a file size of 115MB which is unusual. This only had only 5MB abt a ... A: It is quite difficult to pinpoint the issue with Excel file size. Backup your file and try the ...
Macro to automate Find/Replace function	4/3/2010
Q: I have an Excel workbook with six sheets: "Summary", "RK000", "RK001", "RK002", “RK003” and “RK004”. ... A: I understand the frustration of being able to dream of processes to simply your work but not able to ...
IF FORMULA	4/2/2010
Q: I have two cells. A1 and B1. I need B1 to stamp today's date when A1 is populated with ANY data. ... A: You are right, the =today() formula will not work because the dates will be updated everyday. ...
Excel 2007 VBA coding	4/1/2010
Q: I am trying to create a user form that will search different data ranges based on the date selected ... A: From the way you wrote the description of the program requirements, you should have a good ...
Excel and Macros	3/31/2010
Q: So I have been searching around and have not found a way to do this, Hopefully you can help. I was ... A: Yes, there are freewares available that runs in the Windows background which can be set to replace ...
Compare sets of data	3/31/2010
Q: Victor, I appreciate your help with this. For simplicity, I will use numbers. Let's say I have ... A: Excel has a built-in function COUNTIF that can easily do what you need. Use the following formula: ...
pdf to xls	3/29/2010
Q: I copy a document from pdf and paste it to excel, then I want to use "text to columns" to divide up ... A: There is no easy way to make sure copying text from PDF file will be align to your Excel file ...
"finding between 2 cells the max of odd numbers"if this stand do max-min else min+max	3/28/2010
Q: Vivtor! I need your help in a excel formula. the simple excel formula i have is ... A: The following formula is what you need. If cell m3 is odd, cell n3 is odd, and cell m3>n3 then ...
unusual file size	3/27/2010
Q: i have a exel file that has a file size of 115MB which is unusual. This only had only 5MB abt a ... A: Excel file size are known to increase with no apparent reason. Save a copy of your file and try the ...
Make a cell mandatory based on another cell value	3/26/2010
Q: I'm a basic excel user and i need your help for my below requirement . If a value is entered in ... A: It can be done using Excel VBA Macro. However, users could still turn off macros and bypass the ...
VB Macro	3/26/2010
Q: I need to create a function to return a value from one worksheet back to another. Using a field on ... A: The following function will do what you wanted: For cell A1 in WS1 columnA, lookup the corresponding ...
Linked and Non Linked cells	3/24/2010
Q: I have a really large worksheet and I need to link various cells(columns) to a second worksheet in ... A: I am not so sure on the setup of your spreadsheet especially as to how the cells in the second ...
Formula help - splitting text in a cell into multiple cells limited by character length	3/23/2010
Q: I am hoping you can help me modify a formula that I am struggling with ? Basically I have a ... A: It is best to use a user-defined function (UDF) for your needs. The UDF is a function that is stored ...
macro to copy rows with particular value in column k	2/25/2010
Q: I have a sheet with 3000 rows and I am looking for a macro to copy the rows having a particular ... A: The following macro will do what you are looking for. It will copy the entire row in Sheet1 based ...
Paint Tiniting Formula	2/25/2010
Q: Using Excel, I am trying to create a formula to convert a a paint tinting formula automatically from ... A: I don't understand your example. Could you further explain? I assume that if Y = 48, simply change ...
Scheduling Time problem	2/24/2010
Q: My question is a formula based question using Excel 2003. I work in logistics and am trying to ... A: In order for your formula - IF(Shipping time <=Scheduled Time,HIT - to work, it needs to detect the ...
Dropdown menu seperate value help	2/23/2010
Q: I have a dropdown menu on sheet one, that pulls data from sheet 2. The dropdown select data are ... A: To have the monetary value associated with the value in the dropdown (data validation) shown in a ...
Create a table with macro	2/22/2010
Q: Good Morning Victor, I need your help in creating a table with macro, I don't need to create it ... A: To create a table in Outlook using Excel VBA, create a HTML mail in Outlook. Please see the ...
Excel calendar vba	2/22/2010
Q: Good morning Victor, Can you please help me I am very new to vba and would appreciate some help ... A: I'm happy to help. I made some small changes to your code so that it will continue to select the ...
Hyperlinnks in Comments	2/21/2010
Q: I'm trying to insert a (working) hyperlink into a comment box in Excel 2007. Can it be done? Thanks ... A: Hyperlinks are not available in Comments. However, it is possible to use VBA and Autoshape to ...
Create a table with macro	2/21/2010
Q: Good Morning Victor, I need your help in creating a table with macro, I don't need to create it ... A: Do you mean "Excel Table" in Excel 2007 which was previously known as a "list"? You need a range of ...
Calculating time Question	2/20/2010
Q: I wonder if you can give me some guidance. I have a sheet in Excel 2003 that is populated with ... A: I am not following your question well. It will help me to understand if you could provide an example ...
Follow up Question	2/20/2010
Q: A couple of days ago I asked you a question regarding Data Validation. Your response was very ... A: I appreciate your feedback. Now, for your question it is certainly possible. However, you have to ...
VBA for Creating Pivot Table	2/19/2010
Q: Greetings!! I have a worksheet of data with 8 columns (date, category, customer, month, unit, ... A: Greetings. Thanks for writing to me, I would like to know if you managed to create the Pivot Table ...
macro for alert and pop-up	2/18/2010
Q: I have excel file which receiving data from DDE. What I’m trying to accomplish is to create a sound ... A: In order for the macro to check the values in the spreadsheet when cells are updated, use the ...
Single reference with multiple values	2/18/2010
Q: How do I reference a single object that has more than one value located in separate rows/columns? ... A: You can use VLOOKUP in worksheet 1 cell D2 =VLOOKUP(A1,Sheet2!A:C,2,0) to retrieve the value in ...
continuing question	2/17/2010
Q: I had to ask a new question. you replied; May I know why your formula in cell G5, is ... A: The formula I provided will work if you manually change it to adapt to each cells as required. I do ...
continuing question	2/16/2010
Q: I had to ask a new question. you replied; May I know why your formula in cell G5, is ... A: You could re-write the formula in your conditional formatting in cell G5 as ...
formula to get the result vertical	2/15/2010
Q: kindly help me that how i can compile the article location in one cell from different cell location. ... A: There are no formula in Excel that can search for a particular "article" and return multiple ...
merging and consolidating	2/13/2010
Q: I have 5 excel sheets.I need to merge unique employee ID's from onlycolumn A from all workbooks to a ... A: I tried to run the VBA code and get the same error message as you have on the following statement. ...
connecting multiples row into one row	2/13/2010
Q: how can I connect multiple rows of data that spread over several columns into one row spreading over ... A: I do not know of a simple way other than to cut-paste the values. If it's really needed, we could ...
Date stamp needed with referance to another Cell content	2/12/2010
Q: My need is getting date punched automatically in "P" when some one select "Closed" in "O". I already ... A: The below code will do what you wanted. ...
Conditional formatting can be VBA	2/11/2010
Q: I have some basic knowledge of vba. ok. heres what i have so far. 3 colors with conditional in ... A: I looked at your spreadsheet and the conditional formatting. Instead of using a more complicated ...
two columns	2/11/2010
Q: I have two column lists (one value in each cell) the first {A B C C C B B A A C} and the second {1 2 ... A: Use the SUMIF function. If the first column list {A B C C C B B A A C} is in A1:A10 and the second ...
Conditional formatting can be VBA	2/9/2010
Q: I have some basic knowledge of vba. ok. heres what i have so far. 3 colors with conditional in ... A: Sorry I am not really following your question well. Are you using conditional formatting with ...
Excel	2/9/2010
Q: I am looking to see if Excel is capable of highlighting a cell red when a certain word is written in ... A: Excel is capable of highlighting cells with a certain word. Use the Conditional Formatting feature ...
Drop Down List + Auto Columns	2/8/2010
Q: Trust all's well with you.I'm a managing partner of a language school and we currently offer ... A: It is certainly possible. Debra Dalgleish a Microsoft Excel MVP professional has an excellent guide ...
summtion of base 20 etc	2/6/2010
Q: Hay! I m zulfiqar and need help in excel Sir, As when we add the time in excel for example A1 = ... A: Yes, it is possible in Excel to get the value of 33:12:06 in A3 using different base value for time. ...
Search for a word across multiple excels	2/5/2010
Q: I have more than 100 excel files on a local drive. Is there any way to find rows containing a ... A: Yes, it is possble to match the text string "Mentor" across 100 excel files and copy all rows with ...
Pivot Table Running Account Balance	2/5/2010
Q: I have created a Project Finance Reporting Tool(which comprises Cash Book, Account Receivable and ... A: I would need a the working spreadsheet in order to follow your description. If you could send the ...
lookup data from multi workbooks	2/4/2010
Q: I am trying to lookup data from multi workbooks based on a user entering a date. The workbooks I ... A: You can use the following formula to look up data from your workbook - Chatt.Sched.week of 2-1.xsls ...
Percentage Formula – Excel 2003	2/3/2010
Q: I have two columns; one showing date as reported by a supplier i.e. (Date 1) 26/11/2009. The other ... A: Using a simple "Date 2 - Date 1" gives you the number of total days that elapsed between that ...
Excel and the number Zero	2/2/2010
Q: How can I stop zero from disappearing? My column requires subdivision numbers which frequently begin ... A: I think you have to format your cells. To show 6 numbers in a cell with leading zero, right-click on ...
Excel "If Then" formula	2/1/2010
Q: I am trying to run a formula that calculates how far into a "commission table" my employees are in ... A: Instead of nested IF functions which is long and complicated, use VLOOKUP with a commission table to ...
Data Matching	2/1/2010
Q: I am looking for a excel formula to match two sets of values located on two separate worksheets. ... A: You can use the MATCH function with a wildcard inside a INDEX function to return the value in column ...
Macro troubles	1/29/2010
Q: Victor, I work for a restaurant, and I am trying to create an excel template that will accumulate ... A: The problem lies in the CurCell of the For Each loop. CurCell is referring the to current selected ...
check if a found file is open with VBA	1/28/2010
Q: I am a beginner to medium Excel VBA user I have a worksheet containing a filename in cell B1 and an ... A: You can use the following code to test if the workbook is open. ...
Help with VBA	1/27/2010
Q: Can you tell me a macro which would save a excel file and then open it. A: There is no need to open the excel file that was saved as the Excel file would still remain open ...
Freeze the headers	1/27/2010
Q: How can i Freeze the header i wish to see the header always even i scroll up/down.My header reciding ... A: Even if the header is reciding in the middle of the worksheet, you can use Freeze Panes (under ...
Colourcoding dropdown lists and conditional formatting	1/26/2010
Q: I have created a dropdown list using M, K, 1, 2, & 3. I need to assign a colour to each letter. i ... A: On Excel versions earlier than 2007, there is a limit of three conditional formats per cell. In ...
excel on google docs, summarising and coverging sheets	1/26/2010
Q: I hope you can help- i have had a look around and since im fairly new to excel i can't seem to ... A: As seen in my example, in the Summary sheet cell A1, type the following formula: =Data!A:A followed ...
VBA help	1/25/2010
Q: I have two tabs (M1) and (M2), M2 has all my data on it, the M1 is a table that is linked to Access. ... A: May I know if you are proficient in VBA? For the setup, a macro can be written to copy the vlookup ...
excel on google docs, summarising and coverging sheets	1/23/2010
Q: I hope you can help- i have had a look around and since im fairly new to excel i can't seem to ... A: In Google Docs Spreadsheet, if you want a master sheet that shows selected columns of other sheets, ...
Matching and suming data between sheets	1/21/2010
Q: I have two sheets within one excel (2007) and i can't seem to figure out how to do the following ... A: However, I'm not sure what to summarize in sheet 3. To sum up/add the values that fit the three ...
data enquiry	1/20/2010
Q: I have two work sheets; one is essentially a data sheet where I have a very long list (900+) of ... A: If you are typing the property reference (column A) in your table, you can use the VLOOKUP function ...
Help on Macro Simplification	1/19/2010
Q: I'm just new to VBA, just tried it out last week. I would like asked if this macro can be ... A: I have simplify the code. It will not switch between worksheets when copying, and this will improve ...
VB Question with Pivot Tables	1/18/2010
Q: I'm looking for a solution with VBA. My proficiency is to the extent I can sort of review code and ... A: It sounds like something I have seen before. However, I'm not too sure if I understand all you ...
VBA issue sorting on a named range	1/18/2010
Q: I am modifying some recorded macros created by a colleague of mine to make the process more ... A: I did not receive your email, could you copy and paste my email address and try again: ...
VBA issue sorting on a named range	1/15/2010
Q: I am modifying some recorded macros created by a colleague of mine to make the process more ... A: You don't need a selection object to sort but a Range object. I'm not sure why you have the error ...
Compare two lists of names	1/15/2010
Q: I really hope you could help me with this. I have two lists of names that I need to compare. ... A: In order to do what you wanted, we have to use a custom Excel User Defined Function (UDF) that ...
Replace value in a cell if it is within a certain range	1/14/2010
Q: I have a column of numbers in Excel 2007, for example: 19 35 29 62 What i want to ... A: Yes, there is a formula for this. Simply use VLOOKUP(lookup_value, table_array, col_index_num, ...
Searching by Tabs	1/12/2010
Q: I am linking different worksheets together and want my formula to choose which tab to take the data ... A: Yes, it is possible using Excel formula. You will need to use the lookup functions in Excel ...
SUMIF with Multiple Criteria	1/12/2010
Q: I'm not very good at excel and am having trouble creating a SUMIF function with two conditions. ... A: Instead of using SIMIF with multiple criteria, use the SUMPRODUCT function. For example, to sum all ...
Filtering date	1/11/2010
Q: I have basic excel knowledge and have been asked to complete a task that involves me filtering data ... A: You can use Excel AutoFilter to perform the task. Microsoft Office online offers a wonderful video ...
Relative Field	1/11/2010
Q: I'm trying to design a dashboard that shows data from 2 Quarters. The user will choose the surrent ... A: In your screenshot "Jun-10" was selected in the cell but there are no Jun-10 in the spreadsheet. Do ...
lookup after 2 criteria	1/8/2010
Q: I need a formula that will return values after two critera. I think i need a some sort of vlook up. ... A: You can use the IF function to check the values of the header for "a" and then return the text ...
Problem with a Goal Seek Macro	1/7/2010
Q: I'm attempting to use a macro I found online to perform Goal seek for a range of multiple cells. I'm ... A: I have tested the code and you can make it work for rows and columns by changing the vba loop to the ...
PDF to Excel Macro	1/5/2010
Q: Is there a way to pull data from a PDF file and paste into excel as part of VB script? I have a ... A: Yes there are ways to pull data from PDF into Excel using VBA. The simplest solution is to use VBA ...
Print Parameters	1/4/2010
Q: Hey Victor, I have macro below that i'm currently using but cant seem to add parameter that enables ... A: The VBA macro you originally presented did not actually set the orientation. I have corrected the ...
Auto Increment	1/4/2010
Q: I am Using Formula =IF(A1=0,"","Rel-2010/001") showing Results "Rel-2010/001" in a particular cell. ... A: If the formula is in the first row (row 1), you can use the following formula. ...
Excel sorting	1/3/2010
Q: When sorting in Excel, everything sorts with "no header row" except sheet3. I have to manually ... A: When using Excel sort, it automatically recognize whether your data has header. If it does, Excel ...
Spell Check	12/22/2009
Q: Can we force xl to check spelling whenvever we close the workbook. I tried the following by saving ... A: Sure, the code will also work for the BeforeClose Event. (See example below.) ...
Excel VBA and Internet Explorer	12/21/2009
Q: I've never really used VBA to manipulate Internet Explorer before so I'm kinda new with the syntax ... A: To click on an image, include a reference to "Microsoft HTML Object Library" in Excel VBE. The ...
Spell Check	12/21/2009
Q: Can we force xl to check spelling whenvever we close the workbook. I tried the following by saving ... A: Yes, it is possible to have Excel run the BeforeClose event of multiple workbooks, but we will have ...
Conditional formatting	12/19/2009
Q: I had created a Matrix for all my employees in our company with orientation dates in it, now we are ... A: Select the cells where you want to apply conditional formatting. Use the following formula in ...
Solving Travel Saleman Problem in Excel	12/19/2009
Q: I'm stuck in the famous Travel Saleman Problem using Excel.I've 235 locations and a base.The travel ... A: Yes, you may send the file to me. Could you further explain (with example) and what you meant by, ...
Column and row multiplication	12/18/2009
Q: I have data in one column and in one row and want to multiply the first data cell of the column with ... A: We can use an array formla to give the results in a column (B1:B5) for rows. For results in column ...
Excel VBA and Internet Explorer	12/17/2009
Q: I've never really used VBA to manipulate Internet Explorer before so I'm kinda new with the syntax ... A: There are ways to use Excel to manipulate Internet Explorer to fill up fields, click buttons, and ...
Pricelist and Quote	12/17/2009
Q: Its probably been asked/answered already but i cant find it. I made a price list for my work so i ... A: We can certainly add a button "Add to Quote" in your price list and do what you wanted. Could you ...
EXCEL - MACROS	12/16/2009
Q: Sir, I have a format with columns heading Serial No., Name, EC No., Work station no., Extn. No., ... A: From what I understand, you will send emails to 200 team members with an attached excel file. Each ...
One Master spreadsheet to rule them all.	12/16/2009
Q: I have a bit of a tricky question. If you could help me that would be great. I need to create a ... A: It is possible to update the master workbook with the data from each individual workbooks for ...
Copy VBA Userforms	12/15/2009
Q: I am using excel version 2000. I want to create a macro that automatically copies Userforms from my ... A: Yes, it is possible. Include the reference to "Microsoft Visual Basic for Applications ...
excel 2007 help	12/14/2009
Q: I am not able to do it on excel, A B C D E F protein ... A: If you looking to Format Cells, select a range of cells or range of characters in a cell and key ...
How to convert mmm:ss to [h]:mm:ss in excel	12/14/2009
Q: How can I convert 73,611:59 (this is 73,611 minutes and 59 seconds) into 1226:51:59 (1226 hour 51 ... A: Assuming that the figure of 73611:59 (73,611 minutes and 59 seconds) is in Cell A1. The following ...
vba	12/13/2009
Q: in excel 2003 i have seeht that has a data entry rows and related formulas in other cells of same ... A: Yes, this can be done in VBA, but there is a simpler method in Excel. To prevent users from ...
excel solver	12/12/2009
Q: Please help - I don't even know where to start. I do not understand solver and do not know what to ... A: This is a very powerful tool for optimization, such as finding the max or min subject to ...
MS Excel Help	12/12/2009
Q: Question Hello Victor, I would really appreciate your help on the following problem: I need this ... A: I will need more details before I can assist. Is your workflow as described by the steps below? 1. ...
Format to Date	12/11/2009
Q: Can you provide me a macro or function that could identify date format mm/dd/yyyy then convert it to ... A: If you have "dd/mm/yyy" date format in your regional settings, running the function =DAY(E2) (or ...
Find value in cell in different sheet and modify it	12/11/2009
Q: I'm trying to create a macro that searches for a value(variable value)in cell("C4",Sheet2)look for ... A: Insert the code just below the line - Set rLookup = Range("C4") 'value in cellC4, Sheet2 ...
Find value in cell in different sheet and modify it	12/10/2009
Q: I'm trying to create a macro that searches for a value(variable value)in cell("C4",Sheet2)look for ... A: Please find the code below. (It has been tested.) ...
excel	12/9/2009
Q: Is it possible to imbed a video clip in excel and play it without leaving your spreadsheet? A: Yes, it's possible to imbed a video clip in excel and play it without in the spreadsheet. (Note ...
excel vlookup and if functions	12/9/2009
Q: Student name-Exam 1-Exam 2-Quiz 1-Quiz 2-Quiz 3-Project 1-Project 2-Project 3-Project 4-Total Points ... A: Create "Grade Scale" in a range of unused cells in the worksheet. (In my example, I have created it ...
using subtotals in macros - can it count and sum at the same time	12/8/2009
Q: I have a spreadsheet with a large amount of data that is added to each month. the data changes but ... A: Do you have some empty columns in between data? Pivot Table works even with empty cells so it should ...
Print Parameters	12/8/2009
Q: Hey Victor, I have macro below that i'm currently using but cant seem to add parameter that enables ... A: I corrected some lines of the code and tested that it is working in Excel. Please try the code ...
Excel 2007	12/8/2009
Q: I have a master file which shows 4 columns of stock we sent into a customer. Columns show store ... A: Yes it can certainly be done easily using the SUMIF function with a key column on both workbook. ...
Print Parameters	12/7/2009
Q: Hey Victor, I have macro below that i'm currently using but cant seem to add parameter that enables ... A: I have made some addition to you code. Try it and let me know. Sub ShowPrintParameters() ' Keyboard ...
renew column field in pivot table which link to a OLAP cude	12/7/2009
Q: I have a pivot table, which link to OLAP cube, I only need to change column data when I need to use ... A: I have some suggestions below. First, I need to know if you are able to pivot the table manually ...
Summing three columns of different dimensions on aSummary Sheet	12/6/2009
Q: Could you please help me out I want to sum up three columns of different height on a summary sheet ... A: If Reduction_KSI_Prior, Reduction_KSI_2002, Reduction_KSI_2003 are proper named ranges, there should ...
using subtotals in macros - can it count and sum at the same time	12/6/2009
Q: I have a spreadsheet with a large amount of data that is added to each month. the data changes but ... A: Excel Subtotal could not show both count and sum at the same time. Even with a macro, it is not the ...
List Box - Depending on value, copy	12/5/2009
Q: I have a Listbox on sheet "General Information". The list box is called Proposal_Type. The type ... A: Create a ActiveX Listbox named Proposal_Type. Setup the parameters of Listbox to show "GC" (General ...
values to be sum retrieved from if test	12/3/2009
Q: I have 150 Rows and 30 columns. columns contain some specific text. according to the text some ... A: Sure, I am happy to assist. First, ensure that you have a range of cells with the letters a-z and ...
Summing data from months to semester	12/3/2009
Q: The problem I am facing is the following. I am working on a financial model and in one sheet i have ... A: If I understood your question correctly, what you need to do are the following steps: On the 1st ...
a macro in excel	12/2/2009
Q: I would like to use excel as a database and have a form to complete that when a person enters data ... A: Yes, it can certainly be done. However, without knowing more about the data you are using and the ...
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 can assist you in manipulating and filtering data, creating and using formulas, pivot tables, and writing VBA to solve problems and automate processes. I can also research and find a solution for almost anything in Excel for you.

Experience

3 years of professional Excel experience. Currently working for a global Investment Bank.

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

Encyclopedia

Browse Answers: