richard florida, backspace key, apostrophe: Paul I m not sure exactly what you mean by hit CR . If you are refering to the enter key, then perhaps you are somehow changing the formula from a formula to text. If the = or plus sign has been removed from the formula or you have inadvertantly put a...

Excel: getting ecell to display data between dates, hydrogen sulphide, ecell

hydrogen sulphide, ecell, ppm parts per million: Tim, I am not sure of your use of the word page - generally if you print a worksheet, the sheet could have 1 or more pages defined by the print capabilities of the selected printer or if hard coded pagebreaks, then those would be factored in. I suspect...

Excel: Excel 2003 scatter graph, scatter graph, scatter chart

scatter graph, scatter chart, pivot table: Assuming you have columns O&P totally available: Sub MakeXY() Dim Rg As String For Each thing In Range( A2 ).CurrentRegion If thing.Value = x Then n = n + 1 Cells(n, 15).Value = Cells(1, thing.Column).Value...

Excel: Excel 2007 Data Validation, data validation, item descriptions

data validation, item descriptions, description column: Debbie - This is a common problem, for which Excel does not have a solution. It can be solved by using VBA code that does not allow users to paste into the validated cells. The best explaination I have found for the solution is here: http://www.j-walk.com/ss/excel/tips/tip98.htm...

Excel: Excel 2010 _ Appear a specific number of columns, macro button, lct

macro button, lct, sheet1: Hi Daniel, Suppose the # of members is entered in a cell named NumberOfMembers . This macro creates the list of members on Sheet2: Sub CreateMemberTable() Dim lCt As Long For lCt = 1 To Worksheets( Sheet1 ).Range( NumberOfMembers ).Value ...

Excel: excel, microsoft excel, mathew

microsoft excel, mathew, dialog box: Hi Al, You need to select both the columns in order to sort correctly. Assuming Names are stored in column A and numbers are stored in column B , then follow the below setps: 1. Select Columns A and B (make sure you select both the columns) 2. Select...

Excel: How can I do in Excel, man hour, vlookup function

man hour, vlookup function, exact layout: Hello Rizwan, Thank you for your question and the opportunity to answer it. It sounds like you might need a VBA solution since you want to press a button and update the Man Hour Record sheet once the Timesheet is updated. VBA is not an area of my expertise,...

Excel: excel, question pool, microsoft excel

question pool, microsoft excel, mathew: Excel will sort a continuous table this way by default providing no data is selected OR all data is selected. IF you have only one column of the table highlighted then excel will just sort this selection. Hope this explains it but let me know if you need...

Excel: Excel, ipad, dear tom

ipad, dear tom, c30: Dear Tom, There are certain formula which would not work in Ipad as the quick office would be a lighter version with not having all the functionalities. I would suggest break down the formula in different columns and try. Honestly, I would get back...

Excel: Excel, row labels, pivot table

row labels, pivot table, suman: Dear Suman, The best possible way is to handle this using pivot table. Date (All) - Report Filter Sum of SR - In the summary field Row Labels - Dimension Total a 6 b 11 c 8 Grand Total 25 share your email id I would share you the sample...

Excel: Excel Autopopulating, b13, a16

b13, a16: Dear Gail, Please input 70% in one of the cell like B13 below and correspond to $ column multiply like below formula. Now if you change 70% to other % the result would be automatically given. Please send me your email id I would share sample file. Hope...

Excel: Excel: Compare multiple columns to return a result, fourth column, two colors

fourth column, two colors, excel workbook: Jamie, Since you are returning a number, you can use this function in Excel 2007 or later D2 of sheet1: =Sumifs(Sheet2!$D:$D,Sheet2!$A:$A,$A2,Sheet2!$B:$B,$B2,Sheet2!$C:$C,$C2) then drag fill down column D if you don t have Excel 2007 or later...

Excel: Excel Drop Down List, excel drop down list, data validation

excel drop down list, data validation, start at the top: Hi Jaclyn, On your first question, the drop down arrow will always start at the first item in the list following the sort order of the original list. Eg, if your drop down box is picking up the list from B1:B5 containing values 5,3,2,4,1 in the same order,...

Excel: Excel 'if' formula, pivot table in excel, seven layers

pivot table in excel, seven layers, pivot tables: Hello Biddy, Thank you for your question and the opportunity to answer it. You can use an IF formula, but it would be rather cumbersome and in the Excel 2003 version you are limited to nesting an IF formula to only seven layers. You needs might exceed...

Excel: Excel formulas, checkbook register, excel formulas

checkbook register, excel formulas, home tab: Julie - Without looking at how the data is behaving, I m afraid I can t figure out what could be preventing your formula in column F from carrying down. My alternate suggestion is to create a last row, that contains all of your formulas but no data....

Excel: Excel Forward Compatibility, forward compatibility, security scan

forward compatibility, security scan, excel 5: Hello Brian, Thank you for your question and the opportunity to answer it. I have a couple of solutions. Hopefully one of them will work for you. By the way are you using a computer with Windows 7? Other people have had this issue and it seems...

Excel: Excel Forward Compatibility, forward compatibility, richard florida

forward compatibility, richard florida, blank sheet: BRIAN Of course you don t know what the issue is so fixing it specifically is difficult, but you might try copying the data including all formulas, etc and pasting it to a blank sheet in the 2010 version. You will need the ability to open both the 2007...

Excel: Excel help, cell array, vlookup

cell array, vlookup, equivalent number: I ve taken cell A as being A1, Cell B as A2, Cell C as A3 etc =MAX(VLOOKUP(A2,Table2,2,FALSE),VLOOKUP(A3,Table2,2,FALSE),VLOOKUP(A4,Table2,2,FALSE))*VLOOKUP(A1,Table1,2,FALSE) Table1 is a 2 cell by 5 cell array which contains the text values Remote,...

Excel: excel sheets, move one, copy and paste

move one, copy and paste, zoom: Hi Aamir, If the problem is about magnifying 6 times, please check the font size in the destination sheet. Also please check if the zoom is at 100% or is it at something more. Alternatively, when you past the data, a small icon appears near the pasted...

Excel: Excel spreadsheet, vlookup function, excel spreadsheet

