blp, hi thanks, shift key: To byapss and/or prevent macros from running when you open a workbook that has code in it, hold down the SHIFT key when you open it. If code opens a workbook and that workbook being opened has workbook and/or worksheet event drive code then use Application.EnableEvents...

Excel: VBA Code to Remove Excel File, current date, xls

current date, xls: It can t be done the way you are thinking it can be done. Code in Workbook1.xls can not delete Workbook1.xls. You will need another workbook to open that will close the workbook to be deleted and the code in that workbook will have to delete the workbook...

Excel: VBA Code - Security Settings, excel security, code security

excel security, code security, security settings: Heather, It is impossible to change the security settings in Excel with VBA code. Imagine the trouble it would cause if one could do that. The only thing that I have found that addresses this problem is to design a workbook so that all of your sheets...

Excel: VBA Code - select from dropdown and activate, vba code, combobox

vba code, combobox, picklist: You could still use the Forms based one (see View-Toolbars-Forms). It has really nothing to do with Forms, just there for compatibility purposes. However, in your case, right click on the ComboBox, View Code and copy the code what you need (I assumed combobox1...

Excel: VBA Code Using CountA Yielding Wrong Answer, a10, wrong answer

a10, wrong answer, correct answer: Ken, I m on vacation now and no way of running excel... but I think if you store A10 as a formula instead of a value it would work... Try switching your last line of code with this: Range( June!A10 ).Formula = rcount You may need to define rcount...

Excel: VBA Code, vba code, address link

vba code, address link, internet explorer: Sub Macro1() Nav_Link http://www.XL-Logic.com End Sub Sub Nav_Link(Link As String) On Error GoTo ErrHand ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True Exit Sub ErrHand: Err.Clear MsgBox Cannot Open: & Link...

Excel: VBA Combo or List box, wayne langlois, matthew dunne

wayne langlois, matthew dunne, chris renaud: Easiest way is to set up code to build the menu when excel is loaded and remove it when Excel is closed. You could put this code and the onaction code in a workbook that you make into an addin. then have your users select the addin. Hard to say what is...

Excel: VBA Combobox

Hi Manny, If your lists are all assigned Excel defined names, and you load those names into the first combobox, you can add this change event to Combobox1 Private Sub ComboBox1_Change() ComboBox2.List = _ Application.Transpose(Range(ComboBox1.Value))...

Excel: VBA Command Button Macro Editing, relative coordinates, cell location

relative coordinates, cell location, arrow keys: 1 - each checkbox should have its own linked cell so the commandbutton can simply check these cells for True/False and take the appropriate action if True. Easiest if the value is in a hidden row from the user, and in the same column as the checkbox represents....

Excel: VBA Compile Error, default margin, exact path

default margin, exact path, windows 2000 professional: You RARELY ever have to select a cell to do something with it or to. Look at the code and see what is repeated that can be removed. For example: Range( ac32 ).Select Selection.Copy Notice Select/Selection twice? It s redundant, but not wrong. ...

Excel: VBA - Compound interest & take % from reinvested amount, tax on earnings, interestrate

tax on earnings, interestrate, compounding interest: Hi Ryan How are you? I think that the following, with some currency formats would do: 1. Type 10000 in A3 (principal) 100 in B3 (payment per period) 9% in C3 12 in D3 (number of periods per year ... eg. 12 months) 4 in E3 (number of years) 15%...

Excel: VBA Conditional Formatting in Excel, charter boat company, conditional formatting in excel

charter boat company, conditional formatting in excel, blank rows: Hi Ben, Without knowing what the conditions are that you want to base the heading row color on I can t be absolutely sure, but because you mention needing more than three it is likely that it can t be done with conditional formatting. Usually in such...

Excel: VBA Copy & Paste Portion of Merged Range, row headings, column headings

row headings, column headings, cell b1: You can t have it both ways -- If unmerging is not an option, and you want to copy B1:B3 where B1 is part of A1:C1, you re going to have to do it the way you ve already done (but is slow) or bring in the values as I mentioned. However, the procedure you re...

Excel: VBA Copying, index formula, index lookup

index formula, index lookup, reference number: Simon, here s some example code that should get you going: Sub RelateFill() code created by Jeffrey W. Kamps Jeff is a private consultant specializing in data entry and analysis utilizing VBA code. He can be contacted at kamps.jeffrey@gmail.com ...

Excel: VBA Create Range Names Automatically, range names, sht

range names, sht, time chris: Here s an example, naming the range A1:B2 on each sheet. Sub nameRanges() Dim sht As Worksheet For Each sht In ThisWorkbook.Worksheets ActiveWorkbook.Names.Add Name:=sht.Name, _ RefersTo:=sht.Range( a1:b2 ) Next sht...

Excel: VBA cascading userfrom initilise, error 400, swallow

error 400, swallow, calculator: David, I have had a quick look, and whilst I see some things I am not absolutely sure what the problem is, the app is too big and too complex to assimilate in one swallow. Can you give me a step-by step account of what I need to do to reproduce the problem,...

Excel: VBA with changing data size, sumproduct, range selection

sumproduct, range selection, data changes: Hi Lily The following macro can be called from a button. It assumes all your data is in columns A B & C It produces a sum X Y table for each unique team in columns f & g The macro will refresh the results table each time it is run. It will...

Excel: VBA code for auto date in excel, excel vba code, target address

excel vba code, target address, quetion: Hi Vic How are you? I have not checked your quetion entirely, but thois could be a good start Private Sub Worksheet_Change(ByVal Target As Range) Dim S As Range Set S = Sheets(1).Range( h2:H12 ) If Union(S, Target).Address = S.Address Then Target.Offset(0,...

Excel: VBA code for automation with pivot - table, flight analyst, pivot table

flight analyst, pivot table, code tools: Rajiv, What you now describe you want was not what you originally described you wanted. Unfortunately, I responded to what you originally described. If you found that offensive or a waste of your time, my apologies, but I can only respond to what you stated....

Excel: VBA code for drop down box to input into cells on 3 different worksheets., drop down boxes, vbe

drop down boxes, vbe, spreadsheets: Shaun If you have the form, and it functions, then the code must be present. Maybe it has a password on it... Are you just wanting to update the code??? If you open the userform in the VBE (with the file open, press Alt F11 to open the VBE), you can...

Excel: VBA code problem, keyboard shortcut, macro code

keyboard shortcut, macro code, random key: Sub MACC() Dim i As Integer, counter As Integer counter = 0 For i = 1 To 10 Range( F5:G16 ).Sort Key1:=Range( F5 ), Order1:=xlAscending, _ Key2:=Range( G5 ), Order2:=xlAscending, Header:=xlGuess, _ OrderCustom:=1,...

Excel: VBA coding giving combinations, 5 steps, asset classes

5 steps, asset classes, hedge funds: This code should do what you need: Sub GenerateCombinations100() Dim i, j, k, l, m, n As Integer Dim z As Long z = 1 For i = 0 To 100 Step 10 For j = 0 To 100 Step 5 For k = 0 To 100 Step 5 ...

Excel: VBA coding giving possible asset allocations, asset allocations, 5 steps

asset allocations, 5 steps, asset classes: I m not sure, but I m sorry but I really haven t time to write code that could possibly do this Thank you for your comments, how much time for you spend giving free advise? I answer dozens of questions per day for free. just because your question is too...

Excel: VBA command line to go to row # in a cell, cell 77, sheet1

cell 77, sheet1, hello bill: Hello Bill Assuming a cell had a defined name of CustomerCount ex: Insert= Name= Define Name: CustomerCount RefersTo: =sheet1!$B$9 and cell B9 in Sheet1 contains the number 77 then set sh = Worksheets( Sheet1 ) sh.Activate...

Excel: VBA: copy/paste cell offset formula, quadratic formula, interpolation

quadratic formula, interpolation, wht: Instead of ActiveCell.Offset(((7 * k - (k * k * 0.5) - 7 + j - k) * 119), 0).Select Wht not: Range( B65536 ).end(xlup).offset(1).select which will select the next free cell in column B -- which I thought is what you want. I don t feel like deciphering...

Excel: VBA cut and paste event handling, target value, destination cell

target value, destination cell, cell entries: Brad, I am not getting that behavior. I get two Change Events firing. the first is the cut cell(s), the second is the paste to cells. The are both fired at the paste. Without knowing what you are looking for, I would validate the value of the cell referenced...

Excel: VBA Data Import, bob johns, distinct blocks

bob johns, distinct blocks, consistent group: Bob, I took the time to make this pretty complete for you. You need to check the comments within the code and edit as described. You need to place the code below in a module before proceeding. The code was developed in Excel 2003. If you have an earlier...

Excel: VBA Delete row based on duplicate cells, excel 2002, google

excel 2002, google, h1: You don t need a macro per se. Write a IF formula that compares the two values in that row and, for example, put a 1 if it does match and put a 2 if it does not match. Sort by that column and delete the group of rows manually according to the 1 s or the...

Excel: VBA DropDown or ListBox Pre-made, data validation, sheet names

data validation, sheet names, cell a1: This can only be done with vba coding. On Sheet1 worksheet, give cell A1 the range name rngSheets . In cell B1, create a Data Validation List with the source =rngSheets . In the VB Editor, add this code to the Sheet1 worksheet object: Private Sub...

Excel: VBA Equivalents of Excel Formulas, formulas in excel, excel formulas

formulas in excel, excel formulas, vba code: Ken That question produces all kinds of discussions. VBA has a lot of the built in functions of Excel available to use within the code. However, for starters, Offset is not one of them, neither is If. You would have to use a bunch of code to get to the...

Excel: VBA & Excel, widgets, cells

widgets, cells, goto: Alma, Couple of things wrong with your code. First is you have the error routines in the middle of your code. They go at the bottom before End Sub and after a self inserted Exit Sub . If you put them in the middle of the code, they will execute whether...

Excel: VBA in Excel, customer id, pastes

customer id, pastes, infinite loop: You need to make your destination be a DIFFERENT cell each time it s used. I recommend that you create a function separate from this subroutine, like this: Function pasteInCell() As Range Set pasteInCell = Sheets( sheet2 ).Range( a1 ) Do While...

Excel: VBA & Excel, office xp, c6

office xp, c6, ot: It seems to me, if I understand what you need, that your else needs to be only one of many different cells from different sheets. Therefore, I think you should loop thru all of the sheets to make that determination and then use that as your else . You...

Excel: VBA & Excel, o3, c3

o3, c3, hierarchy: Alma, When I said upwards, I meant left in the hierarchy of sheets. Here s some more discussion to clarify this... Let s say you have 4 sheets, just for the sake of doing this. They are named sheet1 to sheet4, and they are in the same order. The activesheet...

Excel: VBA and Excel, web query, stock quotes

web query, stock quotes, simple stuff: John, Unfortunately there isn t a way to have a conversation, so when you post it is posted as a question. I then have to respond to it or I get a please respond message after a while. I do have a choice of a response of no question here , but I don...

Excel: VBA in Excel, dear tom, ogilvy

dear tom, ogilvy, message box: Hello James, You need to maintain a record/flag that the message has been shown. Private Sub Worksheet_Change (ByVal Target As Range) Static bMsgShown as Boolean **************** If Sheets( MOM ).Cells(42,4).Value = 5000000 Then if bMsgShown = False...

Excel: VBA for Excel drop down menu worksheet, target range, strr

target range, strr, drop down menus: Not trivial, but this works. Key range is A1:G7. Colors I used are: RED,BLUE,GREEN,CYAN,ORANGE,YELLOW,BLACK Here s the code -- right-click the sheet tab, and select View Code, then paste this in: Private Sub Worksheet_Change(ByVal Target As Range) ...

Excel: VBA or Excel formula routin for text checking, excel vba code, excel formula

excel vba code, excel formula, routin: Hi, you can use the InStr VBA function as shown in the code below. I allow 3 (minimum 1 search word): Call it in a cell as: =yourfunction(A1, none , no , error ) Function YourFunction(FindIn, what1, Optional what2, Optional what3) On Error Resume...

Excel: VBA Excel help, conditional formatting, abc format

conditional formatting, abc format, cell a2: [Revised May 18: in option 1) below, the formula in the Conditional Formatting process should be an absolute reference, $A$2 instead of A2] 1) Use Conditional Formatting to make row 2 invisible (white font) when cell A2 is text abc : Select Row 2. From...

Excel: VBA Excel Macro, hyperlink anchor, hyperlinks

hyperlink anchor, hyperlinks: Hi Mike, I don t understand your question. Could you provide more details? You can send me an example if you like (this would probably help the most, but would break the hyperlinks if you are linking to something outside your workbook). My email address...

Excel: VBA for Excel problem, excel macros, b cells

excel macros, b cells, proper syntax: Hi Rolf, How are you? The logic could not be entirely correct as I did not pay proper attention but the statements and the syntax to invoke the function are as following: Option Base 1 Public Function kalk(x As Range) input x will always be a number...

Excel: VBA in Excel - Relative Ranges, macro in excel, cell position

macro in excel, cell position, zero rows: For example, see below for code that copies the active cell to the cell one column to its right. The code activecell.offset(0,1) refers to the cell that s zero rows down and one column to the right of the active cell. Sub CopyToRight() ActiveCell.Copy...

Excel: VBA & Excel, expert listings, excel expert

expert listings, excel expert, easy job: Hello, Alma, I guess you didn t notice where I say that I m not familiar with VBA. (I mention it both in my expert listing and on the question page.) I am a programmer, but I use the old macro language that came out with an earlier version of Excel. ...

Excel: VBA email/attachments/etc.., email attachments, outlook application

email attachments, outlook application, fileformat: Hi Mike I am guessing that it is not necessary for persons #1 to #4 to actually retain a copy of the incomplete workbook. If that is so, there is no need to save at all. When the workbook arrives by e-mail they open it in Outlook, fill in their fields and...

Excel: VBA error, followup questions, quotation marks

followup questions, quotation marks, seperator: 1. If your goal is to get the formula =SUM(Sht1:Sht2!D9) into cell D9, then I d do it with one line of code: Sheets( PROGRAM ).Range( D9 ).FormulaR1C1 = _ =SUM(Sht1:Sht2!RC) 2. There s no need to loop in order to get the formula into a range...

Excel: VBA and excel, intermediate code, excel 2002

intermediate code, excel 2002, vba: Sharon, Here s what I came up with. xf Sub RememberFilterSettings() XeonFlux March 2005 Dim w As Worksheet Dim filterArray() Set w = Worksheets( mySheet ) change the name of the sheet as appropriate Set x = Range( a1 ) change to any cell...

Excel: VBA / Find & copy Area, asset types, asset type

asset types, asset type, column c: Hi Ulf, How are you? What do you think of the following Sub ggg() Dim A As Range, d As Range Set d = Sheets(1).Range( E1 ) destination range Set A = Sheets(1).Range( a1:A100 ) A.Activate c = 1: i = 1 Do While i = A.Rows.Count If...

Excel: VBA / Find & copy Area, dd mm yy, false application

dd mm yy, false application, box message: PART 1 You realize in your input box message you ask the user to use the format DD-MM-YY and then you set the cell numberformat to mm/dd/yy . But then again, it doesn t really matter what you specify as the number format since your code just plugs a...

Excel: VBA with FORECAST func., empty cells, indirect address

empty cells, indirect address, forecast values: hi Jaka, Try this Option Explicit Public Sub forecast_values() Dim Bcell As Range For Each Bcell In Range( I1:I391 ) If IsEmpty(Bcell) Then Bcell.Formula = =FORECAST(INDIRECT(ADDRESS( & Bcell.Row & , & (Bcell.Column - 1) & )),D1:D22,A1:A22)...

Excel: VBA Forms, word wrap, control buttons

word wrap, control buttons, excel 2000: Hi Katherine, I apologize for being slow to respond to your question. I am traveling at the moment, but managed to get access to a computer, and finally got a chance to try out your code. I assume that your form is a Userform with a really big textbox...

Excel: VBA - Future Value Problems, e mail address, interestrate

e mail address, interestrate, future value: Hello Ryan, Yes, you can email a copy to me. My e-mail address is VBAexpert -AT- myway.com. I did run your code for a few cases, and I m not sure what the problem is that you are referring to. I did not get an overflow. Do you have a test case for which...

Excel: VBA to find a value in a list, cell a2, rng

