aidan, heritage, virgin: OK, basically no problem - though I m not 100% sure why data is being put onto other sheets and then bringing back onto the main sheet - but I d need a lot more detail to write such a macro - if it helps to send me a sample file you can get me at aidan.heritage@virgin.ne...

Excel: VBA Adjustment, blank row, relevant area

blank row, relevant area, spreadsheets: Vish The best way would probably be to have the relevant area on the files in the folder have a range name. If you named the range AreaToFormat you would be able to address the range with ActiveSheet.ListObjects.Add(xlSrcRange, Range( AreaToFormat...

Excel: VBA - Autofit merged cells causing protection issue, target cells, relevant cells

target cells, relevant cells, format cells: Protecting a sheet means you can t change the contents of locked cells. You run a macro that protects the sheet; therefore, you can t then e.g. delete a word from the cell s contents. Do you want the sheet protected or not? If not, then remove the ActiveSheet.Protect...

Excel: VBA Coding, Sixthsense

Sixthsense: Hi Mohammad, It is not possible using the VBA to convert the PDF file to excel. You have to use some third party softwares to do the work. Do a google search mentioning the search keyword as PDF to excel coverter you will get the list of software names...

Excel: VBA Coding, csv files, column method

csv files, column method, mybook: You didn’t specify version of excel, which MIGHT matter as far as getting the full list is concerned – opening the file can be recorded so is easy enough to incorporate into a macro – I’ve GUESSED that the files are CSV files, so my version of a recorded macro...

Excel: VBA find, expand range, copy and paste onto another sheet, cell b6, target value

cell b6, target value, target address: Paolo Is seems like you could use the MATCH and OFFSET functions to have the active recipe (the one selected with the validation in B10)picked up dynamically. If, for example the recipe names are in column A and there are only 4 rows per recipe (like...

Excel: VBA or IF function?, 5s, vba

5s, vba, user friendliness: How do you determine whether the 1 becomes a 5 or a 10? A formula solution is probably best & has nothing to do with user-friendliness. Something like: =IF(D$1= Yes ,5,1) which would handle the 1 s (again-- I don t know when it should be a 10), so this...

Excel: VBA Help, rw2, keyboard shortcut

rw2, keyboard shortcut, sh2: The line to change is the one with the 2702 reference in it - my method would be ActiveSheet.ListObjects.Add(xlSrcRange, Range( $A$1:$H$ & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row), , xlYes).Name = Table1 I d also look to remove...

Excel: VBA Macro won't run, counta, vba macro

counta, vba macro, h7: Hi Timothy How are you? Please have a look at Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim RC As Range, R1 As Range, conta As Integer Set RC = ActiveSheet.Range( K7:K31 ) Set R1 = ActiveSheet.Range( H7 ) conta...

Excel: Vlookup formula, c500, hth

c500, hth, e2: If you have John and Smith in cells E1 and E2, for example, then this is the formula you want -- it MUST be entered with Ctrl+Shift+enter: =INDEX(C:C,MATCH(E1&E2,B1:B500&C1:C500,0)) or, as text: =INDEX(C:C,MATCH( JOHNSMITH ,B1:B500&C1:C500,0)) HT...

Excel: Wheat data, wheat samples, protein machine

wheat samples, protein machine, white wheat: * No worries - we all have our specialties, I have never even used SAS (yet - I m always learning!), and I do relate to what you re saying about Access though. I never run Insert queries, but I think I know what you mean just from a select query. Anyway,...

Excel: Working with Worksheet_Change, data changes, target

data changes, target, hasn: * It s kind of hard to use this code for myself and test it out, since you re using a lot of named ranges that I don t have of course. But anyway, what specifically is not working about the code? What error are you getting - or what is not happening...

Excel: wildcard formula for Excel?, relevant cells, format cells

relevant cells, format cells, instructional technologist: Hi Gary, You can try customizing your number format (Format Cells) by selecting the relevant cells, make the formatting change (i.e. 1. ###) and applying the format. Let me know if this works Kindest Regards, Mary A. Lindsey Instructional Technologist...

Excel: wrong email hyperlink definition, Sixthsense

Sixthsense: Hi Eric, Just select the range or column on which the email addresses are present and do copy (Cntrl+C) and select blank cell or column and do Right Click Paste Special Select Values Option Button and give ok. Take your mouse pointer on the email...

Excel: Zero's in a pivot table, zero rows, zero results

zero rows, zero results, zero result: Gail I am not aware of a way to suppress zeros,and unfortunately you can t use a regular or advanced filter on a PT. If the zero results from all the entries in your data source having a zero value, you can put that particular data column as a page field...

Excel: Accessing a User form, target value, reply reply

target value, reply reply, vb module: Mike, it is best to keep things simple. Load Reply Reply.fpolicy.Value = Target Set rng = Activesheet.Cells.Find(What:=Target, _ After:=Activesheet.Range( A1 ), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ ...

Excel: how to add certain values, empty cells, freights

empty cells, freights, waqas: You could use a pivot table to get a complete summary, or you could use SUMIFS (in excel 2007 or later) or SUMPRODUCT to achieve your aim =SUMPRODUCT(--(ISBLANK(A1:A3)),--(NOT(ISBLANK(F1:F3))),G1:G3) amend a1:a3, f1:f3 to the actual ranges of A and F,...

Excel: Add [X] points to Auto-fitted Row height, number 0, cell a1

number 0, cell a1, ochs: Ryan, My name isn t TIM. It is TOM. I can t think of any situation that would cause that - but try this. That should ignore the error and keep processing Sub SetRowHeight() Dim r As Range, cell As Range Set r = Range( A1 , Cells(Rows.Count, ...

Excel: Adding names from Excel to an Access table, vba project, query design

vba project, query design, strsql: * I only recently started using this more. But boy, was I happy with my newfound habit. Using DAO (or some people would prefer ADO, but they re close to identical for most purposes) code to DIRECTLY execute sql statements and really add records into an...

Excel: Apply autofilter to multiple worksheets and copy columns to another workbook, copy columns, osh

copy columns, osh, hi thanks: Hi Archie, I m sorry, I just assumed the source workbook would be the active workbook. Modify my code as follows: Sub FilterAndCopy() Dim oSh As Worksheet Dim oTargetSheet As Worksheet Change the name of the workbook to your master workbook...

Excel: how to do the arrow goes down before copying in VBA, vba code, cursor

vba code, cursor, arrow: Silvio, that is the first thing I gave you becuase it wasn t clear what you wanted to do. It looked like you would put code below the rw = rw + 1 and if you did that, then it would have done what I understood you to describe. I can t read you mind...

Excel: Auto copy between cells, marriott hotel, auto copy

marriott hotel, auto copy, sheet1: At it s simplest, a formula on sheet 2 referencing sheet 1 would do it - simply have these pre-filled (probably with an IF statement to leave blank if blank on sheet1). You could also use VBA to update cells when data is changed - but I ve limited information...

Excel: Auto import data into a 2nd sheet, message taker, control thanks

message taker, control thanks, tab view: Mike, I think it is the same amount of code. set sh = Worksheets(me.TextboxK.Text) rw = 20 assume you are filling out some kind of form/format on the view sheet sh.Cells(rw, A ).Value = me.Textbox1.Text set sh1 = worksheets( Data ) lastrow...

Excel: avoid #value!, invisible space, error value

invisible space, error value, numerical value: There s some information that you re missing in posting this question, and therefore, I am missing too. Because, if A1 is displaying a valid numerical value (result of a formula, or not, either way)...And B1 literally has nothing in it, Then: If...

Excel: Calculate average in excel, richard florida, sure what your question

richard florida, sure what your question, florida usa: Sara The formula =AVERAGE(A1:A10) Will calculate the average for all of the cells that have data in them, if a cell doesn t have a grade in it then it will not include it in the calculation. I m not sure what your question is regarding the #N/A error...

Excel: Calculation on SLA, allexperts, sla

allexperts, sla, saturday and sunday: I ve answered various questions related to SLA, and spent about a week in a previous job devising a formula for such a situation (mine had calls logged outside and fixed outside of hours, we DID work Saturdays, we did have to allow for non available time,...

Excel: CELL MODIFICATION DATE UPDATION IN DIFFERRENT LOCATION, excel formula, richard florida

excel formula, richard florida, updation: POLARO there is a pretty good explanation of time/date stamping in the help section, try the attached website I think you will find your answer there. http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/ Hope this helps Richard Florida...

Excel: Cell references based on a max value in Excel, row headings, column headings

row headings, column headings, cell references: To get the cell address of the cell with the max value in it for column A: =ADDRESS(MATCH(MAX(A:A),A:A, 0), 1, 4) For column B: =ADDRESS(MATCH(MAX(B:B),B:B, 0), 2, 4) To string them together: =ADDRESS(MATCH(MAX(A:A),A:A,...

Excel: Cell Separation for Database Mailing, delimeter, database mailing

delimeter, database mailing, cell separation: You can separate city, state, zip by selecting the column and using Data/Text to columns, using space as a delimeter, and maybe a 2nd go-around using a comma as a separator (if it s city, state zip -- with that comma). For column G, you d be better off...

Excel: Where two cells cross what is their value, column index, a25

column index, a25, index number: Francis, I assume 12345 is in cell A25 on say Sheet1 and you have a date in H5 on Sheet1. =offset(Sheet1!$A$25,0,column(H25)-1,0,0) now if you have the date in a cell and the product code in another cell and you want to find where those are and return...

Excel: Change date when specific cell changes, target value, change column

target value, change column, column c: Karen, lightly tested, but see if this works for you Private Sub Worksheet_Change(ByVal Target As Range) Dim v as Variant If Target.Count 1 Then Exit Sub If Target.Column = 3 And Target.Row 1 Then If Len(Trim(Target.Value)) 0 Then On Error...

Excel: how to change a whole column of C, empty column, cell c2

empty column, cell c2, ogilvy: Mark, assume your data is in C1:C1200 assume you next empty column is column M in the name box to the left of the formula box on the formula bar, type in M2:M1200 and hit enter this select M2 to M1200 with M2 as the activeCell. go to the formula...

Excel: Clearing content of duplicate text, rng, rrr

rng, rrr, ogilvy: Hello -Rush- Sub ABC() Dim lastrow As Long, rStart As Range Dim rng As Range, rng1 As Range, cell As Range lastrow = Cells(Rows.Count, A ).End(xlUp).Row Set rStart = Range( A1 ) Set rng = Range(rStart.Offset(1, 0), Cells(lastrow, A )) For Each...

Excel: Collection of Data, horizontal orientation, vertical orientation

horizontal orientation, vertical orientation, header row: This does what you requested: Sub ToDUMP() Dim d As Worksheet, sh As Worksheet, n As Integer, LookFor As String On Error Resume Next Set d = Sheets( DUMP ) For i = 1 To d.Range( XFD1 ).End(xlToLeft).Column LookFor = d.Cells(1, i).Value...

Excel: Compare two worksheets using VBA in Microsoft Excel code not working, application object error, exceltip

application object error, exceltip, excel code: Pam, You are in this loop when you get the error: For c = 1 To maxC Application.StatusBar = Comparing cells & Format(c / maxC, 0 % ) & ... For r = 1 To maxR cf1 = cf2 = On Error Resume...

Excel: Some concerns with Macro's, sql info, sheet stock

sql info, sheet stock, excel spread sheet: If you provided the macro for examination we could discuss it more directly. In general, a recorded macro that applies an AutoFilter will work correctly. But a macro that copies a range based on a recorded action will, indeed, risk being too short in...

Excel: Conditional formating, array formula, cph

array formula, cph, formating: Rakesh ghole, Assume your first data point is in row 2 in columns A:D and A1 is the active cell when you select all your data (from row 2 down ) and open conditional formatting. You would use a formula to determine if the formatting should be applied ...

Excel: Conditional formating based on formula, conditional formatting, paste values

conditional formatting, paste values, formating: Tom, conditional formatting is a format - so if you copy and paste (within excel), you overwrite your formatting/conditional formatting and it no longer exists when you just do a paste. If you do a copy, then do a paste values in the paste dropdown - then...

Excel: Conditional Formating Color values, jp jp, pivot tables

jp jp, pivot tables, many different ways: JP, Since you rated me the worst expert in the group, what kind of answer were you looking for???? it is usually helpful to have a detailed descrition with illustrations when encountering new concepts if you are a new user as you state. Therefore,...

Excel: Conditional Formatting, Excel Sixthsense Excel Highlight Referenced Cell

Excel Sixthsense Excel Highlight Referenced Cell: Hi Parantapkumar, I have posted your query in Microsoft Answers and got the below solution. a href= http://answers.microsoft.com/en-us/office/forum/office_2007-excel/comparing-formula-with-cell-reference-criteria-in/8c07dc34-e4bc-4360-94ec-740fd0c822fe?page=1&tm=1320764854191#footer...

Excel: Conditional Formatting in Excel 2007, conditional formatting in excel, certification dates

conditional formatting in excel, certification dates, false test: Use CONDITIONAL FORMATTING to accomplish this. The formulas used in CF are TRUE/FALSE formulas, so they may look a little odd to you if you ve never seen on before. If the dates were in M2:M1000 (or however deep), then a TRUE/FALSE test to see if the...

Excel: Conditional range copy, parent cells, cell1

parent cells, cell1, sheet1: Carry ----- line wrap problem corrected - but this macro assumes that the same items are on the same row in sheets 1 and 2 as assumed in the first problem - so this macro will not work if they are not and the workbook you sent indicates that they are not...

Excel: Convert base 58 to 10 and around in Excel 2010 macro, base58, question index

base58, question index, exit function: Function Base58(rg As String) As Double Dim x As String x = 0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUV q = 1 For i = Len(rg) To 1 Step -1 pos = InStr(x, Mid(rg, i, 1)) - 1 If pos = -1 Then ...

Excel: Copy cells to another worksheet based on a value, value question, double entries

value question, double entries, n300: Your macro was quite close, but you have used N in a confusing way, and hadn t specified the type of value it should contain - try Sub Inventory() Dim n As Range Dim Y With Worksheets( Sheet2 ) Set n = .Range( A3 ) End With For...

Excel: Copy the first 30 words from a cell, ck1, formula approach

ck1, formula approach, b david: David, Actually, you can use any character in place of the 1. You just have to make sure that character does not appear in the string you are working with. I have used # (I mostly see this type of formula approach using a # character, so I would suggest...

Excel: copy just numbers, copy worksheets, excel worksheet

copy worksheets, excel worksheet, ogilvy: Silvio, Range( A1 ).copy Range( B1 ).PasteSpecial xlValue or Range( B1 ).Value = Range( A1 ).value both cells and range refer to locations in an excel worksheet. The methods for each are the same so the cell equivalent would be Cells(1,...

Excel: Copy/Paste/Concatenate Macro, Excel Sixthsense Excel Concatrange

Excel Sixthsense Excel Concatrange: Hi Brian, If you re new to macros, you may want to read David McRitchie s intro at: a href= http://www.mvps.org/dmcritchie/excel/getstarted.htm http://www.mvps.org/dmcritchie/excel/getstarted.htm /a *************************************************************************...

Excel: Copy/Paste/Concatenate Macro, cell b1, blank cell

cell b1, blank cell, stg: Sub Attacher() Dim stg As String, n As Integer n = 1 Range( C60000 ).End(xlUp).Offset(1, -1).Value = END For i = 1 To Range( C60000 ).End(xlUp).Row + 1 If Cells(i, 2).Value = Then Again: stg = stg & Cells(i, 3).Value...

Excel: Copy populated cell, leave blank if empty, conditional formatting, data validation

conditional formatting, data validation, blank cell: You have a number of options - the value you are seeing is the time equivalent of zero, which is the value of the blank cell - so you COULD use conditional formatting to set a zero value to white text - white text on white background isn t visible, though...

Excel: Copy sheet by vba, array argument, sheet names

array argument, sheet names, shift report: vimal, Yes, it can be done. It depends on how your list of sheet names is oriented. If they are all in a single column and each cell in the range contains a legitimate sheet name, then it would be like this Sub XXX_VerticalRange() Dim v As Variant...

Excel: Copying Formula from worksheet to new worksheet, excel workbooks, richard florida

excel workbooks, richard florida, e24: Andy The best way to do it is the way you have suggested, by copying the existing file and renaming it, this will rename the formulas. I otherwise do not know of an easy way to accomplish what you want. Sorry I couldn t give you a better suggestion....

Excel: copying values using a macro, q cells, srow

q cells, srow, c cells: The default property of a range is its Value, so you should be OK there. In future, when in doubt which property you re addressing, use the property instead of relying on the default one. Not sure what is wrong with your code, it does look as if it is doing...

Excel: =COUNTIF and Duplicates, Excel Sixthsense Excel Countif

Excel Sixthsense Excel Countif: Hi Ray, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. Click or copy and paste the below link to Download the File:- a href= https://docs.google.com/leaf?id=0B2TMxNyEPQ2rNGQzZjdiN2UtNDllNS00MzZiLTkwZGItYjE3MTljNTMwZjI4&hl=en_US...

Excel: Creating a Formula to extract Variant Data from codes in a Cell, nfl football game, college football scores

nfl football game, college football scores, top 25 college football scores: Not very attractive formulas, but they work: 1) =LEFT(MID($A2, FIND(B$1,$A2)+11,LEN($A2)), FIND( ; ,MID($A2, FIND(B$1,$A2)+11,LEN($A2)))-1) 2) =LEFT(MID($A6, FIND( 2) , $A6), LEN($A6)), FIND( /= , MID($A6, FIND( 2) , $A6), LEN($A6)), FIND( 4) ,MID($A6,...

Excel: Creating range names, range names, shape size

range names, shape size, excel workbooks: Hi Ed How are you? Please have a look at: Sub RNtoWB() rncount = ActiveWorkbook.Names.Count Dim Rx As Range Set Rx = Worksheets(1).Range( a1:a1 ) For I = 1 To 1 x = Rx.Cells(I, 1) MsgBox x Y = Rx.Cells(I, 2) ...

Excel: custom function, "maxif", max function, sumproduct

max function, sumproduct, desired results: Hi Jonathan! How are you? Please see the following: R1 is the range A2:A10 e R2 is E2, then E3, then E4 Public Function MaxIf(R1 As Range, R2 As Range) M = -9999 For I = 1 To R1.Rows.Count If R1.Cells(I, 1) = R2.Cells(1, 1) Then If...

Excel: Data Entry Macro, column c, fifth column

column c, fifth column, g10: Wayne, Macros don t support this type of activity. However, you can select say C2:G10 with C2 as the active cell. Then just keep hitting tab as you make an entry (instead of enter) and this will do exactly what you describe - just complete your entry...

Excel: Data sorting, cell a1, xls file

cell a1, xls file, paste data: Zaina, Sorry - I think I got myself confused. It is difficult to write code with no ability to test it - so your going to have to bear with me. but I believe this will work. Sub abc() Dim sh As Worksheet, sh1 As Worksheet, sh2 As Worksheet Dim...

Excel: Date Reminders, conditional formatting, excel spreadsheet

conditional formatting, excel spreadsheet, richard florida: I am thinking that the easiest way would be to use conditional formatting and use the greater than feature. for example if you want a cell that has a date in it greater than 10/4/11, you could simply use the conditional formatting feature to highlight that...

Excel: deleting words from excel, array formula, excel formula

array formula, excel formula, letter example: http://www.dailydoseofexcel.com/archives/2007/02/21/find-position-of-first-capital-letter-in-a-string/ has a starting point for getting the FIRST capital – this gives =RIGHT(B15,LEN(B15)-MATCH(TRUE,ABS(CODE(MID($B$15,ROW($1:$255),1))-77.5) 13,0)+1) As an...

Excel: Dynamic Hyperlink, names thanks, excel template

names thanks, excel template, text functions: CREATE HYPERLINKED DIRECTORY LISTING Here s a macro for creating a hyperlinked listing of files in a chosen directory that match a certain file type (or all). Macro will list the last modified time stamp as well. A sample file is include on the page: ...

Excel: dynamic movement of Data Date bar for progress tracker, s curve, scatter chart

s curve, scatter chart, date bar: Ram To plot a vertical line on a chart I usually use a data range that includes only 4 cells Today() 0 Today() top point on vertical line This only works if the chart type is an x-y scatter chart. It will draw a vertical line from today s...

Excel: Excel 2003 - Make cells in two columns required, blank cells, excel spreadsheet

blank cells, excel spreadsheet, wks: Stephanie, Your code right now gives the impression that something must be in every cell in columns S and T - however, I can t believe that to be true. So you need to tell me what cells I need to have an entry - if it isn t a constant list of cells, then...

Excel: Excel 2003 Macro, circle button, area of a circle

circle button, area of a circle, return button: Jim, If you want to send me a sample sheet, I can take a look. twogilvy@msn.com tell me exactly what you want to do - I assume the buttons are in place and you just want code added (or you want me to tell you what that code should look like - do you...

Excel: Excel 2007, dropdown arrow, data validation

dropdown arrow, data validation, cell a1: Enid, It sounds like you want to have a data validation dropdown box in cell A1 (use the list option in data validation). In data validation you would also go to the error tab and unselect the top checkbox: Show Error Alert after invalid data is entered...

Excel: Excel 2007 (Changing Date To Real Date), Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Hermann, I have created a sample file for you, Click or Copy and paste the below link in web browser and download the Example file. a href= https://docs.google.com/leaf?id=0B2TMxNyEPQ2rYWU4ZjQ2NmMtZTNlMS00NmZlLWJjZjQtMTM2ZmQyYjdjODdl&hl=en_US https://docs.google.com/leaf?id=0B2TMxNyEPQ2rYWU4ZjQ2NmMtZTNlMS00NmZlLWJjZjQtMTM2ZmQyYjdjODdl&hl=en_US...

Excel: Excel 2007 nesting IF, vlookup, table cells

vlookup, table cells, chv: * Yes ... Put the lookup values in a table (I don t use actual, official tables much in Excel although some people swear by them, but when I say table, I just mean two columns intentionally placed next to each other LOL)..... like: 1. Office 2. ...

Excel: Excel 2007 Search and Replace, cell a1, initial unit

cell a1, initial unit, unit numbers: Hello Kath, Thank you for your question. This should be pretty easy to solve. Assuming your data is in column A, beginning with A1, you will need a helper column to place the data in the format you desire. Let s assume you are using Column B as the...

Excel: Excel, cell b1, variable value

cell b1, variable value, formulae: Neither Goal Seek nor Solver are appropriate for this task. However, it seems like it s straight algebra and you really have TO solve this equation for B1: TAN(5*5*(1/10^2-1/B1^2))=1.23*SQRT(1/B1^2-1/400)/SQRT(1/100-1/B1^2) or x: TAN(5*5*(1/10^2-1/x^2))=1.23*SQRT(1/x^2-1/400)/SQRT(1/100-1/x^2)...

Excel: Excel, math formula, cell b1

math formula, cell b1, variable value: Hi Kahn How are you? Please do t5he following 1. Type 11 in B1 2. Type = TAN(5*5*(1/10^2-1/B1^2)) in A1 3. Type =1.23*SQRT(1/B1^2+1/400)/SQRT(1/100-1/B1^2) (there is a problem with your math formula... I just replace the - for + on the argument...

Excel: Excel, cell b1, richard florida

cell b1, richard florida, variable value: Talha I am not sure what the question is, but the formula you have given me will yield a NUM# error because the result of the second formula is a negative number. You can change the formula to be a positive and it will work. For example. =SQRT(1/B1^2-1/400)/SQRT(-(1/100-1/B1^2))...

Excel: Excel, cell d1, cell b1

cell d1, cell b1, optimal value: If the SOLVER has not been installed/activated, be sure to add that under TOOLS ADDINS. Then use that feature to figure out the optimal value in B1. In D1 you could add another formula: =IF(ISERROR(A1-A2), 1, A1-A2) Then run the solver checking...

Excel: excel, lotus 1 2 3, richard florida

lotus 1 2 3, richard florida, florida usa: Kathy If I understand your question correctly, I do not know of a way to copy the range represented by a name by simply copying the name of the range. I do not work with names very often, so my experience is limited, therefore I suggest you might reask...

Excel: Excel, impossible levels, time need

impossible levels, time need, saturday and sunday: This is the kind of thing that spins to impossible levels as you add more and more criteria. This ugly horrible formula seems to do what you ve asked, but you ll have to see. Any more new criteria and I m not sure this ugly formula can expand a whole lot...

Excel: excel-7 if formula more than 64 levels, excel 7, raca

excel 7, raca: Hi Sanjay, Put your information in cells (names and numbers next to each other in column A and B). Then use the VLOOKUP worksheet function to get your data. I assumed your names and numbers are on Sheet2, cells A1:B100: =VLOOKUP(C667,Sheet2!A$1$:$B$100,2,FALSE...

Excel: Excel automation, vlookup formula, routine work

vlookup formula, routine work, macro recorder: Sure. Turn on the macro recorder and record you doing this whole process one time from start to finish. That recorded macro will be crude, for sure, but might actually work and be reusable. Post the resulting macro back here and I can help you tweak...

Excel: excel control/activeX toolbox, activex controls that, control activex

activex controls that, control activex, control toolbox: William, In Excel 2007, if I go to the Developer Tab, then I can insert Form Controls or ActiveX controls. I haven t seen the term control toolbox used in Excel 2007 or Excel 2010. You essentially say as much when you say: In excel 2007 and 2010,...

Excel: excel control/activeX toolbox, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi William, Refer the attached screenshot and check whether that helps. I hope that the below link will give some idea about the subject which you are asking for. a href= http://peltiertech.com/WordPress/forms-controls-and-activex-controls-in-excel/...

Excel: excel control/activeX toolbox, control activex, control toolbox

control activex, control toolbox, button combo: Hi William, In Excel 2003, the control sets are on two separate toolbars: The control toolbox (ActiveX in 2007/2010) and the Forms toolbar. As far as I know, the number of tabs on the control properties dialog are the same in 2003/2007/2010, provided you...

Excel: excel control/activeX toolbox, control activex, control toolbox

control activex, control toolbox, control tab: There are two versions of controls in excel 2003 - the forms toolbar is the older version, the control toolbar the newer. I would suggest using the control toolbar - from here, select the control you want and draw it onto the worksheet - right click to...

Excel: excel control/activeX toolbox, control activex, control toolbox

control activex, control toolbox, activex controls: No you can t add in the new 2007+ features into the 2003. Just use 2007+. For controlling colors of Controls in 2003, right-click the control and select Properties, then use the properties window to set font, color, background, etc. It s not a a friendly...

Excel: Excel Data Combinations, cell b2, lookup tables

cell b2, lookup tables, group number: Sam, OK. J2: =((F2-1)*36+1)+((G2-1)*12)+((H2-1)*6)+I2 then drag fill down the column J. (I used extra parentheses to group the sections of the formula for clarity) That worked for me. It uses group number from 1 to 360 inclusive and each combination...

Excel: excel date, keyboard shortcut, excel 7

keyboard shortcut, excel 7, auto advance: no problem - there are various ways - when auto-filling, excel will do it s best to work out the pattern - with one date the usual pattern would be to increment one at a time so this is what excel does. If you enter the same date twice, select both cells...

Excel: Excel Date range and "now", colomn, ogilvy

colomn, ogilvy, different time: dr. Eugene, I don t know where you live, so I don t know whether your dates are expressed as mm/dd/yyyy or dd/mm/yyyy but for purposes of the formula and the sample dates you gave, it really doesn t affect the formula. I will show results o f Block...

Excel: Excel drop down lists, drop down boxes, listboxes

drop down boxes, listboxes, multiplier: From the description, it sounds as though the second box will return a single value based on whatever the first box shows - at which point, it doesn t need to be a drop down box - just vlookup will do fine - this would look up the chosen item and return the...

Excel: Excel Formula, excel formula, proper answer

excel formula, proper answer, basic v: Hello Kenneth, Thank you for your question. In order to give you a proper answer I need to know more about your spreadsheet and how the data is laid out. Is it possible to sort or filter the entries and find the left over dollar amounts that do not...

Excel: Excel formula troubles, cell a2, array formula

cell a2, array formula, cell a1: Gennadi, =SUM(IF(LEFT($A$1:$A$10,1)= A ,MID($A$1:$A$10,2,255),0)*1) entered with Ctrl+shift+enter (hold down the Ctrl and shift keys and hit Enter) rather than just enter (since this is an array formula) will do what you describe. You can replace...

Excel: Excel Formulas, cell h4, math function

cell h4, math function, excel formulas: Malcolm, since you only want the link on every other line, the IF statement makes that conditional decision as the formula is drag filled down the column row() returns the row number of the cell containing the formula the mod function is a math function....

Excel: Excel Loops to test two conditions, smax, max value

smax, max value, a2: Something like this: Option Explicit Sub SolverSpecial() Dim sMAX As Double With ActiveSheet sMAX = .[A1].Value If sMAX = 0 Or sMAX = .[A2] Then MsgBox Please set the max value in A1 higher than A2 Exit Sub End...

Excel: Excel Macro Date Weekly, format date, excel macro

format date, excel macro, timevalue: In order to save it Friday at 7PM the workbook MUST be open at that time. In the workbook s Open event, put this code: Private Sub Workbook_Open() Application.OnTime TimeValue( 7:00:00 PM ), runifFriday End Sub and in a regular module, put this...

Excel: Excel Macro / Sequential Numbering, cell a1, project 101

cell a1, project 101, astericks: Paul, since you asked for a macro, I assume you know how to use macros. Assume the worksheet to be duplicated will be the active sheet assume cell A1 of that sheet holds data like *Project-101* *Type-Checklist* *Item-No-1* including the astericks....

Excel: EXCEL PROBLEMS., rs 1, pmt

rs 1, pmt, fir: at it s simplest a search and replace (three times over) would do it - you could also use a formula - the SUBSTITUTE worksheet function replaces characters with others - so =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, + , ), - , ), , ) would remove all...

Excel: Excel Sorting Terms, result row, filter selection

result row, filter selection, home tab: Kee, select all your data including row 1 and on the home tab in the ribbon, go to the right side and select sort & filter, then select filter. Now you will have dropdown arrows in row 1. In the column C dropdown (click on it to get the submenu),...

Excel: excel spreadsheet, car windshield, auction listing

car windshield, auction listing, isnumber: Seems like you would have some sort of 2-column lookup table. Say column D had the list of words to search for, then column E had the resulting word you would like displayed of the searched word appears in a title in column A. Then you could put this...

Excel: excel spreadsheet, car windshield, search worksheet

car windshield, search worksheet, auction listing: A relatively easy one this time - use a blank column on the perfect spreadsheet and put a countif formula which is along the lines of =countif(CorrectSKUList,YourSKUCell) fill this down, then use the autofilter to filter on zero s - these don t exist...

Excel: extracting usable info from a text string, cell a1, usable info

cell a1, usable info, countif: Barry, assume that your first sample string is in cell A1 (and second in A2, etc), so in B1: =IF(COUNTIF(A1, *FT* ) 0,MID(A1,FIND( FT ,A1),255),IF(COUNTIF(A1, *MTS:* ) 0,MID(A1,FIND( MTS: ,A1),255), )) then drag fill down column B. I also assume...

Excel: Extracting values from another worksheet, input criteria, sheet names

input criteria, sheet names, range values: Tithee, ------------- Why the extremely negative rating. I can assue you that based on what you asked/described, that this solution works very well? Did you get a better answer from someone else. Did this not work. I put in my best effort to provide...

Excel: Excel, error value, b2

Excel: Excel, error value, b2, b1

Excel: Macro to Copy and Paste, list of names, blank lines

Excel: Macro to Copy and Paste, list of names, blank lines, copy and paste

Excel: Macros, kind response, execution

Excel: Macros, kind response, execution

Excel: Macros, cell b1, a2

Excel: Macros, cell b1, a2, execution

Excel: File Problem, time interval, recovery mode

time interval, recovery mode, richard florida: ecsuraweera You can change the auto save feature to eliminate it altogether for this workbook or change the time interval for saving workbooks. Click on the excel office button (top left) then click on excel options at the bottom right of the drop...

Excel: Formula to calculate what percentage of groups have completed a course, resource scheduler, false values

resource scheduler, false values, position name: =sumproduct((B7:B23= Admin )+(B7:B23= Resource Scheduler )+(B7:B23= Ops Support Superintendent )+(B7:B23= Ops Superintendent )) will give you the count of Admin/Super/Resource to get the count of those that completed the course add another condition...

Excel: formula returning a blank, bad habit, space bar

bad habit, space bar, ogilvy: Mike, the best you can do is something like this =if(H5= , ,1.35*(h5*.15)) the cell won t actually be blank because it has the formula in it, but it will appear blank until you put something in H5. If you use the space bar to clear cells (a...

Excel: Formula for Top5 & Bottom5?, row 24, g100

row 24, g100, countless times: The adaptation is incorrect on a few counts. =LARGE(100$g:100$am,ROW(G100)) isn t right. 100$g is not a valud cell reference. Maybe you mean $G100? and Row(G100) should be ROW(A1). The A1 is exact. It has nothing to to with cell A1 except to get the value...

Excel: formulas, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Narendra, Have a look in this links to know about Excel. a href= http://dmcritchie.mvps.org/excel/excel.htm http://dmcritchie.mvps.org/excel/excel.htm /a a href= www.contextures.com www.contextures.com /a a href= www.cpearson.com www.cpearson.com...

Excel: Formulas, sumproduct, g19

sumproduct, g19, males and females: SUMPRODUCT (or SUMIFS) is better. For example, if A, S, P are in column G and M/F in column H, these formula will give the counts: =SUMPRODUCT(N(G7:G19= A ),N(H7:H19= M )) will count male abscences. =SUMPRODUCT(N(G7:G19= A ),N(H7:H19= F )) will count female...

Excel: Formulas spanning multiple sheets, sheet names, sheet1

sheet names, sheet1, b30: Karen, Yes. =Sheet1!B30-Sheet2!B22 or Sheet1!B30-Sum(Sheet2!B5:B21) or =sum(Sheet1!B2:B40)-Sum(Sheet2!B5:B21) would be examples of how you could refer to cells on different sheets. If your sheet names actually have a space as in Sheet...

Excel: IF & AND function, dd mm yy, week3

dd mm yy, week3, yyyy: In general, the syntax is: =IF(AND(A1 =DATEVALUE( m/d1/yyyy ),A1 =DATEVALUE( m/d2/yyyy )), Week1 ,IF(AND(A1 =DATEVALUE( m/d1/yyyy )+7,A1 =DATEVALUE( m/d2/yyyy )+7), Week2 ,IF(AND(A1 =DATEVALUE( m/d1/yyyy )+14,A1 =DATEVALUE( m/d2/yyyy )+14), Week3 ,IF(AND(A1...

Excel: How to generate alpha-numeric value using formula?, excel vba, alpha numeric

excel vba, alpha numeric, numeric value: * I guess there are different ways this could be approached/solved. My first reaction is, and I have to say this, this should be in Access. You re essentially wanting to create standardized records with unique ID s in a pre-determined way, you re wanting...

Excel: Generate invoice Numbers in Userforms, database spreadsheet, initialize event

database spreadsheet, initialize event, invoice numbers: Mike, say your msgID is found in column A of sheet Data then Private Sub CommandButton1_Click() Dim sh as worksheet, r as range set sh = worksheets( Data ) set r = sh.Columns(1) get the max msgID currently in the worksheet Data maxID = Application.Max(r)...

Excel: Giving an option to user to name the file, string s1, spath

string s1, spath, saveas: Navin, Here is a correction. Sub ABC() Dim sPath As String, sName As String Dim s As String, s1 As String sPath = C:Log sName = MGW KPI Analyser.xls s = Dir(sPath & sName) If s Then means a file with that name exists s1 = Application.GetSaveAsFilename(filefilter:=...

Excel: Help with auto-populating in execl spreadsheets, direct email, excel sheet

direct email, excel sheet, data input: Does the data need to be retained in the master sheet, or is tht ONLY being used for input? If I was doing this, I would personally do this via a macro - probably running on the change event of the worksheet - though the structure of the sheet would matter...

Excel: hide columns depending on the month, target address, false application

target address, false application, cell c1: It s possible that Excel would prevent this manually as well, under certain circumstances, like comments existing way off to the right. Please send the offending workbook to me at bobumlas@hotmail.com, use subject of AllExpertsQ and repeat the issue. ...

Excel: Hide Sheet and append, example sheets, visibility

example sheets, visibility: It rather depends what the macro does - a macro can put data on any sheet whether hidden or not, but it won t be able to activate the sheet for example. There is no need to activate a sheet - for example sheets( Sheet1 ).range( A1 ).value=2 would set...

Excel: How to get the higher and the lower number?, column c, worksheet functions

column c, worksheet functions, range c: Silvio, If you are looking at the entire column, why would you care about the last used row. Nonetheless, if you want to do that, you certainly can set r = Range( C1 ,Cells(rows.count, C ).End(xlup)) or if you want to start in row 6 ans1 =...

Excel: hold running, programe, cheers

programe, cheers, cells: Hi Ecsuraweera How are you? Please have a look at the following code and adjust to your problem Sub whilereading() Dim R As Range Set R = ActiveSheet.Range( a1 ) y = Do y = InputBox( Please enter the Payment Type ) Loop Until y ...

Excel: Invoice Numbering, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Deanne, Use the Max function. For example assume that A1 to A5 you have already entered some numbers and you would like to get the next number from A6 onwards. In A6 type the formula as =Max($A$1:$A5)+1 and drag the A6 formula to the remaining cells...

Excel: largest number in variable-sized range, array formula, result table

array formula, result table, pivot table: Barry, the easiest solution would be to use a pivot table and choose the max option for your summarization function. then you wouldn t even need a list of unique ID s. If you need an example, send a file with your data to twogilvy@msn.com an...

Excel: Link code to sheet, button example, command button

button example, command button, active control: Hi Eran How are you? Please do the following 1. Draw a command button 2. Assign to it the following macro Sub Macro1() Sheets( txtDate.text ).Select End Sub or 1. Draw an active control 2. copy Private Sub CommandButton1_Click() Sheets(...

Excel: linked cells between Excel spreadsheets, grocery prices, tiny sheet

grocery prices, tiny sheet, excel spreadsheets: Hi Nancy, Activate the cell of interest. Then, on the Formulas tab of the ribbon, find the Formula Auditing group. In that group, click the Trace Dependents button. It should draw an arrow (or more) starting from the active cell. If you have an arrow...

Excel: list and count how many times each unique value is repeated, admission data, 123456

admission data, 123456, person id: Dave, this explains in great detail how to get your unique list. No use me repeating what already exists: http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/ It assumes you have header...

Excel: Lists, question pool, cell b1

question pool, cell b1, cell c1: Peter, Apparently Edward didn t understand or for whatever reason couldn t address your question right now since I found this question in the Question Pool. Hopefully this is what you want. In C1 I would put the formula =if($B1= , ,Index($A$7:$A$13,Match($B1,$A$1:$A$6,0),1))...

Excel: Locating textboxes on a worksheet, google search, ms courses

google search, ms courses, case text: I think one of the best resources is the macro recorder (especially in xl 2010). Or, be on the lookout for an upcoming Excel user conference led by yours truly -- very beginning stages of formulation - at http://www.bobsexceluserconference.com If you name...

Excel: putting lot of prices, ajmal khan, cell d2

ajmal khan, cell d2, sheet1: Assuming: 1) the part numbers are in column A on both sheets 2) the prices are in column B on both sheets Do this: 1) On sheet1 in cell D2, put this formula to check sheet2 and display the new price if found, the old price if not found: =IF(ISNUMBER(MATCH($A2,...

Excel: Macro, paste command, first selection

paste command, first selection, ogilvy: Wayne You don t need to do a copy to do a fill down - so I don t think you need the first Selection.copy, but I will leave it in there. Range( A1:O1 ).Select Selection.Copy Dim r As Range if Range( count1 ).Value 1 then Set r = Range(...

Excel: Macro, double application, smsg

double application, smsg, strikethrough: Tom, VBA doesn t have a way to get conditional formatting colors or to work with them. Chip Pearson has written hundreds of lines of code to create a generalized code that will read the condions you put into conditional formatting and evaluate weather...

Excel: macro to copy one folder anc rename it, master folder, folder test

master folder, folder test, analysis test: Assuming the value in cell A1 were: H:GarciaJPrivateIdeas Cost analysisTest ...then this might work: ============= Sub CopyTemplateFolder() CreateObject( Scripting.FileSystemObject ).copyfolder _ H:R&DProjectsProject...

Excel: Macro to Copy and Paste, sh1, column c

sh1, column c, cnt: Tae, this macro should do that Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, r1 As Range, cell As Range Dim cnt As Long, res As Variant Set sh1 = Worksheets( Sheet1 ) Set r = sh1.Range( A2:A5 ) For Each cell In r Set r1...

Excel: macro for dropdownlist, data validation, hypens

data validation, hypens, excel sheet: On a blank Excel sheet, cell A2, create in-cell dropdown (using Data Validation) with choices: Payment FDD In D1:D5 enter Payment Payment Payment FDD FDD In E1:E5 enter Investigation Payment posting reversal Invoice printing Invoice...

Excel: Macro to multiple combine CSV files into a new file, run time error, csv files

run time error, csv files, csv data: Subscript out of range means Excel is trying to access something by name and cannot find it. The page noted above lists the parts of the code IN RED that you must adapt to match your working environment. For instance, the first line in red indicates...

Excel: Macros, macro execution, kindness

macro execution, kindness, a2: does this actually have to put a formula in place - surely it would be simpler to do dim N as date,M as date M=range( A1 ).value n=range( B1 ).value dim counter as long counter=2 while M N M=M+1 cells(counter,1).value=M wend which would keep...

Excel: Macros, cell b1, kind response

cell b1, kind response, mydate: 1 - it seems you already have written the macro to do what you want -- is it not working or is it taking too long or what? 2 - can you guarantee that there will be a cell in column A which will equal cell B1? If not, your macro will loop until you reach the...

Excel: Macros, ogilvy, macros

ogilvy, macros, a1: Hello deekshituluchs, Sub copydown() Dim i As Long i = 2 If isdate(Range( B1 )) and isdate(Range( A1 )) then Do Cells(i, A ).FormulaR1C1 = =Date(year(R[-1]C),Month(R[-1]C)+1,1) i = i + 1 Loop While Cells(i - 1, A ) Range( B1...

Excel: Macros, aidan, heritage

aidan, heritage, virgin: Do you have a list on the sheet somewhere of the items that make up the kit - assuming you do, the macro would simply go down each item in the list, returning those that were needed - it might help to see a mock up of the file - my email is aidan.heritage@virgin.net...

Excel: Macros if & ands, conditional formatting, cash receipts

conditional formatting, cash receipts, column c: Select all the cells from A:F, use conditional formatting with this formula (assuming row 2 was the active cell s row): =AND($F2 3,$C2 8000,$C2/$A2 10%) then click Format and assign Bold, and a font of blue (or green) Then select column F and add another...

Excel: MAX Function, vba macro, random orders

vba macro, random orders, sixthsense: As long as it s working that is fine - though the logic of the statement will check ae if the value of AD isn t Saturday, and AI if the value of AD isn t Sunday - which doesn t read quite the same as the original statement? I suspect that if you had the highest...

Excel: Movement of one row, target range, drop down menus

target range, drop down menus, sheet tab: Are you saying that my macro is leaving blank rows in your PreHOM page? It s not supposed to, it was written to completely delete the row from PreHOM, everything else is moved up. Is that not happening? If you want the rows to be left there blank and...

Excel: Merge two or more spreadsheets in Excel 2007, change selection, consolida

change selection, consolida, spreadsheets: Change Selection.Copy Destination:=Sheets(1).Range( A65536 ).End(xlUp) to Selection.Copy Destination:=Sheets(1).Range( A65536 ).End(xlUp).Offset(1) ------------------------------------ Here s a more efficient version of the whole macro: Sub Consolida()...

Excel: Merging 2 excel worksheet, mailing feature, vlookup function

mailing feature, vlookup function, excel sheet: I m not clear what you need to merge, BUT the vlookup function would PROBABLY be the way to bring one set of data in from one worksheet to another one. Let me know if you need help with this function let me know, though I recommend the file to be found at...

Excel: Message box appear if a value of a cell changes, cell changes, best bet

cell changes, best bet, message box: Not an easy task -- the events which MIGHT be responsible are Worksheet_Change event or Worksheet_Calculate event, but neither will trap a change in a specific cell. Your best bet is to save the value of the cell upon workbook open, and in the calculate event...

Excel: Minimum values in one column related to values in another, chris answer, grouping functions

chris answer, grouping functions, query group: * I wouldn t even try to dream up a set of formulas for that, that s more like a query it sounds like that should be in Access. You could probably use Excel VBA to accomplish it, I guess, though. It is a perfect problem that needs to be solved by using...

Excel: MVB question, e mail address, ms office professional

e mail address, ms office professional, offcial website: Gabor, the error message does tell you what the problem is. You have named your module Timesheet and in that module you have a procedure named timesheet - so excel is confused as to which timesheet you are referring to in the workbook_open event. You...

Excel: Need Help in Excel, fundamental restriction, e4 e5

fundamental restriction, e4 e5, 99999999999999999: Hello, What do you mean by Blanking a cell? It can be any of the following: 1) The value in the cell should become Zero 2) The value in the cell should become truly blank... Eg: C1 = 3) The font in the cell can become the same colour as the background...

Excel: Need an Macro for Down Arrow., down arrow, ogilvy

down arrow, ogilvy, single line: Jay, you can try this single line command Range(ActiveCell.Offset(1, 0), Cells(Rows.Count, ActiveCell.Column).End(xlUp)).SpecialCells(xlVisible).Areas(1)(1).Select it may be wrapped in the email, but it is a single line. -- Regards, Tom Ogilvy...

Excel: Count IF, grade a1, test a1

grade a1, test a1, blank cell: Adam You can t count them because of their color; but, if they are a certain color because of a conditional format you should be able to count them based on the same logic that you used in the conditional format to color them. I don t know what that is so...

Excel: Number present as string and not able to perform mathematical operations, empty cells, number present

empty cells, number present, mathematical operations: Naveen, this worked for me. I don t know if it will be faster. Sub ConverttoNumbers() Dim sh As Worksheet, r As Range Dim r1 As Range, r2 As Range Dim cell As Range On Error Resume Next For Each sh In Worksheets Set r = sh.UsedRange Set...

Excel: Number present as string and not able to perform mathematical operations, array formula, empty cells

array formula, empty cells, text numbers: You can do that, or you can adapt your SUM formula to actually sum on the text numbers. If the text numbers are in column I, you can SUM them with a formula like: =SUMPRODUCT((I2:I100)*(1)) To find the MAX of the same text numbers, this array...

Excel: Offset Non Active Sheet, tom hope, userforms

tom hope, userforms, true application: Paul, My userforms were working but i needed to use activex so i added all my controls to a sheet. Useforms do use activeX controls - they only use ActiveX controls so I am not sure of the significance of your statement. Is the solution to now...

Excel: Opening Multiple Text Files with VBA, destination cells, fname

destination cells, fname, txt files: Heh, that looks like something I would write. The problem may be in your perception, TXT files don t have columns, they are flat files. The commas may be delimiters to you, but not to Excel, and not unless you specifically indicate such. Another approach...

Excel: Pivot Table filter in Excel 2007, red nosed reindeer, pivot table

red nosed reindeer, pivot table, pivot tables: He was asking about Windows Live Skydrive. Question and my answer follows: I have a simple Pivot Table uploaded to the web via Windows Live Skydrive. Once uploaded, the Pivot Table shows fine with one exception. Clicking on the funnel icon in the...

Excel: Pivot Table report field formatting, excel 2007 pivot table, filter area

excel 2007 pivot table, filter area, table options: I ve not done a lot with pivot tables in office 2007, and my tables TEND to be fairly unformatted. I can see from discussions that this is an issue, but I ve not got a fix. Possibly the discussion at http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/d68c65b0-8111-4713-87a3-370b87d04cd1/...

Excel: Pivot Table report filter formatting, filter area, pivot table

filter area, pivot table, table options: Hi Al, In your field drop down list select show all and apply your formatting for all drop down list in one shot and after that do individual selection. If the above is not the right answer then requesting you to just send me a screen shot for my better...

Excel: Pivot Table report filter in browser shows Multiple Selections improperly, pivot table, latest versions

pivot table, latest versions, funnel: I m afraid I have no experience of this - it SOUNDS as though it s an issue with Windows Live Skydrive/browser functionality. Microsoft are known for not sticking to strict HTML so it MAY be that that is the issue. Hopefully someone else may be able to help...

Excel: Pivot Table report filter in browser shows Multiple Selections improperly, allexperts, pivot table

allexperts, pivot table, excel vba: Hi Al, I believe this can be achieved using Macro (VBA) but as i mentioned in my profile I am not having proficiency in excel VBA and I request you to try one of the other excel experts that claim expertise in Excel VBA such as Bob Umlas, Tom Oligvy, Stuart...

Excel: problem in formula, dc general, excel sheet

dc general, excel sheet, desai: Open a new Excel sheet. In cell c1439 enter 9. In cell j1 enter 2. In cell j2 enter 3. In cell e1439 enter general. In cell A1 enter =IF(C1439-J1 0, ,C1439-CHOOSE(MATCH(E1439,{ general , 1st , 2nd , Night , DC 1st , DC 2nd , DC Night , DC General },0),J1,J2,J3,I2,K1,K2,K3,K2))...

Excel: question copy and paste VBA, a3 b3, vsource

a3 b3, vsource, paste values: Silvio, You can t do in code what you can t do manually. Generally, you should do each copy and paste separately Dim vSource as Variant, vDest as Variant, shSource as Worksheet Dim shDest as Worksheet, i as long vSource = Array( I4 , J4 , C6 ,...

Excel: Ranking invoices by date, column c, perfect answer

column c, perfect answer, invoices: * I know this may not be what you want to hear, but this needs to be done in Access. this is EXACTLY what access is for. Imagine how simple this query would be in access. Dump all of this data into a really simple table - with nothing but a field...

Excel: Recognising dates as dates rather than numbers, true dates, blank cell

true dates, blank cell, text sort: Gail, If I look at your dates in your example, they are in descending order based on a text sort. Given that, I would guess that your source data has the dates stored as Text Strings rather than dates. If you go to the original data and select the date...

Excel: recognize date & time in VBA excel, vba excel, cell a1

vba excel, cell a1, datevalue: Dustin Your text string is not set up in such a way that excel can recognize it as a date and time. So what you must do is convert the text string into something that may be recognizable by excel as date and time. You may do this by first converting the...

Excel: Relative Cell Reference, relative cell reference, question pool

relative cell reference, question pool, direct email: I think that probably the INDIRECT worksheet function is going to be the one you need to use. However, there is not quite enough information in the question to be certain. Macros would be able to automate most repetitive tasks, though I d need to know...

Excel: Resize a range, cell reference, message application

cell reference, message application, range selection: The statement Set R2 = ... is fine, and sets a VARIABLE called R2 to the wanted range. However, Range( r2 ).Resize(.Rows.Count - 1).Name = r2 is invalid, simply because you can t have a range named R2. That s reserved for the cell R2! Try naming any...

Excel: Resize a range, range selection, w50

range selection, w50, formulae: Hi Carry How are you? (wie geht es dir?) I know the following does nor do what you want, but it is easier to understand. You may restart working from this: Option Base 0 Sub Convert_Formulae_To_Value() Dim ar As Variant, i As Long, r As Range, r1...

Excel: Return blank or a value, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Tia, Try any of the below formula in B3 cell. =IF(B2 ,B1, ) or =IF(B2= , ,B1) The above formula s will check whether the B2 is having any non blank value. If you want to show the B1 cell value when B2 is having only Numeric Number...

Excel: how to get rid from empty rows automatically???, google, waqas

google, waqas, thanks in advance: 1) First get the whole thing working correctly in ONE workbook. Make sure all the data from the DATABASE sheet is appearing in your people sheets properly. 2) On the people sheets, you will have to change the COUNTIF() formula in C1, that s one function...

Excel: how to get rid from empty rows automatically???, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Waqas, Click or Copy and paste the below link in web browser to download the Example files. a href= https://docs.google.com/leaf?id=0B2TMxNyEPQ2rNzU3NjQxNzMtMDIxNS00ZGYzLTkwZjAtMTZkNTU3ZDYxZDhi&hl=en https://docs.google.com/leaf?id=0B2TMxNyEPQ2rNzU3NjQxNzMtMDIxNS00ZGYzLTkwZjAtMTZkNTU3ZDYxZDhi&hl=en...

Excel: rounding a number almost like INTEGER, x cells, 7 digits

x cells, 7 digits, integer type: Silvio instead of Dim x as Integer use Dim x as Long Long is a long integer. It uses four bytes for storage and can handle a range of values (whole numbers) from -2,147,483,648 to 2,147,483,647 A single can store a much larger number,...

Excel: Running a macro on refresh data, ms query, button code

ms query, button code, query result: Hebron If there is a button that needs to be clicked to run the refresh, can t you just copy the code from your refresh button the end of the code for the refresh? Or are you clicking the built in menu item to refresh? If that is the case, you could add...

Excel: Running a macro on refresh data, ms query, vba code

ms query, vba code, query result: * Well, sure - depends on the code you have behind the refresh button? I m not totally clear on that. What exactly is the code you have behind the Refresh button? or did you just right-click on that, and say Assign Macro, and assign it to a macro that...

Excel: search an name in excel, jane doe, client names

jane doe, client names, options button: Hi Dusty, If you click on the OPtions button of the Find dialog, you can tell Excel to search the entire workbook. Alternatively, you can also download my FlexFind addin, which does thiis automatically: http://www.jkp-ads.com/officemarketplaceff-en.as...

Excel: sequential numbering, sequential numbering, template document

sequential numbering, template document, m36: Lisa, You would need a macro to do this. I will hypothesize a situation to illustrate. sheet Name: MyForm on sheet MyForm, the user enters 10 in A1 and 35 in A2 to indicate they need 35 sequentially numbered copies printed starting with the first...

Excel: Sort a selection of data obtained from pivot table details, sort functions, pivot table

sort functions, pivot table, automatic feature: After you double-click, Excel automaatically makes a Table out of the result and the sort you re experiencing is because of the table. Select any one cell, then in the table tools contextual design tab, there s an option to convert it to a range. Then you...

Excel: sorting in excel, column headers, copy paste

column headers, copy paste, spreadsheet: Hello Terry, Thank you for your question. I need to know a little more about your worksheet. Why are you wanting certain rows at the top of the sheet? Is there a way you can filter or sort the data so those rows will show up at the top? Where are...

Excel: IF statement, change event

change event: It SOUNDS as though you want I55 to contain both a value AND a formula, which it cannot do - IF the data is in another cell, then it can be done with a formula - lets say 154 contains the value =if(I54 37.5,37.5,I54) in I55 and =if(I54 37.5,I54-37.5,0)...

Excel: Stopping a formula, datedif, optimal time

datedif, optimal time, excel sheet: Hi Andy, There may be a way to create a macro that uses entry into I1 as a trigger to freeze G1, but my macro knowledge is not deep enough to suggest how to do that. I may be oversimplifying things, but what about changing the Header for Column I to...

Excel: SUBTOTAL combined with SUMPRODUCT, custom programming

custom programming: I m not aware of a way to do that without custom programming, but if you re applying a FILTER to the data via another column of data, then you have some criteria in mind for that filter, yes? If so, that criteria can be applied to get you the result you...

Excel: Sumproduct in VBA, sumproduct, date1

sumproduct, date1, value date: Jun Same way you would in if you wrote the formula in a cell manually myCount = Application.Evaluate( SUMPRODUCT((A2:A10=C1)*(B2:B10=D1)) ) if you actually want to use variables then Sub EFG() Dim Name As String Dim Date1 As Date Dim s As...

Excel: Sumproduct in VBA, sumproduct, ogilvy

sumproduct, ogilvy, cultural differences: Jun, did you try that formula directly in your worksheet. It doesn t work there either. The problem is that the date cells don t equal the string 12/31/09 they probably hold a date serial number if they are actual Excel date values (and I assume they...

Excel: Tab Names, macro code, sheet names

macro code, sheet names, sh1: Fareed, I thought I initialized rw to be 1, but guess I was just thinking that I needed to do that and didn t actually do it. It is done now. The question mark is a wild card for a single characters. If you are opening xl2007 and later workbooks, they...

Excel: Time card - entering time in excel, entering time, negative time

entering time, negative time, time card: No problem- you will basically need to compare each END time with it s equivalent start time, and if the end is before the start then add the 12 hours (0.5) necessary to make the sum work In this example I ve got in, out, in, out at a,b,c and d2 =IF(B2...

Excel: Turn time calculation, conditional formatting, calculation time

conditional formatting, calculation time, time calculation: Kannan First it seems to me that calculation time and turn time are the same. I am not going to give you all of the specific formulas you need, but make some suggestions for you so that you might work it out. the most important thing to remember when working...

Excel: Use value in a cell as parameter to a formula, pull down menu, current value

pull down menu, current value, text content: * Appreciate what you wrote out to explain, but I m still pretty confused. What cell do you want to actually look up? B12, or B20? And what range do you want to search within? At the beginning of your post, it sounded like you want to search...

Excel: Userform Autosize Text Box, pressing my luck, multiline text

pressing my luck, multiline text, text column: Paul, In light testing, this worked fine for me: Private Sub TextBox1_Enter() TextBox1.Height = 50 TextBox1.Width = 250 End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox1.Height = 25.5 TextBox1.Width = 120 End...

Excel: Using the MAX Function, max function, ae

max function, ae, cells: Well, there is no MAXIF function, so I would insert another column to determine whether or not the day was a Sat or Sun... =IF(OR(AD= Sat ,AD= Sun ),0,AE) This will set the value equal to what s in AE if the value is NOT Sat or Sun; otherwise you ll...

Excel: Using the MAX function, array function, max function

array function, max function, data columns: Hebron You are going to have a lot of trouble with this if you try to do it all in one formula. The helper columns don t need to be in the middle of things, nor do they even need to be visible. I think you should stick them off to the side somewhere,...

Excel: Using the MAX function, array formula, max function

array formula, max function, direct email: There didn t seem to be a question attached, but I d do this with an array formula =MAX(IF(C1:C5 sat ,AE1:AE5,0)) entered with ctrl shift enter in this example, column C contains the text sat - though I m guessing in reality your date is actually...

Excel: Using the MAX function, Excel Sixthsense

Excel Sixthsense: Hi Hebron, Try this... Copy the below formula and press F2 in the cell and paste the formula and hit Cntrl+Shift+Enter, since it is an array formula. =MAX(MAX(IF((AD1:AD7 )*(AD1:AD7 Sat ),AE1:AE7,0)),MAX(IF((AD1:AD7 )*(AD1:AD7 Sun ),AI1:AI7,0)),MAX(IF((AD1:AD7...

Excel: Using wildcards with the count functin, alpha characters, allexperts

alpha characters, allexperts, using wildcards: You could use the COUNTIF function... http://www.599cd.com/tips/excel/sumif-countif/?key=AllExperts You ll also need the LEFT function to determine the first character. It would probably be easiest to separate out that first character into the next column:...

Excel: If value of cell in column f = m, then lookup value of cell in same row, different column, then sum, empty cells, deposit balance

empty cells, deposit balance, column c: Dory, -- I have added some thoughts after I read you subject - as I say in my intro or at least I hope I still have that in my intro - please put all information in the body of the post - the subject may not get read ------- from the blue dot, I...

Excel: VBA for adjusting chart range, run time error, run time error 1004

run time error, run time error 1004, current sheet: Dennis You probably just need to completely qualify the Range( ThisManyRows ). In the code that works, I suspect the active sheet is changed to be the sheet with the range ThisManyRows which is why it works in that context. Also, that would explain why...

Excel: VBA assistance, cell d2, excel worksheet

cell d2, excel worksheet, source files: * Sure. will need even more info, though. what is the pattern of source file D2, master file A79. What definition of which cell goes in which? From the source files, you want to just open each one and ONLY get the data from D2 ? And you want...

Excel: VBA Code, vba code, button example

vba code, button example, number 0: Hi Eran, Worksheets(txtDate.Text).Activate Note that an error will occur if the sheet does not exist. You can circumvent that like this: On Error Resume Next Worksheets(txtDate.Text).Activate If Err.Number 0 Then Msgbox Worksheet & txtDate.Text...

Excel: VBA Code to print pdf., de bruin, smsg

de bruin, smsg, outlook application: Abdul Jaleel, I understand you want to email each of the PDF files listed in C3:C1500. I will assume that you have the email address in column E in the same row. (The number of copies isn t relevant to emailing I wouldn t think). Abdul Jaleel, ...

Excel: VBA to create sheets, blank sheet, sanjay

blank sheet, sanjay, constants: Like so: Option Explicit Sub CopyAndCreate() Dim rngName As Range, cel As Range Set rngName = ThisWorkbook.Sheets( Sheet1 ).Range( A:A ).SpecialCells(xlConstants) For Each Cell In rngName Sheets( Sheet2 ).Copy After:=Sheets(Sheets.Count)...

Excel: VBA finction tied to a Cell, data validation, vba programming

data validation, vba programming, source table: Suppose the formulas are in the cells C5:I5. Right-click the sheet s tabe and select View code. The paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range( A:B )) Is Nothing Then Exit Sub Range( C5:I5...

Excel: VBA Macro to move one cell to sheet2 continually adding data, jackie jackie, vba macro

jackie jackie, vba macro, new numbers: Jackie, I would suggest a macro for this. If you want to send a simple workbook I can put a macro in it to show you. Have the checkbox and tell me where you want the data. Also, I am not totally clear about never been to a location and the true/false...

Excel: VBA question, personal workbook, saveas

personal workbook, saveas, target: Abdul Jaleel Sub SubTotal() Dim r as Range set r = Range( A4 ,cells(rows.count, A ).End(xlup)).Resize(,17).Select Selection.SubTotal GroupBy:=14, _ Function:=xlSum, _ TotalList:=Array(10, 11), _...

Excel: VBA to Repeat Macro x times, new timer, macro x

new timer, macro x, input box: You can use the OnTime method to run a macro at particular intervals. Typically, the macro that is run has code at the end to reset the OnTime timer again. You can declare a public variable, above you macros at the top of the module to set a count of how...

Excel: VBA for shapes renaming based on 2 lists combination, shape text, string s1

shape text, string s1, data validation: Shue, Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim shp As Shape Dim nw As Variant Dim old As Variant Dim iloc As Long, s As String, s1 As String in case there is an error... On Error GoTo...

Excel: VBA - Userform Multiple Row Insert, detail line, record description

detail line, record description, description lines: Paul, -- That is kind of vague to say it isn t working. if you want to send me a simple copy of your workbook with your userform and tell me how you want to determine where to input the data, I can take a look. Send it to twogilvy@msn.com -----------------------...

Excel: vlookup, text values, text string

text values, text string, human eye: the N/A result, whether from VLOOKUP or INDEX/MATCH, I would expect to be consistent. Changing from one function to the other doesn t fix the problem in the data. The data that looks the same simply is not, in some fundamental way. If you can create...

Excel: Vlookup, richard florida, column c

richard florida, column c, b20: Alan Assuming that the same combination of material and variant numbers always equal the same Obj Dep. You can use an IFAND formula. The formula might look like this. =IF(AND(Sheet1!A4=Sheet2!A$4:A$20,Sheet1!B4=Sheet2!B$4:B$20),+Sheet2!C$4:C$20) ...

Excel: vlookup, excel user, blank cell

excel user, blank cell, amazon: Not at all trivial. But this works. Enter this formula via ctrl+shift+enter: =VLOOKUP(J22,A1:H10,MATCH(FALSE,ISBLANK(OFFSET($A$1,MATCH(J22,A1:A10,0)-1,2,1,100)),0)+2,FALSE) ----------- Check out my new book here: http://www.mrexcel.com/outsidethebox.html...

Excel: Vlookup Formula, array formula, curly brackets

array formula, curly brackets, result table: Sivaraja, Vlookup doesn t work with two columns (although in your sample data, the lookup doesn t need to look at the second column). you can use === revised formula to use absolute references so it can be copied down ======= =INDEX($D$3:$D$6,MATCH($G4&$H4,$B$3:$B$6&$C$3:$C$6,0),1)...

Excel: Vlookup Formula, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Sivaraja, I have created a sample file for you, Click or Copy and paste the below link in web browser and download the Example file. a href= https://docs.google.com/leaf?id=0B3_dG0BVmYlOYWI4ZjQwZjUtZDJhYS00MjI2LWIwYTItNzc1NTY1NDRkM2E1&sort=name&layout=list&num=50&pli=1...

Excel: Vlookup Formula, database range, result table

database range, result table, database table: Sivaraja I assume you want to lookup the bonus for a particular ID number. In that case the formula =VLOOKUP(ID,Database Table,3,false) will lookup the bonus (column 3) for the particular ID in the database range. I hope this helps. Ke...

Excel: Vlookup formula for oposite columns, relative reference, column c

relative reference, column c, match: Vlookup won t work like that - one solution is to add column C with a formula of =a? where the ? is replaced by the row number, and then you can use vlookup as normal as the data becomes formatted in the correct order. An alternative MIGHT be to use the...

Excel: Vlookup with two variables, vlookup, column c

vlookup, column c, sheet1: Sheet1, F2: enter this formula by first holding Ctrl+Shift, then pressing enter: =INDEX(Sheet2!C:C,MATCH(C2&D2,Sheet2!$A$1:$A$1000&Sheet2!$B$1:$B$1000,0)) G2: =INDEX(Sheet2!D:D,MATCH(C2&D2,Sheet2!$A$1:$A$1000&Sheet2!$B$1:$B$1000,0)) then fill down...

Excel: Workbook_BeforeSave macro won't start, valid filename, saveas

valid filename, saveas, yyyy: Carry You probably don t want this code in the BeforeSave routine. What do you want to trigger the saving of the file? The date formatted as dd.mm.yyyy is probably not going to be a valid filename, so is is at least part of the problem. I hope this...

Excel: workout the number of shifts in a day, shift hours, richard florida

shift hours, richard florida, workdays: Nikki I am a bit confused you say you are trying to work out the number of shifts in a day, but the number of hours you have come uop with is 96 obviously more than what is in a day. Having said that, if your formula is =NETWORKDAYS(B9,C9,0) this...

Excel: last 3 rows, a1 index, cell a1

a1 index, cell a1, b2: Starting from the bottom, I would do it this way: Last unique number B1: =INDEX(A:A, COUNTA(A:A)) Second to last unique number B2: =LOOKUP(2,1/(($A$1:$A$100 C1)*($A$1:$A$100 0)), $A$1:$A$100) Third to last unique number: B3: =LOOKUP(2,1/(($A$1:$A$100...

Excel: last 3 rows, a1 index, cell a1

a1 index, cell a1, b2: Hello. May I know why you want to do this? Perhaps there is a simpler solution for your problem. It is possible to do what you asked for, but it will take a really long formula to do so. In addition there are other queries such as: 1) What if the level before...

Excel: last 3 rows, a1 index, cell a1

a1 index, cell a1, xls: I cannot think of a way to do this with only one formula, but I have come up with a 2 formula solution - see http://www.aidanheritage.byethost3.com/excel/FindLastThreeUniqueValues.xls for the file with this in (it seems easier to give you a working file than...

Excel: Adding variables in user forms?, google searches, concatenation operator

google searches, concatenation operator, variable types: Lee, Here is the situation. The information in a textbox has a data type of string. The + operator used to have double duty as the concatenation operator. The ampersand (&) now has that duty, but the + sign still will work as a concatenation operator....

Excel: auto populate data tables based on a drop down selection, writer author, complete database

writer author, complete database, unauthorized persons: Hi Hayley, Solution I think could be -- (1) Create a complete database on another worksheet (say Data )in same workbook. (2) Protect the worksheet so that unauthorized persons cannot see this. (3) On a worksheet (say Input ), person chooses from drop...

Excel: auto run the VBA cose, target, macros

target, macros, cose: Hi Yoqubjob, Generally speaking, sheet modules are meant for event code: code that responds to things happening in your sheet. You can see which events are available by first clicking in the left-hand dropdown at the top of the sheet s code window and...

Excel: Auto update of date, creating pivot tables, pivot table

creating pivot tables, pivot table, filter section: Given your updated info on worksheet names, simply try this formula... =IFERROR(DATEVALUE(MID(CELL( filename , $A$1), FIND( ] , CELL( filename , $A$1))+1, 32)), NaD ) The cell function returns the full path of the excel file and worksheet name. We use...

Excel: How to Auto-Update Macro on Moving Reference Cells, vba code, formulas in excel

vba code, formulas in excel, sum formula: Maxime, No, vba has no cognizance of what actions you are taking on a worksheet as Excel itself does. Now how to achieve what you want. It would depend on what you mean by moved. Say excel had a formula =sum(E16:E18,F9:F18) then you went to...

Excel: Automate summing number from various time periods, time periods, time ie

time periods, time ie, column c: Cort, I am not following the description nor do I understand how you determine what is to be included in the sum - so if you could give me a complete explanation in the context of the sample file (what you expect the answer to be and why is included in...

Excel: Bold If Macro, b1 and b2, conditional formatting

b1 and b2, conditional formatting, excel user: You re setting B1 and B2 to Strings, not ranges. You need: Set B1=Range( B36:J36 ) Set B2=Range( B37:J37 ) You re comparing strings M36 and N36, not the range values. If range( M36 ).Value = Range( N36 ).Value Then B1.Font.Bold = True Else ...

Excel: Budget Formulas YTD, 6 cells, question pool

6 cells, question pool, budget sheet: Tony Month(Datevalue(June 1, 2011)) returns 6. so =IF(A2= ,0,SUM(OFFSET(Sheet1!$B5,0,0,1,MONTH(DATEVALUE($A2& 1, 2011 ))))) becomes =if(A2, ,0,Sum(Offset(Sheet1!$B5,0,0,1,6)) when A2 contains Jun or June the arguments to offset are...

Excel: call sheets, indirect address, blank cells

indirect address, blank cells, cell a1: Faris, There isn t anyway to put in one formula and have it dynamically pick up the sheet names (unless you use a trick that uses an old Excel4 style macro command). but since you know your sheet names, you could just put them in column A and have the...

Excel: how to go to a cell in a different worksheet, ogilvy, silvio

ogilvy, silvio: Silvio, it depends on whether you want to actually make the cell visible on the screen and select it: Dim r as Range set r = worksheets( Sheet2 ).Range( B9 ) r.parent.Activate r.Cell.Select or Dim r as Range set r = worksheets( Sheet2 ).Range(...

Excel: Changing tab colors, format tab, jj

format tab, jj, spreadsheet: Hi Janie, The only thing I can think of is that you have all of your tabs selected together (to print all tabs, or make a change to all tabs, etc.) Try to unselect all, by finding a tab that is not highlighted, or even adding a new tab and selecting it....

Excel: Check Box Protection, check boxes, 3 things

check boxes, 3 things, checkboxes: Hi Craig, You need to do 3 things for this thing to work. You are already doing the two. 1. Removing TICK mark from Locked Check Box in Format of Check boxes 2. Including linked cells in editable list. But you are perhaps missing one thing. When you...

Excel: Check Boxes, check boxes, excel user

check boxes, excel user, amazon: send a sample to me at bobumlas@hotmail.com, use subject of AllExpertsQ, repeat what you re trying to do. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30, Atlantic City See http://www.bobsexceluserconference.com -----------...

Excel: Choosing which function a cell calculates based on the text in another cell, cell b6, input cell

cell b6, input cell, c10: Jim I don t know of any way to have the value in B6 impact the actual formula that is calculated in B7; but, it would be very easy to have a 5x2 table where you have your 5 functions in a column and the result of those calculations in the adjacent column...

Excel: Close one Workbook when I open other one, time thanks, wb

time thanks, wb, xls: Assuming these macros are running from your PERSONAL.XLS workbook so you can close the other workbooks safely, you can cycle through the workbooks collection and close all workbooks, then open the one you wanted. Sub CloseAllWBs() Dim wb As Workbook...

Excel: Closest Rank, Excel Sixthsense Excel (Rank)

Excel Sixthsense Excel (Rank): Hi Parantapkumar, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. a href= https://docs.google.com/open?id=0B2TMxNyEPQ2rMzgzYzJmNGEtODhjMy00ZDFkLThkMWItYTUyZDliMDJhNzhm https://docs.google.com/open?id=0B2TMxNyEPQ2rMzgzYzJmNGEtODhjMy00ZDFkLThkMWItYTUyZDliMDJhNzhm...

Excel: Closest Rank, hi bob, e11

hi bob, e11, number 22: You need a helper column (which you can hide). Say that s G. In G1 enter: =ABS(IF(OR(D1 $B$1,D1 $C$1),1E+99,D1-$A$1))+ROW()/1000 Fill down to G11 in E1 enter =IF(SMALL($G$1:$G$11,ROW(A1))=1E+99, , Object &MATCH(SMALL($G$1:$G$11,ROW(A1)),$G$1:$G$11,0))...

Excel: no colors in excel, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Richard, Click Start, and then click Control Panel. Click Accessibility Options. On the Display tab, click to clear the Use High Contrast check box. Click OK to close the Accessibility Options dialog box. Feel free to post a follow-up, if your...

Excel: Combination of stacked colum and xy scatter, plot chart, column chart

plot chart, column chart, scatter plot: Hello SK, Excuse me, but my computer has been sent for repairs and I don t have access to Excel, still I will try to solve your problem from the top of my head. Firstly, what are you trying to acheive. Perhaps there is a simpler method to do that thing,...

Excel: Combinations Help, math genius, slow response

math genius, slow response, permutations: Hi Derek, I apologize for my slow response to your question. I got back from vacation on Sunday and have been catching up ever since. From the mathematics viewpoint what you are asking is for the permutations of 75 things taken 3 at a time. This is...

Excel: Combine VLOOKUP with OR, letter state abbreviations, array formula

letter state abbreviations, array formula, array entry: Robert, your discription made it sound like expecting all states would be reasonable. Nonetheless, =INDEX(B:B,MIN(IF(COUNTIF(A:A, DE ),MATCH( DE ,A:A,0),20000),IF(COUNTIF(A:A, MD ),MATCH( MD ,A:A,0),20000),IF(COUNTIF(A:A, PA ),MATCH( PA ,A:A,0),20000)),1)...

Excel: Combo box runtime error 13, runtime error 13 type mismatch, text application

runtime error 13 type mismatch, text application, tbx: If it clears the textbox, along with the error, then just suppress the error. Put this in the beginning of each Sub: On Error Resume Next If you need more, send me the file -- send to bobumlas@hotmail.com, use subject of AllExpertsQ and repeat the...

Excel: Combo boxes, combo boxes, gotfocus event

combo boxes, gotfocus event, cbox: I can t imagine why you d want to have 6 different combo boxes all linked to the same cell. But since you asked... I d put 6 different ActiveX combo boxes on the sheet, name them combobox1 through combobox6, set the linkedCell on each to A4, and use vba code...

Excel: Common factors in excel spreadsheet, excel spreadsheet, excel sheet

excel spreadsheet, excel sheet, common factors: I don t know what you mean by common parts found in products. So I ll guess that you mean to list, for each row, which Prod columns have an x . On a blank Excel sheet, in range B1:E1, enter the numbers 1, 2, 3, 4. In A2, enter Part A In cells B2,...

Excel: Conditional formatting, algebraic rules, two choices

algebraic rules, two choices, parenthesis: * OK, normally just reserve follow ups for true follow up of the same question pls.. I believe you could do this: go to one single cell and apply a new conditional formatting rule. Use the formula to determine for conditional formatting. say the...

Excel: Conditional formatting, cell c2, conditional formatting

cell c2, conditional formatting, cell a2: Hi Randy, This is simple to do. 1) Choose the left and top most cell of your data... may be Cell A2. 2) Using the mouse and scroll bar get the bottom and right most cell visible on screen... may be cell E135 3) Keeping the Shift key pressed, click...

Excel: Conditional formatting, conditional formatting, black cross

conditional formatting, black cross, cells: Normally, if you have a conditional format set in a cell, say A1, selecting and dragging that cell down using the small black cross, or simply copy-pasting where required should transfer the conditional formats to all the cells. Alternatively, you can format...

Excel: the consolidation code, code question, date entry

code question, date entry, sh1: nissar, the format was used to demonstrate how the other functions worked - it just formatted the result in a different way to demonstrate that an the string 1-1-10 was actually converted to a date. I am willing to look at your files. You can send...

Excel: convert text to valuxe, cell b2, cell a1

cell b2, cell a1, addin: Gary, there is no built in support for what you want to do. I know of a free addin that will provide the functionality you want - it offers an evaluate type function as I recall. The trouble is that it still is available in one form or another on the...

Excel: converting cells, dropdown arrow, home tab

dropdown arrow, home tab, double quotes: Bas, in the menu you can use the replace function (similar to the Find function). First you would select multiple cells where you want to do this. In Excel 2003 and earlier, you would go to the edit menu, then locate the Replace choice and click it. In...

Excel: Coping data from one workbook to another using VBA, spath, vba macro

spath, vba macro, cstr: Maybe this part gets you started: Sub GetOpenFileNameExample2() Dim vFilename As Variant Dim sPath As String Dim lFilecount As Long Dim lCount As Long sPath = c:windows emp ChDrive sPath ChDir sPath vFilename...

Excel: Copy one column to another column based on criteria, double quotations, header row

double quotations, header row, excel sheet: Hi Randy, The easiest way to do this would be to use and IF-THEN statement. (IF this , THEN do this , otherwise do this ) Assuming you have a header row (row 1), starting in row 2 cell N2 formula would be =If(M2 0,-M2, ) If the value of M2...

Excel: Copy data to new workbook, copy test, subscript out of range

copy test, subscript out of range, range error: Carry, Since C6 never changes, there is no need to use an array to hold the destination. You could do it this way. Sub Copy_test_1() Dim v1 Dim v2 as String Dim i As Long Dim r As Range Worksheets( Sheet1 ).Select Set r = Range( D10 ) r.Name...

Excel: Copy & Paste Macro, cell h4, data element

cell h4, data element, sumif: omar, this worked for me as I understand your requirement (the first method described). I have made all the locations variable so you can set them to be what you want them to be. They are set to your description locations right now. Sub copyData()...

Excel: Copy values from a named range to new sheet, vba solutions, dim statement

vba solutions, dim statement, range names: Hi again Carry, I am still a bit uncertain about exactly what result you are looking for. It appears that you want to copy offsets from range rge to rge1 and to locations that are offset from rge1. But rge is but a single cell, while rge1 is 50 cells....

Excel: Copy values from a named range to new sheet, r1 value, example worksheets

r1 value, example worksheets, a50: Hi Carry, I did not have access to Excel, nor do I have now, and answered your question from memory. Please check the syntax again, as in the worksheet spelling problem. In addition try with a proper range... example: Worksheets( sheet1 ).Range( A1 ).Offset(0,...

Excel: Copying excel graph to Powerpoint, print screen button, excel drop down list

print screen button, excel drop down list, prt scr: Hi Emmy, Oooh, this is a cross platform question and I am not really an expert in Powerpoint, I simply hate it. Anyway, I guess this is what is happening, but I could be wrong... when you do a paste, Microsofts extra-intelligent software detects that the...

Excel: create macro to populate cells, a2, many thanks

a2, many thanks, cells: Hi Garry, I have really not understood the requirement. 1. Someone enters value in Active!A2. 2. Macro searched for ALL records matching Active!A2 in Data and copies them to Data Searched at the end of records. 3. Returns to Active A2 with A2 being...

Excel: Creating multiple worksheet from one file, cell b2, cell d2

cell b2, cell d2, cell b3: Vish I am not a macro expert, so I cannot help you with a macro to do what you want. Having said that, I am not sure that a macro is what you need. Usually you can work with the data in the file (warehouse file) to arrancge it in such a way that it will...

Excel: Data bars, horizontal bars, negative numbers

horizontal bars, negative numbers, quirk: I have to confess, I no longer have Excel 2007 on any of my systems, so I can t test this to see what it looks like, but I did try it in Excel 2010 and it looks perfectly fine to me: http://amicron1.com/images/data-bars.jpg So perhaps it s just a quirk...

Excel: Data entry in Sheet2 from sheet1, empty cells, sheet1

empty cells, sheet1, information fields: Try this: =IF(ISBLANK(Sheet1!A10), ,Sheet1!A10) Here s some additional information on the IF function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts I cover the ISBLANK function here: http://599cd.com/site/courselist/excel2010/expert/x3/?key=AllExperts...

Excel: get data by sql in excel by vba, sql queries, column format

sql queries, column format, loop through: You haven t said how the VBA handles it, but if it can get a recordset and loop through each record, it is possible to output the data horizontally instead of vertically. An alternative would be to use formulas to transpose the data - see http://aidanheritage.byethost3.com/excel/TransposeDataWithFormula.xls...

Excel: database information, abc store, budget code

abc store, budget code, material purchases: Jim You have two options that would best suit one being the Pivot Tables and another being groups. Depending on how the table is setup would depend what one to use. The easiest way would be if I could see the table. Would it be possible to send me a...

Excel: Date field, cell a1, initial answer

cell a1, initial answer, google: It s beyond the scope of this free service for me to teach you how to use vba ( macros ) if you start with no idea. So you should do this manually, as described in my initial answer. First, enter =today() in the cell. Then, copy that cell, and PasteSpecial...

Excel: Date formatting, excel user, amazon

excel user, amazon, target: The code as initially given will put the time of the last change made to the sheet in cell A1. If that s not happening, then one of 2 things are possible: 1 - you haven t enabled macros; 2 - You re not putting the code in the right place. It MUST go in the...

Excel: date and time in excel 14.1.3, dear tom, 12345678

dear tom, 12345678, time value: Mark, if you have the date in a1 and the time in b1, then in c1 put in a formula =a1+b1 and then do a custom number format mm/dd/yyyy hh:mm or mm/dd/yy hh:mm that is if you have dates in one column and time in another. However, just because...

Excel: day fromand to total, excel user, hi bob

excel user, hi bob, pivot table: You lost me. Please send me a file + a complete description again (I don t keep all the files i get). Send to bobumlas@hotmail.com, use subject of AllExpertsQ . ----------- Check out my new book here: http://www.mrexcel.com/outsidethebox.html check...

Excel: Delete rows between two different strings, false selection, management errors

false selection, management errors, false application: Anthony, Sub CleanUp() Dim r1 As Range, r2 As Range Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveSheet.AutoFilterMode = False Set r1 = Columns(1).Find(What:= Cash Management Errors , _ ...

Excel: Delete string between two characters using VBA, left bracket, unwanted data

left bracket, unwanted data, loop through: Anthony, Sub CleanUp() Dim r As Range, r1 as Range Dim s As String Set r1 = Range( A2:A100 ) for each r in r1 if len(trim(r.text)) 0 then Remove any Text between the first Hyphen and first Left Bracket found in cell r ...

Excel: deleting words from excel, blue ford, answer link

blue ford, answer link, direct email: you ve already asked me this question, but possibly the answer link didn t get through to you - the answer I gave is at http://en.allexperts.com/q/Excel-1059/2011/10/deleting-words-excel.htm Should it help, my direct email is aidan.heritage@virgin.ne...

Excel: Enable user to select starting cell, bubble chart, chart c

bubble chart, chart c, range c: Kristoffer What do you want to trigger the running of the macro? How do you know whether the three inputs are in a row or a column? Does it always start with the active cell? If you are always starting with the active cell, then you won t need an input...

Excel: how to enter/add a $ sign to colums, dollar signs, import module

dollar signs, import module, database import: select a range of cells filled with numbers without dollar signs, and run this procedure Sub addDollarSign() Dim cl As Range For Each cl In Selection.Cells If Not IsError(cl) Then If cl.Formula Then ...

Excel: Error 1004 in Excel Macro, runtime error 1004, autofilter

runtime error 1004, autofilter, row c: This is how I would do that: ================ Option Explicit Sub DueTomorrow() Dim LR As Long Sheets( Hoja1 ).Cells.Clear Sheets( Hoja1 ).Range( C1 ) = Date + 1 With Sheets( CA ) .AutoFilterMode = False .Rows(6).AutoFilter ...

Excel: exce/formula question, cell c1, cell a1

cell c1, cell a1, richard florida: Mark Here is what your formula looks like. =IF(AND(A1 =17,OR(B1= DASTA ,B1= EDCON )), CONVERTED ,+IF(AND(A1 17,OR(B1= DASTA ,B1= EDCON )), TRAILER , )) One slight difference is if none of the condition combinations is met the formula will place a...

Excel: Excel 2003, blank cell, l6

blank cell, l6, p6: Your formula doesn t make sense. In your original question, you said that L6 is true or false. But in your follow-up, you include a formula that begins =IF(K6=TRUE... why K6?? If you want to ask a question again, be very very clear and precise. EXACTLY...

Excel: Excel 2003, Excel Sixthsense Excel (Format)

Excel Sixthsense Excel (Format): Hi Renee, Refer the below link for details. a href= http://plan-b-for-openoffice.org/ooo-help/r2.1/en-US/WIN/calc/guide/cellstyle_minusvalue http://plan-b-for-openoffice.org/ooo-help/r2.1/en-US/WIN/calc/guide/cellstyle_minusvalue /a Hope That Helps!...

Excel: excel 2003 date formula ?, analysis toolpak, xll

analysis toolpak, xll, optional modules: When your Excel 2003 was installed, these optional modules may not have been stored on your system. This is on of the myriad of things you can turn off/on during the original installation. As a much less elegant alternative: =DATE(YEAR(TODAY()),...

Excel: Excel 2007- Formate changed into date, format cells, pcreview

format cells, pcreview, home tab: 1.Open the workbook that contains the custom number format that you want to delete. 2.On the Home tab, click the Dialog Box Launcher (little arrow) next to Number. 3.In the Category box, click Custom. 4.In the Type list, select the custom number format...

Excel: EXCEL 2007 PIVOT TABLE - VBA QUESTION, excel 2007 pivot table, pivottables

excel 2007 pivot table, pivottables, pivottable: there really isn t just one answer for this, I m sorry. you re trying to run code based on a macro recording which just doens t work. the recording produces junk code, not variables that can be worked with later on, .. for the most part. you would need...

Excel: excel 2010 formula, cell b1, spread sheet

cell b1, spread sheet, a3: It took some time, but I was able to figure out a formula for you. It is not simple because your formula depends on a selective range. In other words, instead of looking from A through Z, you need to look from A to P and R to Z, based on some logic. Hence...

Excel: Excel, max match, column input

max match, column input, fourth column: Rajesh: I m going on the assumption the following values are in row 1 of columns A, B, C, and D. If that is not true then the formula will need to be adjusted. Here is the formula you should use: =INDEX({ Low , Low , Low , Medium , Medium , Medium...

Excel: Excel - Conditional Formating, conditional formatting, attendance list

conditional formatting, attendance list, countif: sounds a little tricky - my only thought would be to use a vlookup via a translation table , this table would have all the variations of the spellings, with the standardised one in the second column, and a countif in the third which counts the number of times...

Excel: excel formula, excel formula, two solutions

excel formula, two solutions, a1: Hi Jay, The two solutions I can think of most readily are: A. type a 1 in A1 and a 2 in A2, highlight both A1 & A2 and then grab the fill handle (little black square in the lower right corner of A2) and drag down as far as you desire. B. type a 1...

Excel: Excel Formula, salary schedule, excel user

salary schedule, excel user, excel formula: Maybe I m not understanding -- have each formula reference the cell above * 1.03, like =M1*1.03 then when you fill down, the next will be 3% bigger, etc, but in cell M3 you d see =M2*1.03. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE:...

Excel: Excel formula, g16, disb

g16, disb, excel formula: Hey Ray I am sure your question is clear as can be to you but I am struggling a bit to understand exactly what you need to do. So my answer may not be exactly to the point of your question and if its not I apologize for that. here is a suggested formula....

Excel: Excel Formula, sections rows, excel formula

sections rows, excel formula, pivot table: Hi Ben, Hmm... I am not sure how Point 2 has exceeded 0.005 difference, but if you can code your logic into an IF statement, there are more than one ways to approach your problem. Most likely, your formula would be something like... =(B1 - C1 0.005)......

Excel: excel formula, inventory sheet, cell references

inventory sheet, cell references, excel formula: Unfortunately you want the data in the inventory sheet to be in a different order than the date sheets, the ROWS in the date sheets go ACROSS by column in the inventory sheet, so you can t copy across the formula and have the relative referencing work for...

Excel: Excel Formula to convert inches to cubic meters, excel formula, conversion ratio

excel formula, conversion ratio, simetric: Here s a link to a very complete conversion ratio table: http://www.simetric.co.uk/si_c.htm From there we find that converting from cubic inches to cubic meters is 0.000016387 So if the cubic inches were in A1, then the formula would be:...

Excel: excel formula with date, target date, excel expert

target date, excel expert, excel formula: Well, here s what I d do: Put the number of pills she uses per day for a particular medication in column A. Put the number of pills currently in stock for that medication in column B. In column C, you can calculate how many days until she s out...

Excel: Excel formulas, cell c1, cell a1

cell c1, cell a1, excel formulas: Actually Excel has many Lookup formulas designed for this specific thing. I tend to use the LOOKUP() formula for the scenario you ve outlined where the lower number represents the beginning of a range by which a specific value is applied. Let s say...

Excel: Excel List Comparison, sheet names, compatibility mode

sheet names, compatibility mode, bk2: RR, ===== Only revision was I changed the last comment since it was originally copied from the processing of the rOld sheet and then updated to reflect processing of the rnew sheet - but I forgot to update the comment to reflect that. =============== ...

Excel: Excel Lookup Question, excel lookup, question question

excel lookup, question question, braces: Try this formula in any cell and let me know if it works for you... =MID(CELL( filename , MAX(Table1[@Result],Table112[@Result],Table113[@Result],Table114[@Result],Table115[@Result]) ), FIND( ] ,A2)+1,256) In the above... MAX(Table1[@Result],Table112[@Result],Table113[@Result],Table114[@Result],Table115[@Result])...

Excel: Excel lookup and if (v2010), irs withholding tables, excel lookup

irs withholding tables, excel lookup, amazon: If your data is in A1:C10, for example (name in A, ded in B, Sal in C), and your tax table is in I1:M7, then in D2, enter this formula =VLOOKUP(C2,$I$1:$M$7,B2+1) then fill down. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30,...

Excel: excel macro, excel macro, varghese

excel macro, varghese, a100: Hi Varghese How are you? Please do the following 1. Run the vba Editor 2. Insert module 3. Copy the code: Sub Ticking() Dim R As Range Set R = ActiveSheet.Range( A1:A100 ) For Each C In R If C.Value = A1 Then C.Offset(0, 1) = * End If...

Excel: excel macro, excel macro, frn

excel macro, frn, dmm: Sub findDupes() Dim cl As Range Set cl = ThisWorkbook.Sheets(1).Range( a1 ) If (cl = ) Or (cl.Offset(1) = ) Then Exit Sub Set cl = cl.End(xlDown) Do If cl.Offset(-1) = cl Then cl.Offset(0, 1).Range( a1:c1...

Excel: your excel program doesnt work, subscript out of range, consolidated data

subscript out of range, consolidated data, sh1: the line you cite set sh = Workbooks( Master.xls ).Worksheets( Consolidate ) would produce that error if you didn t have a workbook named Master.xls that is already open in excel that has a sheet named Condolidate . It is possible you have...

Excel: Excel Programming, pilot names, cell b2

pilot names, cell b2, pilot name: Hi Iain, I feel your data is not structured right and hence is causing your problems. Try the following restructure of your excel and I guarantee your current problems will be solved and you may find additional uses for your data. I will use the following...

Excel: Excel transfering data, added convenience, transfering data

added convenience, transfering data, error value: Manuel I am guessing that you are using a copy to transfer the data. What this is doing is copying the formula in the cell in the first sheet but now the new formula references cells in the second sheet and the result of the formula is producing an error...

Excel: Excel - VBA, vba code, excel vba

vba code, excel vba, parent cells: Prakish Below is a more efficient way to get the cells hidden. You loop through the range and hide the row if the value is zero. I am sure you can add the printing and unhiding of the rows back in since they were working for you before. Sub Hide_Print_Unhide()...

Excel: Make excel worksheets work together, vlookup formula, cell a2

vlookup formula, cell a2, number column: Dylan Yes you can do this with a VLOOKUP() formula The formula might look like this =VLOOKUP(A2, item-price list !A1:B100,2,FALSE) The formula says Go to the item-price list sheet and look in the range A1 to B100 find the value that is in cell...

Excel: Extract Text/word using macro, paradiselost, text word

paradiselost, text word, tana: Hi, try this code... Function StripNumber(stdText As String) Dim str As String Dim i As Integer stdText = Trim(stdText) For i = 1 To Len(stdText) If Not IsNumeric(Mid(stdText, i, 1 )) Then str = str & Mid(stdText , i, 1) End If Next...

Excel: Extracting "top 10" values where duplicate entries exist, array formula, excel spreadsheet

array formula, excel spreadsheet, formulae: Hi Gordon, please share the formula you are using to display the header for me to help you better. Perhaps we can use some combination of INDEX, IF and COUNTIF formulae. Basically, we need to check whether a number in an Array is same as the one above or below...

Excel: Extracting "top 10" values where duplicate entries exist, cell b10, b24

Excel: Extracting "top 10" values where duplicate entries exist, cell b10, b24, b15

Excel: Random Number generator in excel 2000

Excel: Random Number generator in excel 2000

Excel: single digit cell entry with automatic cursor move

Excel: single digit cell entry with automatic cursor move

Excel: Find - Loop Once Only When Clicked, input sheet, blank cell

input sheet, blank cell, tom hope: Paul, you say a button named CurrRecNew holds the value of the row number - I am not sure what that means. A button can t hold a value. The way your code call CurrRecNew, it would refer to a named range on sheet3. So I assume the named range CurrRecNew...

Excel: Fit to Page Macro, reply data, hidden columns

reply data, hidden columns, question sir: Hello. I am not sure why it is doing that. Might be some settings that got left out. I will need your file to diagnose. Alternatively, try to record a macro where you perform some of the intended tasks such as setting zero column width and autofitting, and...

Excel: Followup to Copy and Paste Macro, sh1, john doe

sh1, john doe, jim smith: Tae, Looks like there was a typo in the code where I used Sh1 where I should have used Sh. I have revised the code and tested it with data similar to what you describe and it worked for me: Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim r As...

Excel: food cost, food price, food cost

food price, food cost, job: The only way excel could help you here is if you had all the prices from each store for each food item you wanted to buy - it could then show you which store was the cheapest per item. Other than that, I m not sure how a formula could do this job for you...

Excel: formatting a range of cells to show all text in upper case, data validation, capslock

data validation, capslock, upper case: You cannot change the display of a text cell with cell formatting. You would need to force the user to turn on the CAPSLOCK, which can be done with data validation in that column. Let s assume the cells to validate are D1:D100. 1) Select D1:D100 ...

Excel: Formula Problem, orange 4, green orange

orange 4, green orange, b4 4: Lorna, IN B1 put in a formula =IF(A1= , ,IF(A1= Red ,1,IF(A1= Blue ,2,IF(A1= Green ,3,IF(A1= Orange ,4, Nothing ))))) if you have a lot of values, then in another sheet (or in another location) let us say in a sheet named Sheet10 you have your...

Excel: Formula question, vlookup formula, man hour

vlookup formula, man hour, question question: Jim You are welcome The ability to rate an expert is the choice of the expert, we can turn the ratings off if we want, which is what I have done, I am prolly among the few who has done so. I have been doing this about 10-15 years or so, so ratings aren...

Excel: Formula for Wages Needed, duty numbers, vlookup formula

duty numbers, vlookup formula, format cells: Darren Is the data in three columns? Or is Duty 103 in a column? The word duty is basically just clutter, so I will ignore it for now. If you have your list of duty numbers in column A, the respective times in column B, the the following formula...

Excel: Freeze a result from constantly changing data and execute secondary code when a figure appears in a specified cell, circular references, ogilvy

circular references, ogilvy, g1: Burt, While this might be possible with intentional circular references, I would generally do this with a macro. I don t know if a macro solution is acceptable. If it is, post a followup and I will show you how to do it. I assume that when you say...

Excel: Frequency of Pairing, abby cat, sheet layout

abby cat, sheet layout, cat owners: Cindy, Not sure what you find problematic about my answer. I even offered to do it for you - but so be it. Good luck solving your problem. I am not sure why you consider that difficult. It would take about two minutes. Use the advanced filter...

Excel: Froumla, array formula, curly brackets

array formula, curly brackets, column c: Hi Tom the following will do what you need,however,you must enter the formula as an Array formula ie Type the formula in the cell and press the CTRL, SHIFT and ENTER keys at the same time instead of ENTER. You must do this the first time you enter the...

Excel: Full tab export, vba macro, mwb

vba macro, mwb, lengthy explanation: Hi again Bill, To get the pasted table to size automatically to the page size you can add the following code to the end of the Word macro I previously provided: Dim n As Integer n = ThisDocument.Shapes.Count With ThisDocument.PageSetup...

Excel: Generate numbers, 1001 bay, many different ways

1001 bay, many different ways, number sequence: If you need hyphens in between the numbers, then where would the hyphens be?? If you have 1001 in col A, and 04 in col B, where would the hyphen be...col A or B? Do you want to enter the input on the worksheet? do you want the output to be in a specific...

Excel: Hyperlink, meenakshi, office microsoft

meenakshi, office microsoft, hyperlinks: Meenakshi: There is a hyperlink function within Excel. The following websites explain how to use the function: http://www.techonthenet.com/excel/formulas/hyperlink.php http://office.microsoft.com/en-us/excel-help/hyperlink-HP005209116.aspx http://www.mrexcel.com/archive/Hyperlinks/23826.htm...

Excel: Hyperlink Formula, Excel Sixthsense Excel (Hyperlink)

Excel Sixthsense Excel (Hyperlink): Hi Meenakshi, I have described it in a better way, please go through the below suggestion/solution. u b Referring Sheet Name – Clarification 1 /b /u Open a new excel and by default you will be having 3 worksheets (It may vary if you changed your...

Excel: imported data and autosum, cell b1, paste button

cell b1, paste button, yellow diamond: Jacques: Having a comma as the thousands separator shouldn t prevent Excel from calculating a total. Instead, what may be happening (as a side effect of there being a comma in the cell) is those values are being treated as text. Here are some solutions:...

Excel: index formulas, index formulas, amazon

index formulas, amazon, excel user: Sure - sent to me at bobumlas@hotmail.com, use subject of AllExpertsQ and repeat the issue. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30, Atlantic City Details here: See http://www.bobsexceluserconference.com -----------...

Excel: Insert Columns with code, ogilvy, tact

ogilvy, tact, formatting: Bill, You subject says insert columns and the body of the email says number of rows. Columns(1).Resize(,n).EntireColumn.Insert Rows(1).Resize(n,).entirerow.Insert will insert columns or rows. However, you speak about functions and formatting...

Excel: insert space, format cells, cell a1

format cells, cell a1, space question: John, then my approach wouldn t work - but you didn t ask how to handle letters. You said: I have hundreds cells with 13 numbers each one also 1005MGE3354 doesn t have 13 characters and it doesn t fit the pattern you originally described. So...

Excel: insert space, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi John, Assume that your Data is in A2 cell. A2 cell 1305001247785 Copy and paste the below formula in B2 cell =IF(A2= , ,LEFT(A2,4)& &MID(A2,5,2)& &MID(A2,7,3)& &MID(A2,10,255)) Drag the B2 cell formula to the remaining cells of B-Column...

Excel: insert space, format cells, greece

format cells, greece: There are two methods depending on what you need it for! To physically alter the cells, use =LEFT(YourCell,4)& &MID(YourCell,5,2)& &MID(YourCell,7,3)& &RIGHT(YourCell,4) This would need to be in a different column, though you could then select...

Excel: insert space, relevant cells, 0000

relevant cells, 0000, greece: John Is it okay if the number just looks like ### ## ### #### or does the space really have to be there? The easiest and best in my opinion would probably be to make a custom number format 000 00 000 0000 * and use that as the format for all the relevant...

Excel: insert space, hi john, copy paste

hi john, copy paste, a1: Hi John, There is certainly a formula (Assuming original data in A1) -- =Left(A1,4) & & MID(A1,5,2) & & MID(A1,7,3) & & RIGHT(A1,4) Make sure that once you have all data changed to new format, you carry out -- COPY - PASTE SPECIAL - VALUES...

Excel: If /then issue, parameter example, copy contents

parameter example, copy contents, sheet1: Hi, Firstly using Excel as a DB, especially one where you need to move data from one sheet into another, is highly unadvisable. It may at best be better to have a single worksheet with rows and columns and use Pivot Tables to analyze your data. Anyway,...

Excel: Links always update when file opened, click options, current user

click options, current user, spreadsheets: ========================= Update all links or no links in a workbook by prompt 1. Close all workbooks. 2. Open the workbook that contains the links. 3. When asked whether to update links, click Don t Update. 4. On the Edit menu, click Links....

Excel: Loading Value, student address, text boxes

student address, text boxes, richard florida: Eran You can use a VLOOKUP foremula to bring over the data from the data sheet to the userform. The formulas in the userform sheet might look like this. =VLOOKUP(A2, data sheet !A2:C100,2,FALSE) =VLOOKUP(A2, data sheet !A2:C100,3,FALSE) The formula...

Excel: Loading Value, text cells, excel user

text cells, excel user, city details: Private Sub txtName_Change() Dim n As Integer On Error Resume Next n = 0 n = Application.Match(Me.txtName, Columns(1), 0) If n 0 Then Me.txtAge.Text = Cells(n, 2).Value Me.txtAddress.Text = Cells(n, 3).Value ...

Excel: Loading Value, value question, command button

value question, command button, student address: Private Sub cmdEnter_Click() Dim wks As Worksheet Dim colNames As Range, markCell As Range Dim vRow As Variant Set wks = ThisWorkbook.Sheets( Sheet1 ) Set colNames = wks.Range( a1 ).EntireColumn vRow = Application.Match(Me.txtName,...

Excel: Loading Value, vlookup, student address

vlookup, student address, text boxes: one way to do it, change sheet name or other constants to suit: me.txtAge = application.worksheetfunction.vlookup(me.txtName,sheets( Sheet1 ).Range( A:A ),2,False) me.txtAddress = application.worksheetfunction.vlookup(me.txtName,sheets( Sheet1 ).Range(...

Excel: Locking specific cells, private site, uwgb

private site, uwgb, microsoft excel: Hebron: Yes, Excel offers a feature to lock and protect cells. I have provided links to several websites explaining how to accomplish this task. One of them should be able to explain to your liking: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm...

Excel: Locking specific cells, format cells, dialog box type

format cells, dialog box type, private site: You don t say what version of Excel you re using, so I ll guess it s 2007 or later. On a new workbook, Sheet1, select range A1:B2. Then from the home ribbon, click the arrow to the right of Alignment to open the Format Cells dialog box. On the box s Protection...

Excel: Locking specific cells, format cells, private site

format cells, private site, aidan: All cells are locked by default, but the locking only takes effect when the sheet is protected - so select the cells you want the user to edit, right click and goto format cells, use the protection tab and uncheck the locked box. You then need to protect...

Excel: Locking specific cells, excel user, private site

excel user, private site, amazon: Cells are all locked by default on a new worksheet. You unlock the ones you want users to be able to change, then password protect the worksheet. I don t know if there s any difference because of a Sharepoint site. ---------- Reserve the dates for BOB...

Excel: logical test on large data sets, logical test, 44444

logical test, 44444, h5: If your 5 codes are stored in H1:H5, for example, and the 18000 zip codes are in A1:A18000, enter this in B1 and fill down: =IFERROR(INDEX($H$1:$H$5,MATCH(A1,$H$1:$H$5,0)), ) or you can put the codes directly into the formula: =IFERROR(INDEX({11111;22222;33333;44444;55555},MATCH(A1,{11111;22222;33333;44444;55555},0)),...

Excel: Look up multiple values, cells

cells: * I m sorry, I don t fully understand the question. So all the cells in, say, column A are identical. and when you enter one of them, you want to return all the values from another column? where? I m unclear on the question, can you be much more specific....

Excel: Lookup, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi John, Do a google search you will get lot more ideas about it. Don t ignore Index and Match match functions also. Have a look in the below like for some of my solution files. a href= https://docs.google.com/leaf?id=0B2TMxNyEPQ2rOGU0MjRkZTktZjhlMy00MTFkLTk0YWQtYzQ2MDMzZTRhYzhi&sort=moddate&desc=true&layout=list&num=50...

Excel: lookup help?, excel expert, thanks in advance

excel expert, thanks in advance, aidan: Seems perfectly possible - and it would use Vlookup - see http://www.xlfdic.com/ for a file with explanations of this and other functions - I would PROBABLY use an IF statement to only do the lookup on rows where you have data, but other than that it will...

Excel: Lookups to other Worksheets, master spreadsheet, cell b1

master spreadsheet, cell b1, vlookup: Nick, It is possible only if the workbook 1000.xls or 1001.xls is open when the formula is recalculated or links are updated. =if(A1 ,vlookup( House ,Indirect( [ &A1& ]Sheet1!A:C ),3,False), ) Indirect will not work with closed workbooks - so...

Excel: macro, cell a2, cell a1

cell a2, cell a1, direct email: I m not sure I have the full information here - based on the data given, it sounds like a random number is generated and stored in two cells, and the value from A1 is stored in B1 - can you clarify? If it helps, my direct email is aidan.heritage@virgin.ne...

Excel: Macro Addins, empty workbook, personal workbook

empty workbook, personal workbook, macro name: Macros from an Addin don t show up in the list, but they re available. If you know the macro name, you can just type it in. Usually, Addin macros are run from a key-sequence (like Ctrl/Shift/L or something) or they contain Functions which you can use just...

Excel: macro to change cell color based on content, content question, macro button

content question, macro button, color rgb: To use the macro, press Alt/F8, then double-click Colorize . You can put a code in column U (assuming it s available), then sort on that column. The code would come from this updated macro: Sub Colorize() For Each cl In Range( L4:L250 ) ...

Excel: macro to conditionally copy and paste in next available cell, monthly budget, b15

monthly budget, b15, c15: Try... Set R = Range( H4:H12 ).SpecialCells(xlCellTypeBlanks)(1, 1) This part of code... Range( H4:H12 ).SpecialCells(xlCellTypeBlanks)(1, 1) ...should return the first blank cell in a particular range. If using Set does not work, see what you...

Excel: Macro to printing page, excel user, amazon

excel user, amazon, sr 1: Probably you should hide the columns with no data. But if you have 300 columns, and insist on fitting to one page, you re going to get a very teeny print. I m not sure what you want to do. Widen all columns would first assume they re all showing, but then...

Excel: macros, conditional formatting, cash receipts

conditional formatting, cash receipts, format font: Using AM1:AM3 as a place to hold these values: Sub FillExplanation() Range( AM1 ).value= Cash Receipts but no Expenditures, then Cash on Hand is highlighted in BROWN. Range( AM2 ).Value= Cash on Hand is greater than 10% of Expenditures, then Cash...

Excel: macros, excel addin, ok bob

excel addin, ok bob, yuo: You can do addins anywhere. In your save as, choose Excel addin (*.xlam). Doing so will change the directory initially, but point it back to anywhere you want. The file can be emailed or put directly onto the S drive. Once it s an addin, yuo won t be able...

Excel: Macros, excel user, macro name

excel user, macro name, shared files: If the macro resides in your personal macro workbook on the S drive, they do need to open it. They can run it with the shortcut key combination or View/Macro... But again, I have very little experience running macros from a shared drive, so if it s not...

Excel: Macros, Forms, and Hassle, cell b1, sendkeys

cell b1, sendkeys, buffer problem: Hi Mike How are you? I am sorry but I cannot test your program. I had a look at it though. Please see the following Sub ggg() Dim R As Range Set R = ActiveSheet.Range( B1 ) N = R Rem Activate Kofax For I = 1 To N Rem SendKeys ( {UP}{TAB}{TAB}{UP}{ENTER}...

Excel: Marco to move rows to another sheet conditionally, kt history, dear madam

kt history, dear madam, false application: This is how I would do that: ============= Option Explicit Sub MoveData() Dim wsSrc As Worksheet, wsDest As String Dim LR As Long, NR As Long wsDest = KT-History this should be the only variable you have to edit Application.ScreenUpdating...

Excel: Matching Columns row by row, conditional formatting, direct email

conditional formatting, direct email, aidan: I m not 100% sure I follow what you need to do, BUT if it s a case of highlighting data where E and D are not the same then conditional formatting would do it =D5 E5 would be the formula to use in the conditional formatting box If this isn t what...

Excel: merging excel files., blank calendar, aidan

blank calendar, aidan, calendars: It s certainly possible, though it would help to see a copy of the calendar - do we have both calendars in one workbook, or does it get more complicated - if it helps, my email is aidan.heritage@virgin.net which would enable you to send me a sample copy of...

Excel: missing data, excel spreadsheet file, question pool

excel spreadsheet file, question pool, sheet tab: Dave, You didn t ask me this question - I found it in the question Pool I assume that each year is in a separate sheet in the same workbook. If so, I suspect this is what happened. I suspect you click on the first sheet tab, then held down the shift...

Excel: Monster headphones, quick brown fox, lazy dog

quick brown fox, lazy dog, thanksgiving day: The quick brown fox jumped over the lazy dog s back. The quick brown fox jumped over the lazy dog s back. The quick brown fox jumped over the lazy dog s back The quick brown fox jumped over the lazy dog s back. The quick brown fox jumped over the lazy dog...

Excel: Multiple potential inputs, dollar change, vba macro

dollar change, vba macro, macro code: Bill, with just formulas, you won t be able to do what you want if you only want to use 4 columns. A cell can contain a contant value or a formula. If you type into a cell with a formula, then it will overwrite the formula. You could have formulas...

Excel: Multiple prompts, target cells, target address

target cells, target address, input cells: What error do you get? If you hit Debug, which line is highlighted? To get different defaults: Private Sub Worksheet_Change(ByVal Target As Range) Dim vAnswer1 As Variant Dim sDefault As String If Target.Cells.Count 1 Then Exit Sub ...

Excel: Multiple If statements, data validation, question thanks

data validation, question thanks, sheet1: is there a way to return nothing instead of the #N/A If you re using Excel 2007 or greater, then use IFERROR() to trap the N/A results: =IFERROR(VLOOKUP($A1, Sheet2!$A:$B, 2, 0), ) If you re using Excel 2003 or earlier, you ll unfortunately...

Excel: Nested Ifs, vlookup, banging my head

vlookup, banging my head, target: Mike, I think you want your formula this way: =IF(AND(VALUE(LEFT(J43,3)) =200,VALUE(LEFT(J43,3)) =299), Class 1 ,IF(OR(N43=0,N43= ),IF(VALUE(LEFT(J43,1))=3, Class 2 ,IF(VALUE(LEFT(J43,1))=4, Class 3 , Unknown )),VLOOKUP(N43,assign,2))) ...

Excel: Offset and match, de2, c15

de2, c15, ogilvy: Faris, go ahead and send the file to twogilvy@msn.com Instructions like etc and ... are meaningless to me. Tell me specifically what you want. If you wanted 12 contiguous cells, then you could refer to AO15:AZ15. right now you say AO15, AP1...

Excel: Pivot charts, richard florida, florida usa

richard florida, florida usa, parameters: Nissar I am sorry-- I am really not expert in Pivot charts an tables. I may be able to answer your question however you qill get a much quicker answer and a much better answer if You ask another allexperts person. sorry I couldn t be of more help ...

Excel: Pivot charts, pivot table, youtube video

pivot table, youtube video, daily basis: Hi Nissar, Is it possible for you to upload your file somewhere? In Excel 2007 onwards, you can easily create a Pivot Chart. For the earlier, you may need to create a normal Pivot first and then make a chart out of it. In 2007+, go to the Insert Menu,...

Excel: Pivot Table - Matching / Duplicate Numbers, array formula, countif

array formula, countif, pivot table: Same answer -- perhaps you didn t enter with holding ctrl+shift before pressing enter: =SUM(1/COUNTIF(B1:B25,B1:B25)) The above formula will give the # of unique values. Therefore, the # of duplicates is =25-that value. The formula doesn t work with blanks,...

Excel: Pivot Tables, pivot table, pivot tables

pivot table, pivot tables: * Depends on what you want the users to be able to do with the pivot table that you re distributing. If there isn t a need to refresh the table after the fact, then just copy the sheet with the pivot table on it to a new sheet, and distribute the table...

Excel: populate cells on seperate sheet from single active cell, cell a2, sheet tab

cell a2, sheet tab, cell address: This would be accomplished with a macro. On the ACTIVE sheet, right-click the sheet tab and select VIEW CODE to open the sheet module. Paste in this self-activating macro: =============== Option Explicit Private Sub Worksheet_Change(ByVal Target as...

Excel: Prompts in Excel, target cells, target address

target cells, target address, blank text: If I understand correctly: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim vAnswer As Variant This code will only respond to changes in column A, if you want it to respond to changes in another cell, change...

Excel: Prompts for Sources, target address, yield curve

target address, yield curve, sheet tab: Sorry, I misunderstood. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) A1 Then Exit Sub Range( A2 ).Value = Source: Treasury Daily Yield Curve, 20 year Bond as of 11/1/2011, retrieved 11/2/11 End Sub ...

Excel: Question on excel calculation with different prices for different quantities, poultry sales, cell c2

poultry sales, cell c2, leg quarters: Hi Gabor, You could put all this into one formula, but I think it is easier to explain by having a column that determines your price and then another column that multiplies the price x quantity, so you can more easily check for errors. To determine price...

Excel: Random Generator, paste values, formula index

paste values, formula index, f9 key: Lilliana in say C10 put in this formula =Index($A$1:$A$7,trunc(rand()*7+1),1) then drag fill it down (or copy and paste) to cell C50. Now this is a formula, so everytime there is a recalculation on the sheet (such as hitting the F9 key or editing...

Excel: Random Number generator in excel 2000, random number generator, visual basic for applications

random number generator, visual basic for applications, v team: If you don t know how to use vba (visual basic for applications, sometimes called macros ), this is impossible. If you do know how to use vba, we can accomplish the task (13 pair match-ups of numbers 1 through 27) using this procedure: Sub makeMatchups()...

Excel: Random order of a list, cell b1, array formula

cell b1, array formula, cell c1: Gordon You can create a random list the following way. I will assume that the list is in A1:A8. Enter the formula, =RAND(), in cell B1 and copy down. Next, enter the array formula, =INDEX($A$1:$A$8,COUNTA(A:A)-SUM((B1 $B$1:$B$8)+0)) into cell...

Excel: Range Select, range selection, abcd

range selection, abcd, thanks in advance: Craig, First, your code causes an error for me. Selection is a property of the Window or Application. As you show it in your last line, you make it a property of a worksheet - which, as I say raises an error for me. So lets ignore that for a few minutes....

Excel: ranking, ogilvy, dear sir

ogilvy, dear sir, a2: dei, assume you have 25 numbers starting in A2 and going down to A26. Further assume these numbers are sorted descending as you show (duplicate numbers will be adjacent). Assume the 50 that you show is in A2 and the two 48 s are in A3 and A4 and so...

Excel: ranking, rank values, rank number

rank values, rank number, ref number: Hello Dei, Here is a user-defined function (UDF) that calculates rank similiarly to the Excel RANK() worksheet function. If you use it instead of the RANK it will average the rank values for identical marks as you want. Here is the code: Function RankAvg(Number...

Excel: Reference Error, unused cells, data ref

unused cells, data ref, blank cell: The ref error is not caused by cells being empty but by -for example- a row being deleted manually, or by refreshing an external data table which has the option set to delete unused rows. If your data comes from e.g. a database query, right-click inside...

Excel: REPORTING TEMPLATES, question sir, report template

question sir, report template, pivot tables: Hello, once again there is nothing like a single report template in excel and you have to create everything from scratch. Either get yourself some software linked to a database or in excel, you will need to create a new worksheet with the following columns......

Excel: Retaining Data when page is reset, blank row, weird problem

blank row, weird problem, target: Makes total sense, and it is perfectly possible BUT will require VBA to achieve it - basically, you need a reset macro - which would copy the data you want to the next blank row on the total inventory page as the values rather than formulas, and then clear...

Excel: Returning only part of a cell's contents, error value, ogilvy

error value, ogilvy, psi: Stephanie, =IF(COUNTIF(A2, *PSI* ) 0, if PSI is in the cell then IF(OR(COUNTIF(A2, *inline* ),COUNTIF(A2, *spring* )), If inline or spring in the cell then , psi plus inline or spring - return an empty string ...

Excel: returning part of a cell's contents, ogilvy, a2

ogilvy, a2, len: Stephanie, =IF(COUNTIF(A2, *regulating* ) 0, REG ,MID(SUBSTITUTE(SUBSTITUTE(A2, U , U ), U , # ,LEN(SUBSTITUTE(A2, U , U ))-LEN(SUBSTITUTE(SUBSTITUTE(A2, U , U ), U , ))),FIND( # ,SUBSTITUTE(SUBSTITUTE(A2, U , U ), U , # ,LEN(SUBSTITUTE(A2, U , U...

Excel: How To Run Form in VBA, button number, excel user

button number, excel user, city details: Sub Form1() Userform1.Show End Sub or, if you ve given it a name: Sub Form1() MyForm.Show End Sub ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30, Atlantic City Details here: See http://www.bobsexceluserconference.com...

Excel: Search & fill, psuedo code, countif

psuedo code, countif, sheet1: Eran, the code I gave you should do what you describe. The only new information I see is that you have specified sheet2. If you want the code modified to include Sheet2 as a qualifier then I don t know where the textbox is located (could be on a...

Excel: side by side floating bars, gantt chart, excel user

gantt chart, excel user, hi bob: It s Gantt chart, not Gnat chart. Send sample data to me at bobumlas@hotmail.com, use subject of AllexpertsQ Repeat what you re trying to do. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30, Atlantic City See http://www.bobsexceluserconference.com...

Excel: single digit cell entry with automatic cursor move, golf league software, target range

golf league software, target range, digit cell: You cannot trigger a vba procedure ( macro ) while in edit mode. You cannot have the procedure automatically run until the data is entered into the cell... that is, you must type something and then hit tab or enter or a cursor key. That s why my macro only...

Excel: Sort by date, sort function, excel sheet

sort function, excel sheet, office microsoft: I assume that the dates are not dates but text. You will need to convert them to dates. First, try formatting the column as dates. You can also try placing a 1 in an empty cell and copy it. Select the dates and choose Paste-- Paste special-- Multiply, which...

Excel: sort within a sort, sort operation, way over my head

sort operation, way over my head, ucsd: Hi Susie, This should work fairly easily for you. Rather than using the A-Z or Z-A icons, you ll want to use the sort operation. First, highlight your entire range of data. If you have Excel 2003, then choose Data/Sort and then it will allow you to...

Excel: How to split data into multiple files, string fname, stock data

string fname, stock data, stock symbol: Read through this carefully, let me know if any of the commands are unclear as to what they do. ================ Option Explicit Sub SheetToWorkbooks() Dim fPath As String, fName As String Dim cell As Range, wbArchive As Workbook fPath = C:2011StockArchives...

Excel: IF statement help, banging my head, silver level

banging my head, silver level, index data: Dana If you want to sum the corresponding values in column D if the value in column A (both on sheet Data_Agreements) matches the value in C2 of the sheet with the formula then you should use this formula [that is what I understand you to say - although...

Excel: If statement using true/false, vlookup, explantion

vlookup, explantion, correct result: Dana Wylie, Vlook doesn t require that the data be sorted =vlookup(A2,Monitoring!$A$2:$C$45,2,False) will return the value in column B. for the matching row and a #N/A if there is no match in the Monitoring sheet. If you want to dipslay something...

Excel: Time filtering in excel, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Ranjithkumar, Assume that your above provided example data is in Column-A. DONT USE THE BELOW FORMULA S IN THE b FIRST ROW /b If you want to use the formula anywhere in the SAME SHEET then copy and past the below formula in the cell you like...

Excel: Time in Which Fraction, starting time, perfect sense

starting time, perfect sense, a3: Parantapkumar: A7 =ROUNDUP((A4-A1)/((A2-A1)/A3),0) B5 =((A2-A1)*(A5-1)/A3)+A1 C5 =((A2-A1)*(A5)/A3)+A1-(1/24/60/60) B6 =((A2-A1)*(A6-1)/A3)+A1 C6 =((A2-A1)*(A6)/A3)+A1-(1/24/60/60) B7 =((A2-A1)*(A7-1)/A3)+A1 C7 =((A2-A1)*(A7)/A3)+A1-(1/24/60/60) ...

Excel: Transpose multiple rows into 2 columns, cell1, vertical data

cell1, vertical data, transpose function: Nathan, I believe this is what you want: Sub abc1() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r1A As Range, rw As Long Dim cell1 As Range, x As Long x = 5 number of coulmns - change to match the number of columns you have. Set...

Excel: Update Value with Functions, array formula, curly brackets

array formula, curly brackets, red arrow: Tae Assume the cell with the red arrow is cell C19 so you are looking for the last non-zero value in row 19. Also assume that you only need to address a reasonable number of columns in that row - for example, I will look at columns D19:AZ19 -- so in...

Excel: Vacation; Sick time via Excel, microsoft access database, spreadsheet environment

microsoft access database, spreadsheet environment, access tutorials: This would be very difficult to set up in Excel. Once person would still have to maintain and monitor it. I would recommend setting something up in a Microsoft Access database where users could fill out a request form and then you could just click approve/deny....

Excel: VBA Adjustment, v2a, d5

v2a, d5, b5: Vish activesheet.name=wsdata.cells(i+2,4).value Sorry, it should have been cells instead of range. That s why you got the error. I think the general rule for sheet names will be the sheet name comes from the row 2 higher than the counter i. That is...

Excel: VBA Adjustment, cell d2, v2a

cell d2, v2a, finishing touch: Vish, since we are looping through column B, then I can only guess that you are talking about using the value in column D of the same sheet/same row Sub eOne_Priorities() Dim wsData As Worksheet, sh As Worksheet Dim v1 As Variant, v2 As Variant,...

Excel: VBA Adjustment, g28, d33

g28, d33, g33: Nishant, I made a mistake dividing up your arrays. The correction below works. But don t depend on me to set up the specific locations in the arrays - that is something you have to do. I am just showing you how to technically solve the problems you identify....

Excel: VBA code excel 2007, Excel Sixthsense Excel (Macro)

Excel Sixthsense Excel (Macro): Hi Petros, Try the below macro in a module. The below macro will perform copy action from D1 to F1 content and paste in D to F cell based on the last Non blank row of Column A. Sub CopyPasteMacroSimplified() Application.ScreenUpdating = False Range(...

Excel: VBA Code to retrieve files from server location, csv file, vba procedure

csv file, vba procedure, csv files: It s generally beyond the scope of this free service for me to create an entire complex vba solution for you from scratch. If you do at least some of the work yourself, and get stuck on a specific issue, them I m more likely to help. But to at least give...

Excel: VBA copy col to next empty column, vba code, empty column

vba code, empty column, sh1: Alannah, Sub CopyColFtoSheet1() Dim sh1 As Worksheet, sh3 As Worksheet, col As Long Set sh1 = Worksheets( sheet1 ) Set sh3 = Worksheets( sheet3 ) col = sh1.Cells(1, sh1.Columns.Count).End(xlToLeft).Column + 1 sh3.Range( F:F,H:H ).Copy sh1.Columns(col).PasteSpecial...

Excel: VBA Help, cell b2, cell d2

cell b2, cell d2, cell b3: Vish, This code loops through 10 rows of data starting in row 3 of the data sheet. For each row, it adds a new worksheet at the end of the tab order, then copies 3 cells from the prescribed location in the row being processes in the Data sheet to the prescribed...

Excel: VBA to insert blank Row, blank row, k200

blank row, k200, isnumeric: Abdul Jaleel Sub AddRows() Dim i As Long, cnt As Variant For i = 200 To 3 Step -1 cnt = Cells(i, L ).Value If IsNumeric(cnt) And Len(Trim(cnt)) 0 Then Cells(i, L ).Offset(1, 0).Resize(cnt, 1).EntireRow.Insert End If Next End Sub ...

Excel: VBA(macro) for Functions, vba macro, sumproduct

vba macro, sumproduct, cell reference: Raja, Here are the basics. You can add your own error checking. Public Function Something(d As Range) Dim sForm As String, sforma as string, sformb as string Dim sform1 as string, sform1a as string, sform1b as string sForm = (--(TEXT(NOW()-XX,...

Excel: VBA question in Excel 2007, text cells, irow

text cells, irow, time jim: Jim, I looked at you code and it looked good to me. So I named a worksheet 1 and inserted a userform. Put in the 4 textboxes using your names. Added the two commandbuttons using your names. I then ran the userform, entered data, and clicked cmdAdd...

Excel: VBA question - how to loop through a range, strd, kathleen kathleen

strd, kathleen kathleen, detail records: Kathleen Sub loopdata() assume you have headers in row 1 so data starts in row 2 (A2) of sheet1 Dim sh1 As Worksheet, r As Range, cell As Range Dim sh2 As Worksheet set a reference to worksheet named Sheet1 Set sh1 = Worksheets( Sheet1 )...

Excel: Vlookup two values and return value in same row, Excel Sixthsense Excel (Multiple Matches)

Excel Sixthsense Excel (Multiple Matches): Hi Shane, Please refer the below example files which will give you some idea in achieving your expectation. Click or Copy and paste the below link in web browser to download the my Example files. a href= https://docs.google.com/leaf?id=0B3_dG0BVmYlOYWI4ZjQwZjUtZDJhYS00MjI2LWIwYTItNzc1NTY1NDRkM2E1&sort=moddate&desc=true&layout=list&pid=0B2TMxNyEPQ2rOGU0MjRkZTktZjhlMy00MTFkLTk0YWQtYzQ2MDMzZTRhYzhi&cindex=4...

Excel: working with email addresses, outlook records, region addresses

outlook records, region addresses, office phone numbers: Mike Castlehouse, If I have house@email.com in A1 then in B1 =IF(A1 , www. &MID(A1,FIND( @ ,A1)+1,255), ) this will display #VALUE if A1 is not empty, but it does not contain an @ symbol. Change the A1 to refer to the cell with the...

Excel: Writing to Log file by VBA, string fname, information guidelines

string fname, information guidelines, macros: Hi vthakar, I suggest you use the following function to determine if the file exists: Function FileExists(filespec As String) As Boolean Dim Fname As String Fname = Dir(filespec) FileExists = Fname End Function Just add this...

Excel: How to add Years, dear tom, th st

dear tom, th st, ogilvy: Raja, A1: =25/01/1950 will divide 25 by 1, producing 25 which will then be divided by 1950 producing 0.0128205128205128 which is not a date - it just performs math on the numbers. So I assume that isn t what is in A1. I will assume you actually...

Excel: alerts, home tab, first solution

home tab, first solution, numbers don: Hi Samantha, If you have Excel 2007 or 2010, this will be really easy. Highlight the pallet ID column in Sheet2 and then go to the Home tab and choose Conditional Formatting , then Highlight Cell Rules, then Duplicate Values. Whenever you enter a...

Excel: Apply Filter Based on Mutliple Item Selection in Data Validation List, target value, iloc

target value, iloc, data validation: Navaneeth, I don t have this problem. I tested the code in Excel 2007 with several kinds of values in the filter column. I can only guess that you made an error in implementing the code in your procedure or you are entering values in I5 that are not in...

Excel: Attempting Index & Match, invoice numbers, customer name

invoice numbers, customer name, invoice number: Sure. Post up your workbook(s) somewhere we can look at them together. Make sure your desired results are demonstrated somewhere to make the goal clear to see. Post online and provide a link here, or use my site (in my profile) to send me the file(s)...

Excel: auto filling data, vendor contact, column c

vendor contact, column c, sheet1: Ainsley, assume in the second sheet, you have Column A Manufacturer Column B Vendor Column C Vendor Contact infor assume the second sheet is named Sheet2 in Y2 of the original sheet put in =IF(U2= , ,IF(ISERROR(MATCH($U2,Sheet2!$A:$A,0)),...

Excel: auto populate in excel, cell b5, data validation

cell b5, data validation, vlookup: Hi Vamique, Assuming the following... 1) All your flat numbers are in column A and that every row will be unique 2) There relevant people names are in column B 3) You will type the required flat number in Cell C5 (Not B5, since column B is in use) ...

Excel: Average excluding highlighted cells, blank cells, conditional formatting

blank cells, conditional formatting, sum formula: Tracy I don t think you can sum cells based on highlighting which I assume is some cell formatting in the range A1:a7. There are a lot of things you can do that may help though. If there is rule that determines what cells get the highlight, you can build...

Excel: bulk find/replace, image names, simple solution

image names, simple solution, file names: I think this would need to be a macro solution - I haven t quite enough information to go on to write a suitable macro, but the basic process is for each name on sheet 2 search sheet 1 for an exact match and replace with nothing repeat for all found entries...

Excel: bulk find/replace, image names, sheet1

image names, sheet1, exact text: FIND/REPLACE is not a formula feature. It makes physical changes to the data, so it requires VBA to accomplish if you re not going to do it manually. Based on your original question, the macro given will do the work. 1) Press Alt-F11 to open the VBEditor...

Excel: how to calculate a formula in excel for schedules, exact hours, military time

exact hours, military time, time range: Lisa, assuming the value you show is in cell C5 then =TEXT((TIMEVALUE(MID(C5,FIND( - ,C5)+1,255))+0.5)-TIMEVALUE(LEFT(C5,FIND( - ,C5)-1)), hh:mm ) worked for me with the sample data you show. I would have to know what the possibilities/situations...

Excel: Do calculation using only visible row, speadsheet, col3

speadsheet, col3, promotion plan: Sylvain If your goal is to total visible (unfiltered) cells then the SUBTOTAL formula will do exactly what it seems to me you want; unless there is still more to the story. If you put the formula =SUBTOTAL(9,$A$2:A3) in column B (starting in row...

Excel: Changing picture in image control, run time error, string fname

run time error, string fname, cell a2: Ed, If I put an image control on a worksheet and wanted to load a picture in it and the image controls name was Image1 I would use Activesheet.Image1.Picture = LoadPicture( C:WindowsDesktophi.bmp ) for example, this was tested in the situation...

Excel: Checking for mapped drive, dos command prompt, command net use

dos command prompt, command net use, string cmd: Hi. I just went through your code. Why are you calling madison when inbuilt vba objects and apia can manage what you need better. Also, how is your NextAvailabeDrive variable getting its value. See support.microsoft.com/kb/256847 Further if only your...

Excel: Code to move data, copy titles, history sheet

copy titles, history sheet, blank row: Maybe this: ========== Option Explicit Sub MoveData() Dim wsSrc As Worksheet, wsDest As String Dim LR As Long, NR As Long wsDest = History this should be the only variable you have to edit Set wsSrc = ActiveSheet change this to...

Excel: How to get comma delimited text file into excel using vba, csv extension, rainfall intensity

csv extension, rainfall intensity, chip pearson: Mallory, if it has a .csv extension, then you can just open it Sub OpenCSV() workbooks.Open C:MyfolderMyfile.csv End Sub that will open it in a new workbook. If you need to get the data into the active sheet starting at say cell A20 Sub...

Excel: Complex Charts, inventory turnover, dynamic charts

inventory turnover, dynamic charts, cell references: Yana I seldom use charts, so am not really expert in them. I suggest you reask the question of another allexperts person and I am sure you can get a quicker and better answer. I also suggest you give the experts some cell references where your data is...

Excel: converting time h:mm to whole number, question pool, stop time

question pool, stop time, whole number: pobrien, You didn t ask me this question. I found it in the question pool. Let us say your 0:16 value is in C5, A5: 14:45 B5: 15:01 C5: 0:16 (=B5-A5 formatted as time) D5: $0.17 then in E5 where you show $0.00 you would use this formula...

Excel: Copying data from one workbook to another, sheet tab, question thanks

sheet tab, question thanks, answer right: That s not having it transfer to all the other sheets . That s entering a formula to pick up the values, and you d have to do that in each sheet. The grouping of sheets makes that task easier. Sure, you can enter a formula in sheet2!A1 which refers to =Sheet1!A1,...

Excel: Count as a Percentage of Another Count?, course score, f3

course score, f3, spreadsheet: Using the table in the attached picture, the formula in G3, then copied down/across the table would be: =SUMPRODUCT(--($A$2:$A$2000=$F3),--($B$2:$B$2000 =G$1),--($B$2:$B$2000 =G$2)) / COUNTIF($A:$A, $F3) This cell is formatted as percentage. Don t...

Excel: Count and Sum, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Umesh, I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file. Follow the link and in the google docs screen click FILE and Click DOWNLOAD (ORIGINAL). a href= https://docs.google.com/leaf?id=0B2TMxNyEPQ2rZGM3OTY5YmUtODFjYy00MWMzLWIyOGQtMzNmNTVlZWJjNzE1&sort=moddate&desc=true&layout=list&pid=0B2TMxNyEPQ2rOGU0MjRkZTktZjhlMy00MTFkLTk0YWQtYzQ2MDMzZTRhYzhi...

Excel: Create timer when an if statement is true, module option, data source

module option, data source, vba: It is rather complex. I have created a user-defined function. Paste this code in a normal module: Option Explicit Dim mvCells() As Variant Dim mbStarted As Boolean Public Function ChangeTimer(R) Dim dTime As Date Dim sOldInfo As String...

Excel: Creating manditory fields, dropdown menus, many thanks

dropdown menus, many thanks, spreadsheet: Grace When do you want the warning to occur? What do you want to happen if there are blanks in mandatory columns? How do you know what rows are required to have data? I am sure you can make it do what you want, but, you need to provide a little more information....

Excel: Creating a search box to search the same column in 4 different sheets, target value, q2 q3

target value, q2 q3, target address: Ritch, Macros have to be enabled. If you want me to look at it, send the workbook to twogilvy@msn.com As I said, tested and worked fine for me. But I also noticed that I didn t properly react to changes in cells other than B9, so here is a modification...

Excel: Cross linking within document, Sixthsense

Sixthsense: Hi Phenix, Sorry to say that it s not possible from my end, and you have to go for Macro (VBA) Solution. It can be done using formula but it requires lot of helper columns to achieve it, which is not a wise method from my point of view. The better method...

Excel: database, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Bob, All your above stated queries are possible in excel and follow the below links for more details. a href= http://contextures.com/tiptech.html http://contextures.com/tiptech.html /a a href= http://www.cpearson.com/Excel/Topic.aspx http://www.cpearson.com/Excel/Topic.aspx...

Excel: databases, database functionality, city state zip

database functionality, city state zip, bob bob: bob Yes that is all possible. There are lots of tools in Excel to emulate database functionality; but it is not a database. I think it is a lot better and easier to use. You do have row limit of about a million which you don t have in a database. You need...

Excel: Deleting blank areas within a range, blank areas, command buttons

blank areas, command buttons, command button: Tim, I put some data in row 16 on a worksheet and pasted in your code (with a command button on the same sheet). I then duplicated A16:I16 several times. I pasted in your code and change RangeA to RangeB and duplicated J16:Q16 several times as well. I...

Excel: Disability to calculating, format cells, text strings

format cells, text strings, text string: Perhaps these values that look like numbers are actually text strings? You can convert text-string numeric values into ACTUAL numeric values with many tricks. My favorite: 1) Put the number 1 into an empty cell 2) Copy that cell 3) Highlight the range...

Excel: Displaying top ten values against corresponding header, cell b10, array formula

cell b10, array formula, excel spreadsheet: Hello Gordon, Sorry for the delayed response, had problems with my internet yesterday. Anyway, let s assume the following... 1) Your headers are in the range A1:T1 2) Your totals of the data are in the range A20:T20 3) In cell A22, we type a caption...

Excel: Div/0, index error, question thanks

index error, question thanks, last option: Can you upload your file somewhere and I ll take a look. It will also help if you tell me what error you get, instead of saying it does not work. Anyway do this... Repeat the For Loop, but this time use Worksheets(i) instead of ActiveSheet . In addition...

Excel: drop down boxes, data validation, drop down boxes

data validation, drop down boxes, blist: 1) Create a sheet called LISTS to hold your special lists and named ranges we will create 2) In A1:A3 enter your first set of drop down values: 1, 2 and 3 3) Highlight A1:A3 and then type ALIST into the name box, just to the left of the formula bar (where...

Excel: EXCEL 2003, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Nitin, No need to go for Macro solution for this query, it s easily attainable in worksheet function. Please provide the sample data in a excel worksheet and your expected result. Attach the sample file in any of the below link. Attach your file...

Excel: EXCEL 2007, array formula, value error

array formula, value error, australia nz: Hi, Assuming Destination value will be entered in cell B8 and Weight in B9, the formula you need is... =(index(B2:F5,match(B8,A1:A5,0)-1,match(B9,A1:F1,0)-1)) This is an Array Formula, hence after entering the formula you need to press CTRL+SHIFT+ENTER...

Excel: Excel 2007, Excel Sixthsense Excel (IF Formula)

Excel Sixthsense Excel (IF Formula): Hi Tomika, Try the below formula =IF( T:Filemanagement[BEN-work from home 121511.xlsx]BEN-work from home!I2= T:Filemanagement[BEN-work from home 121511.xlsx]BEN-work from home!B2,TRUE, False Difference : &A3-B3) The above formula will get #VALUE...

Excel: Excel 2011 and Worksheet_Change, target address, sheet tab

target address, sheet tab, macro code: Worksheet_Change macro code is NOT written in a module. You must right-click the sheet tab, select View Code, then put the code THERE. It will not show up in the macros list -- just type into G1. By the way, instead of using Target.Offset(0,1), why not just...

Excel: Excel, seperate sheet, excel user

seperate sheet, excel user, city details: Easier to show than explain -- send sample wb to me at bobumlas@hotmail.com, use subject of AllExpertsQ and repeat what needs doing. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE: March 29-30, Atlantic City Details here: See http://www.bobsexceluserconference.com...

Excel: EXCEL, equal marks, sort command

equal marks, sort command, rank name: I do not think, you need macro unless you want it to be done automatically every time entry is made in sheet 1. As for sorting, you will need to use Data -- Sort command. For ranking the you want use function - =RANK(C1,C:C:,1) in A1....A5 Regard...

Excel: Excel Chart Question, bar charts, fiscal years

bar charts, fiscal years, explanations: Hi Mike, I ve done something similar, but this is one of the better documented explanations of how to do what it looks like you are trying to do. Try one of these links: http://peltiertech.com/WordPress/clustered-stacked-column-bar-charts/ http://www.ozgrid.com/Excel/stacked-columns.htm...

Excel: EXCEL COMPARISONS, shipping containers, cell b1

shipping containers, cell b1, cell a1: Hi Manoj, What do you mean by compare? Do mean all the cells should be of the same text, check if values are less than/greater than, etc. In addition, is it possible to upload a copy of the excel file to visualize how are the data organized. I need clarification...

Excel: Excel Cross Pollination Question, cross pollination, great gatsby

cross pollination, great gatsby, instr function: . I am not sure I follow which cell needs to be added to which cell, when and under what condition. However you can concatenate anything - Like: =A2 & B2 Otherwise if this isn t it, pls email me some sample at ipisors at yahoo dot com and include...

Excel: excel data, ajmal khan, very time consuming

ajmal khan, very time consuming, sheet1: If the item code is in column A and the list of new prices is on a worksheet called Prices, with item codes in column A and prices in column B, then use this formula in a column next to the item codes in the original list: =VLOOKUP(A1,Prices!$A$1:$B$1500,2,FALSE...

Excel: EXCEL DATA ENTRY, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi NITIN, Follow the link, and in the google docs screen click FILE and Click DOWNLOAD (ORIGINAL) and save it in your PC. Vlookup Solution File (Client Requirement).xls a href= https://docs.google.com/viewer?a=v&pid=explorer&chrome=true&srcid=0B2TMxNyEPQ2rZTdiMTMwNWYtYjczZS00NWE5LWFiMjAtMzhmMGE4NjI2YzEw&hl=en_US...

Excel: Excel - deleting rows whit specific data, whit, digits

whit, digits, len: In a helper column, say H, enter: =IF(LEN(A1) 9,1, ) and fill down. Then calculate (F9), press Alt/F11 (takes you to vba), then Ctrl/G, then enter this 1 line statement: COLUMNS(8).SpecialCells(xlCellTypeFormulas,xlNumbers).Entirerow.Delete then Alt/q...

Excel: excel formula, cell a1, excel formula

cell a1, excel formula, blank sheet: On a blank sheet, enter $10 in cell A1. In A2, enter =ROUND(A1/113%,2) This formula will return $8.85. This means that if your selling price is $10, then the base price is $8.85 (rounded to nearest penny). I.e., if you add the 13% tax to $8.85, the result...

Excel: Excel Formula, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Chris, Try the below formula. =IF(A2= , ,IF(A2 DATE(2010,1,1), YES , NO )) Not Equal To = Less Than Or Equal To = Greater Than Or Equal To Feel free to post a follow-up, if your requirement is not fulfilled. Don t forget to rate the...

Excel: Excel Formula, massechusetts, excel formula

massechusetts, excel formula, 00000: Formulas cannot change the values in the original reference cells, only VBA can do that. But the leading zeros is an old one and easy to fix the display. Highlight the column with your zip codes and apply a custom number format to that column of 00000....

Excel: Excel Formula, conditional formatting, excel formula

conditional formatting, excel formula, excel sheet: You will use CONDITIONAL FORMATTING to accomplish this. 1) put the numbers 1-45 in the cells A7:I11 2) Highlight all the numbered cells 3) Change the font color to WHITE to make the numbers invisible for now, but the numbers are technically still there...

Excel: Excel Formula, ira contribution, excel formula

ira contribution, excel formula, personal contribution: Column D didn t have a formula. That was where I entered my contribution, whatever I happened to want it to be. I showed you going down the column as I contributed more than the cap , the THEIRS contribution maxxed out at $1,000. So, contribute whatever...

Excel: excel formula with E, cell a2, excel formula

cell a2, excel formula, correct interpretation: Mark, I am not sure I understand exactly what your formula is. I will guess it is 183.01 times the math constant e raised to the power of (.000004 times the value of x) =183.01 * exp(0.000004 * A2) or =183.01*exp((4E-06)*A2) or =183.01*exp(4E-06*a2)...

Excel: excel query, excel query, google

excel query, google, sheet1: I would do this with formulas based on what I ve seen so far in the sample sheet you sent. 1) Open both workbooks to set this up. 2) On sheet 480, put this formula in B12: =IF(ISNUMBER(MATCH($F12, master sheet.xlsx !$E:$E, 0)), INDEX( master sheet.xlsx...

Excel: excel query, Excel Sixthsense Excel

Excel Sixthsense Excel: This is the formula for 480 sheet condition =IF(COUNTIF( [Bills.xlsx]480 !$F:$F,$E4), [Bills.xlsx]480 !$B$2, For creating the formula for 480 sheet alone means =IF(COUNTIF( [Bills.xlsx]480 !$F:$F,$E4), [Bills.xlsx]480 !$B$2, ) If your want to include...

Excel: excel query, excel query, cell references

excel query, cell references, waqas: B. Sorry without having all of the data available, for example what the data in the sheets used and what the data in the cell references looks like, I cannot tell wht might be wrong with your formula. I can see that it is missing a parenthesis, but beyond...

Excel: Excel Question

I need more info. The formula would really reside in a cell, and the results of that cell can be in the text box, but it s hard to explain given only what you just asked. Feel free to send a sample wb to me at bobumlas@hotmail.com, use subject of AllExpertsQ...

Excel: Excel Random Numbers, percentage values, richard florida

percentage values, richard florida, decimal places: Joe Sorry to be late in responding I just saw your question-- I believe you will have to trick excel into giving you the answer you want. The formula should look like this--- =+(RANDBETWEEN(1,9)/10000)+1% As you can see the results are being manipulted...

Excel: excel sort in desc automatically with rank, countif, equal marks

countif, equal marks, rank name: Based on the pictured example: C2: =RANK($B2, $B$2:$B$6) + COUNTIF($B$1:$B1, $B2) Copy that down to rank the original data with unique values. In your new table, enter a standard ascending key (like shown in column F) G2: =INDEX(A:A, MATCH($F2,$C:$C,...

Excel: Excel spreadsheet w/Windows explorer filenames, bill manville, hierarchy tree

bill manville, hierarchy tree, folder hierarchy: Joe, ====================================== added information ====================================== I see you have enriched your question. If you want to send your code that is partially working (single level of indenture) to twogilvy@msn.com...

Excel: excell formula problem, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Sheree, Copy and paste the below formula in any of the cell other than A column of Sheet1 and B Column of sheet2. =MAX(Sheet1!A:A,Sheet2!B:B)+ROW(A1) After pasting the formula drag the formula cell to the remaining cells of the column. Feel...

Excel: Div/0, formula 1, len

Excel: Div/0, formula 1, len

Excel: Find and kill VBA

Excel: Find and kill VBA

Excel: how to fix a warning and change .xls back to .xlsx, office mac, data connections

Excel: how to fix a warning and change .xls back to .xlsx, office mac, data connections, spreadsheets

Excel: Formula

Excel: Formula

Excel: formula with alpha and numeric, m1 a1, isnumber

Excel: formula with alpha and numeric, m1 a1, isnumber, numeric values

Excel: Replacing Div/0

Excel: Replacing Div/0

Excel: Find and kill VBA, dummy files, hard disk drive

dummy files, hard disk drive, code option: Raja, Here is some code I adapted from code found on Microsoft Site. It will prompt you for the top directory to search (it defaults to C: which I assume is what you want). It then prompts you for the file to seach for (it defaults to log.txt)....

Excel: how to fix a warning and change .xls back to .xlsx, xls file, answer mark

xls file, answer mark, technet microsoft: Mark, Does the file have a data connection? I assume it must because that is what this message is indicating is disabled. You can check which ones with In Excel, click Data - Connections to show a list, and click Properties to look at the connection...

Excel: format to dd/mm/yyyy, cpearson, yyyy

cpearson, yyyy, ogilvy: Raja, that is what is should give. You then need to format the cell to display the way you want. Otherwise you will be producing a string and not a date. If you want to produce a string, then you won t be able to do date calculations using the results...

Excel: Formatting a number embedded within text cell, true percentage, d19

true percentage, d19, decimal places: Pete = Application Cost ( & Text(D19/D20, 0.00 ) & %) But I would think it should be something like = Application.Cost ( &text(D19/D20, 0.00% )& ) if results of the division is 0.0109818531572828 that would display Application.Cost...

Excel: Formula, cell a1, sheet1

cell a1, sheet1, programming knowledge: Hi Nicole, What you described cannot be done as is, but there is an alternative. Let s say you need to enter information in either cell A1 of Sheet1 or cell A1 of Sheet2. If I understand you correct, if you enter some info in cell A1 of Sheet1, then you...

Excel: formula with alpha and numeric, array formula, a1

array formula, a1, cells: Krista You can do an average using the Array Formula option which is Ctrl+Shift+Enter after you have inserted the formula. Assuming that the figures are in a Row this would do it =AVERAGE(IF(ISNUMBER(A1:M1),A1:M1)) or Column =AVERAGE(IF(ISNUMBER(A1:A20),A1:A20))...

Excel: Formula disappears in listbox linked cell, listboxes, radio buttons

listboxes, radio buttons, ogilvy: Barbara, No setting that you can change to alter the behavior. My perception is that when you close the workbook (or open it - one or the other) the worksheet is recalculated and I assume one of the last things done is that the linked cell is updated by...

Excel: IF formula with time as credential, chris answer, path windows

chris answer, path windows, sc documents: Hi Chris W, Still I could not able to get access with your file. I am getting an error message stating that the FILE IS NO LONGER EXIST . I have attached the screenshot for your reference, please go through the same and if possible attach the file...

Excel: Formula with Two Variable Lookup, two different computers, percentage of increase

two different computers, percentage of increase, table index: You have to construct your table properly for this. You set the beginning of each range, not both values for the range, that s not necessary. The table has to be ordered ascending, too, to work properly. --------80%-----88%-----96%-----104%----112% 0-------0.00%---0.00%---0.00%---0.00%---0.00%...

Excel: IF Function with THEN, numeric entries, value error

numeric entries, value error, aidan: I assume that G2 returns a true false value so =if(g2,c2/sum($d$2:$d$12),0) would do it - however, the value error may indicate non numeric entries in any of the cells c2, and D2:D12. Happy to help further and if it helps to send me a file you can get...

Excel: Function in excel, string constants, question pool

string constants, question pool, ogilvy: FIRDOSE, You didn t ask me this question. I found it in the question pool. You can easily get the counts using the countif function. However, you can t preserve the setting of the color of the total in red unless you put the total in a cell by itself...

Excel: Identify First Non Zero Cell in Series in a Row, index formula, g26

index formula, g26, zero value: The formula in E5, then copied down and acroos would be: =IF(B5= , 0, IF(Manloader!G4=0, 0, IF(COUNTIF(Manloader!$G4:G4, 0 )=1, $B5/$D5+$C5, $B5/$D5))) This formula will work regardless of what values you put in the Manloader cells, you could format...

Excel: Identifying Cell Quantity with Color, final score, onclick event

final score, onclick event, drag down: Hi Deborah, Do you know how to program Macros or in VBA? A simple click and sum solution will require this skill, where you can write a code in the OnClick event of the cell. Aleternatively, there is a formula solution that will take a little extra manual...

Excel: insert blank row after row of existing Data, blank row, rw2

blank row, rw2, ogilvy: Raja, Sub Addblankrow() Dim r As Range, rw1 As Long, rw2 As Long, i As Long Set r = Selection.Resize(, 1) rw1 = r(1).Row rw2 = r(r.Count).Row For i = rw2 To rw1 Step -1 Rows(i + 1).EntireRow.Insert Next End Sub Tested and worked fine for...

Excel: How to inset checkboxes in a drop down list, coloumn, k90

coloumn, k90, l90: Neha Sorry I just saw your question-- I am not sure how to do what you want to do--I suggest you reask the question of antoher allexperts person and I m sure someone will be able to help you. sorry I couldn t be of more help Richard Florida USA...

Excel: label and formula, bottom corner, increments

bottom corner, increments, line 1: Hi Ryan, In C3 paste the following contents, 5 WEll Name 9D1-12-3D1-10-8-30 W1M LICENCE# 11A195 In C8 paste the following contents =c3+5 ------------this would be formula WEll Name ...

Excel: Loading and updating value, command button, student address

command button, student address, text boxes: Private Sub btnAddMarks_Click() Dim wks As Worksheet Dim colNames As Range, markCell As Range Dim vRow As Variant Set wks = ThisWorkbook.Sheets( Sheet1 ) Set colNames = wks.Range( a1 ).EntireColumn vRow = Application.Match(Me.txtName,...

Excel: Lookup Type Formula for Excel, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Vicki Corrin, Assume that your data in A1 cell. A1 cell HSBC 0.5% 01/01/2012. Copy and paste the below formula in B1 cell B1 cell =IF(A1= , ,LEFT(TRIM(A1),FIND( ^ ,SUBSTITUTE(TRIM(A1), , ^ ,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1), , ))))-1))...

Excel: Macro or Better Formula, conditional formatting, cell addresses

conditional formatting, cell addresses, two rows: Hi James! How are you? Please try the following macro Sub Macro1() Dim R As Range Y = InputBox( Please type the address where you want to check for the x ) Set R = ActiveSheet.Range(Y) K = 6: P = 0 For I = 2 To 6 Step 2 If R.Cells(1, 1) = x...

Excel: macro to filter excel file, macro names, google

macro names, google, parse: Sheet1 to Many Workbooks Here s a macro for parsing rows of data from one sheet to many workbooks named for the same values. https://sites.google.com/a/madrocketscientist.com/jerrybeaucaires-excelassistant/parse-functions/sheet1-to-wbs My macro names...

Excel: Macro - Populating Explorer files in directory, folder 1a, spath

folder 1a, spath, getfolder: Try this: Dim FSO As Scripting.FileSystemObject, Locn As Range Sub StartListing() Dim TopFolderName As String Dim TopFolderObj As Scripting.Folder Dim DestinationRange As Range TopFolderName = C:Users umlasDocumentsBobs Folder...

Excel: Mailing Lists, excel user, city details

excel user, city details, type address: I don t see the issue -- works fine as is. Perhaps you can email me a sample which would illustrate the issue better -- bobumlas@hotmail.com, use subject of AllExpertsQ and repeat the issue. ---------- Reserve the dates for BOB S EXCEL USER CONFERENCE:...

Excel: Manual Input into a Cell Containing Formula, manual input, variable value

manual input, variable value, cells: A cell can either contain a value or a formula - putting a value in it would destroy the formula - I would TEND to handle situations where a formula needs a variable value by having that value in another cell - so =a1*1.15 would display 1.15 times the...

Excel: Match up price on another list, vlookup formula, richard florida

vlookup formula, richard florida, j6: cody You should be able to use a vlookup formula that might look something like this--- =VLOOKUP((+I6*J6),B1:C100,2,FALSE) The formula looks within the array range of B1 thru C100 for an exact match. I am not sure what is the meaning of the last...

Excel: Microsoft Office Excel 2007, amy smith, work books

amy smith, work books, office excel: Angie, You said 4 workbooks, but you then use a sample that appears to be 4 worksheets- I assume in a singel workbook. So if you want to send me a sample workbook with data similar to what you show, I will show you how I would do it. send it to twogilvy@msn.com...

Excel: MS Excel Formula, conditional formatting, excel formula

conditional formatting, excel formula, excel sheet: I would use conditional formatting for this - have all the numbers in the cells already, but with white text on a white background. Then use conditional formatting to have the formula =countif(Your6NumberRange,YourNumber) 0 and have this set the font...

Excel: mulriple conditions between cells, situation results, conditional statement

situation results, conditional statement, true value: Stephen: This can be done with an IF function. Here is an example (this function won t actually work because I m using letters instead of numbers; however, I did this to follow your example): =IF(AND(A1 =a,A1 =b),x,IF(AND(A1 =c,A1 =d),y,0)) The IF...

Excel: need a formula for my sheets, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi Waqas, Attach your file in any of the below link and provide me the file downloading path. Windows Live Skydrive: A HREF= http://skydrive.live.com http://skydrive.live.com /A MediaFire: A HREF= http://www.mediafire.com http://www.mediafire.com...

Excel: Need Macro to open a file from specified path., double quotations, true hope

double quotations, true hope, variable names: Hello Jay, I feel you are have problems with variable naming and addressing conventions. Variable names should not be enclosed with double quotations. In addition, you need to make sure your path name ends with a , else you need to manually prefix it...

Excel: Need Macro to open a file from specified path., message box, thanks in advance

message box, thanks in advance, regard: Jayendran There are lots of things that could be going wrong. I don t have enough information to give specific help, but, some of the things you need to check are described below. Start by checking the availability of the file and the rest of the code...

Excel: Need a time macro, time macro, target

time macro, target, cell address: In the SHEET module: Private Sub Worksheet_Change(ByVal Target As Range) Dim cell as Range For each cell in Target If cell.Address = $A$3 And cell.Value Then Application.EnableEvents = False Range( K3 ) = Now ...

Excel: Number formatting in Excel 2007, format cells, exceltip

format cells, exceltip, home tab: Gail, there are a couple of ways you could go on this. One way is to create a new template that you will use when you create a new workbook. So when you choose new, you specify that template and it will create a workbook with your custom settings. for...

Excel: Pass a list of distinct values to cell as a list, data validation, duplicate keys

data validation, duplicate keys, distinct values: Navaneeth, Assumptions you are using the list option for data validation in a cell on one sheet and you want to use vb code to build the source for that list. you can do that either of two ways. ONE way is to build that unique list in another location...

Excel: Passing Variable From Main Module to Form Module, output destination, variable range

output destination, variable range, global variables: Damian --------- added info ---------------- using code that changes the code in the VBE will require the user to select the security option to trust changes in the VBE. Unless the user is a developer I doubt this would be selected and your code will...

Excel: Popup only once, inputbox, edate

inputbox, edate, correct password: Are you saying you didn t want to see the INPUTBOX popup??? If that s true, then you should not have the statement for an Inputbox at all. If that s not true, then it works just fine, and the ONLY popup is the inputbox question asking for the password. I m...