vlookup function, spread sheets, 2 ways: 2 ways of doing this - ONE, and the easiest, is to use a vlookup function - you enter a value, the cell next to it (say) has a formula that is =vlookup(YourValue,YourList,2,false) YourValue is the value you want to look up and yourList is the range of...

Excel: how to lock particular rows and columns in excel sheet, excel sheet, help section

excel sheet, help section, passwords: Prasanna I will refer you to the help section of excel..there is a very good explanation of how to protect elements or cells in the worksheet in that section. Read it carefully and you will have no problem understanding it. Go to toolbar/help/index/...

Excel: lookup function, excel, lookup

excel, lookup: Actually, it is not a simple formula. In those cases I usually use an extra column to store the positions of the items found. For the example, I will assume that the data is on range A1:B100, the city to search is entered on cell E1, and the results will...

Excel: Macro activation Input, obox, new button

obox, new button, simple 1: Hi Yevgeny, That is very simple. 1. Assign a macro to the listbox (rightclick, assign macro, click New button) 2. Edit the new macro so it contains something like this: Sub ListBox1_Change() Dim oBox As ListBox Set oBox = ActiveSheet.ListBoxes(Application.Caller)...

Excel: Macro to convert row headings to columns, row headings, layout location

row headings, layout location, cell contents: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Macro to copy a "new" row in some data to a main worksheet, address phone number, distinct rows

address phone number, distinct rows, daily basis: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Macro to delete text based on a conditional match, png image, variable text