cell a2, rng, ogilvy: Assume the passwords are in column A, beginning in cell A2 and the names are in column B. Dim ans as String, res as Variant Dim rng as Range, rng1 as Range ans = InputBox( Please enter password ) With Worksheets( Roster ) set rng = .Range(.Cells(2,...

Excel: VBA form (Offset ), c cells, cancle

c cells, cancle, command buttons: Ola Antonio How are you? Activecell.offset(2,1) means 2 rows down and one col right Activecell.offset(-2,-1) means 2 rows up and one col left Activecell.offset(0,-1) means same row and one col left Please have a look at Private Sub cmdok_Click()...

Excel: VBA to Generate All Sets of Combinations of a Number (2-6) After a Filter of 10000 Stocks, interger, problem thanks

interger, problem thanks, combinations: the number of combinations of 60 items taken 2 through 6 at a time produce: 1770 2 at a time 34220 3 at a time 487635 4 at a time 5461512 5 at a time 50063860 6 at a time for 50063860 sets of 6 numbers, if 6 columns are used, then...

Excel: VBA Guru needed, info ie, memory problem

info ie, memory problem, calculation time: Hi Nick, This is something I can most likely help with, but obviously will need details in order to fully understand your problem. Probably the best approach would be to email me a sample of your workbook. My email address is damon.d.ostrander@lmco.com....

Excel: VBA Guru needed, info ie, pivot table

info ie, pivot table, guru: Before I get bogged down in complicated ways of doing it, shall we try a simple one first - try using a pivot table (from the data menu) - I THINK this will produce exactly what you are after - vba can be used to refresh the chart, but otherwise it will give...

Excel: VBA Help, label displays, text boxes

label displays, text boxes, elseif: Look at the example in my previous answer. If you wanted to add to it the codition that if age is 46-55 & amount is 0-750000, then comment is Msg 4, then build on the previous answer link this: If (age = 46) and (age = 55) and (amount = 0) and _ ...

Excel: VBA Help On Adding An Empty Workbook, communication errors, ken wilson

communication errors, ken wilson, saveas: Thanks for the explanation. as a rule of thumb, always best to test code on a copy of your data and before you tell someone their code doesn t work, better to test it and tell them why it doesn t do what you asked. You have a good weekend as well....

Excel: VBA Help to Create List of Excel Filenames, personal computer tutor, left directory

personal computer tutor, left directory, rw 1: Ken, I wrote an article a couple of years ago that dealt with a similar situation. You can read it at http://personal-computer-tutor.com/abc4/v33/chad33.htm Your list is simpler than the one I wrote about and the following code will accomplish what...

Excel: VBA Help With CVS Creation, text import wizard, csv file

text import wizard, csv file, fileformat: How are you importing the data from the CSV file or opening it? When you open it directly, and the text import wizard should kick in when this happens, then a new workbook is created. When this happens then the code to save just that new workbook is: ...

Excel: VBA Help for complex copy & paste macro, bottom 12, copy paste

bottom 12, copy paste, cells: Sub insertRows() Dim thisSheet As Worksheet Dim bottomCell As Range, rangeToCopy As Range Set thisSheet = ActiveSheet find the text product list info in column B Set bottomCell = thisSheet.Range( B:B ).Find( _ What:= product...

Excel: VBA Help To Locate Specific Row, range f1, proper criteria

range f1, proper criteria, search range: Ken, This should point the way. xf Sub FindMe() x = 0 For Each cell In Range( f1:f3000 ) If cell = June Then If cell.Offset(0, 1) = 25 Then x = 1 MsgBox Row & cell.Row & contains a match or ...

Excel: VBA Help on TextBox Issues, textboxes, position x

textboxes, position x, shapes: You can prevent the user from deleting the text box by protecting both the textbox AND the worksheet - the protection of the textbox should be Locked, but to allow the user to enter data into it, its lockedtext should be false. This code will also prevent...

Excel: VBA Help, demo code, inputbox

demo code, inputbox, input box: Hi James Try this code out and let me know how it goes. Place it in a button or module. Rem OBTAIN A NEW VALUE FOR C1 Dim val1 Do Until val1 val1 = InputBox( NEW VALUE PLEASE ) Loop Range(...

Excel: VBA Hide Sheets

Hi, try: Sub HideThem() For Each sh In ThisWorkbook.Worksheets If Not sh.Name = TOC Then sh.Visible = False Next sh End Sub Sub UnHideThem() For Each sh In ThisWorkbook.Worksheets If Not sh.Name = TOC Then...

Excel: VBA help, b100, e6

b100, e6, a1: ...copy the entire row... -- row 6 from sheet1 or where it matches in sheet 2? ...paste special value in sheet3 a1... this will repeatedly replace the contents of row 1 -- what purpose does THAT serve? ...until there is no value in sheet1 col e.....

Excel: VBA help please, admission time, expert community

admission time, expert community, nursing station: Shared workbooks are a bad idea - Microsoft thought they were great, but the general feeling in the expert community is that they will spell disaster very quickly. The job you are doing doesn t sound like an Excel sheet - an Access database would be much...

Excel: VBA help please, admission time, mr marshall

admission time, mr marshall, combobox control: Hi Paul Open VBA (Alt F11), double-click the second worksheet (under Microsoft Excel Objects in the Project window) and copy/paste the following into the codespace window: Private Sub Worksheet_Change(ByVal Target As Range) RoomNumberColumn = 2...

Excel: VBA Insert formula instead of value, brute force solution, algebraic expressions

brute force solution, algebraic expressions, cell d1: Greta, I don t know about the most elegant solution, but another option is to add a counter. Before the While..Wend block add: i = 1 Then inside the While..Wend block use i in the formula: Range( D1 ).Formula = =A & i & *B & i & +C & i i = i +...

Excel: VBA to identify cell location, array function, row move

array function, row move, cell location: Michael, In my own humble opinion g , I feel VBA is one of my strong points. for the first problem, in a general module (insert= Module), put in code like this Sub Macro1() Dim rng as Range, rng1 as Range Dim s as Variant s = Worksheets( Control...

Excel: VBA im an idiot, value application, macro names

value application, macro names, data exchange: Sub Macro2() ========you should not use macro names like Macro2 Also, I ve eliminated some unnecessary activating of files and selecting of cells. Application.ScreenUpdating = False Range( B1 ).Copy Workbooks.Open Filename:= C:SR1Month1Data...

Excel: VBA LOOP on Cells, cell d1, delphi code

cell d1, delphi code, cell a1: Brian, There are several ways to do it in VB, but this is the most efficient in my opinion. Sub Brian() For Each cell In Range( A1:D1 ) cell.Value = 1 Next cell End Sub or even simpler Sub Brian2() Range( A1:D1 ).Value = 1 End...

Excel: VBA for lookup & replacement in a table, spreadsheet formulas, generic macro

spreadsheet formulas, generic macro, vlookup: Sorry, I can t help because I do not write macros for people on this site who either ask me to do so, seem to expect me to do so, and/or need one. 99% of the time they are more involved than you first think and/or tell me that it needs to do. And since...

Excel: VBA MACRO HELP, invoice numbers, vba macro

invoice numbers, vba macro, fileformat: Without seeing the workbook then I can t tell why, if I understand correctly the problem you are having, the new workbook is empty. I would suggest copying the whole sheet the invoice is on to a new workbook instead of copying the ranges. You can record...

Excel: VBA MAcro Based Upon Cell Click, cell a1, vba macro

cell a1, vba macro, ken ken: Ken, Generally speaking, you can write code that will monitor a cell or range of cells for changes and fire a macro if certain conditions are met, but I do not know of a way to fire a macro simply by the user selecting the cell by way of a single click...

Excel: VBA - Macro, false selection, stack space

false selection, stack space, copy windows: sorry I misread the numbers, this is from the VBA help Out of stack space (Error 28) The stack is a working area of memory that grows and shrinks dynamically with the demands of your executing program. This error has the following causes and solutions:...

Excel: VBA Macro, passing judgement, copy windows

passing judgement, copy windows, vba macro: Your question was, and I quote ...Is there a way to make it so that the new data comes in the row of cells below the old data?... And the answer to that question is Yes. Before you start calling people names and passing judgement perhaps you should learn...

Excel: VBA Macro, test xls, copy windows

test xls, copy windows, project test: Hi Aashish, There are a couple of issues with the code a. The input and output files are not clearly defined. b. the variables CP etc. that you are defining are not needed as they are values in a cell that you are evaluating c. Column function is not...

Excel: VBA/Macro code, blank row, vba macro

blank row, vba macro, macro code: Hello Ray, This does what I understand you to say you want. Sub AddRows() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = lastrow To 1 Step -1 If Cells(i + 1, 1).Value Cells(i, 1).Value Then Rows(i...

Excel: VBA Macro Help. Moving specific data from Sheet1 to Sheet2, sheet1, vba macro

sheet1, vba macro, sorry cant: Ajay, I normally like to avoid Macros where possible. What you need can be done using formulae itself In Sheet2 - A2 - set the formula: =OFFSET(Sheet1!$A$1, 4*(ROW()-1)-1-1,0,1,1) In Sheet2 - B2 - set the formula: =OFFSET(Sheet1!$A$1, 4*(ROW()-1)-1+COLUMN()-2-1,1,1,1)...

Excel: VBA Macro interacting with IE password Prompt, keyboard macros, record macros

keyboard macros, record macros, connection password: I m fairly certain that you can t do what you want. The password protection is put there to prevent access automically and therefore I think you will have to be prompted to type the requested data. You can get programs that record macros from keyboard...

Excel: VBA Macro - Need help with Ranges...., outlook application, vba macro

outlook application, vba macro, asset value: You need a loop to cycle on the rows, one way of doing it is to pass a parameter on the macro you have, and call it from a different macro, changing that parameter. Something like: Sub ExportDates() For i = 1 To 92 outaddcal (i) Next ...

Excel: VBA/Macro program to insert blank rows, blank row, blank rows

blank row, blank rows, vba macro: No need to apologize and you did not offend me at all. I was just stating the facts and trying to give you an understanding of what I can do and am willing to do. For future reference, the more info you can provide the better I, or another volunteer on this...

Excel: VBA/Macro programming, cell b2, blank cells

cell b2, blank cells, code snippet: Hi Dan, There are several ways to accomplish this. The easiest and most reliable uses the Range object s End method to find the last cell containing data in a column (as in this example) or row. Here is a simple example code snippet for copy/paste from...

Excel: VBA Macro, vba macro, variables

vba macro, variables: The question doesn t contain enough information to provide an answer - but a macro IS vba, so if it is creating a file, it must know WHAT is being created to act on it - macros can open files in Excel, read information into variables etc - so the basic answer...

Excel: VBA to Mark a cell then return to it., macro recorder, spreadsheet

macro recorder, spreadsheet, thanks in advance: Hi Max How are you? What about the following Sub WhereIWas() Dim a As Range, b As Range addr = ActiveCell.Address Set a = Sheets(1).Range(addr) a.FormulaR1C1 = _unique_string_xzy987_ go and do something elsewhere .... ...then find and go...

Excel: VBA Menu handling, worksheet menu bar, annoying problem

worksheet menu bar, annoying problem, menu bars: You can count the number of menu items in File menu using: msgbox commandbars( Worksheet Menu Bar ).Controls(1).count To see the name of the file menu msgbox commandbars( Worksheet Menu Bar ).Controls(1).Caption To see the name of the file- New...

Excel: VBA Minimise Worksheet, Maintain Form, code option, mini application

code option, mini application, alternative solutions: Try this to hide the application and display the UserForm [code] Option Explicit Sub ShowForm() Application.Visible = False UserForm1.Show End Sub[/code] make dure you put this code into the UserForm Query_Close Option Explicit Private Sub...

Excel: VBA: Move to Next Blank Row?, test xls, blank row

test xls, blank row, active selection: Example: the code below copies the active selection, and pastes it to sheet CP, and it ll paste to the FIRST BLANK CELL at location A3 OR BELOW. Sub test() Dim wksPasteTo As Worksheet Dim rngPasteTo As Range Selection.Copy Set wksPasteTo...

Excel: VBA macro, hyperlink anchor, sans serif

hyperlink anchor, sans serif, vba macro: Hi Mike, How are you? Please see below Sub aatestInsert_Hyperlinks() Dim CurrentPositionOfCursor As Range Dim n As Byte, a As Range, SubAddressReference As String Dim Selection As Range Set a = ActiveCell For n = 1 To 100 Set CurrentPositionOfCursor...

Excel: VBA macro to average, vba macro, pivot table

vba macro, pivot table, ogilvy: Hello Randy, Actually, this could be done without VBA and in a very short time period using either a pivot table or formulas. If you want to send me the workbook or a sample workbook, I can set it up for you. Don t make me guess what you want done - spell...

Excel: VBA macro control, square brackets, vba project

square brackets, vba project, vba macro: Try this: First, I believe you have to set up a reference to the workbook that contains the subroutine that you want to call to make this work (references are set up with the Tools References command). Then, precede the macro name with the workbook...

Excel: VBA macro to format data, false selection, xlsin

false selection, xlsin, vba macro: Jason, I normally like to set the macro as below as it makes it easy to follow and debug Sub Macro1() Dim xlsIn As Excel.Worksheet Dim xlsOut As Excel.Worksheet Dim i As Long Dim j As Long Set xlsIn = Excel.ActiveSheet Set xlsOut = Excel.Worksheets.Add...

Excel: VBA macro functions in Excel, vba macros, functions in excel

vba macros, functions in excel, macro functions: First of all, I have no idea who Adelaide is or even what she has to do with your question. Secondly, what exactly is your question? Saying ...something funny is going on... is of no help. That is like taking your car to the mechnaic and saying something...

Excel: VBA macro help - Ranges?, outlook application, vba macro

outlook application, vba macro, fixt: You want to make 92 entries? Sub outaddcal() Dim objOL As Object Dim objItem As Object Set objOL = CreateObject( Outlook.Application ) for i = 1 to 92 Set objItem = objOL.CreateItem(olAppointmentItem) usedate...

Excel: VBA macro (to supercede the previous email), vba macro, question thanks

vba macro, question thanks, mail: You would just have more criteria than just the times. In your criteria, you would count all of the items in the list where the time is greater than or equal to an 8:00 hour and less than or equal to the 9:00 clock hour AND the date is equal to a certain...

Excel: VBA macro, vba macro, xls file

vba macro, xls file, vba code: Hi Joe How are you? It depends on the file that is generated by the macro. You can for instance generate the VBA code for a module of a .XLS file. In that case, your macro creates the vba program and another macro just run it. Have also a look at Shell....

Excel: VBA macros and shared workbooks, microsoft excel help, run time error

microsoft excel help, run time error, excel workbook: Hi Stephen, I don t see your code but I assume that you are trying to do some merging/splitting of cells in the shared workbook. Unfortunately, that cannot be done. Go Microsoft Excel- Help - Limitations of shared workbooks Merge cells or split merged...

Excel: VBA (NT versus Windows 2000), run time error, subscript out of range

run time error, subscript out of range, excel macro: Hi Brent, There are numerous possible causes for such a problem. One possibility is that the code is reliant on Windows API calls or on file paths to system files being the same on both systems--which is not necessarily so. But I would have to see the code...

Excel: VBA PasteLink, run time error, run time error 1004

run time error, run time error 1004, test xls: Again, what version of EXCEL are you using? and this line looks odd Variable.Worksheets(1).Range( range ).Copy unless Variable refers to a valid and open workbook Followup: If you had used the recorder as I had asked.suggested you would see that...

Excel: VBA- Pivotitems, pivottables, target

pivottables, target, time error: With ActiveSheet.PivotTables( PivotTable2 ).PivotFields( param ) For i = 1 To .PivotItems.Count If UCase(.PivotItems(i).Name) = Ucase( specific name ) Then .PivotItems(i).Visible = False End If Next i End With I don t know why you are introducing...

Excel: VBA Problem, string test, multiple users

string test, multiple users, object model: Robin, that is a bit odd. I played with this a little bit to see what it was doing, and I can make it produce errors either way - with or without the file extension. I never got an error when both of the files were provided with the file extension. I m...

Excel: VBA Programming, microsoft excel 2002, excel 2002 power programming with vba

microsoft excel 2002, excel 2002 power programming with vba, reed jacobson: Todd, Here s a routine that will work. For simplicity, it doesn t create a different file, but rather a different sheet in the same workbook / file. You can move or copy the sheet to a separate file after the macro is done if you like. Assumptions...

Excel: VBA to Pull Chart Data Together, sheet1, w49

sheet1, w49, vba code: Ken I still need to assume that the first range is the x axis value. Then, you can use this code to get the chart: Sub Ken() Dim m As Range Dim n As Range Dim lowlmt As Range Dim uplmt As Range Set m = Range( Jan!G4:G45 ) Set n = Range( Jan!M4:M45...

Excel: VBA passing arrays between functions, irow, arrays

irow, arrays, b5: Here s an example that might help. The code below creates a 2x2 array from the values in the range A1:B2. It calls this origArray; it then creates newArray as a 2x2 array with each value being one plus the corresponding value in the origArray. It then uses...

Excel: VBA & plotting, generic macro, plot point

generic macro, plot point, checkmark: I understand what you want to do, but statements like (Due to baseline graph, the counter and series wont align 1 to 1 with series) and what MyCounter is are probably meaningful to you, but not to me. You can send me a sample workbook and I will...

Excel: VBA plotting, column address, line location

column address, line location, excel sheet: Here s a sample of vba code that creates a chart: Sub ChartOnNewPage() Dim newChart As Chart Set newChart = Charts.Add With newChart .ChartType = xlColumnClustered .SetSourceData Source:=Sheets( Sheet1 ).Range( B3:D5 ), PlotBy:=xlRows...

Excel: VBA to populate and print report, pay advice, hello tom

pay advice, hello tom, employee names: Hello Joe, Without specifics, the best can do is define the variables and let you set the locations sh: Sheet where C6 and G6 are located Sheet1 advice: range reference to the area you want to print sheet2!A1:M20 EmpList: range...

Excel: VBA or is it possible to do with a formula, data names, follow ups

data names, follow ups, column headers: Joshua, yep, that makes more sense. It s just a matter of changing the main IF statement, to turn around what it does with the data it finds. Anyway, here s the new code: Sub checkNew() Range( A:A ).Select For Each cell In Selection current =...

Excel: VBA probability problem, randomization test, probability problem

randomization test, probability problem, keyboard shortcut: Soorry for the last com, I pressed the wrong button !! Questions re cell H20 1) IS it set to at the start of the routine 2) During operation of the routine is set to either 1 or of does it accumulate a series of 1 s in the same cell ? If...

Excel: VBA problem, case password, password1

case password, password1, inputbox: Todd, If you remove the following line the procedure should work for you: Case password , password1 , uResponse Some programming languages require that type of a line defining the Cases, but Visual Basic doesn t need it. In fact it treats it as an...

Excel: VBA problem, first error, msgbox

first error, msgbox, hhh: Hi Robin How are you? The second error you can easily overcome by using always the fullname test.xls. It may result from another object (created by the user) named test that does not exist in all the Excel applications. The first error has to to with...

Excel: how does VBA process formulas?, search definitions, vba module

search definitions, vba module, reference column: You can make the code the other way around, that is, changing the values in the same range of the worksheet using code. This way you can build loops and change the value as you need. About the code, it will help if you post the part you are having issues...

Excel: VBA programming, cell c2, answer sheets

cell c2, answer sheets, 2 ways: First, a note on using .FormulaR1C1. You may use it in 2 ways. 1) Sht1!R2C3 refers to the 2nd row and 3rd column of Sht1, in other words, Cell C2. 2) =Sht1!R[2]C[3] means the cell RELATIVE to the active cell. So if the activecell on Sht1 is D5, then...

Excel: VBA programming of linest Function, linear regression, erroneous output

linear regression, erroneous output, functions in excel: the limitation for regression/linest is 16 x values. Perhaps you have a collinearity problem. In your function you should have Dim ols as Variant You current setup may work, but it also may cause a problem. Anyway, here are some resources...

Excel: VBA programming, microsoft excel 2002, vba commands

microsoft excel 2002, vba commands, brick wall: Cameron I think you an solve it in a easier way by using sraightforward formulas As follows First column= zip code second column, third column, fourth column, etc.= times (by hour??) use a if formula to separate the times into the appropriate columns...

Excel: VBA Query, target address, target value

target address, target value, address range: Gill, the original author used the change event. That means the code would fire anytime a cell was changed. It has a condition that it won t check for a password unless the change was made in cell A1 - so it assumes the password is entered in cell A1. ...

Excel: VBA Question, status bar message, retrieving data

status bar message, retrieving data, ampersand: Your code says to find the word OldRegion and replace it with the word NewRegion, or vice versa - can t remember the syntax now. Your code says this because you have those words in quotes. If you want to replace the word AND USE THE VARIABLES then simply...

Excel: VBA Question, drop down menu, pops

drop down menu, pops, cells: You don t give me enough specific detailed info but based on what you have told me then you can record a macro that will then show you the code that deletes the contents of cells. If there is more to this than that then you were not clear about that. Remember,...

Excel: VBA question to Compare Columns, dollar gain, cell d1

dollar gain, cell d1, dollar loss: I don t quite follow - in some instances it seems you re looking from the active cell upwards to see what happened, in others it seems you re looking downwards from the active cell - do I don t follow your calculations. For example, D1 you label as Open...

Excel: VBA question - combo box, select different charts, excel combo box, control toolbox

excel combo box, control toolbox, combobox: If it is a combobox from the control toolbox toolbar, then you would use the click event of that control. On the control toolbox toolbar, in the upper left corner, there should be a button with a drawing triangle and a ruler on it. Click this to go...

Excel: VBA question; Des was helping with but is now on vacation, sheet1, wkb

sheet1, wkb, excel workbook: Sorry about that. Please try this instead ... On Error Resume Next For Each wks In wkb.Worksheets ThisWorkbook.Sheets(wks.Name).Delete wks.copy after:=thisworkbook.sheets(thisworkbook.sheets.count) Next wks On Error GoTo ErrHandler wkb.Close...

Excel: VBA question for excel 2003, vba script, hot key

vba script, hot key, string value: In a case like this, I won t use VBA for that, but a formula that would tell me which of the people on the guests list are in the bad guys list. To do so, it would help to name (menu Insert- Name- Define) the list of the bad guys to something like bad_guys...

Excel: VBA Run Macro, windows menu, niether

windows menu, niether, message saying that: Paula, I have been programming Excel for over 10 years and I have never had to do anything else unless there are duplicate names in separate modules within the workbook. On way to check your situation is, in Excel itself, go to the Windows menu item and...

Excel: VBA range, b cells, correct syntax

b cells, correct syntax, numeric arguments: Since you prefer to use the Cells method, and that is certainly OK to do so, then you would use Dim MyRange as Range Set MyRange = Range( B10 , Cells(FinalRow, FinalCol) with my alternate way of handling the range Since FinalRow and FinalCol...

Excel: VBA & range, colums, quick reply

colums, quick reply, copy paste: There was no assumption - that is what you told me. You did not say that they would be varying. Remember I can not see what you see and was not in your head as you are/were designing this. So, all I can know is ONLY what you tell me. To do what you want...

Excel: VBA and remote file references, sheet1, excel 2002

sheet1, excel 2002, time error: Hi Lain, I hope you are well. Will the following helps you? Sub ggg() Dim A As Workbook, mystr As Range Set A = Workbooks.Open( D: estar.xls ) Set mystr = A.Sheets(1).Cells(1, 1) MsgBox mystr End Sub Or you want something copmpletely different?...

Excel: VBA runtime error 1004, data columns, runtime error

data columns, runtime error, time error: It seems that you mean for RangefT, RangefA, RangefB to be or refer to ranges. If this is the case then whenever a variable is to an object, like a range, a worksheet, a workbook, a chart, etc. then you must use the Set command. So, Dim RangefT As Range...

Excel: VBA Select Directory, ms access vba, directory dialog

ms access vba, directory dialog, bbrowse: Hi Joanne, I found this code at one point during my search for a similar problem. Hope this helps Bhaskar Option Explicit Private Type BrowseInfo hwndOwner As Long pidlRoot As Long sDisplayName As String sTitle As String...

Excel: VBA Select Next Visible Cell, odd method, excel vba

odd method, excel vba, input box: Luc, I think this is one of my biggest gripes with Excel VBA. I have not found an obvious way to select cells within an autofiltered list. I have however, developed this odd method that seems to work OK. I set it for the second visible cell, assuming that...

Excel: VBA - Selection.Find, laymans terms, description column

laymans terms, description column, thanks in advance: Well if that is the case then how is the code going to recognize when a row needs to be inserted and then put a total line as it looks at the cells? The code will have to be able to identify all items that belong to a certain category for which you want...

Excel: VBA Show forum on sheettab selection, excel spreadsheet, thanks in advance

excel spreadsheet, thanks in advance, usefull: Rightclick the appropriate sheet s tab and choose View code From the left dropdown at the top of the code window, select Worksheet . From the right-hand dropdown, select Activate . You will now have two sheet event routines. Remove the SelectionChange...

Excel: VBA SUB ROUTINES/FUNCTIONS, brian hughes, msgbox

brian hughes, msgbox, vba code: Hello Brian, If calculation is set to automatic, the formula should recalculate automatically as your code changes values the formula is dependent upon. If that is not happening or you want to do it explicitely then If you want to force the cell to...

Excel: VBA Sub for Excel, range cities, selection sort

range cities, selection sort, a3: Hi Craig, Try the code below: Sub Cities() Dim ws As Worksheet Dim c As Range Dim varI As Variant Application.DisplayAlerts = False Define Cities Range With Worksheets( AllCities ).Range( A3 ) ...

Excel: VBA save macro, cell changes, sove

cell changes, sove, concatenation: Ken, the code for that would look something like this: ActiveWorkbook.SaveAs C:VBA tests & Sheet1.Range( B8 ).Value & _ & Sheet1.Range( G8 ).Value & _ & Sheet1.Range( B9 ).Value & .xls , FileFormat:=xlNormal If the location of the cell changes,...

Excel: VBA script issue, legal jargon, object error

legal jargon, object error, doc id: Nick I guess the break in the code wasn t obvious enough. You can NOT combine FillDown with the next segment of code - there must be no period and continuing code after FillDown. Selection should start a new line of code. That should be all you need...

Excel: VBA seens to not be working, invoice numbers, seens

invoice numbers, seens, number generation: Greg, Any answer is a pure guess since I don t know the version of excel or anything else about your situation or environment - but if you have security set to high, and manually open the workbook, macros are disabled silently without prompt. (xl2002 and...

Excel: VBA - setting Range with another cell value, getvalue, sname

getvalue, sname, fname: With only part of the code, I can t understand why you are looping through a range containing one cell. Also, I have no idea what Getvalue is - possibly a Function. Try this, it will allow the user to select a range. sName = Summary Set Myrange =...

Excel: VBA to size spreadsheet tabs to screen size, area7, area2

area7, area2, alistair: Unfortunately, the Zoom method requires the window to be active to work, so the macro has to activate the areas to be effective, no workaround that I know. One thing with your macro, as you have it now it will put the worksheet zoom at 100%, you may try to...

Excel: VBA Textbox control lacking of .Characters method, textbox control, ogilvy

textbox control, ogilvy, excel 2000: If your talking about the textbox in the control toolbox toolbar, you don t have a characters method because, for one reason, it doesn t support rich text formatting. You can place more than 255 characters in it at one time however, so for the specific article...

Excel: VBA Transfer Range Values from Table, proper syntax, vlookup formula

proper syntax, vlookup formula, value source: Hi Ken How are you? I did not understand completely what you are heading to. But the following is possible Sub hhh() Set Dest = Sheets(1).Range( F1 ) Set Source = Sheets(1).Range( G1 ) Set x = Sheets(1).Range(Dest.Cells(1, 1)) Set y = Sheets(1).Range(Source.Cells(1,...

Excel: VBA - tie a macro to the click of a cell, chris answer, cell a1

chris answer, cell a1, command button: Well, don t get upset at me - I answered the question you asked. If you knew the answer to the question you asked then I have to wonder why you asked it in the first place. How was I to know that you wanted to know HOW instead of just can it be done? All...

Excel: VBA timed event, time intervals, appers

time intervals, appers, excel 7: Hi Jim! How are you? Please try the following: 1. TOOLS - MACRO - VISUAL BASIC EDITOR 2. SELECT THISWORKBOOK ON THE PROJECT EXPLORER 3. Copy Private Sub Workbook_Open() MsgBox testing Application.OnTime Now + TimeValue( 00:00:10 ), my_Procedure...

Excel: VBA training, vba programmers, des o connor

vba programmers, des o connor, live interaction: Hi Des, Thanks for that support, it s good. Training is the old bug-bear. I will contact you privately as I think a chat would probably address it quicker and more expeditiously than a salvo of correspondence. I ll be in contact. Regards Bo...

Excel: VBA and Userforms, correct dollar, correct column

correct dollar, correct column, column headings: Jeff, This question is too long to try and answer with the limited resources of this site. If you can email a copy of your workbook to chad@welchkins.com I will be happy to try and help you. Please remove any confidential information before sending the...

Excel: VBA Using Outlook/Excel, mymessage, excel group

mymessage, excel group, outlook group: Given the inter-operability of these applications, you COULD write the code in Excel, have the form there and just automate outlook from there. You can easily create a UserForm and call it (UserForm1.Show). youruserformname.Show by the way should also work...

Excel: VBA - userforms, input mask, numeric entry

input mask, numeric entry, custom dialog box: There is no built-in Property of a Textbox for this. You can add code to the TextBox_Exit event to force this to happen. Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim cnt As Integer Dim x x = Me.TextBox1.Value...

Excel: VBA not working as expected, index number, shapes

index number, shapes, spreadsheet: Hi Mark, The reason why you are having this problem is that each time a picture shape is deleted, all the pictures get renumbered (not their names, but their index number) so that after the first picture is deleted you then have pictures 1..3, then 1..2,...

Excel: VBA not working as expected, brainbench, shp

brainbench, shp, shapes: Mark, For not using VBA very much that is a very nice piece of code. If the only shapes on the sheet are pictures then it will work if you change one line: With Sheet2.Shapes(n) to With Sheet2.Shapes(1) Every time that line of code is evaluated the...

Excel: VBA, random number generator, 6 cells

random number generator, 6 cells, additional number: it ll be 2 to the power of 6.... To test, ... have another cell with the sum of the 6 cells... and calculate until this = 6... incrementing a counter. Sub Macro1() ActiveSheet.Calculate icounter = 0 While Range( G1 ).Value 0 ActiveSheet.Calculate...

Excel: VBa Substruct, array formula, cell a1

array formula, cell a1, blank cell: I haven t anything similar, but I don t know why it is needed - if such values have been created on a worksheet, the easiest fix is to correct the formula sub Correcter Range( A1 ).Formula = = & Range( A1 ).Value end sub If you don t want to...

Excel: VBLOOKUP FORMULA, 12345678, comma

12345678, comma, credit card number: =vlookup(CreditCardNumber,Account.xlsx!location,2,false) is the basic structure - the easiest way to build it is to have both files open side by side, then start typing the formula - click on the credit card number cell in the first workbook just after...

Excel: VBScript, dictionary object, array

dictionary object, array: It depends on what you want to compare inside the row, one way of doing it is the following: - Parse the rows on the first worksheet, and fill an array or a dictionary object with the values that form the key. - Parse the rows on the second worksheet, comparing...

Excel: my Vb code cannot read path other than c:, array array, e drive

array array, e drive, true space: Hi Zulfattah How are you? I am sorry I cannot try your code because at the moment I am not connected to a network. I tried with a uSB stick and it works. It opens the files that are stored on the E: drive. But where are you going to copy this data to? ...

Excel: Vba array formula, array formula, gold team

array formula, gold team, pivot table: Robert, Array formulas entered with code are restricted to 255 characters in length I believe. However, this does not appear to be your problem. There is no restriction on the number of conditions, at least it is well over 4 give formula length restrictions....

Excel: Vba-code, vba commands, contenets

vba commands, contenets, code application: Ulf, There is a mantra is VBA programming that goes like this You do not have to select to affect. Meaning, you do not have to select a cell in order to affect its contenets. Consider the two examples below. One selects cell D7 then changes its contents...

Excel: Vba Forms, merry christmas and happy new year, sheet1

merry christmas and happy new year, sheet1, happy new year: a Merry Christmas and Happy New Year to the Thompson Household as well (From Fairfax, Virginia, USA; just outside Washington, D.C.) Private Sub CommandButton1_Click() Dim i as Long, ctrl as MSForms.Control With Worksheets( Sheet1 ) .Range( A1:A12 ).ClearContents...

Excel: Vba Help, counta, pivot table

counta, pivot table, google: All you need is a tricky dynamic named range. It is basically a range that is dynamically expanding as you add data and of course, your pivot table refers to the named range. if you had your dates in column A on Sheet1 and the first row is the header, then...

Excel: Vba macro, vba macro, true font

vba macro, true font, myfile: Hello Minal, Here is the problem. F contains a fully qualified filename such as C:MyfoldderMyfile.xls however, the Workbooks Collection requires just the unqualified filename MyFile.xls That is why IsWorkbookOpen doesn t work with the...

Excel: Vba to match pivot table to new size data, table wizard, pivot table

table wizard, pivot table, database column: Assume the data starts in A1 on Sheet Data insert= Name= Define Name:= List Refersto: =Offset(Data!$A$1,0,0,CountA(Data!$A:$A),11) make the defined range List the source for you pivot table. Assumes that for rows in your database, column A will...

Excel: Vba Solution to trimming Date and Time to date, hh mm ss, decimal portion

hh mm ss, decimal portion, value int: Hello Robert Sub TrimTime() for each cell in Range( Loaded_Date ) if isdate(cell) then cell.value = Int(cell.value) end if Next End Sub Would be the basic approach to remove the time portion. Dates are stored as whole numbers marking the...

Excel: Verifying a combinaison of numbers, conditional formatting, combinaison

conditional formatting, combinaison, winning numbers: If you let me have your email address (it doesn t get displayed to the expert) I will send you some files that do what you want - I use CONDITIONAL formatting to change the colour of numbers that match the chosen values, but it will be easier for you to see...

Excel: Verifying Names in multiple worksheets, cell d2, column c

cell d2, column c, sheet1: Hi Eric, I will take up your second problem first. Since you have many smiths, what you can probably do is combine columns A & B in Column C by a formula - =A2& &B2 This will make names line Smith John for example. Then you can use VLOOKUP to verify...

Excel: Vesting, cell b5, question thanks

cell b5, question thanks, current date: Christina: Okay, going to be very blunt here - not trying to be mean :), but I believe your math is in error. Either that, or the information you have provided me is incorrect. Let me step through your most recent question... 1) After 1 year you are...

Excel: View and Edit Database, userform excel, commandbutton

userform excel, commandbutton, textboxes: Hi, are you talking about the built-in userform Excel has for databases (under Data-Form... on the menu) or a UserForm that you want to create yourself? If you already have a sample, I wouldn t mind seeing it. What version of Excel are you using? ...

Excel: View image within spreadsheet, exit function, view image

exit function, view image, text string: Hi Steve, There are numerous ways to bring up a picture in Excel. Probably the easiest way is to use the ShowPicD function I ve written (see the code below) which allows you to simply pass a file pathname to this function to cause the corresponding picture...

Excel: Viewing date in Pivot using VBA, pivot table, pivottables

pivot table, pivottables, anita: Rather than selecting a cell, please try to use a line of code like this: ActiveSheet.PivotTables( PivotTable1 ).PivotFields( month ).PivotItems( jan ).ShowDetail = False Just change the names to correspond to your own PivotTable name, field, and item....

Excel: Views in excel, accessability options, spreadsheet

accessability options, spreadsheet, cells: No. You could put a cell comment in EVERY cell that will contain the contents of that cell but this will add a lot of overhead to the workbook. You will need a third party program to do that. I think you will quickly find that this will get very annoying....

Excel: Virtual AutoFill macro, generous number, zero values

generous number, zero values, zeros: Yes I can and know how to write it. How short is short? Also, you need to be aware that 99% of all macros are more involved than what you think and rarely am I provided with enough specific and complete details initially to have the code work the first...

Excel: Visio, Excel and C#, component object model, visio diagram

component object model, visio diagram, visio application: Hi Adelaide, I am not familiar with either MS visio or C#. Nevertheless, since all the Microsoft development languages have a similiar way of interacting with Excel, the following pointers should help you get started. 1. The mechanism of interacting...

Excel: Visual Basic and Excel, printing preferences, excel application

printing preferences, excel application, using visual basic 6: Yes, no problem - you don t actually click the button, you run the associated macro but in principle it s the same thing Dim myobj As Object Set myobj = GetObject(, Excel.application ) myobj.Run personal.xls!test NOTE that I ve just shown the...

Excel: Visual Basic and Excel, vb question, printing preferences

vb question, printing preferences, vb developer: Jeff, Although I m not a VB6 expert... (This really is a VB question as opposed to an Excel question.) To implement an excel object I know you ll probably need the reference for Microsoft Excel XX.0 object library in your VB6 app. You might try...

Excel: Visual Basic / Macro Problems, speadsheet, excel spreadsheet

speadsheet, excel spreadsheet, windows xp pro: I m not really a macro expert, but a couple of mistakes that I make are using the absolute/relative referencing when I want the opposite, may be the reopening after changes problem. Importing a file and not importing the macro, maybe why it doesn t come back...

Excel: Visual basic error, run time error, custom icon

run time error, custom icon, personal folder: Hi Roger Greetings from the UK too. Portuguese though. Can you please try something like Sub MyIF() On Error Resume Next Do Err.Clear MsgBox Please Plug-in the memory stick Workbooks.Open Filename:= E:Excel bitsIF.XLS Loop While Err.Number...

Excel: Visually display data, conditional formatting, info charts

conditional formatting, info charts, excel charts: Difficult if not impossible to answer - conditional formatting could highlight areas of the data, pivot table could consolidate it, and various chart options exist (over and above the standard, see http://processtrends.com/ http://peltiertech.com/Excel/Charts/index.html...

Excel: VLOOKUP, general ledger account, value segment

general ledger account, value segment, column feature: Stefanie Your formula worked for me. I copied it directly from the text of your question to an excel spreadsheet and then created data in p2 thru q11361 and it worked fine. sounds like you have checked most everything, but I m guessing that the data you...

Excel: VLOOKUP with 2 conditions, array formula, apprentice 2

array formula, apprentice 2, vlookup: Hi Brian, Unfortunately, EXCEL does not permit VLOOKUP on 2 columns directely. You have 3 options- (1) Wrting a MACRO - Not advised by me (2) (a) Create an additional column between your B and C Columns. (b) Use =A2 & B2 in new C2 and Copy / drag it till...

Excel: VLOOKUP where 2 or more values match, numeric entries, vlookup

numeric entries, vlookup, epact: Hi Mig Difficult to get my head around. 6 columns. Columns A, B, D and E each contain 50 numbers all different, unsorted. Column F contains index data. Need confirmation, and more gen on EXPEA and EPACT. Note that my core expertise is VBA, so you may end...

Excel: VLOOKUP, vlookup, days thanks

vlookup, days thanks, excel 2002: Hi, Angelo, VLOOKUP cannot handle two parameters, but there is probably another way to accomplish what you re trying to do. Can you write back with a specific example? Tell me what your data looks like, and show me exactly what you want the formula to...

Excel: VLOOKUP, cell b1, cell a1

cell b1, cell a1, vlookup formula: I suggest using the VLOOKUP formula. Presuming you are entering the lookup values in cell A1 of sheet1, you would put something like this in cell B1: =VLOOKUP(A1,sheet2!A:D,2,false) In C1: =VLOOKUP(A1,sheet2!A:D,3,false) In D1: =VLOOKUP(A1,sheet2!A:D,4,false)...

Excel: VLOOKUP, cell d5, vlookup function

cell d5, vlookup function, sheet1: ...have even added False to the formula and get the same results... Well, what results do you get? And, ...cell D6 will not perform its function... What do you mean cell D6 will not perform its function? Cells do not perform functions - they just...

Excel: VLOOKUP, ascending order, aidan

ascending order, aidan, spreadsheet: Based on the information given in this question and in your email to me, I would SUGGEST the easiest way of achieving what you want is to sort the data such that the values are in ascending order of column A, but with the FLAT item first, the SPHE second and...

Excel: VLOOKUP, douglas m smith, cell a2

douglas m smith, cell a2, brainbench: Hi Shai, VLOOKUP will not work within a cell, but you can use the FIND function to locate a string of text in a cell. If cell A1 contained your example text and cell A2 contained NM_130832, then this formula would find the string: =FIND(A2,A1) The...

Excel: VLOOKUP, product information sheet, quotation form

product information sheet, quotation form, vlookup formula: Oliver The VLOOKUP function should do the job for you. It should be set up as follows. You will have a cell where you will enter the code of the product or item. The vlookup formula will refer to that cell and ask excel to lookup the code in the product...

Excel: VLOOKUP and Cartoon Cusswords, cusswords, vlookup

cusswords, vlookup, gmail: Fred The formula should look like this =VLOOKUP(E11,A1:B3,2,2) There should be no quotes as this will change the formatting. If this isn t the problem can you send me a copy of the file as I will be on break from allexperts but will be able to access...

Excel: VLOOKUP - CONTAINS, vlookup formula, search formula

vlookup formula, search formula, perfect match: Rita I don t know of a way to do your lookup exactly as you are describing it, But the search formula will return a position number if the text is present in a cell. You might use that as an indicator. You would have to do a formula for each value that...

Excel: VLOOKUP on Dates, vlookup, e20

vlookup, e20, 7 oct: First, get the location of the first date by =MATCH(lookupdate,lookuparray,0) For example, if your lookupdate is in C1 and your array is column A, use =MATCH(C1,A:A,0). This would give a value like 4. Then, assuing the MATCH function is in cell E1, enter...

Excel: VLOOKUP in Excel, vlookup in excel, absolute references

vlookup in excel, absolute references, thanks in advance: Hi Pete, The codes worksheet needs to have the unique ID (vlookup) field as the far left column of the look up area. It doesn t necessarily have to be column A, but it does need to be the left side of the range that you will be looking up and everything...

Excel: VLOOKUP function, alpha numeric, adam eve

alpha numeric, adam eve, albert hotel: Mike Vlookup will only work if the lookup cell is in alpha numeric order and is unique otherwise you have difficulty finding the correct answer. Because you are using the cells something like a database it may be difficult to do what you want using a...

Excel: VLOOKUP function, vlookup function, match

vlookup function, match: I would work with the function rather than fighting it - offset your lookup values by one row so that the values you are returning are effectively correct. IF the table needs to be viewed by humans as well, just have the offset values in a hidden column to...

Excel: VLOOKUP function, array formula, adam eve

array formula, adam eve, albert hotel: As far as I can tell, vlookup(value,range,column,TRUE) will do it for you IF the data is laid out as you have it, with all the values you are looking up in order value being the value you want to look up range being the range you are looking in column...

Excel: VLOOKUP HELP, cell a1, index number

cell a1, index number, combobox: VLOOKUP looks in the FIRST column for the information and returns the information on the same row of that column, or a column TO THE RIGHT of that column. So you need a different solution (assuming you entered the name in cell A1): =INDEX($A$11:$A$46,MATCH($A$1,$B$11:$B$46,0))...

Excel: VLOOKUP Help!!!, cell b1, b30

cell b1, b30, exact reason: As there is nothing wrong with the formula, it means that the value at B1 does not exist in the same format on the range A1:B30 on sheet1 - eg, extra space, or number formatted as text. Without seeing the data I cannot give you an exact reason - my email...

Excel: VLOOKUP in Multiple Rows, vlookup, ticket number

vlookup, ticket number, dream ticket: Hi Ken, Unfortunately that is how VLOOKUP will work. As I understand, you want ALL possible actions to be recorded against a Ticket Number. I think the best way would be to use a MACRO. If you are open to use=ing a MACRO, I can write one for you. Please...

Excel: VLOOKUP with multiple lists, vlookup function, scale score

vlookup function, scale score, raw score: Hi Aaron, I still feel - based on the information given by you, that VLOOKUP with IF should work. Please give, Ranges of reference table, the desired conditions and the formula you tried. May be we can give another try. I have used IF and VLOOKUP together...

Excel: VLOOKUP with multiple lists, vlookup function, scale score

vlookup function, scale score, raw score: In C2: =IF(A2= , ,IF(A2= B ,VLOOKUP(B2,SheetB!$A$1:$F$20,5,FALSE),IF(A2= C ,VLOOKUP(B2,SheetC!$M$10:$Z$100,10,FALSE),VLOOKUP(B2,SheetD!$B$20:$G$150,3,FALSE)))) would be an example. This assumes column A will be either empty or contain B, C, or D....

Excel: VLOOKUP reference table, vlookup formula, vlookup function

vlookup formula, vlookup function, key field: Patrick, Yes, you can use the indirect function to do this: =IF(ISERROR(VLOOKUP(A3,Indirect( WIP Open Jobs !$A$15:$M$3000 ),8,0)), ,VLOOKUP(A3,Indirect( WIP Open Jobs !$A$15:$M$3000 ),8,0)) You might have to experiment whether the single quotes...

Excel: VLOOKUP references, vlookup function, leftmost column

vlookup function, leftmost column, column c: For VLOOKUP to work the lookup value must be in the leftmost column - see http://www.excel-it.com/excel_functions.htm. What you need to do is use the Index & Match functions. Assuming the result is in Column B and the table is A1:C10. =INDEX($A$2:$A$4,MATCH($E$1,$B$2:$B$4,0))...

Excel: VLOOKUP return, paste values, vlookup formula

paste values, vlookup formula, spreadsheet: You may try copying and pasting, not cutting. The cut operation normally carries the references with it, and that may be the cause of the error. Other option is copy and paste values, this should be the most similar to directly type the number in the cell....

Excel: VLOOKUP & SUMIF, e mail address, frederique

e mail address, frederique, chantal: Enter your dates in a format that s recognized by Excel as a date. So instead of 1st Jan enter 1/1/2007 Then, for example, if you ve got dates like this in Col A, and someone s hours in col B, and you want to get the sum of all hours this person...

Excel: VLOOKUP - Variable table_array, douglas m smith, tom mccann

douglas m smith, tom mccann, cell a2: Hi Tom, As you ve discovered, Excel thinks you are trying to reference a different instead of looking for the cell range you want. Fortunately, Excel can manufacture a reference using string functions and the INDIRECT function. If you enter your reference...

Excel: VLOOKUP won't work within SUMPRODUCT, cell c2, cell a2

cell c2, cell a2, pivot table: Hi Bill, How are you? I think that the best way to do it is to: 1st Insert an additional column between TERRITORY NUMBER and Product 2nd Fill this column with the region e.g. assuming that territorynumber is in A2, type in B2 =VLOOKUP(LEFT(A2,2),DistToReg,2)...

Excel: VLOOKUP into another workbook in VBA, phone number address, bad debts

phone number address, bad debts, notes application: For example: DON T use Address = Application.WorksheetFunction.VLookup(CustNo, [CustomerList.xls]!Range( $A$4:$F$1000 ), 3, False) but instead: Address = Application.VLookup(CustNo, Workbooks( CustomerList.xls ).Sheets( Sheet1 ).Range( $A$4:$F$1000...

Excel: VLOOKUP, format menu, menu option

format menu, menu option, possible solution: One POSSIBLE solution is not to use vlookup at all, but to use autofilter - this would only show the cases that match the criteria. Alternatively, if you have up to FOUR different formats, you could use the format - conditional format menu option - this allows...

Excel: VLookup Alternative, sheet1, vba code

sheet1, vba code, description column: If you want to find ALL the occurrences of the data in G1, it should be absolute because you re filling it down and you don t want to find the occurrence of G2 for your second find. If you ALSO want to find stuff you have in G2, G3, etc, (all occurrences),...

Excel: VLookup & Drop Down Boxes, vlookup function, drop down boxes

vlookup function, drop down boxes, cell a1: You are right, VLOOKUP is the function that fits best on your case. In order to return different columns, you have the third parameter of the function, where you can select the column to be returned from the range entered as second parameter. For example,...

Excel: VLookup Help, vlookup function, column c

vlookup function, column c, optional argument: When looking up numbers, or text, and you want to find an exact match then use the fourth optional argument and use FALSE, =VLOOKUP(A1,C1:D100,2, FALSE) If the number you are looking up is text then the table s number must be in text also. If they...

Excel: VLookup Help, vlookup function, column c

vlookup function, column c, integer number: Hi Leon, I like to use MATCH in situations like yours. The formula for column C would be something like: =MATCH(A2,$B$2:$B$3000,0) If there is a match, the result is an integer number corresponding to the nth cell in the range. If there is no match,...

Excel: VLookup using two lookup values - excel 2000, array formula, hold down ctrl

array formula, hold down ctrl, text cells: Hello, Terry, Yes, there is a way to do this using an array formula. In case you re not familiar with array formulas, they are a handy way of processing a single formula over an entire range of cells, all in one step. An array formula is perfect for creating...

Excel: VLookup for vertical and allignment question., how to use vlookup, format cells

how to use vlookup, format cells, currency symbol: (1) I haven t been able to get the currency symbol to move away from the first number in Office XP, so it may be version dependentant, but try format cells, alignment left to fix this (2) there are VLOOKUP and HLOOKUP functions, but I m SLIGHTLY puzzled...

Excel: VlookUp Again, circular reference, column c

circular reference, column c, column number: Did not see all of your reply/question so I just replied by saying OK since I did not know what else to say. I could not tell you were asking for more help. Not sure I understand. IF they are not referencing the table then change the references so that...

Excel: VlookUp, vlookup formula, absolute references

vlookup formula, absolute references, source table: You only made part of the formula be an absolute reference - the false part of the IF statement. I suspect you want both to be that way and are referring to the reference inside the IF as the one that keeps changing . So, =IF(ISNA(VLOOKUP(B593,Values!$B$4:...

Excel: Vlookup, vlookup formula, text numbers

vlookup formula, text numbers, hi mike: Hi Mike, Not exactly sure why that is. But I would try this when you go to paste. Right click and select Paste Special then select Values . That way it is only pasting the values and not the formatting of any kind. Hopefully this solves the problem....

Excel: Vlookup, vlookup formula, vlookup function

vlookup formula, vlookup function, f9 key: 1 - in D2: =VLOOKUP(B2,$A$1:$A$15000,3,FALSE) and fill down to D3000 or so. 2 - no 3 - no. But if you see an absolute match which returns #N/A, select the item in the vlookup formula, say the B44 in =VLOOKUP(B44,$A$1:$A$15000,3,FALSE), and press the F9...

Excel: Vlookup, vlookup function, optional argument

vlookup function, optional argument, product numbers: You use both workbook and sheet interchangably but they are different. I will assume that you mean sheets for both. The VLOOKUP function has a fourth optional argument that, if not typed, defaults to TRUE. Using FALSE will tell the function to find an...

Excel: Vlookup, raw report, vlookup formula

raw report, vlookup formula, cell a1: NINA I just noticed that your question is listed as being unanswered--I m sorry I thought I had answered it several days ago. I believe the simplest way for you to do the update is to use a formula that transfers the numbers from one sheet to the other....

Excel: Vlookup, 222222, 111111

222222, 111111, how to use vlookup: I m not absolutely sure what the quesiton is but-- when you use VLOOKUP it will return the first value it finds, even if there are multiple values, so duplicates of what you are trying to lookup will not be itdentified. Sometimes this can be rectified with...

Excel: Vlookup

Thomas VLOOKUP is designed to return the value, it will not return the address. Sorry I m not sure what ou are trying to do, so I can t make any meaningful suggestions, but perhaps a combination of VLOOKUP and other formulas might work for you. ...

Excel: Vlookup, rate percentage, inputted

rate percentage, inputted, headcount: I don t really understand the question. VLOOKUP(AND(... will wind up being VLOOKUP(TRUE,GrowthRate20,1,FALSE) or VLOOKUP(FALSE,GrowthRate20,1,FALSE) so that won t work. But I can t help more because I just don t understand what you re looking for. Including...

Excel: Vlookup and array formula, vlookup formula, array formula

vlookup formula, array formula, problem thanks: Hi Gary How are you ? I have tried the following and it works within excel 2003 1. Type (table) b12:1 B13:20 B14:40 c12:3000 c13:4900 c14:8000 2. type a20: 23 b20: =VLOOKUP(A20,$B$12:$C$14,2) d6: 4900 3. Type IN E8 =B20=D6 Cheers Adelaide...

Excel: Vlookup basic, vlookup function, column c

vlookup function, column c, thanks in advance: Leon, I m not sure I understand what you re trying to do. You have a long list of numbers in A and a shorter list in B. You would like to see which numbers in B also occur in A, correct? And if a number in B *does* occur in A, you d like to note that in...

Excel: Vlookup if column c contains content 'like', colonnades, vlookup

colonnades, vlookup, column c: If A1 contains the CT-FC Colonnades-Costs, and I m assuming that if there s a dash (-) in the cell there will be 2 and you want to extract the middle piece, this formula will do it: =IF(ISERROR(FIND( - ,A1)),A1,LEFT(MID(A1,FIND( - ,A1)+1,255),FIND( - ,MID(A1,FIND(...

Excel: Vlookup Function, inventory discrepancies, cell c2

inventory discrepancies, cell c2, model column: The function VLOOKUP won t help too much in this case, it would be better suited to return the Serial number that correspond to a model, and only if the model numbers are unique in the listing. For your case, I would use the SUMPRODUCT formula in a separate...

Excel: Vlookup Function, inventory discrepancies, model column

inventory discrepancies, model column, excel works: Hi Yezenia, Firstly, the assurance.. VLOOKUP will work. Secondly, the caution... It will not work directly. :-) Unfortunately, VLOOKUP in excel works on single column. For instance, I can find whether Each of the Model#s in Sheet1 are in Sheet2 or not....

Excel: Vlookup function, wildcard characters, ogilvy

wildcard characters, ogilvy, source file: I guess it depends on what similar means. Vlookup doesn t require an exact match, but when it doesn t, it requires the data to be in sorted order. You can get the details in help. You can also use wildcard characters to make the match a little more...

Excel: Vlookup Help, info thanks, true statement

info thanks, true statement: It s just doing what you told it to do. Your statement says, If it can t find C5, return A . Otherwise, if it CAN find C5, then if the result of the lookup is empty, return VLOOKUP(C5,$I$5:$J$5000,1,FALSE) -- which is C5! and if it s not empty return N...

Excel: Vlookup Macro, false hope, q3

false hope, q3, specifier: Hi again Chris, Yes, that explanation helps. I think the following code does what you want. It uses the MATCH function rather than VLOOKUP so that it knows which cell to copy. It then uses a simple copy, which copies both contents and format of the cell....

Excel: Vlookup Macro, false application, range selection

false application, range selection, selection range: HI Chris I need more information than it simpley didnt work. did it crash if so did you press debug ? if so what line of code was highlighted. I suspect its this line Worksheets( Yesterday s Orders ).Columns( T:T ).Find(what:=Range(Qs).Text, after:=Worksheets(...

Excel: Vlookup for multiple 100x100 tables, cv100, macro security

cv100, macro security, vlookup: Hi Brandon How are you? You can write a VBA program to ask the age the user wants to check and highlight the row of the corresponding age. Something like 1. Tools - Macro - security - security level - low Tools - Macro - security - trusted publishers-...

Excel: Vlookup nested with If then Formula, sorry for the confusion, chanes

sorry for the confusion, chanes, e100: Make the appropriate chanes to the ranges. This assume the lookup value is in A1 adn your lookup table is in B1:E100 and column 3 is the positive lookup column and 4 is the negative lookup column. =IF(ISERROR(IF(A1 1,VLOOKUP(A1,B1:E100,3,FALSE),VLOOKUP(A1,B1:E100,4,FALSE))),...

Excel: Vlookup Pasting Problem, vlookup formula, absolute references

vlookup formula, absolute references, source table: You only made part of the formula be an absolute reference - the false part of the IF statement. I suspect you want both to be that way and are referring to the reference inside the IF as the one that keeps changing . So, =IF(ISNA(VLOOKUP(B593,Values!$B$4:...

Excel: Vlookup question, array formula, array formulas

array formula, array formulas, rep name: Bill, You would need to use an array formula You can download a sample file at the URL below. The columns D and F to build the uniques (in Customer Alignment) is not required for the list of customers. It is just how I got a unique list. You may already...

Excel: Vlookup References, vlookup function, silly things

vlookup function, silly things, gist: My guess is there are hidden characters you can t see (duh) - 2 recommendations: 1) enter =LEN(TheCellAddressitCantFind) and see if it returns the same # of characters you expect to see. If it does, then 2) email me a sample workbook & indicate which cell(s)...

Excel: Vlookup & If statement, vlookup function, sheet names

vlookup function, sheet names, number thanks: MATTHEW I m not quite sure in reading the formula whats going on with it but, I believe you can do what you need by using multiple VLOOKUP formulas. I.E. =IF(VLOOKUP(1,A1:A26,1)+VLOOKUP(2,B1:B26,1)+VLOOKUP(3,C1:C26,1) 0,VLOOKUP(G26,Sheet3!A1:C26,3))...

Excel: Vlookup table, array formula, personnel id

array formula, personnel id, vlookup formula: Roger, Greetings from the US. This involves use of an array formula and is somewhat specific to the layout of the data. It is a bit difficult to explain, so if you could send me a sample workbook with the basic layout (both pages) I can set it up for you...

Excel: Vlookup in VBA, range c, macro code

range c, macro code, sheet1: You could put that statement .Range( W2 ).Value = Range( Q2 ) - WorksheetFunction.VLookup(.Range( M2 ), .Range( C:H ), 5, False) in a loop as For i = 2 To 404 .Range( W & CStr(i)).Value = Range( Q & CStr(i)) - WorksheetFunction.VLookup(.Range(...

Excel: Vlookup, microsoft excel 97, vlookup

microsoft excel 97, vlookup, table a5: Bill, My kneejerk response is to ask why can the 4 tables not be combined? If they are separate, you re left with a complicated formula like below (which does work, but it may be hard to follow). For reference: Table1 = A2:B4 Table2 = A6:B8 Table3...

Excel: Vlookups by Rank, vlookup, column c

vlookup, column c, columb: assume that your data is in sheet2, and B1 in the sheet3 has the value Top/Bottom (Can easily be changed to the link cell of the validation button) A11 onwards has 1-20 (the 20 numbers to be retrieved) use this formulae in B11 =VLOOKUP(IF($B$1= Top...

Excel: vaildation list, data validation, blank space

data validation, blank space, excel 2002: Hello, Irena, There s a very easy way to do this. Instead of typing your list of choices into the Data Validation box, put it on the spreadsheet instead. Find some out-of-the-way spot and type in your list, one item per cell (making sure to leave one...

Excel: validate and clear objects in a userform, combo boxes, textboxes

combo boxes, textboxes, ok button: Attached to the OK button: For i=1 to 30 If me.controls( Textbox & i).text= then Msgbox Textbox & i & cannot be blank Me.controls( Textbox & i).SetFocus Exit Sub End If If me.controls( Combobox & i).Value= ...

Excel: validate and clearing objects in a userform, combo boxes, textboxes

combo boxes, textboxes, octl: Like this maybe: Private Sub CommandButton1_Click() Dim oCtl As MSForms.Control For Each oCtl In Me.Controls If TypeOf oCtl Is MSForms.TextBox Then If oCtl.Text = Then MsgBox oCtl.Name & cannot be empty,...

Excel: validation - pick list, dual monitors, visual basic for applications

dual monitors, visual basic for applications, excel vba: VBA=Visual Basic for Applications - When I work with Excel (always), I have the excel environment on one monitor and the programming language (VBA) on the other. You can see it by pressing Alt/F11. However, if you never heard of it, then I don t think that...

Excel: validation using lookup, b23, z21

b23, z21, a23: Can you be more specific re searches multiple lists ? If you mean A23 contains a match of something in A1:Z1, you can define a name, say List, to be =OFFSET($A$1,0,MATCH($A$23,$A$1:$Z$1,0)-1,23,1) and use =List in the validation dropdown for B23. Otherwise...

Excel: validation using lookup, data validation, z21

data validation, z21, b23: Chris: I m not 100% sure I understand the question. As such, I have uploaded a sample spreadsheet here and I hope it is what you are looking for: http://home.swbell.net/nate-sus/excel/lists.xls Step 1: I created the lists and data in rows A1:Z21...the...

Excel: validation, drop down boxes, validation

drop down boxes, validation, cells: D. It is possible but both workbooks have to be open, if this is suitable have a look at this website for linking the two workbooks. If this isn t suitable then you may want to link the workbook with the drop down items to a sheet in the other workbook,...

Excel: last value in a column of formulas, array formula, index array

array formula, index array, e5000: =INDEX($E$1:$E$5000,SUM(LARGE(IF($E$1:$E$5000 ,ROW($E$1:$E$5000)),1)),1) entered with Ctrl+Shift+Enter rather than just enter since this is an array formula. Change 5000 in each case to something beyond the last row you expect to ever use. You can...

Excel: #value error, value error, menu tools

value error, menu tools, zero values: The #VALUE error on formulas occurs when the cells refered have a different format that the one expected. For example, a text value on a multiplication. Check the values and formats of the data, I am not able to replicate your exact enviroment to tell you...

Excel: #value! error, value error, error value

value error, error value, cell a1: Hi Imtiaz, There is nothing wrong with the following formula you sent. =sum( Week 1 !F7+ Week 2 !F7+ Week 3 !F7+ Week 4 !F7) What you need to look at is any of the above cells formulas. For instance if one of the above cells has a formula such as....

Excel: value in $Millions, ms excell, numerical figures

ms excell, numerical figures, cell a1: Abdul I don t know of a way of formatting the worksheets to show only millions and fractions thereof. What I do is simply divide the number by 1000000 to get the results that I want. This may require that you import then create a formula that will convert...

Excel: Getting a value from another sheet, sheet1, holding period

sheet1, holding period, ogilvy: =indirect( K & sheet1!C12) is a possibility. But that makes the row dynamic and it sounds like you want the column dynamic I have no idea what rows labelled as Year1, Year2 means or where these labels would be or how they correlate to locations in sheet2....

Excel: #value!, blank cell, value error

blank cell, value error, error return: Hey, Dennis, it s not such a long shot! You bet you can. A common reason for the #VALUE! error is when a formula is trying to operate on the wrong value type. An example might be =J10*2 where J10 is Dennis. However, let s say that J10 sometimes has...

Excel: values that make a sum, long ranges, many thanks

long ranges, many thanks: Hi Korneel, Unfortunaltey, I don t know of a formula in Excel that will give you the results you are looking for. You would probably have to use some combination of formulas, but I don t know if Excel would be able to handle a calculation such as this....

Excel: valuta calculating, excel 2000, copy and paste

excel 2000, copy and paste, conversion: As I had requested ...Explain in more detail what you want ... and you still did not do this. Therefore I am assuming you want to convert a number from one form of currency to another. You will have to write a formula in the adjacent column, for example,...

Excel: variable input to interative calculation, exact formulas, automaticlly

exact formulas, automaticlly, variable input: Hey Keith, Would you be able to email me your spreadsheet so I can take a look at what you mean? If so, my email address is bluecat74@yahoo.com. I would be able to create exact formulas for you to use if you can send it. If not, can you try to explain...

Excel: variable posting on input form, relevant cells, tiny button

relevant cells, tiny button, command button: Since you said ...depending on the row of the button which opened the form... then this will be based on the activecell when the code is started and you can use what I suggested. ActiveSheet.ActiveCell.Value = UserForm1.fReadDate.Text ActiveSheet.ActiveCell.Offset(0,-1).Value...

Excel: variable source data to graph by marco!, colomns, range selection

colomns, range selection, sheet1: You don t need any VBA - use the range name in the chart SERIES formula, and have the range name be dynamic so as data grows/shrinks, so does the range name s reference. It s a lot easier to show you than explain, so email me your address & I ll send a sample....

Excel: variables, code option, mlx

code option, mlx, byval: You could use a class module to do that. Insert a class module, in the properties window, change its name to clsWorkbook. Enter this code in the class module: Option Explicit Private mobjBook As Workbook Private mlX As Long Private Sub Class_Terminate()...

Excel: variances, datedif function, project duration

datedif function, project duration, comparing dates: You can add another condition on this part: AND(A2 ,A1 ) For example: AND(A2 ,A1 ,A3 ) The d is a parameter to the DATEDIF formula, to give the difference in days. The 0 is the value that the IF formula will return if the statement is...

Excel: variation on autofi, cell a2, rng

cell a2, rng, excel 2002: Barry Based on my understanding of your description, this should do what you want. Works on the activesheet. Sub ABC() Dim rng As Range Dim rng1 As Range Set rng = Range(Range( B2 ), _ Range( B2 ).End(xlDown)).Offset(0, -1) Set rng1 = Range(Range(...

Excel: vb code for input in Excel, b cell, c cell

b cell, c cell, command button: Hi Brendan, I think you have gone by the route of recording MACRO. Generally, I go by the way of writing it. Hence, approach may differ. Let me try to visualise your data... Column B probably has employee number with some BLANKS in between. These numbers...

Excel: vb code for input in Excel, b cell, syntax error

b cell, syntax error, c cell: Hi Bredan Did it work ok for you Best Des ...

Excel: vb code for simple input in Excel, b cell, c cell

b cell, c cell, command button: Brendan, I see I mispelled emtpy and you may not know how to correct it. I have corrected it. while you have gone with the simplistic code Chad provided, perhaps you would test his on a blank workbook and see why a I added a little more complexity and a...

Excel: vb code for simple input in Excel, b cell, c cell

b cell, c cell, command button: Brendan, Your code is getting very close. I just cleaned it up a little. The following should work for you: Sub AddPerson() Macro to add a person Do Range( B65536 ).End(xlUp).Offset(1, 0).Select ActiveCell.Offset(0, -1).Value = ActiveCell.Offset(-1,...

Excel: vb coding, blank cells, exsisting

blank cells, exsisting, looper: Not sure how far you have got with this - my method for outputing to variable rows/columns is to use an OFFSET function - you can set row and column counters to do this eg dim Looper as long dim MyCounter as long MyCounter=0 for Looper=1 to 100 if...

Excel: vb formula, array formula, type mismatch

array formula, type mismatch, av1: Ken, The type mismatch is because of the SumProduct command. It can t be used with the application object. However, SumProduct won t work in this case anyway. You need to use an array formula. The only problem is that Excel won t allow an array formula...

Excel: vb isempty problem, paste data, range selection

paste data, range selection, excel 2002: Your syntax is wrong. You should have: If IsEmpty(Range( AM1002 )) = True Then You left out Range Additionally, you don t need to select the cell first to see if it is empty. RARELY do you ever have to select a cell - it is redundant. You can do...

Excel: vba, speadsheet, predefined time

speadsheet, predefined time, timevalue: Hi M.lock How are you? I wonder if the following suits you, but it can at least be a starting point for improvement: 1st TOOLS-MACRO-VBE 2nd Select thisworkbook (in project explorer - leftmost panel) 3rd Copy Public Sub WORKBOOK_OPEN() Application.OnTime...

Excel: vba, lcol, blank cell

lcol, blank cell, rectangles: Something like this: - Assign the same macro to each rectangle Then put this macro in a normal module: Sub PutDateBelow() Dim lCol As Long lCol = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Column Cells(Rows.Count, lCol).End(xlUp).Offset(1).Value...

Excel: vba, des o connor, text boxes

des o connor, text boxes, design mode: Hi Sherry I have ammeded the code so that movement enmasse of text boxes is now possible :- Hope you are un stumped now :) Regards Des Private Sub CheckBox1_Click() Rem SELECTS ALLSHAPES on the active sheet If ActiveSheet.CheckBox1.Value...

Excel: vba, pieterse, keycode

pieterse, keycode, vba: Well, you could use the keyup event of the textbox: Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) Dim sText As String sText = TextBox1.Text If sText Then sText = Left(sText, Len(sText)...

Excel: vba, digit id numbers, vba

digit id numbers, vba, cells: Something really similar to what you need, except for the part of the selected cell, can be achieved using autofilter (menu Data- Filter- Autofilter). If you need to do using vba, this is one way of achieving it: Sub HideRows() Dim myValue As Variant...

Excel: vba, b1 and b2, real time data

b1 and b2, real time data, running round in circles: M.Lock, I have no experience with dde links, so I do not know why they do not trigger a change event nor how to force them to do so. Here is how I use the Worksheet_Change event to keep a running sum of changing cells. Relating my code to your example,...

Excel: vba, excel formula, excel formulas

excel formula, excel formulas, vba code: Hi, i don t know of any special rules, use concatenate (&) if you need a long string. Also, you need to double the if it is included in the formula as you see below. Worksheets( Marks2 ).Range( B1 ).Formula= =IF(Marks!B1= ,0,Marks!B1) HTH,...

Excel: vba, target value, set target

target value, set target, cell b2: sub cyclethroughb2() dim counter as integer dim target as range for counter = 2 to 125 set target = worksheets( method2 ).cells(counter,2) target.formula = =(((Marks...etc target.calculate if target.value 100 then target.value...

Excel: vba, rng, input range

rng, input range, hth: There is no such built-in function to my knowledge. You can build one in VBA: Function TrimMean2(rng As Range, pct, Optional fromtop As Boolean) sumrng = Application.WorksheetFunction.Sum(rng) nrng = rng.Count dropn = Int(pct * nrng) sumdrop = 0 ...

Excel: vba, spreadsheet user, count cells

spreadsheet user, count cells, target: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngAbove As Range, checkCell As Range Dim thisCol As Integer thisCol = Target.Column If thisCol Range( e1 ).Column Then Exit Sub If thisCol Range( bz1 ).Column Then...

Excel: vba, spreadsheet user, count cells

spreadsheet user, count cells, target value: I m not sure exactly what you want, but is this anything like Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row 5 Then Exit Sub If Target.Column 5 Then Exit Sub Dim rng As Range Dim cl As Range Set rng...

Excel: vba, thanks in advance, cells

thanks in advance, cells: Something like this should do the trick. Place code in a normal module: Sub OpenFileAndRemoveLine() Dim oFound As Range Workbooks.Open Filename:= c:PathToDesktopHereYourFile.xls Switch off error handling in case string won t be found...

Excel: vba on cells already returning formla results, target range, conditional formatting

target range, conditional formatting, cell content: Neil, As I said, there is no Target argument in the Calculation event. So it would need to be modified. Assume you want to process the range A1:A10 Private Sub Worksheet_Calculate() Dim icolor As Integer for each cell in Range( A1:A10 ) ...

Excel: vba is converting text to values, type mismatch, zeroes

type mismatch, zeroes, 00000: The function in VBA is FORMAT, BUT I would suggest that including a line to format the cell as text to start with would be a safer bet (in fact, to be safe, do both) - I have found from experience that FORCING Excel to do what you want is often safer than...

Excel: vba is converting text to values, type mismatch, single quote

type mismatch, single quote, zeroes: You can do: Range( NewZip ).Value = Format( _ Range( OldZip).Value, 00000 ) However, essentially you have already done that, so I don t think that will be effective. Better would be Range( NewZip ).Value = & _ Range( OldZip).Text ...

Excel: vba error during sort, run time error 13 type mismatch, run time error

run time error 13 type mismatch, run time error, run time error 13: As I say, the elegent solution to not entering in blank columns is to set them as protected - which would exclude the user from entering data in those cells. On error resume next would give you the error trap. Error 13 is a type mismatch, and again there...

Excel: vba excel: usage of screenupdating & freezepanes, validation checks, vba excel

validation checks, vba excel, validation tests: Hi Grace, Regarding ScreenUpdating, the only effect this should have on the running of the macro is to make it faster, since updating and refreshing the screen with each change to a worksheet takes time. But turning screen updating off also is often done...

Excel: vba excel, open egg, line input

open egg, line input, variable name: Oops -- mistake -- try this... First try this: Sub KeepBigSmall() Application.Calculation=xlcalculationmanual For i=range( A65536 ).end(xlup).row to 1 step -1 if cells(i,1).value BIG and cells(i,1).Value SMALL then rows(i).Delete Next...

Excel: vba formula, type mismatch, excel function

type mismatch, excel function, b11: You re not using the SumProduct function correctly; refer to the Excel function help to review how this function works. Briefly: the SumProduct function takes as its arguments 2 or more RANGES, separated by commas. What you ve got in the parentheses following...

Excel: vba formula, f1 formula, ps version

f1 formula, ps version, vba code: Hi there, for the life of me, I don t understand why you would not just enter the formula into F1 and copy paste down for the rest of the 4 students. But, here is the VBA solution for you: Sub CycleThrough1() Dim Counter As Integer For Counter...

Excel: vba macro copy and arrange data from multiple sheets onto one, cell values, paste data

cell values, paste data, vba macro: David: Not sure where you have been searching (GOOGLE, I assume) but you will not find anything specific since the task you want the macro to perform is very specific. The things it needs to do, search for records, copy and paste data, etc. are general...

Excel: vba pasting problem, cell b2, nextcell

cell b2, nextcell, searh: Sorry, it just looks like a mess. Do it like this: Sub Del_Recs() Dim KillVal As Variant Dim EvalRange As Range, cell As Range, DelRange As Range Dim NextCell As Range, FirstCell As Boolean Set EvalRange = Range(Sheet1.Range(...

Excel: vba pivot table, pivottables, macro error

pivottables, macro error, pivot table: Excel seems to have an issue with changing the date field in code. If you simply record changing the date & play it back you get a macro error. This seems to work: Dim dt as String dt=date ActiveSheet.PivotTables( PivotTable2 ).PivotFields( _ DATe_OTV_PARTIJE_POTROSNE...

Excel: vba printing row, vba code, logo thanks

vba code, logo thanks, sheet1: Here s an example. If Print_Area is a named range on Sheet1, here s the vba code to print each row of the range on a separate page. Sub printRows() Dim wks As Worksheet Dim rngRow As Range Set wks = ThisWorkbook.Sheets( sheet1 ) For...

Excel: vba problem, excel worksheet, exact problem

excel worksheet, exact problem, text boxes: Nikhil, The code works just fine if the names in your code match the names on you form. You mention that you have three text boxes named name, dob and city, but your code calls them txtname, txtdob and txtcity. Make sure your code refers to the correct...

Excel: vba question about file name save, documents and settings, format date

documents and settings, format date, saveas: Sub AskAndSave() Untested if msgbox( Do you want save the file ,vbyesno+vbquestion)=vbyes then activeworkbook.save if msgbox( Do you want to save a copy on the desktop ,vbyesno+vbquestion)=vbyes then ActiveWorkbook.SaveAs Filename:= C:Documents...

Excel: vba for reading line by line in a table, reading line, result table

reading line, result table, camm: something like this Sub qt() Dim counter As Long, looper As Long For looper = 0 To 6 If Range( b1 ).Offset(looper, 0).Value = 2 Then Range( i1:L1 ).Offset(counter, 0).Value = Range( a1:d1 ).Offset(looper, 0).Value counter = counter + 1 End...

Excel: vba for reading line by line in a table, reading line, result table

reading line, result table, sh1: Hello Raj, To do this on the activesheet, Put this code in a general module and then execute it through Tools= Macro= Macros. Option Explicit Sub PrintSelectedItems() Dim sh As Worksheet, sh1 As Worksheet Dim rw As Long, rng As Range Dim cell...

Excel: vba for reading line by line in a table, dropdown arrow, reading line

dropdown arrow, reading line, result table: Why bother with VBA code, when the ability to do this is builtin to Excel. Click on any of those cells, use Data/Filter/Autofilter. Then select the little dropdown arrow in Col B and select 2. All you ll see are the rows with the 2 in col B. Print and you...

Excel: vba for reading line by line in a table, reading line, result table

reading line, result table, camm: Hi Raj, copy the following code into a module in your workbook and run the macro from the sheet where your table is located (active sheet). Change the consolidated sheet tanme where the result will be extracted (I used Sheet2 here): Sub Extractor() ...

Excel: vba record time, directory commands, vba project

directory commands, vba project, close application: HI :) The syntax error is in this line ; open (C:my documents ino.xls) The open command is incorrectly used and your directory commands seem imcomplete. I dont know your disc configuration but I suspect the line should read like this ; Workbooks.Open...

Excel: vba task, a3, a1

a3, a1: Hi there, you don t really need VBA to do this. You could enter in A3: =IF(ABS(100-A1) =ABS(100-A2),1,2) If you really want VBA, here it is: Function MyFunction(val1, val2) Const x = 100 a = Abs(x - val1) b = Abs(x - val2)...

Excel: vba for word merge, vba, data source

vba, data source, microsoft word: Sorry, I don t just write macros for people on this site who need one, seem to expect I am supposed to, or who ask me to do so. 99% of them are always more involved that you realize and require a lot of typing here to get all of the specific details of where...

Excel: vba, real time data, calculation mode

real time data, calculation mode, excel spreadsheet: m.lock Not sure I ve got this exact, but first things first. I would recommend that you set the Calculation mode to Manual. That allows the following code to set itself up. The following code needs to be placed in the Sheet object that you are working in...

Excel: I've used Excel for many years,...

Sarah I don t know of a way of sorting that will sort all of the sheets simultaneously. It is possible to create a macro that will accomplish the task, but it might be easier for you to put the entire list on one sheet so that when you sort you will get...

Excel: I've been workin' on the Railroad, steel coils, s yard

steel coils, s yard, steel coil: I think Excel would be ideal for your situation. The only downside is there would probably be quite a bit of time consumed in setting up your database. I have placed a sample database here; http://home.swbell.net/nate-sus/excel/steel.xls I made several...

Excel: Cannot view embedded object in Excel 2003, visio diagram, print preview

visio diagram, print preview, excel 2000: Paul No I haven t had the problem you are experiencing, but I don t deal very often with imbedded objects. Perhaps I ve been lucky. I d reask the question of other rallexperts persons and maybe they have had similar experiences and have solved the problem....

Excel: visual basis programming for option buttons in excel, control toolbox, option buttons

control toolbox, option buttons, exit design: From main menu, choose View, Toolbars, Control Toolbox. From the Control Toolbox, place 3 option buttons on your Main sheet. These option buttons will automatically be given the default names OptionButton1, OptionButton2, and OptionButton3. Double-click on...

Excel: vLOOK UP, vlookup formula, daily basis

vlookup formula, daily basis, error results: VENKAT I will attempt to answer although I do not totally understand your question. you can create a formula that will lookup many rows, and if an error results then it will not put #N/A, but will put another symbol or a space or a zero, etc. the formula...

Excel: vLOOKUP, recording keystrokes, vlookup formula

recording keystrokes, vlookup formula, macro code: Hi Venkat, I m sure there are other ways, but the way I get around this is (in my macro) to copy a column of the trades sheet over to the column where I will put the vlookup formula in and then use the copy shift end down arrow (macro recording keystrokes)...

Excel: vLookup Vba problem, vb skills, range c

vb skills, range c, macro code: Hi Rimmon, It is very clear that you know VBA programming. So I will be brief in my answer.. Probably you can try something like this- For i = 2 to 25 Cells(i,23).Value = Cells(i,17).Value - WorksheetFunction.VLookup(.Cells(2,13), .Range( C:H ), 5, False)...

Excel: vlook up formula question, vlookup formula, column c

vlookup formula, column c, blank cell: Please bear with me while I walk through your formula: =IF(ISERROR(VLOOKUP(B2,LAData!$C$1:$D$351,2,0)), ,VLOOKUP(B2,LAData!$C$1:$D$351,2,0)) The zero you see here...$D$351,2,0, isn t actually the zero that is returning from an error. It is actually...

Excel: vlookup, blank cells, macro button

blank cells, macro button, paste method: Steve, If I understand your dialogue, yes this can be done with a few lines of code. You may have to adapt my code to your actual spreadsheet situation, but it should give you a general idea and framework. I m assuming your values are in column A and...

Excel: vlookup, vlookup, ch st

vlookup, ch st, index code: =IF(OR(C5= DH ,D5= CH ,G5= ST ),VLOOKUP( CH ,Code,3,True),VLOOKUP( PH ,Co de,3,True)) Will return either 20 or 10 If you want the max of DH, CH, ST and they will alway be higher than PT or PH, then =IF(OR(C5= DH ,D5= CH ,G5= ST ),Max(Index(Code,0,3)),VLOOKUP(...

Excel: vlookup, array formula, cell c5

array formula, cell c5, code1: in each case (Code1=C5) returns a value of true or false. for the multiple cells in code1, it returns an array of true or false - value for each row. When this value is multiplied against the corresponding value in the column defined for code3 (same row),...

Excel: vlookup, colum, spreadsheet

colum, spreadsheet, email: oh now i kind of see what you want to do. i just want a sample data and then I can help in a spreadsheet. makes it easier than trying to explain in writing. my email jimmytrust@hotmail.com if you sent some sample data with what you need to do, it...

Excel: vlookup, sort feature, lookup functions

sort feature, lookup functions, sort function: makeymv Can you better describe to me what your data looks like? In your question you refer to a row--does the data you are looking for run across all columns in the row or is it limited to a few columns in the row. Could the lookup data (I.E. the...

Excel: vlookup, douglas m smith, vlookup formula

douglas m smith, vlookup formula, column letter: Hi Myles, 1) This formula is doing a comparison of two looked up values to see which one to use. The value in cell G5 is being looked up on the Tariff-Origins sheet in cells D6:D47. When values returned will be from cells K6:K47 (8th column) of L6:L47...

Excel: if vlookup, vlookup, logical test

vlookup, logical test, cell reference: www.aidanheritage.co.uk/Excel has a full explanation of Vlookup, but with the table as it stands you won t be able to do it - you need the cost of item as a unique value (so replace 100 with 0, 100-150 with 100, 151-200 with 151 etc. The logical test will...

Excel: vlookup, cell reference, array

cell reference, array: As you are using a name for the table, you can use the funcion INDIRECT to refer to the range it represent. In this case, you can use: INDIRECT(TRIM(G1)) in the table_array parameter. If the trimmed value of G1 is the name of the table, the vlookup should...

Excel: vlookup, cell a1, pls reply

cell a1, pls reply, a2: I presume you already know how to use VLOOKUP to extract the first item in a row. Nevertheless, here is a basic example for us to start with: Lets presume the lookup value is in cell A1 and the matching value is going to be somewhere in cells A2 through...

Excel: how to do this vlookup, vlookup function, circular reference

vlookup function, circular reference, sheet names: sub LookupData() change the b9 to refer to the cell containing the formula. Change sheet6 to the correct sheet name Dim rng as Range, rng1 as Range, tot as Double Dim i as Long, res as Variant, v as Variant set rng = worksheets( sheet6 ).Range(...

Excel: vlookup 2 files, array formula, candidate solution

array formula, candidate solution, macro function: June, sure it is possible. You don t give the type of specific details necessary to provide even a candidate solution, but for a discussion of an approach see http://www.fabalou.com/Excel/vlookupmultiple.asp An array formula technique using index and...

Excel: vlookup, blank cells, macro button

blank cells, macro button, excel spreadsheet: STEVE Couple of ways you can solve the problem First you could insert another column and use another identifier for the second set of numbers I.E. use X for the first identifier and Y for the second obviously this could go on and on depending on the...

Excel: vlookup, vlookup formula, learning excel

vlookup formula, learning excel, blank cell: you would need a separate formula in each of C7 to C10 C7: =Vlookup($B$5, Product Lookup !A:F,2,False) Type C8: =Vlookup($B$5, Product Lookup !A:F,3,False) Model C9: =Vlookup($B$5, Product Lookup !A:F,4,False) Shipping C10: =Vlookup($B$5, Product...

Excel: vlookup, vba code, string result

vba code, string result, b9: No. Builtin Worksheet formulas can not access the formula you have in a cell - so there is no way to do what you ask without writing a User Defined Function using VBA. If you don t know VBA, then the following might come across as unclear. So if you...

Excel: vlookup, c data, hyperlink

c data, hyperlink, pdf files: If you know which folder are the files, you can use the HYPERLINK formula for that, something like: =HYPERLINK( C:Data & A1,A1) This formula doesn t check if the file exists or not, it just creates the link to the location entered as the first parameter....

Excel: vlookup, blank cells, macro button

blank cells, macro button, paste method: Congratulations on that clever solution, Steve. Adding the X is a great way to solve your problem. And it helped me get a clearer picture of what s going on in your worksheet. (AllExperts can be hard sometimes because you can t see what s going on, and you...

Excel: vlookup, copy switch, excel spreadsheet

copy switch, excel spreadsheet, thanks in advance: Yes, there s a simple way, if I understand what you re trying to do, and you don t need any button... Simply select all the cells in the first column you want to transfer over (ctrl/clicking them), and when you have the ones you want, simply use edit/copy,...

Excel: vlookup, check register, registers

check register, registers, checks: Steve I ve read your question a bunch of times and I m confused as to exactly what you want and how your data is set up. Many times when we set up files we discover later that it would have been easier to have set them up a different way. And it sounds...

Excel: vlookup, vlookup function, excel 2000

vlookup function, excel 2000, a2: Hi Richard, The CONCATENATE function will combine both values. Or using the & you can combine values such as =A1&A2 would combine both values. If you send me an example workbook of the weekday function and an example of the VLOOKUP function I could...

Excel: vlookup alphabetical order, optional parameter, alphabetical order

optional parameter, alphabetical order, thanks in advance: Their is an optional parameter in the VLOOKUP which if set to FALSE will allow the lookup to function regardless of alpha order. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) It s the range_lookup parameter that you want to set to FALSE....

Excel: vlookup alphabetical order, false hope, ascending order

false hope, ascending order, alphabetical order: Chris There isn t a good alternative to the ordering of the lookup column in ascending order. The only suggestion that I can make is that you consider copying the data to another worksheet, for lookup purposes, and sort it alphabetically, and maintain...

Excel: vlookup alphabetical order, vlookup function, optional argument

vlookup function, optional argument, thanks in advance: Yes. Use the fourth optional argument of the VLOOKUP function, which is TRUE or FALSE. TRUE is assumed if you don t type the fourth optional argument. You need to use FALSE, which looks for an exact match and not where the item being looked falls within...

Excel: vlookup alphabetical order, sort buttons, sort keys

sort buttons, sort keys, dropdowns: Hi Chris, I m not sure I understand your question. Is the problem that the VLOOKUP wants the data in col A to be in alphabetical order, but for some other reason you need the data to be in some other order? Or is the problem that when you sort column...

Excel: vlookup commission plans, microsoft excel help, commission splits

microsoft excel help, commission splits, real estate sales: Hi Bob, Firstly my feel is VLOOKUP will not be correct one to be used. We may need to use nested IF functions. But, one query is I do not understand 7% off the top . Secondly, for a business of (say) 45000$, will amount upto 40000 be calculated at 50% or...

Excel: vlookup and if condition, vlookup, sheet document

vlookup, sheet document, 22222: COUNTIF is a standard excel function which will COUNT the entries if they meet the criteria - so it s countif(range,criteria) - this will give you a zero or non zero figure, which means you can use it to determine which sheet to return the information from....

Excel: vlookup and creating macros to copy and paste, cell a1, table a1

cell a1, table a1, column table: You don t need a macro and even if you did I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore...

Excel: vlookup with dates, how to use vlookup, format cells

how to use vlookup, format cells, number ie: Select the column, use Format/Cells, select Custom, enter mmddyyyy. However, they re still Excel dates, only FORMATTED as mmddyyyy. the Value 9/26/2006, although it LOOKS like 09262006, it s the actual value 38986, and you still need the solution I previously...

Excel: vlookup and/or if formula, book1, vlookup

book1, vlookup, windows 2000 professional: In A2 of spreadsheet2 (assuming A1 has a header), enter: =INDEX([Book1]Sheet1!$A:$A,MATCH($C2,[Book1]Sheet1!$C:$C,0)) (Change [book1] to the name of the real spreadsheet, like [MyFile.xls]) the [ & ] are required In B2, enter: =INDEX([Book1]Sheet1!$B:$B,MATCH($C2,[Book1]Sheet1!$C:$C,0))...

Excel: vlookup and/or if formula, claim numbers, vlookup

claim numbers, vlookup, daily basis: Not sure about that error message Make sure you have changed ALL of the cell references, including adjacent sheet names if applicable, correctly. My formula assumed certain ranges - you needed to have made the appropriate substitutions to fit your need...

Excel: vlookup functions, cell a2, empty cells

cell a2, empty cells, a1 index: Hi, I think the following steps will help you: 1. Open a new workbook. 2. Type the following in Sheet2: Cell A1= 1111 Cell A2= Input the key in A1 (1111, 1112, etc) Type the formula as shown below: Cell A4= =IF(ISERROR(SMALL(INDIRECT(...

Excel: vlookup (Highlight Color), highlight colors, conditional formatting

highlight colors, conditional formatting, custom formulas: Bad news I am afraid Prem (if I correctly understand what you want to do). If you want the vlookup formula to produce the same color as the cell in column D or C, then I am afraid there is no built in support for it. First, formulas can t really...

Excel: vlookup and images, vlookup function, community newsgroups

vlookup function, community newsgroups, learning curve: If you re new to macros, you have a pretty hefty learning curve ahead and I d suggest you visit http://www.microsoft.com/smallbusiness/community/newsgroups/dgbrowser/default.mspx?dg=microsoft.public.excel.misc&lang=en&cr=US&r=4636078e-e866-42e2-871c-8851fa889c8c...

Excel: vlookup link between sheets, current sheet, arguement

current sheet, arguement, aidan: It s perfectly possible - it s even possible across workbooks! =vlookup(What,Where,Which,Logical) is the format and I suspect is what you are using - so something like =VLOOKUP(A1,Sheet1!$A$1:$B$3,2,FALSE) will check the value of A1 on the current...

Excel: vlookup and na#, blank space, c10

blank space, c10, cheers: Mark I think that you need to wrap the Vlookup in an IF statement that uses IfError to see if the the result of the Vlookup creates an error. So it would be (If(IsError(Vlookup()),0,Vlookup()) or with data: =IF(ISERROR(VLOOKUP(0.7,A2:C10,3,FALSE)),0,VLOOKUP(0.7,A2:C10,3,FALSE))...

Excel: vlookup returns text string, customer code, blank area

customer code, blank area, excel workbook: Hi Isabelle, I found a user-defined function on the web while looking for other stuff. This will return cell comments directly into a cell. What you want to do is copy and paste the following function. It is easy enough to do. Here s how Select the following...

Excel: vlookup use, vlookup formula, excel function

vlookup formula, excel function, vlookup function in excel: Hi Rakesh, The Vlookup function is extremely useful and very common, so you should definitely become comfortable with it. It would be better if you had a particular example to work with. I ll try to give you a simple one. Suppose you had a store...

Excel: vlookup not working on some columns, column text, excel spreadsheet

column text, excel spreadsheet, numeric character: Once the data is in the cell, formatting it alone will not change what is stored there. I think you just need to check both ways (text or number) =if(iserror(vlookup(Text(A1, general ),Table1,2,False)),Vlookup(A1*1,Table1,2,False),Vlookup(Text(A1, general...

Excel: vlookup not working, vlookup formula, vlookup function

vlookup formula, vlookup function, a2: Hi Jay, The most likely answer is the cell that contains the formula is formatted as Text . To check this. Select the cell containing the VLOOKUP formula then go to the Excel menu and select Format then Cells... then the Number tab then this is...

Excel: vlookup, excel spreadsheet, several thousand

excel spreadsheet, several thousand, thanks in advance: Hello Steve, If I understand your question correctly, you would like to copy any rows selected on your database worksheet to your destination worksheet when you click a button that is located on the database worksheet. The copied rows should be added...

Excel: vlookup0, menu cells, format cells

menu cells, format cells, swbell: I m certain I do not understand the problem. Vlookup doesn t copy formats, it only copies a value. As such, you would just need to use the formatting options in Excel to ensure the actual (click format- cells from the menu) cells have the same format. ...

Excel: vlookups, vlookup, exact requirement

vlookup, exact requirement, cell b1: Hi Recardo, To begin with, let me understand the exact requirement. comparing tables will not make much sense to me. Are you looking for comparison of Column B values in two tables with respect to Column A? For example, Values of 3 in two tables donot...

Excel: vlookups for multiple sets of data, rep name, vlookup

rep name, vlookup, colum: Assuming that separate tab with the rep name to look up is called Sheet2, and that the state/Semester/Repname is in columns A:C on that tab, you can use this formula in sheet1 (say cell M2): =INDEX(Sheet2!C:C,MATCH(J2&G2,Sheet2!A$1:A$3000&B$1:B$3000,0)) ...

Excel: WANT TO KNOW WHAT FORMULA I HAVE TO APPLY, format cells, ssn

format cells, ssn, 000000: Hi, Akber. :) Two things. One: Trying to do this with a formula is not efficient. Instead, do it with a format. Select your cells, then Format-- Cells, choose a custom format and type 000-00-000000 into the cell. (It might be ###-##-######, I am not...

Excel: Want to make mandatory cells filled by user, patient days, number rows

patient days, number rows, counta: A few minor changes -- Right-click the sheet tab, select View Code, paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 and target.Row 76 And Target.Row 133 Then If Target.Offset(0, -2) = Then MsgBox Please...

Excel: Warehouse spread Sheet, appications, mirosoft

appications, mirosoft, spread sheet: Mike There are few available that I can send you, I will send them to your weekday email address this week, you may be able to mix n match or use them as they are, if you have any questions regarding them don t hesitate to get back to me. Richar...

Excel: Warehouse spread sheet, spread sheet, allexperts

spread sheet, allexperts, weekdays: Mike Don t know of a spreadsheet that, already exists, that has all of the items that you need. Looks like you ll have to start from scratch unless someone else has one. Check with the other allexperts people. Sorry I couldn t be of more help. Richar...

Excel: Warning message, csv document, csv file

csv document, csv file, excel tips tricks: Before closing the file: Application.DisplayAlerts = False should do it. By the way -- I m leading a FREE 1-hour online Webinar on Excel Tips & Tricks. September 28, 4-5PM(est), and October 12, Oct 19, and Nov 30. If interested, go to http://www.iil.com,...

Excel: Watermarks, gridlines, pale grey

gridlines, pale grey, watermark: Try using the drawing toolbar and inserting a textbox - set this to NO FILL and NO LINES - I would also do semi-transparent just to be safe - this should resolve the gridlines issue. You could ALSO look to setting actual borders on the cells you are interested...

Excel: WEEK NUMBER, 8th august, week number

8th august, week number: Hello Hidajadi, You can use the formula =INT(DAY(B4)/7+0.99) to yield the week number, where cell B4 contains the date. This defines the weeks of the month as starting on the first day of the month, regardless of what day of the week this day is....

Excel: Web Query VBA time stamp, hh mm ss, web query

hh mm ss, web query, rimmon: Hi Rimmon How are you? I have not checked your question completely, but I think that it would be useful for you to have a look at the following changes: Private Sub Worksheet_Change(ByVal target As Range) Application.EnableEvents = True Dim...

Excel: Web Query, vlookup excel, web query

vlookup excel, web query, data source: Possibly a formatting problem - if the web query brings in text like result rather than result then a normal vlookup will fail - if this is the case, put a formula to the left of the webquery =Trim(CellRef) which would loose the rogue spaces, then use...

Excel: Web table to excel, web page tables, table conversion

web page tables, table conversion, web table: Sherlock, I would probably need the url to see what s entailed. I m headed out for the rest of the week, though, so I won t get to this at all until next week. Generally, my experience with web pages is limmited, but what I do is save the page as a txt...

Excel: Week days, life span, time dave

life span, time dave, fri: I am not sure what a big ask is, but the logic to the formula approach is: In the first cell determine if the start date is within 5 days of the first day of the month and is a weekday. In the subsequent cells simply add 5 to the date in the previous...

Excel: Weekly History, target value, target address

target value, target address, sheet tab: Not sure what Fred represents, but here s an outline of what you can do: Right-click the sheet tab of the sheet containing the cell you want to keep track of, select View Code, and, assuming this cell is in D6, for example, enter this code: Private Sub...

Excel: Weekly Status Report, weekend period, column c

weekend period, column c, ogilvy: Joni, No it wouldn t. I stated what my assumptions were since you didn t specify anything. nor have you this time either. I won t guess what you want as a product. Do you want to calculate the number of hours in each row that fall within your specified...

Excel: Weight Training Spreadsheet, left hand corner, curl bar

left hand corner, curl bar, cell a2: Steve - The simplest way to have a word represent a number is to create a Named Range. A Named Range can stand for a whole spreadsheet, or as in your case, one cell. Lets say on Sheet 2 you create a table that has the numbers 35 in cell A1, 10 in cell...

Excel: Weighted Average, calculating weighted averages, douglas m smith

calculating weighted averages, douglas m smith, suggestion submission: Hi again, No, there s no specific function for calculating weighted averages. You have to use other functions to build your own as shown in the example. It s like using SUMIF and COUNTIF to come up with the result of AVERAGEIF, because there isn t a built-in...

Excel: Weighting factors., excel tips tricks, avrage

excel tips tricks, avrage, yellow orange: If the weighting factors are in A1:C1, then you can use something like =(D1*A1+D2*B1+D3*C1)/SUM(A1:C1) if I understand you correctly I m leading a FREE 1-hour online Webinar on Excel Tips & Tricks. Jan 27 from 4-5PM. If interested, go to http://www.iil.com,...

Excel: Weighting grades to get an average, weighted average formula, averaging grades

weighted average formula, averaging grades, semester grade: Hi Joel, Excel does not have a weighted function. You can use the following formula to calculate a weighted average. If you had grades in cells A1,B1,C1,D1,E1 and cooresponding weights in cells A2,B2,C2,D2,E2 =SUMPRODUCT(A1:E1,A2:E2)/SUM(A2:E2) ...

Excel: Well, I thought I'd do the..., address lines, column c

address lines, column c, address data: Well if I can actually direct you on how to structure it, I d recommend putting the addresses in different columns of the same row as the name. Then we don t have sorting problems. The way you ve got it now, it s not hard for me to do it... but explaining...

Excel: Wide paper print in Excel, custom button, hp designjet

custom button, hp designjet, printer options: John One way is to get a printer driver that has 18 paper. Something like the hp designjet and use that for your document or depending on your printer options you should have a custom button that will allow you to put in your own dimensions. This can...

Excel: Wildcard in IF function, nba scores, quotation marks

nba scores, quotation marks, home game: Hi Brendan, If you have a cell that will either start with a @ sign or not then you could use a formula like this. =IF(LEFT(A1,1)= @ , Away game , Home game ) You can use the Search function if you want. =IF((SEARCH( @ ,B2,1)), AWAY , HOME ) One...

Excel: Window resizing, aidan, lounge area

aidan, lounge area: It s not happening when I do it - which makes it a little odd - POSSIBLY it s a version thing (though I admit I don t normally use workbook protection, just protecting the sheets is enough for my purposes!) - possibly worth asking the question in the lounge...

Excel: Windows 2000, exact data, backup copy

exact data, backup copy, bad news: Hey Sharon, I m not really sure how the M32 file became overwritten with data from NI 184, but unfortunately, there is no way to recover an older version of a file that was saved over, unless you had already made a backup copy :( Sorry for the bad news!...

Excel: Windows 2000 Excel, exact data, backup copy

exact data, backup copy, copy and paste: It sounds as though at some point you have accidentally used the File SaveAs dialog box and thus overwritten the file - I am not aware of anything else that can cause this. The only way I know of to restore the original data is to restore the file from your...

Excel: Windows 2000, tmp files, oringinal

tmp files, oringinal, exact data: Sharon It looks like you saved the original file with the NI when you closed, that is about the only explanation on how this can occur. The only way to recover this is by finding a temp file that maybe will have what you need, the temp file for excel looks...

Excel: Windows on Task bar, warm greetings, excel sheet

warm greetings, excel sheet, task bar: Venkatesh I m not much help, I tried to duplicate the problem and I cannot. Have you checked the file that you are sharing to see if it has hidden checked in the window/view options? Otherwise I can t find a solution. Sorry i couldn t be of more...

Excel: WinZipped an Excel File and most Formulas disapeared, file xml, structure data

file xml, structure data, absolute value: Chris - I haven t heard of this, either. Save the Excel file as an XML file. XML is a smaller file size than .xls, and still has the capability to contain structure, data and formulae. Plus when it gets to the receiving end, you can open the file...

Excel: Wisdom On How To Grow Deeper, e mail address, lotus 123

e mail address, lotus 123, database knowledge: Hi, Ken, it was nice to hear from you. Thanks for the compliment. My book has been out of print for a while -- when and where did you get your copy? There are several places to get some good Excel tips on the web. One of my favorites is Aaron Blood s site...

Excel: Wishing to Automate External Data Retrieval, text import wizard, visual feedback

text import wizard, visual feedback, kind assistance: Sarah, to allow file selection, use fname = Application.GetOpenFileName You can use an inputbox function to prompt for the start row although it won t provide the visual feedback. If the visual feedback is essential, then you would have to build...

Excel: WORK ROTA SHEET, hlookup, horizontal row

hlookup, horizontal row, peoples names: It SOUNDS as though it s an HLOOKUP - where it looks up a date in a horizontal row and returns information from below it? It s not easy to relate to the question without seeing a file though - is it possible to send a sample file (my email is aidan.heritage@virgin.net...

Excel: WORKING WITH DOLLARS AND CENTS, excel spread sheet, decimal place

excel spread sheet, decimal place, decimal places: Todd I m not quite sure what your input is, but apparently the input is more decimals than you want. You can eliminate the decimals with formatting that shows only the number of decimals that you want, but that doesn t eliminate the amounts that are there...

Excel: WORKSHEET NAME

Ramon I am not sure that I correctly understand the question, and I don t want to be flippant but as far as I know the way to display the name in a cell is simply to type it into the cell. If you need to link a formula from one sheet to another the way...

Excel: Wonderfull Lists..., coordinates

coordinates: Eric If you are adding the formulas to the data by writing the first formula, then using Ctrl to insert the same formula in the next row, you are out of luck if you remove the formula above. The utility of that shortcut is pretty minor, since it simply...

Excel: Word to Excel, word document format, array array

word document format, array array, text paragraphs: Hi again Diane, Yes, in general it is easier to copy multiple paragraphs from Word documents to Excel if the Word data are in the format of either Word Tables or Forms. This is because these formats eliminate the need for parsing the text to find out where...

Excel: Word forms to xl database update, word forms, database fields

word forms, database fields, loop through: I haven t come across it, but I see no reason why not - if the document is laid out in table form, it MAY be easier just to copy and paste to Excel and do it this way rather than use VBA, but VBA can certainly go through the fields/cells and (by using a for..next...

Excel: Word Wrap, compaq nc6000, word wrap

compaq nc6000, word wrap, excel 2002: The only ways to do what you want is to EITHER embed a word document in the spreadsheet or to use a text box to contain the text - Excel does indeed have a limit to the size of the text in a cell and it is in the design of the software, so I m not aware of...

Excel: Wordart and printing margins, printing margins, header footer

printing margins, header footer, wordart: There is no real side header/footer -- your best bet would be to make room on the side of the worksheet for your wordart and mark the column as part of your Print_Titles so it ll print on every sheet. To get real wordart into a header you d have to save the...

Excel: Work out 24 hour notice period, chip pearson, abandoned vehicles

chip pearson, abandoned vehicles, notice period: You can start out at this page on Chip Pearson s site: http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours for an overview of how dates and times are stored: http://www.cpearson.com/excel/datetime.htm for marking, you can use conditional formatting....

Excel: Work Sheet Tab Size, sheet tabs, appearance tab

sheet tabs, appearance tab, program font: Patrick The default settings for the window view in Excel are determined by the Windows operating system and Excel. Minimize all applications and right-click the desktop. Select Properties, and select the Appearance tab. Change the font size in the...

Excel: Work sheet question, input box, beter answer

input box, beter answer, ck: Christopher I don t know of a name ck that will let you test for existing worksheet names prior to your trying to name the worksheet the same as an existing one. Of course when you try to use an existing worksheet name in the same workbook you will get...

Excel: Workbook change, wn, window menu

wn, window menu, aiden: The code you have given looks fine at face value, BUT I suspect that you have misunderstood when this will fire - if you use the WINDOW menu in Excel to switch to another workbook, the deactivate event will be triggered, and similarly using this to switch...

Excel: Workbook nolink when copying, change source, shortcut menu

change source, shortcut menu, microsoft excel: Do it as you currently do, but as soon as you have the copied sheet, use Edit/Links, click on the unwanted link, click Change Source, and point the link to the same workbook as you now have (it needs to be saved first). All the links will be done at the...

Excel: Workbook reference, dialog box, blank sheet

dialog box, blank sheet, xla: It sounds like one of your workbooks is hidden or maybe it is an XLA/Addin. Another open workbook has a reference to this workbook in Tools= References in the VBE. As stated, as long as that reference exists and the other workbook is open, this workbook...

Excel: Workbook structure, sales invoices, sheet test

sales invoices, sheet test, true range: :...Also the countif formula provides a result of 2 if there are two lines for that supplier.... Again, WHAT countif formula? I did not see that in your code. Again, remember I can not see what you see and ALL I know about your project is ONLY what...

Excel: Workbook structure, sales invoices, countif

sales invoices, countif, blank sheets: I would create an extra column which contains the following formula (example shown is for row 1) =COUNTIF(S1:$S$8,S1) NOTE that the first cell reference is relative, the second absolute. Dragging this down will give you a count of that supplier, so...

Excel: Workbook Tab Color, sheet names, data validation

sheet names, data validation, issue id: Aaron, The tab color must be changed by manually changing it or by using a macro. You second question would best be accomplished with a macro unless you mean you already have a list of sheet names on the first main sheet. In that case, on that sheet...

Excel: Workbook Unlink when copying, change source, shortcut menu

change source, shortcut menu, microsoft excel: I suggest that you go into Edit/Links and update them there. For each link that you want to sever, follow these steps: 1) In the Edit Links box, click Change Source. 2) Navigate your way to the *current* workbook, so that the link is redirected to the...

Excel: Workbook, hyperlinks, hyperlink

hyperlinks, hyperlink, alex: Hello Alex, I m at a bit of a loss. I thought all Excel files were Workbooks. Are you using a very early version of Excel? I would assume, too, that if you move data from one Excel file to another and it s the same version, that no features will be lost....

Excel: Workbooks..., ok alot, entry screens

ok alot, entry screens, wks: Hi Angie, You can create the same type of formula to link workbooks as you do to link the worksheets. Open both files. Put your cursor in the second one (the destination file) and choose the first cell to link. Press the = sign and then nativate to the...

Excel: Workbooks, sensible answer, aidan

sensible answer, aidan, many thanks: Not quite enough information for a sensible answer to your problem - so I ll give you some alternatives Copy and Paste - NOTE that by using paste special you can paste the VALUES if the original data was formulas. Cell Links - in the new book type =...

Excel: Workbook_Open macro, open macro, excel tools

open macro, excel tools, vbe: Periwan, Actually, what I would recommend is that you edit the code to run only when the filename is the name of the original file, not the subsequent files. You can also disable macros in Excel, but that would prevent all macros from running, and I don...

Excel: Working on access restriced login screen, spread sheet, macros

spread sheet, macros, ep: Hi Ryan, Part of the problem lies around you testing vbOK. This is VB constant not, a returned value. You would normally use it in circumstances such as this If MsgBox( Is this OK? ,vbOKCancel) = vbOK Then not as you have used it. I am not...

Excel: Working With Dates in Excel 2003 Pivot Table, pivot table, defect tracker

pivot table, defect tracker, excel spreadsheet: Sure, here is an example: Suppose ProcessMovement is in cell A1 and LocalClient is in cell B1. The following formula would merge the two (place in cell D5, or anywhere) =CONCATENATE(A1, - ,B1) The result of the above formula would be ProcessMovement-LocalClient...

Excel: Working with Excel, target value, word application

target value, word application, private function: Michael This one is a bit touchy, in that errors can create problems that are difficult to diagnose. The code needs to be placed in 2 separate places, so make sure you get these right. First off, the code will be set to run each time a selection is made...

Excel: Working with Formulas, two pennies, decimals

two pennies, decimals, spreadsheets: Common problem unless I m very much mistaken - the problem isn t with the autosum but with the OTHER formulas - the cells are probably formatted to DISPLAY to a certain number of decimals, but the result itself is NOT rounded - so although a cell may display...

Excel: Working with a huge list, client names, lookup functions

client names, lookup functions, huge list: Sanjay This isn t a real simple solution but--- I would use the LOOKUP function to determine if the name already appears in sheet 2. If it does appear I would put a marker in a cell next to the name. I would then use the IF function to copy all names...

Excel: Working with a Macro protected Book., aproach, macros

aproach, macros, xls: What I would suggest is to make the macro in a third workbook, and from there open the original workbook, create a new one, and move the sheets from the original to the new. You won t have issues with the macro protection on the original workbook, as the...

Excel: Working with Macros in Excel, macros in excel, false selection

macros in excel, false selection, keyboard shortcut: Kevin, you simply need to copy the FTD code into this macro, it was near the bottom of the code set. Sub ProcessFiles(FileName As String) ActiveWindow.SmallScroll Down:=-6 Columns( AC:AF ).Select Selection.Locked = False Selection.FormulaHidden...

Excel: Working with Macros, thanks for your help, microsoft

thanks for your help, microsoft: Hi Kevin, I have to run to do something tonight. I will respond in the morning. But for right Now I wanted to make sure I mentioned Personal.xls workbook or make sure you know about it. See link below. This will help to run in all open workbooks. I understand...

Excel: Working with text files, routine application, excel sheet

routine application, excel sheet, excel 97: Yes, unfortunately, sometimes excel Macros just stop! You can get around this by using OnTime: Sub OpenFirst() Application.OnTime now, More workbooks.open yourfileGoesHere End Sub Sub More() this will kick in after the file is opened now...

Excel: Working with time values in Excel, time hh, time sets

time hh, time sets, time values: Dave, I have a solution for you. Shoot me an email at xeon REMOVE flux@comcast.net (do not include the REMOVE in the address line) and I will send you a file. Things to keep in mind: 1) you are making this too hard on yourself in my opinion. I think...

Excel: WorkSheets, run time error, subscript out of range

run time error, subscript out of range, metrics: Ken, The reason you are getting the error is the Metrics is not enclosed. Even then the above formula will not work. Try this Worksheets( Metrics ).Cells(5, 5).Formula = =COUNTIF(ETOSpreadsheet!C:C, 2007_01 ) - COUNTIF(ETOSpreadsheet!C1:C47, 2007_01...

Excel: WorkSheets, excel spreadsheet, buttom

excel spreadsheet, buttom, dialog box: Ken, If you protect the worksheet by Clicking on the Tools menu, selecting Protection and then Protect Sheet a dialog box will open. Uncheck the first two boxes that read Select locked cells & Select unlocked cells. You can also add a password if you...

Excel: WorkSheets and coding, run time error, subscript out of range

run time error, subscript out of range, sheet names: Your code should do what you say. The error you are getting is because you either don t have a sheet named Metrics_Summary or you don t have a sheet named ETO . You may think you do, but I suspect that one of them has a leading or trailing space on the...

Excel: WorkSheets and coding, array formula, cells

array formula, cells, countif function: You can simply enter this array formula in E5 =SUM(IF(Worksheets( ETO )!F:F= NEW CUSTOM , IF(Worksheets( ETO )!AV:AV= 2007_01 ),1,0),0)) Note that this is an array formula = so instead of pressing enter after typing this formula, you need to press Ctrl+shift+Enter...

Excel: WorkSheets and coding, run time error, subscript out of range

run time error, subscript out of range, valid entries: Seem like your variable Metrics or ETOSpreadSheet do not contain valid entries. If these are the actual names of the sheets, you need quotes around them: Worksheets( Metrics ).Cells(5, 5) = Application.CountIf(Worksheets( ETOSpreadSheet ).Cells(3, 48), 2007_01...

Excel: WorkSheets, array formula, cell a2

array formula, cell a2, sumif: Ken, Hope you are doing well. To recap 1. Data4Anal has 3 columns. Col 1 has the dates. Col 3 has the data that to translate. Suppose there is data from row2 to 200 2. Metrics Summary has output data - 2 columns. Cells A2:A10 has the dates (or is it years)...

Excel: Worksheet Arrangement, sheet tab, tile

sheet tab, tile, exercise: Here s a little exercise to teach you how: Open a new, blank workbook. Hit Window-- Arrange, Tiled. Now, hit Window-- New. Click on Sheet 2 s sheet tab. Now, hit Window-- New. Click on Sheet 3 s sheet tab. Now, hit Window-- Arrange, Tiled. Don...

Excel: Worksheet comparison, consecutive columns, value application

consecutive columns, value application, vba code: This code doesn t present exactly what you are asking, but it will search which elements on sheet1 doesn t have the same number of elements on sheet2, and it will return the element itself, the count on sheet1, and the count on sheet2, on three consecutive...

Excel: Worksheet existence, several ways, existence

several ways, existence: IF I understand what you are asking then there are several ways to know. One way is: Dim SHCount As Byte Dim Sh_Exists As Boolean Dim X As Byte Sh_Exists = False SH_Count = Sheets.Count For X = 1 To SHCount If Sheets(X).Name = Sheetx ...

Excel: Worksheet formulas in a loop?, sum formula, cell a1

sum formula, cell a1, sheet1: No, the sums still work, but if you use a workbook with lots of calculations and simply enter a new value somewhere, the whole workbook recalculates. By turning calculation to manual temporarily, the workbook stops calculating with each change (putting in...

Excel: Worksheet Limit, proposal package, tabloid size

proposal package, tabloid size, available memory: Hey Kath, The amount of Sheets you can have in a workbook should only be limited by your available memory. I would suggest sending the two files to another computer and trying to combine them to see if that is indeed the issue. Did you receive an error...

Excel: Worksheet Name List, time dave, sorry for the delay

time dave, sorry for the delay, isp: Dave Sorry for the delay. My ISP has been having issues today. Anyway, here s some code that will make this happen for you. Sub SheetNames2() Dim SheetNames() SheetCount = ActiveWorkbook.Sheets.Count ReDim SheetNames(1 To SheetCount) Sheets( DataCapture...

Excel: Worksheet names, douglas m smith, target range

douglas m smith, target range, cell a1: Hi Patrick, There s no way to directly link it but it can be done with a macro: Sub SetSheetName() ActiveSheet.Name = Range( A1 ).Text End Sub You would have to manually run this macro (or assign it to a button and press the button) when you wanted...

Excel: Worksheet Protection and inputs, odometer readings, mileage rate

odometer readings, mileage rate, macro ability: Well, you could simply record the code that protects and insert that before Range( L2 ) = lNum and record the code that protects the sheets and then insert after Range( L2 ) = lNum. What DOES NOT get recorded is the password so you need to add that. ...

Excel: Worksheet Protection, check boxes, control toolbox

check boxes, control toolbox, question thanks: I am sorry, I am still unable to reproduce the error. I have started a new workbook, done the same steps that you are describing here, and it works for me. Can you think on anything special for this book? Macros? In any case, one workaround may be the...

Excel: Worksheet references, cell references, menu option

cell references, menu option, one of those days: Sorry for the late response, I haven t had Internet connection for the weekend. Regarding your question, I would use the Edit- Replace menu option, with the advanced option of looking at the whole workbook. Writing the whole path that the cells references...

Excel: Worksheet referencing in Excel, sheet1, lawrie

sheet1, lawrie, d4: Hi Sue I will work on the assumption that, by replicating the formula in D4 , you mean that you want D5 to pick up sheet2, D6 to pick up sheet3 etc. If so, and assuming the sheets are actually named sheet1, sheet2 etc, to D5 copy: =INDIRECT( [Bookname.xls]sheet...

Excel: Worksheet tab colours, target address, visual basic for applications

target address, visual basic for applications, elseif: If you don t know how to use vba (Visual Basic for Applications) programing, it s not possible. If you do know vba, the code is: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = Me.Range( E5 ).Address Then If Target...

Excel: Worksheet view, minus signs, hand edge

minus signs, hand edge, gray area: John What you have in the gray area is the subtotal colapse keys. to get rid of it. Paint the entire worksheet (clic on the button at the intersection of the column and rows (top left) then go to toolbar/data/subtotals/ in the subtotal pop up screen...

Excel: Worksheet/Workbook protection, format cells, menu format

format cells, menu format, default selection: You can protect each sheet by using the menu Tools- Protectio- Protect sheet. The default selection will prevent the user to modify the contents of the cells on that worksheet, that have been marked as locked. Locked is the default status of the cells, to...

Excel: Put What is in Worsheet X A2 in This Cell - Lookup?, attendance cell, lookup functions

attendance cell, lookup functions, ofcourse: one of the lookup functions could do this for you, and should be OK even if data is resorted. You would need to make the lookup list LONGER than the actual list to save changing the formula every time you add a new student. sorry for the delay in replying...

Excel: Put What is in Worsheet X A2 in This Cell - Lookup?, attendance cell, vlookup function

attendance cell, vlookup function, hlookup: TOM I m not real clear on your question as to how your worksheets are formulated or wht criteria you are using to have the student name automatically appear , but based on what I think you need----yes you can use the lookup functions to have first lookup...

Excel: Put What is in Worsheet X A2 in This Cell - Lookup?, attendance cell, ofcourse

attendance cell, ofcourse, student transfers: Tom, If I understand your question correctly, you would simply like cell A2 on the Attendance tab to equal whatever is currently in the cell in A2 on the StudentList tab. If this is correct, highlight the A2 Attendance cell. Then press =, then click the...

Excel: WRITING SIMPLE FORMULAS, amt pd, sheet tab

amt pd, sheet tab, target: Right-click the sheet tab, select View Code, paste this in: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 Then Target.Offset(1, -3).Select End Sub This tells Excel that when the change you made to the worksheet is in column...

Excel: Wrap text, copy space, special feature

copy space, special feature, keystrokes: Michelle I don t know exactly what is causing the problem, but its likely it is some type of formatting issue, so I would try this. Copy the data to another sheet in another workbook, but use the copy/space special feature and when the paste special...

Excel: Writing a complex formula into an excel spreadsheet, douglas m smith, spreadsheet column

douglas m smith, spreadsheet column, class column: Hi Denise, Your main problem looks to be translating copy type number in column D to the corresponding cost value. To do this, create a table away from your list of data, for this example, in cells J1:K6. In column J, enter 1 through 6. In column K,...

Excel: Writing an if then in excel, array formula, blank rows

array formula, blank rows, quick reply: And presumably ONLY that data, such that you have no blank rows? If you want a macro to do this, it would need to open the original workbook and work through it row by row - this is relatively easy to do. If it needs to be a formula, it could be done with...

Excel: Writing a formula in Excel, douglas m smith, microsoft office 2000

douglas m smith, microsoft office 2000, cell a1: Hi Brad, A formula using nested IF statements would be (for a value in cell A1): =IF(A1 0.05,0.04,IF(A1 0.03,A1,0.03+(A1-0.03)/2)) You can simplify it like this: =MIN(IF(A1 0.03,A1,0.03+(A1-0.03)/2),0.04) The IF statement returns the same value...

Excel: Writing functions in VBA, rectangle shape, target cells

rectangle shape, target cells, false application: Hi, Here is the code: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False Application.EnableEvents = False On Error Resume Next Dim oXlCell As Range Dim intCurRow As Integer Dim intCurCol As Integer intCurRow...

Excel: Writing an INDIRECT function, dollar signs, string argument

dollar signs, string argument, ogilvy: Hello Shannon, based on your description I would see it as: =COUNTIF(Indirect( Base Data !$C &AE42& :$C &AE43), 0 ) assumes AE42 and AE43 are on the same sheet as the formula (Monthly Data) which is what I understood you to say. You don t need...

Excel: Writing an INDIRECT function, concatenated, data sheet

concatenated, data sheet, spreadsheet: Here is a possible formula: =COUNTIF(INDIRECT( Base Data !$C & AE42 & :$C & AE43), 0 ) I suppose that this formula will be on the same sheet as the AE42 and AE43 information. The part inside the INDIRECT is text concatenated with the & operator. ...

Excel: Writing a Macro in Excel, macro in excel, zeroes

macro in excel, zeroes, desired results: Carol Sorry if you didn t get my original answer. I answered on 5/6 but for some reason it didn t get sent. Anyway-- It looks to me like you don t have to write a macro to do your conversion, but a simple formula will do. All of your desired results...

Excel: Writing Macro, budget spreadsheet, c7

budget spreadsheet, c7, help section: First of all, functions can not do what you describe - they can not perform actions. They can only return values. You will need a macro to do what you want to do, assuming I understand your question. A sample of code you will need is Range( A1, C1:C7,F1:F7...

Excel: Writing macros in excel, macros in excel, mobile phone numbers

macros in excel, mobile phone numbers, vlookup formula: cigdem Try using the VLOOKUP function You can use it to look up what you type in a cell and bring forward the data related to what you have looked up. For example if you want to lookup the phone number of a person you might ask excel via the VLOOKUP...

Excel: Writing macros - Testing applications on run, expert listings, excel expert

expert listings, excel expert, easy job: Hi, Christophe, thanks for writing in. I guess you didn t notice either in my expert listing or on the question page where I say that I m not familiar with VBA. I am a programmer, but I use the old macro language that came out with an earlier version of...

Excel: Writing macros - Testing applications on run, index expression, microsoft application

index expression, microsoft application, object index: Hi Christophe, Is the application you are checking for a Microsoft application? If so, I know you can use this: ActivateMicrosoftApp Method This activates a Microsoft application. If the application is already running, this method activates the running...

Excel: Writing for PC Upgrade Magazine's Leasrning Series, pc upgrade magazine, david crohn

pc upgrade magazine, david crohn, douglas m smith: Hi David, Yes, I m very interested. Please send me the details. I once wrote a 12-part series on AppleWorks spreadsheet tips and tricks (Apple II version, not Mac) for SoftDisk magazine in the late-80 s. Douglas M. Smith Charlotte, NC 919-210-7621...

Excel: Writing over picture, david answer, background feature

david answer, background feature, excel sheet: David Hopefully this will work but it depends on how accurate you want it to be. You may have move the graphic around in the header a bit to get it to fit properly. Use the graphic in the header and also the graphic in the background, this way you will...

Excel: Writing a recursive formula in Excel, excel tips tricks, recursive formula

excel tips tricks, recursive formula, excel sheet: I m leading a FREE 1-hour online Webinar on Excel Tips & Tricks on Aug 10 and Aug 24. If interested, go to http://www.iil.com, click on the yellow/orange Try a free webinar link on the left side, click the Microsoft Excel Tips & Tricks link, follow...

Excel: Writing a script for a command button, command button, jackie johnson

command button, jackie johnson, writing a script: Insert the command button. Right Click -- Assign Macro You will see something like Book1!Button1_Click Edit... Now you can right the code. OR Alt-F11 Insert---Module Right a MACRO and ssign it to button by Right Clicking. However, for all this,...

Excel: Writing a smal macro to read excel data and write to a file, a3 b3, sname

a3 b3, sname, excel help: Tracy, Try this macro: Sub ABC() Dim sName As String Dim rng As Range, cell As Range sName = ActiveSheet.Name sName = Application.GetSaveAsFilename( _ InitialFileName:=sName & .txt , _ FileFilter:= Text Files (*.txt),*.txt ) If sName = Then...

Excel: Wrong Results from VBA CountA Function, a10, f10

a10, f10, cta: Hi Ken How are you? Please try the following Sub hhh() Set cta = Sheets( June ).Range( A1:A2 ) Set dest = Sheets( June ).Range( F10:F1011 ) cta.Cells(1, 1) = Application.WorksheetFunction.CountA(dest) remove all previous row data Rows( 10:65536 ).Delete...

Excel: I want to learn VBA for Excel...., vba, dummies

vba, dummies, scratch: Get a book or take a class. The Dummies series on VBA is a good starter. Then record a macro and study that code. Then modify that code from things you learn from the book or class. Then start writing code from scratch. Each time learn form your mistakes....

Excel: I want to take the males from..., ttest, hardrive

ttest, hardrive, google: I m not sure what you re asking. Are you saying the TTEST in help is insufficient? Have you tried http://groups.google.com/groups?q=excel+ttest&hl=en Your sample formula is wrong, but I m assuming it s a typo: You wrote =TTEST(G2:G288,H2-H288,2.1, and...

Excel: i want to restrict to some value in cell of the excel., data validation, message tab

data validation, message tab, word letter: Hi Shiva, (1) Select the cells you want to add restriction to. (2) DATA -- VALIDATION (3) For your example, from drop list choose Text Length (3) In Data, Choose Less than or equal to (4) in Maximum enter 4 (5) You can enter input message (This appears...

Excel: I want to set up a logical..., logical test, format button

logical test, format button, format option: Danny If the cell is always in the same place then you can use conditional formatting. Go to Format and select conditional formatting. Click on the the drop down arrow and select formula is, in the formula section type in =$C$18= yes where c18 is cell...

Excel: want to suppress empty rows, general ledger account, little arrows

general ledger account, little arrows, menu path: If I understand your question, all you need to do is turn on Excel s Filter. Click anywhere in your table (which must have data with one row of field names). From the main toolbar select: Data- Filter- Autofilter Little arrows will appear in the field...

Excel: warehouse stock control, warehouse stock, stock control

warehouse stock, stock control, search filters: Sorry, I have no idea what you mean by an excel file with a different search filter. Re different ways if displaying & manipulating data, there are probably millions if not billions of answers to this question - methinks you need to be a lot more specific...

Excel: water mark, footer section, size tab

footer section, size tab, graphic format: Hi Moshe, You insert a picture or graphic as a background to a sheet by selecting Sheet from the Format menu and then Background from the submenu. Then navigate to the picture you want to use. Please note that this background picture will NOT be printed...

Excel: water mark, wordart, excel microsoft

wordart, excel microsoft, water mark: Hi Moshe, Watermarks are not available in Excel. However many people have tips on creating watermark like things in excel. Many people fool around with the WordArt in Excel. To try WordArt in Excel go to the Excel menu and select View then Toolbars...

Excel: watermark an excel worksheet, watermark format, arrow keys

watermark format, arrow keys, excel spreadsheet: Corrinne - Unlike Word, Excel doesn t have a Watermark format. However, it isn t too difficult to create one. First. Use the Wordart button from your Drawing Toolbar and create the Draft just as you d like it, size and color and shape (you can use...

Excel: Is there a way Excel can do this?, array formula, array formulas

array formula, array formulas, excel user: ...if there is a function or macro that excel could run to give me the requested result... Well, yes again Concerning a macro, there is not one - macros have to be written to do the specific task. Concerning a formula, I think the best bet is to...

Excel: is there a way to link a cell to a different worksheet?, microsoft office applications, sheet names

microsoft office applications, sheet names, linking data: It most certainly DOES apply. I have a beautiful 24-year-old daughter. Thanks! I believe that the answer for your TOC is to just run the macro again. As I recall, that macro would update the TOC as opposed to creating a new one all over again. If NOT, then...

Excel: is there a way to make sure...

The only way to do that would be using the help of VBA code. Probably the closest you could get to that would be installing a button to run code for a duplicate number check on that particular column. If you are interested, I could make the VBA code (for a...

Excel: web page filter, filter tabs, web sheet

filter tabs, web sheet, pop up block: Saving as HTML is the same as saving as a web page. I have uploaded a new spreadsheet in reference to the discussion below: http://home.swbell.net/nate-sus/excel/filterweb.htm http://msdn2.microsoft.com/en-us/library/aa155769(office.10).aspx Please...

Excel: web query, microsoft excel 97, ms office 2000

microsoft excel 97, ms office 2000, static parameters: Office 2000 is a version I no longer have access to, so I have had to use the MS Support site to HOPEFULLY provide an answer (http://support.microsoft.com) HOW TO: Specify Dynamic Web Query Parameters in Excel 2000 View products that this article applies...

Excel: webpage on excel, excel spreadsheet, web query

excel spreadsheet, web query, hyperlinks: (1) hyperlinks work fine in Excel (2) I THINK that what you are trying to do is a web-query - this has a wizard to guide you through it (data, get external data, run web query) and this will auto update as far as I know (ALTHOUGH I ve never had a cause to...

Excel: webpage on excel, chip pearson, excel ontime

chip pearson, excel ontime, open dialog: kb, Use the file= Open dialog, then put in the URL. See what you get. (In terms of being able to use the hyperlinks). to update it, use an ontime macro to close the open workbook and open a new one. See Chip Pearson s site for examples of using...

Excel: weighted, 5th column, max x

5th column, max x, e8: Philip, You mention - First row is worth 5points, 2nd is 3points, 3rd is 1 point, 4th is 0 points and if the last row is checked then the row will now count in the overall total score. I think you mean column. Anyway, assuming that the data is in...

Excel: white space, hiq, empty cells

hiq, empty cells, space question: If you received an earlier answer, ignore it. It contained some debugging code which might corrupt your data. Use this instead. As always, test it on a copy of your data. Select the first cell that you want to process before running the macro. This...

Excel: IF, THEN wildcard in Excel, random series, question mark

random series, question mark, search function: Rich You can use the wildcard in the search function such as +SEARCH( * ,A1,1) In this case if A1 has anything in it it the formula will produce a 1. You can also use +IF(A1 ,1,A1) this will produce 1 if A1 has anything in it greater than....

Excel: IF, THEN wildcard in Excel, array formula, letters of the alphabet

array formula, letters of the alphabet, random series: Excellent question Richard. It does seem strange that Excel does not have a built-in function in its library that would allow the use of standard wildcard characters. So now we are forced to find other clever ways to accomplish the task. I can think...

Excel: Why won't this For Each...Next Loop work?, range selection, loop work

range selection, loop work, wits: You need to select each sheet for this operation. see amendment, I have also added some code to make sure that the user is returned to the starting sheet and also switched off ScreenUpdating. Option Explicit Sub LOI_Colors() LOI_Colors Macro...

Excel: I wonder if you can help me...., decimal points, a3

decimal points, a3, sales tax: Paula This should do it, however you will have to limit the cells to two decimal points to get the correct amount otherwise it will go to 4 or 5 decimal points. Subtotal HST Total =a3/(1+15%) =(A3*15%)/(1+15%) ...

Excel: word doc into excel cell, word wrap, word doc

word wrap, word doc, word documents: Options I can think of are (1) format the cells to NOT word wrap (2) put a hyperlink in each cell - this way, you have a link to each document (3) insert object to put a picture (insert as icon) of the document in each cell, which can be double clicked...

Excel: word to excel via vba, macro feature, excel spreadsheet

macro feature, excel spreadsheet, word text: KAVITA you can copy and paste the word document to the excel spreadsheet. It will not copy to 10 columns, it will copy to one column, you can then use the text to columns feature to put each of the 10 words in a column. All of this can be done with a...

Excel: word-page numbers, numbers menu, format tab

numbers menu, format tab, section breaks: You should have SECTION BREAKS in the various sections of the document - you can then insert the page numbers using the INSERT, PAGE NUMBERS menu, but use the format tab to ensure you get the formatting and lack of continuation that you want - hope this helps...

Excel: word = value in a fromula, mileage reimbursement, mileage rate

mileage reimbursement, mileage rate, vlookup formula: Reginald It sounds as though the easiest way to accomplish what you want is to set up a table or list and then use the vlookup formula to look up whatever yout ype into the To column. In another part of the worksheet, lets say column K thru column...

Excel: words going past column, alignment tab, column c

alignment tab, column c, explaination: Hi Ashley, I wasn t sure which thing you were talking about so I am going to take a guess at which you were talking about. Excel has cell comments that you can add to a cell. But it sounds like you are typing words directly into the cell and they...

Excel: work problem, douglas m smith, value functions

douglas m smith, value functions, cell references: Hi Shane, I suspect the problem lies with the value in the cell. The only way I could get 0.00 to show a result of HIGH was to enter it as text: 0.00. Try adding VALUE functions around the cell references to filter out any text formatting: =IF(VALUE(K1091)...

Excel: workbook before close, excel workbooks, excel workbook

excel workbooks, excel workbook, xla: Untested, but I think this should work --: Private Sub XlApp_WorkbookBeforeClose(ByVal Wb As Excel.Workbook) Dim WrbName As String WrbName = Right(Wb.FullName, 3) If lcase(WrbName) = xla Then Exit Sub MsgBox Wb.FullName & is closed....

Excel: workbook closes when opening another workbook, queston, good answer

queston, good answer, excel 2000: Steve Are you sure that the first workbook is closing or is it being hidden by the opening of the second workbook? Go to the toolbar clic on window and the drop down box will tell you what workbook is open. If both workbook 1 and workbook 2 are shown...

Excel: If workbook exists, explicit function, module option

explicit function, module option, directory folder: That s good news. You need to change this line MsgBox Checked & WbCnt & workbooks, total value is: & dblValue, vbInformation, Result To ThisWorkbook.Worksheets( Main ).Cells(1,1).Value= dblValue Change sheet & cell reference to suit...

Excel: workbooks, startup directory, excel spreadsheet

startup directory, excel spreadsheet, directory option: remove the workbook from the startup directory or change the startup directory IF that is the problem (see tools options general for the at startup open all files in option and the startup directory option), or (less likely) take the worksheet out of the add-ins...

Excel: I am working with Excel 2000...., format cells, sequencial

format cells, sequencial, use auto: Hi Joane, How are you? 2nd Question 1.Select A1:A2 2. Format - cells - number - text 3. type 001 in A1 type 002 in A2 4. use auto fill This gives a sequencial numbering Instead of step 2 you can type 001 in A1 and 002 in A2 1st question ...

Excel: =if not working until I hit F2, circular reference, vlookup

circular reference, vlookup, message bar: It sounds like you are having problems with Excel itself which requires troubleshooting/memory upgrades/etc. In other words, there is no specific answer to your problem. I have tried answering the above issue in the past, but of course, my solution don t...

Excel: working with large data and lists, excel help file, apples and oranges

excel help file, apples and oranges, best bet: I m not 100% sure I will be providing the answer you are looking for; nevertheless, I will give it a shot: 1) Suppose the following data appears in column A and B Apples 10 Apples 20 Oranges 30 Oranges 20 You want a subtotal by Category, such as...

Excel: working out percentages with a formula, working out percentages, how to work out percentages

working out percentages, how to work out percentages, regional company: Hi Catherine, I hear you, I have a difficult time remembering how all the different percentages work also. Here is a link to basic percentage information that may help. http://www.netcomuk.co.uk/~jenolive/percent1.html Your formula is correct =B2/$B$7...

Excel: worksheet change event and other worksheets, target value, target address

target value, target address, ogilvy: Yes. Don t try to select in the other worksheet. Just reference the cells in the other worksheet. for example, is this is in the change event of Sheet1 Private Sub Worksheet_Change(ByVal Target As Range) if Target.Address = $B$9 then worksheets(...

Excel: worksheet name in formula, cell a1, cell reference

cell a1, cell reference, cell b1: A reference to another sheet always includes the sheet name (Sheet2), an exclamation (!), and the cell reference (E12). In your case, you d like this first part to be another cell reference. You can concatenate the pieces of information together into a...

Excel: Same worksheet new question, target value, cleverer

target value, cleverer, time thing: I m working on the form for you at the moment (it s been busy - it s not a difficult thing, just a time thing!) - the code could be amended by having another IF test - but the problem is I was using an OFFSET to work through the sheet to add the records -...

Excel: worksheet open and close using command button, open macro, command button

open macro, command button, chdir: I m assuming you know how to assign the button to a macro to run. Here s the open macro: Sub Opener() Workbooks.Open MyFile.xls End Sub If it s not in the current directory, you can point it by (fix the path, obviously): Sub Opener() Chdrive...

Excel: From one worksheet to another, combo boxes, check boxes

combo boxes, check boxes, combonation: Ryan, If I understand your question correctly, you need some way to record the inputs of various forms on one sheet to the last row of a table on another sheet. The way I would do it is to have a Record button that is tied to a macro. The macro would...

Excel: worksheets in MS Excel, user level security, format cells

user level security, format cells, security settings: I don t know about setting up different user level security settings. There is workbook protection which prevents anyone from adding or deleting worksheets. There is worksheet protection, which prevents entry/edit in Locked cells on a worksheet. ...

Excel: too many worksheets in single workbook, how to organize the worksheet., jan altman, horizontal scroll bar

jan altman, horizontal scroll bar, tiny sheet: I m not aware of any window in which you can rearrange the worksheets in a workbook. But there *are* a couple of little tricks I can teach you to make your job easier. Do a right-click on the little worksheet scrolling arrows. A list of all the sheets appears,...

Excel: worksheets won't copy., blank worksheet, copy option

blank worksheet, copy option, sheet1: Then it sounds like a corrupt file. See if you can create a blank workbook with the same # of sheets, then copy from the old to the new by Cells (don t copy the actual sheet, but the cells, objects, etc) for each sheet, then try copying a sheet from within...

Excel: wrap text, format cells, double click

format cells, double click: Hi Lucas, This may not be the answer you are looking for, or you ve already tried it, but... Have you tried resizing all your rows? Highlight the whole selection and double click on the line between one of the numbers on the left side of your sheet. ...

Excel: How to write Macro in Excel, macro in excel, india china

macro in excel, india china, vlookup: In the first sheet (Sheet1) you should have Sin Singapore Ind India My Malaysia CN Canada US United States Assume Sin is in A1. then on the second sheet, say in G9, you could use a formula like =if(F9= , ,Vlookup(F9,Sheet!$A$1:$B$100,2,False))...

Excel: How to write a macro for countin excel cells when they're filled and lable them, q16, a16

q16, a16, a3: Let me make sure I understand - I had a hard time following what you need based on the way you wrote your post. Assume you type P in A3 the first time. Then you want A3 to turn green. If you type P in A3 AGAIN then you want A3 to be some other color, like...

Excel: How to write a macro that label and count each cell in a row as it's filled, holiday tomorrow, q16

holiday tomorrow, q16, a16: HI Nischal I understand your mission here although I really dont see how the code you have supplied realtes to the problem at hand ??? I will probably take a different approach once you have answered my questions. Hello Again I go on holiday tomorrow...