XL Templates | XL Add-ins | XL Training | XL Estimating | XL Scheduling | XL Recovery | XL Trading | XL Financial | XL Conversion | XL Charting
For all questions relating to Excel and/or Excel VBA only.
How to get FREE EXCEL QUESTIONS/THREADS
37 New Custom Functions For Excel. No spyware or malware. 37 New Custom Functions For Excel
How to use Visual Basic for Applications (VBA) to change UserForms in Excel How to Programmatically Manipulate a UserForm How to Programmatically Create UserForms in Visual Basic for Applications Download Attachment At Bottom For Live Links The Hey, Scripting Guy! Archive: Microsoft Office
GOLDEN RULES Also See Excel Best Practices Never Use Manual Calculation Mode, It's a False Reading Waiting to Happen! If You are Forced to use Manual Calculation, you HAVE a Bad Spreadsheet Design Which Should be Fixed, not Catered to. 1 Worksheet For ALL Related RAW Data. Classic Table Format For Related Data. That is, Row 1 For Headings and Corresponding Data Underneath. Don't Mix Raw Data With Final Results, Reports or Data. No Blank Cells in a Table. No Merged Cells (Use Center Across Selection Instead). Avoid Empty Text ("") For Formula Results, Use Zero Instead. Tools>Options - View - Zero Values to Hide Globally. Or, Custom Format Like: 0;-0; To Hide Cell-By-Cell.
Object Model Map *
Is it possible to have multiple dropdown menus in a single cell that change based on the choice from another dropdown menu? Example In cell A1 I would have a cell with a dropdown menu that has options of Fruit, Veggies, or Grains In cell A2 I would want it to have a dropdown based on what was chosen in A1. If it were Fruit in A1 it would give the options for Pineapple, Watermelon or Tomatoes. If it were Veggies in A1 it would be Brocolli, Peas, or Carrots. If it were Grains in A1 it would have a dropdown choice of Rice, Wheat, or Barley.
I have a col. with cell values of the type "0-7148-3496-3" How can I convert this value to "0714834963" Also, the hyphens may appear at different locations; for eg: "0-944344-49-6" is a valid value!
Hi, I am new to this forum so I am not sure where to post or what to do here. I have a very simple excel sheet which lists doctors names and was wondering if I can simplify my work some how. I have to count the doctors in the list to ensure there are no duplicates and give each of them a value to ensure it always adds up to 23. If a doctor is listed with just his name, he has a value of "1", if he is listed with an "am or a "pm" after his name, he is given a value of "0.5". I then add all the values and make sure it is "23" total and that no doctor is listed twice. is there a way to accomplish this? Ive messed around with drop down boxes, and messed with group boxes, but can't figure out what to do here. can anyone point me anywhere or help me with what to do? I spend so much time on...
Hi everyone, I am trying to automate some tasks for reports that I have made. What I need to do is set conditional formatting based on a value and then later set a formula in a different cell. So far I have got the entire macro to work and apply the formatting plus enter the formula, however on the conditional formatting quotes are being added and I don't want them to because it makes the formatting not work. For example my macro puts in that if a Cell Value > "$E$21" then do some formatting, but i want it to say Cell Value > $E$21. This is my code: Sub Conditional_Format()
Not sure what i should have named the title. Sorry in advance i have a sheet with 3 columbs. A,B,C. Columb A is a number And so is Columb B. I have a userform that enters the numbers to the sheet automaticlly. Here is what i need. Colum A is a minimun quantity and Columb B is an actual quanity on hand. I need a code that will check the userform Boxs for Min And Actual quanitys and prepare a formula for every entry and than place the formula in columb c for every entry. Example 1 Userform Box Min has 2 in it. And Userform Box Actual has 1 in it. I want Columb C to show "Order" But if the Actual is higher than the Min then show "In Stock". But because the differance in the numbers will depend on the 2 entry boxes the code eludes me. I would rather this be done in vba so...
Good Day, I have a macro that extracts text from multiple worksheets and places it in a column along with the cell reference and worksheet name. I'd like to do the same thing but instead of text I'd like it to pull formulas with text only. Some of the formulas have more than one word associated with them. EX: =IF($B34="","Enter Part Number",IF($D34="","Enter OEM",IF($F34="","Enter Quantity",IF($G34="","Enter Published List Unit Price",IF(VLOOKUP($D34,LAN_Range,2,FALSE)="","Enter NE Discount",($G34-(VLOOKUP($D34,LAN_Range,2,FALSE)*$G34))*$F34))))) Here's the current formula:
Hi I'm hoping someone has written a macro that locates the weekend days and grays those sections of the report out. Ideally I would have done holidays as well but that is asking too much! Attached is a spreadsheet that clarifies what I want to do because it is difficult to explain. Thanks
Hi all: Not sure if I'm posting this in the correct thread or not, but here goes. I have an excel spreadsheet that tracks calls made on packages not yet received. K6 through K266 track whether a customer said that the package is still coming (with a "Y"). N6 through N266 track whether or not the package was received. A "Y" is placed in N6 through N266 when the package is received. When a "Y" is placed in N6 through N266, I would like K6 through K266 to be automatically cleared. I'm assuming that this needs to be a Macro, but I can't seem to figure this one out. Any help is greatly appreciated. Limo
I have a budgeting spreadsheet broken down by category (i.e. Equipment, Travel, Contract, etc) and at the end of each category, I have a subtotal of the category and at the bottom, I have a grand total. Example is below What I need help on is a way to automatically insert a row each time the user inserts an entry (in the blank) so I can provide a template for the user in which I can automatically aggregate the subtotals in another part of the workbook. Example Equipment -Entry -Entry BLANK
I have a workbook that was generated as a report of an application, it contains: group name, the group email and the members of the groups. My goal here is to apply a filter, so I can know what users are members of what groups. The only way I can apply the filter is if I manually delete the rows between the new group names and if I manually copy the group names and group emails. Can you please help me with this!!!
I need to be able to pull all of the cell values in spreadsheets in alphabetical order into one spreadsheet. Looks like I cannot do this with Pivot or Consolidate or.....
I have a worksheet using Excel 2002 that will automatically insert a text string from the clipboard that works but the problem is that I need it to insert the text on the line that I have highlighted and it will only do that on the first line and then it wants to re-write the line above it instead of inserting the text on the line where I want it (may be the second or third line down with a blank line in-between). Here is the Activate & Insert code so maybe someone can tell me what is wrong? Thanks, Gene Here is the type of string that will be on the clipboard -- A17; Bill Smith; Mary Smith; Phoenix; AZ; Bigfoot; 21FW; 2003; 8 ; 15; 19; justme@mysite.com ; Joey 'Summary: Returns text from the clipboard as a string. 'Does the clipboard contain format?
I am trying to get a vlookup where I need to match column c from sheet 1 to column a from sheet 2 to return column c from sheet 2 but the problem is that column c on sheet 1 has 4 characters more then column a on sheet 2 is there any way to get vlookup to start the lookup from column a after the first 4 characters ?
I've created a Pivot Chart in which I want to display travel by various folks to mulitple sites. The Site field is a Page field. If I set the Pivot Chart to a Custom -- Floating Bar style it displays fine, but if I change the Page field selection the chart reverts back to a stacked bar style. I've tried setting the default chart type to Floating Bar (this doesn't take), and naming a custom style that is Floating Bar (and setting the pivot chart to this custom style -- but again the style does not hold on a change to the Page field). I ran into this a few years ago and wrote some event macros to reformat the chart when the sheet is activated and when it calculates, but it seems there should be a simpler (built-in) way. Am I missing something obvious? Is there a way to make the...
Hi, I have a strange problem regarding DataValidation dropdown visibility in excel. I have an excel sheet with some data validation dropdowns. The thing is once i update these workbooks, i send them to a client who then re-updates data and sends them back to others. Strange thing is once my client receives the workbook, the validation tab disappears completely. I just can't seem to place where the problem lies. Please help me out. Thanks in advance Aswani Kumar
I need to convert a list into a matrix. The list contains 3 columns where the first two columns would span the dimensions of the matrix and the 3rd column are the values that enter into the cells of the matrix. My problem is that one cell of the matrix can potentially contain more than one entry, i.e. the combinations of 1st and 2nd row entries of the list may not be unique. I have tried to solve it with a pivot table, but I do not get the resulting table to display to contents of the 3rd row of the list, but rather it gives me just the counts. I guess a macro would solve the problem but I don't know how to start. I attach a file for illustration. Thanks a lot for any help on this!
I have a macro that is deleting rows based on certain criteria. The macro is deleting every other row instead of all rows. Below is the code I'm using. I'm thinking a Do While Loop may work but I'm not sure. Any help would be apprecaited. Sub FineSort() Sheets(1).Activate Application.ScreenUpdating = False Range("G2").Select For z = 1 To 10000 If Left(ActiveCell.Offset(0, -4).Value, 2) = "PU" Then
Hi there, Can anyone tell me how i can use the following formula in VBA? I have 4 cells (A, B, C, D). The formula is this: "10-(A-(B+C))=D" I,ve tryed this but is doesn't work: Range("D1").Formula = "=IF((OR(isblank(A1),A1="""")),"""",,(10-(A1-(B1+C1))" Range("D1").AutoFill Destination:=Range("D1:D" & Cells(Rows.Count, 10).End(xlUp).Row)
Hi, I was looking for help with merging duplicate entries into 1 row and deleting the duplicate. I have attached a sample workbook, Column C is the duplicate and I need the data in the remaining columns to be listed on just 1 line and delete the additional line below. To explain an example is the first 2 lines in the attached. I need First, Last and Working data on the 2nd line moved up to the 1st line and the 2nd line deleted. Thank you!!!!
Hey all, I'm new here and just starting to explore the joys of Excel. I'm trying to learn a little bit about how to automate my monotonous copy-paste data entry job. I'm working with a workbook full of several dozen worksheets, which each have a table full of elements. Unfortunately, the order of elements for each sheet is not consistent throughout the workbook. So, I'm trying to find a way to Find a particular term (in column A), and copy/paste the corresponding time (in column B) to a summary list in is separate sheet. Is the Find function even the right way to go about this, or would VLookup be more advantageous? This is what I crudely threw together, after a bit of searching old posts and trying to modify it to my own needs. However, in running it, I get a runtime error. ...
There are currently 567 users browsing this forum. (9 members & 558 guests)
Use this control to limit the display of threads to those newer than the specified time frame.
Allows you to choose the data by which the thread list will be sorted.
Order threads in...
Note: when sorting by date, 'descending order' will show the newest results first.
Forum Rules