png image, variable text, dear tom: Mike, I already provided you such a macro. Sub RemoveTradingAs() Dim r As Range, cell As Range Set r = Range( A2 , Range( A2 ).End(xlDown)) For Each cell In r If InStr(1, cell, trading as , vbTextCompare) Then iloc = InStr(1, cell, trading...

Excel: Macro-deleting blanks, blank cells, label statements

blank cells, label statements, goto label: Try adding a few simple lines of code to test, though there are other ways as well Msgbox Application.CountA(Selection) Msgbox Selection.Cells.Count Before your IF And, as an alternative, you could just sort the data since all blank rows will go...

Excel: Macro/formula help, excel, vba

excel, vba, sum: I don t know how you have structured your macro, but if you are using any range object to populate the formula, you can know the row that it is located using the row property. With that information you may be able to craft the SUM formula. For example, using...

Excel: Macro Help, two rows, macros

two rows, macros, sums: Staci, Its unlikely that you need a macro to do what you want. Which is probably good news if you want my help, because I don t offer macro or VBA advice on this site. Most things that need doing do NOT require macros and this sure sounds like one...

Excel: Macro to indent wrapped text, space indentation, definition phase

space indentation, definition phase, sheet changes: That macro wouldn t do what you want. That macro runs automatically whenever anything on the sheet changes. All it does is run if you enter 8 characters anywhere (!) then changes the column width to 25.5 of whatever column you entered those 8 characters into,...

Excel: Macro to merge rows that match a condition, png image, variable text

png image, variable text, b14: Mike, guess I didn t study the picture closely enough. The values that you appear to want to retain are blank cells. The ratings in B and C associated with the Trading as rows actually hold values and you want to delete those (when the row is deleted)....

Excel: Macro Needed for Collecting Data from different Fiels, spath, straight copy

spath, straight copy, sh1: Kiran, hard to write code without knowing much about what the data looks like. I am making an assumption that the data is on contiguous range starting in A1 of each sheet and that the sheets all have the same names in each workbook as you stated. I do...

Excel: Macro Question on unique number, 4 digits, department shares

4 digits, department shares, start from scratch: You should keep a separate file in G named LastNumber.xls which simply contains the last used # in cell A1. This workbook can be opened, use the value in A1+1, add 1 to A1, and close the file (saving it) so the next person can get it. You can use a function...

Excel: Macro question, c cells, range f1

c cells, range f1, b cells: Sub Combine() On Error Resume Next Set NewSheet = Worksheets.Add n = Sheets( A ).Cells.SpecialCells(xlCellTypeLastCell).Row With Sheets( A ).Range( A3 ).CurrentRegion .Offset(1).Resize(.rows.Count-1).Copy NewSheet.Range( A1 ) End With ...

Excel: Macros for conditionaling Formating, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: You can do pretty much anything with VBA code. I m sure you could mimic conditional formatting on a greater scale… but yes, it would involve programming. Let me know if you have any other questions or comments. Also, be sure to visit my web site and...

Excel: Macros and protected worksheets, alternate approach, ogilvy

alternate approach, ogilvy: Phil At the top of each of the macros that need it Activesheet.Unprotect Password:= Dog at the bottom ActiveSheet.Protect Password:= Dog You can also look in help at the protect method and read about the UserInterfaceOnly option for...

Excel: Macros results different when using step through, copy worksheets, 6 cells

copy worksheets, 6 cells, false application: Send me the wb - bobumlas@yahoo.com use subject of AllExpertsQ . Hard to tell what might be causing the difference. By the way, this line: Union(Cells(x, 2), Cells(x, 3), Cells(x, 5), Cells(x, 6), Cells(x, 7), Cells(x, 8), Cells(x, 9), Cells(x, 10), Cells(x,...

Excel: Mandatory entry from data validation list, conditional formatting, data validation

conditional formatting, data validation, mandatory entry: Options are (1) save the file with a default answer (2) use conditional formatting to cause any output to be displayed as white text on white background UNTIL all compulsory responses have been made. There isn t any other way that I know of to force...

Excel: Marco to Hide Specific Columns not working, quick response, culprit

quick response, culprit, cells: Seems VERY odd that if you do this manually then the columns you intend to hide actually get hidden but then when you run the macro you get different results assuming that your recorded macro did hide the columns you wanted and intended to be hide while recording....

Excel: Master Tab, one tabs, sheet names

one tabs, sheet names, cell reference: Not sure what you want the master tab to do - however, to create a copy of an existing sheet, right click it and move/copy, create copy - that gives you the layout - IF you want a consolidated total, that s easy - =SUM(FirstSheet:LastSheet!CellRef) ...

Excel: Maxa, array formula, curly brackets

array formula, curly brackets, menu tools: A few pointers: - Be sure to enter it with Crtl+Shift+Enter. Excel will put curly brackets around the formula on the formula bar to mark that it has been entered this way. - Check that the ranges and the names of the worksheet are right - Evaluate the...

Excel: Merging files, excel, merge

excel, merge: The merge option from the menu is part of a full process of sharing the workbook, distributing it to other users, and merging their contributions. Probably that is not what you are trying to do, so I would suggest taking a look at the copy/paste/merge section...

Excel: Message text box while macro is executing, control toolbox, site tip

control toolbox, site tip, vba code: Ian, You could use the techniques shown here (it wouldn t necessarily need to be a progress bar: http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/ or you could show a non modal userform myuserform.Show vbModeless ...

Excel: Moving Excel Comments, spreadsheet

spreadsheet: Comments are designed to be comments on a particular cell, so will always link to that cell - if you EDIT the cell you will see it s location, but on mouse over it will indicate the comment against the cell itself. I cannot think o any way round this I m...

Excel: MS Excel, first position, excel project

first position, excel project, occurrences: David, What you mean is this: For every letter in the first position (Col G), you want to know the count of the other letters in the second position , column H. In other words, if there are 6 A s in the first position, there are a certain number...

Excel: Multiple Formulas., lease land, sum 4

lease land, sum 4, spreadsheets: Er, yes, it is rather long. You don t need multiple sums (use commas to seperate the ranges eg =SUM(A1:J1,A2:K2,A3:K3)), and you don t need individual cell refs - S:OKCSharedLease LandAdmin AssistantWork Drivers (2009)[Don_Monthly (2009).xls]2009...

Excel: Multiple Selection Drop Down, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: In a nutshell, nope. Drop-down boxes in Excel aren t really designed for multiple selections. You can do it in Microsoft Access, but without SERIOUS programming, forget about it in Excel. Let me know if you have any other questions or comments. Also,...

Excel: Multiple V-lookup?, balanced scorecard, correct column

balanced scorecard, correct column, kpi: Greg, You say look up a Name and vlookup does come to mind, but then you say look up a specific score - so you will have two dropdown s and the Supervisor will select Name and the KPI name is now my thought. In the below, I assume the formula presented...

Excel: Multiplying selections in combo box and displaying result in text box in a userform, free microsoft excel, combo boxes

free microsoft excel, combo boxes, math operations: Sure: TextBox1 = ComboBox1 * ComboBox2 Put this in an AfterUpdate event for either or both of your combo boxes, or make the textbox bound to a calculated query value. It sounds to me like you just need to learn how to properly create CALCULATED QUERY...

Excel: macros and matching a cell to a column, cell a1, message answer

cell a1, message answer, paranthesis: I think I m missing something here- the macro says if worksheetfunction.countif(sheets( Sheet2 ).range( B:B ),sheets( Sheet1 ).range( A1 ).value) 0 at the start, which counts for the number of times A1 exists in sheet2!b:b - if MORE than zero, the rest...

Excel: making a word have numeric value, days of the week, friday saturday sunday

days of the week, friday saturday sunday, saturday and sunday: CAMMIE The formula should look like this: =IF(A1= FRIDAY ,1,+IF(A1= SATURDAY ,1,+IF(A1= SUNDAY ,1, ))) If you wanted to name the days Friday=5, Saturday=6 and Sunday =7, then you can change the formula accordingly. The way the formula is worded...

Excel: manipulating of data using existing vba in excel, cell a2, cell a1

cell a2, cell a1, spath: Mike, That would be an indication that no textfiles that met the criteria were found or only one file was found. The code assumes that many files will be found. I have added some protective statements. One problem might be the extension I mentioned...

Excel: matching two columns and copying related (missing) data to another sheet, blank cells, blank cell

blank cells, blank cell, missing data: Yuan, On sheet2, select column 2 and do Edit= goto and select special, then select blank cells. this should select all the blank cells in column Q. We now want to enter a formula relative to the first selected cell. Let s assume the first selected...

Excel: Get the minimum value, blank cells, column values

blank cells, column values: Chris, Create another column to change the numbers with an associated N into blank cells, and to pass on unchanged the numbers with an associated Y. Then take the MIN of that column. A simple IF statement which checks for an N or a Y is the...

Excel: missing dates in excel, conditional formatting, missing records

conditional formatting, missing records, hourly basis: Srikanth Working with dates and time is always tricky. I sugest that you use a formula that will test the cell to determine if it is equal to the previous cell plus 1 hour. That formula might look something like this. =IF(+C7=C6+1/24,1,2) In this formula...

Excel: missing dates in excel, excel, date

excel, date: If the hours are in column B, you could use a formula like this to detect where the gaps are and how wide they are: =IF(B2-B1 TIME(1,0,0),(B2-B1)/TIME(1,0,0)-1, ) Copy the formula down on the column and it will show numbers representing the missing hours...

Excel: missing dates in excel, format button, missing records

format button, missing records, hourly basis: If the date is in one column & the hour in another (say col A & B), you can select all the data in column B, press the tab key to make B2 the active cell, then use Format/Conditional Formatting, change Cell Value Is to Formula Is , then enter: =ROUND(B2-B1,5)...

Excel: how to modify #N/A in percentrank output, PERCENTRANK

PERCENTRANK: Paul: Well, you are going to have to use an IF of some type to perform this task; however, it can all be combined in one function. Let me cover the variables first: Lets say... 1) Your array is in A1:A5 2) The value you are checking is located in...

Excel: NCAA tournament bracket, dialog box contents, ncaa tournament bracket

dialog box contents, ncaa tournament bracket, data validation: Nick, Examine the dialog box contents for the data validation list in one of the copied sheets. What do you see there? Is there a reference to the sheet name fin the list source? if so, take it out and make a copy from that copy, and see if it works....

Excel: NEED HELP With Formula to Look up data and replace, vlookup, expl

vlookup, expl, thanks in advance: N, I d like to help but I don t understand what you want. Please ###spell check### your question before sending. Pls don t abbr. wds. Technical questions are complex enough without making the poor expert guess what you mean. Be...

Excel: Need to assign points to population in a group based on their rank in that group, vlookup, increments

vlookup, increments, match: Chris, I hope you know about the RANK function, because you should apply this first. Put it in a column next to the score you re ranking. It will create a number from 1 to X, with 1 being the best and X being at the bottom. RANK will give identical...

Excel: Need help with a formula, logical test, rep name

logical test, rep name, sumif: The multiple condition countif function which this equates to will work with as many conditions as you want - so =sumproduct(--(firstrange=test),--(secondrange=test),--(thirdrange=test)) etc the =test can be any logical test - as you saw in the original...

Excel: Need to Insert 9 rows after each line, question thanks, left border

question thanks, left border, shift key: Evelyn, this will put in the sequential numbers and fill in the other data: Sub sequentialNumbers11() Dim lastcolumn As Long Dim lastrow As Long, r As Range, cell As Range lastrow = Cells(Rows.Count, 1).End(xlUp).Row + 9 lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column...

Excel: Needs to find missing records, cell a2, dropdown arrow

cell a2, dropdown arrow, missing records: You could subtract each set of two rows from each other. Say your times are in column A, starting from cell A2, on row 3 in any cell, add this formula: =A3-A2 Copy down to fill your column. Now apply Data, Filter, Autofilter. If you click the dropdown...

Excel: Needs to find missing records, missing records, time comparison

missing records, time comparison, time result: Srikanth, If a record is missing, how can you find it????? Its gone, right? There is no record to find! You can t return non-existent data! But maybe what you really mean is you want to IDENTIFY which records are missing, instead of recovering...

Excel: Needs Macro for inserting rows for missing records, missing records, hourly basis

missing records, hourly basis, semicolon: srikanth, First, this would require a macro. do you want a macro? If so, send me a sample spreadsheet with at least several days of data in it. It isn t clear to me if your times are in the same cell with your dates or not or in what cells you data...

Excel: New row...continued.., ogilvy, new date

ogilvy, new date, r2: Paul, if r2.Offset(0, 13).Value = r2.Offset(0, 13).Value _ + Cells(i, N ).Value did what you want, then To add a line to update the date: r2.Offset(0, 13).Value = r2.Offset(0, 13).Value _ + Cells(i, N ).Value r2.Offset(0,12).Value...

Excel: Number format in concatenate, text string, help file

text string, help file, proper format: XiDau, You change the NUMBER into TEXT with the TEXT function and then concatenate it into your text string. The TEXT function has a parameter called the FORMAT TEXT. The procedure for looking up and putting in the proper Format Text value is clearly...

Excel: Numbers to text, twelve thousand, functionality

twelve thousand, functionality: There is no built in functionality in EXCEL to do this, you will need a custom function, which is in essence a macro, to do this. Rather than paste all of that code here then go to the link below. It is a long bit of code. No need to reinvent and retype...

Excel: need a help, vlookup, lookup

vlookup, lookup, excel: Prakash... Unfortunately there is no built in function meant to do exactly what you are trying to do. But, with some work, we can get there. Try this formula: =LOOKUP(2,1/(A2:A9=A1),B2:B9) A2:A9 is your lookup range and B2:B9 is the value to return....

Excel: need a help, array formula, formula index

array formula, formula index, last match: Prakash, No existing function does that if =Vlookup(F9,Sheet2!$A$1:$B$100,2,False) gives you the value from column B for the first occurance of the value in F9 found in column A of sheet2, then to get the value from column B for the last occurance...

Excel: need a help, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, last match: You can specify which column of a range you want to return. See this tutorial: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable...

Excel: needs Macro to copy data in excel, layout location, voice of experience

layout location, voice of experience, macros: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Offset() imbedded?, interger, b9

interger, b9, negative numbers: I ve assumed there is a typo in your question, as if A10 was 9, it PRESUMABLY woud be a nine cell range, so B2 therefore =SUMIF(INDIRECT( B &11-A10& :B10 ), 0 ) gives you what you want for your more complex one, simply sum to B9 instead of B10,...

Excel: Page breaks in an alphabetic list, vba macros, header row

vba macros, header row, page break: Robert, Sub pagebrk() Assume 1st row is a header row ActiveSheet.ResetAllPageBreaks With ActiveSheet.PageSetup .Orientation = xlLandscape .PaperSize = xlPaperLetter .Zoom = 67 .FitToPagesWide = False .FitToPagesTall = False End...

Excel: Paste special values into first blank rom, false selection, false application

false selection, false application, blank row: Jackie, If you copy an entire column, then you will run into this problem. Better to copy only the data you need and I would assume it is much less than a full column Sub copyData() Dim r1 As Range, r2 As Range Dim r3 As Range With Sheets( Manual...

Excel: Percentages, excel

excel: Annika... I am not sure how you have your worksheet set up, but I would recommend starting out by listing out the possible answers to the questions. Once this step is complete, you can use the countif function to count the number of times that each possible...

Excel: Perpetual rows, blank rows, txt document

blank rows, txt document, methodology: Magnetite, It s difficult to know what you did. Your description isn t precise, and uses non-standard Excel terminology. If you have a million rows of BLANK data, then how is it that you have a million rows? Every worksheet you open has a million...

Excel: Previous 90 Days Formula, SUMIF, date math

SUMIF, date math: Hi Mark, This should do what you want: =SUMIF(range of dates, = &TODAY()-90,range of landings) SUMIF conditionally adds up numbers based on specified criterion. TODAY() returns the current date and 90 days is subtracted from that. The = term...

Excel: Get Previous Date, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, cell a2: Just subtract 1 from your date. For example, if your date was in A1, in cell A2 you could say: =A1-1 That will give you the previous date. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my...

Excel: Problem with my formula, louise louise, data columns

louise louise, data columns, value error: Louise, Your formula looks correct, but there is one important thing I can t tell. All of the arrays, which you have named, MUST be exactly the same size. That is, they MUST all have the same number of cells in them, and possibly (but I am not...

Excel: Product, thick border, correct numbers

thick border, correct numbers, tiny square: Hi Sarah, Sounds like you would benefit a lot from a beginner book on Excel. The formula you are looking for is simply: =C2*D2 You enter that formula into for example cell E2. Then you copy that cell and paste it in all of the cells below it to...

Excel: Pulling Data From Multiple Files, spath, sname

spath, sname, sh1: Zohar, Sub CopyData() Dim bk As Workbook, bk1 As Workbook Dim sh As Worksheet, sh1 As Worksheet Dim sPath As String, sName As String Dim rw As Long, r As Range Set bk = Workbooks( Masterfile.xls ) change name Set sh = bk.Worksheets( Results...

Excel: parse and sum multi-value cells, layout location, cell values

layout location, cell values, voice of experience: You will need a macro to do that and sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and...

Excel: percentages, homework problem, number changes

homework problem, number changes, percentage increase: Abdul, Its quite easily done (QED)! I apologize for thinking this was homework. I have no way of telling who is writing to me, and I am not supposed to do homework for students on this site. I have no idea of your age, or experience,...

Excel: picture or clip art to be an answer in a formula, magic tip, cell a1

magic tip, cell a1, qat: Send me a sample WB and I ll show you how to do it -- you re going to need to have ALL your pictures take the same number of rows and ALL be down one column (which can be out of view) -- the basic approach is using the Camera Tool - I ll explain how to get...

Excel: To print all the sheets contained in the workbook, worksheet menu bar, excel addins

worksheet menu bar, excel addins, number 0: You can make it an addin simply by selecting the ThisWorkbook in the project window and change the IsAddin property to True (and for consistency you can name it whatever.xla). After this opens, you will have a new command in the file menu ( Print Same Range...

Excel: If And problem, o18, technical perspective

o18, technical perspective, thanks john: John this worked for me (from a technical perspective): =IF(AND(N18 =0,O18= ), OVERDUE ,IF(AND(N18 0,O18= ), PENDING ,IF(Q18 0, CLOSED , Open ))) AND(N18 0 ,O18= ) should not have the zero in quotes if you are checking a value/number Whether...

Excel: QTD,YTD numbers, exact data, a100

exact data, a100, c100: Claire: Based on your example it appears the totals are at least numbered. For example, 195,488 shows beside the 1 (which looks like it is in cell A7ish). The 252,509 is beside the 2. We know Q1 is months 1, 2, and 3, so we can create a formula with...

Excel: Quary, excel sheet, missing something

excel sheet, missing something, new location: I must be missing something here - EITHER select all (ctrl A) and copy, then paste to new location, or right click and then choose move or copy - create a copy, and choose to book - New Book as the option. If this wasn t what you referred to do let me know...

Excel: Question, excel worksheet, print button

excel worksheet, print button, word doc: With ActiveSheet.PageSetup .PrintQuality = -3 .Draft = True .BlackAndWhite = True End With SHOULD handle the black and white bit As to protection, when you go to tools, protect sheet, you have options over what you are protecting...

Excel: Question for you Tom, john walkenbach, stephen bullen

john walkenbach, stephen bullen, rob bovey: Ian, Books by John Walkenbach are very good http://www.spreadsheetpage.com should have links to his books. A more advanced book would be Professional Excel Development by Stephen Bullen, Rob Bovey and John Green They also have a lower level book...

Excel: question, control toolbox, properties button

control toolbox, properties button, excel worksheet: Steve, Activesheet.PageSetup.BlackandWhite = True In Excel 2002 and Excel 2003, you should get the same screen in Tools= Protection= Protect Sheet Here is a link with detailed information: http://support.microsoft.com/kb/826923 Description of...

Excel: Random Generator, random name generator, random number generator

random name generator, random number generator, random drawings: Veronica, I think what you mean is that you are trying to create a random number generator which generates a number to match one of the employee numbers. The matching employee has won . The first thing to do is turn the calculation method to...

Excel: Rank, monte carlo simulation, recalculation

monte carlo simulation, recalculation, ogilvy: Michael, If you mean you simulation is causing a recalculation at each step of the simulation and the formula is slowing everything down, then how about turning calculation to manual while your simulation is running and turn it back to automatic as the...

Excel: Ranking in Excel

Troy A sort should work for you-- If you have these amounts in this order in column A -.15 -.11 +.10 -.13 And you sort column A in descending order, you will get +.10 -.11 -.13 -.15 If I am understanding correctly, this is what you want....

Excel: RE: Counting Rows/Entries, 1st x1, logical functions

1st x1, logical functions, final result: Josh, Lets start over. There s no example with this followup though the text refers to a rewritten one. I m confused. Rewrite your question so its clear and unambiguous and doesn t have mistakes in it. be sure to cover all possible cases... what...

Excel: Re: percentages in excel, free microsoft excel, percentages in excel

free microsoft excel, percentages in excel, 599cd: It s just a matter of math. 523.60 = X * 1.08 Solve for X. X = 523.60 / 1.08 There s your answer. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO...

Excel: Reading data from multiple worksheets, reading data, james allen

reading data, james allen, wks: To scan across multiple worksheets without naming them individually, use code like this: Sub fromOtherSheets() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets If (wks.Name Welcome ) And (wks.Name Totals ) Then [yada...

Excel: Reconciling variable data amounts to a variable given total, b27, set of numbers

b27, set of numbers, variable data: Suppose your #s are in A1:A27. In C1 put =A1*B1 (B1 is empty). fill down to C27. In C28 put =SUM(C1:C27). Use Solver (add-in), set the value of C28 to the total you want; constrain B1:B27 to be =0 and also =1 and also Integer. When Solver is done, B1:B27...

Excel: Regression, linear trend, select tools

linear trend, select tools, regression: Hi Mildred, The easiest way is by using the analysis toolpak add-in: Tools, Addins, check the Analysis Toolpak addin (not the one which has VBA in its name). Then select Tools, Data analysis and pick the regression option. The rest speaks for itself...

Excel: To Rename and save workbook, pak suzuki, fileformat

pak suzuki, fileformat, c28: I THINK you re asking me 2 things. One is how to save the activesheet as a workbook. The code for that is: ActiveSheet.Copy ActiveWorkbook.SaveAs Filename:= C:aaa.xls I believe you re also asking how to delete an existing file. This is possible...

Excel: Reporting information after a filter has been applied., excel, subtotal

excel, subtotal, autofilter: Peter... Somewhere in the header row place the following: =SUBTOTAL(2,B:B) This assumes the number you are counting is in column B. the 2 in the function above tells Excel to count. Joseph Harris http://www.excelyogi.com/functions.html ...

Excel: Returning rows with data, data validation, quoting system

data validation, quoting system, screen dump: Dan, I ve given your workbook a brief look, and it looks as though it works properly. When I put a number in the QTY column of the database, the part description and other info show up on the other sheets... so I don t understand what DOESN T work....

Excel: Rounding, excel, round

excel, round: I am assuming that you want to round up, as 1.768 is closer to 1.75 but you expect it to be 1.79. With that, you can use a formula like: =IF(CEILING(A1,0.05)=CEILING(A1,0.1),CEILING(A1,0.1)-0.01,CEILING(A1,0.05)) If you don t want to round up, substitute...

Excel: Run Macro When Entering Data in Cell, programming macros, target address

programming macros, target address, target range: Try these TWO events Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $A$1 Then MsgBox test test End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = $A$1 Then MsgBox test End Su...

Excel: Running an event on open, false application, value date

false application, value date, pastes: Steven, I would just run it from the worbook_Open event which must be in the ThisWorkbook Module in the Thisworkbook module Private Sub Workbook_Open() Gets auditor name only on the active assign date Copies and pastes it, and the info...

Excel: read cell, search for the content in other sheet, cell b1, srow

cell b1, srow, sheet1: Peter, I have a feeling that this can be done WITHOUT a macro, but I am not sure because I don t understand your question exactly. You have used too many THATS and ITS, which are never clear. You said: [with my comments in square brackets] ...

Excel: recommended appropriate function for the following?, test ct, conditional test

test ct, conditional test, days group: Dianna, Since I can make Excel do anything , this is quite possible. In fact, you ll be glad to know, its easy. All you need is a nested IF statement. An IF statement consists of three parts: 1) A CONDITIONAL TEST (CT), which must...

Excel: recovery of data, inner contents, correct filename

inner contents, correct filename, datas: Guru, Sounds like you made a serious mistake and destroyed your original file. You should be very careful when using SAVE AS and be sure you have the correct filename typed in the correct place. I doubt that you can recover the A file. ...

Excel: refEdit values to Word & incorrect date format, word form, date formats

word form, date formats, relevant section: OK, so we can be sure the date is coming in as a date which is good! dateserial is something I nearly always use as it stores the serial number, and therefore cannot get misinterpreted as a US format date rather than UK etc format =dateserial(year,month,day)...

Excel: reference cell from other xls file, excel, reference

excel, reference, indirect.ext: In this case you may use the function INDIRECT.EXT, that can be found here: http://xcell05.free.fr/morefunc/english/indirect.ext.htm It works like the INDIRECT function, but it works on closed cells, so it is the closest alternative to a direct reference....

Excel: remove macros, dialog box, existence

dialog box, existence, yahoo: The existence of VBA code ANYWHERE (modules, sheets, Thisworkbook) will trigger the dialog. If you still can t find it, feel free to email wb to me at bobumlas@yahoo.com and I ll point to where it is (or remove it if you wish). use subject of AllexpertsQ...

Excel: removing columns, horizontal scroll bar, lookat

horizontal scroll bar, lookat, column names: You will at some point have entered data to the right of the area - USUALLY by formatting an entire row with a border in my experience - this macro Sub OptimizeWorkbook() Dim ws As Worksheet Dim lastrow As Long, lastcol As Long Application.ScreenUpdating...

Excel: removing spaces, excel user, upc numbers

excel user, upc numbers, text result: Sorry - I misread the question and THOUGHT you d said the cell was formatted as a number rather than general - change the formatting to NUMBER with zero decimal places and it should work. The reason the TRIM function is working is because thats a text function,...

Excel: repeat a value until new value is seen, blank cells, digit numbers

blank cells, digit numbers, letters and numbers: Scott, Well, there is sort of a way to do this, but of course it s not automatic . Its a one-shot deal that, if you add more data to this pseudo-database, you ll have to repeat, or remember to take care of the problem as you add the new data. ...

Excel: Sales cascade model, cascade model, binary input

cascade model, binary input, maturities: Andy, I propose a slight change to the main table you re working with. Instead of having ROW 1 be a marker as to when a store opened, make it be COLUMN A. Instead of a simple 1 or 0, put the OPENING MONTH NUMBER in this column. Then in place of where...

Excel: Sales Data, dcount, dsum

dcount, dsum, autofilter: Cassandra, Your pivot table method might work. Try exploring the MATCH() and INDEX functions instead of LOOKUP. If you can get the list of numbers with LARGE, , then MATCH will tell you which rows have those numbers. Then you can use INDEX to get...

Excel: Save as and attach to email command, default mail client, spath

default mail client, spath, e mail adres: Hi Tanya, Q1: 1. Paste this code into a normal module in your file (adjust the path to your situation): Sub GetFileNameAndSave() Dim vFilename As Variant Dim sPath As String sPath = c:data vFilename = Application.GetSaveAsFilename(sPath,...

Excel: Saving a file according to the Purchase Order No., true application, excel spreadsheet

true application, excel spreadsheet, xls: In the code behind ThisWorkbook : * (assumes F3 contains the PO# to save with): Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Cancel = True Application.EnableEvents = False ThisWorkbook.SaveAs Purchase No...

Excel: Search an array, row index, z500

row index, z500, cell address: Thanks for the challenge. Let s say the value you re looking for is x and that you re looking for it somewhere in A1:Z500. Ctrl+shift+enter this formula to get the address of the cell: =ADDRESS(LARGE(N(A1:Z500= x )*ROW(1:500),1),MATCH( x ,INDIRECT(LARGE(N(A1:Z500=...

Excel: Select a the most in the right value of a row array, conditional test, true return

conditional test, true return, f1: Leonardo, It can be done using a nested IF statement. Each conditional test will be NOT(ISBLANK()) of one of the cells, beginning at the rightmost cell and progressing to the left with each successive nesting. If NOT(ISBLANK(F1)) is true, return the...

Excel: Selecting Multiple Rows on the basis of a Criteria, conditional formatting, format

conditional formatting, format, excel: Azeem... You can do this with conditional formatting. Step 1: Highlight your data table Step 2: Click Format Conditional Formatting on the toolbar. Select “Formula is” in the first drop-down and enter “=$b1 1000″. Click the Format button...

Excel: Separate day from the rest, cell a1, string text

cell a1, string text, dddd: Valen, First assume what you show is in cell A1 and A1 hold a real Excel date (not a string/text that looks like a date) so define what you mean by day (day of the month, or day of the week) If you mean the 2 (assuming m/d/y), =Day(A1) will give...

Excel: Separating number from text, string manipulation functions, free microsoft excel

string manipulation functions, free microsoft excel, excel tips tricks: You ll have to use text string manipulation functions like LEFT and RIGHT. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: Setting up a formula for summing without adding the original cell vaules, blank cells, number 0

blank cells, number 0, excel help: Daniel, If you want to count the number of cells that are written in you can use CountA If you want to count the number of cells that have numbers in them you can use count If you want to count the number of cells that have a specific date in them...

Excel: Shadow form use in excel, paste values, blank row

paste values, blank row, tourism office: You didn t refer me to the original answer, but the phrases used don t SOUND like mine - not least, because using the DATA menu you have an online form already in existence - but I m happy to look at your data and give you a form that could (for example) validate...

Excel: Simple Excel Chart Line graph, group ranges, line graph

group ranges, line graph, plot area: Lisa, Each of your groups is called a data series. There are several techniques you can use. 1) Just display all three groups on the same Y-axis. The scale of the Y-axis will automatically adjust from zero (probably) to 20, to encompass...

Excel: Simpler way to do this, free microsoft excel, dividing by 4

free microsoft excel, dividing by 4, excel tips tricks: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: Sorting in Excel without messing up row relations, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Short answer: don t use shared Excel documents. Excel wasn t built for that. Use a database program like ACCESS for this. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT...

Excel: Sorting a list based on various criteria within each cell, array formulas, ddsys

array formulas, ddsys, curly brackets: Rick, Excel supports special formulas called array formulas. That is what you are doing when you look at multiple rows in column B - you are evaluation a condition against and array of values. The use of ctrl+shift+enter tells excel to evaluate your formula...

Excel: Sorting and listing data, customer account manager, database function

customer account manager, database function, drop down list: Geoff, If by total LEVEL of sales you mean a SUM of sales for the selected customer OR account manager, then there is certainly a solution. In fact there are at least three. However, you will need two separate equations: one if you choose...

Excel: Source of VBA Array, dimensional array, programmatically

dimensional array, programmatically, rnd: Khurram http://support.microsoft.com/kb/213798 Sample Visual Basic macros for working with arrays in Excel there really isn t that much to it. Sub Manhandle_an_Array() Dim i as long, j as Long, k as Long dim v as Variant v = Range( A1...

Excel: SPREADSHEET DESIGN, spreadsheet design, financial stats

spreadsheet design, financial stats, business segments: Tina, The key to making a report such as this work properly and easily is the organization of the database! To help figure that out, it is useful to know what sort of questions you are likely to ask of your database, and across what business segments...

Excel: Stock Vesting Calculation, www google co uk, rlz

www google co uk, rlz, google: I m not familiar with the math that would be needed (or most of the terms you used!) - but see http://www.google.co.uk/search?sourceid=navclient&ie=UTF-8&rlz=1T4RNWN_enGB318GB318&q=Stock+Vesting+Calculation+excel which gives a number of possible answers...

Excel: SUMIF Text, column c, list of names

column c, list of names, c1: It SOUNDS as though you don t want to sum, merely to lookup the appropriate value? if so, it s vlookup you need =vlookup(f1,$C$1:$D$10,2,false) where F1 is the value you want to look up, C1:D10 is the data range where the data falls in column c - you...

Excel: Subject: Macros and Custom Toolbars, speedy reply, toolbar change

speedy reply, toolbar change, program knowledge: Carol, JK Pieteriese has written and extensive article on distributing workbooks with code and how to implement specific features such as menus and toolbars. go to the URL and as a minimum look at the Menus Toolbars sections in the article. I...

Excel: Subtotal based on value in range, apples and oranges, sum columns

apples and oranges, sum columns, b1000: Matt, Then I guess I would see it this way: Dim rngB as Range set rngB = columns(2) if (Application.countif(rngB, Apples ) + _ Application.countif(rngB, Organges )) 0 then Selection.subtotal GroupBy:=2, _ Function:=xlSum, TotalList:=Array(18),...

Excel: Sum function macro, empty cells, word client

empty cells, word client, blank row: Raza, Assume the word Client is in Cell A1. further assume there is one blank row between each set of data. Sub InputSums() Dim i As Long, cell As Range Dim r As Range, r2 As Range lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i = lastrow +...

Excel: Sumif Statement 2 conditions, then sum, sumproduct, minus signs

sumproduct, minus signs, y100: Eric, If you have Excel 2007, use SUMIFS to do this. The function reference describes this well. If you have Excel 2003, use the advanced form of SUMPRODUCT(). You can use SUMPRODUCT() in 2007 also. The advanced from of SUMPRODUCT is not described...

Excel: Summary of random non-congruent list, project ids, time entries

project ids, time entries, pivot table: Jay, There are several ways to do this. If a pivot-table meets your definition of a function solution , than that s certainly the easiest. You don t have to write a single equation! Just put the ID in the ROW area and the hours worked in the...

Excel: sheet tab name, vba procedures, cell a1

vba procedures, cell a1, sheet tab: When you want things to happen automatically in Excel, it often involves using Visual Basic for Applications (vba) procedures, aka macros. If you don t know how to use vba, your issue can t be solved. If you do know a little vba, then you d enter the following...

Excel: sliding scale, cell a1, sq metres

cell a1, sq metres, sq metre: Sorry, I should have had both references be the same cell. If A1 contains .9, then 150-50*A1 gives 150-50*.9 which is 150-45 which is 105, which is what you said you wanted. If the value is 1.1 in A1, then 150-50*a1 is 150-50*1.1 which is 150-55 which is 95,...

Excel: How to solve of creating an infinite loops?, blank row, vba code

blank row, vba code, infinite loops: Howard, As I understand the question you want to only have one blank row between each filled row. Base on your description, you don t have any cases where there are two adjacent filled rows. The column to make this determination is column B rw...

Excel: sorting problem, countif, custom filter

countif, custom filter, filter auto: Not too complicated for excel, but a bit complicated for me to follow (but it s been a long day at work!) - however, I THINK I ve grasped the requirement - you want to show ONLY those cases where the data in B exists in C - I ll assume that E is currently...

Excel: the specified formula cannot be entered because it uses more than 64 levels of nesting, f4, premiums

f4, premiums, little bit: Marinda: Is there any particular reason you cannot do this? =IF(F4 0,-15,+15) Beyond the above Excel does have a built-in limit on the number of nested IF functions. There is no way around that limit; however, you can convert to a different formula...

Excel: split text string into separate columns, elmira st, harmony park

elmira st, harmony park, jasmine st: You want the initial number in one column and the rest in another column? Or more involved than that like UNIT E201 should be in its own colulmn and APT 108 in its own and #14 7 in its own? If the latter, then you must come up with the rules of what keyword(s)...

Excel: if statement, 6 cells, numeric input

6 cells, numeric input, concatenate: Then we can do something like: =IF(COUNT(A1:F1) 3, Enter only 3 values ,AND(ISNUMBER(D1),ISNUMBER(E1),ISNUMBER(F1))*I1+AND(ISNUMBER(A1),ISNUMBER(B1),ISNUMBER(E1))*H1+AND(ISNUMBER(A1),ISNUMBER(B1),ISNUMBER(C1))*K1) This has the disadvantage of showing zero...

Excel: If statement, mathematical formula, c6

mathematical formula, c6, c9: Sure. The first part 0.5*(MAX($C$4:$C$6)+MIN($C$4:$C$6)) will take the biggest and the smallest numbers on the range C4:C6, add them together and multiply them by 0.5. If there are only two numbers on the range, those will be the two numbers considered....

Excel: if statement, second operator, menu tools

second operator, menu tools, copy paste: By design, that formula will give that error if the two numbers on the range C4:C6 are the same. If you don t want that to happen, you could include an additional check, something like: =IF(AND(COUNT(C4:C6)=2,COUNT(C7:C9)=1,MAX($C$4:$C$6) MIN($C$4:$C$6)),0.5*(ATAN(2*MAX($C$7:$C$9)/(MAX($C$4:$C$6)-MIN($C$4:$C$6)))*(180/PI())),...

Excel: If statement with multiple columns, selection criterion, absolute reference

selection criterion, absolute reference, double quotes: Dierdre, If you are using Excel 2007, you can use the AVERAGEIFS() function to do this! Read the function reference about this function and you will see how to apply it. It usually helps to write the selection criterion in a separate cell and make...

Excel: If statement refering another worksheet, cell font, sheet tab

cell font, sheet tab, address value: Raquel, I think a macro should work. You would right click on the sheet tab of the sheet where the change would be made and the font would be changed and select view code. then put in code like this: Private Sub Worksheet_Change(ByVal Target As Range)...

Excel: sumif with a cell used as criteria, row sums, circular reference

row sums, circular reference, record selection: Mark, I think what I would do here is create a column which sums all the rows, and then use a multiple-criteria record selector to include only the row sums for records which match the criteria. So add a column and sum each row in it. You can...

Excel: Time additions & Time Formatting, time additions, smoke room

time additions, smoke room, blank cell: Send me a sample wb to bobumlas@yahoo.com, use subject of AllExpertsQ A basic issue you re seeing is that you re putting in cells and then using them in arithmetic which will cause the #VALUE! errors. You should use 0 instead of and format the 0 s...

Excel: Time Conversion i Excel 2007, time conversion, time number

time conversion, time number, thanks for your help: Alan, I think the best way to do this is to take the TIME number apart, do a calculation, and put it back together again. You can extract the HOURS() and MINUTES() from a time with formulas of the same names ]HOURS() and MINUTES() ] Then...

Excel: Time converting Macro, hh mm ss, ss format

hh mm ss, ss format, time cells: Cyndee, You also are probably misusing the word MACRO. You need a FORMULA, not a MACRO. Look up the definition of MACRO so you learn to use the Excel terminology correctly; I almost deleted your question because I don t offer MACRO advice here! ...

Excel: Time taken from problem logged to resolution excluding non-office hours, resolution time, allexperts

resolution time, allexperts, a2: Given the complexity of the original solution and it wasn t set up to be flexible, I m afraid I can t modify it to ignore holidays. Maybe the real solution (flexible one) would involve a user-defined function and VBA, but I m currently quite busy and would...

Excel: Tracking Metrics, supplier names, time supplier

supplier names, time supplier, thanks in advance: Jon, Of course there is a way to do this! Its not exactly the way you ve guessed, though. All you have to do is add another column that contains the same logical equation you used to turn the cell red. This will show TRUE or FALSE. if you...

Excel: Transfering data between worksheets, cell a1, transfering data

cell a1, transfering data, sheet1: Brian You need to create links between the sheets to transfer the data. Here is what a link formula within the same workbook looks like. =Sheet1!A1 This formula may be in sheet 2 or any other sheet, and in any cell on that sheet. What it does...

Excel: Transfering file from Mainframe to PC automatically in excel format, anitha, day job

anitha, day job, mainframe: SOUNDS like it is more of a mainframe query - what program do you use to access the mainframe (in my day job we have used Dynacom elite, and now us IBM Pcoms) as this might form the basis of a useful answer - also, how would you get the file from the mainframe...

Excel: Transposing single column of data into rows, vertical data, column c

vertical data, column c, ogilvy: Stacy, Assumes the vertical data you show starts in A1 of sheet Raw then in A1 of the new sheet: =OFFSET(Raw!$A$1,(ROW()-1)*3+COLUMN()-1,0) then drag filled to C1 then select A1:C1 and drag fill down worked fine for me. In the first...

Excel: Trend line Equation, linear graph, true equation

linear graph, true equation, trend line: You can get the true equation by using formulas - I ve noticed that the dates seem to throw off the coefficients as well - send me your email address and I can show you how to create the coefficients using formulas. send to bobumlas@yahoo.com use subject...

Excel: Troubleshooting a Macro, cstr, paste move

cstr, paste move, macro code: This is because you are deleting the row and thus everything moves up BEFORE you then need to look at the next row because you are deleting the entire row and not just the columns of data where the data is. This gets the next row that you WOULD have looked...

Excel: Trying to Calculate Date of Retirement, staff names, cell reference

staff names, cell reference, dates of birth: Peju, I assume you know their birthdays, right? Otherwise it s impossible. So here s what you do. Take the YEAR(birthday), MONTH(birthday) and DAY(birthday). Then add 60 to the year. Then create the new date using DATE(). If you are clever,...

Excel: Trying to do inventory using Excel, poss. use Vlookup, paste values, tube name

paste values, tube name, inventory stock: I think a SUMIF function would do what you want - this would SUM entries in one column where another column is equal to a given value - however, this wouldn t handle the delete and not recalculate part of the problem - for which you would need to use some...

Excel: tab and copying formulas, sheet names, column numbers

sheet names, column numbers, column number: Rodney, One really good way to do this is to create the addresses from which you want to pull the information, and then embed the addresses inside the INDIRECT function. I do this all the time! All you need is a list of sheet names, and while...

Excel: If table..., forst, spreadsheet

forst, spreadsheet, ef: Sorry, I don t follow your example. What ROW are you referring to in your example? In row 2, I see a 1 in col a but don t see a 1 in col d. ...in the example below 1 is seen in A then offset in d... And what is forst? ...additional table...

Excel: One text/ number string, excel, concatenate

excel, concatenate: One potential workaround that I normally use for this is the following: 1. Enter the numbers one by one on a column. For example, with 20 number fill up the range A1:A20 2. In cell B1, insert this formula: =A1& , 3. In cell B2, insert this formula: =B1&A2&...

Excel: time compare vba, close match, time values

close match, time values, a5: I don t understand. 10:04:10 is 22 minutes from 10:26:29 and 10:20:44 is 2 minutes. I don t get how you can calculate the first value. What is the real criteria. Occuring first? What does that mean? What id the time in B5 were 8:08:22 PM -- that would also...

Excel: time formula, emergency room visits, running into trouble

emergency room visits, running into trouble, quality indicator: Jeanne, Assume these are just times and not date and time in the same cell and that visits do not exceed 24 total hours in duration. assume intime is in A1 outtime is in B1 =mod(B1-A1,1) Format the cell with the formula as Time. Works for...

Excel: User Form Collating Data, option boxes, combo boxes

option boxes, combo boxes, confirmation letter: You can definitely compile all this into a textbox on the user form. You may need to sue Userform1.Repaint to show it, but that d not an issue. You can also use multiple pages, and by the looks of your embedded example, it looks like you already know how to...

Excel: Using Data, Subtotals in VBA doesn't work, range selection, spreadsheet

range selection, spreadsheet, array: Matt, try selecting just one cell - Excel will try to figure out what to include. I suspect when the first subtotal is put in place, the selection then doesn t include one of the rows because rows have been added - I can t say why, but that is what is sounds...

Excel: Using DateSerial in my script, text cells, r2 value

text cells, r2 value, dateserial: Lee, I don t like to be one of several people working on the answer to the same question - but that doesn t appear to be the case here - if you don t get an answer, then there is certainly no problem with asking someone else. I am at a disadvantage...

Excel: Using RANK with specific ID, ogilvy, b1

ogilvy, b1, many thanks: Michael, If you have values in B1:B10000 and ID s in A1:A10000 then to you can use in C1 =SUMPRODUCT(--($A$1:$A$10000=A1),--($B$1:$B$10000 B1))+1 or =SUMPRODUCT(--($A$1:$A$10000=A1),--($B$1:$B$10000 B1))+1 Depending on which way you want to...

Excel: Using VBA To Hide A Checkbox, excel spreadsheets, stupid mistake

excel spreadsheets, stupid mistake, cbox: Jack, my code had a typo in it. When I was adding value to the end of CBOX in two places, I added it to the end of the wrong one set cbox.Value = activesheet.Checkboxes(s) if cbox = xlon then should be set cbox = activesheet.Checkboxes(s) if...

Excel: urgent help needed- waiting online! Excel repeats error message, alice first, f11 key

alice first, f11 key, ogilvy: Alice, first I assume that excel isn t locked up and you can still click in the sheet if your release the error message. If I got that message it would tell me that I need to put a data table on the worksheets, (put data in cells in the worksheet),...

Excel: using excel to calculate YTD ! ?, spreadsheet, tony tony

spreadsheet, tony tony: Tony, The Y-T-D is an accumulating total. It is the total from each day added together. The Y-T-D for the first day is equal to the total of that day. So you put in an equation which copies the total for Day One into the Y-T-D column. All...

Excel: using excel as a data match program, negative angles, angle values

negative angles, angle values, program question: Nolan, Many ways. One way is to go to column F and in F2 put in the formula =if(Countif(C2:E2, Match ) 0, show , no show ) then drag fill that down column F Put Header1 in F1. Now you can do an advanced filter on all the data using column...

Excel: VALUEIF, destination cell, search worksheet

destination cell, search worksheet, model numbers: gary, On sheet2, assume the first model number is in G2 in H2 put in the formula =if(G2= , ,vlookup($G2,Sheet1!$A:$B,2,False)) then drag fill down the column next to your data in column G if the formula ever returns #N/A, it means a match for...

Excel: VALUEIF, destination cell, search worksheet

destination cell, search worksheet, leftmost column: Gary, In order to be able to do this, there can be only ONE INSTANCE of each model number on sheet 1. If that s your situation, use VLOOKUP to retrieve the corresponding data for each model number. Here s a brief tutorial on VLOOKUP, and I suggest...

Excel: Values determined by other cells., two different things, data validation

two different things, data validation, input cell: Krista, The problem you have is that you are trying to make one poor cell do two different things. In one case you want it to be an input cell where you can make an entry, Pending , Canceled , Received , and in the other case you want THE SAME CELL...

Excel: Variable Function Length?, vlookup function, future proof

vlookup function, future proof, variable function: You can use a macro to fill a formula, or to enter it in the event of data entry, but personally I tend to prefer to future proof my sheets by putting in the formulas ready for use, but with IF statements to stop them being executed if no data present...

Excel: Variables and Equations in Excel, mc 14, column c

mc 14, column c, ogilvy: Jon, In D2 =IF(OR(B2= ,C2= ), ,B2-IF(C2= MC ,14,IF(C2= TR ,21,IF(C2= SB ,28,0)))) format the cell with the formula (D2) with a Date format. then Select D2 and drag fill down column D for as far as you need the formula. (or copy and paste) ...

Excel: VBA, control toolbox, seperate files

control toolbox, seperate files, commandbutton: Alex, run this macro against the workbook/worksheet that is giving you a problem. Sub ABC() Dim btn as Button for each btn in ActiveSheet.buttons msgbox btn.Name & calls this macro: & vbNewline _ bnt.OnAction Next End Sub If...

Excel: VBA, j30, blank cell

j30, blank cell, vba code: Tony, do you mean when all are blank: =If(And(J30= ,C30= ,A30= ), ,IF(J30 =10,SUM(A30*if(C30= ,1,C30)*J30),SUM(A30*J30))) But since we adjusted the formula so C30 could be blank, then perhaps just check J and A =If(And(J30= ,A30= ), ,IF(J30...

Excel: VBA, 6 digits, colorindex

6 digits, colorindex, 4 digits: Matthew, Assume the barcodes are in column A of each sheet here is some untested pseudo code to get you started. Dim r1 as Range, r2 as Range, r3 as Range Dim cell as Range with worksheets( Master ) set r1 = .range(.cells(2,1),.cells(rows.count,1).end(xlup)...

Excel: VBA, inventory checklist, worksheet functions

inventory checklist, worksheet functions, vba code: The great thing about Excel VBA is that you can record macros, so you can see the syntax for what you are trying to do - I THINK this is a countif situation - you are counting any instances of the barcode in the other location, if the value returned is zero...

Excel: VBA adjustment, unintended consequence, header row

unintended consequence, header row, vba code: Kevin, Here is my guess. Test it on a copy of your data: From what I can see of your headers, Status is in column 5 (column E) Date Recorded is in column 18 (column R) Program Start Date is in column 20 (column T) If that isn t the case,...

Excel: VBA Code: Look for values - copy/paste, target address, empty template

target address, empty template, template sheet: Chris, Assume that in the sheet with the empty template, the template name will be written in cell A1. Assume that the sheet with all 20 templates is named Sheet2. In the empty template sheet, right click on the sheet tab and paste in this code: ...

Excel: VBA code, combobox, combo box

combobox, combo box, ogilvy: tony, I have only seen that happend when the combobox is an activeX combobox on the sheet and it is linked to cells in the worksheet in the properties. I assume that is the case - you are linked to the worksheet to get the list of contacts (source for...

Excel: VBA code to create hyperlinks to sheets, cell b3, sheet names

cell b3, sheet names, dear tom: Hisham This worked for me assuming the sheet where the sheet names are listed is the first sheet in the tab order and the first sheet name is in cell B3 as you state. Make a copy of your workbook and test it on the copy Sub AddHyperlinks() With...

Excel: VBA define dynamic range names, kol kol, range names

kol kol, range names, valid values: Nino, The code worked fine for me. I had valid values that could be used for defined names in cells A3:I3 of Sheet1 and I had more values in those columns. You need to look at the message in the runtime error and see what it says. You could have...

Excel: VBA excel code help, max match, vba excel

max match, vba excel, excel code: as an example =Invester( Fred , sheet1!A1:A1000, 3) would check for all instances of Fred in sheet1 range a1:a1000 and if found return the information against that record in column D - which is 3 across from the data found. I included this parameter...

Excel: VBA Help, sh1, d17

sh1, d17, sname: Taha, Sub ABC() Dim sName As String, sh As Worksheet Dim bk As Workbook, sh1 As Worksheet Dim rw As Long, col As Long, cel As Range sName = C:Chq Pick upReporting.xls Set sh = ThisWorkbook.Worksheets( Cheque Pick up Form ) On Error Resume Next...

Excel: VBA match function, column c, assu

column c, assu, occurances: John, In Excel VBA help, for the findNext command, they provide this sample code: With Worksheets(1).Range( a1:a500 ) Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value...

Excel: VBA Projects, internet assistant, analysis toolpak

internet assistant, analysis toolpak, xla: Garry, atpvbaen.xla is the Analysis Toolpak - VBA funcres.xla is the Analysis Toolpak HTML.xla is as it says, the Internet_Assistant Addin. You can unload addins by going to Tools= Addins and unselecting them. That should remove them from the VBA...

Excel: VBA - Pulling From Excel (For Word), mark selection, core projects

mark selection, core projects, getobject: Richard Even though you are using GetObject, I will assume you have a reference to Excel since you don t get an error on the Excel.Workbook line. I don t program in Word, so I will assume the argument to typetext is a string. Sub AutoOpen() Dim...

Excel: VBA problem, target value, questioners

target value, questioners, spreadsheet: Private Sub Worksheet_Change(ByVal Target As Range) Dim offsetvar offsetvar = Sheets( Sheet2 ).Cells.SpecialCells(xlCellTypeLastCell).Row Application.EnableEvents = False If IsDate(Target.Value) Then Target.EntireRow.Copy Destination:=Sheets( Sheet2...

Excel: VBBA / Macro to create last edit date in row, good answer, deletions

good answer, deletions, target: Mark I am definatly not a VBA expert--I know it can be done, and have done it, but I borrowed someone elses code. I suggest you reask the question of another allexperts person and I am sure you can get a good answer. Check the resumes to see if they...

Excel: Visual Basic Loop "Counters", counter application, do loop

counter application, do loop, start at zero: Counter = Application.Countif(Range( yourrange , Network ) or if the result already exists in the worksheet in cell E3, for example, Counter = Range( E3 ).Value In a do-loop: For i = 1 to Range( E3 ).Value not sure if I answered your question.....

Excel: VLOOKUP Problem, vlookup formula, vlookup function

vlookup formula, vlookup function, b100: You can t use VLOOKUP for multiple lookup cells -- if what you want to look up is a combination of columns A & B and the result is in column E, for example, then you need INDEX & MATCH. If the cells you want to look up is in G1&G2: =INDEX(E1:E100,MATCH(G1&G2,A1:A100&B1:B100,0))...

Excel: VLOOKUP with a reference to a tab name, sheet tab, ogilvy

sheet tab, ogilvy, b1: Assume the tab names are in A1:A5 A1: Sheet2 A2: sheet3 A3: sheet4 A4: sheet5 A5: Sheet6 Assume the value to be looked up is in C1 in B1 =vlookup($C$1,Indirect(A1& !A1:F100 ),5,False) then drag fill down the column. if the sheet/tab...

Excel: VLookup, column spreadsheet, data validation

column spreadsheet, data validation, employee names: Kay, It is unlikely that your second formula (the one on the new sheet)returned the correct result, because you have the column index number set to ONE, (incorrectly). The col-index-num should be 2. A col-index-num = 1 will return the same name as...

Excel: VLookup (Excel 2000), price sheets, dear bill

price sheets, dear bill, product sheets: Gretchen, Is it your goal to ELIMINATE the four separate price lists and replace them with a new, consolidated price list? If so, VLOOKUP is not what you want. If you are trying to look something up on one of four different price lists, and they...

Excel: Vlookup, vlookup function in excel, lokos

vlookup function in excel, lokos, customer number: VLOOKUP looks up ONE value. Lokos like you re trying to look up 2 values. You need something like this -- assumes cust# in A, Sales# in B. In Sheet3, select the same # of rows as you re looking at in Sheets 1 & 2, (for this example I used 15); enter this...

Excel: Vlookup, berthold technologies, b4 4

berthold technologies, b4 4, vlookup function: Hi Sandeep How are you? I think that if you write on the table just Berthold, the vlookup function looks for all the strings that start by Berthold. E.g. Suppose the table is on $G$2:$H$4 1. Type Berthold in A4 2. Type Berthold Technologies in...

Excel: Vlookup, cell b10, array value

cell b10, array value, vlookup formula: Brian, Let s say you have that reference typed into cell B10. What you are probably doing is =VLOOKUP(B9,B10,N,0) which doesn t work. Instead try =VLOOKUPB9,INDIRECT(B10),N,0) I tested this and it works. Bil...

Excel: Vlookup and Cell Reference, excel, vba

excel, vba: I am not sure on what you want to achieve here, but my suggestion would be to make a loop on the values of vardata, and then craft the formula as a string with something like: ActiveCell.Formula = _ =VLOOKUP(a2, [book10] & var & !B$2:I$229,8,0) ...

Excel: Vlookup within a range of dates, text phrases, conditional formatting

text phrases, conditional formatting, sumproduct: Patrick, Hopefully your database contains actual YEARS and DATES rather than text phrases like Jan 1 to Dec 31 or from 1950 to 2009 , because if it doesn t, you re almost dead in the water. I don t know what the MIN(IF...) & MAX(IF...) formulas...

Excel: Vlookup table arrays, excel, indirect

excel, indirect, vlookup: You can use the INDIRECT function for that. For example: =VLOOKUP(A2,INDIRECT(A1 & !A1:B100 ),2,FALSE) The second parameter is the one that will change depending on the worksheet name on A1. Change the other ranges as appropriate. Hope this helps,...

Excel: vba, target range, g13

target range, g13, e13: Angela, If you want different conditions based on which range of cells, then you would just reproduce the code Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range( C2:C13 )) is Nothing Then ...

Excel: vba code help, vba code, time values

vba code, time values, question sir: in VBA code this: Function Closetime(Begin As Range, possibles As Range) As Double diff = 1000000000# For Each thing In possibles If Abs(Begin - thing) diff Then diff = Abs(Begin - thing) hold = thing ...

Excel: last visit, array formula, curly brackets

array formula, curly brackets, layout sheet: Anthony, Looks like Stuart used a macro for this, but you can do it with worksheet functions which is probably easier. Assume in check-in, ID: column A Date & time column B Assume entries are chronological by Date so that the last visit will...

Excel: Want DGet to return more than on result, array formulas, dcount

array formulas, dcount, filter works: Donald, It isn t a function, but advanced filter works with the same criteria as DGET or the other D functions and you can set it to copy the data to another location - so you could get a specific subset of your rows in another location. In xl2003 and...

Excel: Way of wiritng distinct values from one sheet to another..., address phone number, distinct rows

address phone number, distinct rows, countif: Paul, I have tested the code and it worked fine for me. I can only get that error if I put none numeric data in the cells that are to hold numbers. If you changed E to N, what did you do with r2.Offset(0,4)? I don t see a change there. r2.offset(0,4)...

Excel: Web page Filter, INTERACTIVITY EXCEL 2007

INTERACTIVITY EXCEL 2007: Marion: Unfortunately, I don t have a good answer for you. This feature has been removed in Excel 2007. For additional information please refer to this website: http://blogs.msdn.com/excel/archive/2006/08/24/718786.aspx Several paragraphs down you...

Excel: It Worked! Set to Manual Calc Before Sorting Data, xlstart directory, strange behavior

xlstart directory, strange behavior, addin: Naomi, Glad it worked. Don t forget to turn calculation back to automatic if you need it. Also, if you close excel and open it back up, calculation probably will already be set back to automatic (so if you sort immediately thinking it is off, it could...

Excel: Xcell, excel, format

excel, format: By default cells are locked, but you can be sure of that on the menu Format- Cell- Protection tab. The protection doesn t get in place until you protect the worksheet using the menu Tools- Protection- Protect Sheet, and on that menu you can select to lock...

Excel: XL formula, cell b1, math rules

cell b1, math rules, excel formula: HI Ian, That is really simple in Excel. In cell A1, enter your number. In cell B1 (or any other cell), enter this formula: =A1-250*1.61+520 Your logic is wrong however. According to math rules (multiplication comes before addition and substraction)...

Excel: xl in office 2000, cell b10, text phrase

cell b10, text phrase, ed number: Milton, So you need to devise a test for a POSITIVE INTEGER. And then IF the test passes (is TRUE) you ll copy the positive integer to another cell. Hmmmm... how could this be done? Lets say your number is in cell B10 To test for POSITIVE,...

Excel: AA TO ZZ, rw 1, line1

rw 1, line1, ogilvy: ZEAD MK Assuming Line1 means row 1 and writing to column A: Sub ABC() Dim i As Long, j As Long Dim s As String Dim rw as Long rw = 1 For i = 1 To 26 For j = 1 To 26 cells(rw,1) = Chr(i + 64) & Chr(j + 64) rw = rw + 1 Next Next End Sub...

Excel: ABOU EXCEL FORMULA, analysis toolpak, exact period

analysis toolpak, exact period, valid answer: I tend to use the YEARFRAC function for date handling - this is in the analysis toolpak (tools, add-ins, analysis toolpak) and calculates the exact period of time between two dates =int(yearfrac(now(),datevalue( 01/01/1999 ))) would give you a valid...

Excel: Absenteeism Formula, free microsoft excel, dividing by 6

free microsoft excel, dividing by 6, absenteeism at work: I m not quite sure what you re trying to do, but if I read this correctly, you should be able to use an IF function to calculate the percentage correctly. You can use the IF function to have Excel place a value in a cell based on one or more conditions...

Excel: Accumulating numbers, excel, sum

excel, sum: The exact formula will depend on your layout, but this technique may be useful for you. If you want to accumulate in a column, for example, you could start with a formula like: =SUM($A$1:$A1) When you copy the formula down, it will change only the last...

Excel: Adding continuous entries, target range, d8

target range, d8, different times: you could try this: Private Sub Worksheet_Change(ByVal Target As Range) If Target = Range( d8 ) Then Range( E8 ).Value = Range( E8 ).Value + Range( d8 ).Value Else Exit Sub End If End Sub where a value is entered into D8 it is added to E8 and the...

Excel: Adding a formula for quarters, column c, aidan

column c, aidan, fourth quarter: My email is aidan.heritage@virgin.net - if you d like to send me a sample of what you are doing and how you want it to look I can try to show you via the spreadsheet what I m suggesting (also if I ve misunderstood what the problem is this will help me resolve...

Excel: Adding previous values to new sheet, address value, sheet1

address value, sheet1, rectangular area: Adam, As you said, you would have to run a macro when you wanted to add the data sub AddData() Dim cell as Range for each cell in worksheets( sheet1 ).range( A1:C3,D4:F6 ) with worksheets( sheet2 ) .Range(cell.Address).value = .Range(cell.Address).Value...

Excel: Allocating various costs across several months, solution work, data inputs

solution work, data inputs, excel 2002: Alex, Although I can make Excel do anything , sometimes it takes a while to think of the way to make it do it! Do you still need an answer to this? I haven t devised one quite yet, but would like your question to be resubmittted if you still...

Excel: Allocating various costs across several months, data inputs, excel 2002

data inputs, excel 2002, calendar days: Alex, I have a solution in mind which I am sure will work. Its slightly difficult to explain and you might find it complex. I have no idea what your Excel skill level is. I see that you sent me your workbook, with the hope that I might create...

Excel: Alphabatizing, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: No, but if you SELECT columns A and B and then sort them, they ll stay together. Or… use a DATABASE like MS Access for this. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute...

Excel: Append to excel, layout location, voice of experience

layout location, voice of experience, macros: Sorry, as politely as possible, I don t write macros for free for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone...

Excel: Assign keyboard shortcut?, keyboard shortcuts, keyboard shortcut

keyboard shortcuts, keyboard shortcut, macro commands: I assume this means to macro commands? If so, you need to see the developer ribbon, which you can get from the office button, the Excel options, it s hopefully then on the most popular bit - once you have this, find the macros button, click it and use...

Excel: Assigning values to properties in a drop down menu., cell c5, carbon calculator

cell c5, carbon calculator, modes of transport: Fee You can use a formula like this-- =IF(A5= bus ,+B5*0.15,+IF(A5= train ,B5*0.1,+IF(A5= taxi ,+B5*0.27,+IF(A5= car ,+B5*0.75,+IF(A5= van ,+B5*0.88))))) As you can see the car and van multiples need to be changed but otherwise the formula can be...

Excel: Auto fill, partial values, consecutive rows

partial values, consecutive rows, value exchange: Vinay, Is this a living document or a one-shot deal? The answer entirely depends on this. By living document , I mean that sheet 1 is a database that you constantly add information to. As you add the information, you want the results in sheet...

Excel: Auto Populate Custom Text Cell, b1 b2, simple solution

b1 b2, simple solution, increment: Nothing comes to mind that will let you use Autofill. Therefore, try this: With 010 in A1, then type 1 in B1 and 2 in B2. Select B1:B2 and drag down to create a series of numbers that start with 1 and increment by 1. You can format column B to a custom...

Excel: Auto populate cells from a master sheet, master database, pivot table

master database, pivot table, legitimate reason: Randy, Either the cell where the formula is is formatted as TEXT, or there is an invisible single quote in front of the equal sign. Try =ISTEXT(formula) on the cell, where (formula) is a cell reference to the cell where the formula is (like D45)....

Excel: Auto populating inverted sheet 1 to sheet 2, chess board, a1

chess board, a1, chess: Nearly everything is possible - some things just take time! This one is fairly simple - I m assuming we are talking an eight by eight grid here - and I ve ASSUMED that it starts in A1 on sheet 1 - in A1 on sheet 2 put this formula:- =OFFSET(INDIRECT( Sheet1!a...

Excel: Auto-populating sub-sets of data from a Master report, business intelligence application, keyboard menu

business intelligence application, keyboard menu, exact record: Peter, There are two ways I can think of to do this. One way uses manual (keyboard, menu driven commands) and is based on the ADVANCED FILTER, and the other is incredibly complex and uses equations based on OFFSET, MATCH, INDEX, and a suite of other...

Excel: Autofilter, custom filter, serial numbers

custom filter, serial numbers, aidan: If the fields concerned are dates, then these are serial numbers effectively - 2009 is equates to 1st July 1905 using serial numbers, so not overly suprising you didn t get any matches - instead, try custom filter and select greater than 1/1/2009 AND less...

Excel: Automate solver application, target percentage, code column

target percentage, code column, material code: ...I was wondering if it is possible to automate the solver application for Excel using VBA.... Yes it is possible. ...Is it possible to create a mask so the solver will understand that the values at that column are the minimum, in the other the maximum...

Excel: Automated timestamp saving problem, allexperts, timestamp

allexperts, timestamp, time stamp: As I didn t write the original answer...! IF you save a file, then that file MUST have the data you saved on it - so if it hasn t, it wasn t saved - or the data was overwritten. For the macro to run, macros must be enabled - so it MAY be that the issue is...

Excel: Automatically Hiding Rows, target address, cell b1

target address, cell b1, sheet tab: Shane, You could do it with the Sheet Change Event using a macro. Assume the cell with the dropdown is in cell B1 You haven t said what to do when the dropdown is changed from N/A to something else, so I will assume you want to unhide the rows. ...

Excel: Automatically Hiding Rows, having a bad day, shane

having a bad day, shane, assumption: OK. Again, all I will say is that you asked is it possible to hide rows 3 to 10? And I answered yes, the answer to your question. How was I to know that you might have wanted to know something different. I can t read your mind and can only base my...

Excel: Automatically Hiding Rows, conditional formatting, colour text

conditional formatting, colour text, aidan: I would be inclined to use conditional formatting to make the questions invisible (i.e. same colour text as background) but you could use VBA to achieve it using the change event of the worksheet - if VBA is acceptable I m happy to do something for you along...

Excel: Automatically Hiding Rows, target cell, conditional formatting

target cell, conditional formatting, ogilvy: Shane, yes, its quite possible. But it can only be done with a macro. Since I don t offer macro advice on thsi site, you ll have to ask Tom Ogilvy for help. You COULD use conditional formatting to make the contents of those rows disappear (use...

Excel: Automatically opening my personal.xlsm, macro execution, c program

macro execution, c program, microsoft office: I m confused - the CORRECT file name for the personal macro file is personal.xlsB not M? If the file has THIS file name, it opens automatically by default - also, the start area that you have specified is an automatic start area anyway, so shouldn t NEED...

Excel: Automatically removal of a specific character in excel, numeric representation, numeric characters

numeric representation, numeric characters, alpha numeric: The sort will be an issue in that by including non numeric characters, the values are treated as text, so sorted alphabetically - hence the sort you are seeing. a macro to do the job dim Cl for each cl in range( A:A ) if len(cl.value) 0 then cl.value=worksheetfunction.substitute(cl.value,...

Excel: AVERAGEIF, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: I don t quite understand. You want to average up ALL of the scores where revisit is zero, or you want an average for EACH name with a zero? Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE...

Excel: Average days, spans, pairs

spans, pairs, avg: Carol, I m certain that I don t understand your question. You said: Right now I find the difference between two days to determine the number of days... You probably MEANT that you find the difference between two DATES, right? But which...

Excel: activities completion dates follow up, excel, format

excel, format: I suppose that the completion status is on a separate column, and it is entered manually. If it has to be automatic and the only criteria is the date, you could use a formula like: =IF(TODAY() B1, Completed , Not completed ) With this, you can highlight...

Excel: How do I add hours to a particular date?, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, excel tutorials: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: adding start and stop times together, excel, times

excel, times: I am not sure on what formulas you are using, but with time values you can use a direct subtraction. For example: =(B2-A2)+(D2-C2)+(F2-E2) I am assuming that the layout of the data is each of those times on a column starting on A. If this doesn t work...

Excel: averageif formula, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: You can t do that directly with AVERAGEIF. What you would have to do is make a second column next to that one with an IF function to show ONLY those items that meet your criteria, then average THOSE. Let me know if you have any other questions or comments....

Excel: Basic Macro Question, down arrow, enought

down arrow, enought, b10: I would need to see your code to see how you are approaching this. What you are wanting to do can be done but my suggestion depends on the approach your code is taking and how you are running the code. For example, do you always or do you think you always...

Excel: Built-in Menu w/ VBA, input cells, jkp

input cells, jkp, custom menu: Hi David, What is your problem with the menu? As to protection: all depends of course on what you want your user to be able to do and what not. All cells are locked by default, but that only comes in action when you protect the worksheet in question....

Excel: To bypass UpdateLink Dialogue, true application, l29

true application, l29, l11: Untested, but should work: Sub Combine() Dim Area As Range, Files As Variant Files = Application.GetOpenFilename(MultiSelect:=True) Application.DisplayAlerts = False For i = LBound(Files) To UBound(Files) Workbooks.Open Files(i), False NextRow...

Excel: Calculating average based on condition, empty cells, countif

empty cells, countif, question thanks: Magnus, I would change CountIF to Sumif with the sum portion from column B =IF(SumIF(A1:A6001; Department A ;B1:B6001) 0;AVERAGE(IF(A1:A6001= Department A ;IF(B1:B6001 0;B1:B6001)));0) Again, entered with Ctrl+shift+enter -- Regards, Tom Ogilvy...

Excel: Calculation In Excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, basic excel: This is just basic Excel arithmetic: =A1+B1 See my Excel 101 Tutorial. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: CELL CHANGING, vba procedure, transpose function

vba procedure, transpose function, vba functions: Hi Alicia How are you? First question: you can use the transpose function: 1. select 4 cells in a column 2. Type =transpose(A1:D1) 3. Hold down the keys ctrl and shipt and press Enter This transposes the 1st row. For the other rows you have to...

Excel: Cell formula automatically updating, should not, h1, discovery

h1, discovery, f1: Don, When I first read your question, I thought that you must be doing something wrong, this behavior just cannot be! But I duplicated your setup and Excel behaves the same way for me! I have absolutely no idea why and I have never seen such...

Excel: Cell Reference, seperate cells, vba code

seperate cells, vba code, cell reference: Cooper, are you familiar with VBA code? The only way you will be able to do this is with some code. I will need you to be a bit more specific with cell refs etc to be able to make an example but then you will need to know how to use the code etc. Use the...

Excel: Cell Value Comparison, b1 and b2, cell a2

b1 and b2, cell a2, conditional formatting: Sirajuddin I may be misreading your question or the way your data is set up, but it looks to me like you can use conditional formatting to determine if you have matching cells. The conditional formatting feature is at toolbar/format/conditional formatting/...

Excel: Cell Value Comparison, excel, conditional format

excel, conditional format: Due to time constrains I am not doing custom code on this site any more, so I cannot help you with a macro. I can offer other alternative that is the conditional format. For this, select the whole range with the data, but starting on cell A2 (one way...

Excel: Cell Value Comparison, b1 and b2, conditional formatting

b1 and b2, conditional formatting, cell values: Sirajuddin, Good news. This will be a much easier project than you anticipate. It can be done with conditional formatting instead of a macro. First, do you want to only color the cell if the duplicate data are in adjacent rows, or if the duplicate data...

Excel: Cell Value Comparison, b1 and b2, conditional formatting

b1 and b2, conditional formatting, cell values: I don t use Excel 2007, because I think it s evil. I believe you should be able to get to the same Conditional Formatting dialog box as is available in Excel 2003. If this is possible, then you can accomplish this (making any cell in row 2 have a red pattern,...

Excel: Cell Value Comparison, b1 and b2, cell b5

b1 and b2, cell b5, cell values: Sirajuddin, This is so easy to do, you ll save tons of time! All you have to do is apply a simple Conditional Formatting (CF)rule. You can apply this simple rule to the entire swath of data all at once and it will find every instance of duplicate...

Excel: Cell Value Comparison, b1 and b2, cell values

b1 and b2, cell values, sirajuddin: Shouldn t need a macro, as you can use conditional formatting - use the home ribbon, then the conditonal formatting option - enter use a formula to determine which cells to format and (in A2) put =A2=A1 and set the format of the fill to red Then...

Excel: Cell Value Comparison, b1 and b2, conditional formatting

b1 and b2, conditional formatting, selection areas: Sirajuddin, If it will only be two rows selected: Sub CompareCells() Dim r As Range, cell As Range Dim r1 As Range, cell1 As Range clear all colors from selection Selection.Interior.ColorIndex = xlNone loop through cells and compare Set...

Excel: Changeable Sums, partial sum, exact number

partial sum, exact number, numerical value: slightly confusing explanation, but it SOUNDS like you want a SUMIF formula - which is =SUMIF(RangeToTest,Test,OptionalRangeToSum) RangeToTest is the full range of cells that you are going to test against a criteria Test is the test = so if you are...

Excel: Changing Excel Start of Day time, cell reference, tv broadcast

cell reference, tv broadcast, new time: Mark, If you do reset the start time for a day, the sort order will be exactly the same as if you hadn t made the change... Excel will sort in increasing order of date & time, even if you don t offset the time by 5 hours. Won t it? But perhaps...

Excel: Changing excel layout, lising, copy paste

lising, copy paste, spreadsheet: Jack You can change the layout with some simple cut paste special etc. use the copy paste special transpose feature to have your dates,numbers, etc copy down in rows instead of copying across in columns. Also use some copy paste to repaeat the months...

Excel: Changing Sheet Reference, account names, reference question

account names, reference question, account numbers: Then I don t see why currentYearSheet would need to change. Is it based on the year? That is, if it s 2008 is it really 2008!A1:AO147? and for 2009 it s 2009!A1:AO147? And similar for CurrentYearPeriods (so each sheet has its own locally-defined names... ...

Excel: Check box idiosyncracies, control dialog, control toolbox

control dialog, control toolbox, toolbar options: There were (as far as I remember) two ways of inserting check boxes - one was the CONTROL toolbox but the other was the forms toolbox - however, as you are looking to tidy things up I d be inclined to delete what you have and replace with two same type boxes....

Excel: Check box idiosyncracies, control toolbox, control dialog

control toolbox, control dialog, format menu: Tim, It sounds like the NET 30 is a control from the control toolbox toolbar as you say. The Sales Tax is a checkbox from the Forms toolbar. So there are in fact two different types of controls. The Forms toolbar controls have been around the...

Excel: Clear contents of blank cells, spreadsheet output, blank cells

spreadsheet output, blank cells, tom dugan: I THINK you ll find that these blank cells aren t blank at all - they simply contain one (or more) space characters - from experience, a lot of people think that the LARGE key at the bottom of the keyboard is a delete key, but to excel a space isn t a blank....

Excel: Closing Excel 2000 workbook, excel 2000, project explorer

excel 2000, project explorer: Yes it s possible - the CLOSE event of the workbook would be the best place for this to happen - alt F11 to get into the VBA editor, use the project explorer on the left to find the workbook, and change from general to this workbook - then find the close item...

Excel: Closing a workbook in a macro, false application, myfolder

false application, myfolder, easy solution: Roger, Application.CutCopyMode = False ActiveWorkbook.Close SaveChanges:=False I would expect that to work, but if it doesn t, then Application.DisplayAlerts = False ActiveWorkbook.Close SaveChanges:=False Application.DisplayAlerts = True ...

Excel: Column header reference on Cell, excel, offset

excel, offset: You could use a formula like this: =OFFSET($B$4,0,MATCH(1,C10:H10,-1)) You can insert columns, and the formula will react if you just update the reference C10:H10. The formula doesn t have error control, so it will return an error if there is no number...

Excel: Column vector into matrix, column vectors, column vector

column vectors, column vector, manual operations: Aya, If the original data is static and not constantly changing, you could easily do this with a few manual operations. I d suggest SORTING on the column that contains the words. That will bring all the identical words together in one group. ...

Excel: Column width will not stay at 14.11, portfolio summary, column width

portfolio summary, column width, blank sheet: Very odd- I ve just set up a blank sheet, set it to concourse, and modified the width - all OK. I ASSUME the document isn t protected in any way? Is it possible to see the file - aidan.heritage@virgin.net will get me - as that MAY shed some light on the...

Excel: Combining statements in VBA, unc path, path server

unc path, path server, server share: There s quite a bit of code missing for me to be able to help. For example, where is fullpath defined? What s NO_ERROR, IBUFFER_SIZE, WNetGetConnection32, etc? If fullpath isn t defined, then this line: ii = lpszRemoteName & Right(fullpath,Len(fullpath)-...

Excel: Comments-font size, google search, appearance tab

google search, appearance tab, font type: From a quick GOOGLE search: You can change the font size of new comments by changing the settings in your Control Panel. (Instructions for Windows XP) Right-click on the desktop, and choose Properties On the Appearance tab, click Advanced. From...

Excel: Commision Calculation on Collections, taking into consideration, report doesn

taking into consideration, report doesn, excel 2000: Franz, It would seem that you have to keep a database of checks received, so you know what limits have been met when the next check comes in. The limits are the values at the three step points, $300, $1,700, and $2,000... although you said the NEXT...

Excel: Comparing array data to a list, sized arrays, array data

sized arrays, array data, size shape: Bill, You can make this happen exactly as your words describe it. There are several steps involved. For each step, I would make a SHADOW SHEET with an array whose size and shape EXACTLY matches the size & shape of the array on the second sheet. ...

Excel: Comparing Excel files, spreadsheet templates, sas programs

spreadsheet templates, sas programs, excel application: When the machine is locked the display isn t on, so that is PROBABLY the reason - I don t know what your process is trying to do - my CURRENT thought is to have it write to a CSV file, have excel running all the time in the background, but checking for the...

Excel: Comparing Totals in Excel, aidan, spreadsheet

aidan, spreadsheet, heritage: Not really clear what you want to do - are you talking about showing the difference between months? A simple x-y formula would do this - but if it needs to determine which month to use then it would be more complex. My email, should it help, is aidan.heritage@virgin.ne...

Excel: If Condition, button menu, macro recorder

button menu, macro recorder, time thanks: Parmesh, Record two simple macros that performs the keystrokes you use, and assign them to the control-t and control-d keys. The macro recorder will be clear and easy to use. if you are using Excel 2007, you need to enable the DEVELOPER ribbon in...

Excel: Conditional Formating, excel, conditional format

excel, conditional format: I suppose that the headers would change over time, as if not you could directly color the cells that fulfill both conditions. In any case, you can use Conditional Formatting for this. Select the whole range of data, starting on cell B2, go to menu Format-...

Excel: Conditional Formatting, excel, conditional formatting

excel, conditional formatting: Leprakawn: The simplest solution would be to add a helper column. In H1, type this formula and drag down: =C1-TODAY() Then format column H as a number. The result will be the number of days remaining to the due date. Next apply conditional formatting...

Excel: Conditional Formatting, conditional formatting, data validation

conditional formatting, data validation, vlookup formula: Graham, ------------------ Graham, You started your rating with Bill (my name is Tom ), so I assume you didn t use my suggestion, but used the suggestion provided by someone named Bill. Unfortunately, I don t think you can withdraw a rating, but In...

Excel: Conditional formatting, cell a2, conditional formatting

cell a2, conditional formatting, cell a1: Say for example that previous month figure is in cell A1, and current month is in A2, and you want the background color in A2 to be red if the current month exceeds the prior month by 10 or more. Select cell A2, and from main menu choose: Format, Conditional...

Excel: Conditional formatting w/dates, conditional formatting, current training

conditional formatting, current training, due date: Charles, Everyone else wants this also, so Excel has a function called CONDITIONAL FORMATTING that does exactly what you want. You MUST know about this already since you referred to an icon set which is only available in the Conditional Formatting...

Excel: Conditional rollup of data, excel, sum

excel, sum: The exact format would depend on the layout, but this can give you some ideas. For example, if the data will be on the range A1:A12, you could use a formula like: =SUM(OFFSET($A$1,COUNT($A$1:$A$12)-3,0,3,1)) This will add the last three numbers of the range,...

Excel: Conditional sum of visible cells in a filtered list, vector array, counta

vector array, counta, total function: Ashtosh, You don t understand, because that is NOT exactly what is happening! I took your spreadsheet and replaced your formula in cell R7 with this: =SUMPRODUCT(--($Q$23:$Q$2776=25),(R$23:R$2776)) and got exactly the same results...

Excel: Convert Text to Columns Wizard, default delimiter, xls

default delimiter, xls, wizard: You need to do it manually first time only for each time you start Excel. There s no way (I know of) to change the default delimiter. You can record the steps to do the text to columns and change the delimiter, then play back the macro (stored in personal.xls,...

Excel: Converting 4 digit Military to decimal, military time, decimal places

military time, decimal places: Dwight, Do you really have the time as 1630 instead of 16:30? What do you mean by 1630-0800? Do you want to subtract the times? Do you really have the 0800 as 0800 instead of 08:00? If you take one of your times, and format it as a...

Excel: Convinience of a drop-down calendar, data validation, drop down boxes

data validation, drop down boxes, excel function: Pat, A full-blown drop-down calendar is not a standard Excel function. The drop-down boxes provided by Data validation are set up to provide a linear list of items. Thus, you COULD create a list of all possible dates and scroll through them, picking...

Excel: Coping formulas, excel, replace

excel, replace: The way that I usually do this is with the menu Edit- Replace. You can make the copy of the sheet, go to the sheet(s) where you have the formulas related to the original sheet, go to the Edit- Replace menu, and fill the Find what and Replace with fields with...

Excel: To Copy Cell Ranges From Multiple Worksheets and PasteSpecial in One Sheet, t51, inspite

t51, inspite, adnan: Sub Combine() Dim Area As Range, Files As Variant Files = Application.GetOpenFilename(MultiSelect:=True) Application.DisplayAlerts = False For i = LBound(Files) To UBound(Files) Workbooks.Open Files(i) NextRow=Thisworkbook.Sheets(1).Range(...

Excel: Copy cells from multiple worksheets To Another Workbook, soure, cells

soure, cells: Sunil... You can simply right click on the cell you want to copy and choose copy and then got to the workbook where you want to paste, then right click and choose paste. If you want to link the workbooks, the format is... = [linked book.xls]sheetname...

Excel: Copying columns to multiple worksheets with a macro, pastes, ogilvy

pastes, ogilvy, formatting: Bill, Assume the columns to be copied are on the activesheet when you run the macro further assume that the columns are B:F (you can change it in the macro) Sub copycolumns() Dim r As Range, sh As Worksheet Set r = ActiveSheet.Range( B:F ) For...

Excel: Copying data from multiple worksheets to a master without blanks., basic principle, buyer name

basic principle, buyer name, address sales: The basic principle I m happy with, BUT your output is subtly different to the input - you have week 1 in row one on the output, but Tuesday on the input? The basic principle seems to be for each sheet in the workbook as long as it s not sheet 2 copy...

Excel: Copying Indirect Formula and changing references, auto increment, column references

auto increment, column references, column c: PJ, sorry PJ, you must have made a mistake in entering the formula or you don t understand it. when I originally posted it, I copied it out of a workbook where it returned the value in AllUsers!J1 - so the formula was tested and does work properly. It...

Excel: Copying Rows Based on Criteria, string s1, cell1

string s1, cell1, iloc: Michael, I revised the code in accordance with what you have told me (making a few assumptions about where A:C goes and F:L goes and what move it over means (looking in P:Z for inspect). the code is commented. it should run. I do demonstrate/comment...

Excel: Count cells in column A whose corresponding cell in column B contains criteria, excel, count

excel, count, sumproduct: You could use the SUMPRODUCT formula for this, something like: =SUMPRODUCT(--(A1:A50 ),--(C1:C50=1)) Write the criteria that you want to check on the C1:C50 part, and it will return the count of the cells that fulfill both that criteria and a not blank...

Excel: Count Formula, len function, correct character

len function, correct character, louise louise: Louise, Your formula looks fine, except that I can t see for myself what the defined names are. They all MUST be exactly the same size. A good thing to do is to open a new sheet and Paste the names table with F3. Then you can see for sure. Referring...

Excel: Count Multiple columns, array formula, carrier1

array formula, carrier1, counta: total number of closed =count(B:B) or Count(B1:B5000) counts all the numbers in column B and works with a whole column argument since it is not an array formula. Dates are stored as numbers if they are actual data. If they are being entered as strings/text,...

Excel: Counter in VBA, wage calculations, there some sort

wage calculations, there some sort, paste technique: I m not sure what would enable you to work out where to apply this but the use of the CELLS option instead of range would seem to be the way to go - Range( L6 ) is the same as cells(6,12) - where 6 is the row and 12 is the column - armed with this you could...

Excel: Counting cells within a month having some remarks, record selector, excel function

record selector, excel function, ntries: Michael, For the second part of your question, I want to find out how many Enquirers who made enquiries in January have become members. , you can use the more sophisticated form of SUMPRODUCT as a multi-criteria record selector, which can then sun any...

Excel: Creating a quick look Excel tracker, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: Excel has a feature called Conditional Formatting that you can use to change the format (color, font, bold, etc.) based on the value of the cell. See this FREE video tutorial on my web site for instructions on how to use Conditional Formatting. http://599cd.com/tips/excel/conditional-formatting/?key=AllExperts...

Excel: Creating VBA form to show data from datasheet & also allow user input, lastname firstname, input fields

lastname firstname, input fields, cell references: Hi Brian, That s big question and to give you a clue the last project I did like this took me over a day to complete and test. Because of this I wont be doing the work for you but I will start you off.. I presume you have already tried this and not...

Excel: Ctrl+C in Excel Vista, www google co uk, google co uk

www google co uk, google co uk, google search: office 2007 still has the same shortcuts - as I proved earlier today by answering a question for someone else - do you have any macros on the workbook that have that as a shortcut key? The only other option I ve found is the google search http://www.google.co.uk/search?sourceid=navclient&ie=UTF-8&rlz=1T4RNWN_enGB318GB318&q=vista+excel+ctrl+c...

Excel: Custom Formatting a Column Heading as "Profit/Loss" in a DataTable, datatable, profit loss

datatable, profit loss, apparent reason: Nicholas I can t tell exactly what the problem is, is it in the heading or is it in the numbers that are being calculated in the sheet, in either event I am guessing that it is in the custom formatting, but without being able to see how you have done...

Excel: How to calculate my time working, format cells, menu format

format cells, menu format, time type: Example: In cell A1 enter start time like 1/1/2009 7:45:00 AM In cell A2 enter end time like: 1/1/2009 5:15:00 PM Select A3. Choose from main menu Format, Cells, Number: Time, Type: 37:50:55, OK. Enter in A3 =A2-A1 A3 will display: 9:30:00...

Excel: If a cell becomes TRUE between 2 time variables, target price, time variables

target price, time variables, cell a1: You will need some kind of macro IF I understand what you need. The cell that contains the Now function needs someway to constantly update itself - it will not update itself until you enter something else in the sheet that will then cause all formulas to...

Excel: cell values, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, cell values: You ll need to INCREASE DECIMAL PLACES using the toolbar button. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: checkboxes :s ˛, range target, cell c2

range target, cell c2, cell a2: You might be better off with a List.. but I hate them.. so try this: Private Sub Worksheet_Change(ByVal Target As Range) If Cells(Target.Row, 1) = Then Dim Row_Range Row_Range = Target.Row ActiveSheet.OLEObjects.Add ClassType:= Forms.CheckBox.1...

Excel: checking sheets and changing names through vba, data validation, logical test

data validation, logical test, listboxes: lee, If I understand what you are asking, you want to know if a sheet with a given name already exits - is that correct Dim sh as Worksheet set sh = Nothing On Error Resume Next set sh = worksheets(sCandName) if sCandName already exitst, then...

Excel: Compare two columns and find the dups, pivot table, sumif

pivot table, sumif, left hand side: the formula will count all entries in the range A1:A4000 that match B1 exactly- but any leading or trailing spaces for example would cause a false reading - however, at face value, what you have looks fne. aidan.heritage@virgin.net will get me if an attachment...

Excel: combining multiple cells into single entity, b1 and b2, cell b5

b1 and b2, cell b5, information column: =IF(COUNTIF(A2:$A$7,A2)=1,combinetext(A2,$A$2:$A$7,1), ) would be the function I d use - with CombineText being a custom function Public Function CombineText(Match As Variant, CheckRange As Range, OffVar As Long) Dim cl For Each cl In CheckRange...

Excel: 'OR' command, free microsoft excel, city wildwood

free microsoft excel, city wildwood, wildwood nj: Personally, I would use a VLOOKUP statement for this with a list of cities and states. You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller...

Excel: concatenar formato, conditional formatting, spanish messages

conditional formatting, spanish messages, question thanks: I THINK having read your translation and checked with my Spanish speaking brother, that you are needing some conditional formatting to apply- it MAY be easier if you could email me an example file? My email is aidan.heritage@virgin.net - I can relay Spanish...

Excel: conditional formatting, excel spread sheet

excel spread sheet: Yes but you will need a macro to do this. And, to do what you say you want then that macro will ALWAYS and EVERY TIME highlight the row each and every time you use the file. I suspect that you would not want that. Therefore, you would need to have some...

Excel: conditional formatting, conditional formatting, odd rows

conditional formatting, odd rows, sheet names: Hi Mark, Seems Excel doesn t notice it needs to recalculate the conditional formatting formulas. Does it help to have an out-of-the-way cell on the same sheet point to the range modelperiod directly? That will tell Excel it at least needs to recalc that...

Excel: conditional formatting cells using multiple keywords, berry grape, c cells

berry grape, c cells, word apple: In your questions, please include only the information directly related to one specific question. In this case, I ll assume your actual question is: in a particular range, how do I shade only those cells that contain the word apple or pear etc? In range...

Excel: conditional formatting linked to another sheet, conditional formatting, target cell

conditional formatting, target cell, odd rows: Say you have a cell with range name modelPeriod. You want a cell to have a conditional format when modelPeriod has the value m . You can do so, whether or not the target cell is on the same sheet as modelPeriod. When setting up your conditional formatting,...

Excel: conditional formatting number of decimal places based on a table value, comex gold, futures contract

comex gold, futures contract, value question: Ah, I d misread that - I m not sure I can think of any other easy ways round this - the use of the TEXT function with a lookup for the format would be a way to get the display right, but THEN the values would be text not values - so you would have to convert...

Excel: to convert values into text at excel, google, addin

google, addin, numeric value: Shereif, You generally need to use VBA to do this: here are some references: http://support.microsoft.com/?kbid=140704 XL: How to Convert a Numeric Value into English Words http://support.microsoft.com/?kbid=95640 ACC: How to Convert Currency...

Excel: copy cells to another worksheet based on a value, macro code, column headers

macro code, column headers, aidan: you might have to point me to the original macro, but it s PROBABLY using an offset with that being set to a given starting point - I would imagine specialcells to get the last used row - amend it to start at row 2 and it will be fine - but for specific details,...

Excel: copying dates, keystroke sequence, true dates

keystroke sequence, true dates, exact method: S, I don t see how this could happen. Perhaps you should explain your exact method for copying dates. Maybe you are using an odd keystroke sequence that seems normal and usual to you, but if I saw you do it, I d immediately see what s wrong. ...

Excel: IF count then, blank cells, counta

blank cells, counta, a3: what did you want to count in the range of cells, and is it always the same three? COUNTA will count non blank cells COUNTIF will count cells matching certain conditions so =if(counta(a1:a3)=3, S , N ) would return S if there are any 3 entries...

Excel: count date, excel, countif

excel, countif: You could use the COUNTIF formula, for example: =COUNTIF(A1:A50,MAX(A1:A50)) I have used the MAX formula as the value to search, but you can use something different, if it is not accurate to calculate your last date on the range. Hope this helps, Migue...

Excel: count inventory using excel, inventory list, allexperts

inventory list, allexperts, aidan: There is no attachment as allexperts doesn t allow that - my email is aidan.heritage@virgin.net which would get round that problem. I would IMAGINE you are going to have a list of orders, so need to see at a glance any items where the stock total is below...

Excel: countif(and, c4, c1

c4, c1, critique: J, I m curious why you said the question wasn t answered. I gave you a very specific and clear answer, including accurate formulas that should have worked. Plus, I gave you a valuable critique of your formulas. So specifically what didn t...

Excel: countif, cell references, double quotes

cell references, double quotes, absolute reference: Steven, Make the reference to the data column an ABSOLUTE reference so it will not change when you copy it down. You do this by adding $-signs to the cell references. A reference to the cell H5, which contains the state name, should work perfectly....

Excel: countif(and formula question, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, countif: This would be easiest done with a second column. Make an IF statement to see if your date is within the range. If so, put a 1 there, if not a 0, then just SUM that column up. Let me know if you have any other questions or comments. Also, be sure to...

Excel: counting specific dates, sorry bob, e mail

sorry bob, e mail: =IF(COUNTIF(Sheet2!H6:H332,DATEVALUE( 1/3/09 )) 0, x , o ) It s because =1/3/2009 is not found. There s no = actually inside the range H6:H332; and the date 1/3/2009 as text isn t there either -- you need to make it a real date via DATEVALUE, as shown...

Excel: how to create formula to ignore bkts., array formula, running in circles

array formula, running in circles, cell a2: =A1-SUM(VALUE(LEFT(A2:H3,SEARCH( ,A2:H3)-1))) ) as an array formula (ctrl shift enter) would I think do it for you - as I see this, it s the instance of a space that marks the end of the number, so it s that that I would search for. Any problems do...

Excel: cross referencing names across multiple worksheets, david answer, p1w1

david answer, p1w1, excell: David, Okay, I ve confused you (as I thought I might) and you have confused me back. So lets start completely over and pretend none of this has happened! Please resubmit an entirely new question (not a followup) and pretend that I haven...

Excel: DATE DIFFRENCE, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, excel tutorials: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: Daily Pick 6 Lottery Number Generator, lottery number generator, max rows

lottery number generator, max rows, rw 1: Anibtu, Sub gennumbers() Dim rw, i, j, k, l, m, n rw = 1 For i = 0 To 9 For j = 0 To 9 For k = 0 To 9 For l = 0 To 9 For m = 0 To 9 For n = 0 To 9 Cells(rw, 1) = i Cells(rw, 2) = j Cells(rw,...

Excel: Data based entry and extruction, cell b2, vba code

cell b2, vba code, question questions: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...

Excel: Data Chart, true dates, compound statement

true dates, compound statement, 00000000000: Winston, So if you took my advice and read about the functions I ve named: MONTH(), YEAR(),DAY(), and DATE() functions, and the IF compound statement, then you must realize that you take apart the dates in question, perform a logical comparison of...

Excel: Data from multiple spreadsheets, spreadsheets, aidan

spreadsheets, aidan, macros: Yes, you can do this with a macro - are all the files going to be in the same location? If so you can use the Application.filesearch method to find all the files - not sure how well versed you are with macros, but if you can let me know if the files are in...

Excel: Data reduction in excel, excel, time

excel, time: Usually the pivot tables work wonders when making groups, but there are other alternatives. In this case you could do a something like the following: 1. On a separate sheet, type the bottom time of each interval that you want to consider, for example start...

Excel: Data reduction in excel, air quality data, time series data

air quality data, time series data, time stamps: Hi Molly, I would use a pivot table. Insert an extra column that calculates the time decade that row is in: =TEXT(HOUR(A2), 00 )&TEXT(MINUTE(A2), 00 )&TEXT(INT(SECOND(A2)/10), 00 ) For each span of 10 seconds this formula gives an identical result....

Excel: Data Vaidation and vlookup problem, data validation, dear bill

data validation, dear bill, rumana: Not clear how the data appears, but IF it s always over the same number of rows, but just a different sheet, then the INDIRECT worksheet function would do it for you =INDIRECT($A$1& ! &ADDRESS(ROW(),COLUMN())) with the sheet name in A1, this would return...

Excel: About Data validation, data validation, number thanks

data validation, number thanks, information student: Amit, I don t fully understand the question and sending a sample to look at would help. Are you missing the zero off the beginning of the number? This is common in telephone numbers and is cured by formatting the relevant cell(s) as text rather than...

Excel: Database, staff instructions, company server

staff instructions, company server, separate pages: Lee, There are several ways that you can do this. Which way you choose is mostly dependent on how many staff there are. 1) you can email each of them a spreadsheet page , have them download it, fill it out, and email it back to you. Then you get...

Excel: Date column for 24 hours, time column, time format

time column, time format, time data: I assume the data is JUST in time format, rather than day and time (if so, it s easy, as you just need to return the INTEGER - or INT function - of the date and time. Assuming not, put the date you want to start with in the first cell then (I ll assume here...

Excel: Date formats, tom hope, regional settings

tom hope, regional settings, calendar control: James Allen, the calendar control isn t part of excel, so I don t have any specific knowledge of it, but I would try Private Sub Calendar1_Click() Insert_date.Value = format(cdate(Calendar1.Value), dd mmmm yyyy ) Calendar1.Visible = False End...

Excel: Day by day update in chart, category axis, bxxx

category axis, bxxx, chart options: It s possible, but you ll need to use defined names. In the workbook with the dates & values -- say the dates are in A and the values in B: Define a name, Dates, to be: =INDEX(Sheet1!$A:$A,MATCH(TODAY(),Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(TODAY(),Sheet1!$A:$A,1))...

Excel: Delete duplicate rows in excel, dynamic ranges, ogilvy

dynamic ranges, ogilvy, macros: Shreya, If you are convinced that your answer just has to be a macro, I can t help you because I don t offer macro advice on this site. You ll have to check with another expert, try Tom Ogilvy because he is great at macros. But - - - If you have...

Excel: Delete rows macro help, cell changes, filter auto

cell changes, filter auto, column number: Lets say the active cell is k2 - when your macro deletes k2, the original k3 becomes the new k2, so the active cell changes - but I d misread the macro you wrote and not spotted the IF statement - my example is the method I tend to use and it s the one you...

Excel: Deleting extra check boxes from a cell., check boxes, checkboxes

check boxes, checkboxes, checkbox: Checkboxes (and all objects) have a topleftcell property which I guess you can use, since a checkbox isn t in a cell. untested, but this should clear all checkboxes which sit on top of row 4 (change accordingly): Sub DeleteFromRow() dim First as boolean...

Excel: Deleting the macros in modules of a VB Project, master template, macro code

master template, macro code, vb project: Example: to delete Module1 of active Workbook, the code is: Sub DeleteModule() Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Set VBProj = ActiveWorkbook.VBProject Set VBComp = VBProj.VBComponents(...

Excel: Display Different Pictures Based on User Response, lame answer, google

lame answer, google, excel formula: John, I ve seen this done but have never done it. The best I can do is tell you there is a website where there is a downloadable example of this. I myself don t know how it works. The best I can do suggest that you go on Google and try to find...

Excel: Display user form problem, trust center, macros

trust center, macros, many thanks: Lee: I have not used 2007 yet but I suspect that its events are the same as earlier versions. Therefore, if the workbook_open event does cause the form to load then I don t know why it will not load. Here is a sample for you to compare your code to hoping...

Excel: Drawdown formula, excel function, correct direction

excel function, correct direction, chronological order: Frank I am pretty sure that I don t fully understand the question, but I am thinking that you can use the LARGE() or SMALL() formula to find the answer to the largest loss. If you need to find the largest loss, which might be the amallest number depending...

Excel: Drawing objects in Excel, relevant boxes, autoshapes

relevant boxes, autoshapes, flowcharting: I ve spend a fair amount of time trying to research this for you, slightly hampered by the fact that I haven t got an installed copy of 2003 - and I ve managed to draw a fat blank - which doesn t exactly help - sorry to be of no use on this occassion...

Excel: Drop Down Lists within Excel, excel, drop down

excel, drop down: Depends on the type of drop down list. If it is based on data validation, select the cell, go to menu Data- Validation and modify the list of values or the original range. If it is a drop down from the forms menu, you can right click on it, select Format...

Excel: Due Dates to Chart, numerical quantities, chart excel

numerical quantities, chart excel, experience growth: Bonita, When you say a chart , do you mean you want a graphical display like a bar or line chart ? Chart is the Excel word for a graph. But the rest of your description doesn t seem to fit. I think what you really mean is a columnar TABLE....

Excel: Dynamic change in cell value depending on drop-down list item change, target value, excel drop down list

target value, excel drop down list, dynamic change: Vamshi, you need to use the Worksheet_Change function instead. This will trigger every time any cell in the worksheet is changed and you will therefore need to apply IF validation to the statement so that it ends immediately if the cell you are interested...

Excel: Dynamic range lookup select and copy/paste, block id, industrial engineer

block id, industrial engineer, block content: I m sorry, but without the trail of your question & my answer(s), this is pretty impossible to follow/understand. Sorry, but I have no idea what you re asking for. Either supply the entire trail of the question, and/or email me a sample workbook at bobumlas@yahoo.com...

Excel: Dynamic sumif formula, left hand corner, apple sales

left hand corner, apple sales, word sales: Ross, I love this kind of problem, it s fun to solve! Instead of INDIRECT, I d use a combination of MATCH and OFFSET to create the range for SUMIF. You can use MATCH() to determine the relative Column Number that contains the word SALES....

Excel: data reduction in excel, air quality data, time series data

air quality data, time series data, time stamps: For this example, we ll assume you ve got in cell A1 11:43:00 and in cell A2 11:43:01 etc down the col. In col B you have corresponding values for these times. In this example, col A and col F should be formatted as TIME, col B and col G should...

Excel: How to delete ', excel, clean

excel, clean, apostrophe: Mcheal, There are two simple ways to remove the leading apostrophe in Excel. The first way is specific to numbers. Simply multiply each cell by 1, then change the number format to custom 0000000 in your case. The second method is to use the function...

Excel: differentiate two columns in excel, excel, compare

excel, compare: Can you send more details on the layout, and what you are trying to achieve? The very basic comparison will be something like: =IF(A1=B1,value_if_true,value_if_false) This will compare cell by cell. If you are aiming for a different comparison, please...

Excel: dynamic printing in excel, colums, spreadsheet

colums, spreadsheet, novice: Lyle, you need to decide how you want to trigger setting the print area but this code bases the print area on the active cell Dim StartRow Dim StartColumn Dim EndRow Dim EndColumn StartRow = ActiveCell.Row EndRow = StartRow + 25 StartColumn =...

Excel: To Enter Date Through Calendar Control, visible target, target address

visible target, target address, calendar control: Attach the calendar control to C25. Right-click the tab, select View Code, insert this: Private Sub Calendar1_Click() Range( C25 ).Value = Me.Calendar1.Value Range( C26 ).Select End Sub Private Sub Worksheet_SelectionChange(ByVal Target As...

Excel: Enter rows on multiple sheets at different rows, row 24, sht

row 24, sht, arrow: Hi Dave, Have a look if this code does what you need: Sub InsertRowsAndFillFormulasJKP() Dim oCell As Range Dim vNumRows As Variant Dim lCt As Long For lCt = 1 To 3 I assume the sheets are named Sheet1, Sheet3 and Sheet3...

Excel: Error 91, datacol, sheet1

datacol, sheet1, setfocus: The problem is likely cause by the fact that you included this statement: frmFert.Show in the userform s initialise event. Normally you d use some code in a normal module that handles when the userform is shown, e.g. like this: Sub ShowFormNow()...

Excel: EXCEL, vlookup formula, cell a1

vlookup formula, cell a1, question data: Amit I appears as though you need to use a vlookup formula that will look up the identifier you enter (for example SA3) in the workbook that your data is located and bring back data from a column that you identify in the formula. The formula might look...

Excel: EXCEL COPY ERROR, excel, 1904

excel, 1904, date: That may happen if the option for 1904 date system is active on one workbook and not in the other. As the figures copied are numbers, they will be interpreted differently between workbooks. Check it on the menu Tools- Options- Calculation tab- Workbook options...

Excel: EXCEL LOGIC PROBLEM, conditional formatting, logic problem

conditional formatting, logic problem, spell checker: James, All you need to do is apply Conditional Formatting to the cell whose color you want to change. In Excel 2003, Conditional Formatting is located on the FORMAT menu, I think. In Excel 2007, it is located in the center of the Home ribbon....

Excel: Excel 2000, excel spreadsheet, column widths

excel spreadsheet, column widths, excel 2000: How are you importing the text? And is the file always the same name? I d be inclined to do it via a macro (indeed, I regularly do!) - would need slightly more details to the questions I ve outlined to help further, but happy to do so - my email is aidan.heritage@virgin.net....

Excel: Excel 2000, ogilvy, hyperlink

ogilvy, hyperlink, excel 2000: Glen You can change their properties with this macro: Sub ABC() Dim hlnk As Hyperlink For Each hlnk In ActiveSheet.Hyperlinks Set r = hlnk.Parent r.Font.Underline = True r.Font.ColorIndex = 5 Next End Sub or you can just have the macro delete...

Excel: Excel 2003, bloat, multiple users

bloat, multiple users, disaster: You ve answered your own question - it does make a difference, so unshare it and all will be well again - by the way, the accepted rule that all experts I have dealt with use for when workbooks should be shared is NEVER - it s a great idea, but it s a recipe...

Excel: Excel 2007, dimensional table, increments

dimensional table, increments, right direction: Lori, What you need to do is investigate the use of the SCENARIO. With a scenario, you can pick one or two variables, and a bottom-line result. Then you can specify over what range the variables will vary, and in what increments. Excel will automatically...

Excel: Excel 2007: Excel User Interface display, nissan nissan, interface window

nissan nissan, interface window, button interface: Nissan, Those are all things you can turn off in the EXCEL OPTIONS button at the bottom of the Button interface window. Look under ADVANCED and DISPLAY & DISPLAY OPTIONS FOR THIS WORKBOOK and uncheck the appropriate options. Then you can...

Excel: Excel, datevalue, second test

datevalue, second test, aidan: You don t explain the function very clearly - it SOUNDS like a 2 level count if? =sumproduct(--($L$4:$L$42=2),--(whattest=what)) is the method I d use - but what is the second test? Testing for a date is easy enough - datevalue will return the value...

Excel: Excel, pivot table, select group

pivot table, select group, invoices: Hi Ian, If your data is structured like a simple table, with each invoice on its own row and each field in its own column, then I advise you to try a pivot table (Data, Pivot table). Drag the date to the top row and the type to the row area. Rightclick...

Excel: Excel:, format button, dropdown list

format button, dropdown list, spreadsheet: If the dropdown result is in cell E3, for example: Select the whole row you want to change color, use Conditional Formatting, put in this formula: =$E$3= closed then click the Format button and select the green fill pattern. Add another conditional format...

Excel: Excel, target value, sheet tab

target value, sheet tab: Right-click the sheet tab, select View code, put this in: Dim Prev Private Sub Worksheet_Change(ByVal Target As Range) If IsNumeric(Target) Then Application.EnableEvents = False Target.Value = Target.Value + Prev Application.EnableEvents...

Excel: Excel, time series data, time column

time series data, time column, column c: The important thing is that you have a method that works - a macro method would simply speed up the process, and would be able to use fewer formulas - it s process would be start at the first cell check that the next cell is one second away - if not,...

Excel: Excel for Address Verification, phone number changes, address phone number

phone number changes, address phone number, excel spreadsheet: Amanda, The philosophy of this site is that experts will spend about 5 minutes or less to answer a question. It sounds like you are asking for a complete application. This is really a forum for peer to peer support. If someone has a technical issue...

Excel: Excel Auto color row, format button, format dialog

format button, format dialog, time chris: Hi Chris, Welcome in here! So you have rows spanning columns A to N (inclusive). Assuming you have Excel 2003 or earlier. - Select all these cells (e.g. A2 to N20) - Choose Format, Conditional format - from the first dropdown, select Formula...

Excel: Excel Cell Formatting - Time hh.mm.ss, mm ss, many different ways

mm ss, many different ways, time hh: Kiym, I am certain I can help you with this, because I completely understand how the Date & Time serial number works. Once you know that, you can figure out everything about dates and times. But I am not sure I understand the nature of your data....

Excel: Excel Charts and Data to PPT Automatically, powerpoint object model, click to add text

powerpoint object model, click to add text, hardeep: It would certainly be possible to control a PowerPoint presentation via VBA, but I have no experience of the PowerPoint object model- and as it is this you need to be addressing this question would PROBABLY best be directed to a PowerPoint expert (I have limited...

Excel: Excel changes my cell formatting, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, column c: If you enter a DATE even though your format is h:mm, Excel is going to change the format. That s just how it works. I don t know of any way around it. Stop entering dates in that column. :) Let me know if you have any other questions or comments. Also,...

Excel: Excel copying to another sheet, cell pointer, nextcell

cell pointer, nextcell, dynamic name: Thomas, Now that I look at your question (again), I see that something about it doesn t make sense, and indeed what you want may not be possible (without a macro). You say that when you type R&R in Cell B13 and (presumably ) ONLY in cell B13, you...

Excel: Excel drop down list, excel drop down list, drop down list

excel drop down list, drop down list, ogilvy: Taylor, Actually this is somewhat complicated but can be done. A special type of form-box is required with special properties being set, very similar to a maro. I don t do macro advice here, so I ll refer you to Tom Ogilvy who I am sure knows exactly...

Excel: Excel File Names, current date, yyyy

current date, yyyy, file names: You could use the before save event of the workbook to trap the save and run a Save As instead? BUT that would require the user to enable macros, so it wouldn t be foolproof? Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)...

Excel: Excel File Names, track thanks, current date

track thanks, current date, file names: Hi, sorry this answer will be a little short as it is Easter but feel free to come back with follow up questions. I think this should be easy enough to do by creating a Before Save sub that saves the sheet. You will probably also overwrite the existing...

Excel: Excel Filter, blank cells, logic equations

blank cells, logic equations, fliter: Vinay, The Autofilter doesn t and won t work that way. Leaving blank cells in a database is a very bad idea, and will come back to haunt you. What happens when you sort the database? or insert a row? Your database should never be dependent...

Excel: Excel Formula, header row, excel formula

header row, excel formula, pivot table: You could do a sum if, but MUCH easier would be to use a pivot table - you didn t specify version, but if NOT 2007 then it s data, pivot table and chart report and then follow the wizard - drop the server column (which would have to have a heading row) into...

Excel: Excel Formula, excel, lookup

excel, lookup: I have assumed that there is always a gap of 7 spaces between values. With that, you could do a formula like: =SUM(OFFSET(B1,MATCH(C1,A1:A17,0)-4,0,7,1)) This formula will give errors if there is no match, or the date is on the first three positions. You...

Excel: Excel Formula, james d brown, e d brown

james d brown, e d brown, joe bloggs: Entire column is way too big for this. It d be better to create a dynamic name, like ColA whose definition is: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) and ColB whose definition is: =OFFSET(ColA,,1) then use this formula (and I forgot to mention...

Excel: Excel Formula, excel formula, resultant data

excel formula, resultant data, cell values: Mike, Thanks for the positive comments. I believe you will be pleased with this formula: In cell, holding the 6 and the 8, then these cells hold as specified: Assume AB1: 6 (the row offset) AB2: 8 ( the column offset) then in the...

Excel: Excel Formulas, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vlookup: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: Excel Forumla, sum function, single one

sum function, single one, aidan: Why do you need to duplicate the information - would not a SUM function do what you need? It s also not clear what you mean by column/Row C7=etc - especially as you used J5 twice in the explanation? Is it possible to see a sample sheet - my email being aidan.heritage@virgin.ne...

Excel: Excel formatting, zeros, spreadsheet

zeros, spreadsheet, formatting: JOSH You are limited to 7 imbedded IF statements, but you are not limited if you make the statements stand alone. I believe this formula will work for you. =IF(AND(H3= Yes ,I3=1),65,0)+IF(AND(H3= Yes ,I3=2),80,0)+IF(AND(H3= Yes ,I3=3),95,0)+IF(AND(H3=...

Excel: Excel formula, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, excel formula: There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates. Read this free tutorial from my web site that explains how...

Excel: Excel formula for Expiration Dates, etc., excel, dates

excel, dates, format: Regarding the formula, you could do something like: =IF(ISBLANK(B6), ,IF(ISBLANK(S6), N/A ,B6+90)) This will return the value of B6+90 if B6 is filled up, and N/A in case S6 is filled up. I am not sure on the formulas that you are using for conditional...

Excel: Excel formula error = #value!, value question, quotation marks

value question, quotation marks, excel formula: Sure, but I don t understand the difference that the code makes on the formula. For example, to replicate the results that you have on the picture you could use a formula like: =ABS(E2-D2)*B2 You are right, the text conditions can be entered there with...

Excel: Excel formula question, grade structures, grade structure

grade structures, grade structure, excel formula: I have a slight disadvantage in that I m in the UK so don t know the grade structure, but I think you d need to store the DATE of the response, and use the current date to work out how many years have elapsed - and then return current grade based on that -...

Excel: Excel formula using date parameters and other column criteria, g 5000, logical results

g 5000, logical results, sumproduct: Dave, =sumproduct(--($G$2:$G$5000= M ),--($F$2:$F$5000 datevalue( 10/01/2008 )),--($F$2:$F$5000 DateValue( 12/31/2008 ))) would be the general form for all your questions. this ansered the first specifically. You can t address and entire column...

Excel: Excel formulas, excel formulas, excel spreadsheet

excel formulas, excel spreadsheet, b10: Tania, Y, N and NA are all perfectly okay. I think all you need to know is the number of N s. So, for each row, =COUNTIF(the-row, N ) will give you the number of N s. If each N is one point, then just subtract the number of...

Excel: Excel - Help with summing range of values in lookup table, cell references, excel help

cell references, excel help, integer values: If I follow this you want to add up all cells in a range where another column in the same range is between 2 dates? If so, you can do this with sumproduct =sumproduct(--(DateRange =FirstDate),--(DateRange =SecondDate),SumRange) DateRange,FirstDate,SecondDate...

Excel: Excel help, spreadsheet

spreadsheet: It means the column isn t wide enough to display the data - so rather than display 1 January for example, which could be 1, 11, 21 or 31 January if not wide enough, excel displays the # symbol so that you don t get confused when reading the printout. The...

Excel: Excel help, excel, if

excel, if, logical: You may try with a formula like: =IF(OR(AND(ISBLANK(O3),G3 F3),O3 F3), PAST DUE , OK ) You show a good understanding of logical functions, so I am sure that you will be able to tweak it if needed. The conditions that it is tracking are two: it will be past...

Excel: Excel: Linking cell contents and formula, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, source sheet: You are correct. That s how you do it. Or you can COPY and then Edit Paste Special Paste Link. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL....

Excel: Excel looking up and returning ranges, loy yang power station, loy yang power

loy yang power station, loy yang power, sequance: Lucasz, I seem to understand what you need, and perhaps it has been so long that you have solved it by now. But if not, here are a few ideas to get you started. What you seem to need is a variable range that you can easily select and paste....

Excel: Excel Macro, orgsh, c values

orgsh, c values, selection sort: Mel, I moved those code lines higher in the procedure to avoid making modifications to the sheet and then not restoring them. With trying to figure out what your code is doing to fine the best place, I forgot to move a related line above or prior to those...

Excel: Excel Minimize, task bar, spreadsheet

task bar, spreadsheet: I would IMAGINE that you have Excel running when you shut down the computer, probably minimized to the task bar - this is the most likely cause of the symptoms you describe as Excel will start at last used window status - or as close as it can get - on toolbar...

Excel: Excel, nested if structure, excel, na

excel, na: I am not sure what you want to show with NE, if it is a text or the actual error code. If it is the error code, you could use the formula NA(), something like: =IF([Years Service] 1,IF([Pay Grade]=1,$Z$6,IF([Pay Grade]=2,$Z$7,IF([Pay Grade]=3,$Z$8, Invalid...

Excel: Excel Pivot Tables, pivot tables, row headers

pivot tables, row headers, pivot table: Holli, Sorry for the delay in answering this, and I realize you may have gotten another answer by now. Hopefully your other answer is better than mine, because I have to say I don t know of a way to do this! The only thing I can think of is to use...

Excel: Excel Programming Issue, programming issue, sheet names

programming issue, sheet names, excel programming: I m not sure I totally follow the requirements as you state you want to store that value into cell A5 - is that on the original sheet (thus destroying all data except the last) or the new sheet - however, something like dim looper as double looper=1...

Excel: Excel pivot Table Visible field manipulation Using VBA, excel pivot table, excel 2007 pivot table

excel pivot table, excel 2007 pivot table, pivottables: Hi Adam, I believe that in Excel 2007 you must enable multiple PageItems when you work with pre-Excel 2007 OLAP PivotTables. Try this: Dim Pfield As PivotField Set Pfield = ActiveSheet.PivotTables( PivotTable1 ).PivotFields( [Time].[Hierarchy].[workweek]...

Excel: Excel Question, variable percentage, question question

variable percentage, question question, percentage changes: Ingrid, If the -9.2 % is wrong, what should it be? What result do you want there instead? Now... you just went and, somehow, by hand, calculated what it SHOULD be. Show me EXACTLY what you did, and I can write an equation that duplicates your...

Excel: Excel Question about creating a copy formula, incident number, vlookup

incident number, vlookup, questioners: Katina, I m sorry that I upset you by either talking above, or below, your skill level... I still don t know which way I erred! I have no knowledge of your skill level... you didn t indicate anything. Therefore I included a tutorial on VLOOKUP....

Excel: Excel If Statement?, sum formula, column c

sum formula, column c, pie chart: Jake, All you need is a stack of SUMIF() functions. However, your image refers to different columns than your text. You seem to want to sum column B (not A) if there are certain values in column C (not B). Also the data in the image goes from...

Excel: Excel sorting, consecutive rows, index column

consecutive rows, index column, sort key: Cameron, This will probably teach you not to create a database that has linked records across more than one row! That s a real no-no for reasons that you now see. I m sure it was convenient to compare plan vs actual, so I understand why you did it. ...

Excel: Excel spreadsheet formula, spreadsheet formula, excel spreadsheet

spreadsheet formula, excel spreadsheet, true answer: Sorry, but this doesn t make sense to me. N1 needs an input -- you mean someone enters a value into N1? If so, if it s between A1 & B1, how can it equal C1? I think I need an actual workbook to look at. Send one to bobumlas@yahoo.com, use subject of...

Excel: Excel text formula, bank account numbers, excel sheet

bank account numbers, excel sheet, original p: Doris, Don t worry, its easy and there are two ways you can do it. 1) The SORT method This is the easiest because you won t have to write a single equation. But its not automatic as you add new customers. you d have to add their numbers...

Excel: Excel text to number, excel, format

excel, format: You can do that with a custom format (menu Format- Cells- Number tab). This one, for example, will show the cell blank if a number is entered, and it will show 3 if any text is entered: ;;; 3 I am not sure if this what you wanted, as this will not change...

Excel: Excel UDF returning #NAME? error, error function, udf

error function, udf, vb: Try Public Function GetSmall() As Integer Dim r As Range, ans As Integer With Sheet1 Set r = .Range( A1:F1 ) ans = Application.WorksheetFunction.Small(r, 1) MsgBox ans End With GetSmall = ans End...

Excel: Excel VBA Code Help- find and copy from mult sheets, excel vba code, ap cells

excel vba code, ap cells, srow: Moving to a larger number of questions, I d suggest insert a userform into your program - this could ask all the questions - for the choice of sheet, a list of available sheets would be sensible. Clearing contents is is done with range(whatever).clearcontents....

Excel: Excel VBA Connection String or Shell, vba code, shell question

vba code, shell question, excel vba: You re right, that is what I did. I am not unwilling to help and did not know how much, if anything, you know about macros since you did not say. And therefore how was I to know? To your question, please give me a few examples of what the code needs to...

Excel: Excel VBA InputBox to Create Sheets, vba code, excel vba

vba code, excel vba, cell a1: Sub addSheets() Dim strWks As String, strRng As String Dim rngName As Range Dim wks As Worksheet Dim i As Integer strWks = InputBox( Name of Worksheet ) strRng = InputBox( Call with sheet name on & strWks & sheet ) Set rngName = ThisWorkbook.Sheets(...

Excel: Excel VBA; Navigate to Specific Cell on Sheet 2 Based on Cell Address in Sheet 1, question pool, sheet1

question pool, sheet1, commandbutton: John, No problem with the late rating. Thanks for coming back and making the effort to give me a terrible rating. I am sure you will give me the respect of not asking me any further questions since you have such a low opinion concerning my abilities....

Excel: Excell field expansion, free microsoft excel, table relationships

free microsoft excel, table relationships, excel tips tricks: You would need a second table to store the details, and a single field (preferably an ID) to relate them. It sounds to me like you need to learn a little more about the basic concepts of building a Relational Database, and making relationships between two...

Excel: Excell Help, indirect address, cell values

indirect address, cell values, address values: Sudhir, I don t see the pattern in what copies to where, given your example. In the meantime, I would suggest that possibly the =INDIRECT(ADDRESS(parameters) compound function could be used. This function will retrieve...

Excel: Execel - simple cash low payout, detailed explanation, help system

detailed explanation, help system, financial analyst: Bob, Since I am not a financial analyst, I don t know the terms you re using. If you d define exactly what a payout is, and explain how you would calculate this on pad of paper, using your numbers as an example, then I am sure I could devise a means...

Excel: Extracting the email address from outlook after the name is insert into the worksheet, outlook address book, outlook application

outlook address book, outlook application, global address: I would have to see the value is in the global adress book for the person you are trying to find to see why what you type in A1:A2 is not found and obviously I can t see the entries in your address book. Have you tried writing that data to the sheet to...

Excel: Extracting info from a string using excel, column data, a17

column data, a17, doable: By count do you really mean count or sum? In the example you gave, for the desired extensions, the count would be 6 for add, delete or modify, but the sum would be 1 for add, 6 for delete, and 0 for modify. Assuming you want SUM: For your specific example,...

Excel: excel, excel formula, pivot table

excel formula, pivot table, aidan: I think this would need VBA to achieve with a custom function, but I m not 100% sure I m clear on what you want - for instance, if the values in A are repeated, would you want to have the same information returned each time - would it not be easier to use...

Excel: excel, coloum, attendance sheet

coloum, attendance sheet, tathagata: How does the sheet record that you are present - this has a bearing on the answer - it is BASICALLY going to be along the lines of =if(or(Present on AbsenceDay+1,Present on AbsenceDay-1),1,3) as a means of working out the leave time - but obviously the...

Excel: excel cell 'rule', target value, rule question

target value, rule question, sheet tab: Assuming you mean column A when you refer to column 1, then do the following: Right-click the sheet tab, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Dim ColumnBValue As String If Target.Column = 1 And LCase(Target.Value)...

Excel: excel cells, excel, text

excel, text: You will certainly need to use another cell as B1 for that. Cells in Excel can contain either values or formulas, but not both at the same time. Regarding the formula, it will depend on how the apples are entered in the cell. Excel works better on counting...

Excel: excel column is not doing the correct sum in excel 2003, c code, cells

c code, cells, syntax: I m not sure I follow the question - if the C# code is creating the file, it should be able to create it correctly- certainly only in one file- IF the data is coming over as text (which it shouldn t) then it could be modified by code - something like (VBA...

Excel: excel conditional formatting, color phase, different colors

color phase, different colors, logical formulas: Rick, The title of your question contains the answer. Use Conditional Formatting to achieve this. (I will hereafter type CF to mean Conditional Formatting). So, you obviously already knew that: what s your actual question? Don t you understand the...

Excel: excel and email addresses, correct options, network paths

correct options, network paths, must be a way: Edwin, You didn t mention what version of Excel you are using. The steps will be slightly different in the different versions, but you will need to access the Auto Correct Options. In Excel XP and previous versions this is in the Tools menu. In Excel 2007...

Excel: excel - export data, excel export data, count column

excel export data, count column, nextpos: as you gave me a full blown, reasonably complex procedure I assumed you were Ok with VBA - so my apologies for that - the case select was written to give you an indication of what to do - so I ve put in a REMark (the indicates that) which should be replaced...

Excel: excel formula, cell c1, ff2

cell c1, ff2, fl1: I ll interpret your question thusly. You ve got data that starts in cell A1 and continues down col A. You want to count the # of times that there s a cell that begins with one of the 5 strings you list... AND a cell immediately below it with the value Circuit...

Excel: excel formula, cell a2, excel formula

cell a2, excel formula, cell a1: This is somewhat confusing, because first A and B aren t rows, they re columns. Secondly, you say you have 10-digit numbers, but your example is an 8-digit number. That being said, the solution is as follows. Say you have in cell A1 the number: 12345678...

Excel: excel formula, excel formula, 2m

excel formula, 2m, a2: Sure. It is still an array formula, entered on cell A1 of Sheet2: =SUM(--(ISNUMBER(SEARCH( FLOREAL 3.3, Trib2 Slot 408 2 Mb/s ,Sheet1!A1:A31)))) The difference is the adding of the name of the sheet before the range to search. It still needs to be entered...

Excel: excel formula, cell b3, index function

cell b3, index function, excel formula: Use the MATCH worksheet function to return the row from the data set =match(TheValue,DataSet,0) this will give a number between 1 and 15 representing the location of that item in the list. You can then use the index function with this so =index(YourNameRange,match(TheValue,DAtaSet,0),1)...

Excel: excel formula, thanks in advance, countif

thanks in advance, countif, excel formula: On Sheet2, cell B1, enter: BAIE JACOTET/3-MLK R/4 1S On Sheet2, cell A1, enter: =B1& = &COUNTIF(Sheet1!A:A,B1& * ) This formula will return: BAIE JACOTET/3-MLK R/4 1S =6 because in Sheet1, Col A, there are 6 entries that begin with BAIE...

Excel: excel formula, excel formula

excel formula: Carol You can use an if statement with a Day option, have a look at the formula below. =if(Day(A1) =15,1,2) This will look in A1 for the date you are using (or whichever cell you have the date in and then using the Day function will check to see if...

Excel: excel formula to convert min:sec to seconds, excel, seconds

excel, seconds: You are doing right with the format, but the data entry may be the problem. If you type 7:30 in a field with the mm:ss format it will record 7 hours and 30 minutes. If you include the hour on the same cell, it will be recorded properly, so you can try typing...

Excel: excel formula to convert min:sec to seconds, format cells, mm ss

format cells, mm ss, excel formula: Hi Pauline How are you? Please do the following: 1. Type the time 7:30:00 in A3 2. Select A3 3. Format- Format cells 4. Select customise in Category (on the left) 5. Type hh:mm in Type (on the right) Second Question 1. Type in B3 =HOUR(A3)*60+MINUTE(A3)...

Excel: excel formula help, having a nightmare, sumproduct

having a nightmare, sumproduct, excel function: Fraser, You should have written sooner instead of getting so frustrated! It isn t that hard. But, it IS advanced, so it s not surprising that you didn t know how-to. What you are looking for is called a selecting database records with more...

Excel: excel formula problem, excel, sum

excel, sum: I am not sure if the SUMIF formula is the right one here, you may try with a simpler approach like: =stop_min-start_min+60*(stop_min =start_min) As I am not sure on what stop_min and start_min represent (a single cell or a range of cells), or the exact format...

Excel: excel formulas, ny islanders, names thanks

ny islanders, names thanks, excel formulas: Dennis, What happens when ST LOUIS DETROIT or DETROIT ST LOUIS you can only make formulas so smart. I would suggest you make a list of teams name in another location and use that in the formulas to parse out the team names. If you want to go...

Excel: excel help, colb, evrytime

colb, evrytime, sum function: If you re going to need i & j to be known, then =Closetime(A5,B5:B7) is the same as knowing i and j where i is 5 and j is 7. So I don t understand the difference. When you write an =SUM(...) function, you also need to specify what it s referring to, not have...

Excel: excel PROBLEM, shif, dear sir

shif, dear sir, half day: 1 - All caps looks like you re shouting, please avoid. 2 - I have no idea how 1/2 day duty is indicated. 3 - Shifts of A,B, etc doesn t mean anything to me and I don t know how this affects your issue. 4 - I don t see what your question is. 5 - I don...

Excel: excel percentage, excel, percentage

excel, percentage: The formula that I normally use it the first one, =((B61-C61)/C61)*100. This is the difference between the two, divided by the last year figure. I understand your concern about the 300%. That is actually the ratio between the two of them, but that doesn...

Excel: excel if-then problem, d103, currency

d103, currency: A cell FORMATTED as something doesn t change it s underlying value, I would tend to use a ROUND function in any calculated results to resolve this - your explanation is a little hard to follow, but assuming the formula is the problem =(IF(AND(round($D98,2)...

Excel: excel - how to stack multiple sheets, excel, copy

excel, copy: You can use VBA for that. Here is one page with various tips that can be useful, with code samples: http://www.rondebruin.nl/tips.htm This one, that can be closer to what you want, is on the copy/paste/merge section: http://www.rondebruin.nl/copy2.htm...

Excel: excell count function, chip pearson, array functions

chip pearson, array functions, array function: Glyn, Sorry I clicked the wrong button. To do this you need to use an array function. Chip Pearson has a great tutorial on array functions on his site http://www.cpearson.com/excel/ArrayFormulas.aspx. Your formula will end up looking something like: {=SUM((B1:B200=...

Excel: exel, excel, list

excel, list: You can use one of them as the master, where you do all the changes, and use formulas like this on the other: =Sheet1!A1 This way you will see the same list of names on each sheet. If you need to work with values, and be able to modify them on both sheets,...

Excel: exel comment box, space character, help file

space character, help file: The name is the indicator of the person who made the comment, it s taken from tools, options, general, user name - although you CAN change your name, there must be something in this box - one option would be a single space character - but that would still...

Excel: Filter on excel, excel, advanced filter

excel, advanced filter: You can use advanced filter for this. Format the headers as bold, so Excel recognize them as headers, select the range, and go to menu Data- Filter- Advanced Filter. You can either filter the list in place or copy to other location, but the box that will...

Excel: Find Duplicates, serial numbers, spreadsheet

serial numbers, spreadsheet, ip addresses: Steve, I read this question to mean that you want to know if each entry in Col H exists in Col AD (several times you refer to AB instead). If the entry does exist, you want col K to take the value from col AB from the row associated with the matching...

Excel: Find and dates, chronological order, array

chronological order, array: You don t say how far down Col A the monthly dates go. So for this example, I ll assume 100 rows of dates. You can easily change this by changing the 100s in the formulas to 1000s etc. In cell E1, enter the formula: =SUM(IF(YEAR($A$1:$A$100)=YEAR(D1),$B$1:$B$100,0))...

Excel: Find and Report Data, excel, index

excel, index, match: You can use the INDEX/MATCH combination, for example: =INDEX(A1:H1,1,MATCH( Up ,A1:H1,0)+1) This will look on the row 1, from A to H columns for the Up cell, and it will return the value from the cell to the right of it. If the location of Up changes,...

Excel: Finding out addresses of cells based on index based string search in a sheet, excel, countif

excel, countif: My recommendation on this case would be to include the Market Area number on a separate column, and use a formula like SUMPRODUCT: =SUMPRODUCT(--(A1:A1000= MarketAreaFirst ),--(H1:H1000= Passed )) I think Excel 2007 has a COUNTIFS formula that can be useful...

Excel: Finding max value from a list with same index, array formula, curly brackets

array formula, curly brackets, nb6: Sada, Put this list in Sheet2 Sheet2 a1: NB6.3A22 a2: NB21.4A11 a3: NB21.6A21 a:4 NB22.1A11 then, enter the below formula in b1 of sheet2: a1: NB6.3A22 b1:=Max(if(Sheet1!$A$1:$A$1000=$A1,Sheet1!$B$1:$B$1000)) a2: NB21.4A11 a3: NB21.6A21...

Excel: Finding a sum based on a single criteria in multiple columns, irregular structure, vacation hours

irregular structure, vacation hours, excel spreadsheet: Laetitia, This is somewhat difficult due to the irregular structure of your calendar. But it can be done, of course! The solution is to add TWO shadow calendars , one on a sheet called SICK and another on a sheet called VAC. What we ll...

Excel: FORMULA FOR TIMESHEETS, excel, time

excel, time: I am not sure what formula you are using, but you can try with a simple subtraction: =B1-A1 The # signs can be because of the cell format, trying to show more details that can fit in the current width. Try making the column wider, or forcing the format...

Excel: Follow up on past question - worksheet compiler, subscript out of range, alex graham

subscript out of range, alex graham, code excel: Alex, When you get the error, hit choose debut and you should be taken to the vBE with the offending line highlighted. The array is the obvious suspect, but there is nothing in the code that would cause the array to be addressed out of bounds. ...

Excel: Form validation in vba and date issue, validation procedure, validation procedures

validation procedure, validation procedures, dummies books: Private Sub txtExamDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim ctrl As Control Dim blnValid As Boolean blnValid = True Set ctrl = Me.txtExamDate If ctrl = Then MsgBox Cannot leave Date textbox blank ...

Excel: Formula, excel formula, row c

excel formula, row c, work thanks: Jon, a great opportunity to use my very favorite Excel formula.. nested ifs... try and compile the statement in your head by saying if this then that.. write it down and then turn it into formula.. something like this.. assuming you mean columns not...

Excel: Formula about, vlookup, column c

vlookup, column c, thanks in advance: Sherif, This is a simple application of VLOOKUP. Pus a VLOOKUP in each cell of Column D. The VLOOKUP in D1 will look like: =VLOOKUP(C1,$A$1:$B$500,2,0) Then just copy this down as far down column D as you need. Note that I have assumed...

Excel: Formula About, vlookup, question thanks

vlookup, question thanks, column c: shereif, Here is a copy of my workbook A B C D 1 Jack 3 Victor 2 Henna 3 Victor 4 Daniel c1: 3 D1: if($C1= , ,vlookup($C1,$A:$B,2,False)) so the formula works fine...

Excel: Formula for data replication, data replication, clarifications

data replication, clarifications, aidan: Not exactly sure what you need here- are you talking about having as many rows as you need and have this update - if so, a macro could do this - or a formula set to as many rows as you think you need - my email if it helps with any clarifications is aidan.heritage@virgin.ne...

Excel: Formulas, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Without seeing your sheet, or your data, I can t tell what you re trying to do. Perhaps you could use VLOOKUP to look up the values and colors and do your math that way… but you didn t give me anywhere near enough information to answer your question. You...

Excel: Frequency function with dynamic criteria, frequency function, frequency table

frequency function, frequency table, input value: Kim, At last I have thought of a way to accomplish this! 0) the technique will be to replace the DATA-RANGE in the FREQUENCY function with a dynamic range which is computed by the OFFSET function. 1) in order to do this, the entire data range...

Excel: Absenteeism Formula

Excel: Absenteeism Formula

Excel: Auto fill, invoice number, stock control

Excel: Auto fill, invoice number, stock control, max 5

Excel: averageif formula, b4, a4 a6

Excel: averageif formula, b4, a4 a6, cell values

Excel: If Condition, cells

Excel: If Condition, cells

Excel: Convinience of a drop-down calendar

Excel: Convinience of a drop-down calendar

Excel: Deleting the macros in modules of a VB Project, vb components, excel application

Excel: Deleting the macros in modules of a VB Project, vb components, excel application

Excel: Dynamic sumif formula, cheers, ross

Excel: Dynamic sumif formula, cheers, ross

Excel: How to delete ', prefix character, excel function

Excel: How to delete ', prefix character, excel function, printing characters

Excel: Excel VBA Connection String or Shell, loser

Excel: Excel VBA Connection String or Shell, loser

Excel: excel and email addresses, blank cell

Excel: excel and email addresses, blank cell

Excel: excell count function, column c, formulae

Excel: excell count function, column c, formulae, logic

Excel: Form validation in vba and date issue, goto label, 4 digits

Excel: Form validation in vba and date issue, goto label, 4 digits, cdate

Excel: Looping through rows in excel to compare data, donator, spread sheet

Excel: Looping through rows in excel to compare data, donator, spread sheet, z1

Excel: Pasting Conditional Formatting, target cells, format painter

Excel: Pasting Conditional Formatting, target cells, format painter

Excel: Random Question Generation in MS Excel, quiz, demo

Excel: Random Question Generation in MS Excel, quiz, demo

Excel: Run-time error '13': Type mismatch, c cells, array

Excel: Run-time error '13': Type mismatch, c cells, array

Excel: return multiple data from dropdown list, data validation, category column

Excel: return multiple data from dropdown list, data validation, category column, valid values

Excel: Scanning ID Barcodes into Excel and have them show Names, notepad, canada

Excel: Scanning ID Barcodes into Excel and have them show Names, notepad, canada

Excel: Stretching a set of data, dynamic arrays, vsz

Excel: Stretching a set of data, dynamic arrays, vsz, usz

Excel: Sum Numbers only in cell array, array formula, exact answer

Excel: Sum Numbers only in cell array, array formula, exact answer, number string

Excel: Turn Around Time Calculation (TAT), turn around time, lorry

Excel: Turn Around Time Calculation (TAT), turn around time, lorry, csv

Excel: Use of Excel 'Text to Columns' wizard

Excel: Use of Excel 'Text to Columns' wizard

Excel: Validation Order, blank rows, drop down list

Excel: Validation Order, blank rows, drop down list, validation

Excel: vba - excel to pdf, excel sheet

Excel: vba - excel to pdf, excel sheet

Excel: xl 2007 - conditional formatting - Icon set, conditional formatting, relative cell

Excel: xl 2007 - conditional formatting - Icon set, conditional formatting, relative cell, large numbers

Excel: filling in formulas from columns to rows, cell a2, course excel

cell a2, course excel, sh1: Steven, In you first post you said: Of course excel is designed to fill in the same direction so I m sure this will require a macro. So I gave you a macro. If you want formulas, then send me a copy of the workbook and I will set it up for you....

Excel: find the missing parts, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vlookup: You can use the VLOOKUP or HLOOKUP function. VLOOKUP is a powerful function in Microsoft Excel that allows you to look up a value from another spreadsheet - or a smaller subtable (lookup table). See this FREE tutorial for help with VLOOKUP: http://599cd.com/tips/excel/vlookup/?key=AllExperts...

Excel: find the top 5 quantity of product sold, january row, orng

january row, orng, field options: OK. I named the range with data Data (insert, name, define). This code inserts a new worksheet and puts the top-5 items on that sheet: Option Explicit Sub GetTop5() Dim oSh As Worksheet Dim oRng As Range Dim lCt As Long Set...

Excel: find the top 5 quantity of product sold, sh1, rr1

sh1, rr1, aa1: Mike, If I put the name in AA1 and the value in AA2, then I am taking up two rows per month (I believe you said you wanted the high five for each month). I have implemented a report in the macro. Hopefully that will suffice. I tested this on notional...

Excel: find the top 5 quantity of product sold, january row, vba procedure

january row, vba procedure, writing a script: Hi Mike How are you? You can use the function Largest(range, K), i.e. If you have 6 products - names in A1:F1 1. Type 1 t 5 in G1:K1 2. Type in G2 =large($A$2:$F$2, F1) 3. Copy G2 to H2:K2 This do not give you the product names, just the quantities....

Excel: finding a company name, toyota dealers, cell b1

toyota dealers, cell b1, cell c1: Jie Since the name you are searching for may be in a string of text you can use the search feature to determine if the name is present in the string. The formula might look like this =SEARCH( toyota ,A1) The formula will return the number of the...

Excel: finding first name from two worksheets, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, information columns: Combine both names into Column C and then search on this. =A1 & & B1 Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: IF AND THEN formula, data manipulation, logical statements

data manipulation, logical statements, menu functions: Jon, Thanks for the kudos! Well, this time I might not have the answer you want. In order for a value to appear in a cell, there HAS to be an equation IN that cell which equates to the value. A formula cannot place a value in a cell other than...

Excel: If formula, result columns, variable numbers

result columns, variable numbers, character positions: Niki, Yes, its quite possible, though you don t use an IF statement. By the way, if you are using Excel 2007, it has a built-in ribbon operation that just does this automatically. Use DATA-- TEXT-TO-COLUMNS and forget the rest of this answer....

Excel: formula to shrink a string too long error and ignore blank cells, spreadsheet formulas, blank cells

spreadsheet formulas, blank cells, hello bill: David, You can modify your IF statements to include a check for blank cells. This will make them even longer, so you ll have to resort to splitting them into several cells and recombining as you suggested. Something like this should work for...

Excel: formula for summing values under merged cells, cell references, real solution

cell references, real solution, time right: Srikanth, I thought there was something you weren t telling me! So your REAL solution is to use an OFFSET function. It will look like this: =SUM(OFFSET(parameters)) The OFFSET function defines a region. Any function which uses...

Excel: which formula to use?, playoff hockey pool, highest score

playoff hockey pool, highest score, numerical value: Jeff =Index(A:A,Match(Max(H:H),H:H,0),1) Should return the name of the player in your example. However, with so many duplicate scores, you are just lucky someone has the single highest score. There or no regular formulas that will really work...

Excel: IF formulas, relative magnitudes, conditional test

relative magnitudes, conditional test, test ct: Jon, A DIT (aka Do-If-True) is just like any equation any where in any cell, except you don t begin it with an = sign. So if you needed to multiply two numbers in cells D12 and D13, your write D12*D13 for the DIT. If you want to set the result...

Excel: formulas, cell references, additional number

cell references, additional number, stop time: Jason, Okay, here s a REAL answer. Knowing that time is a special serial number, you can test to see if the stop time is less than the start time. IF it is, add 1 to the stop time. Adding 1 is the same as adding 24 hours. Then, you can...

Excel: frequency in excel, frequency distributions, frequency function

frequency distributions, frequency function, follow ups: If this is simply a question of counting particular entries, then I d use a pivot table to do this - that would easily give you the COUNT you need - I would expect the frequency function to fail as text doesn t fall with a range of values as is required...