vlookup function, excel spreadsheet, refering: assuming that the costs exist in a table, then a vlookup function would do the job - you would need to enter the formula (and if this is to have a lot of entries, you would need to have it set up waiting in the other rows) along the lines of =if(isblank(D2),...

Excel: Excel spresdsheet, cell a1, automaticly

cell a1, automaticly, line 1: Ronald - This is what we used to call 3-D formula : create formulas on the Master page that, instead of looking at cells within it s own sheet, looks at cells on OTHER sheets (or even other Workbooks). Begin a calculation on the Master sheet - cell...

Excel: Excel stock system, amazing apples, ebay business

amazing apples, ebay business, crunchy carrots: Hi Alex, This one looks tricky. Even before we start working on the excel, we need to get a few things standardised: 1. the quantity - you store apples, bananas and carrots in Kgs, the report gives the weight in grams. 2. even if we try to use text to...

Excel: How to exctract data, line thanks, allexperts

line thanks, allexperts, macros: Hi Deana, There are macros out there which should be able to do what you need, but it also depends on the kind of chart and complexity of data which you are using. I can help you better if you could send me a copy of your chart and the original data....

Excel: excel sheets

Excel: excel sheets

Excel: Finding duplicate value, cell1, colorindex

cell1, colorindex, tom hope: Rakesh, select your data and run this routine. i can only take values 1 to 56. I started it at 33 since those colors are lighter. However, if you have a lot of differenct duplicate values, it will loop around and may color a cell with a dark color. ...

Excel: Finding the last row of each sheet for a column, wbk, rws

wbk, rws, pastes: This should work, code Sub LoopThroughSheets() LoopThroughSheets Macro -1st adds a workbook -Loops though sheets gets last row from each sheet Dim shts As Worksheet, Wb As Workbook, WBK As Workbook, WbSh As Worksheet Dim Rws As Long,...

Excel: Follow-up regarding not printing blank rows, blank rows, vba script

blank rows, vba script, column name: Ted, the second argument of Cells is the column. It can be specified as a number or as the column Name (such as B or IV as examples). In the below code, I have changed the 1 (column A) to a B to make it easier to see. Private Sub Workbook_BeforePrint(Cancel...

Excel: form Control Button disappearing!!!!, button properties, button name

button properties, button name, control button: since it s possible to have more than one shape with the identical name (yes, happens), you can get the name of the shape, but when you access it you may access a different one! Only solution is to run some shape-naming procedure to look through all the shapes...

Excel: Formula in Excel calculating when US Markets would open, excel user, national holidays

excel user, national holidays, juhasz: ANNOUNCEMENT: There will be an Excel User Conference May 1-3 in California. There are 3 instructors: Bob Umlas(me) (an Excel MVP), Tom Urtis (an Excel MVP), and Szilvia Juhasz (an Excel trainer). The location is either San Francisco or Los Angeles, still...

Excel: IF Function, cell b1, value error

cell b1, value error, 599cd: Well, without seeing what you currently have for a formula, I can t tell you what you re doing wrong with your a href= http://599CD.com/X2BPIU IF Function /a . However, what I would do is this: =IF(A1=5, No , Yes ) Put that in cell B1 to tell you if...

Excel: Global String Variable, declarations section, userforms

declarations section, userforms, public declaration: Paul, You could make it a constant. at the top of a general/standard module (insert= Module in the vbe) you could put Const mydir As String = C:TestFolderSubFlder by putting it at the top of a general/standard module in the declarations...

Excel: Harvest Multiple RowLabel criteria with one Excel formula?, row labels, input cells

row labels, input cells, sumif: I can t imagine any simple way to extract data using row labels like yours. Before you can do anything normal , you would need to do something to convert those row labels into standard simple date references. Perhaps adding a column to your data that...

Excel: Hiding blank lines, cell b1, numeric value

cell b1, numeric value, indirect address: So I guess in the daughter sheet you have something like this (assuming the Mother sheet is named Sheet1 =IF(INDIRECT( Sheet1!B &ROW())=$B$1,INDIRECT(ADDRESS(ROW(),COLUMN(),,TRUE, Sheet1 )), ) I put that in A2, then drag filled it over to D2 then...

Excel: Hlookup using a named range, cell a1, column number

cell a1, column number, tax table: Indirect converts the contents of a cell to a reference, BUT in this instance it sounds like that isn t what you want to do - but I m a bit confused about what you DO want to do! Searching for the word tax would be =MATCH(IA1, Tax Table !$A$1:$AJ$1,0)...

Excel: Importing Data, case matters, column c

case matters, column c, business names: Hello Ramal, Thank you for your question and the opportunity to answer it. You did not state whether or not case matters in the names. Sometimes data is imported in ALL CAPS, sometimes in lower case, and sometimes in a proper format, such as John Smith,...

Excel: Index help, index 1 2 3, match

index 1 2 3, match: INDEX = a range of values. MATCH = finds a specific value in a range of values INDEX/MATCH find a specific value in a range of values and return a value from a different range adjacent to the matched value. Your question is unclear to me as are your...

Excel: index and match issue, code snippet, paste values

code snippet, paste values, christopher mitchell: Chris Mitchell, with so little specific information, best I can do is show you some sample code. If I wanted to replaced formulas in the range D2:D300 on Sheet1 with the values they produced then I would use the below code snippet. with worksheets(...

Excel: index and match issue, srng, xx3

srng, xx3, chris mitchell: Chris, this code worked for me as I understand the requirement. It replaced the formulas in column B but left the formulas in E, H, K, N, Q intact in your workbook in the sheet termylyassessment. Sub ABC() Dim v As Variant, s As String Dim sh As...

Excel: Indexing, function index, index 1 2 3

function index, index 1 2 3, b1: Raj: I suggest the following function... =INDEX({ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 },(MATCH(E27,{ Remote , Unlikely , Possible , Likely , Almost Certain },FALSE)-1)*5+MATCH(F27,{ Insignificant , Minor , Moderate , Significant , Severe },FALSE))...

Excel: Linking Password Protected Workbooks, master accounts, r400

master accounts, r400, raw data: Hello Ginger, Thank you for your question and the opportunity to answer it. Yes, you can link password protected workbooks. However, when you want to update the Master workbook, you will need to enter the passwords for the source workbooks. So, no,...

Excel: Loop through Sheets, dear dave, rws

dear dave, rws, pastes: Try this, code Sub Button1_Click() Dim shts As Worksheet Dim Ws As Worksheet, wsRws As Long Dim Rws As Long, Rng1 As Range, Rng2 As Range Rws = Cells(Rows.Count, A ).End(xlUp).Row Set Rng = Range(Cells(1, 1), Cells(Rws,...

Excel: mac 2011 decreasing list VBA, target validation, target application

target validation, target application, data validation: Jeff - The code works perfectly on my computer, so I m pretty sure I know what the problem is. I have a PC. The VBA code has probably been written for the PC. You have a Mac - Mac Excel 2011 does not support scripting runtime objects, you have to create...

Excel: Macro, time macro, pseudocode

time macro, pseudocode, ad1: Wayne, Maybe this untested pseudocode. Sub ABC() Dim sh as Worksheet Dim rw as long set sh = Activesheet rw = sh.Cells(sh.rows.count, D ).End(xlup).row sh.Range( D1:H & rw).Printout sh.parent.ExportAsFixedFormat Type:=xlTypePDF, _ Filename:=Thisworkbook.Path...

Excel: macro for sorting sheet without sorting frozen rows, frozen cells, macro class

frozen cells, macro class, macro buttons: that answer looks valid for you as well - Range( A5:H & WorksheetFunction.CountA(Range( A:A ))).Select was a selection of cells from a5 to H whatever - I d PROBABLY now suggest Range( A5:H & ActiveSheet.UsedRange.Rows.Count).select As the sorting...

Excel: Macro to sum different rows & get result in next column, student criteria, student numbers

student criteria, student numbers, adaption: Abdul - You can use the SUMIF() function for this. Expand the student numbers for all purchases: student# item purchased cost total 456 pencil .10 456 paper 1.00 123 pencil .10 123...

Excel: Matching data in 2 columns, then calculating based on 2 supporting columns, b count, column c

b count, column c, ogilvy: Michelle, assume xyz in Material A is cell A2 then =Match(A2,C:C,0) would return 4 (cell C4) to get the 4 under count B you would do =Index(D:D,Match(A2,C:C,0),1) then say you are doing this in E2 E2: =$B2-INDEX($D:$D,MATCH($A2,$C:$C,0),1)...

Excel: Matching a range of days, analysis toolpak, array formulas

analysis toolpak, array formulas, credits and debits: I found a UDF easier. Function Matching(rg1 As Range, rg2 As Range) For i = 1 To rg2.Rows.Count If rg1(1) = rg2(i, 1) - 3 And rg1(1) = rg2(i, 1) + 3 Then If rg1(2) = rg2(i, 2) And rg1(3) = (rg2(i, 3) * -1) Then ...

Excel: Nested If Statement exceeding limit, question pool, vlookup formula

question pool, vlookup formula, salary information: There is a limit for nesting of brackets, but generally this indicates that the formula could be refined - in this instance, the tax table should be redesigned so that it can be used with a vlookup formula -this would eliminate most of the brackets - it was...

Excel: Obtaining data from date range 1.7.2012 and 13.11.2012, australian dates, dear tom

australian dates, dear tom, ogilvy: Hello Annette, if the cells actually contain dates, then you can do =countif(A:A, =1.7.2012 )-countif(A:A, 13.11.2012 ) I am in the US so using US notation, this worked for me: =COUNTIF(A:A, =07/01/2012 )-COUNTIF(A:A, 11/13/2012 ) It is...

Excel: Open Workbook, line thanks, thanks in advance

line thanks, thanks in advance: MyBook.xlsx does not allow macros to run at all. I cannot imagine that workbook closing itself. If the file you re opening is a .xlsm file, just change the extension to .xlsx and the macros won t fire. They will still be there until you save the file...

Excel: permutations, combination code, ogilvy

combination code, ogilvy, c1: Tony A1: 0 B1: 0 C1: 0 D1: 0 A2: =IF(MOD(ROW(),10)=0,9,MOD(ROW(),10)-1) B2: =IF(A2=0,IF(B1=9,0,B1+1),B1) C2: =IF(AND(B2=0,B1=9),IF(C1=9,0,C1+1),C1) D2: =IF(AND(C2=0,C1=9),D1+1,D1) select A2:D2 and drag fill down to row 10000 or you can run...

Excel: printing a protected worksheet, blinking cursor, cells

blinking cursor, cells: Hi. Make sure that you have properly edited a cell and that the cursor is still active. (Not in Edit mode with a blinking cursor). What you said should ideally not happen and is not a bug. Upload your file somewhere, if you want me to test it out. Shahi...

Excel: Problem in Pulling next row data.., Excel Sixthsense Excel Get Multiple Matches Using Function

Excel Sixthsense Excel Get Multiple Matches Using Function: Hi Sumit, Assuming that the provided example data is present from A1:B11 and the input value (i.e.) A in D1 cell. In D2 cell - For 2007 or higher versions code =IFERROR(INDEX($B$2:$B$11,SMALL(IF(($A$2:$A$11=D$1),ROW($B$2:$B$11)-1),ROW(A1))), ) /code...

Excel: Prorated Vacation, cumulative hours, hr work

cumulative hours, hr work, richard florida: Nicole yes you can put in a formula that will accrue the vacation based on your criteria. You haven t given me quite enough information to give you the formula but if you can give me the criteria for the vacatin accrual and where the data is in your worksheet...

Excel: protection in Excel 2010, down arrow, word tools

down arrow, word tools, dear tom: Bas, When you go to save the workbook, you will get the save as dialog There you can specify the location and file name. At the bottom are two buttons. Save Cancel but to the left of the Save button is the word tools. This is also a button,...

Excel: question on your 3d vlookup and hyperlink.xls, array formula, sheet names

array formula, sheet names, formula index: I would create columns and use the sheet names as the headers of those columns. The search names in column A, the sheet names across B1:F1. Then this formula in B2 copied down and across will mark each column (sheet) where the column A name can be found....

Excel: re: Excel VBA, excel vba, excel user

excel vba, excel user, invoice numbers: First, this should be done with a pivot table - 4 clicks & you re done. But here s a VBA solution: Sub Summarize() Dim Curr As Worksheet, AnsSheet As Worksheet Dim Invno As String, cum Set Curr = ActiveSheet Set AnsSheet = Sheets.Add Curr.Activate...

Excel: Relate one main number to a colum, colum, variable value

colum, variable value, cells: Karolina: Assuming the first, variable, value is in cell A1, then you would use this fomrula in all cells where you want the value to automatically change to match the first: =$A$1 Just copy that formula to any cell where you wanted the 500 to automatically...

Excel: reorder and truncate text in cell, emrich, search cell

emrich, search cell, question thanks: Sorry, blame it on the long day here in the UK coupled with a 4 month old - try =RIGHT(A1,LEN(A1)-SEARCH( . ,A1))& , &MID(A1,SEARCH( . ,A1)-1,1) for the formula method - amend the macro in a similar way (let me know if this isn t clear though!...

Excel: Repeating a formula in VBA, cell b6, circular reference

cell b6, circular reference, excel help: In help, you can search for rc notation and there s be many things to look at. Range( A10000 ).end(xlup) is the code for effectively selecting cell A10000 and then pressing ctrl/Up-Arrow. The .Row part gives the row of effectively doing that. In reality,...

Excel: Reset Workbook to Default Values, excel saveas, input cells

excel saveas, input cells, template search: Hi Steve, If you are looking for code to reset your sheets, I would no be able to help you with that as I have no idea how your sheets are set up. You could save your workbook as a template a href= http://office.microsoft.com/en-ca/excel-help/save-a-workbook-or-worksheet-as-a-template-HA010218874.aspx...

Excel: Getting Sheet Name from Unique Sheet Number

Hi daniel, The solution depends on your situation actually. If this is a worksheet you yourself insert(ed) and control, you can change that unique name (technically referred to as the sheet s CodeName) yourself by editing the name property of the Sheet12...

Excel: Sorting Within a Sheet (Extreme Novice), custom sort, row 24

custom sort, row 24, google: What you re asking can be done, but you ll need a helper column, like column V (or some unused column). It d help me to help you if you could either send me a sample wb or mock one up which could be used to demonstrate what formula would need to be put into...

Excel: SUMIF(S) to harvest from Pivot Table?, input cells, row labels

input cells, row labels, sumif: does 2012-M5 mean the range 5/1/12-5/31/12 and 2012-W51 means the 7 days of week 51 and 2012-Q2 is from 4/1/12-7/31/12? If so, does 2012-W1 mean the first 7 days of 2012 or does that depend on the day of the week of 1/1/2012? If you want to avoid a nasty...

Excel: table format in excel, line colors, spreadsheet

line colors, spreadsheet, formatting: Julie - This is simple, but not intuitive. Right-click anywhere in the table, and select Table Convert to Range. It asks Do You Want to Convert the Table to A Normal Range? , answer Yes. This removes the table, but retains formatting - which is good,...

Excel: Time Sheet withover time lunch, time lunch, double time

time lunch, double time, dropbox: Glenn: I have uploaded a sample spreadsheet here: https://www.dropbox.com/s/11er96lh9evv0f1/GlennTime.xlsx?m I hope this provides you with the help you need in designing your own timecard. Nathan Please remember to donate - I spent about 30...

Excel: Times of Intrsecting Events, question example, allexperts

question example, allexperts, vba: Parantapkumar: What you are requesting is possible to do with formulas; however, it would be very time consuming to develop. It would probably take me several hours to design. Are you interested in paying more - say $25 an hour? I could likely complete...

Excel: Tracking 401(k) Eligibility, conditional formatting, eligibility question

conditional formatting, eligibility question, eligibility dates: Robert The simplest way to handle it would be to create a column that has the eligibility date (or one year from hire date in it) and use the same conditional formatting that we created previously and apply it to that column. In my previous answer I assumed...

Excel: Get TypeName, obj, shapes

obj, shapes, cells: Hi Daniel, Perhaps like so? For Each wksht In Worksheets For Each sShapes In wksht.Shapes If sShapes.Type 4 Then Do not want Comment With sShapes lLoop = lLoop + 1 Incremente...

Excel: Userform beforeclose closes without saving., enduser, selwyn

enduser, selwyn, boolean: Hi, You should not be able to close the userform, it closes by itself. It asks me if I want to save changes when the userform closes. Maybe your workbook is already saved when you click the close Button. You can add activeworkbook.save to the unload...

Excel: Using multiple criteria to return a value from a table, shift type, vlookup

shift type, vlookup, d13: This formula will do it, is should be obvious where you would add XL to the match array: In the example picture shown, the formula goes in H1, then is copied down and across through H. Adjust the range of cells (currently 20 rows) to the range needed,...

Excel: Usiusing two criteria to return a value from a table, array formula, shift type

array formula, shift type, easy solution: Frode: Extra) Your formula has an error...it should be indata!A1:A13=A1. Maybe that is just a typo? 1) You need to change the part that says indata!D1:D13= LA to indata!D1:D13= UL in order to return shift UL. 2) In order to eliminate the need to...

Excel: VBA Code, acell, colb

acell, colb, message box: Hi dp, try this out code Sub INdex_Match1() Dim wksWatch As Worksheet, wksDash As Worksheet Dim colA As Range, aCell As Range Set wksDash = ThisWorkbook.Sheets( DashBoard ) Set wksWatch = ThisWorkbook.Sheets( (5) Watch List ) ...

Excel: VBA code to find most recent revison in spreadhseet, column id, inputbox

column id, inputbox, revison: Hi Mike, I have a feeling that we should be able to handle this need with a combination of formulae only and need not go into macros and things like that. If you could send me an excel with a sample of your real data (non-confidential), I can try and work...

Excel: VBA to create Dynamic Named Range, counta, celss

counta, celss, cell a1: Charlie, If the first range is say 4 columns wide by 50 rows, it could expand to 6 columns wide by 200 rows. So you would have to explain why it would do that. In the code it should name the range as contents of top left hand cell, ...

Excel: VBA Find, d7, ogilvy

d7, ogilvy, dashboard: David, You say look in wsOE column E, then you set wsOE to Order Entry and then your code looks in a third sheet in column E. So I changed the Find to look in wsOE column E This summarizes what I am trying to do. Find the match for wsDB.range(...

Excel: VBA formula, christopher mitchell, sheet tab

christopher mitchell, sheet tab, chris mitchell: Christopher Mitchell, The code needs to be enhanced to avoid recursive calls. So I have tested this routine and it worked for me: Private Sub Worksheet_Calculate() Dim sh As Worksheet On Err GoTo errHandler Application.EnableEvents = False Set sh...

Excel: VBA Next available row, empty cells, value time

empty cells, value time, value date: Option Explicit Sub Cancel_Order() Dim wsOE As Worksheet, wsDB As Worksheet Dim vFIND As Range Set wsDB = ThisWorkbook.Sheets( Dashboard ) Set wsOE = ThisWorkbook.Sheets( Order Entry ) If wsDB.Range( F7 ) 0 And wsDB.Range( J7 ) = 1 Then Find...

Excel: VBA Solution, ogilvy, cells

ogilvy, cells: Jesse, first, instead of rw = sh4.Cells(sh4.Rows.Count, S ).End(xlUp) you need rw = sh4.Cells(sh4.Rows.Count, S ).End(xlUp).row Then Dim rw as Long rw = sh4.Cells(sh4.Rows.Count, S ).End(xlUp).row Range( V4 ).Value = sh4.cells(rw, S ).Value...

Excel: vba solution, bottom table, question thanks

bottom table, question thanks, bottom row: Hi Jesse, Something like this perhaps? Sub MoveRow11() With ActiveSheet Copy current values and paste below table 2 .Range( B11:BX11 ).Copy .Range( A & .Rows.Count).End(xlUp).Offset(1).PasteSpecial xlValues ...

Excel: VBA Solution, bottom row, bq

bottom row, bq, ogilvy: Jesse, You said E:BX, but your example was to BQ. This code worked for me using BQ (and as I understand the requirement). You can alter it to do what you want. I have assumed that Table1 will always end above row 30. Further, even though you say...

Excel: Vlookup, efgh, zafar

efgh, zafar, column c: Hi Zafar, I m not clear on what you are asking for. Can you send a more specific example - what is there in sheet 1 column A, Column B and Column C. What is there in Sheet 2 columns A,B & C and what are the conditions / criteria to be met. Gulshan...

Excel: vlookup using a range, array formula, vlookup function

array formula, vlookup function, formula index: rachel, Assume Table 1 starts in A2 with the value 0.340180122 assume table 2 starts in M2 M2: A N2: 0 O2: 0.2 In B2 I put this formula =INDEX($M$1:$M$6,SMALL(IF(($A2 $N$2:$N$6)*($A2 =$O$2:$O$6),ROW($N$2:$N$6)),1),1) entered with Ctrl+shift+Enter...

Excel: Working on christmas day, overtime pay, christmas day

overtime pay, christmas day, line 6: I can certainly try- you said Christmas day, but you ve typed 24th December - which confuses a little, but I m also failing to follow the rules - on rule 1 work was from 12 until 6pm, but you ve calculated pay from 12 to 1:15pm and then the non worked hours...

Excel: writing formulas in VBA, chris mitchell, d4

chris mitchell, d4, ogilvy: Chistopher Mitchell, Dim sh as worksheet Set sh = Worksheets( calculation ) if sh.Range( B1 ).Value = 5 then sh.Range( D4 ).Value = Not Available else sh.Range( D4 ).Value = sh.Range( C1 ).value * 2 end if -- Regards, Tom Ogilvy ...

Excel: Zipping Excel Files, richard florida, florida usa

richard florida, florida usa, trisha: Trisha Sorry I have no experience with the JZIP software. But just a comment--Excel files usually are not large and don t require zipping. I suggest you reask the qeustion of another allexperts person and maybe someone can give you a better answer....

Excel: 16th digit of any number, math functions, digit number

math functions, digit number, digits: In excel 2003 and earlier, this is one of the limits - the precision is 15 digits. There is no way round it in this version of excel - unless you don t need the number to be stored as a number, if the cell was set as text then you could have the value you...

Excel: ActiveWorkSheet.Protect, run time error, custom ribbons

run time error, custom ribbons, custom macro: Sounds odd - the cells that are to be sorted do indeed need to be formatted as unlocked in the protection area - and that needs to be ALL the cells that might be sorted - I have a SUSPICION that this may be a bug within Excel - did the sorting need to happen...

Excel: Adding time in Excel, cell c1, adding time

cell c1, adding time, harsha: Hi Harsha, Although I don t have Excel on hand to test out my solution, I think you are using formulas unnecessarily. Excel is smart enough to add time if youhave formatted your column right. Assuming column A is cell formatted as Time, the below formula...

Excel: Additional conditional formating, formating, aidan

formating, aidan, target: You don t need VBA - you need a lookup table The table would have the unique identifier for the team member in the first column, the minimum target in the second (I m assuming this may change) and the target in the third. Name this table - that part is...

Excel: Align, label names, lbl

label names, lbl, ogilvy: Eran, this worked for me: Dim LBL As Control For Each LBL In frmWelcome.Controls If Left(LBL.Name, 2) = LM Then LBL.TextAlign = fmTextAlignRight End If Next LBL I assume that your label names start with the...

Excel: arrange cells in Excel 2010, emails addresses, empty spaces

emails addresses, empty spaces, copy and paste: No problem - select them all as a block (so you have them all highlighted) and then simply use sort to sort them into order - which will move the blanks to the end - I haven t got office 2010 on a machine in front of me at the moment, but in 2007 it s on the...

Excel: Array (CSE) Formula, blank cells, e2

blank cells, e2, b2: Hi Robert, I tried many ways to solve your problem, but am afraid I was not able to do so perfectly. The Rank formula does not support Conditional Ranges and I couldn t use the SUMPRODUCT formula alternative for your case. For simplicity, I d request you...

Excel: Autocomplete typing data validation list, matchentry property, data validation

matchentry property, data validation, userforms: * As far as I know, you can t do this in Excel with data validation in a cell. The kind of functionality you are looking for, however, comes standard on excel Userforms (in vba project), and Access forms as well, via the MatchEntry property. I suggest...

Excel: Average Sales from each day of the week, word hope, richard florida

word hope, richard florida, a15: Nathan You are gonna like this, because excel can handle it very easily. The formula might look like this =AVERAGEIF(A1:A100, Monday ,B1:B100) The formula says if any of the cells in the range A1 to A100 are equal to Monday, then take those amounts...

Excel: BeforeClose event, pseudo code, good solution

pseudo code, good solution, second time: Daniel, there isn t a good solution for this. You basically have to cancel the user initiated close and control everything yourself. The only problem is you need to disable events to prevent cranking up the beforeclose event a second time when your macro...

Excel: Can we break a VBA code which is protected, vba project, vba code

vba project, vba code, google: * From my understanding, the answer is no . The only type of protection I have seen broken using vba code is worksheet level protection. However, I have also heard from trusted sources that the software solutions you can purchase (google break password...

Excel: Button to goto a sheet on excel page, vba code, page thankyou

vba code, page thankyou, page hi: Hi Kevin How are you? Please do the following 1. Activate Programmer on your menu (Excel Options) 2. Draw the command button (toolbox) 3. Copy the vba code Private Sub CommandButton1_Click() Sheets(2).Select End Sub Cheers Adelaide ...

Excel: Calculate commission earned for a pay period between two dates., column c, oct 11

column c, oct 11, ogilvy: Scott assume the second set of data is in column M for start period, Column N for End Period and thus for dates in row 2, in cell O2 put in =sumif(B:B, = &M2,C:C)-sumif(B:B, &N2,C:C) then drag fill down column O. If you wanted to do this...

Excel: how to calculate time, Microsoft Excel, Copying formulas

Microsoft Excel, Copying formulas, calculations: Hi June, Not sure why this is happening. Are you trying to copy cells or copying the whole Excel tab? When I copy entire tab I do not have any problems. When only copying cells, too many variables could be present in either spreadsheet to determine cause....

Excel: having cells one under the other, emails addresses, empty spaces

emails addresses, empty spaces, copy and paste: No problem - select them all as a block (so you have them all highlighted) and then simply use sort to sort them into order - which will move the blanks to the end - I haven t got office 2010 on a machine in front of me at the moment, but in 2007 it s on the...

Excel: Changing Color in Formula, time jerry, red background

time jerry, red background, color changes: When you say Actual Formula you are referring to the formula that is IN the cell you are trying to color? You re going to use Excel s Conditional Formatting feature to color the cell, not cell formatting. Select the cell, then open the Format Conditional...

Excel: Changing referred cell references by mouse, mouse keyboard, cell references

mouse keyboard, cell references, richard florida: Chris I am not exactly sure what you are needing to do, but I ll make a couple of suggestions. Sometimes when I copy a formula I do not want to make changes to the formula referencing, so I will make the formula text by removing the equal sign, then I...

Excel: Clear all text box, text boxes, textboxes

text boxes, textboxes, time thanks: Give each of the the textboxes a name that begins with Text . Then use code like this: Private Sub CommandButton1_Click() Dim txtbx As Control For Each txtbx In UserForm1.Controls If Left(txtbx.Name, 4) = Text Then txtbx...

Excel: colour change, cell b2, home tab

cell b2, home tab, new date: * Hi Kevin, This is a lot to answer in one question, only because there are various ways to approach it. Here is one suggestion, use conditional formatting. Let s suppose your dates are in column B. Starting in row 2, as traditional use of simple...

Excel: Column/Rows Grouping in Excel, cell d1, richard florida

cell d1, richard florida, question thanks: Haseeb For example--assume that columns E,F,G are grouped. When the group is closed you will only see columns A,B,C,D,H,I,J, Etc. But you can put in cell D1 Group 1 represents all available fruits and this will be able to be viewed when the group...

Excel: combine excel generated macro code with other VBA code, formula worksheets, copy worksheets

formula worksheets, copy worksheets, source tab: Hi Paul, You need to do two things before anything else... first figure out where the user wants the data to be pasted and the second to set up a repeating condition. Unfortunately, both of these need basic VBA coding knowledge and cannot be simply done...

Excel: How to compare two sheets of an excel, cell a2, vlookup formula

cell a2, vlookup formula, cell c2: Hi Abhay, Assuming column A always contains a unique DocNo. you could use the Vlookup formula, or the the vertical column lookup match formula. You could either do this in sheet3, or pick one of the sheets you want to compare to the other, which...

Excel: Comparing Two Columns Of Dates And Getting A Percentage, random dates, a26

random dates, a26, maintenance report: Nick Rupp assume the dates are in A1:A26 then =(countif(A1:A26, =Jan 1, 2012 )-Countif(A1:A26, =Jan 31, 2012 ))/26 format the cell to display as percentage If you had Jan 1, 2012 in F1 and Jan 31, 2012 in F2 you could do =(countif(A1:A26,...

Excel: Complicated Excel formula using 3 workbooks/worksheets., invoice example, matrix report

invoice example, matrix report, excel formula: Hi Allen, Excel formulas work across workbooks as well. First try the VLookup on a separate worksheet and see whether that method is OK to pull data. It should be updated dynamically as and when you type data. If it works keep all your workbooks open, and...

Excel: Complicated Question, introductory lesson, complicated question

introductory lesson, complicated question, chocolate cake: This is one of those things that is MUCH better done in a DATABASE program like Microsoft Access, not Excel. In Access you would create a table to store all of your individual ingredients (eggs, milk, flour, etc.). You would create a second table to store...

Excel: Conditional Format, conditional formatting, bottom border

conditional formatting, bottom border, trying to find a way: Can easily be done with conditional formatting. If you don t know how to do that, it s pretty hard to explain without a physical workbook -- send one (or a sample with not so many rows) and I ll be able to explain what I did... Send to bobumlas@hotmail.com,...

Excel: Conditional formula to return a "Yes" or a "No", countif, vlookup

countif, vlookup, false position: Jess =IF(COUNTIF(Sheet2!$A$1:$A$5,A2), YES , NO ) if the value is not there countif returns a zero and in the if statement this zero value is treated as a false and the expression in the false position is used to return a result for the if funciton....

Excel: Consolidating hours tracked over several projects, project column, bottom section

project column, bottom section, description section: Neal, I have modified the formula to work with the ranges you show. I have attached an image showing the first formula producing the unique list from column A. The hours are produced by the modified second formula: A26: =IF(OR(ISERROR(MATCH(0,COUNTIF($A$25:A25,$A$5:$A$19),0)),INDEX($A$5:$A$19,MATCH(0,COUNTIF($A$25:A25,$A$5:$A$19),0))=0),...

Excel: convert numbers to numbers, blank rows, must be a way

blank rows, must be a way, real numbers: You re right that the VALUE() function should take a TEXT value and convert it to a number. I d have to see that PDF file and the resulting Excel file to tell you what the problem is. Don t send it, however, since I only have time here for SHORT answers. ...

Excel: Copy folders and subfolders (macro), copy folders, destination string

copy folders, destination string, hopeless task: Here is the absolute simplest copy a folder and all of its contents including subolders to another folder technique I know: pre Sub CopyFolder() Dim FSO As Object Set FSO = CreateObject( Scripting.FileSystemObject ) FSO.CopyFolder C:DokumentsBefore...

Excel: Copying formula, cell a1, dollar signs

cell a1, dollar signs, sheet1: Lisa I m not sure exactly what the problem is but here goes. You can use the normal paste copy for example if in cell A1 of sheet 1 you have the formula =A10. If you copy that formula to sheet 2 into cell A20, the formula will copy but because of relative...

Excel: Copying a formula from one cell to another three cells further in the same row, cape town south africa, column letter

cape town south africa, column letter, quantity column: Ghalieb, There is no built in support for what you want to do that I can think of. What you could do is copy and paste the calculation in the column 3 columns away, then select the column and use the replace functionality to change the column Letter to...

Excel: Correct "hh:mm" to "mm:ss", hh mm ss, paste values

hh mm ss, paste values, track lengths: Say col A is formatted as hh:mm, and A1 contains 07:35. Format col B as [h]:mm:ss, and enter into it the formula =A1/60 into B1 and copy down the col. So e.g. B1 will now display 0:07:35 meaning zero hours, 7 minutes, 35 seconds. If you want to...

Excel: counting colored cells, blank spreadsheet, colored cells

blank spreadsheet, colored cells, chris answer: No problem! Function colornumber(myvar As Range) colornumber = myvar.Interior.ColorIndex MyVar is a variable representing a range - though single cell would be best thus MyVar.interior.colorindex translates to return the number of the color that...

Excel: counting colored cells in a spreadsheet, colored cells, cell interior

colored cells, cell interior, colorindex: my email is aidan.heritage@virgin.net - just in case that helps (a means of getting a file to me for instance!). Insert the code I ve given into a module, then enter the formula - but you would do something like =colorcount(1:1,colornumber(a1)...

Excel: Custom formatting of cells, last option, 1st february

last option, 1st february, missing something: Hi Venkatesan, Custom formatting is same as any other formatting. Simply, right click the cell and choose Cell Format. In the new window, choose the last option... Custom , and in the text box to the right enter.... yyyy/mm/dd (without the quotes). ...

Excel: cut and paste and entire worksheet, cut and paste, exact size

cut and paste, exact size, error message: Hi Robin, You should only cut and paste actual cells/columns with data to avoid receiving the error message you indicated in your question. When you paste what is copied, you must be sure of the exact size of the cells (rows and columns) that was cut....

Excel: If date is with period start and period end calculate commission earned, a140, oct 11

a140, oct 11, ogilvy: Scott, =SUMIF($D$3:$D$123, = &$A140,$J$3:$J$123)-SUMIF($D$3:$D$123, &$B140,$J$3:$J$123) worked for me. See the Image. This is set up so you drag fill it down. the $ signs are important exactly as I have them. -- Regards, Tom Ogilvy...

Excel: Defining range in VBA, vba code, cell reference

vba code, cell reference, snippet: The problem lies with not explicitly telling VBA which worksheet you are referring to in all parts of your statement. Your code: Sheet7.Range(Cells(rowcounter1, 1), Cells(rowcounter1, 40)).Select Selection.Copy Sheet10.Activate...

Excel: Delete a Value in a field, based on a value in another field., target value, conditional formatting

target value, conditional formatting, sheet tab: (Sorry -- had a mistake)... Instead of having them put an X in column H, why not simply clear whatever is in column G? In any case, here s code to do what you want. Right-click the sheet tab, select View Code, then put this in: Private Sub Worksheet_Change(ByVal...

Excel: Very detailed formula!, sheet1, bank holidays

sheet1, bank holidays, g19: Your design decision to break the start date into separate cells is not the best decision. If column E simply held an entire date, this could be less difficult. You can send me the file and I can make some design corrections. In the same file, it would...

Excel: detect Excel version and Service pack, rob bovey, chip pearson

rob bovey, chip pearson, expecting an answer: caroline, I didn t find anything better - it appears to me, you would have to know what the build number is for each service pack and then compare the application.Build against that list and determine where it sequentially fits in the list. this article...

Excel: Different User Different Spreadsheet, hr data, spreadsheet

hr data, spreadsheet, access to data: It is possible, but quite some work. Excel is not well protected, so anyone determined can quite easliy get access to data that is not intended to be seen by him. Especially HR data should not be sent to people not authorised to see the infomation....

Excel: disabling paste, conditional formatting, validation rules

conditional formatting, validation rules, data validation: I m not 100% happy with code solutions to this sort of problem, as if a user has macros disabled they can happily destroy your formatting! The code itself looks fine, but upside down as it seems to stop non validated cells. It also doesn t have a self check...

Excel: Enable/Disable Macro, target cells, cell addresses

target cells, cell addresses, mcro: Hi Charlie, I am guessing you are fairly knowledgeable with VBA and macros. One option would be to use the GetAsyncKeyState API to detect whether a particular key (Control is a good candidate) is pressed during the Workbook_Open event. You can enclose your...

Excel: Enable Event, recalculation, active project

recalculation, active project, ogilvy: Daniel, put this in a general module in the Visual Basic Editor (in the editor do Insert= Module with your workbook as the active project) Public Function AreEventsEnabled() Application.Volatile AreEventsEnabled = Application.EnableEvents End Function...

Excel: Excel 2003 Question, data validation, b3

data validation, b3, suns: Define the cells which make up the choices for Bulletin and call it Bulletin. Similarly for Online and Suns. Say your dropdown (I assume Data Validation) is in B3. Now you want the list in C3 to vary depending on your choice in B3. The data validation in C3...

Excel: Excel 2007 VBA Question, vba code, question question

vba code, question question, table columns: Deborah, As I understand what you have said, there is only one table on the worksheet, so you can just do this Sub abc() Dim TblName As String TblName = ActiveSheet.ListObjects(1).Name Range(TblName & [DESCRIPTION] ).Select End Sub that was...

Excel: excel change color, conditional formatting, colour format

conditional formatting, colour format, base colour: format, conditional formatting will allow you to have a number of colours - up to 4 (it s technically three, but the base colour can count as a colour too) prior to office 2007 - cannot remember the limitations post that and haven t got later versions installed...

Excel: Excel Custom Lists, macros in excel, custom sort

macros in excel, custom sort, macro code: * I understand you re not a programmer (I d hardly call myself one, just discovered that excel vba can help you do a lot of extra things in excel). But is there a problem using the macro I posted? Using macros in excel is very common, programmer or...

Excel: How to get the excel data into the User form?, scroll bar, maximum value

scroll bar, maximum value, copy paste: Hi Shoaib, I just wanted your file so that I could type in some code and upload it back. Anyway, see the code below. I used a Scroll Bar approach instead of buttons. However, you can easily modify my code based on my comments. Before copy pasting this,...

Excel: Excel data validation, excel data validation, cell b1

excel data validation, cell b1, error value: Bob, Assume your dropdown is on sheet1 in cell A1. Assume the table of data is in Sheet2 in column A:C so back on sheet1, in cell B1 you would use =if($A1= , ,vlookup($A1,Sheet2!$A:$C,2,False)) in C1 you would use the same but change the...

Excel: Excel Encryption combining cells, source cells, paste values

source cells, paste values, concatenation operator: Hello Joseph, I am not sure what a GCSE is because I am in the US. But you can concatenate data from multiple cells using the concatenate function =Concatenate(A1,A2,A3) so if A1: Dog A2: Cat A3: House you would get DogCatHouse in...

Excel: Excel Formula, excel formula, jan dec

excel formula, jan dec, wys: * Well, there are probably a few wys to approach this, but it sounds like the easiest w/biggest lift for you at this point is to use this in the column where you are typing Dec, Jan, etc. =month(a1) Unfortunately with Excel (unlike Access), you cannot...

Excel: Excel formula, cost transport, cell references

cost transport, cell references, excel formula: You more or less have your formula Put aside cells for each of the variables =round((((Cost+Transport)/EUR_RATE)+BankCharge)*1.3,2) would be the formula - I ve included a rounding to ensure you always have 2 decimal places - replace my cost, transport...

Excel: Excel formula?, reply thanks, vlookup formula

reply thanks, vlookup formula, speaking engagements: Hello Julie, Thank you for your question and the opportunity to assist. As I understand your question, I believe this is something Excel can solve. However, I will need a little more information. What are the criteria contained in the dropdown list...

Excel: Excel Formula Help, excel formula, ogilvy

excel formula, ogilvy, b1: If I have A1: par n= L-6146 A2: par n= L-6146.1 then in B1 I put =MID(A1,FIND( ,A1)+1,FIND( ,A1,FIND( ,A1)+1)-FIND( ,A1)-1) and drag fill this formula in B1 down to B2 (or down the column as far as you have data). It returns...

Excel: excel function for combining multiple cells, data consolidation, excel function

data consolidation, excel function, ampersand: Hi Joel, No. Unfortunately using the ampersand is the only way to join multiple cells using formula. What is it that you are trying to do. Perhaps I can give an alternate suggestion. Perhaps you may be satisfied with data consolidation. See... http://www.tutorialized.com/view/tutorial/Consolidate-Data-%7BTutorial-for-beginners%7D/27275...

Excel: Excel Help, data validation, validation data

data validation, validation data, spreedsheet: Hi Chan, Data Validation, as the name suggests is just that... validate data as per the given condition. In other words, you can tell the system to check the input value of a cell for a certain condition and the system will throw an error if the condition...

Excel: Excel Help, excel help, richard florida

excel help, richard florida, a20: Chan I am not sure what you mean by your first question. Second question--If you make your list include more rows than what is needed your list, and leave the cells blank that you are not using then when you pull down the drop down box it will only...

Excel: Macro Designation Problem, conditional formatting, custom tabs

conditional formatting, custom tabs, calculation mode: I don t think I ve ever actually created a menu/ribbon choice for a macro in Excel - I tend to put buttons on the form to run them, or use events to drive them - despite a lengthy search I ve been unable to find an answer to this - in older versions it was...

Excel: excel macro, empty cells, dear tom

empty cells, dear tom, excel macro: Kuba, You can use this to get the last cell in a column that is not blank Dim r as Range With Sheet(1) set r = .Range( A32 ,.cells(rows.count, A ).end(xlup)) End with if r(1).row 36 then msgbox No filled rows beyond row 36. Quitting ...

Excel: Excel macro issue, forward slashes, blank cell

forward slashes, blank cell, issue question: I m not 100% sure I follow the first part, but try this: enter n4t,g7d in a cell, select it, run this macro: Sub cvt() With ActiveCell.Offset(0, 1).Resize(1, 6) .FormulaArray = =MID(SUBSTITUTE(UPPER( & ActiveCell.Address & ), , , ...

Excel: Excel Macro , Next largest, decimal fraction, vlookup formula

decimal fraction, vlookup formula, vlookup function: It s not totally clear what needs to happen here, in as much as I don t know what drives the first and second match- is it simply a case of first gets first, second gets second? I d be inclined to use a decreasing (or increasing) countif to add a decimal...

Excel: Excel Problem, format cells, excel sheet

format cells, excel sheet, richard florida: Pravin In your first question I am not sure if you want the numbers to be horizontal or verticle within the cell or horizontal from left to right within the same row, but in different cells, or--- verticle up and down within a column but in different cells....

Excel: Excel Problem, excel sheet, excel worksheet

excel sheet, excel worksheet, f3: Please ask only one question at a time. I don t understand your first question, so I ll answer the second one. To move or copy an excel worksheet from one workbook to another, open both workbooks, right-click on the tab of the worksheet you want to move...

Excel: Excel problem, exclamation point, thanks john

exclamation point, thanks john, microsoft access: I would first try to talk you into doing this in Microsoft Access. A database would be MUCH better suited to this than Excel. However, if you want to refer to a value on a different sheet, just include the sheet name in the reference followed by an exclamation...

Excel: Excel schedule, d2 mod, list of holidays

d2 mod, list of holidays, image columns: Hi Cathy, Follow the below steps... 1) In a new workbook, create a sheet called Employees and enter data in the following format... A B Employees Start Date John Smith 08-02-2012 Alan Brown 12-02-2012 2) Create/Rename a worksheet for Tasks...

Excel: Excel spread sheet, conditional formatting, course attendance

conditional formatting, course attendance, excel spread sheet: Here s what I would RATHER have you do: Enter in a PASS or FAIL and then use CONDITIONAL FORMATTING to color the cell. Don t color it yourself. Then, you can use the COUNTIF function to add up all of the PASS or FAIL grades. There are tutorials for...

Excel: excel spreadsheet taking up memory, cell a1, empty columns

cell a1, empty columns, excel spreadsheet: Hi Robin, See if the following steps help... For each Worksheet in the Workbook... 1) Press CTRL+END, and see if the chosen cell is beyond your data area. If yes, then you have not deleted your data well. Continue or check another worksheet. 2) Select...

Excel: excel spreadsheet taking up memory, excel spreadsheet, true data

excel spreadsheet, true data, storage space: You will probably find if you press Control and End at the same time that you get to a row far down and to the right of the end of the true data - excel remembers the last used cells, and this can take up storage space. I have a macro that can reclaim data,...

Excel: Excel VBA code runs faster when another application is selected, excel vba code, vba macro

excel vba code, vba macro, screen flash: Steve, My guess would be that is depends on the type of code you have written. If excel is the activewindow when your code is run, does the screen flash and show you everything the macro is doing. If so, this would suggest you are using code like worsheets(...

Excel: detect Excel version and Service pack, service pack 3, service pack 1

Excel: detect Excel version and Service pack, service pack 3, service pack 1, service pack 2

Excel: How to separate numeric & alphabet from the values in the cell?, fun code, isnumeric

Excel: How to separate numeric & alphabet from the values in the cell?, fun code, isnumeric, boolean

Excel: Spreadsheet Access

Excel: Spreadsheet Access

Excel: Find and copy and paste, activity id, vba

activity id, vba, spreadsheet: Hi Alexandre How are you? Please have a look at the following code: Sub copyactivities() Dim Rsource As Range, Rdest As Range Dim found As Boolean Set Rsource = Sheets(2).Range( A1:A10 ) Set Rdest = Sheets(1).Range( A1:A10 ) For I = 1 To Rsource.Rows.Count...

Excel: find and replace, custom filter, autofilter

custom filter, autofilter: Yes, you can turn on the AUTOFILTER and filter any column doing a CUSTOM filter for contains: dog or contains: cat . Your question is not complete enough to know what more you mean by find all this at the same time . What s that supposed to look like...

Excel: find and replace, vba code, spreadsheet

vba code, spreadsheet, cells: Murali You could write a VBA code that looped through a list of words and searched for them. The list could be hard coded into the program, or it could pick up a list of words from somewhere on your spreadsheet. You would have to explain what you wanted...

Excel: Finding the number of weekdays in a date range, workdays, m40

workdays, m40, ogilvy: Gail, I am not sure what you are trying to find. If you want the number of workdays in each month you can do it like this. Note: the arguments to Date are Date(year, month, day) A1: =Date(2012,1+row(A1)-1,1) change the parameters to reflect your...

Excel: Fit to Page not working, copy worksheets, zoom

copy worksheets, zoom: D:G and N:P winds up being 7 columns (DEFGNOP), not 6 -- my bad? or yours? Change to Sub SaleList() Range( D:G,N:P ).Copy Worksheets.Add.Name = SaleList ActiveSheet.PasteSpecial Range( A1:G100 ).Name = ActiveSheet.Name & !Print_Area Application.CutCopyMode...

Excel: Format Checkbox in Macro?, range b22, b100

range b22, b100, address c: Like so... you can play with the math on the first .Add parameter til you get the placement exactly how you want. Sub ADDCBOXES() Dim c As Range For Each c In Range( B22:B100 ) c.RowHeight = 15 With ActiveSheet.CheckBoxes.Add( b c.Left +...

Excel: format properties, numberformat, cursor

numberformat, cursor: So you want that when a user enters -1000 they see -1000.00? that is, you want a numberformat of #.00;-#.00 ?? If that s the case, then best would be to update the field when the cursor leaves it, like this: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)...

Excel: Formula for contain?, cell a1, pte ltd

cell a1, pte ltd, depreciation: i How to I make the cell return a False if the cell does not contain these words or word? /i The hard way: =OR(ISNUMBER(SEARCH({ Depreciation , Depn , Depreciatn }, A1))) But since everyone includes Dep in what they DO type, perhaps this...

Excel: formula problems, value error, null string

value error, null string, e6: Brent, the only way my formula would produce a #Value error is if one of the cells C6, D6 or E6 contained a #Value error or the formula has been altered. since you claim that with the data you show it is producing #Value, then I can only conclude it has...

Excel: formula problems, column c, d8

column c, d8, ogilvy: Jenn, I rearranged your data as shown in the attached image. I then built a table lookup formula that provides allows you to locate a value from the interior of your table based on the first column and top row values. the table is in A8:D13. ...

Excel: formula or vba?, column c, flight hours

column c, flight hours, vba: If the hours are in K3, put this in L3 and copy down: =50-(K3-(INT(K3/50)*50)) Or, if you want the value to be ZERO when the total hours is 50, 100, 150, then use this: =MOD(50-(K3-(INT(K3/50)*50)),50) or =MOD(50-MOD(K3,50),50...

Excel: Freeze chart on sheet, f9 key, ogilvy

f9 key, ogilvy, constants: Ana, If you select a series in the chart, you should see the formula associated with the series in the formula bar. It will be made up of a couple of ranges. Select each range (in the formula bar) and hit the F9 key which will evaluate that range and...

Excel: Freezing Rows, hi li, bunger

hi li, bunger, freeze panes: Hi Li, The answer is slightly different depending on you version of excel. In 2010 and I think 2007, the Freeze panes option you are looking for is in the view tab, where I think it is in the Window dropdown of 2003 and earlier. First, you will...

Excel: Generate a unique values and count of values recurrence, pivot table, column c

pivot table, column c, ogilvy: Hudas, Sub ABC() with worksheets( Sheet2 ).Range( B2 ).Resize(10,3) .formula = =if(Countifs(Sheet1!$A:$A,$A1,Sheet1!$B:$B,B$1)=0, N/A ,Countifs(Sheet1!$A:$A,$A1,Sheet1!$B:$B,B$1)) .formula = .value End With End Sub change the resize(10,3)...

Excel: Generate unique values and count of values recurrence, column c, recurrence

column c, recurrence, ogilvy: Hudas, I see no image attached. count of recurrence doesn t really say much to me. I will assume you want to count how many times that data appears on the original sheet. Then I would use =Countif(Sheet1!A:A,A1) in b1 of the new sheet. This assumes...

Excel: Help with advanced formla, array formula, sheet1

array formula, sheet1, universal term: Robert, will value always be unique? If so, then you can get the top five values with =Large(sheet1!$D$2:$D$100,row(A1)) then drag fill that down for 5 cells now assume the first cell with that formula is in D2 of sheet2 so in A2 of Sheet2...

Excel: "INDIRECT" formula, stock ticker symbol, cell b5

stock ticker symbol, cell b5, abbott labs: Joe, If B5 contains the sheet name, then you would use =INDIRECT( F:FINANCES[Frequencies Options.xls] &B5& !$C$2 ) but before you get too excited, if you have a chance to look at the Excel Help on indirect, you will see that Indirect won t work...

Excel: ListBox not performing as expected/required, Excell 2000, control toolbox, manual input

control toolbox, manual input, c13: When crossposting always provide links to the other threads so any one can search the other threads to see if the issue has been resolved elsewhere. The last thing you want to do is waste the time of people helping for free on something you ve already resolved,...

Excel: Very long formula that needs to be put in VBA, ar 2, wasting my time

ar 2, wasting my time, r3: Hudas, Updated: So if you already had a solution, you just wasted my time asking me the question. Please take me off the list of people you ask questions of. I have no interest in wasting my time. Regards, Tom Ogilvy if you put all 19 conditions...

Excel: losing formula when using macros, macro range, nimbus

macro range, nimbus, dashboard: I cannot see the screen shot, and the macro has lines that aren t needed - but if I understand the process, you are moving a months data off the record and another month on - my process for doing this would be copy the 11 months you need to keep to a known...

Excel: Macro copy values from page A to page B, copy worksheets, example worksheets

copy worksheets, example worksheets, macro in excel: Hi Paul, Your code is very confusing for me to follow. Please reply back again with your desired action post Paste only, and only do one set of action. Alternatively upload a sample excel file somewhere. I can give some general guide though for a loop.....

Excel: Macro- Msg Box with Yes/NoOption, cell a1

cell a1: Odd, since just clicking in A1 and pressing DELETE is the same number of steps as running a macro and answering a question...? Anyway: Sub ClearCell() If MsgBox( Clear A1? , vbYesNo, Confirm ) = vbYes Then [A1].ClearContents End Sub ...

Excel: Macro- Msg Box with Yes/NoOption, cell a1, ogilvy

cell a1, ogilvy, typo: Jayendran Looks like I had a typo in my code. vbYeNo should be vbYesNo. Corrected code below. Sub ABC() dim ans as Long ans = Msgbox( Delete contents of A1? ,vbYesNo) if ans = vbYes then Range( A1 ).ClearContents end if End Sub ...

Excel: Macro to save monthly spreadsheet, filepath, inputbox

filepath, inputbox, format date: Gail, in you posting/question, you filepaths don t have any delimiters - so it is hard to tell what the file paths actually are. You certainly can put up a getsaveas dialog and have the user select where to save the file and what name it should have. ...

Excel: Macro to sort columns, copy worksheets, page orientation

copy worksheets, page orientation, landscape 2: Sub Shawn() Range( A:C,F:F,J:K ).Copy Worksheets.Add.Name= ENTER NEW NAME HERE ====answer #2 Activewindow.Pagesetup.Orientation = xlLandscape ====answer #1 ActiveSheet.Pastespecial Range( A1:F50 ).Name = Activesheet.name & !Print_Area...

Excel: military time spread sheet, patient discharges, cell b2

patient discharges, cell b2, patient admission: Laura Time is always tricky to work with because of the hours days, etc are not easy for us to convert mentally to what is needed for excel to do the calculation correctly. It becomes easier if you think the way excel does. Excel converts each date and...

Excel: Modify VBA to Autofit Merge Cells and Wrap Texts, target cells, rowheight

target cells, rowheight, hello tom: Jn, sure do alt+F11 to get to the vbe. Then with your workbook as the active project (selected in the project explorer) do Insert= Module in the menu. This is a general or standard module. Put your code here (or if you already have one, you can put...

Excel: Moving Data, cell a1, cell a2

cell a1, cell a2, cell references: I guess my macro is too complex. If it simply has to copy all rows (except the top one) to another worksheet, use this: Sub CopyEntry() With Worksheets( Sheet1 ) .UsedRange.Offset(1).Copy Destination:= _ Worksheets( Data ).Range(...

Excel: Multicriteria, cheers, yahoo

cheers, yahoo: * ok, that s a start but I would need COMPLETE information on what data is in what column, and what your criteria actually is, and how the layout of everything is. If you want to send me a sample workbook at ipisors at yahoo .com I will look at i...

Excel: multiple conditions per cell, cell a1, return zero

cell a1, return zero, specific s: Jenn, Normally if a cell is displaying say 85%, the actual value in the cell is .85 anyway, assume cell A1 holds the percentage as stated above: 0.85 or a different amount =if(A1 =.85,150,if(A1 =.75,105,if(A1 =.65,75,if(A1 =.55,.45,0)))) now...

Excel: Multiple Sub Totals in Pivot, pivot table, field settings

pivot table, field settings, ogilvy: Prabhat, right click on the cell that has HYD in it. This will pop up a menu. Towards the bottom select field settings. In the field settings dialog, choose Automatic under subtotals. This should default to sum. If it doesn t, then select custom...

Excel: Mutiple If functions, ogilvy, expert advice

ogilvy, expert advice: Shawn, the conditions are evaluated from left to right, so the if B9 is less than 60, it is handled by the first condition and never makes it to the second condition. That means you don t need to check if it is greater than 60 - just if it is less than...

Excel: I need help, sumif, excel sheet

sumif, excel sheet, e10: As stated before, do this: 1) Put this formula in E1: =SUM(A1:D1) 2) Now select cell E1 and press CTRL-C to copy it 3) Select E2:E10 and press ENTER. See how the formulas pasted in have adjusted themselves? Second method: 1) Put this formula...

Excel: Need a macro to close file without saving, ogilvy, circumstances

ogilvy, circumstances: Jayendran Activeworkbook.Close Savechanges:=False as the last command should not elicit a prompt. if the code is in the workbook being closed, then Thisworkbook.Close SaveChanges:=False if you stil have problems (since you only showed me...

Excel: Nested formula in Excel, vlookup function, column c

vlookup function, column c, raws: Md. Ferdous You need to look into using the VLOOKUP function. Basically, if your data is in columns A and B and you want to have a formula in column E1 the returns the name associated with the service number in D1 the formula would be: =VLOOKUP(d1,a1:b600,2,false)...

Excel: Nested IF, AND, OR Statements, cell b1, cell d1

cell b1, cell d1, cell a1: Tony, This meets your stated requirement =IF(A1 TODAY(),IF(OR(B1=1,B1=2), Ready , Compliant ),IF(AND(C1= Y ,D1 TODAY()), Compliant , Not Ready )) However, I don t know if it will do exactly what you want. For example, if A1 is greater than today...

Excel: Next without For error - How to Escape a nested loop?, vlookup, workzone

vlookup, workzone, skus: Rich For i 1 to 10 For j 1 to 3 Something with .Cells(i,j) Set range X = .FIND If x = Nothing Next j ---- you can just skip this line completely, it is what is causing your error Else -Do Something...

Excel: Number Combination Question, number combinations, question question

number combinations, question question, number combination: Courtney, Your question isn t totally clear but below I state what I understand it to be: Not sure if your question is asking for ordered data with at least two even and sum between 121 and 200 inclusive or not - but here is the answer to that question....

Excel: Number to Words, target value, sheet tab

target value, sheet tab, string 1: That s now what you originally asked. you said 123 should be ONE TWO THREE, not One Hundred Twenty Three. Very different. There are lots of resources here: http://www.bing.com/search?q=excel+numbers+to+words&qs=SC&form=QBLH&pq=excel%2520numbers%2520to%2520workd&sc=1-22&sp=1&sk...

Excel: P&L Statement, copy and paste, excel sheet, sheet1

excel sheet, sheet1, grabs: I have an existing macro that parses the data from one main sheet to many other sheets based on the values in a column you determine, typically column A, but that s easily editable. The macro not only parses the values to the sheets, it creates them for you,...

Excel: password protect worksheets, owner names, excel spreadsheet

owner names, excel spreadsheet, xls file: I have no experience of using Excel on the web, but in vanilla Excel, you can do this with a macro - Firstly, the easiest would be to display a userform on startup which asks for his name and password -- the name should be a listbox or combobox listing the...

Excel: Pivot tables in Recorded Macro, mypivot, pivot table

mypivot, pivot table, pivot tables: Change this: Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ MAR1998R!R1C1:R11393C29 , Version:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:= , TableName:= MyPivot , DefaultVersion:=xlPivotTableVersion10...

Excel: Populate cells from contents of listbox., control toolbox, sendkeys tab

control toolbox, sendkeys tab, data validation: OK, I ve looked at this and tested it and it seems to work fine - I THINK you have probably set a linked cell in your listboxx - the linked cell property should be kept blank as otherwise it will update the linked cell - hope this makes sense and resolves...

Excel: Populate Specific Cells Based on a drop down list selection, data validation, tool number

data validation, tool number, peoples names: Hi Martin, Your task is simple to understand but tedious. See... www.contextures.com/xldataval02.html ...for a tutorial on how to populate a drop down based on another drop down, using data validation and the Indirect formula. Basically for each...

Excel: Postal Code Conversion, code conversion, numeric characters

code conversion, numeric characters, numeric values: Hi Paul, Unless I am missing something or you are extracting the number from the portal code and storing elsewhere, all alpha numeric values are stored as text by excel. You can also try something like this... Range( A1 ).NumberFormat = # Shahi...

Excel: Prevention of data duplication, data validation, h50

data validation, h50, cell a1: Use Data, Validation and then custom - enter this formula (in A1) =COUNTIF($A$1:$H$50,A5) 2 OK this, then select all cells in the range a1:h50 and use data, validation again - it will ask if you want to extend this validation to all cells in range -...

Excel: Protecting Cells, format cells, knowledgeable user

format cells, knowledgeable user, correct option: Gary, by default, when you open a new workbook, all cells are marked as locked. So you should decide what you want available to the user and which cells you don t. You then select those cells you want available and right click, choose format cells, go...

Excel: this is my question, hobby activity, blank rows

hobby activity, blank rows, dynamic ranges: I wouldn t worry about named ranges - I use (generally) a COUNT or COUNTA worksheet function to return the number of lines on a sheet which can then be used in a for..next loop - as long as you have no blank rows this works fine. You could also use this in...

Excel: Question about "*.xls", folder address, red apple

folder address, red apple, cell values: Hi Ammon, The dir(filespec) command returns the first file in the folder meeting the filespec. Subsequent files are returned by using dir(), e.g.: Dim sFileName as String sFilename=Dir(Cells(2, 7).Value & Cells(i, 4) & & *.xls ) While Len(sFileName)...

Excel: replace data entered with...., target range, format cells

target range, format cells, sheet tab: In your row 3 matching table I see D listed twice. Shouldn t one of those be B ? Assuming that is the case, then this macro would do as you ask: 1) Right click on the sheet tab and select VIEW CODE 2) Paste in this event macro: Option Explicit...

Excel: rolling last 12 month calculation, 12b, aidan

12b, aidan, spreadsheet: It isn t clear from the question what you need to do - you could use the match worksheet function to get a relative column reference if you have dates, and perhaps use the indirect function to do the calculations you need - but without knowing what you are...

Excel: if scenario help, cell d1, cell c1

cell d1, cell c1, vlookup: Hello Karen, Thank you for your question and the opportunity to assist you. This can easily be done in Excel. Place the formula below in Cell D1. =VLOOKUP(C1,A:B,2,0) Then input a value from Column A into cell C1 and it will return the corresponding...

Excel: search within drop down lists, cell c5, sheet tabs

cell c5, sheet tabs, group mode: Kathy, You haven t said which version of excel and this is one feature that is affected by version. If you have excel 2007 or later, at the right side of the home tab on the ribbon, you have the find command. If you click on the FIND button, it will put...

Excel: sending email from excel list, word mail, mass email

word mail, mass email, booking details: Hi Nitin, Did you take a look at the links I suggested? If you organize your data in rows and columns, then word can do the mail merge for you. You can t strictly automate because there is nothing to automate as word takes care of all the composing and...

Excel: Sending multiple email with different attachements., excel macros, quotation mark

excel macros, quotation mark, john doe: Hi Luis, VBA usually expects you to give it a string parameter within double quotes. If you want to include a double quote in your string, type TWO double quotes together like this.... MyString = This double quote --- will show as one As for...

Excel: Separate each number of a big number to different cells, decimal character, character number

decimal character, character number, d3: Hi Parantapkumar, So, I tried this: A1 9876543219876543219876 A2 2 A3 =(A1/A2) Result = 4.93827E+21 B3 =MID(A3,1,1) Result = 4 C3 =MID(A3,3,1) Result = 9 D3 =MID(A3,4,1) Result = 3 E3 =MID(A3,5,1) Result = 8 This worked for me....

Excel: Separate each number of a big number to different cells, feature text, richard florida

feature text, richard florida, copy paste: Parantapkumar You will need your number in A3 to reflect a value, currently it is reflecting the formula +A1/2. You can do this by using copy/paste special/values and pasting the value into the same cell (A3) or into another cell of your choice. Once...

Excel: How to separate numeric & alphabet from the values in the cell?, vba macro, unwanted characters

vba macro, unwanted characters, question thanks: Hi Shoaib, Below is the code you will need to insert in a new Module. You can call it in any worksheet as a formula in the following format... =GetFromAlpha(B1,TRUE) --- To extract numbers =GetFromAlpha(B1,FALSE) --- To extract characters Function...

Excel: How to shift real time data from one cell to another after a specific time period, real time data, time prices

real time data, time prices, a10: Run this code to initiate the timer. Put the word Stop in cell M1 to stop it. Sub Doit() If Range( M1 ).Value = Stop Then Exit Sub Need some way to not have this go forever. Application.OnTime Now + 5 / 1440, Doit Range( B3:B10 ).Copy...

Excel: How to shift real time data from one cell to another after a specific time period, real time data, exact steps

real time data, exact steps, time prices: Hi Noor, Excuse me, but I don t have any external data to test my solution or to give you the exact steps, but here goes from memory. This is a two part solution. If you see the connection properties for the external data, there should be a property that...

Excel: % sign appears spontaneously, format cells, keyboard shortcut

format cells, keyboard shortcut, tax prep: I think you MAY have used the keyboard shortcut to set % formatting (see http://spreadsheets.about.com/od/tipsandfaqs/qt/81116percent.htm) - however, not a major problem - select the cells you want to alter (as a block - so if it s an entire column or set...

Excel: Solver for Investment Calculations, www google co uk, google co uk

www google co uk, google co uk, acquisition price: I m not experienced at all with this form of calculation, but the link http://www.google.co.uk/#hl=en&output=search&sclient=psy-ab&q=excel+irr&psj=1&oq=excel+irr&aq=f&aqi=g10&aql=1&gs_sm=3&gs_upl=177l1914l0l2925l9l5l0l0l0l0l1183l4486l7-4l4l0&bav=on.2,or.r_gc.r_pw.r_qf.,cf.osb&fp=fc0c48fd1a6589ca&biw=1024&bih=593...

Excel: sort, google co uk, excel function

google co uk, excel function, excel sheet: IF the amounts are unique, then the use of the SMALL or LARGE functions would enable you to sort in ascending order, and using the match function you could then return the rest of the data - I would direct you to http://www.google.co.uk/#hl=en&sclient=psy-ab&q=excel+sort+using+formula&psj=1&oq=excel+sort+using+formula&aq=f&aqi=g1&aql=1&gs_sm=3&gs_upl=386557l389542l1l391755l24l10l3l0l0l2l1835l8640l5-1.2.1.3l7l0&bav=on.2,or.r_gc.r_pw.r_qf.,cf.osb&fp=fc0c48fd1a6589ca&biw=1024&bih=593...

Excel: Spltting text into columns, helper cells, match 2

helper cells, match 2, word hope: The formulas would be unwieldly if they were done in one shot -- that is, the formulas for B1:F1 would be successively longer. I have the solution using helper cells, but I d need your email address. You can send me your email address at mine: bobumlas@hotmail.com...

Excel: Spreadsheet Access, pivot table, strong points

pivot table, strong points, basic knowledge: Hi Chan, It is very much possible to protect a worksheet with password and it is in the General tab.... titled Protect Workbook/Worksheet . However, anyone with some basic knowledge in vba or excel can easily unlock it and security is not one of Excel...

Excel: Spreadsheet name, spreadsheet

spreadsheet: As long as the workbook has been saved at least once, then put this formula in any cell and it will tell you the name of the current u worksheet /u : b =MID(CELL( filename , A1), FIND( ] , CELL( filename , A1)) + 1, 256) /b Put this formula...

Excel: If Statement, debbie brown, dlv

debbie brown, dlv, ogilvy: Debbie Brown, say the value is in C3 in another cell =IF(AND(COUNTIF(C3, *DLV* ) 0,COUNTIF(C3, *CNF* ) 0), Y , N ) That finds the substrings DLV and CNF in the specified cell and if they are both there, it returns Y else it return N ...

Excel: If statements, ebay store, kwow

ebay store, kwow, wholesale price: if the quantity is in column F then =SUMIF( Sale Tracker - Aussie Fragrance !$G$2:$G$1000, Eau De Toilette , Sale Tracker - Aussie Fragrance !$F$2:$F$1000) will sum up the quantities in column F where the row contains Eau De Toilette in column...

Excel: submit button data workbook, sh2, sh1

sh2, sh1, d19: Samia, if the second workbook is open, then assume its name is Master.xls then you would replace Set sh2 = Worksheets( Sheet2 ) with Set sh2 = Workbooks( Master.xls ).Worksheets( Sheet2 ) put in the actual name of your destination workbook...

Excel: Sum of total within date?, cell c1, regional formats

cell c1, regional formats, sumif: Chan, Sumif is the best function from my perspective =Sumif(A:A, = &C1,B:B)-Sumif(A:A, &D1,B:B) would be the basic approach for both. for a month, the month of January 2012 you would have C1: Jan 1, 2011 D1: Jan 31, 2011 that gave...

Excel: Sumif non-adjacent cells in an array, budget columns, richard florida

budget columns, richard florida, j2: Marie Assuming you want to sum the budget amounts by row,I think it will be simplest if you construct a series of if formulas that add all of the budget amounts where the adjacent actual amount is greter than zero. something like this-- =IF(A2 0,+B2,0)+IF(D2...

Excel: Text to Column - each 195 characters, text numbers, break point

text numbers, break point, ogilvy: Gerard, You could do it with text to columns as put in your subject. You would have to figure out where the break point is for each column. I assume you don t want to do that. so you can use formulas. Assume your text is in A2 then in B2 =MID($A2,(COLUMN()-2)*195+1,195)...

Excel: textbox format %, control source, source link

control source, source link, thanks in advance: Caroline, You need to break the control source link between the cell and the textbox - so remove the controlsource property - clear it. then manage the link between the two with code. Once you break the link then you code (as modified below) should...

Excel: Top 20 Data Return, array formula, david answer

array formula, david answer, best score: david Dates in A1:A5000 ID s in B1:B5000 Scores in C1:C5000 =INDEX($C$1:$C$5000,MATCH(LARGE(IF(($B$1:$B$5000= ID3 ),$A$1:$A$5000),1),IF(($B$1:$B$5000= ID3 ),$A$1:$A$5000),0),1) entered with Ctrl+Shift+Enter Tested and worked for me when properly...

Excel: Unhide one worksheet at a time, tsh, keegan

tsh, keegan, ogilvy: Keegan, with only the information you provided, I would suggest Private Sub CommandButton2_Click() Dim sh as worksheet Dim shName as String for each sh in thisworkbook.Worksheets if sh.Visible = xlSheetHidden or sh.Visible = xlSheetVeryHidden...

Excel: Use drop-down to populate another drop-down, granny smith, data validation

granny smith, data validation, inventory purposes: Hi, To protect your data and controlling data entry, you can try out Excel s Workbook and Worksheet protection feature. It should be in the General tab under Formatting. In addition, I believe you can lock Excel for editing as follows... 1. Press CTRL+O...

Excel: Userform Cell Fill, column c, keegan

column c, keegan, ogilvy: Keegan, If I can depend on column C to be able to tell me the next place to put the data Private Sub Confirmbutton_Click() make sheet active ActiveSheet.Activate Determine Empty Row Dim emptyRow as Long emptyRow = cells(rows.count, C ).End(xlup).row...

Excel: Using End(xlDown) to select a range, better solution, two rows

better solution, two rows, offsets: Carry, Sorry - I wasn t focused on anything but the question at hand. NewWb.Worksheets(ar(i)).Range(SourceRange).PasteSpecial xlValues causes an error because SourceRange is a reference to a cell and as an argument to Range, it is not an acceptable...

Excel: Using IF statement, vlookup, e2

vlookup, e2, r2: Something else is going on, so I m not sure ... Try this to see that your assumption is not accurate. in A1 (new sheet) enter = in B1 enter =A1= and you ll see TRUE. So E2 CAN equal . It doesn t depend on whether there s a formula in it. If you...

Excel: #Value! issue, format cells, forward slashes

format cells, forward slashes, best solutions: Hi Paul, Firstly we are here to help, so no need to apologize. Regarding the question... I feel you can easily make do without VBA. I consider the best solutions to be the ones without complicating with VBA and let Excel manage all the data handling...

Excel: How to get the values one by one in excel using Advanced User Form?, excel sheet, shahid

excel sheet, shahid, runtime error: Hi Shoaib, It s going to be very tedious to teach you VBA Form designing from scratch, so I am going to refer you to two very good and easy to follow tutorial. Try the first link, and then do the second after completion of the first. http://www.contextures.com/xluserform01.html...

Excel: VBA: on-click / active cell with a vlookup, target address, vlookup

target address, vlookup, cell address: Hi Jon, Below is the code you will need. It goes in the Worksheet Code section. I have made comments to explain you the code. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Column #1 is column A If Target.Column = 1 Then Use address...

Excel: VBA conditional formatting, conditional formatting excel, chip pearson

conditional formatting excel, chip pearson, shades of green: Clive, Let s start with the simplest approach and see if that is what you want. Sub colorCells() Dim r As Range, cell As Range, cellF As Range change the next to reflect the range you want to process Set r = Range( A3:A50 ) For Each cell In r...

Excel: VBA error - Select method of Range Class Failed, requisition number, number sheets

requisition number, number sheets, command button: there is nothing obviously wrong with your code, though a tidier version which would get round the problem would be Private Sub CommandButton1_Click() lookup last number FinalRow = Sheets( List ).Range( A20000 ).End(xlUp).Offset(1, 0).Row...

Excel: VBA help: Clearing corresponding data in row, logical value, column c

logical value, column c, q500: Lindsay, I assume by True , the cell holds the logical value True and not a string representation of that logical value. If it is a string representation, then change If cell.value = True then to If lcase(cell.Value) = true then Sub...

Excel: VBA for inserting multiple rows, rng, snippet

rng, snippet, cells: Sub Charles() Dim Rng As Range, Num As String, ans As Integer, TheRow As Long On Error Resume Next Set Rng = Application.InputBox(prompt:= Please Select Start Row , Title:= Insert Rows , Type:=8) Num = Application.InputBox(prompt:= Please...

Excel: VBA to paste below active cell, sudharshan, sh1

sudharshan, sh1, single quote: Sudharshan Sub ABC() Dim sh as worksheet, sh1 as worksheet Dim r as Range, r1 as Range set sh = Activesheet set r = sh.Range( A1:M25 ) set sh1 = sh.Next find next cell set r1 = sh1.cells(sh1.rows.count, A ).End(xlup).Offset(1,0) r.copy r1 end...

Excel: VBA postal code issue, seperate cells, segment deals

seperate cells, segment deals, code segment: Hi Paul, Sorry, my mistake, you need to remove the extra dot and account for the extra column here also.... .FormulaArray = =MID(SUBSTITUTE(UPPER( & . Worksheets(“CodeTest”).Range(“D9”).Address & ), , , ),COLUMN(A:F),1) ...in between &...

Excel: vlookup, exact formula, correct result

exact formula, correct result: Please give me the EXACT formula which is returning a #N/A when the workbook is closed, and also the EXACT formula which is returning the correct result when the workbook is open. It should work fine if the workbook is open or closed, so I m guessing there...

Excel: vlookup functionin and pop up input text box, blank cells, conditional formatting

blank cells, conditional formatting, red arrows: Hi Amit, I think we can do some solution using VBA macros and DataForm, however, this will be a bit tedious and it d be better if you upload your file somewhere and I can update it for you. Although, a better and simpler option would be to use Conditional...

Excel: vlookup functionin vba, vba code, vlookup

vba code, vlookup, work books: What have you tried? I find it odd that a working form using VLOOKUP formulas, perfect approach, needs to be rewritten as VBA, you said it was working? Anyway, what have you tried so far? Show me the code(s) you re working on and ask specific questions....

Excel: Whats the difference between these two?, msgbox, hard time

msgbox, hard time, vb code: Hi Howard, See my comments below... Beginning of a section of code Sub Inserter() Inputbox() shows a box with a comment as mentioned and asks user for input norows = Application.InputBox( How many rows to insert? , Type:=1) checks mentioned...

Excel: Write a sheet's Private Sub from Module, change occured, level equivalent

change occured, level equivalent, target: Ana, the easiest way to do this is to use the workbook level equivalent event. For example, the equivalent to the change event is the workbook level sheetchange event. It passes in a variable Sh that is a reference to the sheet where the change occured....

Excel: If Then, cell a2, cell a1

cell a2, cell a1: Hello Mark! Lets say in cell A1 you have either Y or N and in cell A2 you want to convert it to either 1 or 2. Then in cell A2 you type the formula as shown below: =IF(A1= Y ,1,IF(A2= N ,2, WRONG INPUT )) Hope this will help you. Please ask me further...

Excel: 2 Excel questions, pc tab, master template

pc tab, master template, wbk: Jim, Just a typo. The Y2 should just be Y If Application.CountA(.Range( A2:Y2 & .Rows.Count)) 0 Then should be If Application.CountA(.Range( A2:Y & .Rows.Count)) 0 Then for example, this code works fine: Sub ABCD() With ActiveSheet...

Excel: What does Activecell.offset function do, isempty, msgbox

isempty, msgbox, newbe: Hi Shobin How are you? ActiveCell.offset(0,1) refers to a cell one row down and o column to the right of the current cell. E.g. If you are in A1 ActiveCell.offset(1, 0) refers to A2 ActiveCell.offset(0, 1) refers to B1 ActiveCell.offset(1, 1) refers...

Excel: Addind cells with USD, len, cells

len, cells: PROBABLY the quickest way would be to do a search and replace - type USD in the find box and nothing in the replace box, then do replace all - you should then have workable figures - you can also use a formula if needs be, but that s more complicated - it...

Excel: Adding blank lines in excel, blank row, blank lines

blank row, blank lines, step 1: I don t remember if the original request required it not be a VBA solution, but here s a pretty fast one: Sub InsertAtBreak() TheColumn = 1 ===change this to the column of the machine type For i = Cells(Rows.Count, TheColumn).End(xlUp).Row...

Excel: adding to my last question, stock market data, web query

stock market data, web query, web source: The amount of data doesn t matter based on my previous answer - depending on the data you are getting, you may be able to automate the process of getting this - if it comes from a web source, then you could use a web query to embed the data into the sheet...

Excel: addition of multiple currencies, euro sgd, total values

euro sgd, total values, thanks in advance: Shiv, since you didn t state that and you showed only one formula, I assume you wanted that formula to add up the column of values. If you just want to do row by row in K2: =SUMIF($D$2:$J$2, USD ,$C$2:$I$2)& USD, &SUMIF($D$2:$J$2, SGD ,$C$2:$I$2)&...

Excel: Annualized rate of return, annualized return, mirr

annualized return, mirr, rate of return: In range A2:A13, enter the dates that your payments are made, e.g. 1/1/2001 through 1/1/2012. In range B2:B13, enter the payment activity for each date, i.e., -1000000 in each of these 12 cells. In cell A14, enter the date that you receive the money back,...

Excel: Automatic Cell Color Change Based on Date, cell b2, conditional formatting

cell b2, conditional formatting, yellow 6: * I don t follow the first part of your question. What s your actual definition, then, of what you want for the dates? However for the color, assuming that your due date is in column B, select cell B2, then go to conditional formatting and enter a...

Excel: too big of a file.., emails addresses, first steps

emails addresses, first steps, clipboard: Hi Gabe, Have you by any chance opened the files in separate Excel sessions? Easiest is to first open Excel, then use File, Open to open both files. Copying and pasting should just work. If not, probably some add-in is zapping your clipboard when you switch...

Excel: Call schedule, excel data validation, conditional formatting

excel data validation, conditional formatting, sun days: Hi Dan, I d rather you try some design and ask me for specific helps / problems. Anyway, I have shared some ideas below... -18 team members Google for Excel Data Validation . You can create an easy to pull list of employees. For most of your queries...

Excel: change of cell format by due date, conditional formatting, e mail

conditional formatting, e mail, due date: Conditional Formatting in Days is also possible in Excel 2007. Conditional Formatting - Set rule as =TODAY()-$F$4 7 (IF your date is in F4 and you are looking for alert 7 days before). Similarly you can set alert for 30 days etc. As for sending e-mail...

Excel: change font color, change font color, r2a

change font color, r2a, yellow font: Pete, If the value in any of the three cells is not found in C7:C11 or F7:F11, no change is made to the font - which would be the same result as if the value was found and it had 1st to the left. You didn t really address that situation but just so you...

Excel: Changing text to all Upper case, sum function, upper case

sum function, upper case, a3: If you type =UPPER(A3) into a cell and it s actually displaying =UPPER(A3) then you might have the SHOW FORMULAS option turned on. Do ANY other formulas work? Try putting a SUM function in and see if you see that too. If that s the case, click on the FORMULAS...

Excel: check compare and fill the data in desired cell, valid references, sheet names

valid references, sheet names, c number: Amit, Yes you may send me the files - but if you tried the solution I suggested, then let me know what you found unsatisfactory otherwise I will just put that solution in myself again and you might be disappointed again. If you just couldn t figure out...

Excel: Checkbox click count, command button, empty case

command button, empty case, case 2: Hello Svein, Yes it is possible. Here is some code that illustrates how to do it. Private Sub CheckBox1_Click() Static ClickCount As Integer ClickCount = ClickCount + 1 Select Case ClickCount Case 1 code or macro to...

Excel: Combining Dates/ Times/ Text, internet column, basic excel

internet column, basic excel, column c: yes, dates and times in Excel are secretly numeric and then displayed and Dates and Times using formatting. If you are going to concatenate these values you have to apply your own formatting to maintain it. Try this: =TEXT(A2, M/D/YYYY ) & @ ...

Excel: Combining Dates, internet column, basic excel

internet column, basic excel, column c: I am guessing that direct, unqualified/unformatted concatentation is what s displaying to you a string of numbers , or some such thing. Like: =A1 & @ & B1 & & C1 right? Try this type of method: =Text(A1, mm/dd/yyyy ) & @ & Text(B1,...

Excel: how to compare two workbooks with multiple worksheets, spreadsheet tool, personal laptop

spreadsheet tool, personal laptop, xlstart: Hi Kuldeep, Did you check under.. Tools -- Add Ins? You can even manually install the Add-In... To install the add-in, complete the following steps: 1.UnZip the Exe file using 7Zip or WinZip, etc. in any folder 2.On the Tools menu in Excel, click...

Excel: Compile error: Ambiguous name detected: Workbook_Open, ambiguous statement, getsetting

ambiguous statement, getsetting, ambiguous name: slightly difficult to debug, but I ve copied all the code you have given me to a blank workbook and it all works fine - I would GUESS you may perhaps still have two macros called Workbook_Open - you only need one of them - two will generate the error. If...

Excel: CONCATENATING WORKBOOKS, vba procedure, table wizard

vba procedure, table wizard, visual basic for applications: If I had this issue myself, I d create the Master Workbook in which user would list the locations of the individual workbooks, and then run a visual basic for applications procedure ( vba or macro ) that would open each of the individual workbooks and copy/paste-value...

Excel: Conditional Formatting, space show, check balance

space show, check balance, color background: No, the cell format will check the value the cell effectively contains - as you mention a formula, I would suspect that it is not returning exactly zero - I would PROBABLY amend the formula to round the value to two decimal places, which should then mean that...

Excel: Consolidation, files folder, consolidation

files folder, consolidation: WORKBOOKS TO 1 SHEET STACKED Here s a macro for collecting data from all files in a specific folder. a href= https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/merge-functions/consolidate-wbs-to-one-sheet Workbooks...

Excel: Consolidation macro, files folder, consolidation

files folder, consolidation: Not clear to me -- you have a folder named 03/01/2012 , etc? In each folder you have one file or many files? The macro should prompt for a date and open the one file in there? If many files in each folder, which file should it open? Is the master file considered...

Excel: convert large file to smaller ones, gabe gabe, paste option

gabe gabe, paste option, emails addresses: Gabe Highlight the cells that you want to copy. While your pointer is within the highlighted cell, right clic and select copy. Go to the workbook or worksheet that you want to paste it to. Make active the cell or the first cell of the area you want to...

Excel: Convert to number, ogilvy, apostrophes

ogilvy, apostrophes, apostrophe: Raja, General is number if the data is numeric. It is a very vanilla number which seems to be what you are going for. Otherwise, you want to use a format like you would use manually Cell.Numberformat = 0 or whatever is appropriate, but is should...

Excel: Converting mass/liquid units in Excel, mass units, allexperts

mass units, allexperts, shipping cost: You re right - you need a LOT of conditions for that to work right. That s going to mean several nested IF functions. First, here are some tutorials that will help you: IF Function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts...

Excel: copy from one file to another, gabe gabe, paste option

gabe gabe, paste option, emails addresses: Gabe Some people are experiencing glitch with excel 2007 when copying and pasting. This may be your problem, Here is a link that explains wht to do. http://www.ehow.com/how_6803270_copy-excel-2007-freezes-computer.html You may note that the reference...

Excel: Copy and paste desired range when a value is entered in certain cell., excel vba, kind reply

excel vba, kind reply, sheet tab: Private Sub Worksheet_Change(ByVal Target As Range) Dim Path As String, Newbie As Worksheet, WhoamI As Worksheet Path = D:DataExcel ===change accordingly If Target.Column 7 Then Exit Sub entered info in Col G If Application.CountA(Cells(Target.Row,...

Excel: How to copy portions of cells, paste values, transcription errors

paste values, transcription errors, richard nugent: Hello - First question/suggestion: Excel has an option to open text files directly so there s no need to copy/paste values into cell(s) directly. Go to Open and change the All Excel Files to Text Files in the drop-down box. Excel will take a quick...

Excel: copy a range and save it to a new book with desired cell content as the value enters in certain cell, excel vba, kind reply

excel vba, kind reply, source sheet: Amit Rajput, ===== Why ask the question if you don t look at the answer?? ======== make specific changes such as your specific folder. right click on the sheet tab where you will make your entries and select view code. Then paste in this code. It...

Excel: Copy sheets from many workbooks and paste them into one, basic questions, copy paste

basic questions, copy paste: Hi Kevin, Honestly such request really take a lot of time and effort, especially if you yourself don t know VBA. In such cases I would request you to record a macro and start from there. Open any one of the workbooks, copy-paste into a single worksheet...

Excel: Copy specific columns of data from text file to Excel cells, text functions, left

text functions, left, right: There are various TEXT FUNCTIONS that allow you to manipulate strings of characters within cells. LEFT, RIGHT, and MID will handle most of what you need. I cover them in detail in my a href= http://599cd.com/site/courselist/excel2010/expert/x1/Default.asp?key=AllExperts...

Excel: Copying data by use of array (VBA), array vba, cell b5

array vba, cell b5, customer number: Gerard, I don t have any test data, so test this on a copy of your workbook Option Explicit Sub CopyDataRight_TO() Dim r As Range, rr As Range Dim v As Variant, vv As Variant Dim v2() As Long Dim RangeCopyFrom As Range Dim RangeCopyTo As Range...

Excel: Count unique entries based on multiple conditions, field desk, client invoice

field desk, client invoice, good afternoon: Assume the data in your example is in A1:F4. In G2, enter the formula =IF(AND(C2= ESP ,D2= Field ,E2=2011,F2 3,F2 7,ISERROR(MATCH(A2,G$1:G1,0))),A2, ) and copy down range G2:G4. Then the in H2 enter the formula =COUNT(G:G) to get your result....

Excel: Create Dynamic Dependent Drop Down list, cell b2, data validation

cell b2, data validation, k25: It can be done, but takes quite a bit of setting up. Follow these steps. When you re done, hide the columns used as helper columns. In this case, that s K:S. 1 - in K1, enter =COUNTIF(F$2:F2,F2) ad fill down to K25 2 - in L1, enter =IF(K1=1,F2, ) and fill...

Excel: Data lookup, money recieved, b company

money recieved, b company, bbb: Hi Chan, 1. You could use VLOOKUP, but that will return the data for the first entry that is found. Better is to filter the table on the needed date. 2. Which date do you need if there are more than one payment? Again, sounds like a filter is better....

Excel: Data Validation..., entry cells, data validation

entry cells, data validation, cell b3: Maybe it s because I m working in Excel 2010 - but I tried it out before I sent the suggestion and even if the validation cell is in a column 3 chars wide, the validation drop down list, while not full width, is definitely wide enough to differentiate your...

Excel: Data Validation - return short version of item, data validation, excel programming

data validation, excel programming, string function: * No, without VBA I m not aware of any way to format text in an excel cell to only display the first N characters in a cell. Why not just require users to enable excel programming? All users at my company are trained that that is a basic requirement of...

Excel: Data Validation - return short version, data validation, string function

data validation, string function, question thanks: Hi, See the attached image and try to replicate. Don t worry about truncation, because if you click on the cell with the list, Excel will show you a Drop Down symbol and also the entire list. On the cell to the right of the Drop Down List cell, there is...

Excel: database, database sales, date city

database sales, date city, time ed: * when you sort (correctly), all the rows move at the same time, so this shouldn t be a problem. Are you actually talking about Excel or a database? Those are 2 totally different things. Excel should never be considered a database of any kind. try...

Excel: Database lookup macro to Form, cell b3, staff column

cell b3, staff column, sheet1: Chris, to fill the corresponding cells on the form you didn t give me any details on that, so I have just put in some sample code in that section. Beyond that, the code was tested and worked for me as I understand your requirement. Lightly tested...

Excel: database questions, format cells, database questions

format cells, database questions, database sales: I m slightly confused - if you sort the data, the whole dataset should sort - to ensure this DOES happen (if you have blanks) you may wish to select it all as a block first, but having done so it will all sort around. For the notes column, set it to wrap...

Excel: DataValidation -, data validation, string function

data validation, string function, dropdown list: Your choices are: 1) use a formula in an adjacent cell to show just the code. You know, the cell with the data validation in it... you can slim that cell down to a width just wide enough to show the code, maybe that will work. 2) use VBA to physically...

Excel: Dates, macintosh time, ogilvy

macintosh time, ogilvy, yyyy: Gail, N3: =text(AL, dd/mm/yyyy )& - &Text(M3, dd/mm/yyyy ) the numbers you see are what is actually stored in the cell to represent the data/dates. It is the number of days since 1 Jan 1900 and that is how Excel stores dates (1904 if you are on a...

Excel: dividing files, emails addresses, textpad

emails addresses, textpad, first steps: Hi Gabe, I would suggest you to try this. 1. Open up the large file 2. Open up a text editor such as textpad or notepad or wordpad (you would find notepad and wordpad in Start- Programs- Accessories) 3. Open up the smaller file 4. Copy (say about...

Excel: Drop down list/background color, target validation, target value

target validation, target value, sheet1: Harry, can you send me a copy of the workbook to twogilvy@msn.com and let me see what I can figure out. If not, then just send me an email and I will try to answer your question without seeing the workbook. (with the workbook I can at least do some testing)....

Excel: Duration, array formula, worksheet functions

array formula, worksheet functions, dddd: OHOOD Here is a formula that may work for you {=((+J50-J27)+1)-SUM(--(TEXT(J27:J50, dddd )= FRIDAY ))} The list from J27 to J50 contains dates So the first part of the formula says subtract the first date (which is represented by J27 from the last...

Excel: Dynamic Auto Filtered Drop Down List, data validation, dynamic auto

data validation, dynamic auto, sheet1: Send a sample wb to me at bobumlas@hotmail.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. -------------- Check out my book, This isn t Excel, it s Magic! on tips & tricks at: http://www.iil.com/Publishing...

Excel: enter cell# automatic, e13 e14, e7 e8

e13 e14, e7 e8, e4 e5: * Put it aside? I gave you a great answer - goodness, don t give up that easily!! Just ask and I will be happy to help - you right click any worksheet in the workbook, and go to View code. Once you re in that screen, go to Insert, Module. Inside the...

Excel: Entire workbook now protected, why?, book1, different things

book1, different things, colleague: Before I stray down the wrong path, I d like to be sure that I understand your question - because you mention protected sheets and protected workbooks which are different things. In Excel 2007, you have a multi-sheet workbook. Let s say: Sheet1 , Sheet2...

Excel: Excel 02, john kelsch, format cells

john kelsch, format cells, numerical format: John Kelsch, If you go into tools = options = view tab in Excel 2002, then under the windows options section there are a number of checkboxes. One says Zero values If you uncheck that, then cells producing a zero or containing just a zero will...

Excel: Excel 2007 cash flow statement for a year, cash flow statement, interval 3

cash flow statement, interval 3, bonus income: Singaravelu You are going to have to alter your input data and put a start date column in. Then use that to calculate the period of lapse between the month and the start date to determine whether a lapse of 3 months or 6 months has occured. The Monthly...

Excel: Excel 2007 - conditional formatting date ranges, column c, absolutes

column c, absolutes, cells: Hi Sarah, I would try using the CF of Highlight Cell Rules and Between You can highlight all of column C and enter your parameters for your first row (and remove the $ absolutes) In your sample data, assuming the dates start in row 2 you can...

Excel: excel - 2007 formullas, lm 30, richard florida

lm 30, richard florida, ashish: Ashish To separate the data in you may use the TEXT TO COLUMNS function. On your toolbar select data, then select text to columns, then select delimited, then select comma then click finish. This will put the data into cells A1, B1, C1. Hope this helps...

Excel: Excel 2007 Option buttons in a protected worksheet, button cells, option buttons

button cells, option buttons, microsoft equation: kwame, I could reproduce your problem. You need to unlock the cell that the option buttons link to (they should link to the same cell) with the sheet unprotected, right click on the cell that is linked to and choose format cell from the pop-up...

Excel: Excel 2007 - vlookup not returning correct result, vlookup, correct result

vlookup, correct result, dot co: Michelle, why not send me your table/workbook and I can take a look. Include both the table and the results you show above plus what results you expect. Color the cells where you think the results are not correct. (I am not asking for you to do a lot...

Excel: Excel 2010 Excel Solver Proble,, excel solver, number zero

excel solver, number zero, ogilvy: Frank, for the first problem, you have to type the number zero in the box on the right and choose value of. then you have to uncheck the Make unconstrained variables non-negative then the first problem worked for me. for the second I put...

Excel: Excel 2010 Macros, shahid

shahid: Hi Steve, Try this... 1) Replace your existing code with... Sub Macro5(ws As Worksheet) Macro5 Macro ws.Range( D19:D36 ).Select Selection.Copy ws.Range( F19 ).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,...

Excel: Excel, homework question, spreadsheet package

homework question, spreadsheet package, homework questions: Hello neeru, This sounds like a homework question. Sorry, but I do not answer homework questions. However, check this website, it can probably assist you. http://blog.ourindia.info/2012/01/ignou-mbs-ms-07-free-solved-assignment.html Good luck,...

Excel: Excel, vlookup formula, cell d2

vlookup formula, cell d2, absolute references: Hi Emily, Vlookup looks like the right tool for the job. A couple concepts to confirm. The unique ID (national number) in the sheet to match from (sheet2) must be the left most column of the data you are wanting to match and bring back. In your...

Excel: Excel 97, master document, column widths

master document, column widths, popup menu: Karen, After you paste links, then select Edit and then Paste Special. Once you get the popup menu select Formats and then click OK. Then go again to Edit and Paste Special. This time on the popup menu select Column Widths and click OK. This should...

Excel: excel - cursor moving slowly, cursor problem, range names

cursor problem, range names, zillions: If there are ANY formulas in the workbook which refer to the offending sheet, you should replace ALL formulas from = to x=, to avoid the zillions of #REF! errors when you delete the sheet. When all done, change all x= to =. Make sure the newly inserted sheet...

Excel: excel Data Base, database sales, date city

database sales, date city, zip phone: Hello Richard, Thank you for your question and the opportunity to assist you. If all of your data is contiguous, that is in columns next to each other, (For example, Columns A through I since you have 9 columns), when you perform a sort on one column,...

Excel: Excel file too large - can not paste into smaller ones, emails addresses, column header

emails addresses, column header, first steps: I m not sure what the problem is. If you want to copy just column A and paste it somewhere else, all you should have to do is select the column (click on the column header) and click COPY (or press CTRL-C) and then switch over to whatever other program you...

Excel: excel formula, budget worksheet, excel formula

budget worksheet, excel formula, original 3: Zee, What should I do to keep the formula and not show the zero in the result(M1) until there is a number either in L1 or F1? =if(len(trim(L1))+len(trim(F1))=0, ,if(isnumber(L1),L1,F1)) or =if(And(len(trim(L1))=0,Len(trim(F1))=0), ,if(isnumber(L1),L1,F1))...

Excel: Excel formula, dear sirs, vlookup

dear sirs, vlookup, matching number: Hi Fkwan, Assuming your data is exactly as depicted, below are your two formulas... Cell B4: =IF(SUMPRODUCT(--(E5:E7 =A4)*(F5:F7 =A4))=1, OFFSET($E$4, SUMPRODUCT(--(E5:E7 =A4)*(F5:F7 =A4),ROW(E5:E7))-4, 0), Not Found ) Cell C4: =IF(B4 Not Found...

Excel: Excel Formula 2003 Professional Edition, excel formula, pmts

excel formula, pmts, professional edition: i I want to allocate the terms $, by respective Cust ID, to each part number (for the respective Cust Id) based on what % that part number is for the total sales for that specific customer. /i That s not a question. That s a goal. So, allocate in...

Excel: EXCEL FORMULA EXPLANATION NEEDED, a286, e41

a286, e41, input table: Without the workbook, I can only give a general answer. It is multiplying three numbers together. More specifically . . . The first is cell F331 of the sheet named CS-DB - call that number X. The second is cell D35 in the sheet named INPUT -...

Excel: Excel formula required, target address, b1 b2

target address, b1 b2, excel formula: Prohibiting input requires either vba code or protection. Here s a protection (Cells locked) solution. Right-click the sheet tab, select View code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) C1 Then...

Excel: Excel "IF" formulas, question pool, d51

question pool, d51, datatable: The formula is much too complex, as it has too many nested brackets, but it would be much easier to do by use of a vlookup table =VLOOKUP(R61,DataTable,2,TRUE) Data table needs to have 2 columns with the MINIMUM value for each length in the first column...

Excel: excel hyperlink in mail, excel hyperlink, mail question

excel hyperlink, mail question, message mail: Hi Nitin, Both methods are fine. In the first method, we are simply taking two data from two different columns, but in second, two data from a single column. Thus in method one, we are not only saving a column, but user can also see visually what the link...

Excel: Excel Hyperlinks Within a Spreadsheet, excel hyperlinks, excel spreadsheet

excel hyperlinks, excel spreadsheet, database solution: It sounds like your file was corrupted somehow. I really couldn t tell you without seeing a copy of your workbook. HOWEVER, I can tell you this... if your workbook file is up to 300 sheets, it s really time to move up to a DATABASE solution, like a href=...

Excel: excel macro to email from list in sheet, macro cell, row c

macro cell, row c, outlook application: Hi Nitin, Before this line... Set OutMail = OutApp.CreateItem(0) Enter this... MsgBox Cells(cell.Row, C ).Value On running the code, if you get a message with the value in C column, then let me know what data you are getting. If you get something...

Excel: excel/macros, excel macros, repetitive tasks

excel macros, repetitive tasks, microsoft excel: Hi Katie, A macro can do almost anything. You can write macro s which: - automate complex repetitive tasks - Do calculations that are too complex for worksheet formulas - together form an add-in that helps doing your daily Excel work (see some examples...

Excel: Excel Quick Access Toolbar email icon, e mail address, www google co uk

e mail address, www google co uk, mail icon: There are ways, but they will involve vba - see http://www.google.co.uk/#hl=en&gs_nf=1&cp=23&gs_id=6&xhr=t&q=excel+2007+email+button&pf=p&safe=active&output=search&sclient=psy-ab&oq=excel+2007+email+button&aq=0&aqi=g1&aql=&gs_sm=&gs_upl=&gs_l=&pbx=1&bav=on.2,or.r_gc.r_pw.r_qf.,cf.osb&fp=c1a3451c1bcdad7e&biw=1280&bih=799...

Excel: Excel SUMIF multiple criteria, SUMIFS, COUNTIFS

SUMIFS, COUNTIFS: Bad news: The syntax you re using is not going to work (but you didn t need me to tell you that). The better news: In Excel 2003, there is a specific function, SUMIF (and its cousin COUNTIF), that can be used to SUM (or COUNT) against single criteria. The...

Excel: Excel - switching names in a column, Extract Data Using Formula Excel Sixthsense Excel

Extract Data Using Formula Excel Sixthsense Excel: Hi Virginia, Assume that your data is in A1 cell. A1Cell Antoine, Nancy and Doug Copy and paste the below formula in B1 cell =IF(ISERR(FIND( , ,TRIM(A1))), ,TRIM(MID(TRIM(A1),FIND( , ,TRIM(A1))+1,255))& &TRIM(LEFT(TRIM(A1),FIND( , ,TRIM(A1))-1)))...

Excel: Excel Text Manipulation, left, right

left, right, mid: There are various TEXT FUNCTIONS that allow you to manipulate strings of characters within cells. LEFT, RIGHT, and MID will handle most of what you need. I cover them in detail in my a href= http://599cd.com/site/courselist/excel2010/expert/x1/Default.asp?key=AllExperts...

Excel: Excel time calculation, d22, time calculation

d22, time calculation, c22: Gene, It sounds like you need to change this part of your formula MIN(Sheet1!D22,0.666666666666666)-MAX(Sheet1!C22,0.333333333333333) to IF(MIN(Sheet1!D22,TIME(16,0,0))-MAX(Sheet1!C22,TIME(8,0,0)) TIME(0,0,1),0,MIN(Sheet1!D22,TIME(16,0,0))-MAX(Sheet1!C22,TIME(8,0,0)))...

Excel: Excel VLOOKUP, excel, vlookup

excel, vlookup: Set up your columns like this: Sheet1: ZIP, Route#, NumAddresses Sheet2: ZIP, Route#, NumCustomers Use a a href= http://www.599cd.com/tips/excel/vlookup/Default.asp?key=AllExperts VLOOKUP FUNCTION /a to bring the NumCustomers into Sheet1. ...

Excel: excell problem, arabic alphabet, alphabet character

arabic alphabet, alphabet character, arabic characters: Hello Ziad, If I understand you correct, basically you want to type an Arabic alphabet/character in say cell D10, then automatically you want the associated English character to show in say cell D12. If the above is correct, do the following steps......

Excel: Expand a hyphenated series into a comma-separated series, string s2, string s1

string s2, string s1, comma separated list: Steve, select the cells you want to process and run the macro Master. Both procedures should be placed in a general/standard module: Sub Master() Dim r As Range, cell As Range Set r = Selection For Each cell In Selection If Len(Trim(cell))...

Excel: long to wide transformation with of panel data with many variables, variables

Excel: long to wide transformation with of panel data with many variables, variables

Excel: Filling Customer Numbers, formula v1, customer numbers

formula v1, customer numbers, ubound: Set rrS = Cells(v2(i), 1) would get the row number of the cell that has the account number/group number found at the start an account or group. so say that row is 10. We have Cells(10,1) which is A10 now if I did Range( A10 ).Cells(1,1) I get...

Excel: Filtering and consolidation, pivot tables, daily basis

pivot tables, daily basis, single database: best to do work like that from a single worksheet. Here s a macro for merging data from multiple sheets into a consolidation sheet where the source sheets are the same layout. a href= https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/merge-functions/sheets-to-1-sheet...

Excel: Find a column through Macro, field header, thanks in advance

field header, thanks in advance, ogilvy: Omair, assuming state is not a substring of any other header in row 1 then Dim r as Range, cell as Range, r1 as Range set r = Range( A1 ,Cells(1,columns.count).End(xltoLeft)) for each cell in r if instr(1,cell, state , vbTextcompare) then ...

Excel: find text from one list in another list, supplier names, excel expert

supplier names, excel expert, error function: You could use the FIND function to location the position of some text inside a cell. Such as: =FIND( Adobe ,A1) If the string of text exists, you ll get the position, otherwise you ll get an error. Then you could use your favorite error function like...

Excel: Finding the average days it takes customers to pay, customer name, integers

customer name, integers, spreadsheet: Please send me the sample spreadsheet: drjaxsonelph@gmail.com. I ll look it over a little later. In the meantime: In Excel, dates are simply integers. Just subtracting one cell from another (minus 1) will yield the days in between those dates . . . then...

Excel: Follow-up: Excel 2010 Pivot table Collapse Macro, scott test, pivot table

scott test, pivot table, pivottables: Scott test this on a copy of the worksheet that holds your pivot table. the showdetail command works on the whole pivot item whereever it occurs in that field. Even when I do it manually, it behaves the same. Maybe with your data, it isn t as obvious....

Excel: Follow up question, sh1, c line

sh1, c line, source data: Graham, this worked for me. If you source data is formulas and you want to paste the value displayed, then you can replace the Cell.copy Sh1.Cells(i, C ) line with the two lines of code that are commented out. Otherwise, you can delete all the commented...

Excel: Formula help, helper cells, cell a1

helper cells, cell a1, sheet1: I m going to take advantage of a pattern I see - namely, the names are next to each other (in pairs). So, assuming the %SB is in cell A1 of Sheet1, then enter this in Sheet2. I m going to use some helper cells to make it easier, but it can be done directly...

Excel: Formula Help - second question, helper cells, previous question

helper cells, previous question, pairs: You will need helper cells. On Sheet2, cells A1:D1 contain the names. THe helper area is (arbitrarily) in F2:I3. In cell F2, enter this formula: =SMALL(IF(Sheet1!$A$2:$T$2=A$1,COLUMN($A:$T), ),ROW(A1)) but it MUST be entered by holding ctrl+shift before...

Excel: If Formula Question, cell b1, ogilvy

cell b1, ogilvy, a1: Brent you list numbers until you get to after 21.9 and then you go to a percentage. You can t mix and match - so it is unclear what is in the cell. If the cell holds a percentage =IF(A1= , ,IF(A1 =22%,110%,IF(A1 =16%,100%,IF(A1 =11%,90%,IF(A1 =6%,80%,70%)))))...

Excel: Formula to return a value based on cell fill color., excel formula, room numbers

excel formula, room numbers, b18: There s no Excel formula that will find a cell within a range based on its conditional format. You say one of the cells may be filled in blue if it meets certain criteria. You must be clear and specific about what those criteria are, then use those criteria...

Excel: Formula with many variables, xp sp3, vlookup

xp sp3, vlookup, ccfs: * Probably you should just use vlookup s instead of IF formulas, which leads me to say you should probably use Access if you re using database type of functions. But I m not following your question nearly enough to really answer it. can you post a sample...

Excel: Function to check presence of null, blank cells, null question

blank cells, null question, blank spaces: Hi Shobin, One solution would be to show a data entry status in some cell. Let s say for example you want the user to enter some data in cells B2:B10, then in cell A1, enter the below formula... =IF(COUNTBLANK(B2:B10) 0, Enter values , Entry completed...

Excel: help in change case in Excel, change case, spread sheet

change case, spread sheet, dear sir: Hi Veerendra, You will need to use a formula that and then once your formulas are doing what you want, you can paste special as values. Your formulas are =upper(A1) changes all letters in A1 to uppercase =lower(A1) changes all letters in...

Excel: Hide EntireRow when Cell value is equal to specific value, code validation, target address

code validation, target address, validity period: Jn, here are two different ways you could do this. Since you have a fixed list, then you already know what rows to hide, so you might as well just use those cell locations. In the second approach, I use the list of values to find which cells to hide....

Excel: History Sheet via Macro, target value, target address

target value, target address, history sheet: Press Alt/F11, doubleclick ThisWorkbook , enter this: Dim TheVal Private Sub Workbook_Open() TheVal = ActiveCell.Value End Sub Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error Resume Next Application.EnableEvents...

Excel: Import multiple text files into excel with VBA, vba script, keyboard shortcut

vba script, keyboard shortcut, txt files: So maybe you just take the first 3 characters of the original filename and name the sheet that: pre Dim fPATH as String, fNAME as String, ans as String ans = Application.InputBox( What common date value? , Date , Format(Date, MMMDD ), Type:=2) If...

Excel: Index /Match issue, issue question, return zero

issue question, return zero, j2: With the data from your question in the range A1:D4, with NY entered in cell H2, with FT entered in cell I2, and with cell J2 being a date identical to cell D1, then the formula =SUM(IF(A1:A4=H2,IF(B1:B4=I2,OFFSET(B1:B4,0,MATCH(J2,C1:D1,0)),0))) entered...

Excel: Info from multiple worksheets transferred on to one, counta, mws

counta, mws, vendor id: Hi Emily, I am assuming that your separate worksheet is in a separate workbook that is open in Excel, and have named it Sheet1 in workbook Master.xls for this example. Here is the code I recommend you put in each template: Private Sub Worksheet_Change(ByVal...

Excel: Insert Consecutive Months in a Date Column, vb module, dateadd

vb module, dateadd, rw 1: Assuming the first date is in A1 and the last date is somewhere further down, try this: pre Option Explicit Sub AddMonthlyRows() Dim Rw As Long Rw = Range( A & Rows.Count).End(xlUp).Row Application.ScreenUpdating = False Do If Range(...

Excel: inserting color with IF function, conditional formatting, value ranges

conditional formatting, value ranges, multiple value: Hi Sony, You can do this with conditional formatting if you have Excel 2007 or 2010. Home/Conditional Formatting You can use the formula version to look at the value in D3 and change the color of D4 with multiple value ranges. Hope this helps,...

Excel: intersection of data using VBA for a variable, cell position, cell location

cell position, cell location, correct column: =============== addendum to the original answer ============ See code with two added lines to make the check you describe. Assume Sheet2: B5: Order C5: Date D5: Value E5: New Ship date B6: 50 C6: 2/2/2012 D6: $25.00 E6: 3/4/2012 Assume...

Excel: inverting the order of a downloaded table, csv file, bottom row

csv file, bottom row, aidan: easiest way would be to write a macro to do the import, the final process of which would be along the lines of edit, select all, sort by appropriate column - if the file name and location is constant, the entire macro can be recorded. If not, then a minor...

Excel: invoice, vba macro, imput

vba macro, imput, invoice number: Thanks for the explanation - I understand it much better now. However, as I suspected in my first answer, it is probably a macro that s past my comfortable area of knowledge. It s probably better for you to re-issue the question to another expert who knows...

Excel: Issue with looping data, record id, history data

record id, history data, data sheet: Shawn, I think you have a typo in your code. BOTH of your loops are from 2 to finalrow but I believe the h loop should be for h = 2 to Finalrow2 I suspect that the value of FinalRow is much less than FinalRow2 and it just doesn t check all the...

Excel: leader boards, excel 1997, luck david

excel 1997, luck david, highest score: Hello Luke, Thank you for your question and the opportunity to assist. There are several ways to accomplish this. The simplest way would be to just sort the score column either lowest to highest or highest to lowest, depending upon the sport, and the...

Excel: IF a list contains specific text, Retrieving Column Header by Matching A Data Excel Sixthsense Excel

Retrieving Column Header by Matching A Data Excel Sixthsense Excel: Hi Moshe, Copy and paste the below formula in Sheet2 of C2 cell =IF(ISNUMBER(MATCH($B2,Sheet1!$A:$A,0)),Sheet1!$A$1,IF(ISNUMBER(MATCH($B2,Sheet1!$B:$B,0)),Sheet1!$B$1,IF(ISNUMBER(MATCH($B2,Sheet1!$C:$C,0)),Sheet1!$C$1, ))) Drag the above formula...

Excel: Listindex pulling from two worksheets at once, loansearch, command button

loansearch, command button, rebuttal: Hi Mark, There was no image attached. Anyway, I may have misunderstood the question in the past. Correct me if I am wrong, but the problem is that you cannot populate the Update Sheet based on the UserInput, is that correct? i.e. You do not have a problem...

Excel: long to wide transformation with of panel data with many variables, usa gdp, target cell

usa gdp, target cell, longitudinal data: This is simple, just use the Paste Special with Transpose Function. 1) Choose any row or column and copy it 2) Click your target cell, right click and choose Paste Special 3) Whatever option you choose, make sure you choose Transpose as well Shahi...

Excel: macro, command button, combo box

command button, combo box, doubleclick: Add an Activex command button to your sheet, doubleclick it to open the sheet module, then add this macro... the same is for a command button named CommandButton1, edit as needed. Private Sub CommandButton1_Click() If Me.FilterMode = True Then ...

Excel: Macro delete code, path separator, correct syntax

path separator, correct syntax, path property: Hi Svein, VBA has the Kill statement to delete a file. The ActiveWorkbook.Path property has no trailing path separator, so the correct syntax is: Kill ActiveWorkbook.Path & oldername Or if the file is a string in variable called sFileName: ...

Excel: Macro to nsert blank line, then change row height and fill color, irow, blank rows

irow, blank rows, false application: Like so: pre Option Explicit Sub InsertBlankLines() Dim LR As Long, Rw As Long Application.ScreenUpdating = False Application.Calculation = xlCalculationManual LR = Cells(Rows.Count, C ).End(xlUp).Row Find last used cell in Column...

Excel: Macro/VBA/or function to split data, definitive response, pivot table

definitive response, pivot table, ogilvy: Adam, It sounds like you want to send me the file with a complete description and example of what you want me to do for you. If that is in fact the case, then you can send it to twogilvy@msn.com If not, then you really haven t done more than given me...

Excel: macro NOT WORK for cell value which contains formula(lookup), body cells, outlook application

body cells, outlook application, macro code: Your macro specifically is using constants. If column A is filled with formulas: For Each cell In Columns( A ).Cells.SpecialCells(xlFormulas) If column A is a mixture of constants and formulas: For Each cell In Intersect(Activesheet.Usedrange,...

Excel: macro for working days, lastday, firstday

lastday, firstday, dt1: Sudharshan.S Sub Makeworkbook() Dim bk As Workbook, dt As Date Dim dt1 As Date, lastday As Date Dim firstday As Date Workbooks.Add Set bk = ActiveWorkbook dt = Date lastday = DateSerial(Year(dt), Month(dt) + 1, 0) firstday = DateSerial(Year(dt),...

Excel: match in VBA - does not like "&", correct column, string fname

correct column, string fname, msgbox: Daniel, this code worked fine for me: Sub ABC() Dim KPI_Desc As String, fName As String, r As Range Dim Dashboardrow As Variant KPI_Desc = Risk & Ops fName = Daniel_Test1.xls Dashboardrow = Application.Match(KPI_Desc, Workbooks(fName).Sheets(...

Excel: Matching Data in Columns, vlookup formula, xls

vlookup formula, xls, comma: THe function you want is VLOOKUP, this function works between workbooks, and the source workbook doesn t even have to be open. 1) open both workbooks 2) In the smaller workbook, put this much of the VLOOKUP formula in B2: =VLOOKUP(A2, 3) Now pause...

Excel: Matrix Lookup Value, left hand column, index command

left hand column, index command, table index: You ve got the right idea, but you do the FIRST match() on the A value, then the SECOND match across the top of the table. =INDEX($D$4:$G$5, MATCH($A$4, $C$4:$C$5, 0), MATCH($E$1, $D$3:$G$3, 0)) Since the values across the top are numberic, then...

Excel: MS Excel, pivot table, column section

pivot table, column section, word name: I would use a Pivot Table as follows: 1. Give each column a label. For example column A could be Name and column B Activity 2. Click the mouse on the table 3. Select Data 4. Select Pivot Table 5. Select Finish This will bring up a blank...

Excel: need smaller files, Excel copy and paste problem 2010 Excel Sixthsense Excel

Excel copy and paste problem 2010 Excel Sixthsense Excel: Hi Gabe, Requesting you a screenshot by having the active cursor in data. Whether this copy and paste problem persist with this file alone or with any other new files also? Please try Alt + E + M and try to copy the sheet to new workbook and try......

Excel: Nested IF AND statements, score range, score ranges

score range, score ranges, score type: You want to know what range the score falls into. That is, you want to treat a score between 331 and 337 one way, and a score between 338 and 349 another way. So in my answer, the table in A2:B7 translates each score into a code representing its range. A score...

Excel: Nesting IF statement, tabe, f6

tabe, f6, r1: Hi Dan How are you? Please try ande adjust to your problem the follwoing code Sub formuling() Dim R As Range, R1 As Range, tabe As Range Set R = ActiveSheet.Range( H:H ) Set R1 = ActiveSheet.Range( G:G ) Set tabe = ActiveSheet.Range( A1:F6 ) ...

Excel: Open & Close Workbook, dim statement, instrrev

dim statement, instrrev, excel macro: Hi Irfan, That is easy enough. First of all, you need to ensure strFile does not loose its value (only needed if you close the wb in a different sub than the one you used to open it in). To ensure that, move the Dim statement of strFile needs to be moved...

Excel: Output of Number's Concatenate in HINDI?, Hindi Language Numeric Entry in Excel Sixthsense Excel

Hindi Language Numeric Entry in Excel Sixthsense Excel: Hi Parantapkumar, Click the below link and download the File. a href= https://doc-14-3s-docs.googleusercontent.com/docs/securesc/ha0ro937gcuc7l7deffksulhg5h7mbp1/okk5lerhfh9qe4v5b47vongkdmd8vkgd/1332864000000/08849022783486111145/*/0B2TMxNyEPQ2rTUVwMFBvT2VTUXFxYnhKN3FLNkNOUQ?e=download...

Excel: perhaps a form of an "if" statement, xmr, vlookup function

xmr, vlookup function, tvss: Ainsley, Assume A1: EPAN-P-1 B1: =if(Left(A1,4)= EPAN , 01-EPAN-O&M , ) if you have several documents to assign B1: =if(Left(A1,4)= EPAN , 01-EPAN-O&M ,if(left(A1,4)= TVSS , 01-TVSS-O&M ,if(left(A1,2)= NP , 01-NP-O&M ,if(Left(A1,3)= XMR ,...

Excel: picking up current sheet from a macro, mail message, current sheet

mail message, current sheet, outlook application: Hello Billy, Change your code like this, the way I intended... Declare a parameter for your Send() like this.. Sub Send(Ws As Worksheet) Thus whenever we call the Send(), we can also mention on which worksheet it should run. The name of the Worksheet...

Excel: Pivot table page area data, cell d1, pivot table

cell d1, pivot table, excel function: Hello Scott, Thank you for the explanation and the sample spreadsheet you sent. That helped me see what you are trying to do. I am not well versed in VBA, so I cannot offer a VBA solution, which you want to avoid anyway. There is not a GETPIVOTDATA...

Excel: Populate Listbox upon clicking a cell in the column., target cells, target value

target cells, target value, good afternoon: Like so: pre Dim grngCell As String, MyList As String Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 9 And Target.Row 26 Then ActiveSheet.ListBoxes(ActiveSheet.ListBoxes.Count).Cut ActiveSheet.Paste ...

Excel: prevent any cell from entering 3 days back date entry, target cells, false application

target cells, false application, current date: 1. Private Sub Worksheet_Change(ByVal Target As Range) Dim cl As Range For Each cl In Target.Cells If cl.Formula Then If IsDate(cl) Then If cl Date - 3 Then Application.EnableEvents...

Excel: random number generation?, random number generation, team building exercises

random number generation, team building exercises, number functions: Ainsley, Put 15 names in column A next to each name put =rand() select A1:B15 and sort on column B. This will randomly order the names. select the first 5 and that is team 1 and so forth. The cells with random numbers will look nonsensical since...

Excel: Replace values, spelling variations, correct spelling

spelling variations, correct spelling, wb2: Doug, data is on sheets which are located in workbooks. You didn t say anything about which sheet, so I assumed it is the first sheet in the tab order. You can replace the 1 in worksheets(1) with the name of the sheet such as wb1.Worksheets( Data ) ...

Excel: retrieve data from chart, vl, cells

vl, cells: Activate the chart (click on it), then run this procedure -- Sub getvalues() Set ch = ActiveChart Set ws = Worksheets.Add Dim vl As Series n = 0 For i = 1 To ch.SeriesCollection.Count Set vl = ch.SeriesCollection(i) ...

Excel: Rolling Average, w4, a5

w4, a5, d2: Hi Brendan, How will Excel know which is the latest week, if you simply enter W1-W52 in row one? Anyway, if you don t mind inputing the starting week number each time you want to check the average... for example in cell A5, this is the formula you can use......

Excel: Save file into folder from excel file..., excel vba, receipt number

excel vba, receipt number, example address: Hi Firdaus, I know how to export other embedded Office documents, but PDFs are way beyond my expertise. I am sorry I can t help you any further on this other than directing you to this similar link... http://www.excelforum.com/excel-programming/700519-using-excel-vba-to-open-save-as-and-close-embedded-pdf-files.html...

Excel: save picture into folder, len day, receipt number

len day, receipt number, filepath: Hello, You need to provide information as where the picture is coming from? Is it embedded, within a userform control, etc... are there many rows, what are the column names, how is your data organized. I am sorry I can t understand if you only use 2-3 lines....

Excel: sending a form-feed command to the printer, vba commands, excel spreadsheet

vba commands, excel spreadsheet, print key: Hi Dale, I am afraid I have not tried anything as such. Did you try to adjust the Margins? Usually doing so will automatically make the Printer move a few spaces below/above your printed data. You could also try to record a macro before hitting the print...

Excel: How to set up a database, database sales, date city

database sales, date city, time ed: Hi Ellen, It looks like you are setting everything up just how it should be. The only thing you need to do is make sure when you try to sort that you have highlighted the whole table, not just the particular column you are sorting on. If you highlight...

Excel: Shortcut, keyboard shortcuts, keyboard shortcut

keyboard shortcuts, keyboard shortcut, control key: Hello Steve, Thanks for your question and the opportunity to assist you. The keyboard shortcut is Control + H. Hold control key down while pressing the H key. That will open up the box with the Find and the Replace Fields available. You can Google...

Excel: slonim@total.co.il, cell b2, cell a2

cell b2, cell a2, cell c2: Moshe Here is the formula you need in cell C2. You can copy it directly from here to cell C2, then copy it down thru cell C50 and it should work for you. =IF(ISERROR(VLOOKUP(Sheet2!B2,Sheet1!A$2:A$10,1,FALSE)=A2),0,1)+IF(ISERROR(VLOOKUP(Sheet2!B2,Sheet1!B$2:B$20,1,FALSE)=B2),0,2)+IF(ISERROR(VLOOKUP(Sheet2!B2,Sheet1!C$2:C$8,1,FALSE)=B2),0,3)...

Excel: How to split the cost to three years, contract value, krishna

contract value, krishna, 10 months: Hi Krishna, I used these formulas: J1 (calculates total number of months in period): =DATEDIF(E1,F1, m ) G1 (part of contract value in 2009): =IF(E1 DATE(2009,12,31),DATEDIF($E$1,DATE(2009,12,31), m ),0)/J1*D1 H1 (part of contract value in 2010):...

Excel: Split & Freeze, window menus, window feature

window menus, window feature, split window: Hi Firdaus, I am sorry, I do not know any other way other than what I have suggested to you. To clarify further, you are not opening two different worksheets, rather you are opening the same work sheet twice. In a way, this is what Excel does when you use...

Excel: Spreadsheet with weekly dates, cell a2, list of holidays

cell a2, list of holidays, vlookup: Hi Filipe, Assuming the start date, a Friday, is in cell A1, then simply entering the following formula in cell A2 and downward would give you all Fridays, as required... =A1 + 7 However, since you want to skip some days, we first need to determine...

Excel: If statements counta statements, sku number, dummy data

sku number, dummy data, popular items: Hi Marcus - I think I understand what you mean but to be 100% sure, please send a copy of the sheet to me at drjaxsonelph@gmail.com. I ll look at it in a little while. Doesn t have to be the whole workbook - or can contain dummy data if it has sensitive...

Excel: stop the count function, excel 200, error values

excel 200, error values, zeros: Brian, showing me a picture with some zeros in some cells doesn t really tell me much. I have no idea how those zeros are being produced. Based on just what I can see I would suggest in B1 you use =if(A1 ,formula that produces your number, )...

Excel: string comparing, code snippet, richard florida

code snippet, richard florida, florida usa: Michael I m not exactly sure what you want to do, but--- If you want to compare each character in the cell with each character in the second cell, then I might suggest using text to columns and dividing both of the strings into individual columns with...

Excel: No Subject, excel vba, direct email

excel vba, direct email, ok button: I haven t really got enough information to go on - but there is an inbuilt userform in excel that displays data from a database - other than that, you are pulling in data, so i would assume you have stored the row number somewhere within your code - I would...

Excel: Sum of True, richard florida, true answer

richard florida, true answer, simple answer: Mike Here is a simple answer. I assume you have an if formula in cells A1 thru A10. You can change the if formula to one that doesn t produce a false but produces a blank. The formula would look like this =IF(B1=1,+C$7, ) As you can see, after...

Excel: Sweepstake, blank cells, many horses

blank cells, many horses, elegant solution: Hello again - I wanted to come up with something simple without the use of macros - but was unsuccessful. However, I did succeed in coming up with something with very simple macros - ones that you should be able to understand and replicate. I created a spreadsheet...

Excel: text input box in between a vlookup execution, blank cells, red arrows

blank cells, red arrows, cell b5: Amit Rajput, 1) I understand what you are describing 2) It seems like we talked about this before 3) I would need to see the workbooks to put in a macro to do what you want. 4) So you would want the macro to start asking for new values when the...

Excel: tick box / automation, target value, million times

target value, million times, macros: 1) Turn off macros for now by going into DESIGN MODE 2) Select the range of cells where this is to operate, for example B2:B100 3) Format those cells in advance with the Marlett font, size and characteristics you want 4) Put this macro into that sheet...

Excel: Time calculations in Excel, time calculations, whole number

time calculations, whole number, time value: I would GENERALLY enter the time as 2:16 as this is then truly a time value, and can be easily calculated by excel. IF you need to stick to decimal represenatation of times, I would convert them into time before doing the maths - assuming my time value was...

Excel: Tracking Days Out, empty columns, column c

empty columns, column c, mccluskey: Lynn McCluskey =today() will give you today s date and will update each time the sheet is calculated (at a minimum, that would be when you open the workbook and usually anytime you make an entry). C2: date when called D2: =Today() E2: ...

Excel: Unhide & Delete all objects in a workbook, spreadsheets, spreadsheet

spreadsheets, spreadsheet: To unhide all worksheets in a workbook with a macro: pre Sub UnhideAllWorksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = True Next ws End Sub pre After deleting a worksheet from a workbook,...

Excel: Using Array Formulas in VBA, array formula, vba excel

array formula, vba excel, array formulas: Steve, v = Evaluate( MAX(LEN(A1:A4000)) ) Here is a demo from the immediate window: v = Evaluate( MAX(LEN(A1:A4000)) ) ? v 9 It should be pretty fast - I would think far faster than doing lmax = 0 for each cell in Range( A1:A4000 ) ...

Excel: Using GPU in Excel VBA, dynamic link library, graphic processing unit

dynamic link library, graphic processing unit, software development kits: Hi Minh, Yes, dramatic improvements in speed are possible by making use of the GPU if your problem is of a type that GPUs are good at: signal processing, highly parallel or other highly compute-intensive problems. However, you will almost certainly have...

Excel: Using VB to copy data based on matched array, cell a2, array formula

cell a2, array formula, daniel douglas: Daniel Douglas, in AG2 of Structure =IFERROR(INDEX(SEC4!$A:$A,MATCH(F2&G2,SEC4!$B$1:$B$2000&SEC4!$C$1:$C$2000,0),1), ) entered with Ctrl+shift+enter rather than just Enter since it is an array formula change the 2000 to refer to the last row that...

Excel: Using Vlookup function with VBA variables of workbook and worksheet, vba procedure, vlookup function in excel

vba procedure, vlookup function in excel, wkb: Example: in a vba procedure, say you wanted to get the result of a VLOOKUP of cow from the 2nd column of a table in range A1:B3 of Sheet1 of an open workbook named sampleSourceFile.xlsm , in other words: =VLOOKUP( cow ,[sampleSourceFile.xlsm]Sheet1!$A$1:$B$3,2,0)...

Excel: VB version tracking, target range, getsetting

target range, getsetting, sheet tab: Right-Click the sheet tab for Summary_new PwC and select View Code, and put this in: Private Sub Worksheet_Change(ByVal Target As Range) NoChg = Intersect(Target, Range( band5_percent_new )) Is Nothing End Sub Doubleclick ThisWorkbook and enter...

Excel: VBA and Autofilter help!, question pool, processor name

question pool, processor name, c27: Mark White, You didn t ask me this question - I found it in the question pool. Countifs does not honor hidden rows - so the quick answer is no it can t be adjusted to work with counting only visible rows. the equally quick answer is to add a condition...

Excel: vba checkboxes, active x controls, false application

active x controls, false application, excel worksheet: Private Sub CheckBox1_Click() If CheckBox1.Object Then uncheckTheOthers 1 End If End Sub Private Sub CheckBox2_Click() If CheckBox2.Object Then uncheckTheOthers 2 End If End Sub Private Sub CheckBox3_Click() ...

Excel: VBA code, vba code, code windows

vba code, code windows, cint: Hi Gary, This is very simply using the InputBox function. InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]) Dim iCopies as Integer With Sheets() iCopies = CInt(InputBox( Enter number of copies , Print , 3) .PrintOut...

Excel: vba code to import xml data to excel table, sharepoint server, directory url

sharepoint server, directory url, import url: I don t use XML so I won t speak to that, but at first glance it looks like it SHOULD work. In your code, let s add a debugging line so we can see what the sourcefile string actually resolved to. Add this line of code after sourcefile = ..... ...

Excel: VBA help, target cells, target value

target cells, target value, target address: Hi Jacqui, Are you creating a ListBox in a UserForm or within Excel WorkSheet. See this for a simple tutorial. It even has a sample file. You can ignore the additional parts and just concentrate on your requirement. This is the part of the code used that...

Excel: VBA: Listindex combobox help, listindex, loan number

listindex, loan number, orginial: In your beginning code you set the IDX value with: idx = Update.ListIndex I can only guess that means it s matching something you ve selected in the list to your Rebuttal page, but whatever it means, it s working, right? The problem is then...

Excel: VBA Loop Copy and Paste, data4, followup question

data4, followup question, blank row: Mike, I assume you mean copy data from column C to columns D:H Test this on a copy of your data (copy your worksheet and test on that). It assumes the header Number is in cell A1. ============== added information ====== further to my response...

Excel: VBA Match Function for Dates, userform initialize, am6

userform initialize, am6, combobox: Error 2042 is the same as #N/A which match would return in the worksheet if it failed to find a match. In VBA, you need to do the match with the date serial number rather than the date string. The problem is with a date string like mm/dd/yy I can t say...

Excel: VBA Possibility, access attachment, clip icon

access attachment, clip icon, attachment feature: Hi Michael, I discovered more things... If you embed an object using a code similar to below... ActiveSheet.OLEObjects.Add Filename:=EmbedFilePath, Link:=False, DisplayAsIcon:=True, IconFileName:= , IconIndex:=0, IconLabel:=MyLabel You should...

Excel: VBA restricting uses of file, good afternoon, shift key

good afternoon, shift key, microsoft excel: Seems like you put this in your personal.xls! Start excel while holding down the Shift key. You should then have access to the workbook and you can change the code (or move it out of your personal.xls). Once you have excel open, you can also put this in...

Excel: VBA to unhide rows and column., lcol, colorindex

lcol, colorindex, frow: Raja, it works fine for both for me and the code is correct as written. I suggest that your columns are not hidden - you have just made the narrow. For a diagnostic technique, I will describe what to do if you consider column J as being hidden and...

Excel: Vlookup?, pivot tables in excel, money recieved

pivot tables in excel, money recieved, b company: Hi Chan, VLookUp will only return the first row with the date match, but you need all rows that match your input date. In this case you can do one of two things... 1) Use Filters... Data Tab -- Filter 2) Pivot Tables I d suggest you go with Pivot...

Excel: vlookup functionin and pop up input text box, blank cells, red arrows

blank cells, red arrows, cell b5: Amit Sorry--I will not be able to answer this question in a timely manner, I suggest that you reask the question of another allexperts person and I am sure you can get a good and quicker answer than if you have to wait for me. Sorry I could not be of...

Excel: want to change a large fil with smaller ones, emails addresses, 5 steps

emails addresses, 5 steps, copy paste: AFter you highlighted the cells, I assume you did copy them -- Ctrl/C or equivalent. The only way this wouldn t paste is if there s some macro running which empties the clipboard based on what it does. Best to ensure no macros are running by following these...

Excel: worksheet_change macro for addin, format button, value case

format button, value case, case c: First, you don t want to format the selected cell. If you want to format a specifc cell then change the macro to Sub INRFormat() Works upto 13 digits only Range( A10 ).Font.Name = Rupee Foradian Standard For Each c In Range( A10 ) ...

Excel: How to write them in VBA, dt 1, question sir

dt 1, question sir, d day: Raja, Public Function Bdate(d As Range, e As Range) As Date Dim dt As Date dt = DateSerial(Year(d) - e, Month(d), Day(d)) Bdate = dt End Function you would need two arguments to the function d is the cell that contains the date e is the cell...

Excel: IF OR AND, valid combination, l3

valid combination, l3, ogilvy: Gail, Not sure what you want if those conditions are not met. I just put in a meaningless phrase for it to display if a valid combination is not found: =IF(AND(L3 Brakes001 ,L3 Efoods001 ,OR(H3= PI ,H3= DN )), Other Small Supplier , Not Other Small...

Excel: XY Scatter Admission Dates/Times Excel 2007, admission times, bubble chart

admission times, bubble chart, time column: Darren, Why not send me a sample file (cleansed of any restricted information or even a dummy file) and I will see what I can do for you. I many need to add a dummy column or two that actually feeds the graph (although it sounds like you have done that)...

Excel: Adv. filter criteria with column positions changing, column positions, filter criteria

column positions, filter criteria, bbbbb: envlo I assume you are working on the activesheet and your headers are in row 1 starting in column A. I put the criteria range two columns to the right of your data. Sub ApplyAdvancedFilter() Dim r As Range, r1 As Range, cell As Range, rA As Range,...

Excel: Application.Caller (Runtime error '424' - object required), run time error, datediff function

run time error, datediff function, searching the internet: Firstly, the Shift variable is declared within the function so will only exist when the function is called and will cease to exist when the function has finished running - so would never actually be anything other than 1 in practice. I m also unhappy with...

Excel: Auto close, autoclose, correct password

autoclose, correct password, thanks in advance: I wouldn t call autoclose, I d simply close the sheet - something along these lines Sub passwordtest() Dim counter As Long Dim test As Boolean test = False Dim passwordteststring As String counter = 0 While counter 3 And counter = 0 passwordteststring...

Excel: Auto Filtered Drop Down List, column table, status column

column table, status column, sheet1: I have your solution but it is far too complicated to explain in this area. It basically adds some (easily replicable and expandable) flag columns to your Sheet2 , and then uses an OFFSET validation range in Sheet1 based upon that flag column table. Please...

Excel: Automatic copy and paste based on met conditions, blank rows, blank row

blank rows, blank row, error data: The code removes the previous entry because otherwise it will do the copy as soon as you change either cell H6 or cell I6. If it suffices to keep the values in cells A6:G6, then you can change this line of code: Me.Range( A6:I6 ).ClearContents to: ...

Excel: automatic funcutions, data validation, employee names

data validation, employee names, source field: Are you familiar with cell validation? I can visualize the workbook this way: on one worksheet, call it something like data , you have the columns you mentioned above. For the sake of this answer, let s assume you have 100 employees, and let s assume that...

Excel: Average of Data with Dynamic Multiple Criteria, range f1, correct combination

range f1, correct combination, 4 january: Jn, When I woke up this morning, I for some reason I was thinking about your problem. While the answers I gave will certainly work, they may be more involved than necessary. In you IF statement (VBA or Formula) you need to consider 3 situations. ...

Excel: Average with Multiple Criteria in the Same Range, array formula, january february march

array formula, january february march, referenced data: You re using text strings January , February , March , etc. Excel doesn t know that from dog cat apple . It s just text. You may get better results with something numeric, like you re doing with your weeks, like Month 1 , Month 2 , etc. The...

Excel: AverageIF, array formula, q9

array formula, q9, zeros: Gail, I was just reading your question in Allexperts that it was not readable. I was able to see it in an email notification. So =IF(ISERROR(AVERAGE(IF(($Q$9:$CD$9 0)*(MOD(COLUMN($A$1:$BN$1)-1,13)=0),$Q$9:$CD$9))),0,AVERAGE(IF(($Q$9:$CD$9 0)*(MOD(COLUMN($A$1:$BN$1)-1,13)=0),$Q$9:$CD$9)))...

Excel: Binary Variables in Excel, binary combinations, binary variables

binary combinations, binary variables, question pool: A, You didn t ask me this question. I found it in the question pool. put this in any cell and drag fill down so 1024 cells in a single column are filled: =IF((ROW(A1)-1) =511, 0 &DEC2BIN(ROW(A1)-1,9), 1 &DEC2BIN(ROW(A1)-513,9)) In versions of...

Excel: Bold subtotals in a pivot table, diamond business, pivot table

diamond business, pivot table, layout check: Gail, for me, if I go into the pivot Table menu (up at the very top when you select a cell in the pivot table) and choose options from the subsequent menu it produces and then options under that, then under the display tab: If I choose Classic Pivot Table...

Excel: Calculate occurance of staff, pivot table, number of names

pivot table, number of names, dropbox: HI Giovanni, A pivot table is easiest. I added this formula to cell C4: =IF(ISNA(MATCH(B4,$E$5:$E$27,0)), Unit 3 , Unit 2 ) and copied down to match the number of names. I added Unit in cell C3. Then I created a pivot table based on range...

Excel: Calculating difference between two dates, run time error, date1 date2

run time error, date1 date2, days between two dates: Now I get it. There is no need for VBA here either. The average interval for a series of dates is the newest date minus the oldest date divided by the number of intervals, which equals the number of dates minus one. So if the dates are in A1:F1: =(Max(A1:F1)-MIN(A1:F1))/(COUNTA(A1:F1)-1...

Excel: Colouring Of cells based on Data in cells, format cells, kaustubh

format cells, kaustubh, home tab: Kaustubh You can use the conditional formatting feature to format cells based on their data. Go to the home tab on the toolbar, then select styles, then select conditional formatting. I think you will want to use the highlight cells rules. Check this...

Excel: Combinig a group of rows into one cell, ana cristina, antonio eduardo

ana cristina, antonio eduardo, column c: Ana Paula, having 10 rows or 1,000,000 rows doesn t affect the formula. You can select the C1 cell and look at the lower right side of the highlight around the cell. It should have a little square in that corner. You can double click on that square and...

Excel: Combining data from different size spreadsheets, sumif excel, blank cells

sumif excel, blank cells, blank cell: Have you tried a SUMIF (Excel 2003) or SUMIFS (Excel 2007/2010) function in your formula? It allows you to add values based upon one criterion (SUMIF based on customer number) or many criteria (SUMIFS based on customer number, date, . . . etc . . . ). That...

Excel: Complex Copy If Certain Conditions are Met, header row, independent data

header row, independent data, second term: Amjad I am not totally following the criteria that you want to use, but it appears to me that you can use an IFAND() formula to verify the conditions for a copy. With this formula you can create multiple conditions that must be met before a copy is done....

Excel: Complex Nested IF Statement, sales dollars, little trouble

sales dollars, little trouble, c6: I would tend to use a lookup table for something like this as it often makes for easier formulas - however, it is perfectly possible to do this with a nested IF statement. You hadn t mentioned which cell to use for the SalesPrice so I have referred to that...

Excel: computing, tabular layout, direct email

tabular layout, direct email, sumproduct: The tabular layout of data on this system makes it hard to follow what is actually required - also I m not clear what needs to be summed - and finally, I don t know which version of excel we are using - it sounds as though it is a multiple condition sumif...

Excel: Conacatenate changes the Language, ms office tools, unicode fonts

ms office tools, unicode fonts, regional languages: Hello, I have never worked on Excel using Regional languages, but would suggest you to check the following. 1. Make sure you have installed Hindi Language support, from the Office CD 2. Make sure the language is added for use. From the Start Menu, navigate...

Excel: Conditional Formatting/Formulas, excel, conditional formatting

excel, conditional formatting: Setting up an actual reminder would be tough, and would involve some programming, but you can use conditional formatting to make those overdue dates stand out. 1. Select the column with your dates 2. Go to Home Conditional Formatting Manage Rules ...

Excel: Consolidated columns of the sane record in one row, excel sheet, a11

excel sheet, a11, a12: 1) Delete the colored RESULTS section from your sheet to process. 2) Assuming there is a date in B1 and you want to process all the rows from from 9 through the last row of data in column I, this macro will do that and produce the results from your picture....

Excel: Get contents of last cell in a range that has data, decimal fraction, range index

decimal fraction, range index, decimal equivalent: You didn t specify how the formula wasn t working - time to excel is simply a decimal fraction, where 12 hours is 0.5 (half a day) - so my GUESS is that the formula is working fine, but returning the decimal equivalent of your time value - at which point,...

Excel: How to convert MYR to USD in excel file, cell c2, conversion rate

cell c2, conversion rate, midst: Hi Sharon, A simple way is to have an extra column next to the one with the USD prices. In any cell (lets assume cell F1), enter the conversion rate doem USD to MYR. In the column next to the USD prices, enter this formula (assuming the USD prices start...

Excel: Coping cell data across worksheets in a workbook, array formula, aca

array formula, aca, prefix: One way would be by using an Array formula like this: =INDEX(Sheet1!$D$1:$D$100,MATCH(A2& | &B2& | &C2,Sheet1!$A$1:$A$100& | &Sheet1!$B$1:$B$100& | &Sheet1!$C$1:$C$100,0)) This assumes your data is on Sheet1!$A$1:$D$100 and the data to find is in cells...

Excel: Copy multiple sheets from a macro to a new sheet not as a macro, disclosure format, loan disclosure

disclosure format, loan disclosure, instrrev: Pam, I see a couple of problems. GetSaveAsFileName returns a fully qualified filename including the path. So fname would have a name like C:MyfoldersMyfilesCML Impaired Loan Disclosure . . ..xlsx this is not a valid argument for workbooks()...

Excel: Copy and paste desired range when a value is entered in certain cell., target value, application index

target value, application index, data excel: Private Sub Worksheet_Change(ByVal Target As Range) Dim Path As String, Newbie As Worksheet, WhoamI As Worksheet Path = D:DataExcel ===change accordingly If Target.Column 7 Then Exit Sub entered info in Col G If Target.Column = 7 And...

Excel: Copy worksheet formulas and formatting without data, left mouse button, array formula

left mouse button, array formula, column headings: Stephen, there is no way to separate the constant entries you want to retain and those you want to remove. So this is how I would do it I would copy one of the sheet by Clicking on the Tab, holding down the left mouse button, hold down the control...

Excel: Copying formulas in Excel 2007, formulas in excel, new location

formulas in excel, new location: Bonjour Gilbert, Probably you are experiencing the Skype Click-to-call browser add-in bug. There should be an update available through the Skype website. If that isn t your problem, try opening the file(s) using File, Open (from Excel), so youa re...

Excel: Count unique values that meets a criteria, code snippet, q1234

code snippet, q1234, ogilvy: jn, it looks like you are putting the values in the array iounter and then counting how many elements using something like cnt = Ubound(iounter) - Lbound(iounter) you could then do for i = lbound(iounter) to Ubound(iounter) if len(trim(iounter(i)))...

Excel: Create a column summarizing answers from a range of columns, cell a2, blank value

cell a2, blank value, colums: Hi Nastia, I will share a concept and you will have to work on it. If I do this, it will take a long time to eventually figure it out perfectly. The solution is to use Match with a dynamic range to find the next non-blank value horizontally and use it to...

Excel: creating formulas to copy/create data from several work sheets, cell a1, vlookup function

cell a1, vlookup function, sheet names: For part 1: if all five sheets have exactly the same field structure (columns), the single formula shortcut to adding, for example, cell A1 from all five sheets is =SUM( Sheet1:Sheet5 !$A$1) assuming sheet names Sheet, Sheet2, . . . Sheet5. The sheets all...

Excel: Some data question., blank cell, simple 1

blank cell, simple 1, bbb: It s a bit hard to construct, but doable. This formula would be shorter if the data had a final unused string in column A, but assuming it doesn t, then we have to watch for the data coming to an end and having a backup formula to take over. Assuming the...

Excel: debug code, array object, code question

array object, code question, question thanks: Hi Firdaus, I am afraid I haven t done anything like you ask. I guess you need a system in place to keep track of the objects names of the object you create. In other words, when you create an object, use an algorithm to rename it and save the new name...

Excel: delete unique records only, countif, f5 key

countif, f5 key, unique records: Dave, in E2 put in the formula =if(countif(D:D,D2)=1,na(), ) ================== alternate answer === (if you really want it to return True, then use the formula =countif(D:D,D2)=1 and drag fill down - that will return true in every row where...

Excel: Detect number, iloc, hypen

iloc, hypen, number 0: Steve, this worked for me. I assume only one hyphen per cell and the string in the cell doesn t end with a hyphen I select the hypen and the next character and place them in the variable sChar I then convert sChar to a number and place it in the...

Excel: due date formula, conditional formatting, format button

conditional formatting, format button, red click: Sounds like conditional formatting would work well for what you need. Assuming you re using Excel 2007 or 2010 (substantial conditional formatting changes from 2003): lets assume your date is in cell A1. Click Home Conditional Formatting New Rule. The choose...

Excel: Excel, auto invoice, sheet1

auto invoice, sheet1, column headers: Aman, First, having the column headers in row 1 and then the invoice number in B2 underneath the Products header in B1 doesn t seem to make sense to me - but if you send the file, perhaps it will. you can send the file to twogilvy@msn.com but you...

Excel: How to Excel, delimiters, b2

delimiters, b2, ogilvy: Kee, Assume you string mary jones low 10 is in A2. B2: =LEFT(TRIM(A2),FIND( # ,SUBSTITUTE(A2, , # ,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), , ))-1))-1) C2: =IF(ISERROR(1*LEFT(SUBSTITUTE(TRIM(A2),B2& , ),FIND( ,SUBSTITUTE(TRIM(A2),B2& ,...

Excel: excel, zeroes and ones, consecutive rows

zeroes and ones, consecutive rows, sumif: I m sure there are ways to create a User Defined Function (UDF) in VBA - but that would be beyond the scope of my knowledge. Remember that you can just hide row 2 so you won t see the flag row . . . and it is easy and quick and doesn t require VBA - which...

Excel: Excel data copying from one sheet to another, row numbers, unpredictable manner

row numbers, unpredictable manner, numbers change: Chaitanya, As I see, this cannot be done without using Macros. Reason - What happens to rows that do not start with IB . Are you prepared leave them BLANK...? I guess No. Then the row numbers change and change in unpredictable manner. Hence, standard...

Excel: excel formatting, paint bucket, font section

paint bucket, font section, spreadsheet: point the cursor to the number of the row (extreme left row label), and click. then from the home ribbon, in the font section, click the arrow next to the paint bucket icon, and choose the color to shade the entire row. the above assumes excel 2007 or...

Excel: Excel Formula, cell d1, excel formula

cell d1, excel formula, column c: In Excel 2007+ you can use SUMIFS for this, (not SUMIF, but the plural SUMIFS). =SUMIFS($C:$C, $A:$A, 20, $B:$B, 543) In prior Excel versions you will need to use SUMPRODUCT and you will not be able to reference entire columns like that. ...

Excel: Excel help, array type, excel help

array type, excel help, urgent basis: To avoid great difficulties and complications for yourself, you should store the age as a NUMBER, not the text you have in col A. For instance, you could enter into cell E3 =LEFT(A3,FIND( ,A3)-1)/IF(RIGHT(A3,3)= Yrs ,1,12) then copy down through E3:E12,...

Excel: Excel IF or INDEX Assistance?, blank spreadsheet, column headings

blank spreadsheet, column headings, index function: Hi Kate, I am not sure if a formula can achieve the result that you desire. I would suggest that you build a macro to perform this activity efficiently because, a. The IF function would result in blank line b. The Index function would not serve the purpose...

Excel: Excel inventory control, stock management systems, bar code readers

stock management systems, bar code readers, google search: Roger, Where I would start is to see what is available for free. One place is http://office.microsoft.com/en-us/ at the top of that page is a dropdown for the Category Templates this may very well have an 80 or 90% solution which could give...

Excel: Excel Macro, macro code, excel macro

macro code, excel macro, address range: Assuming code is in column A, amt in B, Dates in C: Sub updateAmts() Dim rg As String Application.ScreenUpdating = False Range( A1 ).Sort key1:=Range( A2 ), order1:=xlAscending, Header:=xlYes Range( A1 ).CurrentRegion.Resize(, 1).AdvancedFilter...

Excel: Excel Macro to Save As based on Cell Value and to keep the new File Open, access macro, ac10

access macro, ac10, excel macro: Yogi, Yes, I copied the wrong constant from the help file. I should have said set bk = Workbooks( Master Inspection Form.xlsm ) bk.SaveAs D:MIF & bk.worksheets( Front Page ).Range( AC10 ).Text & .xlsm , _ Fileformat:=xlOpenXMLWorkbookMacroEnabled...

Excel: Excel Macro for scanning attendance, continuing education units, barcode name

continuing education units, barcode name, name badges: BSV, Why not send me a sample workbook with two or three sheets one of which will be the sheet where you scan in the the event and then one or two event sheets where you want to scan in the student s bar code from the name tag. So you would select a...

Excel: Excel Multi Data Validation, date validation, data validation

date validation, data validation, ogilvy: Carrie, assume you are placing the validation on cell B9. select the cell and choose the data validation dialog. Under allow: choose custom in the custom text box put a formula like: =AND(LEN($B$9) =30,EXACT(UPPER($B$9),$B$9)) that worked for...

Excel: Excel - multiple lookups, number column, home telephone

number column, home telephone, two tables: Yes, you could use the a href= http://www.599cd.com/tips/excel/matrix-lookup/Default.asp?key=ALLEXPERTS MATCH & INDEX FUNCTIONS /a to look up this data. You could also a href= http://599cd.com/site/courselist/excel2010/expert/x1/Default.asp?key=ALLEXPERTS...

Excel: Excel program, australian football league, vlookup

australian football league, vlookup, club registrar: VLOOKUP is the way to go. VLOOKUP(DateToFind, TableofDatesAndGrades, ColumnToReturn, 1) =VLOOKUP(F2,$A:$C, 3, 1) The first parameter will be the student s date of birth, listed in a cell. (F2 in the example) The second parameter will be your given...

Excel: excel question, conditional formatting, florida usa

conditional formatting, florida usa, first date: I m assuming the cells you want to turn red contain dates. If those dates are in column A, and the first date is in A1, you would select A1:A25, use conditional formatting, and use this formula: =A1-60 TODAY() then click Format and Fill, then select Red....

Excel: excel returns 0, 6 digits, blank worksheet

6 digits, blank worksheet, excel application: Strange. I just want to verify: You ve cold started (not warm rebooted) your PC, opened up a new instance of the Excel application with a brand new blank workbook and brand new blank worksheet, you enter (for example) 1,2,3,4,5 in cells A1:E1 and type =MAX(A1:E1)...

Excel: excel vba to auto assign names to a task, ws2, names thanks

ws2, names thanks, column c: 1) I would suggest a section of cells on columm B in Sheet2 next to the names that have a SUMIF() formula to count how many pages are currently assigned to each name. In B2: =SUMIF(Sheet1!C:C, A2, Sheet1!B:B) Copy that down. You ll probably have...

Excel: Excel worksheets w/o over write, direct email, pivot table

direct email, pivot table, b product: You don t need to much VBA - take a look at http://aidanheritage.byethost3.com/excel/absenceRecorder.xls which is MUCH more complicated than you need, but it gives you ideas for using an input form, and for finding the last used row and storing data....

Excel: excell formulas, sort button, spreadsheet

sort button, spreadsheet, heading: darren You can sort the first column ie date by clicking on the first cell under the heading in the date column and using the Sort button and all the other columns will automatically sort with it, if you want to sort each individual column then the information...

Excel: Executing a VBA code to selectively format currency of NAMED range based on dropdown list, currency conversion rate, currency signs

currency conversion rate, currency signs, currency format: My guess is you don t need any VBA code to do it and it could be quite simple, but I think I d need to see the actual workbook to test my assumption. Also, when you say convert the groups, are you referring to changing maybe $1,000 to £1,000 , for example,...

Excel: Extracting a christian name from a full name, variable lengths, sharon white

variable lengths, sharon white, first names: Gail, the easiest way would be to copy column A to column B, sleect column B and do Data= Text to columns, choose delimited, then on the next dialog, choose space as the delimiter and select Finish. if you want to stick with a formula you can modify...

Excel: Binary Variables in Excel

Excel: Binary Variables in Excel

Excel: Copying formulas in Excel 2007, btw

Excel: Copying formulas in Excel 2007, btw

Excel: long to wide transformation of panel data with many variables

Excel: long to wide transformation of panel data with many variables

Excel: Paste Special

Excel: Paste Special

Excel: summarizing dates in excel, legitimate answer, educational interest

Excel: summarizing dates in excel, legitimate answer, educational interest, initial question

Excel: Using Group/Ungroup symbols (+/-) when sheet protected, visual basic for applications, correct answer

Excel: Using Group/Ungroup symbols (+/-) when sheet protected, visual basic for applications, correct answer, worksheet example

Excel: How to find the duplicate values in multiple sheets?, conditional formatting, shahid

conditional formatting, shahid, a10: No, you will have to enter that formula in each and every sheet, except for the summary sheet; and you have to drag that cell down till the last required row so that it checks every row of data. I know it is tedious, but it must be done. The summary sheet...